C# sqlite 事务

https://zetcode.com/csharp/sqlite/

1 安装依赖包

System.Data.SQLite.Core

2 demo

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace sqlitedemo
{
    class Program
    {
        static void Main(string[] args)
        {
            SqliteHelper sqliteHelper = new SqliteHelper();




            StringBuilder stringBuilder = new StringBuilder();

            for (int i = 0; i < 100; i++)
            {
                stringBuilder.Append($"insert into test values({i},{i + 10});");
            }

            sqliteHelper.ExecuteNonQueryTransaction(stringBuilder.ToString(),new object());


        }
    }

    class person
    {
        public int id { get; set; }

        public int age { get; set; }
    }
}

SqliteHelper

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SQLite;
using System.IO;
using System.Threading;
using System.Data;
using System.Data.Common;
using System.Text.RegularExpressions;

namespace sqlitedemo
{
    public class SqliteHelper
    {
        string sqlitePath = "local.sqlite";
        SQLiteConnection m_dbConnection = null;

        public SqliteHelper()
        {
            if (!File.Exists(sqlitePath))
            {
                CreateNewDatabase(sqlitePath);
                //string sql = "create table highscores (Id varchar(36), Name varchar(50), Score int)";
                //CreateTable(sql);
            }
            m_dbConnection = new SQLiteConnection("Data Source=" + sqlitePath + ";");
        }


        //创建一个空的数据库
        public static void CreateNewDatabase(string sqlitePath)
        {
            SQLiteConnection.CreateFile(sqlitePath);
        }

        public int ExecuteNonQueryTransaction(string commandText, object lockObj, object[] paramList = null)
        {

            int result = 0;

            try
            {
                Monitor.Enter(lockObj);
                if (m_dbConnection == null)
                    return result;



                if (m_dbConnection.State == ConnectionState.Closed)
                    m_dbConnection.Open();



                using (DbTransaction dbTrans = m_dbConnection.BeginTransaction())
                {
                    using (SQLiteCommand cmd = m_dbConnection.CreateCommand())
                    {

                        cmd.CommandText = commandText;
                        AttachParameters(cmd, commandText, paramList);
                        cmd.ExecuteNonQuery();
                    }
                    dbTrans.Commit();
                }
                //cmd.Dispose();
                //m_dbConnection.Close();
            }
            catch (Exception ex)
            {
               
            }
            finally
            {
                if (m_dbConnection != null)
                    m_dbConnection.Close();

                Monitor.Exit(lockObj);
            }

            return result;
        }



        private SQLiteParameterCollection AttachParameters(SQLiteCommand cmd, string commandText, params object[] paramList)
        {
            if (paramList == null || paramList.Length == 0) return null;

            SQLiteParameterCollection coll = cmd.Parameters;
            string parmString = commandText.Substring(commandText.IndexOf("@"));
            // pre-process the string so always at least 1 space after a comma.
            parmString = parmString.Replace(",", " ,");
            // get the named parameters into a match collection
            string pattern = @"(@)\S*(.*?)\b";
            Regex ex = new Regex(pattern, RegexOptions.IgnoreCase);
            MatchCollection mc = ex.Matches(parmString);
            string[] paramNames = new string[mc.Count];
            int i = 0;
            foreach (Match m in mc)
            {
                paramNames[i] = m.Value;
                i++;
            }

            // now let's type the parameters
            int j = 0;
            Type t = null;
            foreach (object o in paramList)
            {
                t = o.GetType();

                SQLiteParameter parm = new SQLiteParameter();
                switch (t.ToString())
                {

                    case ("DBNull"):
                    case ("Char"):
                    case ("SByte"):
                    case ("UInt16"):
                    case ("UInt32"):
                    case ("UInt64"):
                        throw new SystemException("Invalid data type");


                    case ("System.String"):
                        parm.DbType = DbType.String;
                        parm.ParameterName = paramNames[j];
                        parm.Value = (string)paramList[j];
                        coll.Add(parm);
                        break;

                    case ("System.Byte[]"):
                        parm.DbType = DbType.Binary;
                        parm.ParameterName = paramNames[j];
                        parm.Value = (byte[])paramList[j];
                        coll.Add(parm);
                        break;

                    case ("System.Int32"):
                        parm.DbType = DbType.Int32;
                        parm.ParameterName = paramNames[j];
                        parm.Value = (int)paramList[j];
                        coll.Add(parm);
                        break;

                    case ("System.Boolean"):
                        parm.DbType = DbType.Boolean;
                        parm.ParameterName = paramNames[j];
                        parm.Value = (bool)paramList[j];
                        coll.Add(parm);
                        break;

                    case ("System.DateTime"):
                        parm.DbType = DbType.DateTime;
                        parm.ParameterName = paramNames[j];
                        parm.Value = Convert.ToDateTime(paramList[j]);
                        coll.Add(parm);
                        break;

                    case ("System.Double"):
                        parm.DbType = DbType.Double;
                        parm.ParameterName = paramNames[j];
                        parm.Value = Convert.ToDouble(paramList[j]);
                        coll.Add(parm);
                        break;

                    case ("System.Decimal"):
                        parm.DbType = DbType.Decimal;
                        parm.ParameterName = paramNames[j];
                        parm.Value = Convert.ToDecimal(paramList[j]);
                        break;

                    case ("System.Guid"):
                        parm.DbType = DbType.Guid;
                        parm.ParameterName = paramNames[j];
                        parm.Value = (System.Guid)(paramList[j]);
                        break;

                    case ("System.Object"):

                        parm.DbType = DbType.Object;
                        parm.ParameterName = paramNames[j];
                        parm.Value = paramList[j];
                        coll.Add(parm);
                        break;

                    default:
                        throw new SystemException("Value is of unknown data type");

                } // end switch

                j++;
            }
            return coll;
        }
    }
}