Sqlite安装配置及使用

一、下载SQLite

Sqlite官网

我下载的是3370000版本:sqlite-dll-win64-x64-3370000.zip 和 sqlite-tools-win32-x86-3370000.zip 

二、解压下载的两个压缩包 

 三、配置环境

四、检查是否安装配置成功

• win+R：输入cmd调出命令窗口，输入sqlite3后回车查看sqlite版本

五、Navcate连接Sqlite 

• 以上就是安装配置Sqlite，并使用数据库管理工具---Navicat简单创建或者打开数据库文件，在管理工具中可以对db文件中的表进行修改

六、C#代码中调用SQLite数据库（db文件）

• 新建项目
• 在NuGet程序包内，搜索System.Data.Sqlite  安装Sqlite类库 

• 创建SQLiteHelper类 

    public class SQLiteHelper
    {
         ///可以创建数据库文件
         public static void CreateDBFile(string fileName)
         {
             string path = System.Environment.CurrentDirectory + @"/Data/";
             if (!Directory.Exists(path))
             {
                 Directory.CreateDirectory(path);
             }
             string databaseFileName = path + fileName;
             if (!File.Exists(databaseFileName))
             {
                 SQLiteConnection.CreateFile(databaseFileName);
             }
        }
 
          //生成连接字符串
          private static string CreateConnectionString()
          {
              SQLiteConnectionStringBuilder connectionString = new SQLiteConnectionStringBuilder();
              connectionString.DataSource = System.Environment.CurrentDirectory + @"\StudentDB.db";//这里StudentDB.db文件中的Grade表是我在Navicat中新创建的，用来测试
              string conStr = connectionString.ToString();
              return conStr;
          }

         /// <summary>
         /// 对插入到数据库中的空值进行处理
         /// </summary>
         /// <param name="value"></param>
         /// <returns></returns>
         public static object ToDbValue(object value)
         {
                 if (value == null)
                     {
                         return DBNull.Value;
                     }
                 else
                     {
                         return value;
                     }
         }

          /// <summary>
          /// 对从数据库中读取的空值进行处理
          /// </summary>
          /// <param name="value"></param>
          /// <returns></returns>
          public static object FromDbValue(object value)
          {
                 if (value == DBNull.Value)
                     {
                         return null;
                     }
               else
                     {
                         return value;
                     }
             }

          /// <summary>
          /// 执行非查询的数据库操作
          /// </summary>
          /// <param name="sqlString">要执行的sql语句</param>
          /// <param name="parameters">参数列表</param>
          /// <returns>返回受影响的条数</returns>
          public static int ExecuteNonQuery(string sqlString, params SQLiteParameter[] parameters)
          {
                 string connectionString = CreateConnectionString();
                 using (SQLiteConnection conn = new SQLiteConnection(connectionString))
                 {
                    conn.Open();
                    using (SQLiteCommand cmd = conn.CreateCommand())
                    {
                       cmd.CommandText = sqlString;
                       foreach (SQLiteParameter parameter in parameters)
                       {
                            cmd.Parameters.Add(parameter);
                       }
                       return cmd.ExecuteNonQuery();
                    }
                 }
          }

         /// <summary>
         /// 执行查询并返回查询结果第一行第一列
         /// </summary>
         /// <param name="sqlString">SQL语句</param>
         /// <param name="sqlparams">参数列表</param>
         /// <returns></returns>
         public static object ExecuteScalar(string sqlString, params SQLiteParameter[] parameters)
         {
                 string connectionString = CreateConnectionString();
                 using (SQLiteConnection conn = new SQLiteConnection(connectionString))
                     {
                         conn.Open();
                         using (SQLiteCommand cmd = conn.CreateCommand())
                            {
                                cmd.CommandText = sqlString;
                                foreach (SQLiteParameter parameter in parameters)
                                     {
                                         cmd.Parameters.Add(parameter);
                                     }
                                 return cmd.ExecuteScalar();
                             }
                     }
             }

         /// <summary>
         /// 查询多条数据
         /// </summary>
         /// <param name="sqlString">SQL语句</param>
         /// <param name="parameters">参数列表</param>
         /// <returns>返回查询的数据表</returns>
         public static DataTable GetDataTable(string sqlString, params SQLiteParameter[] parameters)
         {
                string connectionString = CreateConnectionString();
                using (SQLiteConnection conn = new SQLiteConnection(connectionString))
                     {
                         conn.Open();
                         using (SQLiteCommand cmd = conn.CreateCommand())
                             {
                                 cmd.CommandText = sqlString;
                                 foreach (SQLiteParameter parameter in parameters)
                                     {
                                         cmd.Parameters.Add(parameter);
                                     }
                                 DataSet ds = new DataSet();
                                 SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);
                                 adapter.Fill(ds);
                                 conn.Close();
                                 return ds.Tables[0];
                             }
                     }
             }
         
    }

• 调用SQLiteHelper类 

public class Program
{
    static void Main(string[] args)
    {
        GetAllGrade();
    }

    /// <summary>
    /// 查询所有年级
    /// </summary>
    /// <returns></returns>
    public static List<GradeEty> GetAllGrade()
    {
        List<GradeEty> gradeEties = new List<GradeEty>();

        SQLiteHelper sQLiteHelper = new SQLiteHelper();
        string sqlQuery = "SELECT * FROM 'Grade';";
        SQLiteParameter[] parameters = new SQLiteParameter[]{};

        DataTable dt = SQLiteHelper.GetDataTable(sqlQuery, parameters);
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            string Id = dt.Rows[i]["ID"].ToString();
            string GradeName = dt.Rows[i]["GradeName"].ToString();


            GradeEty clientEty = new GradeEty(Id, GradeName);
            gradeEties.Add(clientEty);
        }
        return gradeEties;
    }
}

public class GradeEty
{
    public string Id { get; set; }
    public string Name { get; set; }

    public GradeEty(string id, string name)
    {
        Id = id;
        Name = name;
    }
}

• db文件中的Grade表

• 拿到db文件中Grade表的数据，转化成List集合