MySQL数据库工具类之——DataTable批量加入MySQL数据库(Net版)
MySQL数据库工具类之——DataTable批量加入数据库(Net版),MySqlDbHelper通用类希望能对大家有用,代码如下:
using MySql.Data.MySqlClient; using System; using System.Collections.Generic; using System.Configuration; using System.Data; using System.Linq; using System.Web; using System.Text; public class MySqlDbHelper { #region 私有变量 private const string defaultConfigKeyName = "MySqlConnection";//连接字符串 Database='数据库';Data Source='IP地址';User Id='sa';Password='sa';pooling=true private string connectionString; private string providerName; #endregion #region 构造函数 /// <summary> /// 默认构造函数(DbHelper) /// </summary> public MySqlDbHelper() { this.connectionString = ConfigurationManager.ConnectionStrings[defaultConfigKeyName].ConnectionString; this.providerName = ConfigurationManager.ConnectionStrings[defaultConfigKeyName].ProviderName; } /// <summary> /// DbHelper构造函数 /// </summary> /// <param name="keyName">连接字符串名</param> 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]; } /// <summary> /// DataTable批量加入MYSQL数据库 /// </summary> /// <param name="dataTable"></param> /// <returns></returns> 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; } }
关注下面二维码,订阅更多精彩内容。
![](https://images.cnblogs.com/cnblogs_com/vipstone/848916/o_211225130402_gognzhonghao.jpg)