批量插入数据库之Transaction测试

来源：互联网发布：手机淘宝信用查询编辑：程序博客网时间：2024/06/05 20:18

在Web上测试了一下，默认情况下，用一个循环插入1000条数据，需要大约一分钟多，手动控制Transaction后，插入10000000条数据，用时约为一分钟。

测试环境： Asp.Net 2.0+ Sqlite 数据库

下面是代码：

using System;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;
using System.IO;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }

public void InsertRandNum()
{

DbProviderFactory factory = SQLiteFactory.Instance;

        using (DbConnection con = factory.CreateConnection())
        {
            con.ConnectionString = "Data Source="+@"G:/Test/Sqlite/sqliteDB.s3db";
            con.Open();

            string sqlStr = "create table [RandNumber]([Num] INTEGER)";
            DbCommand cmd = con.CreateCommand();
            cmd.Connection = con;
            cmd.CommandText = sqlStr;
            cmd.ExecuteNonQuery();

cmd.Parameters.Add(cmd.CreateParameter());

            DbTransaction tran = con.BeginTransaction();
            try
            {
                for (int i = 0; i < 1000; i++)
                {
                    cmd.CommandText = "insert into [RandNumber]([Num]) values(?)";
                    cmd.Parameters[0].Value = i;
                    cmd.ExecuteNonQuery();
                }

                tran.Commit();
            }
            catch
            {
                tran.Rollback();
                throw;
            }

            con.Close();

        }
    }
    protected void btnInsert_Click(object sender, EventArgs e)
    {
        InsertRandNum();
    }
    protected void btnCreate_Click(object sender, EventArgs e)
    {

       //删除和新建数据库
        string path = @"G:/Test/Sqlite/sqliteDB.s3db";
        File.Delete(path);
        SQLiteConnection.CreateFile(path);
    }
}