一、下载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集合