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;
}
}