ado.net + sqlserver 批量添加 更新

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了ado.net + sqlserver 批量添加 更新，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含4560字，纯文字阅读大概需要7分钟。

内容图文

TABLE [dbo].[Product] GO CREATE TABLE [dbo].[Product] ( [Id] varchar(36) NOT NULL , [Name] varchar(255) NOT NULL , [Price] decimal(18,4) NOT NULL ) GO ALTER TABLE [dbo].[Product] ADD PRIMARY KEY ([Id]) GO

批量添加

        public static void Insert<T>(string connectionString, List<T> dataList, string destinationTableName, int batchSize = 0)
        {
            DataTable dataTable = ConvertToDataTable(dataList);
            Insert(connectionString, dataTable, destinationTableName, batchSize);
        }

        public static void Insert(string connectionString, DataTable dataTable, string destinationTableName, int batchSize = 0)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                if (connection.State != ConnectionState.Open)
                {
                    connection.Open();
                }
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, transaction))
                    {
                        bulkCopy.BatchSize = batchSize;
                        bulkCopy.DestinationTableName = destinationTableName;
                        try
                        {
                            bulkCopy.WriteToServer(dataTable);
                            transaction.Commit();
                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine(ex.Message);
                            transaction.Rollback();
                        }
                    }
                }
            }
        }

批量添加测试代码

public static void Insert()
        {
            List<Product> products = new List<Product>();
            for (int i = 0; i < 100000; i++)
            {
                Product product = new Product
                {
                    Id = Guid.NewGuid().ToString(),
                    Name = $"商品{i}",
                    Price = (decimal)i
                };
                products.Add(product);
            }
            Stopwatch stopwatch = new Stopwatch();
            stopwatch.Start();
            Insert(SqLiteHelper.SqlServerConnection, products, "Product");
            stopwatch.Stop();
            Console.WriteLine("耗时：" + stopwatch.ElapsedMilliseconds);
        }

批量更新

public static void Update<T>(string connectionString, List<T> list, string destinationTableName)
        {
            var dt = ConvertToDataTable(list);
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                if (connection.State != ConnectionState.Open)
                {
                    connection.Open();
                }
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    using (SqlCommand command = new SqlCommand(string.Empty, connection))
                    {
                        try
                        {
                            command.Transaction = transaction;
                            command.CommandText = "CREATE TABLE #TmpTable(Id varchar(36),Name varchar(255),Price decimal(18,4))";
                            command.ExecuteNonQuery();
                            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, transaction))
                            {
                                bulkCopy.BulkCopyTimeout = 660;
                                bulkCopy.DestinationTableName = "#TmpTable";
                                bulkCopy.WriteToServer(dt);
                                bulkCopy.Close();
                            }
                            command.CommandTimeout = 300;
                            command.CommandText = "UPDATE T SET T.Name =Temp.Name  FROM " + destinationTableName + " T INNER JOIN #TmpTable Temp ON T.Id=Temp.Id; DROP TABLE #TmpTable;";
                            command.ExecuteNonQuery();
                            transaction.Commit();
                        }
                        catch (Exception)
                        {
                            transaction.Rollback();
                        }
                    }
                }
            }
        }

批量更新测试代码

        public static List<string> GetList()
        {
            List<string> list = new List<string>();
            using (SqlConnection conn = new SqlConnection(SqLiteHelper.SqlServerConnection))
            {
                using (SqlCommand command = new SqlCommand("SELECT TOP 5000 Id FROM Product", conn))
                {
                    conn.Open();
                    var data = command.ExecuteReader();
                    while (data.Read())
                    {
                        list.Add(data["Id"].ToString());
                    }
                }
            }

            return list;
        }

        public static void Update()
        {
            var list = GetList();
            List<Product> products = new List<Product>();
            for (int i = 0; i < list.Count; i++)
            {
                Product product = new Product
                {
                    Id = list[i],
                    Name = $"默认{i}",
                    Price = (decimal)i * 5
                };
                products.Add(product);
            }
            Stopwatch stopwatch = new Stopwatch();
            stopwatch.Start();
            Update(SqLiteHelper.SqlServerConnection, products, "Product");
            stopwatch.Stop();
            Console.WriteLine("耗时：" + stopwatch.ElapsedMilliseconds);
        }

List转DataTable

 public static DataTable ConvertToDataTable<T>(IList<T> data)
        {
            PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));
            DataTable table = new DataTable();
            foreach (PropertyDescriptor prop in properties)
            {
                table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
            }
            foreach (T item in data)
            {
                DataRow row = table.NewRow();
                foreach (PropertyDescriptor prop in properties)
                {
                    row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
                }
                table.Rows.Add(row);
            }
            return table;
        }

实体类

 public class Product
    {
        public string Id { get; set; }

        public string Name { get; set; }

        public decimal Price { get; set; }
    }

链接字符串配置

 public class SqLiteHelper
{
        public const string SqlServerConnection = "Data Source=IP;Initial Catalog=库名;uid=帐号;pwd=密码;MultipleActiveResultSets=True";
}

 

测试了一下 添加10W 差不多 10S左右

 

ado.net + sqlserver 批量添加 更新

标签：tin   sage   hsi   value   top   convert   pwd   key   ORC   

本文系统来源：https://www.cnblogs.com/liuxiaoji/p/10138682.html

内容总结

以上是互联网集市为您收集整理的ado.net + sqlserver 批量添加 更新全部内容，希望文章能够帮你解决ado.net + sqlserver 批量添加 更新所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

---