通用方法之根据oracle表名和数据datatable批量插入数据,效率更高

1.根据表名获取oracle表字段和字段类型
var dtColumn = GetDataSet(connectionSql, string.Format("select utc.column_name,utc.data_type,utc.data_length,utc.nullable,ucc.comments from user_tab_columns utc, user_col_comments ucc where utc.table_name = ucc.table_name and utc.column_name = ucc.column_name and utc.table_name = '{0}' order by column_id", tablename.ToUpper()), CommandType.Text);
2.根据字段名称和字段类型拼接插入语句
StringBuilder sql = new StringBuilder();
                sql.AppendFormat(" INSERT INTO {0} (@COLUMNS@) values( ", tablename);
                StringBuilder sbColumnNames = new StringBuilder();
                List<BulkInsertModel> listParam = new List<BulkInsertModel>();
                for (int i = 0; i < dtColumn.Rows.Count; i++)
                {
                    var arr = new List<string>(dt.Rows.Count);
                    DataRow dr = dtColumn.Rows[i];
                    string columnName = dr["COLUMN_NAME"].ToString();
                    sql.Append(":" + columnName + ",");
                    sbColumnNames.Append(columnName + ",");
                    listParam.Add(new BulkInsertModel { ColumnName = columnName, ColumnType = GetOracleOracleDbType(dr["data_type"].ToString()), ColumnValues = GetOracleValues(dt, columnName, dr["data_type"].ToString()) });
                }
                sql = sql.Remove(sql.Length - 1, 1);
                sbColumnNames = sbColumnNames.Remove(sbColumnNames.Length - 1, 1);
                sql.Append(")");
                sql.Replace("@COLUMNS@", sbColumnNames.ToString());
3.插入代码
OracleParameter 参数值是个数组,需要批量插入1000条数据的话 , 将所有数组单列放到数组中赋值给参数
                    using (OracleCommand cmd = new OracleCommand(sql.ToString(), Connection))
                    {
                            cmd.ArrayBindCount = dt.Rows.Count;
                            cmd.CommandType = CommandType.Text;
                            foreach (var item in listParam)
                            {
                                cmd.Parameters.Add(new OracleParameter(item.ColumnName, item.ColumnType)
                                {
                                    Direction = ParameterDirection.Input,
                                    Value = item.ColumnValues
                                });
                            }
                            int result = cmd.ExecuteNonQuery();
                       }
以下是全部代码:
其中需引用Oracle.ManagedDataAccess.Client命名空间. 可在NuGet中搜索Oracle.ManagedDataAccess 19.3.1 版本安装即可
        /// <summary>
        /// 批量插入
        /// </summary>
        /// <param name="connectionSql"></param>
        /// <param name="sql"></param>
        /// <param name="ct"></param>
        /// <param name="values"></param>
        /// <returns></returns>
        public static int ExecuteBulkInert(string connectionSql, string tablename, DataTable dt, out long ellapsedTime, out string msg)
        {
            msg = string.Empty;
            ellapsedTime = 0;
            if (dt == null || dt.Rows.Count == 0)
                return 0;
            Stopwatch watch = new Stopwatch();
            watch.Start();
            try
            {
                var dtColumn = GetDataSet(connectionSql, string.Format("select utc.column_name,utc.data_type,utc.data_length,utc.nullable,ucc.comments from user_tab_columns utc, user_col_comments ucc where utc.table_name = ucc.table_name and utc.column_name = ucc.column_name and utc.table_name = '{0}' order by column_id", tablename.ToUpper()), CommandType.Text);
                StringBuilder sql = new StringBuilder();
                sql.AppendFormat(" INSERT INTO {0} (@COLUMNS@) values( ", tablename);
                StringBuilder sbColumnNames = new StringBuilder();
                List<BulkInsertModel> listParam = new List<BulkInsertModel>();
                for (int i = 0; i < dtColumn.Rows.Count; i++)
                {
                    var arr = new List<string>(dt.Rows.Count);
                    DataRow dr = dtColumn.Rows[i];
                    string columnName = dr["COLUMN_NAME"].ToString();
                    sql.Append(":" + columnName + ",");
                    sbColumnNames.Append(columnName + ",");
                    listParam.Add(new BulkInsertModel { ColumnName = columnName, ColumnType = GetOracleOracleDbType(dr["data_type"].ToString()), ColumnValues = GetOracleValues(dt, columnName, dr["data_type"].ToString()) });
                }
                sql = sql.Remove(sql.Length - 1, 1);
                sbColumnNames = sbColumnNames.Remove(sbColumnNames.Length - 1, 1);
                sql.Append(")");
                sql.Replace("@COLUMNS@", sbColumnNames.ToString());
                using (OracleConnection Connection = new OracleConnection(connectionSql))
                {
                    Connection.Open();
                    using (OracleCommand cmd = new OracleCommand(sql.ToString(), Connection))
                    {
                        try
                        {
                            cmd.ArrayBindCount = dt.Rows.Count;
                            cmd.CommandType = CommandType.Text;
                            foreach (var item in listParam)
                            {
                                cmd.Parameters.Add(new OracleParameter(item.ColumnName, item.ColumnType)
                                {
                                    Direction = ParameterDirection.Input,
                                    Value = item.ColumnValues
                                });
                            }
                            int result = cmd.ExecuteNonQuery();
                            watch.Stop();
                            ellapsedTime = watch.ElapsedMilliseconds;
                            return result;
                        }
                        catch (Exception ex)
                        {
                            ellapsedTime = 0;
                            msg = ex.Message + ex.StackTrace;
                            return 0;
                        }
                        finally
                        {
                            cmd.Dispose();
                            Connection.Close();
                            Connection.Dispose();
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                ellapsedTime = 0;
                msg = ex.Message + ex.StackTrace;
                return 0;
            }
            finally
            {
            }
        }
        private static object GetOracleValues(DataTable dt, string columnName, string dbType)
        {
            switch (dbType.ToUpper())
            {
                case "VARCHAR2":
                case "NVARCHAR2":
                case "RAW":
                case "CLOB":
                case "NCLOB":
                case "BLOB":
                    List<string> list = new List<string>();
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        DataRow dr = dt.Rows[i];
                        list.Add(dr[columnName].ToString());
                    }
                    return list.ToArray();
                case "NUMBER":
                    List<int?> list2 = new List<int?>();
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        DataRow dr = dt.Rows[i];
                        var v = dr[columnName].ToString();
                        if (string.IsNullOrEmpty(v))
                            list2.Add(null);
                        else
                            list2.Add(Convert.ToInt32(v));
                    }
                    return list2.ToArray();
                case "DATE":
                    List<DateTime?> list3 = new List<DateTime?>();
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        DataRow dr = dt.Rows[i];
                        var v = dr[columnName].ToString();
                        if (string.IsNullOrEmpty(v))
                            list3.Add(null);
                        else
                            list3.Add(Convert.ToDateTime(v));
                    }
                    return list3.ToArray();
                default:
                    List<string> listDefault = new List<string>();
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        DataRow dr = dt.Rows[i];
                        listDefault.Add(dr[columnName].ToString());
                    }
                    return listDefault.ToArray();
            }
        }
        private static OracleDbType GetOracleOracleDbType(string dbType)
        {
            switch (dbType.ToUpper())
            {
                case "VARCHAR2":
                case "NVARCHAR2":
                    return OracleDbType.NVarchar2;
                case "RAW":
                    return OracleDbType.Raw;
                case "NUMBER":
                    return OracleDbType.Int64;
                case "CLOB":
                    return OracleDbType.Clob;
                case "DATE":
                    return OracleDbType.Date;
                case "NCLOB":
                    return OracleDbType.NClob;
                case "BLOB":
                    return OracleDbType.Blob;
                default:
                    return OracleDbType.NVarchar2;
            }
        }
   

转载请说明出处:第六感博客 原文链接:

相关阅读:

使用SQL语句导出Sqlserver数据库的Excel形式的数据字典

插入Oracle数据库报错 ORA-24816: 在实际的 LONG 或 LOB 列之后提供了扩展的非 LONG 绑定数据

mysql 为了导出Excel后不显示科学计数 数据列前加单引号 Excel中会显示成字符串

SQLSERVER2008R2下载地址