mysql dataset更新到数据库_C#： MySqlHelper 操作Mysql数据库，将dataset内的数据存入Mysql...

public class MySqlDbHelper

{

#region 私有变量

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

private string connectionString = " Database='sensetechtest';Data Source='127.0.0.1';User Id='root';Password='1234';pooling=true";

private string providerName = " Database='sensetechtest';Data Source='127.0.0.1';User Id='root';Password='1234';pooling=true";

#endregion

#region 构造函数

///

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

///

public MySqlDbHelper()

{

//this.connectionString = ConfigurationManager.ConnectionStrings[defaultConfigKeyName].ConnectionString;

//this.providerName = ConfigurationManager.ConnectionStrings[defaultConfigKeyName].ProviderName;

}

///

/// DbHelper构造函数

///

/// 连接字符串名

public MySqlDbHelper(string keyName)

{

this.connectionString = ConfigurationManager.ConnectionStrings[keyName].ConnectionString;

this.providerName = ConfigurationManager.ConnectionStrings[keyName].ProviderName;

}

#endregion

public int ExecuteNonQuery(string sql, params MySqlParameter[] parameters)

{

int res = 0;

using (MySqlConnection con = new MySqlConnection(connectionString))

{

con.Open();

using (MySqlCommand cmd = new MySqlCommand(sql, con))

{

foreach (MySqlParameter parameter in parameters)

{

cmd.Parameters.Add(parameter);

}

try

{

res = cmd.ExecuteNonQuery();

}

catch

{

res = -1;

}

}

}

return res;

}

public object GetExeScalar(string sql, params MySqlParameter[] parameters)

{

object res = null;

using (MySqlConnection con = new MySqlConnection(connectionString))

{

con.Open();

using (MySqlCommand cmd = new MySqlCommand(sql, con))

{

foreach (MySqlParameter parameter in parameters)

{

cmd.Parameters.Add(parameter);

}

res = cmd.ExecuteScalar();

}

}

return res;

}

public DataTable GetDataTable(string sql, params MySqlParameter[] parameters)

{

DataSet dataset = new DataSet();

using (MySqlConnection con = new MySqlConnection(connectionString))

{

con.Open();

using (MySqlCommand cmd = new MySqlCommand(sql, con))

{

foreach (MySqlParameter parameter in parameters)

{

cmd.Parameters.Add(parameter);

}

MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);

adapter.Fill(dataset);

}

}

return dataset.Tables[0];

}

///

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

///

///

///

public string InsertByDataTable(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 = new StringBuilder();

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 = new MySqlConnection(connectionString))

{

con.Open();

using (MySqlCommand cmd = new MySqlCommand(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 = "恭喜添加成功!";

}

return result;

}

}