SQL 多筆Insert新增資料

SQL 多筆Insert新增資料
使用簡易方法=>傳入DataTable後自動組合成SQL指令後寫入DataBase

//1.取得空的DataTable

DataTable dt = GetEmptyTable("Table1");

//2.資料放入DataTable

DataRow dr = dt.NewRow();
dr["id"] = "1";
dr["no"] = "A001";
dt.Rows.Add(dr);
 

//3.DataTable存入資料庫

MultiInsert(dt, "Table1");
 

//Function

        /// <summary>
        /// 多筆新增資料
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public static int MultiInsert(DataTable dt, string tableName)
        {
            try
            {
                int count = dt.Rows.Count;
                string ConnectionString = ConfigurationManager.ConnectionStrings[BPM.BPM_ConnStr].ToString();
                using (SqlConnection conn = new SqlConnection(ConnectionString))
                {
                    conn.Open();
                    //組合SQL  Insert String
                    string mysql = InsertString_Collect(tableName, dt);
                    SqlTransaction mytrans = conn.BeginTransaction();
                    //逐筆檢驗 逐筆新增
                    foreach (DataRow dr in dt.Rows)
                    {
                        //檢驗比對資料內容是否正確
                        //如c2 ==aa then c4=false...等
                        if (1 == 1)
                        {
                            using (SqlCommand cmd = new SqlCommand(mysql, conn))
                            {
                                cmd.Transaction = mytrans;
                                foreach (DataColumn col in dt.Columns)
                                {
                                    cmd.Parameters.AddWithValue("@" + col.ColumnName, dr[col.ColumnName]);
                                }
                                cmd.ExecuteNonQuery();
                            }
                        }
                    }
                    mytrans.Commit();
                    return count;
                }
            }
            catch (Exception)
            {
                throw;
            }
        }
 
        /// <summary>
        /// 組出SQL Insert String
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static string InsertString_Collect(string tableName, DataTable dt)
        {
            try
            {
                string sql = "INSERT INTO " + tableName + "({p}) VALUES ({v})";
                string p = string.Empty;
                string v = string.Empty;
                foreach (DataColumn col in dt.Columns)
                {
                    p += col.ColumnName.ToString() + ",";
                    v += "@" + col.ColumnName.ToString() + ",";
                }
                p = p.Substring(0, int.Parse(p.Length.ToString()) - 1);
                v = v.Substring(0, int.Parse(v.Length.ToString()) - 1);
                sql = sql.Replace("{p}", p);
                sql = sql.Replace("{v}", v);
                return sql;
            }
            catch (Exception)
            {
                throw;
            }
        }
        /// <summary>
        /// 取得空的Table
        /// </summary>
        /// <param name="tableName">Database Table Name</param>
        /// <returns>DataTable</returns>
        public static DataTable GetEmptyTable(string tableName)
        {
            DataTable dt = new DataTable();
            try
            {
                SqlConnection cn = new SqlConnection();
                SqlCommand cmd = new SqlCommand();
                SqlDataAdapter da = new SqlDataAdapter();
                DataSet ds;
                string sql = string.Empty;
                string sqlwhere = string.Empty;
                string sqlsort = string.Empty;
                #region Execute SQL
                cn.ConnectionString = ConfigurationManager.ConnectionStrings[BPM.BPM_ConnStr].ToString();
                cmd.Connection = cn;
                cmd.CommandText = "SELECT * FROM " + tableName + " WHERE 1=0";
                cn.Open();
                da.SelectCommand = cmd;
                sql = da.SelectCommand.CommandText;
                ds = new DataSet();
                da.Fill(ds, "dt");
                dt = ds.Tables["dt"];
                cn.Close();
                #endregion Execute SQL
            }
            catch (Exception)
            {
                //throw;
            }
            return dt;
        }