C# SQLite

1. 官网下载https://www.sqlite.org/download.html

    各个平台下载的安装包，本次以win10为例，DLL可以根据系统，tool通用

2. 解压后将所有文件放在同一个目录下

   

    

3. 控制台检测

   

   出现版本号就算成功了

4.  可以运行【.help】查看相关命令

   

    

5. 运行命令创建数据库

   

   

     open命令，当数据库不存在就创建新的

6. 执行sql脚本

   

    

    

    注：

   1、sqlite是动态数据类型，所以你可以不声明字段的类型，但最好还是声明；

   2、结束语句加上分号“；”，系统命令无需添加

嵌入C#

1. 添加引用

   

    

    

2. 附上源码（此源码在.net5下创建）

   string connString = "Data Source=netTest.db";
               string sql =
   @"drop table if exists stu;
   create table stu(
       name nvarchar(50),
       age int
   );
   insert into stu values('张三',12);
   insert into stu values('李四',23);
   ";
               string sql2 = "select * from stu";
   
               try
               {
                   using (SqliteConnection conn = new(connString))
                   {
                       conn.Open();
                       SqliteCommand cmd = new(sql, conn);
                       cmd.ExecuteNonQuery();
   
                       cmd.CommandText = sql2;
                       SqliteDataReader dr = cmd.ExecuteReader();
                       while (dr.Read())
                       {
                           Console.WriteLine("name:{0};age:{1}", dr[0], dr[1]);
                       }
                   }
               }
               catch (Exception ex)
               {
                   Console.WriteLine(ex.Message);
               }
   
               Console.WriteLine("End.");
               Console.Read();

   程序中无需创建数据库，SqliteConnection会自动在程序根目录下创建

3. 附上SQLiteHelper

       public static class SQLiteHelper
       {
           #region 获取连接字符串
           /// <summary>
           /// 获取连接字符串
           /// </summary>
           /// <returns></returns>
           private static SqliteConnection GetConnection()
           {
               try
               {
                   string db = ConfigurationManager.AppSettings["sqlconn"].ToString();
                   SqliteConnectionStringBuilder connStr = new SqliteConnectionStringBuilder();
                   connStr.DataSource = db;
                   connStr.Pooling = true;
                   SqliteConnection conn = new SqliteConnection(connStr.ToString());
                   return conn;
               }
               catch { throw; }
           }
           #endregion
   
           #region 修改语句
           /// <summary>
           /// 修改语句
           /// </summary>
           /// <param name="sql"></param>
           /// <param name="commandParameters"></param>
           /// <returns></returns>
           public static bool ExecuteNonQuery(string sql, params SqliteParameter[] commandParameters)
           {
               try
               {
                   using (SqliteConnection conn = GetConnection())
                   {
                       conn.Open();
                       SqliteCommand cmd = new(sql, conn);
                       if (commandParameters.Length > 0)
                       {
                           foreach (SqliteParameter parm in commandParameters)
                           {
                               cmd.Parameters.Add(parm);
                           }
                       }
                       int resutl = cmd.ExecuteNonQuery();
                       return resutl > -1 ? true : false;
                   }
               }
               catch { throw; }
           }
           #endregion
   
           #region 查询语句
           /// <summary>
           /// 查询语句
           /// </summary>
           /// <param name="sql"></param>
           /// <param name="commandParameters"></param>
           /// <returns></returns>
           public static DataTable QueryTable(string sql, params SqliteParameter[] commandParameters)
           {
               try
               {
                   using (SqliteConnection conn = GetConnection())
                   {
                       conn.Open();
                       SqliteCommand cmd = new(sql, conn);
                       if (commandParameters.Length > 0)
                       {
                           foreach (SqliteParameter parm in commandParameters)
                           {
                               cmd.Parameters.Add(parm);
                           }
                       }
                       SqliteDataReader sdr = cmd.ExecuteReader();
                       DataTable dt = new();
                       for (int i = 0; i < sdr.FieldCount; i++)
                       {
                           DataColumn myDataColumn = new DataColumn();
                           myDataColumn.DataType = sdr.GetFieldType(i);
                           myDataColumn.ColumnName = sdr.GetName(i);
                           dt.Columns.Add(myDataColumn);
                       }
                       while (sdr.Read())
                       {
                           DataRow myDataRow = dt.NewRow();
                           for (int i = 0; i < sdr.FieldCount; i++)
                           {
                               myDataRow[i] = sdr[i].ToString();
                           }
                           dt.Rows.Add(myDataRow);
                       }
                       return dt;
                   }
               }
               catch { throw; }
           }
           #endregion
       }

   <?xml version="1.0" encoding="utf-8" ?>
   <configuration>
     <appSettings>
       <add key="sqlconn" value="Data Source=Test.db"/>
     </appSettings>
   </configuration>