Sqlite使用事务插入数据

using System.Data;
using System.Data.Common;
using System.Data.SQLite;

// 创建数据库文件
File.Delete("test1.db3");
SQLiteConnection.CreateFile("test1.db3");

DbProviderFactory factory = SQLiteFactory.Instance;
using (DbConnection conn = factory.CreateConnection())
{
  // 连接数据库
  conn.ConnectionString = "Data Source=test1.db3";
  conn.Open();

  // 创建数据表
  string sql = "create table [test1] ([id] INTEGER PRIMARY KEY, [s] TEXT COLLATE NOCASE)";
  DbCommand cmd = conn.CreateCommand();
  cmd.Connection = conn;
  cmd.CommandText = sql;
  cmd.ExecuteNonQuery();

  // 添加参数
  cmd.Parameters.Add(cmd.CreateParameter());
  
  // 开始计时
  Stopwatch watch = new Stopwatch();
  watch.Start();
  
  DbTransaction trans = conn.BeginTransaction(); // <-------------------
  try 
  {
    // 连续插入1000条记录
    for (int i = 0; i < 1000; i++)
    {
      cmd.CommandText = "insert into [test1] ([s]) values (?)";
      cmd.Parameters[0].Value = i.ToString();

      cmd.ExecuteNonQuery();
    }

    trans.Commit(); // <-------------------
  }
  catch
  {
    trans.Rollback(); // <-------------------
    throw; // <-------------------
  }

  // 停止计时
  watch.Stop();
  Console.WriteLine(watch.Elapsed);
}

SQLite,可以使用sqlite3_exec()函数和sqlite3_prepare_v2()函数来插入数据。下面是一个插入数据的示例代码,其包含两种不同的插入数据的方法: 引用: ```c sqlite3_exec(db,"begin;",0,0,0); // 开始事务 sqlite3_stmt *stmt; const char* sql = "insert into t1 values(?,?,?,?)"; // SQL语句 sqlite3_prepare_v2(db,sql,strlen(sql),&stmt,0); for(int i=0;i<nCount;++i) { // 循环插入数据 sqlite3_reset(stmt); sqlite3_bind_int(stmt,1,i); sqlite3_bind_int(stmt,1,i*2); sqlite3_bind_int(stmt,1,i/2); sqlite3_bind_double(stmt,1,i*i); } sqlite3_finalize(stmt); sqlite3_exec(db,"commit;",0,0,0); // 提交事务 ``` 引用: ```c // 连接数据库 // 定义变量 char* zErrMsg = 0; int rc; sqlite3_stmt *stmt; // 打开数据库 rc = sqlite3_open("test.db", &db); // 创建表格 char* sql = "CREATE TABLE COMPANY(" \ "ID INT PRIMARY KEY NOT NULL," \ "NAME TEXT NOT NULL," \ "AGE INT NOT NULL," \ "ADDRESS CHAR(50)," \ "SALARY REAL );"; // 执行SQL语句 rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg); // 插入数据 const char* data = "Callback function called"; char *sql2 = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " \ "VALUES (1, 'Paul', 32, 'California', 20000.00 ); " \ "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " \ "VALUES (2, 'Allen', 25, 'Texas', 15000.00 ); " \ "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)" \ "VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );" \ "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)" \ "VALUES (4, 'Mark', 25, 'Rich-Mond', 65000.00 );"; // 将SQL语句编译成字节码 rc = sqlite3_prepare_v2(db, sql2, -1, &stmt, 0); // 执行编译好的SQL语句 rc = sqlite3_step(stmt); // 释放资源 sqlite3_finalize(stmt); sqlite3_close(db); ```
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值