官网网址，选择对应的版本进行下载
http://www.sqlite .com .cn
需要用到的DLL为System.Data.SQLite.DLL，需要把SQLite.Interop.dll放到运行目录下(bin\debug)（这两个DLL都是要安装SQLite的时候下载到本地的）
sqlite的管理工具很多，网上搜一下很多，sqlitestudio是个轻量级的管理工具，我用Navicat for SQLite。当然还有其他的管理工具，凭个人喜好。
//SQL操纵数据库
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace SQLiteLibrary
{
public class SQLiteDatabase
{
private string dbConnection = "Data Source=recipes.s3db";
public SQLiteConnection Connecttion
{
get;
set;
}
public void Open()
{
Connecttion.Open();
}
public void Close()
{
Connecttion.Close();
}
/// <summary>
/// Default Constructor for SQLiteDatabase Class.
/// </summary>
public SQLiteDatabase()
{
Connecttion = new SQLiteConnection(dbConnection);
}
/// <summary>
/// Single Param Constructor for specifying advanced connection options.
/// </summary>
/// <param name="connectionOpts">
/// A dictionary containing all desired options and their values
/// </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>
/// Allows the programmer to run a query against the Database.
/// </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
{
SQLiteCommand mycommand = new SQLiteCommand(Connecttion);
mycommand.CommandText = sql;
SQLiteDataReader reader = mycommand.ExecuteReader();
dt.Load(reader);
reader.Close();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
return dt;
}
public DataTable GetDataTable(string sql, IList<SQLiteParameter> cmdparams)
{
DataTable dt = new DataTable();
try
{
SQLiteCommand mycommand = new SQLiteCommand(Connecttion);
mycommand.CommandText = sql;
mycommand.Parameters.AddRange(cmdparams.ToArray());
mycommand.CommandTimeout = 180;
SQLiteDataReader reader = mycommand.ExecuteReader();
dt.Load(reader);
reader.Close();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
return dt;
}
/// <summary>
/// Allows the programmer to interact with the database for purposes ///other than a query.
/// </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)
{
SQLiteCommand mycommand = new SQLiteCommand(Connecttion);
mycommand.CommandText = sql;
int rowsUpdated = 0;
try
{
rowsUpdated = mycommand.ExecuteNonQuery();
}
catch(SQLiteException e)
{
}
return rowsUpdated;
}
public bool ExecuteNonQuery(string sql, IList<SQLiteParameter> cmdparams)
{
bool successState = false;
using (SQLiteTransaction mytrans = Connecttion.BeginTransaction())
{
SQLiteCommand mycommand = new SQLiteCommand(sql, Connecttion, mytrans);
try
{
mycommand.Parameters.AddRange(cmdparams.ToArray());
mycommand.CommandTimeout = 180;
mycommand.ExecuteNonQuery();
mytrans.Commit();
successState = true;
}
catch (Exception e)
{
mytrans.Rollback();
throw e;
}
finally
{
mycommand.Dispose();
}
}
return successState;
}
/// <summary>
/// Allows the programmer to retrieve single items from the DB.
/// </summary>
/// <param name="sql">The query to run.</param>
/// <returns>A string.</returns>
public string ExecuteScalar(string sql)
{
SQLiteCommand mycommand = new SQLiteCommand(Connecttion);
mycommand.CommandText = sql;
object value = mycommand.ExecuteScalar();
if (value != null)
{
return value.ToString();
}
return "";
}
/// <summary>
/// Allows the programmer to easily update rows in the DB.
/// </summary>
/// <param name="tableName">The table to update.</param>
/// <param name="data">A dictionary containing Column names and their new values.</param>
/// <param name="where">The where clause for the update statement.</param>
/// <returns>A boolean true or false to signify success or failure.</returns>
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;
}
}
}
浙公网安备 33010602011771号