c datatable导入mysql_将DataTable中的数据导入到数据库中

上次在

小小的改动即可。

///

/// 将DataTable中数据写入数据库中

///

///

///

public static bool WriteDataToDB(DataTable dt)

{

if (dt==null || dt.Rows.Count == 0)

{

return true;

}

string tname = dt.TableName;

string colNames = "";

for (int i = 0; i < dt.Columns.Count; i++)

{

colNames += dt.Columns[i].ColumnName + ",";

}

colNames = colNames.TrimEnd(',');

string cmd = "";

string colValues;

string cmdmode = string.Format("insert into {0}({1}) values({{0}});", tname, colNames);

for (int i = 0; i < dt.Rows.Count; i++)

{

colValues = "";

for (int j = 0; j < dt.Columns.Count; j++)

{

if (dt.Rows[i][j].GetType() == typeof(DBNull))

{

colValues += "NULL,";

continue;

}

if (dt.Columns[j].DataType == typeof(string))

colValues += string.Format("'{0}',", dt.Rows[i][j]);

else if (dt.Columns[j].DataType == typeof(int) || dt.Columns[j].DataType == typeof(float) || dt.Columns[j].DataType == typeof(double))

{

colValues += string.Format("{0},", dt.Rows[i][j]);

}

else if (dt.Columns[j].DataType == typeof(DateTime))

{

colValues += string.Format("cast('{0}' as datetime),", dt.Rows[i][j]);

}

else if (dt.Columns[j].DataType == typeof(bool))

{

colValues += string.Format("{0},", dt.Rows[i][j].ToString());

}

else

colValues += string.Format("'{0}',", dt.Rows[i][j]);

}

cmd = string.Format(cmdmode, colValues.TrimEnd(','));

}

int ret = 0;

try

{

ret = DbHelperSQL.ExecuteSql(cmd);

}

catch (Exception e)

{

//写错误日志...

string strOuput = string.Format("向数据库中写数据失败,错误信息:{0},异常{1}\n", e.Message, e.InnerException);

//将信息写入到日志输出文件

DllComm.TP_WriteAppLogFileEx(DllComm.g_AppLogFileName, strOuput);

}

if (ret == -1)

{

return false;

}

return true;

}

///

/// 写入基础数据，并删除其中的重复的项目

///

///

/// 主键

/// 主键所在的列

///

public static bool WriteDataToDB(DataTable dt, string KeyName, int icol)

{

//删除数据库中的重复项目

string mKeyStr = "";

for (int i = 0; i < dt.Rows.Count; i++)

{

mKeyStr += "'" + dt.Rows[i][icol] + "',";

}

mKeyStr = mKeyStr.Trim(',');

string sqlStr = "Delete  from  " + dt.TableName + " where " + KeyName + " in (" + mKeyStr + ")";

DbHelperSQL.ExecuteSql(sqlStr);

//向数据库中写入新的数据

string tname = dt.TableName;

string colNames = "";

for (int i = 0; i < dt.Columns.Count; i++)

{

colNames += dt.Columns[i].ColumnName + ",";

}

colNames = colNames + "CreateDate ";

string cmd = "";

string colValues;

string cmdmode = string.Format("insert into {0}({1}) values({{0}});", tname, colNames);

for (int i = 0; i < dt.Rows.Count; i++)

{

colValues = "";

for (int j = 0; j < dt.Columns.Count; j++)

{

if (dt.Rows[i][j].GetType() == typeof(DBNull))

{

colValues += "NULL,";

continue;

}

if (dt.Columns[j].DataType == typeof(string))

{

colValues += string.Format("'{0}',", dt.Rows[i][j]);

}

else if (dt.Columns[j].DataType == typeof(int) || dt.Columns[j].DataType == typeof(float) || dt.Columns[j].DataType == typeof(double))

{

colValues += string.Format("{0},", dt.Rows[i][j]);

}

else if (dt.Columns[j].DataType == typeof(DateTime))

{

colValues += string.Format("cast('{0}' as datetime),", dt.Rows[i][j]);

}

else if (dt.Columns[j].DataType == typeof(bool))

{

colValues += string.Format("{0},", dt.Rows[i][j].ToString());

}

else

colValues += string.Format("'{0}',", dt.Rows[i][j]);

}

colValues += "getdate()";

cmd = string.Format(cmdmode, colValues);

int ret = 0;

try

{

ret = DbHelperSQL.ExecuteSql(cmd);

}

catch (Exception e)

{

//写错误日志...

string strOuput = string.Format("向数据库中写数据失败,错误信息:{0},异常{1}\n", e.Message, e.InnerException);

//将信息写入到日志输出文件

DllComm.TP_WriteAppLogFileEx(DllComm.g_AppLogFileName, strOuput);

}

if (ret == -1)

{

return false;

}

}

return true;

}