C# SQLite数据库参数化批量插入数据库，启用事务

最新推荐文章于 2023-09-04 16:41:29 发布

bearwcn

最新推荐文章于 2023-09-04 16:41:29 发布

阅读量2.2k

点赞数

本文链接：https://blog.csdn.net/bearwcn/article/details/8515333

版权

using (SQLiteConnection con = new SQLiteConnection(conna))
{
    con.Open();
    DbTransaction trans = con.BeginTransaction();//开始事务
    SQLiteCommand cmd = new SQLiteCommand(con);
    try
    {
        for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
        {
            cmd.CommandText = "insert into article(id,tid,province_id,grade,title,CDT,article) values(@id,@tid,@province_id,@grade,@title,@CDT,@article)";


            cmd.Parameters.Add(new SQLiteParameter("@id", DbType.Int32, 4));
            cmd.Parameters.Add(new SQLiteParameter("@tid", DbType.Int32, 4));
            cmd.Parameters.Add(new SQLiteParameter("@province_id", DbType.Int32, 4));
            cmd.Parameters.Add(new SQLiteParameter("@grade", DbType.Int32, 4));
            cmd.Parameters.Add(new SQLiteParameter("@title", DbType.String, 400));
            cmd.Parameters.Add(new SQLiteParameter("@CDT", DbType.DateTime));
            cmd.Parameters.Add(new SQLiteParameter("@article", DbType.String));

            cmd.Parameters[0].Value = Convert.ToInt32(ds.Tables[0].Rows[i]["id"]);
            cmd.Parameters[1].Value = Convert.ToInt32(ds.Tables[0].Rows[i]["tid"]);
            cmd.Parameters[2].Value = Convert.ToInt32(ds.Tables[0].Rows[i]["province_id"]);
            cmd.Parameters[3].Value = Convert.ToInt32(ds.Tables[0].Rows[i]["grade"]);
            cmd.Parameters[4].Value = ds.Tables[0].Rows[i]["title"].ToString().Trim();
            cmd.Parameters[5].Value = Convert.ToDateTime(ds.Tables[0].Rows[i]["CDT"]);
            cmd.Parameters[6].Value = ds.Tables[0].Rows[i]["article"].ToString().Trim();

            cmd.ExecuteNonQuery();

            SetProgressBar(i + 1, ds.Tables[0].Rows.Count);//进度条
            SetText("正在解压数据，请稍候...完成：" + (i + 1) + "/" + ds.Tables[0].Rows.Count);
        }
        trans.Commit();//提交事务
    }
    catch (Exception ex)
    {
        trans.Rollback();
        throw ex;
    }
}

若直接带参数，格式如下：

var insertSQL = new SQLiteCommand("INSERT INTO Brugere (navn, brugernavn, password, pc_id, noter, licens_id)" +
" VALUES (@uNavnParam, @bNavnParam, @passwdParam, @pc_idParam, @noterParam, @licens_idParam)", conn);
insertSQL.Parameters.AddWithValue("@uNavnParam", uNavn);
insertSQL.Parameters.AddWithValue("@bNavnParam", bNavn);
insertSQL.Parameters.AddWithValue("@passwdParam", passwd);
insertSQL.Parameters.AddWithValue("@pc_idParam", pc_id);
insertSQL.Parameters.AddWithValue("@noterParam", noter);
insertSQL.Parameters.AddWithValue("@licens_idParam", licens_id);