devbox
运维做开发
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

c# mysql、sqlserver、Oracle、pg批量插入_sql批量插入

作者：运维做开发 | 2024-07-23 19:37:28

赞

踩

sql批量插入

1、c#对多种数据库批量插入封装，方便调用；

MySql社区驱动MySqlConnector提供的批量插入方式是SqlBulkCopy，基于MySql自身的文件上传机制进行批量插入，参数为一个dataTable对象，原生的批量插入代码如下,计时方式与SqlServer相同,同时，MySql的连接字符串里要添加";AllowLoadLocalInfile=true"，即连接字符串的形式应该是"Server= ;Database=;User ID=;Password=;AllowLoadLocalInfile=true"，同时在MySql数据库上执行"set global local_infile=1"开启批量上传

  1.  /// <summary>
  2.         /// MySqlBulkInsert
  3.         /// </summary>
  4.         /// <typeparam name="T">插入的表对应的model</typeparam>
  5.         /// <param name="list">插入的数据</param>
  6.         /// <param name="tableName">泛型中无法获取到表，直接传表名称</param>
  7.         /// <returns></returns>
  8.         private bool MySqlBulkInsert<T>(IEnumerable<T> list, string tableName = "") where T : class
  9.         {
  10.             var connectionString = MSEApplication.GetConfigValue("ConnectionStrings:BulkConn");
  11.             using (var conn = new MySqlConnection(connectionString))
  12.             {
  13.                 try
  14.                 {
  15.                     var tableEntity = DataCommonUtil.GetTableEntity<T>();
  16.                     if (!string.IsNullOrWhiteSpace(tableName))
  17.                         tableEntity.TableName = tableName;
  18.                     MySqlBulkCopy sqlBulkCopy = new MySqlBulkCopy(conn, null);
  19.                     sqlBulkCopy.DestinationTableName = tableEntity.TableName;
  20.                     var dataTable = list.ToDataTable();
  21.  
  22.                     int i = 0;
  23.                     foreach (DataColumn dataColumn in dataTable.Columns)
  24.                     {
  25.                         sqlBulkCopy.ColumnMappings.Add(new MySqlBulkCopyColumnMapping(i, dataColumn.ColumnName));
  26.                         i = i + 1;
  27.                     }
  28.  
  29.                     conn.Open();
  30.                     var bulkCopyResult = sqlBulkCopy.WriteToServer(dataTable);
  31.                     if (bulkCopyResult.RowsInserted == list.Count())
  32.                         return true;
  33.                     else
  34.                         return false;
  35.                 }
  36.                 catch (Exception ex)
  37.                 {
  38.                     conn.Close();
  39.                     conn.Dispose();
  40.                     return false;
  41.                 }
  42.                 finally
  43.                 {
  44.                     conn.Close();
  45.                     conn.Dispose();
  46.                 }
  47.             }
  48.         }

SqlServer官方提供的批量插入方式是SqlBulkCopy。

  1.  /// <summary>
  2.         /// SqlServerBulkInsert
  3.         /// </summary>
  4.         /// <typeparam name="T">插入的表对应的model</typeparam>
  5.         /// <param name="list">插入的数据</param>
  6.         /// <param name="tableName">泛型中无法获取到表，直接传表名称</param>
  7.         /// <returns></returns>
  8.         private bool SqlServerBulkInsert<T>(IEnumerable<T> list, string tableName = "") where T : class
  9.         {
  10.             var connectionString = MSEApplication.GetConfigValue("ConnectionStrings:BulkConn");
  11.             using (var conn = new SqlConnection(connectionString))
  12.             {
  13.                 try
  14.                 {
  15.                     var tableEntity = DataCommonUtil.GetTableEntity<T>();
  16.                     if (!string.IsNullOrWhiteSpace(tableName))
  17.                         tableEntity.TableName = tableName;
  18.                     SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.KeepIdentity, null);
  19.                     sqlBulkCopy.DestinationTableName = tableEntity.TableName;
  20.                     sqlBulkCopy.BatchSize = 20000;
  21.                     var dataTable = list.ToDataTable();
  22.  
  23.                     foreach (DataColumn dataColumn in dataTable.Columns)
  24.                     {
  25.                         sqlBulkCopy.ColumnMappings.Add(dataColumn.ColumnName, dataColumn.ColumnName);
  26.                     }
  27.                     conn.Open();
  28.                     sqlBulkCopy.WriteToServer(dataTable);
  29.                     return true;
  30.                 }
  31.                 catch (Exception ex)
  32.                 {
  33.                     conn.Close();
  34.                     conn.Dispose();
  35.                     return false;
  36.                 }
  37.                 finally
  38.                 {
  39.                     conn.Close();
  40.                     conn.Dispose();
  41.                 }
  42.             }
  43.         }
  1. /// <summary>
  2.         /// OracleBulkInsert
  3.         /// </summary>
  4.         /// <typeparam name="T"></typeparam>
  5.         /// <param name="list"></param>
  6.         /// <param name="tableName">泛型中无法获取到表，直接传表名称</param>
  7.         /// <returns></returns>
  8.         private bool OracleBulkInsert<T>(IEnumerable<T> list, string tableName = "") where T : class
  9.         {
  10.             var connectionString = MSEApplication.GetConfigValue("ConnectionStrings:BulkConn");
  11.             using (var conn = new OracleConnection(connectionString))
  12.             {
  13.                 using (var adapter = new OracleDataAdapter())
  14.                 {
  15.                     var tableEntity = DataCommonUtil.GetTableEntity<T>();
  16.                     if (!string.IsNullOrWhiteSpace(tableName))
  17.                         tableEntity.TableName = tableName;
  18.                     var dataTable = list.ToDataTable();
  19.                     adapter.InsertCommand = new OracleCommand();
  20.                     adapter.InsertCommand.CommandText = $"INSERT INTO {tableEntity.TableName} ({string.Join(",", dataTable.Columns.Cast<DataColumn>().Select(c => c.ColumnName))})" +
  21.                         $" VALUES ({string.Join(",", dataTable.Columns.Cast<DataColumn>().Select(c => ":" + c.ColumnName))})";
  22.                     adapter.InsertCommand.Connection = conn;
  23.                     foreach (DataColumn column in dataTable.Columns)
  24.                     {
  25.                         adapter.InsertCommand.Parameters.Add($":{column.ColumnName}", column.DataType.ConvertToOracleDbType(), column.MaxLength, column.ColumnName);
  26.                     }
  27.                     adapter.UpdateBatchSize = 20000;
  28.                     conn.Open();
  29.                     using (var transaction = conn.BeginTransaction())
  30.                     {
  31.                         try
  32.                         {
  33.                             adapter.InsertCommand.Transaction = transaction;
  34.                             adapter.Update(dataTable);
  35.                             transaction.Commit();
  36.                             return true;
  37.                         }
  38.                         catch (Exception ex)
  39.                         {
  40.                             transaction.Rollback();
  41.                             conn.Close();
  42.                             conn.Dispose();
  43.                             return false;
  44.                         }
  45.                         finally
  46.                         {
  47.                             conn.Close();
  48.                             conn.Dispose();
  49.                         }
  50.                     }
  51.                 }
  52.             }
  53.         }

  1.  /// <summary>
  2.         /// PostgresqlBulkInsert
  3.         /// </summary>
  4.         /// <typeparam name="T">插入的表对应的model</typeparam>
  5.         /// <param name="list">插入的数据</param>
  6.         /// <param name="tableName">泛型中无法获取到表，直接传表名称</param>
  7.         /// <returns></returns>
  8.  
  9.         private bool PostgresqlBulkInsert<T>(IEnumerable<T> list, string tableName = "") where T : class
  10.         {
  11.             var connectionString = MSEApplication.GetConfigValue("ConnectionStrings:BulkConn");
  12.             using (var conn = new NpgsqlConnection(connectionString))
  13.             {
  14.                 conn.Open();
  15.                 try
  16.                 {
  17.                     var tableEntity = DataCommonUtil.GetTableEntity<T>();
  18.                     if (!string.IsNullOrWhiteSpace(tableName))
  19.                         tableEntity.TableName = tableName;
  20.                     PropertyInfo[] properties = typeof(T).GetProperties();
  21.                     string[] propertyNames = properties.Select(pro => pro.Name).ToArray();
  22.                     string joinedPropertyNames = string.Join(", ", propertyNames);
  23.                     //缓存属性值访问器
  24.                     Func<T, object>[] propertyValue = properties.Select(pro => (Func<T, object>)pro.GetValue).ToArray();
  25.                     using (var writer = conn.BeginBinaryImport($"COPY {tableEntity.TableName} ({joinedPropertyNames}) FROM STDIN BINARY"))
  26.                     {
  27.                         foreach (var item in list)
  28.                         {
  29.                             writer.StartRow();
  30.                             foreach (var valueAccessor in propertyValue)
  31.                             {
  32.                                 var fieldValue = valueAccessor(item);
  33.                                 // 处理 null 值
  34.                                 fieldValue = fieldValue ?? DBNull.Value;
  35.                                 if (fieldValue == null)
  36.                                 {
  37.                                     writer.WriteNull();
  38.                                 }
  39.                                 else
  40.                                 {
  41.                                     writer.Write(fieldValue);
  42.                                 }
  43.                             }
  44.                         }
  45.                         writer.Complete();
  46.                     }
  47.                     return true;
  48.                 }
  49.                 catch (Exception ex)
  50.                 {
  51.                     conn.Close();
  52.                     conn.Dispose();
  53.                     return false;
  54.                 }
  55.                 finally
  56.                 {
  57.                     conn.Close();
  58.                     conn.Dispose();
  59.                 }
  60.             }
  61.         }
  1.  public static partial class BulkUtil
  2.     {
  3.  
  4.         /// <summary>
  5.         /// 判断一个类型是否为可空类型
  6.         /// </summary>
  7.         /// <typeparam name="T"></typeparam>
  8.         /// <param name="o"></param>
  9.         /// <returns></returns>
  10.         public static bool IsNullable<T>(this T o)
  11.         {
  12.             var type = typeof(T);
  13.             return type.IsNullable();
  14.         }
  15.  
  16.         /// <summary>
  17.         /// 判断一个类型是否为可空类型
  18.         /// </summary>
  19.         /// <typeparam name="T"></typeparam>
  20.         /// <param name="type"></param>
  21.         /// <returns></returns>
  22.         public static bool IsNullable(this Type type)
  23.         {
  24.             return Nullable.GetUnderlyingType(type) != null;
  25.         }
  26.         /// <summary>
  27.         /// 判断type是否为集合类型
  28.         /// </summary>
  29.         /// <param name="type"></param>
  30.         /// <returns></returns>
  31.         public static bool IsCollection(this Type type)
  32.         {
  33.             return type.GetInterfaces().Any(it => it == typeof(ICollection));
  34.         }
  35.  
  36.         /// <summary>
  37.         /// 判断type是否为迭代器类型
  38.         /// </summary>
  39.         /// <param name="type"></param>
  40.         /// <returns></returns>
  41.         public static bool IsEnumerable(this Type type)
  42.         {
  43.             return type.GetInterfaces().Any(it => it == typeof(IEnumerable));
  44.         }
  45.  
  46.         /// <summary>
  47.         /// 判断type是否为查询器类型
  48.         /// </summary>
  49.         /// <param name="type"></param>
  50.         /// <returns></returns>
  51.         public static bool IsQueryable(this Type type)
  52.         {
  53.             return type.GetInterfaces().Any(it => it == typeof(IQueryable));
  54.         }
  55.  
  56.         /// <summary>
  57.         /// 判断type是否为字符串类型
  58.         /// </summary>
  59.         /// <param name="type"></param>
  60.         /// <returns></returns>
  61.         public static bool IsString(this Type type)
  62.         {
  63.             return type == typeof(string);
  64.         }
  65.  
  66.         /// <summary>
  67.         /// 判断type是否为支持async的类型
  68.         /// </summary>
  69.         /// <param name="type"></param>
  70.         /// <returns></returns>
  71.         public static bool IsAsyncType(this Type type)
  72.         {
  73.             var awaiter = type.GetMethod("GetAwaiter");
  74.             if (awaiter == null)
  75.                 return false;
  76.             var retType = awaiter.ReturnType;
  77.             //.NET Core 1.1及以下版本中没有 GetInterface 方法，为了兼容性使用 GetInterfaces
  78.             if (retType.GetInterfaces().All(i => i.Name != "INotifyCompletion"))
  79.                 return false;
  80.             if (retType.GetProperty("IsCompleted") == null)
  81.                 return false;
  82.             if (retType.GetMethod("GetResult") == null)
  83.                 return false;
  84.  
  85.             return true;
  86.         }
  87.  
  88.         /// <summary>
  89.         /// 根据type 生成实例类型
  90.         /// </summary>
  91.         /// <param name="type"></param>
  92.         /// <param name="args"></param>
  93.         /// <returns></returns>
  94.         public static object CreateInstance(this Type type, object[] args)
  95.         {
  96.             if (args == null || args.Length == 0)
  97.             {
  98.                 return Activator.CreateInstance(type);
  99.             }
  100.  
  101.             return Activator.CreateInstance(type, args: args);
  102.         }
  103.  
  104.         /// <summary>
  105.         /// 获得基础类型，获得比如被Task，ICollection<>，IEnumable<>,IQueryable<>等包裹的类型
  106.         /// </summary>
  107.         /// <param name="type"></param>
  108.         /// <returns></returns>
  109.         public static Type GetUnderlyingType(this Type type)
  110.         {
  111.             var resultTmp = type.IsAsyncType() ? type.GenericTypeArguments.First() : type;
  112.             var resultTmp2 = resultTmp.IsGenericType
  113.                 ? resultTmp.GetGenericArguments().First()
  114.                 : resultTmp;
  115.  
  116.             return resultTmp2;
  117.         }
  118.  
  119.         /// <summary>
  120.         /// 根据类名获得Type实例
  121.         /// </summary>
  122.         /// <param name="typeName"></param>
  123.         /// <returns></returns>
  124.         public static Type LoadTypeByName(string typeName)
  125.         {
  126.             if (typeName.IsNullOrWhiteSpace()) throw new Exception("typeName must be not empty");
  127.             Type t = Type.GetType(typeName);
  128.             if (t != null) System.Runtime.CompilerServices.RuntimeHelpers.RunClassConstructor(t.TypeHandle);
  129.             return t;
  130.         }
  131.  
  132.         /// <summary>
  133.         /// 获取某个类的默认值
  134.         /// </summary>
  135.         /// <param name="type"></param>
  136.         /// <returns></returns>
  137.         public static object GetDefaultValue(this Type type)
  138.         {
  139.             return type.IsValueType ? Activator.CreateInstance(type) : null;
  140.         }
  141.  
  142.         /// <summary>
  143.         /// 判断是否为字典类型
  144.         /// </summary>
  145.         /// <param name="type"></param>
  146.         /// <returns></returns>
  147.         public static bool IsDictionary(this Type type)
  148.         {
  149.             return type.GetInterfaces().Any(it => it == typeof(IDictionary));
  150.         }
  151.  
  152.         /// <summary>
  153.         /// 通过表达式树获取实体类的属性值
  154.         /// </summary>
  155.         /// <typeparam name="T"></typeparam>
  156.         /// <typeparam name="TResult"></typeparam>
  157.         /// <param name="model"></param>
  158.         /// <param name="propertyName"></param>
  159.         /// <returns></returns>
  160.         /// <exception cref="ArgumentNullException"></exception>
  161.         public static TResult GetPropertyValue<T, TResult>(this T model, string propertyName)
  162.         {
  163.             var result = GetPropertyValue(model, propertyName);
  164.             return (TResult)result;
  165.         }
  166.  
  167.         public static ConcurrentDictionary<string, object> CacheDictionary = new ConcurrentDictionary<string, object>();
  168.         public static ConcurrentDictionary<string, Delegate> CacheDelegateDictionary = new ConcurrentDictionary<string, Delegate>();
  169.         /// <summary>
  170.         /// 通过表达式树获取实体类的属性值
  171.         /// </summary>
  172.         /// <typeparam name="T"></typeparam>
  173.         /// <param name="model"></param>
  174.         /// <param name="propertyName"></param>
  175.         /// <returns></returns>
  176.         /// <exception cref="ArgumentNullException"></exception>
  177.         public static object GetPropertyValue<T>(this T model, string propertyName)
  178.         {
  179.             var type = model.GetType();
  180.             var property = type.GetProperty(propertyName);
  181.             if (property == null)
  182.             {
  183.                 throw new ArgumentNullException($"could not find property with name {propertyName}");
  184.             }
  185.  
  186.             var key = "get:" + type.FullName + property.Name;
  187.             if (CacheDictionary.TryGetValue(key, out var func))
  188.             {
  189.                 return ((Delegate)func).DynamicInvoke(model);
  190.             }
  191.  
  192.             var modelExpression = Expression.Parameter(type, "model");
  193.             var propertyExpression = Expression.Property(modelExpression, property);
  194.             var convertExpression = Expression.Convert(propertyExpression, typeof(object));
  195.             var lambda = Expression.Lambda(convertExpression, modelExpression).Compile();
  196.             var result = lambda.DynamicInvoke(model);
  197.             CacheDictionary.TryAdd(key, lambda);
  198.             return result;
  199.         }
  200.  
  201.         /// <summary>
  202.         /// 通过表达式树设置实体类的属性值
  203.         /// </summary>
  204.         /// <typeparam name="T"></typeparam>
  205.         /// <param name="model"></param>
  206.         /// <param name="propertyName"></param>
  207.         /// <param name="value"></param>
  208.         /// <exception cref="ArgumentNullException"></exception>
  209.         public static void SetPropertyValue<T>(this T model, string propertyName, object value)
  210.         {
  211.             var type = model.GetType();
  212.             var property = type.GetProperty(propertyName);
  213.  
  214.             if (property == null)
  215.             {
  216.                 throw new ArgumentNullException($"could not find property with name {propertyName}");
  217.             }
  218.  
  219.             var key = "set:" + type.FullName + property.Name;
  220.             if (CacheDictionary.TryGetValue(key, out var func))
  221.             {
  222.                 ((Delegate)func).DynamicInvoke(model, value);
  223.             }
  224.  
  225.             var modelExpression = Expression.Parameter(type, "model");
  226.             var propertyExpression = Expression.Parameter(typeof(object), "val");
  227.             var convertExpression = Expression.Convert(propertyExpression, property.PropertyType);
  228.             var methodCallExpression = Expression.Call(modelExpression, property.GetSetMethod(), convertExpression);
  229.             var lambda = Expression.Lambda(methodCallExpression, modelExpression, propertyExpression).Compile();
  230.             CacheDictionary.TryAdd(key, lambda);
  231.             lambda.DynamicInvoke(model, value);
  232.         }
  233.  
  234.         /// <summary>
  235.         /// 构建一个object数据转换成一维数组数据的委托
  236.         /// </summary>
  237.         /// <param name="objType"></param>
  238.         /// <param name="propertyInfos"></param>
  239.         /// <returns></returns>
  240.         public static Func<T, object[]> BuildObjectGetValuesDelegate<T>(List<PropertyInfo> propertyInfos) where T : class
  241.         {
  242.             var objParameter = Expression.Parameter(typeof(T), "model");
  243.             var selectExpressions = propertyInfos.Select(it => BuildObjectGetValueExpression(objParameter, it));
  244.             var arrayExpression = Expression.NewArrayInit(typeof(object), selectExpressions);
  245.             var result = Expression.Lambda<Func<T, object[]>>(arrayExpression, objParameter).Compile();
  246.             return result;
  247.         }
  248.  
  249.  
  250.         /// <summary>
  251.         /// 构建对象获取单个值得
  252.         /// </summary>
  253.         /// <param name="modelExpression"></param>
  254.         /// <param name="propertyInfo"></param>
  255.         /// <returns></returns>
  256.         public static Expression BuildObjectGetValueExpression(ParameterExpression modelExpression, PropertyInfo propertyInfo)
  257.         {
  258.             var propertyExpression = Expression.Property(modelExpression, propertyInfo);
  259.             var convertExpression = Expression.Convert(propertyExpression, typeof(object));
  260.             return convertExpression;
  261.         }
  262.  
  263.         /// <summary>
  264.         /// 
  265.         /// </summary>
  266.         /// <typeparam name="T"></typeparam>
  267.         /// <param name="source"></param>
  268.         /// <param name="propertyInfos"></param>
  269.         /// <param name="useColumnAttribute"></param>
  270.         /// <returns></returns>
  271.         public static DataTable ToDataTable<T>(this IEnumerable<T> source, List<PropertyInfo> propertyInfos = null, bool useColumnAttribute = false) where T : class
  272.         {
  273.             var table = new DataTable("template");
  274.             if (propertyInfos == null || propertyInfos.Count == 0)
  275.             {
  276.                 propertyInfos = typeof(T).GetProperties().Where(it => it.CanRead).ToList();
  277.             }
  278.             foreach (var propertyInfo in propertyInfos)
  279.             {
  280.                 var columnName = useColumnAttribute ? (propertyInfo.GetCustomAttribute<ColumnAttribute>()?.Name ?? propertyInfo.Name) : propertyInfo.Name;
  281.                 table.Columns.Add(columnName, ChangeType(propertyInfo.PropertyType));
  282.             }
  283.  
  284.             Func<T, object[]> func;
  285.             var key = typeof(T).FullName + propertyInfos.Select(it => it.Name).ToList().StringJoin();
  286.             if (CacheDictionary.TryGetValue(key, out var cacheFunc))
  287.             {
  288.                 func = (Func<T, object[]>)cacheFunc;
  289.             }
  290.             else
  291.             {
  292.                 func = BuildObjectGetValuesDelegate<T>(propertyInfos);
  293.                 CacheDictionary.TryAdd(key, func);
  294.             }
  295.  
  296.             foreach (var model in source)
  297.             {
  298.                 var rowData = func(model);
  299.                 table.Rows.Add(rowData);
  300.             }
  301.  
  302.             return table;
  303.         }
  304.  
  305.         private static Type ChangeType(Type type)
  306.         {
  307.             if (type.IsNullable())
  308.             {
  309.                 type = Nullable.GetUnderlyingType(type);
  310.             }
  311.  
  312.             return type;
  313.         }
  314.  
  315.         /// <summary>
  316.         /// 
  317.         /// </summary>
  318.         /// <param name="constructorInfo"></param>
  319.         /// <returns></returns>
  320.         public static Delegate BuildGenerateObjectDelegate(ConstructorInfo constructorInfo)
  321.         {
  322.             var parameterExpressions = new List<ParameterExpression>();
  323.             foreach (var parameterInfo in constructorInfo.GetParameters())
  324.             {
  325.                 var parameterExpression = Expression.Parameter(parameterInfo.ParameterType);
  326.                 parameterExpressions.Add(parameterExpression);
  327.             }
  328.             var c = Expression.New(constructorInfo, parameterExpressions);
  329.             var lambda = Expression.Lambda(c, parameterExpressions).Compile();
  330.             return lambda;
  331.         }
  332.  
  333.         /// <summary>
  334.         /// 替换dataTable里的列类型
  335.         /// </summary>
  336.         /// <param name="dt"></param>
  337.         public static void ReplaceDataTableColumnType<OldType, NewType>(DataTable dt, Func<OldType, NewType> replaceFunc)
  338.         {
  339.             var needUpdateColumnIndexList = new List<int>();
  340.             var needUpdateColumnNameList = new List<string>();
  341.  
  342.             for (int i = 0; i < dt.Columns.Count; i++)
  343.             {
  344.                 var column = dt.Columns[i];
  345.                 if (column.DataType.GetUnderlyingType() == typeof(OldType))
  346.                 {
  347.                     needUpdateColumnIndexList.Add(i);
  348.                     needUpdateColumnNameList.Add(column.ColumnName);
  349.  
  350.                 }
  351.             }
  352.  
  353.             if (needUpdateColumnIndexList.Count == 0)
  354.             {
  355.                 return;
  356.             }
  357.  
  358.             var nameMapping = new Dictionary<string, string>();
  359.             for (int i = 0; i < needUpdateColumnIndexList.Count; i++)
  360.             {
  361.                 var oldColumnName = needUpdateColumnNameList[i];
  362.                 var newColumnName = Guid.NewGuid().ToString("N");
  363.                 nameMapping.Add(newColumnName, oldColumnName);
  364.  
  365.                 dt.Columns.Add(newColumnName, typeof(byte[])).SetOrdinal(needUpdateColumnIndexList[i]);
  366.                 for (int j = 0; j < dt.Rows.Count; j++)
  367.                 {
  368.                     var c = (dt.Rows[j][oldColumnName]);
  369.                     dt.Rows[j][newColumnName] = replaceFunc((OldType)(dt.Rows[j][oldColumnName]));
  370.                 }
  371.                 dt.Columns.Remove(oldColumnName);
  372.             }
  373.  
  374.             for (int i = 0; i < dt.Columns.Count; i++)
  375.             {
  376.                 var columnName = dt.Columns[i].ColumnName;
  377.                 if (nameMapping.ContainsKey(columnName))
  378.                 {
  379.                     dt.Columns[i].ColumnName = nameMapping[columnName];
  380.                 }
  381.             }
  382.  
  383.         }
  384.  
  385.         /// <summary>
  386.         /// 判断字符串是否为空
  387.         /// </summary>
  388.         /// <param name="str"></param>
  389.         /// <returns></returns>
  390.         public static bool IsNullOrWhiteSpace(this string str)
  391.         {
  392.             return string.IsNullOrWhiteSpace(str);
  393.         }
  394.  
  395.         /// <summary>
  396.         /// 对字符串集合进行拼接
  397.         /// </summary>
  398.         /// <param name="source"></param>
  399.         /// <param name="separator"></param>
  400.         /// <returns></returns>
  401.         public static string StringJoin(this IEnumerable<string> source, char separator = ',')
  402.         {
  403.             return string.Join(separator, source);
  404.         }
  405.  
  406.         public static OracleDbType ConvertToOracleDbType(this Type type)
  407.         {
  408.             switch (Type.GetTypeCode(type))
  409.             {
  410.                 case TypeCode.Boolean:
  411.                     return OracleDbType.Boolean;
  412.                 case TypeCode.Byte:
  413.                 case TypeCode.SByte:
  414.                     return OracleDbType.Byte;
  415.                 case TypeCode.Int16:
  416.                 case TypeCode.UInt16:
  417.                     return OracleDbType.Int16;
  418.                 case TypeCode.Int32:
  419.                 case TypeCode.UInt32:
  420.                     return OracleDbType.Int32;
  421.                 case TypeCode.Int64:
  422.                 case TypeCode.UInt64:
  423.                     return OracleDbType.Int64;
  424.                 case TypeCode.Single:
  425.                     return OracleDbType.Single;
  426.                 case TypeCode.Double:
  427.                     return OracleDbType.Double;
  428.                 case TypeCode.Decimal:
  429.                     return OracleDbType.Decimal;
  430.                 case TypeCode.String:
  431.                     return OracleDbType.NVarchar2;
  432.                 case TypeCode.Char:
  433.                     return OracleDbType.Char;
  434.                 case TypeCode.DateTime:
  435.                     return OracleDbType.Date;
  436.                 case TypeCode.Object:
  437.                     if (type == typeof(Guid))
  438.                     {
  439.                         return OracleDbType.Raw;
  440.                     }
  441.                     else if (type == typeof(byte[]))
  442.                     {
  443.                         return OracleDbType.Blob;
  444.                     }
  445.                     else if (type == typeof(TimeSpan))
  446.                     {
  447.                         return OracleDbType.IntervalDS;
  448.                     }
  449.                     else if (type == typeof(OracleClob))
  450.                     {
  451.                         return OracleDbType.Clob;
  452.                     }
  453.                     else if (type == typeof(OracleBlob))
  454.                     {
  455.                         return OracleDbType.Blob;
  456.                     }
  457.                     else if (type == typeof(OracleXmlType))
  458.                     {
  459.                         return OracleDbType.XmlType;
  460.                     }
  461.                     else if (type == typeof(OracleRefCursor))
  462.                     {
  463.                         return OracleDbType.RefCursor;
  464.                     }
  465.                     else if (type == typeof(OracleTimeStamp))
  466.                     {
  467.                         return OracleDbType.TimeStamp;
  468.                     }
  469.                     else if (type == typeof(OracleIntervalYM))
  470.                     {
  471.                         return OracleDbType.IntervalYM;
  472.                     }
  473.                     else if (type == typeof(OracleIntervalDS))
  474.                     {
  475.                         return OracleDbType.IntervalDS;
  476.                     }
  477.                     else if (type == typeof(OracleCollectionType))
  478.                     {
  479.                         return OracleDbType.Object;
  480.                     }
  481.                     else
  482.                     {
  483.                         return OracleDbType.Varchar2;
  484.                     }
  485.                 default:
  486.                     return OracleDbType.Varchar2;
  487.             }
  488.         }
  489.  
  490.     }

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/运维做开发/article/detail/871247
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号