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; } }
关注下面二维码,订阅更多精彩内容。

【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· PostgreSQL 和 SQL Server 在统计信息维护中的关键差异
· C++代码改造为UTF-8编码问题的总结
· DeepSeek 解答了困扰我五年的技术问题
· 为什么说在企业级应用开发中,后端往往是效率杀手?
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 推荐几款开源且免费的 .NET MAUI 组件库
· 实操Deepseek接入个人知识库
· 易语言 —— 开山篇
· Trae初体验