SqlBulkCopy批量插入数据

青旅半醒 2022-08-20 02:28 59阅读 0赞

# SqlBulkCopy介绍 #

SqlBulkCopy是.NETFramework 2.0新增的类，位于命名空间System.Data.SqlClient下，主要提供把其它数据源的数据有效批量的加载到SQL Server表中的功能。类似与Microsoft SQL Server包中名为[bcp][]的命令行应用程序。但是使用SqlBulkCopy类可以编写托管代码解决方案，性能上优于bcp命令行应用程序，更优于如Insert方法向SQL Server表加载大量数据。[SqlBulkCopy][]通过DataTable或DataReader批量迁移数据，而不管数据源是什么。

若数据源的结构和目标数据库的结构不一样（包括表名和列名以及大小写也要完全限定），需要通过SqlBulkCopy.ColumnMappings设置好对应关系，这样就可以直接插入数据了。

# 使用sql语句插入数据 #

代码如下：

/// <summary>
            /// 时间变量
            /// </summary>
            private DateTime startTime;
    
            /// <summary>
            /// 读取给定路径下的所有sql文件，并且插入临时数据库
            /// </summary>
            private void button1_Click(object sender, RoutedEventArgs e)
            {
                textBox1.Text = "";
    
                string filePath = "H:\\meng";
                DirectoryInfo dinf = new DirectoryInfo(filePath);
                FileInfo[] sqlfile = dinf.GetFiles("*.sql");
    
                string strLine = "";
                foreach (var item in sqlfile)
                {
                    try
                    {
    
                        using (StreamReader sr = new StreamReader(item.OpenRead()))
                        {
                            //读取到具体的sql文件中的内容
                            strLine = sr.ReadLine();
                            while (strLine != null)
                            {
                                //插入执行
                                while (!strLine.EndsWith(");"))
                                {
                                    strLine += sr.ReadLine();
                                }
                                //执行sql语句开始的时间
                                startTime = DateTime.Now;
    
                                //执行sql命令
                                string strConn = "Data Source=localhost;Initial Catalog='<span style="font-family:KaiTi_GB2312;">test</span>';Persist Security Info=True;User ID=***;Password=***;Connect Timeout=600;";
                                SqlConnection conn = new SqlConnection(strConn);    //打开连接
                                SqlCommand cmd = new SqlCommand(strLine,conn); //执行的sql语句
                                cmd.CommandTimeout = 600;   //设置超时时间
                                conn.Open();
                                cmd.ExecuteNonQuery();
                                conn.Close();
                                //计算执行结束后的时间段
                                TimeSpan copyTime = DateTime.Now - startTime;
                                textBox1.Text += "插入时间:" + copyTime.Seconds.ToString() + "." + copyTime.Milliseconds.ToString() + "秒";  //得到插入数据所用的时间
    
                                strLine = sr.ReadLine();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                }
            }

由于sql语句很大，写到代码中代码量比较大，所以读取.sql文件中的sql语句使用。

# 插入200条数据对比图 #

![Center][]

# 使用SqlBulkCopy插入数据 #

代码如下：

<span style="font-family:KaiTi_GB2312;font-size:24px;"> /// <summary>
            /// 时间变量
            /// </summary>
            private DateTime startTime;
    
            /// <summary>
            /// 读取给定路径下的所有sql文件，并且插入临时数据库
            /// </summary>
            private void button1_Click(object sender, RoutedEventArgs e)
            {
                textBox1.Text = "";
    
                string filePath = "H:\\meng";
                DirectoryInfo dinf = new DirectoryInfo(filePath);
                FileInfo[] sqlfile = dinf.GetFiles("*.xml");
    
                string cstring = "Data Source=localhost;Initial Catalog='test';Persist Security Info=True;User ID=***;Password=***;";
    
                using (SqlConnection con = new SqlConnection(cstring))
                {
                    con.Open();
                    foreach (var item in sqlfile)
                    {
                        try
                        {
                            string tablename = item.Name.Replace(".xml", "");
                            tablename = "dbo." + tablename;
                            DataSet ds = new DataSet(tablename);
                            ds.ReadXml(item.FullName);  //读取xml文件
                            if (ds.Tables != null && ds.Tables.Count > 0)
                            {
                                startTime = DateTime.Now;
                                //调用sqlbulkcopy方法
                                WriteDataBySqlBulkCopy(ds.Tables[0], tablename, con);
                            }
    
                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show("读取xml文件出错！","提示",MessageBoxButton.OK,MessageBoxImage.Information);
                            return;
                        }
                    }
                }
                TimeSpan copyTime = DateTime.Now - startTime;
                textBox1.Text += "插入时间:" + copyTime.Seconds.ToString() + "." + copyTime.Milliseconds.ToString() + "秒";
            }
    
            /// <summary>
            /// 按照SqlBulkCopy写入数据库
            /// </summary>
            /// <param name="dt">数据表</param>
            /// <param name="tableName">数据库表名</param>
            public void WriteDataBySqlBulkCopy(DataTable dt, string tableName, DbConnection dbCon = null)
            {
                //判空
                if (dt == null || dt.Rows.Count == 0 || string.IsNullOrEmpty(tableName))
                {
                    return;
                }
                SqlConnection con = dbCon as SqlConnection;
                if (con == null)
                {
                    MessageBox.Show("传入的dbCon不是SqlConnection对象","提示",MessageBoxButton.OK,MessageBoxImage.Information);
                    return;
                }
                if (con.State != ConnectionState.Open)
                {
                    con.Open();
                }
                //此部分是重要的东西
                using (System.Data.SqlClient.SqlBulkCopy sbc = new System.Data.SqlClient.SqlBulkCopy(con))
                {
    
                    try
                    {
                        for (int i = 0; i < dt.Columns.Count; i++)
                        {
    
                            sbc.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName); //设置目标表和源数据的列映射
                        }
                        sbc.DestinationTableName = tableName;  //取得目标表名
                        sbc.WriteToServer(dt);
    
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("ex", "提示", MessageBoxButton.OK, MessageBoxImage.Information);
                        throw ex;
                    }
                    finally
                    {
                            if (con.State == ConnectionState.Open)
                            {
                                con.Close();
                            }
                            else
                            {
                                MessageBox.Show("数据库已经关闭", "提示", MessageBoxButton.OK, MessageBoxImage.Information);
                            }
                    }
                }
            }</span>

# 插入200条数据对比图 #

![Center 1][]

# SqlbulkCopy需要注意的性能问题  #

在使用SqlbulkCopy中需要注意的问题，要把某个数据源批量插入数据库，首先需要把这个数据源完全加载到内存之中，然后进行插入操作，这样就会占用大量的内存，如果在进行下一次插入操作的时候没有释放内存中的数据那么整个机器的运行效率会被严重的拖慢，所以建议把源数据加 载成为DataTable格式然后进行插入操作之后将这个DataTable所占用的内存主动释放，然后再去处理下一个数据源。

补充：sqlserver不允许一次性插入1000条以上数据，使用SqlBulkCopy可以解决这个问题。

[bcp]: http://baike.sogou.com/v10510240.htm?fromTitle=bcp
[SqlBulkCopy]: https://msdn.microsoft.com/zh-cn/library/system.data.sqlclient.sqlbulkcopy.aspx
[Center]: /images/20220731/0809d470213f4d20bc90f8e6e954e406.png
[Center 1]: /images/20220731/57c39125563747a3a2106515d481060d.png