还在为拼接sql烦恼吗，提供一种将DataTable的数据插入到数据库的通用方法

在开发过程中经常遇到一些字段很多很多的表，在写入数据时如果采用拼接SQL真的很难受，自创一种通用的方法，主要原理是通过DataTable的列名与字段表名进行一致性匹配，通过USER_TAB_COLUMNS表获取到字段的字段类型信息生成insert 字符串，这里取的字段是DataTable和表中都有的，这样表后期如果增加字段也不会产生影响，希望能够为大家提供参考。

   /// <summary>
        /// 将数据插入到数据库表的通用方法，针对DataTable及数据表中都存在的字段进行操作
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="ConnStr">连接字符串</param>
        /// <param name="TableName">数据库中的表名</param>
        public static void InsertDataToDB(DataTable dt, string ConnStr, string TableName)
        {
            try
            {
                if (dt == null || dt.Rows.Count < 1) { return; }
                //获取数据表中及dt列名共有的字段
                string colStr = string.Empty;
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    colStr += dt.Columns[i].ColumnName + ",";
                }
               colStr= "'"+colStr.TrimEnd(',').Replace(",","','").ToUpper()+"'";
                string sql = string.Format("SELECT COLUMN_NAME,DATA_TYPE  FROM USER_TAB_COLUMNS WHERE TABLE_NAME = '{0}' AND COLUMN_NAME IN ({1})", TableName.ToUpper(), colStr);
                DataTable dt_columns = OracleHelper.ExecuteTable(ConnStr, CommandType.Text, sql);
                if (dt_columns == null || dt_columns.Rows.Count < 1)
                {
                    throw new Exception("数据库无此表");
                }
                StringBuilder sqlStart_SB = new StringBuilder(string.Format("INSERT INTO {0} (", TableName));
                for (int i = 0; i < dt_columns.Rows.Count; i++)
                {
                    sqlStart_SB.Append(dt_columns.Rows[i]["COLUMN_NAME"]);
                    sqlStart_SB.Append(",");
                }
                sqlStart_SB.Remove(sqlStart_SB.Length - 1, 1).Append(") VALUES (");
                string sqlStart_Str = sqlStart_SB.ToString();
                string[] sqlList = new string[dt.Rows.Count];
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    StringBuilder sql_valueSB = new StringBuilder(sqlStart_Str);
                    for (int j = 0; j < dt_columns.Rows.Count; j++)
                    {
                        sql_valueSB.Append(FormatValueExpression(dt.Rows[i][dt_columns.Rows[j]["COLUMN_NAME"].ToString()].ToString(), dt_columns.Rows[j]["DATA_TYPE"].ToString()));
                        sql_valueSB.Append(",");
                    }
                    sql_valueSB.Remove(sql_valueSB.Length - 1, 1).Append(")");
                    sqlList[i] = sql_valueSB.ToString();
                }
                string Msg = "";
                if (!OracleHelper.ExecuteNonQuery_List(ConnStr, sqlList, ref Msg))
                {
                    throw new Exception(Msg);
                }
            }
            catch
            {
                throw;
            }
        }

        /// <summary>
        /// 格式化sql语句的字符串
        /// </summary>
        /// <param name="value">字段值</param>
        /// <param name="data_type">字段类型</param>
        /// <returns></returns>
        private static string FormatValueExpression(string value, string data_type)
        {
            if (string.IsNullOrEmpty(value))
            {
                return "NULL";
            }

            switch (data_type)
            {
                case "VARCHAR2":
                    return string.Format("'{0}'", value.Replace("'", "''"));
                case "NUMBER":
                    return value;
                case "DATE":
                    return string.Format(" to_date('{0}','yyyy-mm-dd hh24:mi:ss')", value);
                default:
                    throw new Exception("暂不支持" + data_type + "类型值的格式化");
            }
        }