using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using System.Text;
namespace SqliteHelper
{
public class SqliteHelper
{
#region 全局变量
private string path;
private SQLiteConnection conn;
private static SqliteHelper instance;
public static SqliteHelper Instance
{
get { return GetInstance(); }
set { SqliteHelper.instance = value; }
}
private static Dictionary<object, object> Sessions = new Dictionary<object, object>();
#endregion
#region 单例
private static SqliteHelper GetInstance()
{
if (instance == null)
instance = new SqliteHelper();
return instance;
}
#endregion
#region 初始化
public void Init()
{
InitPath();
InitConn();
}
private void InitPath()
{
if (string.IsNullOrEmpty(path))
throw new NullReferenceException("请先指定数据库文件路径！");
if (!File.Exists(path))
SQLiteConnection.CreateFile(path);
}
private void InitConn()
{
conn = new SQLiteConnection(string.Format("Data Source = {0};version = 3", path));
}
public void SetDataSourcePath(string path)
{
this.path = path;
}
#endregion
#region 建表、删表
public bool CreateTable(string tableName, params string[][] parameters)
{
InitConn();
var isExists = IsExists(tableName);
if (isExists)
DropTable(tableName);
//校验数据库是否存在
if (parameters == null)
throw new NullReferenceException("参数不能为空！");
var list = new List<string>();
foreach (var parameter in parameters)
list.Add(string.Join(" ", parameter));
var sql = string.Format("create table {0}({1})", tableName, string.Join(",", list));
return ExecuteNonQuery(sql);
}
public void DropTable(string tableName)
{
InitConn();
var sql = "Drop table " + tableName;
using (conn)
{
conn.Open();
using (var cmd = new SQLiteCommand(sql, conn))
{
cmd.ExecuteNonQuery();
}
}
}
public void DropDataSource()
{
if (string.IsNullOrEmpty(path) || !File.Exists(path))
throw new NullReferenceException("文件未找到！");
File.Delete(path);
}
#endregion
#region 增删查改
public bool Insert(string tableName, params string[] parameters)
{
if (parameters == null)
throw new NullReferenceException("数据不能为空！");
InitConn();
var sql = string.Format("insert into {0} values('{1}')", tableName, string.Join("','", parameters));
return ExecuteNonQuery(sql);
}
public bool Delete(string tableName, params string[] parameters)
{
if (parameters == null || parameters.Length != 2)
throw new NullReferenceException("参数错误！");
InitConn();
var _parameters = new List<string>();
_parameters.Add(tableName);
_parameters.AddRange(parameters);
var sql = string.Format("Delete {0} where {1} = '{2}'", _parameters.ToArray());
return ExecuteNonQuery(sql);
}
public bool IsExists(string tableName)
{
var sql = "select count(1) from sqlite_master where name=@name";
var sqliteparameters = new[] { new SQLiteParameter("@name", tableName) };
return int.Parse(GetOnly(sql, sqliteparameters).ToString()) != 0;
}
public bool IsExists(string tableName, params Parameter[] parameters)
{
if (parameters == null || parameters.Length == 0)
throw new NullReferenceException("没有参数！");
var table = GetTable(tableName, parameters);
return table != null && table.Rows != null && table.Rows.Count > 0;
}
public DataTable GetTable(string tableName, params Parameter[] parameters)
{
var sql = new StringBuilder();
var sqliteparameters = new List<SQLiteParameter>();
sql.Append(string.Format("select * from {0} where 1 = 1 ", tableName));
if (parameters != null)
foreach (var parameter in parameters)
{
var sqliteParameter = new SQLiteParameter("@" + parameter.parameterName, parameter.parameterValue);
var _sql = string.Empty;
sqliteparameters.Add(sqliteParameter);
switch (parameter.selectMode)
{
case SelectMode.AndNoequal:
_sql = "and {0} <> @{0} ";
break;
case SelectMode.AndFirstLike:
_sql = "and {0} like '%" + sqliteParameter.Value + " ";
sqliteparameters.Remove(sqliteParameter);
break;
case SelectMode.AndLastLike:
_sql = "and {0} like '" + sqliteParameter.Value + "%' ";
sqliteparameters.Remove(sqliteParameter);
break;
case SelectMode.AndLike:
_sql = "and {0} like '%" + sqliteParameter.Value + "%' ";
sqliteparameters.Remove(sqliteParameter);
break;
case SelectMode.OrEqual:
_sql = "or {0} = @{0} ";
break;
case SelectMode.OrNoequal:
_sql = "or {0} <> @{0} ";
break;
case SelectMode.OrFirstLike:
_sql = "or {0} like '%" + sqliteParameter.Value + "' ";
sqliteparameters.Remove(sqliteParameter);
break;
case SelectMode.OrLastLike:
_sql = "and {0} like '" + sqliteParameter.Value + "%' ";
sqliteparameters.Remove(sqliteParameter);
break;
case SelectMode.OrLike:
_sql = "and like '" + sqliteParameter.Value + "%' ";
sqliteparameters.Remove(sqliteParameter);
break;
default://AndEqual
_sql = "and {0} = @{0} ";
break;
}
sql.Append(string.Format(_sql, parameter.parameterName));
}
return GetTable(sql.ToString(), sqliteparameters.ToArray());
}
public object GetOnly(string sql, params SQLiteParameter[] sqliteparameters)
{
InitConn();
using (conn)
{
conn.Open();
using (var cmd = new SQLiteCommand(sql, conn))
{
if (sqliteparameters != null)
cmd.Parameters.AddRange(sqliteparameters);
return cmd.ExecuteScalar();
}
}
}
public bool Update(string tableName, params string[] parameters)
{
if (parameters == null || parameters.Length != 4)
throw new NullReferenceException("参数错误！");
var _parameters = new List<string>();
_parameters.Add(tableName);
_parameters.AddRange(parameters);
var sql = string.Format("Update {0} set {3} = '{4}' where {1} = '{2}'", _parameters.ToArray());
return ExecuteNonQuery(sql);
}
public bool ExecuteNonQuery(string sql, params SQLiteParameter[] sqliteparameters)
{
InitConn();
using (conn)
{
conn.Open();
using (var cmd = new SQLiteCommand(sql, conn))
{
if (sqliteparameters != null)
cmd.Parameters.AddRange(sqliteparameters);
return cmd.ExecuteNonQuery() > 0;
}
}
}
public DataTable GetTable(string sql, params SQLiteParameter[] sqliteparameters)
{
InitConn();
using (conn)
{
conn.Open();
using (var cmd = new SQLiteCommand(sql, conn))
{
if (sqliteparameters != null)
cmd.Parameters.AddRange(sqliteparameters.ToArray());
var ds = new DataSet();
var da = new SQLiteDataAdapter(cmd);
try
{
da.Fill(ds);
if (ds.Tables == null)
throw new IndexOutOfRangeException();
return ds.Tables[0];
}
catch (IndexOutOfRangeException)
{
return new DataTable();
}
catch (SQLiteException)
{
return null;
}
}
}
}
#endregion
#region 附
public string[] GetAllTables()
{
var sql = "select * from sqlite_master";
var table = GetTable(sql, default(SQLiteParameter[]));
var tables = new List<string>();
foreach (DataRow row in table.Rows)
tables.Add(row[1].ToString());
return tables.ToArray();
}
public string[] GetAllColumnsByTableName(string tableName)
{
var sql = string.Format("pragma table_info({0})", tableName);
var table = GetTable(sql, default(SQLiteParameter[]));
var columns = new List<string>();
foreach (DataRow row in table.Rows)
columns.Add(row[1].ToString());
return columns.ToArray();
}
public void SetSession(object key, object value)
{
if (!Sessions.ContainsKey(key))
Sessions.Add(key, null);
Sessions[key] = value;
}
public object GetSessionByKey(object key)
{
if (!Sessions.ContainsKey(key))
return null;
return Sessions[key];
}
public bool CheckConnection()
{
return !string.IsNullOrEmpty(path);
}
public object Execute(string sql, params SQLiteParameter[] sqliteparameters)
{
InitConn();
using (conn)
{
conn.Open();
using (var cmd = new SQLiteCommand(sql, conn))
{
try
{
cmd.Transaction = conn.BeginTransaction();
if (sqliteparameters != null)
cmd.Parameters.AddRange(sqliteparameters);
var ds = new DataSet();
var da = new SQLiteDataAdapter(cmd);
da.Fill(ds);
if (ds.Tables.Count == 0)
throw new SQLiteException("sql语句不合法.");
return ds;
}
catch (SQLiteException)
{
try
{
cmd.Transaction.Rollback();
cmd.Transaction = conn.BeginTransaction();
return cmd.ExecuteNonQuery();
}
catch (SQLiteException)
{
return null;
}
}
catch (Exception)
{
return null;
}
finally
{
cmd.Transaction.Commit();
}
}
}
}
#endregion
}
}