SQLite使用

1.数据库转换成db文件（百度搜索：SQLite转对文件下载sql server to sqlite db converter工具）

 

2.添加引用 System.Data.SQLite

3.SQLite DBhelper类

4.写语句直接调用helper类（话不多说，直接上源码）

String dbConnection;

/// <summary>
/// SQLiteDatabase类的默认构造函数。
/// </summary>
public SQLiteDatabase()
{
dbConnection = @"Data Source=D:\垃圾\WindowsFormsApp2\WindowsFormsApp2\bin\Debug\text.db;Initial Catalog=sqlite;Integrated Security=True;Max Pool Size=10";
}

/// <summary>
/// 用于指定DB文件的单个Param构造函数。
/// </summary>
/// <param name="inputFile">包含数据库的文件</param>
public SQLiteDatabase(String inputFile)
{
dbConnection = String.Format("Data Source={0}", inputFile);
}

/// <summary>
/// 用于指定高级连接选项的单个Param构造函数。
/// </summary>
/// <param name="connectionOpts">包含所有所需选项及其值的字典</param>
public SQLiteDatabase(Dictionary<String, String> connectionOpts)
{
String str = "";
foreach (KeyValuePair<String, String> row in connectionOpts)
{
str += String.Format("{0}={1}; ", row.Key, row.Value);
}
str = str.Trim().Substring(0, str.Length - 1);
dbConnection = str;
}

/// <summary>
/// 允许程序员对数据库运行查询。
/// </summary>
/// <param name="sql">The SQL to run</param>
/// <returns>A DataTable containing the result set.</returns>
public DataTable GetDataTable(string sql)
{
DataTable dt = new DataTable();
try
{
SQLiteConnection cnn = new SQLiteConnection(dbConnection);
cnn.Open();
SQLiteCommand mycommand = new SQLiteCommand(cnn);
mycommand.CommandText = sql;
SQLiteDataReader reader = mycommand.ExecuteReader();
dt.Load(reader);
reader.Close();
cnn.Close();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
return dt;
}

/// <summary>
/// 允许程序员为查询以外的目的与数据库进行交互。
/// </summary>
/// <param name="sql">The SQL to be run.</param>
/// <returns>An Integer containing the number of rows updated.</returns>
public int ExecuteNonQuery(string sql)
{
SQLiteConnection cnn = new SQLiteConnection(dbConnection);
cnn.Open();
SQLiteCommand mycommand = new SQLiteCommand(cnn);
mycommand.CommandText = sql;
int rowsUpdated = mycommand.ExecuteNonQuery();
cnn.Close();
return rowsUpdated;
}

/// <summary>
/// 允许程序员从DB检索单个项。
/// </summary>
public string ExecuteScalar(string sql)
{
SQLiteConnection cnn = new SQLiteConnection(dbConnection);
cnn.Open();
SQLiteCommand mycommand = new SQLiteCommand(cnn);
mycommand.CommandText = sql;
object value = mycommand.ExecuteScalar();
cnn.Close();
if (value != null)
{
return value.ToString();
}
return "";
}

/// <summary>
/// 允许程序员轻松地更新DB中的行。
/// </summary>
public bool Update(String tableName, Dictionary<String, String> data, String where)
{
String vals = "";
Boolean returnCode = true;
if (data.Count >= 1)
{
foreach (KeyValuePair<String, String> val in data)
{
vals += String.Format(" {0} = '{1}',", val.Key.ToString(), val.Value.ToString());
}
vals = vals.Substring(0, vals.Length - 1);
}
try
{
this.ExecuteNonQuery(String.Format("update {0} set {1} where {2};", tableName, vals, where));
}
catch
{
returnCode = false;
}
return returnCode;
}
/// <summary>
/// 允许程序员轻松地从DB中删除行。
/// </summary>
public bool Delete(String tableName, String where)
{
Boolean returnCode = true;
try
{
this.ExecuteNonQuery(String.Format("delete from {0} where {1};", tableName, where));
}
catch (Exception fail)
{
Console.WriteLine(fail.Message);
returnCode = false;
}
return returnCode;
}

/// <summary>
/// 允许程序员很容易地插入到DB中。
/// </summary>
public bool Insert(String tableName, Dictionary<String, String> data)
{
String columns = "";
String values = "";
Boolean returnCode = true;
foreach (KeyValuePair<String, String> val in data)
{
columns += String.Format(" {0},", val.Key.Trim().ToString());
values += String.Format(" '{0}',", val.Value);
}
columns = columns.Substring(0, columns.Length - 1);
values = values.Substring(0, values.Length - 1);
try
{
this.ExecuteNonQuery(String.Format("insert into {0}({1}) values({2});", tableName, columns, values));
}
catch (Exception fail)
{
Console.WriteLine(fail.Message);
returnCode = false;
}
return returnCode;
}

/// <summary>
/// 允许程序员轻松地从DB中删除所有数据。
/// </summary>
public bool ClearDB()
{
DataTable tables;
try
{
tables = this.GetDataTable("select NAME from SQLITE_MASTER where type='table' order by NAME;");
foreach (DataRow table in tables.Rows)
{
this.ClearTable(table["NAME"].ToString());
}
return true;
}
catch
{
return false;
}
}

/// <summary>
///清空表所有数据
/// </summary>

public bool ClearTable(String table)
{
try
{

this.ExecuteNonQuery(String.Format("delete from {0};", table));
return true;
}
catch
{
return false;
}
}