mysql datatable_MySQL数据库工具类之——DataTable批量加入MySQL数据库(Net版)

usingMySql.Data.MySqlClient;usingSystem;usingSystem.Collections.Generic;usingSystem.Configuration;usingSystem.Data;usingSystem.Linq;usingSystem.Web;usingSystem.Text;public classMySqlDbHelper

{#region 私有变量

private const string defaultConfigKeyName = "MySqlConnection";//连接字符串 Database='数据库';Data Source='IP地址';User Id='sa';Password='sa';pooling=true

private stringconnectionString;private stringproviderName;#endregion

#region 构造函数

///

///默认构造函数(DbHelper)///

publicMySqlDbHelper()

{this.connectionString =ConfigurationManager.ConnectionStrings[defaultConfigKeyName].ConnectionString;this.providerName =ConfigurationManager.ConnectionStrings[defaultConfigKeyName].ProviderName;

}///

///DbHelper构造函数///

/// 连接字符串名

public MySqlDbHelper(stringkeyName)

{this.connectionString =ConfigurationManager.ConnectionStrings[keyName].ConnectionString;this.providerName =ConfigurationManager.ConnectionStrings[keyName].ProviderName;

}#endregion

public int ExecuteNonQuery(string sql, paramsMySqlParameter[] parameters)

{int res = 0;using (MySqlConnection con = newMySqlConnection(connectionString))

{

con.Open();using (MySqlCommand cmd = newMySqlCommand(sql, con))

{foreach (MySqlParameter parameter inparameters)

{

cmd.Parameters.Add(parameter);

}try{

res=cmd.ExecuteNonQuery();

}catch{

res= -1;

}

}

}returnres;

}public object GetExeScalar(string sql, paramsMySqlParameter[] parameters)

{object res = null;using (MySqlConnection con = newMySqlConnection(connectionString))

{

con.Open();using (MySqlCommand cmd = newMySqlCommand(sql, con))

{foreach (MySqlParameter parameter inparameters)

{

cmd.Parameters.Add(parameter);

}

res=cmd.ExecuteScalar();

}

}returnres;

}public DataTable GetDataTable(string sql, paramsMySqlParameter[] parameters)

{

DataSet dataset= newDataSet();using (MySqlConnection con = newMySqlConnection(connectionString))

{

con.Open();using (MySqlCommand cmd = newMySqlCommand(sql, con))

{foreach (MySqlParameter parameter inparameters)

{

cmd.Parameters.Add(parameter);

}

MySqlDataAdapter adapter= newMySqlDataAdapter(cmd);

adapter.Fill(dataset);

}

}return dataset.Tables[0];

}///

///DataTable批量加入MYSQL数据库///

///

///

public stringInsertByDataTable(DataTable dataTable)

{string result = string.Empty;if (null == dataTable || dataTable.Rows.Count <= 0)

{return "添加失败！DataTable暂无数据！";

}if (string.IsNullOrEmpty(dataTable.TableName))

{return "添加失败！请先设置DataTable的名称！";

}//构建INSERT语句

StringBuilder sb = newStringBuilder();

sb.Append("INSERT INTO" + dataTable.TableName + "(");for (int i = 0; i < dataTable.Columns.Count; i++)

{

sb.Append(dataTable.Columns[i].ColumnName+ ",");

}

sb.Remove(sb.ToString().LastIndexOf(','), 1);

sb.Append(") VALUES");for (int i = 0; i < dataTable.Rows.Count; i++)

{

sb.Append("(");for (int j = 0; j < dataTable.Columns.Count; j++)

{

sb.Append("'" + dataTable.Rows[i][j] + "',");

}

sb.Remove(sb.ToString().LastIndexOf(','), 1);

sb.Append("),");

}

sb.Remove(sb.ToString().LastIndexOf(','), 1);

sb.Append(";");int res = -1;using (MySqlConnection con = newMySqlConnection(connectionString))

{

con.Open();using (MySqlCommand cmd = newMySqlCommand(sb.ToString(), con))

{try{

res=cmd.ExecuteNonQuery();

}catch(Exception ex)

{

res= -1;//Unknown column 'names' in 'field list'

result = "操作失败！" + ex.Message.Replace("Unknown column", "未知列").Replace("in 'field list'","存在字段集合中！");

}

}

}if (res > 0)

{

result= "恭喜添加成功!";

}returnresult;

}

}