在客户端配置文件f9d9f4a8f32d97f3ef0c10742ed31240节点下，添加：
<connectionstrings>
<add name="localdb" connectionstring="data source=config/local.db;version=3;useutf16encoding=true;" providername="system.data.sqlite.sqlitefactory"/>
</connectionstrings>
其中【localdb】是本地sqlite数据库的名称，【config/local.db】是在当前程序运行目录下sqlite数据库位置
c# sqlite数据库 访问封装类代码：
/// <summary>
/// 本类为sqlite数据库帮助静态类,使用时只需直接调用即可,无需实例化
/// </summary>
public static class sqlitehelper
{
// application.startuppath
public static string localdbconnectionstring = configurationmanager.connectionstrings["localdb"].connectionstring;
#region executenonquery
/// <summary>
/// 执行数据库操作(新增、更新或删除)
/// </summary>
/// <param name="connectionstring">连接字符串</param>
/// <param name="cmd">sqlcommand对象</param>
/// <returns>所受影响的行数</returns>
public static int executenonquery(string connectionstring, sqlitecommand cmd)
{
int result = 0;
if (connectionstring == null || connectionstring.length == 0)
throw new argumentnullexception("connectionstring");
using (sqliteconnection con = new sqliteconnection(connectionstring))
{
sqlitetransaction trans = null;
preparecommand(cmd, con, ref trans, true, cmd.commandtype, cmd.commandtext);
try
{
result = cmd.executenonquery();
trans.commit();
}
catch (exception ex)
{
trans.rollback();
throw ex;
}
}
return result;
}
/// <summary>
/// 执行数据库操作(新增、更新或删除)
/// </summary>
/// <param name="connectionstring">连接字符串</param>
/// <param name="commandtext">执行语句或存储过程名</param>
/// <param name="commandtype">执行类型</param>
/// <returns>所受影响的行数</returns>
public static int executenonquery(string connectionstring, string commandtext, commandtype commandtype)
{
int result = 0;
if (connectionstring == null || connectionstring.length == 0)
throw new argumentnullexception("connectionstring");
if (commandtext == null || commandtext.length == 0)
throw new argumentnullexception("commandtext");
sqlitecommand cmd = new sqlitecommand();
using (sqliteconnection con = new sqliteconnection(connectionstring))
{
sqlitetransaction trans = null;
preparecommand(cmd, con, ref trans, true, commandtype, commandtext);
try
{
result = cmd.executenonquery();
trans.commit();
}
catch (exception ex)
{
trans.rollback();
throw ex;
}
}
return result;
}
/// <summary>
/// 执行数据库操作(新增、更新或删除)
/// </summary>
/// <param name="connectionstring">连接字符串</param>
/// <param name="commandtext">执行语句或存储过程名</param>
/// <param name="commandtype">执行类型</param>
/// <param name="cmdparms">sql参数对象</param>
/// <returns>所受影响的行数</returns>
public static int executenonquery(string connectionstring, string commandtext, commandtype commandtype, params sqliteparameter[] cmdparms)
{
int result = 0;
if (connectionstring == null || connectionstring.length == 0)
throw new argumentnullexception("connectionstring");
if (commandtext == null || commandtext.length == 0)
throw new argumentnullexception("commandtext");
sqlitecommand cmd = new sqlitecommand();
using (sqliteconnection con = new sqliteconnection(connectionstring))
{
sqlitetransaction trans = null;
preparecommand(cmd, con, ref trans, true, commandtype, commandtext);
try
{
result = cmd.executenonquery();
trans.commit();
}
catch (exception ex)
{
trans.rollback();
throw ex;
}
}
return result;
}
#endregion
#region executescalar
/// <summary>
/// 执行数据库操作(新增、更新或删除)同时返回执行后查询所得的第1行第1列数据
/// </summary>
/// <param name="connectionstring">连接字符串</param>
/// <param name="cmd">sqlcommand对象</param>
/// <returns>查询所得的第1行第1列数据</returns>
public static object executescalar(string connectionstring, sqlitecommand cmd)
{
object result = 0;
if (connectionstring == null || connectionstring.length == 0)
throw new argumentnullexception("connectionstring");
using (sqliteconnection con = new sqliteconnection(connectionstring))
{
sqlitetransaction trans = null;
preparecommand(cmd, con, ref trans, true, cmd.commandtype, cmd.commandtext);
try
{
result = cmd.executescalar();
trans.commit();
}
catch (exception ex)
{
trans.rollback();
throw ex;
}
}
return result;
}
/// <summary>
/// 执行数据库操作(新增、更新或删除)同时返回执行后查询所得的第1行第1列数据
/// </summary>
/// <param name="connectionstring">连接字符串</param>
/// <param name="commandtext">执行语句或存储过程名</param>
/// <param name="commandtype">执行类型</param>
/// <returns>查询所得的第1行第1列数据</returns>
public static object executescalar(string connectionstring, string commandtext, commandtype commandtype)
{
object result = 0;
if (connectionstring == null || connectionstring.length == 0)
throw new argumentnullexception("connectionstring");
if (commandtext == null || commandtext.length == 0)
throw new argumentnullexception("commandtext");
sqlitecommand cmd = new sqlitecommand();
using (sqliteconnection con = new sqliteconnection(connectionstring))
{
sqlitetransaction trans = null;
preparecommand(cmd, con, ref trans, true, commandtype, commandtext);
try
{
result = cmd.executescalar();
trans.commit();
}
catch (exception ex)
{
trans.rollback();
throw ex;
}
}
return result;
}
/// <summary>
/// 执行数据库操作(新增、更新或删除)同时返回执行后查询所得的第1行第1列数据
/// </summary>
/// <param name="connectionstring">连接字符串</param>
/// <param name="commandtext">执行语句或存储过程名</param>
/// <param name="commandtype">执行类型</param>
/// <param name="cmdparms">sql参数对象</param>
/// <returns>查询所得的第1行第1列数据</returns>
public static object executescalar(string connectionstring, string commandtext, commandtype commandtype, params sqliteparameter[] cmdparms)
{
object result = 0;
if (connectionstring == null || connectionstring.length == 0)
throw new argumentnullexception("connectionstring");
if (commandtext == null || commandtext.length == 0)
throw new argumentnullexception("commandtext");
sqlitecommand cmd = new sqlitecommand();
using (sqliteconnection con = new sqliteconnection(connectionstring))
{
sqlitetransaction trans = null;
preparecommand(cmd, con, ref trans, true, commandtype, commandtext);
try
{
result = cmd.executescalar();
trans.commit();
}
catch (exception ex)
{
trans.rollback();
throw ex;
}
}
return result;
}
#endregion
#region executereader
/// <summary>
/// 执行数据库查询，返回sqldatareader对象
/// </summary>
/// <param name="connectionstring">连接字符串</param>
/// <param name="cmd">sqlcommand对象</param>
/// <returns>sqldatareader对象</returns>
public static dbdatareader executereader(string connectionstring, sqlitecommand cmd)
{
dbdatareader reader = null;
if (connectionstring == null || connectionstring.length == 0)
throw new argumentnullexception("connectionstring");
sqliteconnection con = new sqliteconnection(connectionstring);
sqlitetransaction trans = null;
preparecommand(cmd, con, ref trans, false, cmd.commandtype, cmd.commandtext);
try
{
reader = cmd.executereader(commandbehavior.closeconnection);
}
catch (exception ex)
{
throw ex;
}
return reader;
}
/// <summary>
/// 执行数据库查询，返回sqldatareader对象
/// </summary>
/// <param name="connectionstring">连接字符串</param>
/// <param name="commandtext">执行语句或存储过程名</param>
/// <param name="commandtype">执行类型</param>
/// <returns>sqldatareader对象</returns>
public static dbdatareader executereader(string connectionstring, string commandtext, commandtype commandtype)
{
dbdatareader reader = null;
if (connectionstring == null || connectionstring.length == 0)
throw new argumentnullexception("connectionstring");
if (commandtext == null || commandtext.length == 0)
throw new argumentnullexception("commandtext");
sqliteconnection con = new sqliteconnection(connectionstring);
sqlitecommand cmd = new sqlitecommand();
sqlitetransaction trans = null;
preparecommand(cmd, con, ref trans, false, commandtype, commandtext);
try
{
reader = cmd.executereader(commandbehavior.closeconnection);
}
catch (exception ex)
{
throw ex;
}
return reader;
}
/// <summary>
/// 执行数据库查询，返回sqldatareader对象
/// </summary>
/// <param name="connectionstring">连接字符串</param>
/// <param name="commandtext">执行语句或存储过程名</param>
/// <param name="commandtype">执行类型</param>
/// <param name="cmdparms">sql参数对象</param>
/// <returns>sqldatareader对象</returns>
public static dbdatareader executereader(string connectionstring, string commandtext, commandtype commandtype, params sqliteparameter[] cmdparms)
{
dbdatareader reader = null;
if (connectionstring == null || connectionstring.length == 0)
throw new argumentnullexception("connectionstring");
if (commandtext == null || commandtext.length == 0)
throw new argumentnullexception("commandtext");
sqliteconnection con = new sqliteconnection(connectionstring);
sqlitecommand cmd = new sqlitecommand();
sqlitetransaction trans = null;
preparecommand(cmd, con, ref trans, false, commandtype, commandtext, cmdparms);
try
{
reader = cmd.executereader(commandbehavior.closeconnection);
}
catch (exception ex)
{
throw ex;
}
return reader;
}
#endregion
#region executedataset
/// <summary>
/// 执行数据库查询，返回dataset对象
/// </summary>
/// <param name="connectionstring">连接字符串</param>
/// <param name="cmd">sqlcommand对象</param>
/// <returns>dataset对象</returns>
public static dataset executedataset(string connectionstring, sqlitecommand cmd)
{
dataset ds = new dataset();
sqliteconnection con = new sqliteconnection(connectionstring);
sqlitetransaction trans = null;
preparecommand(cmd, con, ref trans, false, cmd.commandtype, cmd.commandtext);
try
{
sqlitedataadapter sda = new sqlitedataadapter(cmd);
sda.fill(ds);
}
catch (exception ex)
{
throw ex;
}
finally
{
if (cmd.connection != null)
{
if (cmd.connection.state == connectionstate.open)
{
cmd.connection.close();
}
}
}
return ds;
}
/// <summary>
/// 执行数据库查询，返回dataset对象
/// </summary>
/// <param name="connectionstring">连接字符串</param>
/// <param name="commandtext">执行语句或存储过程名</param>
/// <param name="commandtype">执行类型</param>
/// <returns>dataset对象</returns>
public static dataset executedataset(string connectionstring, string commandtext, commandtype commandtype)
{
if (connectionstring == null || connectionstring.length == 0)
throw new argumentnullexception("connectionstring");
if (commandtext == null || commandtext.length == 0)
throw new argumentnullexception("commandtext");
dataset ds = new dataset();
sqliteconnection con = new sqliteconnection(connectionstring);
sqlitecommand cmd = new sqlitecommand();
sqlitetransaction trans = null;
preparecommand(cmd, con, ref trans, false, commandtype, commandtext);
try
{
sqlitedataadapter sda = new sqlitedataadapter(cmd);
sda.fill(ds);
}
catch (exception ex)
{
throw ex;
}
finally
{
if (con != null)
{
if (con.state == connectionstate.open)
{
con.close();
}
}
}
return ds;
}
/// <summary>
/// 执行数据库查询，返回dataset对象
/// </summary>
/// <param name="connectionstring">连接字符串</param>
/// <param name="commandtext">执行语句或存储过程名</param>
/// <param name="commandtype">执行类型</param>
/// <param name="cmdparms">sql参数对象</param>
/// <returns>dataset对象</returns>
public static dataset executedataset(string connectionstring, string commandtext, commandtype commandtype, params sqliteparameter[] cmdparms)
{
if (connectionstring == null || connectionstring.length == 0)
throw new argumentnullexception("connectionstring");
if (commandtext == null || commandtext.length == 0)
throw new argumentnullexception("commandtext");
dataset ds = new dataset();
sqliteconnection con = new sqliteconnection(connectionstring);
sqlitecommand cmd = new sqlitecommand();
sqlitetransaction trans = null;
preparecommand(cmd, con, ref trans, false, commandtype, commandtext, cmdparms);
try
{
sqlitedataadapter sda = new sqlitedataadapter(cmd);
sda.fill(ds);
}
catch (exception ex)
{
throw ex;
}
finally
{
if (con != null)
{
if (con.state == connectionstate.open)
{
con.close();
}
}
}
return ds;
}
#endregion
#region 通用分页查询方法
/// <summary>
/// 通用分页查询方法
/// </summary>
/// <param name="connstring">连接字符串</param>
/// <param name="tablename">表名</param>
/// <param name="strcolumns">查询字段名</param>
/// <param name="strwhere">where条件</param>
/// <param name="strorder">排序条件</param>
/// <param name="pagesize">每页数据数量</param>
/// <param name="currentindex">当前页数</param>
/// <param name="recordout">数据总量</param>
/// <returns>datatable数据表</returns>
public static datatable selectpaging(string connstring, string tablename, string strcolumns, string strwhere, string strorder, int pagesize, int currentindex, out int recordout)
{
datatable dt = new datatable();
recordout = convert.toint32(executescalar(connstring, "select count(*) from " + tablename, commandtype.text));
string pagingtemplate = "select {0} from {1} where {2} order by {3} limit {4} offset {5} ";
int offsetcount = (currentindex - 1) * pagesize;
string commandtext = string.format(pagingtemplate, strcolumns, tablename, strwhere, strorder, pagesize.tostring(), offsetcount.tostring());
using (dbdatareader reader = executereader(connstring, commandtext, commandtype.text))
{
if (reader != null)
{
dt.load(reader);
}
}
return dt;
}
#endregion
#region 预处理command对象,数据库链接,事务,需要执行的对象,参数等的初始化
/// <summary>
/// 预处理command对象,数据库链接,事务,需要执行的对象,参数等的初始化
/// </summary>
/// <param name="cmd">command对象</param>
/// <param name="conn">connection对象</param>
/// <param name="trans">transcation对象</param>
/// <param name="usetrans">是否使用事务</param>
/// <param name="cmdtype">sql字符串执行类型</param>
/// <param name="cmdtext">sql text</param>
/// <param name="cmdparms">sqliteparameters to use in the command</param>
private static void preparecommand(sqlitecommand cmd, sqliteconnection conn, ref sqlitetransaction trans, bool usetrans, commandtype cmdtype, string cmdtext, params sqliteparameter[] cmdparms)
{
if (conn.state != connectionstate.open)
conn.open();
cmd.connection = conn;
cmd.commandtext = cmdtext;
if (usetrans)
{
trans = conn.begintransaction(isolationlevel.readcommitted);
cmd.transaction = trans;
}
cmd.commandtype = cmdtype;
if (cmdparms != null)
{
foreach (sqliteparameter parm in cmdparms)
cmd.parameters.add(parm);
}
}
#endregion
}
使用demo：
/// <summary>
/// 获取数据库关键字信息
/// </summary>
/// <param name="category">分类</param>
/// <param name="versions">版本</param>
/// <returns></returns>
private dataset getsystemdatabasekeywords(string category, string versions)
{
stringbuilder sql = new stringbuilder();
sql.append("select keywords , versions , type , description , category , id , extends ");
sql.append(" from a_databasekeywords ");
sql.appendformat(" where 1={0} ", "1");
if (!string.isnullorempty(category))
{
sql.appendformat(" and category='{0}'", category);
}
if (!string.isnullorempty(versions))
{
sql.appendformat(" and versions='{0}'", versions);
}
return sqlitehelper.executedataset(sqlitehelper.localdbconnectionstring, sql.tostring(), commandtype.text);
}
以上就是c# sqlite数据库 访问封装类的内容。