using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;
using System.Linq;
using System.Web;
using WHC.Framework.Commons;
namespace Test.Areas.HelpPage
{
public class SqliteHelper
{
private static string connectionString = GetconnString();//ConfigurationManager.ConnectionStrings["sqlite"].ToString();
private static string GetconnString()
{
string s = ConfigurationManager.ConnectionStrings["sqlite"].ToString();
if (s.Contains("Password"))
{
string[] ss = s.Split(';');
s = ss[0] + ";" + ss[1] + ";" + "Password=" + EncodeHelper.AES_Decrypt(ss[2].Replace("Password=", "")) + ";";
}
return s;
}
/// <summary>
/// 对SQLite数据库执行增删改操作,返回受影响的行数。
/// </summary>
/// <param name="sql">要执行的增删改的SQL语句</param>
/// <param name="parameters">执行增删改语句的参数</param>
/// <returns></returns>
public static int ExecuteNonQuery(string sql, params SQLiteParameter[] parameters)
{
int affectedRows = 0;
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
connection.Open();
using (DbTransaction transaction = connection.BeginTransaction())
{
using (SQLiteCommand command = new SQLiteCommand(connection))
{
command.CommandText = sql;
if (parameters != null)
{
command.Parameters.AddRange(parameters);
}
affectedRows = command.ExecuteNonQuery();
}
transaction.Commit();
}
}
return affectedRows;
}
/// <summary>
/// 执行一个查询语句,返回SQLiteDataReader实例
/// </summary>
/// <param name="sql">要执行的查询语句</param>
/// <param name="parameters">执行查询语句的参数</param>
/// <returns></returns>
public static SQLiteDataReader ExecuteReader(string sql, SQLiteParameter[] parameters)
{
SQLiteConnection connection = new SQLiteConnection(connectionString);
SQLiteCommand command = new SQLiteCommand(sql, connection);
if (parameters != null)
{
command.Parameters.AddRange(parameters);
}
connection.Open();
return command.ExecuteReader(CommandBehavior.CloseConnection);
}
/// <summary>
/// 执行一个查询语句,返回一个包含查询结果的DataTable
/// </summary>
/// <param name="sql">要执行的查询语句</param>
/// <param name="parameters">执行查询语句的参数</param>
/// <returns></returns>
public static DataTable ExecuteDataTable(string sql, params SQLiteParameter[] parameters)
{
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
using (SQLiteCommand command = new SQLiteCommand(sql, connection))
{
if (parameters != null)
{
command.Parameters.AddRange(parameters);
}
SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
DataTable data = new DataTable();
adapter.Fill(data);
return data;
}
}
}
/// <summary>
/// 执行一个查询语句,返回查询结果的第一行第一列
/// </summary>
/// <param name="sql">要执行的查询语句</param>
/// <param name="parameters">执行SQL查询语句所需要的参数,参数必须以它们在SQL语句中的顺序为准</param>
/// <returns></returns>
public static Object ExecuteScalar(string sql, params SQLiteParameter[] parameters)
{
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
using (SQLiteCommand command = new SQLiteCommand(sql, connection))
{
if (parameters != null)
{
command.Parameters.AddRange(parameters);
}
SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
DataTable data = new DataTable();
adapter.Fill(data);
return data;
}
}
}
}
}