將DataTable中的資料匯入到資料庫中

        上次在根據excel的檔案的路徑提取其中表的資料到DataSet中 一文中介紹了將Excel檔案中的資料讀取到DataSet中的方法，今天我來介紹下我曾經在項目中用到的一個將DataTable中的資料匯入到資料庫中的方法和將DataTable中的資料寫入資料庫並過濾掉重複資料的方法。代碼可以給大家作為Demo看下，如果需要應用的話，可能需要進行

小小的改動即可。

/// <summary>
        /// 將DataTable中資料寫入資料庫中
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static bool WriteDataToDB(DataTable dt)
        {
            if (dt==null || dt.Rows.Count == 0)
            {
                return true;
            }
            string tname = dt.TableName;
            string colNames = "";
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                colNames += dt.Columns[i].ColumnName + ",";
            }
            colNames = colNames.TrimEnd(',');
            string cmd = "";
            string colValues;
            string cmdmode = string.Format("insert into {0}({1}) values({{0}});", tname, colNames);
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                colValues = "";
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    if (dt.Rows[i][j].GetType() == typeof(DBNull))
                    {
                        colValues += "NULL,";
                        continue;
                    }
                    if (dt.Columns[j].DataType == typeof(string))
                        colValues += string.Format("'{0}',", dt.Rows[i][j]);
                    else if (dt.Columns[j].DataType == typeof(int) || dt.Columns[j].DataType == typeof(float) || dt.Columns[j].DataType == typeof(double))
                    {
                        colValues += string.Format("{0},", dt.Rows[i][j]);
                    }
                    else if (dt.Columns[j].DataType == typeof(DateTime))
                    {
                        colValues += string.Format("cast('{0}' as datetime),", dt.Rows[i][j]);
                    }
                    else if (dt.Columns[j].DataType == typeof(bool))
                    {
                        colValues += string.Format("{0},", dt.Rows[i][j].ToString());
                    }
                    else
                        colValues += string.Format("'{0}',", dt.Rows[i][j]);
                }
                cmd = string.Format(cmdmode, colValues.TrimEnd(','));
            }
            int ret = 0;
            try
            {
                ret = DbHelperSQL.ExecuteSql(cmd);
            }
            catch (Exception e)
            {
                //寫錯誤記錄檔...
                string strOuput = string.Format("向資料庫中寫資料失敗,錯誤資訊:{0},異常{1}\n", e.Message, e.InnerException);
                //將資訊寫入到日誌輸出檔案
                DllComm.TP_WriteAppLogFileEx(DllComm.g_AppLogFileName, strOuput);
               
            }
            if (ret == -1)
            {
                return false;
            }
            return true;
           
        }

        /// <summary>
        /// 寫入基礎資料，並刪除其中的重複的項目
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="KeyName">主鍵</param>
        /// <param name="icol">主鍵所在的列</param>
        /// <returns></returns>
        public static bool WriteDataToDB(DataTable dt, string KeyName, int icol)
        {
            //刪除資料庫中的重複項目
            string mKeyStr = "";
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                mKeyStr += "'" + dt.Rows[i][icol] + "',";
            }
            mKeyStr = mKeyStr.Trim(',');
            string sqlStr = "Delete  from  " + dt.TableName + " where " + KeyName + " in (" + mKeyStr + ")";
            DbHelperSQL.ExecuteSql(sqlStr);

            //向資料庫中寫入新的資料
            string tname = dt.TableName;
            string colNames = "";
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                colNames += dt.Columns[i].ColumnName + ",";
            }
            colNames = colNames + "CreateDate ";

            string cmd = "";
            string colValues;
            string cmdmode = string.Format("insert into {0}({1}) values({{0}});", tname, colNames);
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                colValues = "";
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    if (dt.Rows[i][j].GetType() == typeof(DBNull))
                    {
                        colValues += "NULL,";
                        continue;
                    }
                    if (dt.Columns[j].DataType == typeof(string))
                    {
                        colValues += string.Format("'{0}',", dt.Rows[i][j]);
                    }
                    else if (dt.Columns[j].DataType == typeof(int) || dt.Columns[j].DataType == typeof(float) || dt.Columns[j].DataType == typeof(double))
                    {
                        colValues += string.Format("{0},", dt.Rows[i][j]);
                    }
                    else if (dt.Columns[j].DataType == typeof(DateTime))
                    {
                        colValues += string.Format("cast('{0}' as datetime),", dt.Rows[i][j]);
                    }
                    else if (dt.Columns[j].DataType == typeof(bool))
                    {
                        colValues += string.Format("{0},", dt.Rows[i][j].ToString());
                    }
                    else
                        colValues += string.Format("'{0}',", dt.Rows[i][j]);
                }
                colValues += "getdate()";
                cmd = string.Format(cmdmode, colValues);

                int ret = 0;
                try
                {
                    ret = DbHelperSQL.ExecuteSql(cmd);
                }
                catch (Exception e)
                {
                    //寫錯誤記錄檔...
                    string strOuput = string.Format("向資料庫中寫資料失敗,錯誤資訊:{0},異常{1}\n", e.Message, e.InnerException);
                    //將資訊寫入到日誌輸出檔案
                    DllComm.TP_WriteAppLogFileEx(DllComm.g_AppLogFileName, strOuput);
               
                }
                if (ret == -1)
                {
                    return false;
                }
            }
            return true;
        }