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

}