大量数据插入ACCESS

DataTable中大量数据插入ACCESS

1000条数据只要2秒钟。

调用updata 方法   string s= data.updata(dt, "rtx_deptuser");

返回的结果："21:03:00/21:03:02"

 

 

 1using System;
 2using System.Collections.Generic;
 3using System.Text;
 4using System.Data;
 5using System.Data.OleDb;
 6
 7namespace DAL
 8{
 9    public class DB
    {
        OleDbConnection con = null;
        OleDbCommand cmd = new OleDbCommand();
        public DB()
        {
            string constr = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=" + System.Environment.CurrentDirectory+@"\Data\RTXMessage.mdb";
            con = new OleDbConnection(constr);

        }
        //打开连接
        public OleDbConnection getCon()
        {
            if (con.State == ConnectionState.Closed)
                con.Open();
            return con;
        }
        //关闭连接
        public void clear()
        {
            if (con.State == ConnectionState.Open)
                con.Close();
        }
public string  updata(DataTable dt1,string table)
        {
            string s = DateTime.Now.ToLongTimeString();
            getCon();


            OleDbCommand cmd2 = new OleDbCommand("insert into " + table + "(ID,deptName,userName,realName) values (@id,@deptName,@userName,@realName)");

            OleDbDataAdapter adapt = new OleDbDataAdapter("select * from " + table, con);

            OleDbCommandBuilder OleDbCmdBud = new OleDbCommandBuilder(adapt);
            OleDbParameter idParam = new OleDbParameter("@id", OleDbType.Integer, 255);
            OleDbParameter deptNameParam = new OleDbParameter("@deptName", OleDbType.VarWChar, 255);
            OleDbParameter userNameParam = new OleDbParameter("@userName", OleDbType.VarWChar, 255);
            OleDbParameter realNameParam = new OleDbParameter("@realName", OleDbType.VarWChar, 255);


            adapt.Fill(dt1);
            adapt.SelectCommand.Parameters.Add(idParam);
            adapt.SelectCommand.Parameters.Add(deptNameParam);
            adapt.SelectCommand.Parameters.Add(userNameParam);
            adapt.SelectCommand.Parameters.Add(realNameParam);

            adapt.SelectCommand.Parameters["@id"].SourceColumn = "ID";
            adapt.SelectCommand.Parameters["@deptName"].SourceColumn = "deptName";
            adapt.SelectCommand.Parameters["@userName"].SourceColumn = "userName";
            adapt.SelectCommand.Parameters["@realName"].SourceColumn = "realName";


            adapt.UpdateCommand = OleDbCmdBud.GetUpdateCommand();
            if (dt1 != null)
            {
                adapt.Update(dt1);
            }
            clear();
            string ss = DateTime.Now.ToLongTimeString();
            return s + "/" + ss;
        }
}