C# 操作SQLite数据库-马育民老师

# 添加依赖库

添加 `System.Data.SQLite` 依赖，操作如下图：

[![](https://www.malaoshi.top/upload/0/0/1GW1GMNRGGgT.png)](https://www.malaoshi.top/upload/0/0/1GW1GMNRGGgT.png)

[![](https://www.malaoshi.top/upload/0/0/1GW1GMMfBAAN.png)](https://www.malaoshi.top/upload/0/0/1GW1GMMfBAAN.png)

# 封装工具类

```
public class SQLite3Util
{
private SQLiteConnection Conn;

/// <summary>
/// 数据库不存在就创建
/// </summary>
/// <param name="path"></param>
/// <returns>如果存在返回true，不存在返回false</returns>
public bool createDatabaseNotExists(string path)
{
    if (!File.Exists(path))
    {
        SQLiteConnection.CreateFile(path);
        return false;
    }
    return true;
}
/// <summary>
/// 连接数据库
/// </summary>
/// <param name="path">数据库路径，如：@"Data Source=D:\sqlliteDb\Test.db;Version=3"</param>
public void Connect(string path)
{
    //连接数据库
    Conn = new SQLiteConnection(path);
    Conn.Open();

}
/// <summary>
/// 执行查询
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public DataTable query(string sql)
{
    SQLiteDataAdapter mAdapter = new SQLiteDataAdapter(sql, Conn);
    DataTable rs = new DataTable();
    mAdapter.Fill(rs);
    return rs;
}
public DataTable query(string sql, IDictionary<string, object> param)
{
            
    SQLiteCommand command = new SQLiteCommand(sql, Conn);
    foreach (KeyValuePair<string, object> kvp in param)
    {
        command.Parameters.AddWithValue(kvp.Key, kvp.Value);
    }
    SQLiteDataAdapter mAdapter = new SQLiteDataAdapter(command);
    DataTable rs = new DataTable();
    mAdapter.Fill(rs);
    return rs;
}
/// <summary>
/// 执行查询
/// </summary>
/// <param name="sql"></param>
/// <param name="tableName"></param>
/// <returns></returns>
public DataSet query(string sql,string tableName)
{
    SQLiteDataAdapter mAdapter = new SQLiteDataAdapter(sql, Conn);
    DataSet rs = new DataSet();
    mAdapter.Fill(rs, tableName);
    return rs;
}
public DataSet query(string sql, string tableName, IDictionary<string, object> param)
{
            
    SQLiteCommand command = new SQLiteCommand(sql, Conn);
    foreach (KeyValuePair<string, object> kvp in param)
    {
        command.Parameters.AddWithValue(kvp.Key, kvp.Value);
    }
    SQLiteDataAdapter mAdapter = new SQLiteDataAdapter(command);
    DataSet rs = new DataSet();
    mAdapter.Fill(rs, tableName);
    return rs;
}
/// <summary>
/// 执行增、删、改、建表等sql语句
/// </summary>
/// <param name="sql"></param>
public void update(string sql)
{
    SQLiteCommand command = new SQLiteCommand(sql, Conn);
    command.ExecuteNonQuery();
}
public void update(string sql, IDictionary<string, object> param)
{
    SQLiteCommand command = new SQLiteCommand(sql, Conn);

foreach (KeyValuePair<string, object> kvp in param)
    {
        command.Parameters.AddWithValue(kvp.Key, kvp.Value);
    }
    command.ExecuteNonQuery();
}

public void close()
{
    Conn.Close();
}
```

# 执行

### 带有占位符的SQL

```
string SQLITE3_INSERT_DOC = @"insert into t_user(
            id ,username ,password ) values (
            @id ,@name ,@pwd)";

IDictionary<string, object> dictInsert = new Dictionary<string, object>();

dictInsert.Add("@id", 1);
dictInsert.Add("@name", "李雷");
dictInsert.Add("@pwd", "123456");
dictInsert.Add("@md5", md5);

sqlite3Util.update(Constant.SQLITE3_INSERT_DOC, dictInsert);
```

原文出处：http://www.malaoshi.top/show_1GW1GMRXAHkk.html