C# Oracle批量插入与修改，结合Sqlsugar_sqlsugar批量新增

作者：很楠不爱3 | 2024-02-23 08:31:09

踩

sqlsugar批量新增

Oracle批量插入与修改，结合Sqlsugar

批量插入大概两万条数据，sqlsugar 的 .ExecuteCommandAsync()执行速度大概再80秒甚至更久，完全无法满足要求，因为架构是winfrom 无法使用sqlsugar自带的 BulkCopy，又因为Oracle dll版本低无法使用Oracle自带的BulkCopy，更换版本需要申请，再网上看到了一个大佬的方法，满足了需求，在此记录一下 https://www.pudn.com/news/62d92bea864d5c73acdb025c.html

这是一个通用的批量插入与修改，我修改了一下代码，如有问题，请指出：

 		/// <summary>
        /// 批量插入
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list">数据实体类</param>
        /// <returns></returns>
        public static int BulkInsert<T>(List<T> list)
        {
            try
            {
                Type model = typeof(T);
                List<PropertyInfo> pi = GetMappedField<T>();

                #region 生成数据源与参数

                OracleParameter[] paras = new OracleParameter[pi.Count];//用于存放数据
                string[] propertys = new string[pi.Count];
                string[] paras_propertys = new string[pi.Count];
                for (int i = 0; i < pi.Count; i++)
                {
                    PropertyInfo p = pi[i];
                    //获取数据库对应字段名
                    SugarColumn[] sugarColumns = (SugarColumn[])p.GetCustomAttributes(typeof(SugarColumn), false);
                    string name = sugarColumns[0].ColumnName;
                    propertys[i] = name;
                    paras_propertys[i] = $":{name}";

                    Type pt = p.PropertyType;

                    //字段是否可可为空
                    bool IsNullable = pt.Name == "Nullable`1";
                    var colArr = new List<object>();
                    foreach (var item in list)
                    {
                        object val = p.GetValue(item, null);
                        colArr.Add((val == null && !IsNullable) ? DBNull.Value : val);
                    }

                    //获取类型
                    OracleDbType dt = OracleDbType.Varchar2;
                    GetOracleDbType(IsNullable, p, pt, out dt);

                    paras[i] = new OracleParameter($":{name}", dt) { Value = colArr.ToArray() };
                }

                #endregion 生成数据源与参数

                #region 获取表名

                string tableName = model.Name;
                //sqlsugar架构的表属性名
                SugarTable[] tableDesc = (SugarTable[])model.GetCustomAttributes(typeof(SugarTable), false);
                tableName = tableDesc.First().TableName;

                #endregion 获取表名

                string sql = $"INSERT INTO {tableName}({string.Join(",", propertys)}) VALUES({string.Join(",", paras_propertys)})";

                int result = ExecuteNonQuery(list.Count, sql, paras);

                return result;
            }
            catch (Exception err)
            {
                throw err;
            }
        }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67

		/// <summary>
        /// 获取映射的字段
        /// </summary>
        /// <returns></returns>
        public static List<PropertyInfo> GetMappedField<T>()
        {
            try
            {
                Type model = typeof(T);
                List<PropertyInfo> proTemp = model.GetProperties(BindingFlags.Instance | BindingFlags.Static | BindingFlags.Public).ToList();

                #region 过滤出与表对应的字段

                List<PropertyInfo> pi = new List<PropertyInfo>();
                for (int i = 0; i < proTemp.Count; i++)
                {
                    PropertyInfo p = proTemp[i];
                    string name = p.Name;
                    Type pt = p.PropertyType;
                    //是否泛型 如：List<User>   Int?  等
                    //bool t1 = pt.IsGenericType;
                    //Console.WriteLine($"序号：{i + 1}；字段{ p.Name}；类型{ pt.Name }；IsGenericType：{pt.IsGenericType}");
                    if (pt.BaseType == null)
                    {
                        continue;
                    }

                    //被标记不与数据库映射的字段SugarColumn
                    SugarColumn[] caArray = (SugarColumn[])p.GetCustomAttributes(typeof(SqlSugar.SugarColumn), true);

                    if (caArray.Any(a => a.IsIgnore == true))
                    {
                        continue;
                    }

                    Object[] caArray2 = p.GetCustomAttributes(typeof(NotMappedAttribute), true);
                    if (caArray2.Length > 0)
                    {
                        continue;
                    }
                    //基本类型
                    if (p.PropertyType.Namespace == "System")
                    {
                        pi.Add(p);
                    }
                    else if (p.PropertyType.IsEnum || pt.BaseType.Name == "Enum" || p.PropertyType.BaseType.Name == "Enum")
                    {
                        pi.Add(p);
                    }
                    else
                    {
                        //不与数据库映射的字段
                        continue;
                    }
                }

                #endregion 过滤出与表对应的字段

                return pi;
            }
            catch (Exception err)
            {
                throw err;
            }
        }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65

 		/// <summary>
        /// 获取数据基本类型
        /// </summary>
        /// <param name="IsNullable">是否为空</param>
        /// <param name="p"></param>
        /// <param name="pt"></param>
        /// <param name="dt"></param>
        public static void GetOracleDbType(bool IsNullable, PropertyInfo p, Type pt, out OracleDbType dt)
        {
            dt = OracleDbType.Varchar2;

            try
            {
                if (p.PropertyType.Namespace == "System")
                {
                    if (IsNullable)
                    {
                        //可为空时找真实基本类型
                        pt = pt.GetGenericArguments()[0];
                    }
                    switch (pt.Name)
                    {
                        case "String":
                            dt = OracleDbType.Varchar2;
                            break;
                        case "Short":
                        case "Int":
                        case "Int16":
                            dt = OracleDbType.Int16;
                            break;
                        case "Int32":
                            dt = OracleDbType.Int32;
                            break;
                        case "Decimal":
                            dt = OracleDbType.Decimal;
                            break;
                        case "Long":
                        case "Int64":
                        case "Double":
                            dt = OracleDbType.Long;
                            break;                        
                        case "DateTime":
                            dt = OracleDbType.Date;
                            break;
                        default:
                            break;
                    }
                }
                else if (p.PropertyType.IsEnum || pt.BaseType.Name == "Enum" || p.PropertyType.BaseType.Name == "Enum")
                {
                    //枚举单独处理
                    dt = OracleDbType.Int32;
                }
            }
            catch (Exception err)
            {
                throw err;
            }
        }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59

		/// <summary>
        /// 数据库执行
        /// </summary>
        /// <param name="count">数据行数</param>
        /// <param name="sql">执行sql</param>
        /// <param name="paras">执行参数</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(int count, string sql, OracleParameter[] paras)
        {
            int result = -1;
            try
            {
                #region 数据处理
                using (var command = Connection.CreateCommand())
                {
                    command.ArrayBindCount = count;
                    command.FetchSize = 1000;
                    command.CommandText = sql.ToString();
                    command.CommandType = CommandType.Text;
                    command.Parameters.AddRange(paras);
                    command.BindByName = true;
                    result = command.ExecuteNonQuery();
                }
                #endregion 数据处理
            }
            catch (Exception err)
            {
                throw err;
            }
            return result;
        }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

		/// <summary>
        /// 批量修改
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list">数据实体类</param>
        /// <returns></returns>
        public static int BulkUpdate<T>(List<T> list)
        {
            try
            {
                Type model = typeof(T);
                List<PropertyInfo> pi = GetMappedField<T>();

                #region 生成数据源与参数

                OracleParameter[] paras = new OracleParameter[pi.Count];
                var updateSet = new List<string>();
                var updateWhere = new List<string>();
                for (int i = 0; i < pi.Count; i++)
                {
                    string set = "";
                    PropertyInfo p = pi[i];

                    //获取数据库对应字段名
                    SugarColumn[] sugarColumns = (SugarColumn[])p.GetCustomAttributes(typeof(SugarColumn), false);
                    string name = sugarColumns[0].ColumnName;

                    //是否为主键 批量修改默认根据主键修改数据
                    if (sugarColumns[0].IsPrimaryKey)
                    {
                        updateWhere.Add($"{name}=:{name}");
                    }
                    else
                    {
                        set = $"{name}=:{name}";
                    }

                    Type pt = p.PropertyType;

                    //字段是否可可为空
                    bool IsNullable = pt.Name == "Nullable`1";
                    var colArr = new List<object>();
                    foreach (var item in list)
                    {
                        object val = p.GetValue(item, null);
                        colArr.Add((val == null && !IsNullable) ? DBNull.Value : val);
                    }

                    //获取类型
                    OracleDbType dt = OracleDbType.Varchar2;
                    GetOracleDbType(IsNullable, p, pt, out dt);

                    if (!string.IsNullOrWhiteSpace(set))
                    {
                        updateSet.Add(set);
                    }

                    paras[i] = new OracleParameter($":{name}", dt) { Value = colArr.ToArray() };
                }

                #endregion 生成数据源与参数

                #region 获取表名

                string tableName = model.Name;
                //sqlsugar架构的表属性名
                SugarTable[] tableDesc = (SugarTable[])model.GetCustomAttributes(typeof(SugarTable), false);
                tableName = tableDesc.First().TableName;

                #endregion 获取表名


                string sql = $"UPDATE {tableName} SET {string.Join(",", updateSet)} WHERE {string.Join(" AND ", updateWhere)}";
                int result = ExecuteNonQuery(list.Count, sql, paras);

                return result;
            }
            catch (Exception err)
            {
                throw err;
            }
        }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82

测试之后只需要两秒就可以完成两万条数据的修改，非常nice

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/很楠不爱3/article/detail/135061