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

墨蓝 2023-10-17 20:36 10阅读 0赞

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