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集合