提高 OleDB insert into 语句的性能，BeginTransaction CommitTransaction

问题描述

我已经编写了附加函数，这些函数将自定义 c# 列表中的数据插入到 MSAccess 中.

I have written to append functions that insert data from custom c# list into MSAccess.

第一个简单地为每个单独的记录集设置一个新的连接:

The first simply sets up a new connection for each individual recordset:

        public static void appenddatatotable(string connectionstring, string tablename, string[] values)
    {

            var myconn = new OleDbConnection(connectionstring);


            var cmd = new OleDbCommand();
            cmd.CommandText = "INSERT INTO " + tablename + " ([RunDate],[ReportingGroup], [Tariff], [Year]) VALUES(@RunDate, @ReportingGroup, @Tariff, @Year)";

            cmd.Parameters.AddRange(new[] { new OleDbParameter("@RunDate", values[0]), new OleDbParameter("@ReportingGroup", values[1]), new OleDbParameter("@Tariff", values[2]), new OleDbParameter("@Year", values[3])});
            cmd.Connection = myconn;
            myconn.Open();
            cmd.ExecuteNonQuery();
            myconn.Close();

    }

然后我简单地遍历我的值列表并在每次迭代时调用这个函数.这工作正常，但速度很慢.

I then simply loop over my list of values and call this function on each iteration. This works fine but is slow.

在第二个函数中，我尝试在函数中包含循环并使用 BeginTransction 和 Committransaction:

In the second function I tried to include the loop in the function and work with BeginTransction and Committransaction:

        public static void appenddatatotable2(string connectionstring, string tablename, string datstr, List<PowRes> values)
    {

        var myconn = new OleDbConnection(connectionstring);
        int icounter = 0;

        var cmd = new OleDbCommand();
        OleDbTransaction trans = null;

        cmd.Connection = myconn;
        myconn.Open();
        foreach (var item in values)
        {
            if (icounter == 0)
            {
                trans = cmd.Connection.BeginTransaction();
                cmd.Transaction = trans;
            }

            cmd.CommandText = "INSERT INTO " + tablename + " ([RunDate],[ReportingGroup], [Tariff], [Year]) VALUES(@RunDate, @ReportingGroup, @Tariff, @Year)";
            if (string.IsNullOrEmpty(item.yr))
                item.yr = "";

            cmd.Parameters.AddRange(new[] { new OleDbParameter("@RunDate", datstr), new OleDbParameter("@ReportingGroup", item.RG), new OleDbParameter("@Tariff", item.tar), new OleDbParameter("@Year", item.yr)});
            cmd.ExecuteNonQuery();
            icounter++;
            if (icounter >= 500)
            {
                trans.Commit();
                icounter = 0;
            }
        }
        if (icounter > 0)
        {
            trans.Commit();
        }


        myconn.Close();

    }

这也能正常工作，但速度更慢.

This also works fine but is EVEN slower.

我的代码有错吗?我怎样才能加快多次插入的速度?

Is my code wrong? How could I speed up the multiple inserts?

谢谢！

推荐答案

这应该比您现有的所有版本都快得多

this should be significantly faster than all of your exiting versions

public static void appenddatatotable2(string connectionstring, string tablename, string datstr, List<PowRes> values)
        {
            string commandText = "INSERT INTO " + tablename + " ([RunDate],[ReportingGroup], [Tariff], [Year]) VALUES(@RunDate, @ReportingGroup, @Tariff, @Year)";
            using (var myconn = new OleDbConnection(connectionstring))
            {
                myconn.Open();
                using (var cmd = new OleDbCommand())
                {
                    foreach (var item in values)
                    {
                        cmd.CommandText = commandText;
                        cmd.Parameters.Clear();
                        cmd.Parameters.AddRange(new[] { new OleDbParameter("@RunDate", datstr), new OleDbParameter("@ReportingGroup", item.RG), new OleDbParameter("@Tariff", item.tar), new OleDbParameter("@Year", item.yr) });
                        cmd.Connection = myconn;
                        cmd.Prepare();
                        cmd.ExecuteNonQuery();
                    }
                }
            }
        }

这篇关于提高 OleDB insert into 语句的性能，BeginTransaction CommitTransaction的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！