直接复制下面的代码,可以使用:
public class SQLiteHelper
{
//public const string sConn = "Data Source=" + @"path";
/// <summary>
/// 数据库连接字符
/// </summary>
private static string strConn = ConfigurationManager.AppSettings["SQLiteConnectionString"];
/// <summary>
/// 执行增、删、改
/// 日期:2019年7月25日 17:54:54
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="parameters">可选参数</param>
/// <returns>返回int 得到受影响的行数</returns>
public static int ExecuteNonQuery(string sql, params SQLiteParameter[] parameters)
{
return ExecuteNonQuery(sql, CommandType.Text, parameters);
}
/// <summary>
/// 执行增、删、改
/// 日期:2019年7月25日 17:55:03
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="type">如何解释命令字符串</param>
/// <param name="parameters">可选参数</param>
/// <returns></returns>
public static int ExecuteNonQuery(string sql, CommandType type, params SQLiteParameter[] parameters)
{
using (SQLiteConnection conn = new SQLiteConnection(strConn))
{
conn.Open();
SQLiteCommand cmd = new SQLiteCommand(sql, conn);
cmd.CommandType = type;
cmd.Parameters.AddRange(parameters);
int num = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return num;
}
}
/// <summary>
/// 执行Insert语句,使用字典传参数 使用字典传参
/// 日期:2019年7月25日 17:55:11
/// </summary>
/// <param name="tbName">表名</param>
/// <param name="insertData">需要添加的数据</param>
/// <returns></returns>
public static int ExecuteInsert(string tbName, Dictionary<string, string> insertData)
{
int result = -1;
try
{
string point = "";//分隔符号(,)
string strkey = "";//字段凭借(id)
string kvStr = "";//值对拼接字符串(@id)
List<SQLiteParameter> param = new List<SQLiteParameter>();
foreach (string key in insertData.Keys)
{
kvStr += string.Format("{0}{1}", point, key);
strkey += string.Format("{0} @{1}", point, key);
param.Add(new SQLiteParameter("@" + key, insertData[key]));
point = ",";
}
string sql = string.Format("INSERT INTO {0}({1}) VALUES({2})", tbName, kvStr, strkey);
result = ExecuteNonQuery(sql, param.ToArray());
//return ExecuteNonQuery(sql, param.ToArray());
}
catch (Exception ex)
{
throw ex;
}
return result;
}
/// <summary>
/// 执行Update语句,使用字典传参数 参数化
/// 日期:2019年7月25日 17:55:19
/// </summary>
/// <param name="tbName">表名</param>
/// <param name="where">更新条件:id=1</param>
/// <param name="insertData">需要更新的数据</param>
/// <returns>受影响行数</returns>
public static int ExecuteUpdate(string tbName, string where, Dictionary<String, String> insertData)
{
int result = -1;
try
{
string point = "";//分隔符号(,)
string kvStr = "";//键值对拼接字符串(Id=@Id)
List<SQLiteParameter> param = new List<SQLiteParameter>();
foreach (string key in insertData.Keys)
{
kvStr += string.Format("{0} {1}=@{2}", point, key, key);
//SQL参数
param.Add(new SQLiteParameter("@" + key, insertData[key]));
point = ",";
}
string sql = string.Format("UPDATE {0} SET {1} WHERE {2}", tbName, kvStr, where);
result = ExecuteNonQuery(sql, param.ToArray());
//return ExecuteNonQuery(sql, param.ToArray());
}
catch (Exception ex)
{
throw ex;
}
return result;
}
#region 删除
/// <summary>
/// 按照ID删除 参数化
/// 日期:2019年7月29日 10:06:05
/// </summary>
/// <param name="tbName">表名</param>
/// <param name="param">SQL参数</param>
/// <returns></returns>
public static int ExecuteDeleteByID(string tbName, SQLiteParameter[] param)
{
string sql = string.Format("DELETE FROM {0} WHERE ID = @ID", tbName);
return ExecuteNonQuery(sql, param);
}
/// <summary>
/// 删除 自带条件查询,参数化
/// 日期:2019年8月5日 11:30:06
/// </summary>
/// <param name="tbName">数据表名</param>
/// <param name="parameterWhere">删除条件 例子:id = @id </param>
/// <param name="param">SQL参数</param>
/// <returns></returns>
public static int ExecuteDeleteBeltWhere(string tbName, string parameterWhere, SQLiteParameter[] param)
{
string sql = string.Format("DELETE FROM {0} WHERE {1}", tbName, parameterWhere);
return ExecuteNonQuery(sql, param);
}
/// <summary>
/// 执行Delete语句 未使用参数化
/// 日期:2019年7月25日 17:56:53
/// </summary>
/// <param name="tbName">表名</param>
/// <param name="where">需要删除的条件</param>
/// <returns></returns>
public static int ExecuteDeleteBeltWhere(string tbName, string where)
{
string sql = string.Format("DELETE FROM {0} WHERE {1}", tbName, where);
SQLiteParameter sqli = new SQLiteParameter();
return ExecuteNonQuery(sql, sqli);
}
#endregion
/// <summary>
/// 查询多行数据 参数化
/// 日期:2019年7月25日 17:57:19
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="param">sql参数</param>
/// <returns>一个表</returns>
public static DataTable ExecuteTable(string sql, params SQLiteParameter[] param)
{
DataTable dt = new DataTable();
using (SQLiteDataAdapter sda = new SQLiteDataAdapter(sql, strConn))
{
if (param != null)
{
sda.SelectCommand.Parameters.AddRange(param);
}
sda.Fill(dt);
}
return dt;
}
#region 查询所用信息
/// <summary>
/// 查询所有
/// 日期:2019年7月25日 17:57:39
/// </summary>
/// <param name="tbName">表名</param>
/// <param name="columnName">查询需要的字段名:"id, name, age"</param>
/// <param name="param">sql参数</param>
/// <returns></returns>
public static DataTable QueryAllTable(string tbName, string columnName, params SQLiteParameter[] param)
{
string sql = string.Format("SELECT {0} FROM {1}", columnName, tbName);
return SQLiteHelper.ExecuteTable(sql, param);
}
/// <summary>
/// 查询所有信息带分页
/// 日期:2019年8月5日 11:27:26
/// </summary>
/// <param name="tbName"></param>
/// <param name="columnName"></param>
/// <param name="pageindex"></param>
/// <param name="pagesize"></param>
/// <param name="param"></param>
/// <returns></returns>
public static DataTable QueryAllTable(string tbName, string columnName, int pageindex, int pagesize ,params SQLiteParameter[] param)
{
string sql = string.Format("SELECT {0} FROM {1} LIMIT {2},{3}", columnName, tbName, pageindex, pagesize);
return SQLiteHelper.ExecuteTable(sql, param);
}
/// <summary>
/// 查询所有信息带分页 带有排序
/// 日期:2019年8月6日 11:29:52
/// </summary>
/// <param name="tbName"></param>
/// <param name="columnName"></param>
/// <param name="pageindex"></param>
/// <param name="pagesize"></param>
/// <param name="orderby"></param>
/// <param name="param"></param>
/// <returns></returns>
public static DataTable QueryAllTable(string tbName, string columnName, int pageindex, int pagesize, string orderby,params SQLiteParameter[] param)
{
string sql = string.Format("SELECT {0} FROM {1} LIMIT {2},{3} {4}", columnName, tbName, pageindex, pagesize, orderby);
return SQLiteHelper.ExecuteTable(sql, param);
}
#endregion
#region 需要传 SQLiteParameter参数 参数不能为空
/// <summary>
/// 查询带有条件,不带有排序 参数化
/// 日期:2019年8月5日 11:28:31
/// </summary>
/// <param name="tbName">表名</param>
/// <param name="columnName">列名</param>
/// <param name="parameterWhere">参数化 条件</param>
/// <param name="param">sql参数</param>
/// <returns></returns>
public static DataTable QueryTableBeltWhere(string tbName, string columnName, string parameterWhere, SQLiteParameter[] param)
{
if (param == null || param.Length == 0)
{
return null;
}
else
{
string sql = string.Format("SELECT {0} FROM {1} WHERE {2}", columnName, tbName, parameterWhere);
return SQLiteHelper.ExecuteTable(sql, param);
}
}
/// <summary>
/// 查询带有条件,带有排序 参数化
/// 日期:2019年8月5日 11:28:42
/// </summary>
/// <param name="tbName"></param>
/// <param name="columnName"></param>
/// <param name="parameterWhere"></param>
/// <param name="parameterOrderBy"></param>
/// <param name="patam"></param>
/// <returns></returns>
public static DataTable QueryTableBeltWhere(string tbName, string columnName, string parameterWhere, string parameterOrderBy, SQLiteParameter[] param)
{
if (param == null || param.Length == 0)
{
return null;
}
else
{
string sql = string.Format("SELECT {0} FROM {1} WHERE {2} {3}", columnName, tbName, parameterWhere, parameterOrderBy);
return SQLiteHelper.ExecuteTable(sql, param);
}
}
#endregion
#region 可以传SQLiteParameter参数 也可以 不传SQLiteParameter 参数
/// <summary>
/// 查询带有条件 不带有排序
/// 日期:2019年7月25日 18:06:54
/// </summary>
/// <param name="tbName">表明</param>
/// <param name="columnName">查询需要的字段名:"id, name, age" </param>
/// <param name="strwhere">查询条件 id = 1</param>
/// <param name="param">sql参数</param>
/// <returns></returns>
public static DataTable QueryTableByWhere(string tbName, string columnName, string strwhere, params SQLiteParameter[] param)
{
string sql = string.Format("SELECT {0} FROM {1} WHERE {2}", columnName, tbName, strwhere);
return SQLiteHelper.ExecuteTable(sql, param);
}
/// <summary>
/// 查询带有条件 并带有排序
/// 日期:2019年7月25日 18:07:02
/// </summary>
/// <param name="tbName">表名</param>
/// <param name="columnName">查询需要的字段名:"id, name, age"</param>
/// <param name="strwhere">条件:"id = 1"</param>
/// <param name="orderBy">依据什么排序,并且是降序还是升序</param>
/// <param name="param">sql参数</param>
/// <returns></returns>
public static DataTable QueryTableByWhere(string tbName, string columnName, string strwhere, string orderBy, params SQLiteParameter[] param)
{
if (orderBy != "" || orderBy != null)
{
orderBy = "ORDER BY " + orderBy;//Deom: ORDER BY id desc
}
string sql = string.Format("SELECT {0} FROM {1} WHERE {2} {3}", columnName, tbName, strwhere, orderBy);
return SQLiteHelper.ExecuteTable(sql, param);
}
#endregion
#region 需要传 SQLiteParameter参数 参数不能为空
/// <summary>
/// 查询所有的数据并分页,不带条件 参数化 SQLiteParameter不能为空 如果为空返回空
/// 日期:2019年7月29日 15:42:27
/// </summary>
/// <param name="tbName"></param>
/// <param name="columnName"></param>
/// <param name="pageindex"></param>
/// <param name="pagesize"></param>
/// <param name="param"></param>
/// <returns></returns>
public static DataTable QueryTablePageing(string tbName, string columnName, int pageindex, int pagesize, SQLiteParameter[] param)
{
if (param == null || param.Length == 0)
{
return null;
}
else
{
string sql = string.Format("SELECT {0} FROM {1} LIMIT {2},{3}", columnName, tbName, pageindex, pagesize);
return SQLiteHelper.ExecuteTable(sql, param);
}
}
/// <summary>
/// 查询所有的数据 并分页 带有条件 参数化 SQLiteParameter不能为空 如果为空返回空
/// 日期:2019年8月5日 11:29:07
/// </summary>
/// <param name="tbName"></param>
/// <param name="columnName"></param>
/// <param name="parameterWhere"></param>
/// <param name="pageindex"></param>
/// <param name="pagesize"></param>
/// <param name="param"></param>
/// <returns></returns>
public static DataTable QueryTablePageing(string tbName, string columnName, string parameterWhere, int pageindex, int pagesize, SQLiteParameter[] param)
{
if (param == null || param.Length == 0)
{
return null;
}
else
{
string sql = string.Format("SELECT {0} FROM {1} WHERE {2} LIMIT {3},{4}", columnName, tbName, parameterWhere, pageindex, pagesize);
return SQLiteHelper.ExecuteTable(sql, param);
}
}
/// <summary>
/// 查询所有的数据,并分页,带有条件,并且有排序:升序 参数化 SQLiteParameter不能为空 如果为空返回空
/// 日期:2019年7月29日 17:50:11
/// </summary>
/// <param name="tbName"></param>
/// <param name="columnName"></param>
/// <param name="parameterWhere"></param>
/// <param name="sortColumn"></param>
/// <param name="pageindex"></param>
/// <param name="pagesize"></param>
/// <param name="param"></param>
/// <returns></returns>
public static DataTable QueryTablePageingAsc(string tbName, string columnName, string parameterWhere, string sortColumn, int pageindex, int pagesize, SQLiteParameter[] param)
{
if (param == null || param.Length == 0)
{
return null;
}
else
{
string sql = string.Format("SELECT {0} FROM {1} WHERE {2} ORDER BY {3} ASC LIMIT {4},{5}", columnName, tbName, parameterWhere, sortColumn, pageindex, pagesize);
return SQLiteHelper.ExecuteTable(sql, param);
}
}
/// <summary>
/// 查询所有的数据,并分页,带有条件,并且有排序:降序 参数化 SQLiteParameter不能为空 如果为空返回空
/// 日期:2019年8月5日 11:29:20
/// </summary>
/// <param name="tbName"></param>
/// <param name="columnName"></param>
/// <param name="strwhere"></param>
/// <param name="orderBy"></param>
/// <param name="pageindex"></param>
/// <param name="pagesize"></param>
/// <param name="param"></param>
/// <returns></returns>
public static DataTable QueryTablePageingDesc(string tbName, string columnName, string strwhere, string orderBy, int pageindex, int pagesize, SQLiteParameter[] param)
{
if (param == null || param.Length == 0)
{
return null;
}
else
{
string sql = string.Format("SELECT {0} FROM {1} WHERE {2} ORDER BY @{3} ASC LIMIT {4},{5}", columnName, tbName, strwhere, orderBy, pageindex, pagesize);
return SQLiteHelper.ExecuteTable(sql, param);
}
}
#endregion
#region 可以传SQLiteParameter参数 也可以 不传SQLiteParameter 参数
/// <summary>
/// 查询所有的数据 并分页 不带条件
/// 日期:2019年7月25日 18:08:39
/// </summary>
/// <param name="tbName">表名</param>
/// <param name="columnName">查询需要的字段名:"id, name, age"</param>
/// <param name="pageindex">显示的起始位置</param>
/// <param name="pagesize">当前页的大小</param>
/// <param name="param">sql参数</param>
/// <returns></returns>
public static DataTable QueryTablePage(string tbName, string columnName, int pageindex, int pagesize, params SQLiteParameter[] param)
{
string sql = string.Format("SELECT {0} FROM {1} LIMIT {2},{3}", columnName, tbName, pageindex, pagesize);
//return sql;
return SQLiteHelper.ExecuteTable(sql, param);
}
/// <summary>
/// 查询所有的数据 并分页 带有条件
/// 日期:2019年7月25日 18:08:45
/// </summary>
/// <param name="tbName">表名</param>
/// <param name="columnName">查询需要的字段名:"id, name, age"</param>
/// <param name="strwhere">查询条件:"id = 1"</param>
/// <param name="pageindex">显示的起始位置</param>
/// <param name="pagesize">当前页的大小</param>
/// <param name="param">sql参数</param>
/// <returns></returns>
public static DataTable QueryTablePage(string tbName, string columnName, string strwhere, int pageindex, int pagesize, params SQLiteParameter[] param)
{
string sql = string.Format("SELECT {0} FROM {1} WHERE {2} LIMIT {3},{4}", columnName, tbName, strwhere, pageindex, pagesize);
//return sql;
return SQLiteHelper.ExecuteTable(sql, param);
}
/// <summary>
/// 查询所有的数据 并分页 带有条件 并且有排序 升序
/// 日期:2019年7月25日 18:08:51
/// </summary>
/// <param name="tbName">表名</param>
/// <param name="columnName">查询需要的字段名:"id, name, age"</param>
/// <param name="strwhere">查询条件:"id = 1"</param>
/// <param name="orderBy">排序列名</param>
/// <param name="pageindex">显示的起始位置</param>
/// <param name="pagesize">当前页的大小</param>
/// <param name="param">sql参数</param>
/// <returns></returns>
public static DataTable QueryTablePageAsc(string tbName, string columnName, string strwhere, string orderBy, int pageindex, int pagesize, params SQLiteParameter[] param)
{
//排序
if (orderBy != "")
{
orderBy = "ORDER BY " + orderBy + " ASC";//Deom: ORDER BY id ASC
}
string sql = string.Format("SELECT {0} FROM {1} WHERE {2} {3} LIMIT {4},{5}", columnName, tbName, strwhere, orderBy, pageindex, pagesize);
//return sql;
return SQLiteHelper.ExecuteTable(sql, param);
}
/// <summary>
/// 查询所有的数据 并分页 带有条件 并且有排序 降序
/// 日期:2019年7月25日 18:09:01
/// </summary>
/// <param name="tbName">表名</param>
/// <param name="columnName">查询需要的字段名:"id, name, age"</param>
/// <param name="strwhere">查询条件:"id = 1"</param>
/// <param name="orderBy">排序列名</param>
/// <param name="pageindex">显示的起始位置</param>
/// <param name="pagesize">当前页的大小</param>
/// <param name="param">sql参数</param>
/// <returns></returns>
public static DataTable QueryTablePageDesc(string tbName, string columnName, string strwhere, string orderBy, int pageindex, int pagesize, params SQLiteParameter[] param)
{
//排序
if (orderBy != "")
{
orderBy = "ORDER BY " + orderBy + " DESC";//Deom: ORDER BY id DESC
}
string sql = string.Format("SELECT {0} FROM {1} WHERE {2} {3} LIMIT {4},{5}", columnName, tbName, strwhere, orderBy, pageindex, pagesize);
//return sql;
return SQLiteHelper.ExecuteTable(sql, param);
}
#endregion
}