c# DBHelper对象导出sql语句

转载

mb5fd86cce321a9 2021-07-20 10:03:00

/// <summary>
/// 由数据集DataSet自动生成Insert的SQL语句集合
/// </summary>
/// <param name="ds">数据集</param>
/// <param name="TableName">表名</param>
/// <returns></returns>
public static ArrayList DataSetToArrayList(DataSet ds, String TableName)
{
ArrayList allSql = new ArrayList();
//
string FieldAll = "";
//获取列名集合
for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
{
FieldAll = FieldAll + ds.Tables[0].Columns[i].ColumnName.ToString() + ",";
}
FieldAll = FieldAll.Substring(0, FieldAll.Length - 1);//去掉最后一个“，”

DataView dv = ds.Tables[0].DefaultView;
string ValueAll = "";
//判断字段类型，其实也可以全部用单引号引起来，只是数据库处理时，内部需要转化类型
for (int n = 0; n < dv.Count; n++)// 用于循环写insert
{
for (int m = 0; m < ds.Tables[0].Columns.Count; m++)//
{
switch (dv[n][m].GetType().ToString())
{
case "System.DateTime":
ValueAll += "'" + (Convert.ToDateTime(dv[n][m])).ToString("yyyy-MM-dd") + "'";
ValueAll += ",";
break;
case "System.String":
ValueAll += "'" + dv[n][m].ToString() + "'";
ValueAll += ",";
break;
case "System.Int32":
ValueAll += Convert.ToInt32(dv[n][m]);
ValueAll += ",";
break;
case "System.Single":
ValueAll += Convert.ToSingle(dv[n][m]);
ValueAll += ",";
break;
case "System.Double":
ValueAll += Convert.ToDouble(dv[n][m]);
ValueAll += ",";
break;
case "System.Decimal":
ValueAll += Convert.ToDecimal(dv[n][m]);
ValueAll += ",";
break;
default:
ValueAll += "'" + dv[n][m].ToString() + "'";
ValueAll += ",";
break;
}
}
ValueAll = ValueAll.Substring(0, ValueAll.Length - 1); //去掉最后一个“，”
allSql.Add("insert into " + TableName + " (" + FieldAll + ") values(" + ValueAll + ");");//insert
ValueAll = "";//清空继续循环
}
return allSql; //返回语句
}
private void Export_mm3s()
{
MessageBox.Show("正在导出mm3s数据！");
ArrayList list = new ArrayList();
string sql = "select * from agencytable";
DataSet dt = DBHelper.GetInstance().ExecuteQuery(DBHelper.connectionString, CommandType.Text, sql, null);
list = DataSetToArrayList(dt, "agencytable");
// 创建文件。如果文件存在则覆盖
FileStream fs = File.Open(@"d:\内容.sql", FileMode.Create);
// 创建写入流
StreamWriter wr = new StreamWriter(fs,System.Text.Encoding.UTF8);
// 将ArrayList中的每个项逐一写入文件
for (int i = 0; i < list.Count; i++)
{
wr.WriteLine(list[i]);
}
// 关闭写入流
wr.Flush();
wr.Close();