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;
}
}
}