大量数据插入ACCESS

DataTable中大量数据插入ACCESS

1000条数据只要2秒钟。

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

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

 

 

Code
 1using System;
 2using System.Collections.Generic;
 3using System.Text;
 4using System.Data;
 5using System.Data.OleDb;
 6
 7namespace DAL
 8{
 9    public class DB
10    {
11        OleDbConnection con = null;
12        OleDbCommand cmd = new OleDbCommand();
13        public DB()
14        {
15            string constr = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=" + System.Environment.CurrentDirectory+@"\Data\RTXMessage.mdb";
16            con = new OleDbConnection(constr);
17
18        }
19        //打开连接
20        public OleDbConnection getCon()
21        {
22            if (con.State == ConnectionState.Closed)
23                con.Open();
24            return con;
25        }
26        //关闭连接
27        public void clear()
28        {
29            if (con.State == ConnectionState.Open)
30                con.Close();
31        }
32public string  updata(DataTable dt1,string table)
33        {
34            string s = DateTime.Now.ToLongTimeString();
35            getCon();
36
37
38            OleDbCommand cmd2 = new OleDbCommand("insert into " + table + "(ID,deptName,userName,realName) values (@id,@deptName,@userName,@realName)");
39
40            OleDbDataAdapter adapt = new OleDbDataAdapter("select * from " + table, con);
41
42            OleDbCommandBuilder OleDbCmdBud = new OleDbCommandBuilder(adapt);
43            OleDbParameter idParam = new OleDbParameter("@id", OleDbType.Integer, 255);
44            OleDbParameter deptNameParam = new OleDbParameter("@deptName", OleDbType.VarWChar, 255);
45            OleDbParameter userNameParam = new OleDbParameter("@userName", OleDbType.VarWChar, 255);
46            OleDbParameter realNameParam = new OleDbParameter("@realName", OleDbType.VarWChar, 255);
47
48
49            adapt.Fill(dt1);
50            adapt.SelectCommand.Parameters.Add(idParam);
51            adapt.SelectCommand.Parameters.Add(deptNameParam);
52            adapt.SelectCommand.Parameters.Add(userNameParam);
53            adapt.SelectCommand.Parameters.Add(realNameParam);
54
55            adapt.SelectCommand.Parameters["@id"].SourceColumn = "ID";
56            adapt.SelectCommand.Parameters["@deptName"].SourceColumn = "deptName";
57            adapt.SelectCommand.Parameters["@userName"].SourceColumn = "userName";
58            adapt.SelectCommand.Parameters["@realName"].SourceColumn = "realName";
59
60
61            adapt.UpdateCommand = OleDbCmdBud.GetUpdateCommand();
62            if (dt1 != null)
63            {
64                adapt.Update(dt1);
65            }
66            clear();
67            string ss = DateTime.Now.ToLongTimeString();
68            return s + "/" + ss;
69        }
70}

转载于:https://www.cnblogs.com/hoho/archive/2009/05/09/1453407.html