ADO.Net 之 Access批量Insert

最新推荐文章于 2026-05-22 06:34:14 发布

原创最新推荐文章于 2026-05-22 06:34:14 发布 · 1.2k 阅读

2 ·

本内容遵循CC 4.0 BY-SA版权协议

收录于

Access

本文介绍了使用ADO.Net在Access数据库中实现批量Insert的方法，包括构造DataTable、填充数据和利用OleDbDataAdapter进行秒级批量提交操作，适用于大量数据的快速导入。

跟SQLServer批量insert步骤一样:

1,构造DataTable

        /// <summary>
        /// 构造Table
        /// </summary>
        /// <returns></returns>
        public DataTable BuildTable()
        {
            try
            {
                DataTable dt = new DataTable("Person");//与目标表名一致
                DataColumn ID = dt.Columns.Add("ID", typeof(String));
                ID.AllowDBNull = false;//空值
                ID.Unique = true;//唯一
                dt.Columns.Add("ksj", typeof(String));
                return dt;
            }
            catch (Exception ex)
            {
                return null;
            }
        }

2,封装并为Table赋值

            DataTable dt =BuildTable();
            for (int i = 0; i < 10000; i++)
            {
                DataRow dr = dt.NewRow();
                dr["ID"] = i.ToString();
                dr["ksj"] = "pil";
                dt.Rows.Add(dr);
            }

3,OleDbDataAdapter批量提交Table

 public void insert(DataTable dt1)
        {
            try
            {
                GetOleDbConn();
                OpenConn();
                OleDbCommand cmd2 = new OleDbCommand("insert into Person(ID,ksj)) values (@ID,@ksj)");//加@等同于转义关键字,最好加
                OleDbDataAdapter adapt = new OleDbDataAdapter("select * from Person", oleConn);// 把目标表数据拿到OleDbDataAdapter 
                OleDbCommandBuilder OleDbCmdBud = new OleDbCommandBuilder(adapt);
                OleDbParameter ID = new OleDbParameter("@ID", OleDbType.VarWChar, 255);
                OleDbParameter Names = new OleDbParameter("@ksj", OleDbType.VarWChar, 255);
                adapt.Fill(dt1);
                adapt.SelectCommand.Parameters.Add(ID);
                adapt.SelectCommand.Parameters.Add(Names);
                adapt.SelectCommand.Parameters["@ID"].SourceColumn = "ID";
                adapt.SelectCommand.Parameters["@ksj"].SourceColumn = "ksj";
                adapt.UpdateCommand = OleDbCmdBud.GetUpdateCommand();
                if (dt1 != null)
                {
                    adapt.Update(dt1);//这里开始提交了
                }
            }
            catch (Exception ex)
            {
                 ex.Message;            }
        }

万条数据可控制在秒级。

标签

#access #insert #exception #sqlserver #table