玄机论坛-专业的C#交流论坛 交流QQ群: 16885911如有疑问,请加入官方群询问
您需要 登录 才可以下载或查看,没有帐号?关闭注册
x
基于官方1.0.94驱动封装的SqLiteHelper.
实现增,删,改,查;等基本功能
使用前先初始化连接通道字符串Conn
例:
[mw_shl_code=csharp,true]string sqlitepath = AppDomain.CurrentDomain.BaseDirectory + "cache.db";//设定库路径
SQLiteHelper sql = new SQLiteHelper(); //声明对象
sql.strConn = string.Format("Data Source={0}ooling=true;FailIfMissing=false;password={1}", sqlitepath, "填写密码");[/mw_shl_code]
查询结果集例子:
[mw_shl_code=csharp,true] using(SQLiteDataReader reader = sql.ExecuteReader("select Name from 表名", null))
{
while (reader.HasRows) //是否存在数据
{
if (reader.Read()) //是否可读取
{
//读取字段信息
string name =reader["Name"].ToString();
}
}
}[/mw_shl_code]
带参数查询,避免SQL注入的例子:
[mw_shl_code=csharp,true] string uname = "admin";
string pwd="admin";
string sqlstr = "select count(*) from admin where Uname=@Uname and Pwds=@Pwds"; //sql语句
int i = (int)sql.ExecuteScalar(sqlstr, new SQLiteParameter[] { new SQLiteParameter("@Uname", uname), new SQLiteParameter("@Pwds", pwd) });[/mw_shl_code]
这里判断i是否大于0即可
[mw_shl_code=csharp,true]using System.Data;
using System.Data.SQLite;
namespace XuanJiSQLiteHelper
{
public class SQLiteHelper
{
/// <summary>
/// 创建链接字符串
public string strConn = string.Empty;
#region 执行Sql语句,增删改
/// <summary>
/// 执行Sql语句,增删改
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="parms">参数</param>
/// <returns>影响行数</returns>
public int ExecuteNonQuery(string sql, params SQLiteParameter[] parms)
{
using (SQLiteConnection conn = new SQLiteConnection(strConn))
{
using (SQLiteCommand cmd = new SQLiteCommand(sql, conn))
{
if (parms != null)
{
cmd.Parameters.AddRange(parms);
}
conn.Open();
return cmd.ExecuteNonQuery();
}
}
}
#endregion
#region 执行Sql语句,1个返回值
/// <summary>
/// 查询方法
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="parms">sql参数</param>
/// <returns> 返回第一行第一列数据 </returns>
public object ExecuteScalar(string sql, params SQLiteParameter[] parms)
{
using (SQLiteConnection conn = new SQLiteConnection(strConn))
{
using (SQLiteCommand cmd = new SQLiteCommand(sql, conn))
{
if (parms != null)
{
cmd.Parameters.AddRange(parms);
}
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
return cmd.ExecuteScalar();
}
}
}
#endregion
#region 执行sql语句,返回结果集
/// <summary>
/// 执行sql语句,返回结果集
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="parms">参数</param>
/// <returns></returns>
public SQLiteDataReader ExecuteReader(string sql, params SQLiteParameter[] parms)
{
using (SQLiteConnection conn = new SQLiteConnection(strConn))
{
using (SQLiteCommand cmd = new SQLiteCommand(sql, conn))
{
if (parms != null)
{
cmd.Parameters.AddRange(parms);
}
if (conn.State != ConnectionState.Open)
{
conn.Open(); //非打开状态时,打开数据库
}
return cmd.ExecuteReader(CommandBehavior.CloseConnection);//当SQLiteDataReader释放时,释放连接
}
}
}
#endregion
}
}执行事务:
[mw_shl_code=csharp,true] /// <summary>
/// 多语句事务
/// </summary>
/// <param name="sql">sql对象集合</param>
/// <returns></returns>
public bool ExecTransaction(List<SqlObject> sql)
{
using (SQLiteConnection conn = new SQLiteConnection(strConn))
{
using (DbTransaction transaction = conn.BeginTransaction())
{
int o = 0;
using (SQLiteCommand command = new SQLiteCommand(conn))
{
for (int i = 0; i < sql.Count; i++)
{
command.CommandText = sql.sqlText;
if (sql.paramList != null)
{
command.Parameters.AddRange(sql.paramList.ToArray());
}
}
o += (int)command.ExecuteNonQuery();
command.Parameters.Clear();
if (o >= 0)
{
transaction.Commit();
return true;
}
else
{
transaction.Rollback();
return false;
}
}
}
}
}
}
public class SqlObject
{
/// <summary>
/// sql语句
/// </summary>
public string sqlText { get; set; }
/// <summary>
/// 参数列表
/// </summary>
public List<SQLiteParameter> paramList = new List<SQLiteParameter>();
}[/mw_shl_code]
[/mw_shl_code]下载地址
SQLiteHelper.zip
(478.56 KB, 下载次数: 5381)
|