c#和数据库的结合编程,给我打击挺大的,让我摸不着头脑,不知道该干嘛了。

批量导入数据(ADO.NET)
如果你需要在程序中批量插入成千上万行的数据,你会怎么编写代码呢?最近在帮朋友调优这个的时候,总结了几种方法,并对其进行比较。

SqlDataAdapter sda = new
SqlDataAdapter(“select*from xsda”, “Data
Source=.;Initial Catalog=XS;Integrated Security=True”);
     DataTable dt = new
DataTable();
     sda.Fill(dt);
     dataGridView1.DataSource = dt;
        }

  首先是c#与数据库的链接,开始学的几天,写不出来。过来几天,好多了。有些概念不明白,不知道他们到底用来干嘛的,什么时候用,给怎么用,只能参考老师给的代码,做了之后还是没感觉,自己还是写不出来,书上写的太抽象了,例子又不明显,没有针对性。

大概的界面如下,我模拟了一个客户资料表.

        dbcon db = new dbcon();
        //第一种方法
        private void button2_Click(object sender, EventArgs e)
        {
            SqlConnection sqlcon = db.Mycon();
          
            DataSet daxsda = new
DataSet();
           SqlCommand   sqlcom = new
SqlCommand();
           try
           {
               sqlcon.Open();
               sqlcom.Connection = sqlcon;
               sqlcom.CommandText = “insert into
xsda values(‘” +
textBox1.Text + “‘,'” +
textBox2.Text + “‘,'” +
textBox3.Text + “‘,'” +
textBox4.Text + “‘,'” +
textBox5.Text + “‘,'” +
textBox6.Text + “‘,'” +
textBox7.Text + “‘,'” +
null + “‘)”;

using System;

 

               int i =
sqlcom.ExecuteNonQuery();
               SqlCommand sqlcm = new SqlCommand();
               sqlcm.CommandText = “select*from
xsda “;
               sqlcm.Connection = sqlcon;
               SqlDataAdapter sldp = new SqlDataAdapter();
               sldp.SelectCommand = sqlcm;
               DataSet ds1 = new
DataSet();
               sldp.Fill(ds1, “xsda”);
               dataGridView1.DataSource = ds1.Tables[0];
           }
           catch (Exception ex)
           {

using System.Collections.Generic;

数据我是放在一个XML文件的,大约6734行。类似下面的格式

               MessageBox.Show(ex.Message);
           }
           sqlcom.Dispose();
           sqlcon.Close();

using System.ComponentModel;

<?xml version=”1.0″ encoding=”utf-8″ ?>
<root>
<Customers>
  <CustomerID>ALFKI</CustomerID>
  <CompanyName>Sina</CompanyName>
  <ContactName>Maria Anders</ContactName>
  <ContactTitle>Sales Representative</ContactTitle>
  <Address>Obere Str. 57</Address>
  <City>Berlin</City>
  <PostalCode>12209</PostalCode>
  <Country>Germany</Country>
  <Phone>030-0074321</Phone>
  <Fax>030-0076545</Fax>
</Customers>
<Customers>
  <CustomerID>ANATR</CustomerID>
  <CompanyName>Ana Trujillo Emparedados y
helados</CompanyName>
  <ContactName>Ana Trujillo</ContactName>
  <ContactTitle>Owner</ContactTitle>
  <Address>Avda. de la Constitución 2222</Address>
  <City>México D.F.</City>
  <PostalCode>05021</PostalCode>
  <Country>Mexico</Country>
  <Phone>(5) 555-4729</Phone>
  <Fax>(5) 555-3745</Fax>
</Customers>
<Customers>
  <CustomerID>ANTON</CustomerID>
  <CompanyName>Antonio Moreno Taquería</CompanyName>
  <ContactName>Antonio Moreno</ContactName>
  <ContactTitle>Owner</ContactTitle>
  <Address>Mataderos  2312</Address>
  <City>México D.F.</City>
  <PostalCode>05023</PostalCode>
  <Country>Mexico</Country>
  <Phone>(5) 555-3932</Phone>
</Customers>
</root>下面首先在服务器稍微准备一下环境

            

using System.Data;

USE [tempdb]
GO

        }
        //统计人数

using System.Drawing;

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Customers](
    [CustomerID] [nchar](5) NOT NULL,
    [CompanyName] [nvarchar](40) NOT NULL,
    [ContactName] [nvarchar](30) NULL,
    [ContactTitle] [nvarchar](30) NULL,
    [Address] [nvarchar](60) NULL,
    [City] [nvarchar](15) NULL,
    [Region] [nvarchar](15) NULL,
    [PostalCode] [nvarchar](10) NULL,
    [Country] [nvarchar](15) NULL,
    [Phone] [nvarchar](24) NULL,
    [Fax] [nvarchar](24) NULL
) ON [PRIMARY]

        private void button1_Click(object sender, EventArgs e)
        {
            SqlConnection sqlcon1 = db.Mycon();
          
            SqlCommand sqlcom1 = new
SqlCommand();
            try
            {
                sqlcon1.Open();
                sqlcom1.Connection = sqlcon1;
                sqlcom1.CommandText = “select
count(学号) from xsda”;
                int i =
Convert.ToInt32(sqlcom1.ExecuteScalar());
                textBox8.Text = i.ToString();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
          
                sqlcom1.Dispose();
      
            
            sqlcon1.Close();
            
            

using System.Linq;

 

        }
        //第二种方法
        private void button3_Click(object sender, EventArgs e)
        {
            SqlConnection sqlcon = db.Mycon();
            sqlcon.Open();
        
            SqlCommand sqlcom = new
SqlCommand();
            sqlcom.Connection = sqlcon;
            sqlcom.CommandText = “XSADD”;
            sqlcom.CommandType = CommandType.StoredProcedure;
            try
            {

using System.Text;

CREATE PROCEDURE [dbo].[usp_InsertCustomer]
    @CustomerID nchar(5),
    @CompanyName nvarchar(40),
    @ContactName nvarchar(30),
    @ContactTitle nvarchar(30),
    @Address nvarchar(60),
    @City nvarchar(15),
    @Region nvarchar(15),
    @PostalCode nvarchar(10),
    @Country nvarchar(15),
    @Phone nvarchar(24),
    @Fax nvarchar(24)
AS

                //SqlDataReader sldr =
sqlcom.ExecuteReader();
                sqlcom.Parameters.Add(“@id”,
SqlDbType.Char,6).Value =
textBox1.Text;
                sqlcom.Parameters.Add(“@name”, SqlDbType.Char,10).Value = textBox2.Text;

using System.Windows.Forms;

SET NOCOUNT ON

                sqlcom.Parameters.Add(“@sex”,
SqlDbType.Bit).Value = textBox3.Text;

using System.Data.SqlClient;

INSERT INTO [dbo].[Customers] (
    [CustomerID],
    [CompanyName],
    [ContactName],
    [ContactTitle],
    [Address],
    [City],
    [Region],
    [PostalCode],
    [Country],
    [Phone],
    [Fax]
) VALUES (
    @CustomerID,
    @CompanyName,
    @ContactName,
    @ContactTitle,
    @Address,
    @City,
    @Region,
    @PostalCode,
    @Country,
    @Phone,
    @Fax
)  我们在tempdb中创建了一个表和一个存储过程
首先,我们把数据加载到一个DataSet        DataSet ds = new DataSet();
        private void btLoadData_Click(object sender, EventArgs e)
        {
            string dataFile = “CustomersData.xml”;
            ds.ReadXml(dataFile);
            bindingSource1.DataSource = ds;
            bindingSource1.DataMember = “Customers”;
            dataGridView1.DataSource = bindingSource1;
           
        }然后,我们第一个测试代码是遍历这个DataSet,每一行提交一次

                sqlcom.Parameters.Add(“@birth”, SqlDbType.SmallDateTime).Value =
Convert.ToDateTime(textBox4.Text);
                  sqlcom.Parameters.Add(“@xim”,
SqlDbType.Char,20).Value =
textBox5.Text;
                  sqlcom.Parameters.Add(“@minzu”, SqlDbType.Char, 2).Value = textBox6.Text;
                sqlcom.Parameters.Add(“@score”, SqlDbType.Int).Value
=Convert.ToInt32(textBox7.Text);
          
  

namespace dddd

        private string GetConnectionString()
        {
            return “server=(local);database=tempdb;integrated
security=true;”;
        }
        /// <summary>
        /// 直接遍历,一个一个的提交给服务器。时间为265毫秒左右
        /// 每一行都需要写日志
        /// </summary>
        /// <param name=”sender”></param>
        /// <param name=”e”></param>
        private void btOneByOne_Click(object sender, EventArgs e)
        {
            SqlConnection conn = new
SqlConnection(GetConnectionString());
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = “usp_InsertCustomer”;
            cmd.CommandType = CommandType.StoredProcedure;
            conn.Open();

                

{

            TimeSpan startTime =
System.Diagnostics.Process.GetCurrentProcess().UserProcessorTime;

                int i =
sqlcom.ExecuteNonQuery();
                SqlDataAdapter sld = new SqlDataAdapter();
                DataSet ds1 = new
DataSet();
                sld.Fill(ds1, “XSDA”);

    

            foreach (DataRow row in ds.Tables[0].Rows)
            {
                cmd.Parameters.Clear();
                SqlParameter[] param = new SqlParameter[]{
                    new
SqlParameter(“@CustomerID”,row[0].ToString()),
                    new
SqlParameter(“@CompanyName”,row[1].ToString()),
                    new
SqlParameter(“@ContactName”,row[2].ToString()),
                    new
SqlParameter(“@ContactTitle”,row[3].ToString()),
                    new SqlParameter(“@Address”,row[4].ToString()),
                    new SqlParameter(“@City”,row[5].ToString()),
                    new SqlParameter(“@Region”,row[6].ToString()),
                    new
SqlParameter(“@PostalCode”,row[7].ToString()),
                    new SqlParameter(“@Country”,row[8].ToString()),
                    new SqlParameter(“@Phone”,row[9].ToString()),
                    new SqlParameter(“@Fax”,row[10].ToString())
                };
                cmd.Parameters.AddRange(param);
                cmd.ExecuteNonQuery();
            }
            conn.Close();

                dataGridView1.DataSource = ds1.Tables[0];
            }
            catch(Exception ex)
            {

    public partial class Form1 : Form

            TimeSpan duration =
System.Diagnostics.Process.GetCurrentProcess().UserProcessorTime.Subtract(startTime);
            MessageBox.Show(“已经全部插入成功,所用时间为” +
duration.Milliseconds.ToString() + “毫秒”);
        }接下来,我们使用ADO.NET内置的一个DataAdapter来提交

                MessageBox.Show(ex.Message);
            }
            
            

    {

        /// <summary>
        /// 这是使用Adapter的方式,其实还是遍历,而且语法也没有简单
        /// 同时,速度甚至更慢。时间为650毫秒左右
        /// 每一行都需要写日志
        /// </summary>
        /// <param name=”sender”></param>
        /// <param name=”e”></param>
        private void btUseAdapter_Click(object sender, EventArgs e)
        {
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.AcceptChangesDuringUpdate =
false;//为了演示目的,把这个开关关掉,以免它在更新完成后把数据集标记为未更改
            adapter.UpdateBatchSize = 10;//这个好像也没有什么用
           
            SqlConnection conn = new
SqlConnection(GetConnectionString());
            SqlCommand insertCommand = conn.CreateCommand();
            insertCommand.CommandText = “usp_InsertCustomer”;
            insertCommand.CommandType = CommandType.StoredProcedure;

          
        }
        DataSet ds;
        DataTable dt;
        //第三种方法
        private void button4_Click(object sender, EventArgs e)
        {
            ds = new DataSet();
            dt = new DataTable();
            try
            {
                dt.Columns.Add(“学号”,
typeof(int));
                dt.Columns.Add(“姓名”,
typeof(string));
                dt.Columns.Add(“性别”,
typeof(Boolean));
                dt.Columns.Add(“系名”,
typeof(string));
                dt.Columns.Add(“出生年月”, typeof(string));
                dt.Columns.Add(“民族”,
typeof(string));
                dt.Columns.Add(“总学分”, typeof(int));
              
                DataRow row = dt.NewRow();
                row[0] =
textBox1.Text.Trim();
                row[1] =
textBox2.Text.Trim();
                row[2] =
textBox3.Text.Trim();
                row[3] =
textBox4.Text.Trim();
                row[4] =
textBox5.Text.Trim();
                row[5] =
textBox6.Text.Trim();
                row[6] =
textBox7.Text.Trim();
              
                dt.Rows.Add(row);
                ds.Tables.Add(dt);

        private SqlConnection dbConnection;

            insertCommand.UpdatedRowSource = UpdateRowSource.None;

                dataGridView1.DataSource = ds.Tables[0];
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

        private SqlDataAdapter adapter;

            insertCommand.Parameters.Add(“@CustomerID”, SqlDbType.NChar,
5, “CustomerID”);
            insertCommand.Parameters.Add(“@CompanyName”,
SqlDbType.NVarChar, 40, “CompanyName”);
            insertCommand.Parameters.Add(“@ContactName”,
SqlDbType.NVarChar, 30, “ContactName”);
            insertCommand.Parameters.Add(“@ContactTitle”,
SqlDbType.NVarChar, 30, “ContactTitle”);
            insertCommand.Parameters.Add(“@Address”, SqlDbType.NVarChar,
60, “Address”);
            insertCommand.Parameters.Add(“@City”, SqlDbType.NVarChar,
15, “City”);
            insertCommand.Parameters.Add(“@Region”, SqlDbType.NVarChar,
15, “Region”);
            insertCommand.Parameters.Add(“@PostalCode”,
SqlDbType.NVarChar, 10, “PostalCode”);
            insertCommand.Parameters.Add(“@Country”, SqlDbType.NVarChar,
15, “Country”);
            insertCommand.Parameters.Add(“@Phone”, SqlDbType.NVarChar,
24, “Phone”);
            insertCommand.Parameters.Add(“@Fax”, SqlDbType.NVarChar, 24,
“Fax”);

        }
    }
}

        private DataSet ds;

            adapter.InsertCommand = insertCommand;
            TimeSpan startTime =
System.Diagnostics.Process.GetCurrentProcess().UserProcessorTime;
            adapter.Update(ds,”Customers”);
            TimeSpan duration =
System.Diagnostics.Process.GetCurrentProcess().UserProcessorTime.Subtract(startTime);
            MessageBox.Show(“已经全部插入成功,所用时间为” +
duration.Milliseconds.ToString() + “毫秒”);

        DataGridView dataGrid;

           
           
        }最后,我们找到了最快的方法

        public Form1()

        /// <summary>
        ///
使用新的API,批量导入,这个速度很快,大约26毫秒,很显然,这种方式只写一次日志,不会为每一行写日志
        /// </summary>
        /// <param name=”sender”></param>
        /// <param name=”e”></param>
        private void btBCP_Click(object sender, EventArgs e)
        {
            using (SqlConnection conn = new
SqlConnection(GetConnectionString()))
            {
                SqlBulkCopy bcp = new SqlBulkCopy(conn);
                bcp.DestinationTableName = “Customers”;
                bcp.BatchSize = 100;//这是批尺寸可以调整
                for (int i = 0; i < 11; i++)
                {
                    bcp.ColumnMappings.Add(i, i);
                }
                TimeSpan startTime =
System.Diagnostics.Process.GetCurrentProcess().UserProcessorTime;
                conn.Open();
                bcp.WriteToServer(ds.Tables[0]);
                TimeSpan duration =
System.Diagnostics.Process.GetCurrentProcess().UserProcessorTime.Subtract(startTime);
                MessageBox.Show(“已经全部插入成功,所用时间为” +
duration.Milliseconds.ToString() + “毫秒”);

        {

            }
       
}还有一种办法是通过在服务器OPENXML,因为XML反复处理效率很差,所以就没有测试了,可以断定它肯定比其他几种还要慢。

            InitializeComponent();

另外提示一下,如果不用编程的方式,那么有其他三个可能的途径去做这个事情

        }

1.
BCP工具(这是一个命令行,可以做导入和导出,不过来源文件如果不规范,那么可能很费劲)

        private void button1_Click(object sender, EventArgs e)

  1. BULK
    INSERT语句(这是一个T-SQL语句,只能做导入,我们上面使用的SQLBULKCopy应该和他很类似)

  2. XML Bulk Load(这是一套COM的对象模型,适合导入XML文档)

        {

还有,在做大量的数据导入和导出时,可以考虑微软为SQL
Server配套的SSIS(Integration Service)

            string conn;

            conn = “Data Source=localhost;Initial
Catalog=lunwen;Integrated Security=true”;

            dbConnection = new SqlConnection();

            dbConnection.ConnectionString = conn;

            label1.Text = “连接成功”;

        }

        private void button2_Click(object sender, EventArgs e)

        {

            

            try

            {

                string sqlString;

                sqlString = “SELECT * FROM 产品;”;

                adapter = new SqlDataAdapter(sqlString, dbConnection);

               ds = new DataSet();

                adapter.Fill(ds, “changpin”);

                

                dataGridView1.DataSource = ds;

                dataGridView1.DataMember = “changpin”;

            }

            catch (Exception e1)

            {

                MessageBox.Show(e1.Message);

            }

        }

        private void 更新_Click(object sender, EventArgs e)

        {

            bindUpdataCommand();

            try

            {

                adapter.Update(ds, “chanpin”);

            }

            catch (Exception e2)

            {

                MessageBox.Show(e2.Message,”更新数据库失败”);

            }

        }

        private void bindUpdataCommand()

        {

            string sqlstring = “UPDATA 产品 SET
产品ID=@number,产品名称=@name,供应商ID=@gys where 供应商ID=@gys”;

            SqlCommand updataCommand = new
SqlCommand(sqlstring,dbConnection);

          
 updataCommand.Parameters.Add(“@number”,SqlDbType.Int,20,”产品ID”);

          
 updataCommand.Parameters.Add(“@name”,SqlDbType.Char,20,”产品名称”);

          
 updataCommand.Parameters.Add(“@gys”,SqlDbType.Int,10,”供应商ID”);

            SqlParameter parameters = new
SqlParameter(“@gys”,SqlDbType.Int);

            parameters.SourceColumn = “供应商ID”;

            parameters.SourceVersion = DataRowVersion.Original;

            updataCommand.Parameters.Add(parameters);

            adapter.UpdateCommand = updataCommand;

        }

    }

}

我的代码写出来,可以与数据库连接了,也显示出来了,就是没法更新数据库,我不知道bindUpdataCommand()是什么,还需要花时间去研究。

不过,能实现和数据库的连接和显示数据库我还是有成果的,对于刚接触这一块的我来说,我会把它搞定的。