SQLiteDao

30 篇文章 1 订阅

using System;

using System.Collections.Generic;

using System.Data;

using System.Data.SQLite;

using System.IO;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

namespace Wsfly

{

/// <summary>

/// SQLiteDAO

/// </summary>

public class SQLiteDao

{

#region 初始数据库

/// <summary>

/// 默认数据库名称

/// </summary>

private static string _defaultDBName = "Wsfly.DC.ERP";

/// <summary>

/// 创建数据库文件

/// </summary>

/// <param name="path"></param>

private static bool CreateDatabase(string path = null)

{

//数据库文件

if (string.IsNullOrEmpty(path)) path = AppDomain.CurrentDomain.BaseDirectory + "AppData\\" + _defaultDBName + ".db";

//已经有数据库

if (File.Exists(path)) return false;

try

{

//创建数据库

SQLiteConnection.CreateFile(path);

return true;

}

catch (Exception ex)

{

throw ex;

}

}

/// <summary>

/// 是否存在数据库

/// </summary>

/// <param name="path"></param>

/// <returns></returns>

private static bool IsExistDatabase(string path = null)

{

//数据库文件

if (string.IsNullOrEmpty(path)) path = AppDomain.CurrentDomain.BaseDirectory + "AppData\\" + _defaultDBName + ".db";

//是否已经有数据库

return File.Exists(path);

}

/// <summary>

/// 初始化数据库

/// </summary>

/// <param name="path"></param>

/// <returns></returns>

public static void InitDatabase(string path = null)

{

try

{

//创建数据库文件

bool flag = CreateDatabase(path);

AppLog.WriteDebugLog("初始数据库结果:" + (flag ? "1" : "0"));

if (flag)

{

//数据库文件名称

string dbFileName = _defaultDBName;

if (!string.IsNullOrEmpty(path))

{

dbFileName = System.IO.Path.GetFileNameWithoutExtension(path);

}

//执行SQL

path = AppDomain.CurrentDomain.BaseDirectory + "AppData\\" + dbFileName + ".sql";

if (File.Exists(path))

{

//SQLiteCommand

SQLiteCommand cmd = GetSQLiteCmd();

//执行初始SQL

string sql = File.ReadAllText(path);

ExecuteNonQuery(cmd, sql);

}

}

}

catch (Exception ex)

{

throw ex;

}

}

#endregion

#region SQLiteCommand

/// <summary>

/// 得到SqlCmd

/// </summary>

/// <returns></returns>

public static SQLiteCommand GetSQLiteCmd()

{

//默认数据库地址

string defaultPath = AppDomain.CurrentDomain.BaseDirectory + "AppData\\" + _defaultDBName + ".db";

//默认数据库连接

string connectionString = "Data Source=" + defaultPath + ";Version=3;UseUTF16Encoding=True;";

//返回SQLiteCommand

return GetSQLiteCmd(connectionString);

}

/// <summary>

/// 得到SQLCMD

/// </summary>

/// <param name="connectionString">连接字符串</param>

/// <returns></returns>

private static SQLiteCommand GetSQLiteCmd(string connectionString)

{

try

{

//数据库连接

SQLiteConnection conn = new SQLiteConnection(connectionString);

//SQLiteCommand

SQLiteCommand cmd = new SQLiteCommand();

cmd.Connection = conn;

cmd.CommandTimeout = 90;

//打开数据库连接

cmd.Connection.Open();

//返回SQLCMD

return cmd;

}

catch (Exception ex)

{

throw ex;

}

}

#endregion

#region 查询

/// <summary>

/// 返回第一行,第一列

/// </summary>

/// <param name="query">查询参数</param>

/// <returns></returns>

public static object GetScalar(SQLParam query, string returnColunm)

{

try

{

//参数检查

if (query == null) throw new Exception("WsflyException:解析参数异常!");

if (string.IsNullOrWhiteSpace(query.TableName)) throw new Exception("WsflyException:需要参数数据表名称!");

//得到SQLCMD对象

SQLiteCommand cmd = GetSQLiteCmd();

if (cmd == null) throw new Exception("WsflyException:未找到数据库连接!");

//SQL

string sql = @"select " + returnColunm + " from [" + query.TableName + @"] where $wsfly.where$";

string where = string.Empty;

//生成SQL及参数

List<SQLiteParameter> ps = BuildQuerySQLAndParameters(query, ref sql, ref where);

//查询数据

return ExecuteScalar(cmd, sql, ps.ToArray());

}

catch (Exception ex)

{

throw ex;

}

}

/// <summary>

/// 返回第一行,第一列

/// </summary>

/// <param name="sql">查询SQL</param>

/// <returns></returns>

public static object GetScalar(string sql)

{

try

{

//参数检查

if (string.IsNullOrWhiteSpace(sql)) throw new Exception("WsflyException:解析SQL异常!");

//得到SQLCMD对象

SQLiteCommand cmd = GetSQLiteCmd();

if (cmd == null) throw new Exception("WsflyException:未找到数据库连接!");

//查询数据

return ExecuteScalar(cmd, sql);

}

catch (Exception ex)

{

throw ex;

}

}

/// <summary>

/// 查询数量

/// </summary>

/// <param name="query">查询参数</param>

/// <returns></returns>

public static int GetCount(SQLParam query)

{

try

{

//参数记录数

object objResult = GetScalar(query, "count(*)");

//是否有查询结果

return DataType.Int(objResult, 0);

}

catch (Exception ex)

{

throw ex;

}

}

/// <summary>

/// 是否存在记录

/// </summary>

/// <param name="query">查询参数</param>

/// <returns></returns>

public static bool IsExist(SQLParam query)

{

try

{

return GetCount(query) > 0 ? true : false;

}

catch (Exception ex)

{

throw ex;

}

}

/// <summary>

/// 查询数据行

/// </summary>

/// <param name="sql"></param>

/// <returns></returns>

public static DataRow GetTableRowBySql(string sql)

{

DataTable dt = GetTableBySQL(sql);

return dt == null || dt.Rows.Count <= 0 ? null : dt.Rows[0];

}

/// <summary>

/// 查询数据行

/// </summary>

/// <returns></returns>

public static DataRow GetTableRow(long id, string tbName)

{

string sql = string.Empty;

try

{

//参数检查

if (id <= 0) throw new Exception("WsflyException:需要参数数据Id主键!");

if (string.IsNullOrWhiteSpace(tbName)) throw new Exception("WsflyException:需要参数数据表名称!");

//得到SQLCMD对象

SQLiteCommand cmd = GetSQLiteCmd();

if (cmd == null) throw new Exception("WsflyException:未找到数据库连接!");

sql = "select * from [" + tbName + "] where [Id]=@Id";

SQLiteParameter[] ps =

{

new SQLiteParameter("@Id",id)

};

//查询数据

DataTable dt = QueryDataTable(cmd, sql, ps);

//是否有查询结果

if (dt == null || dt.Rows.Count <= 0) return null;

//返回第一行

return dt.Rows[0];

}

catch (Exception ex)

{

throw ex;

}

}

/// <summary>

/// 查询数据行

/// </summary>

/// <param name="query"></param>

/// <returns></returns>

public static DataRow GetTableRow(SQLParam query)

{

DataTable dt = GetTable(query);

return dt == null || dt.Rows.Count <= 0 ? null : dt.Rows[0];

}

/// <summary>

/// 获取数据表

/// </summary>

/// <param name="sql"></param>

/// <returns></returns>

public static DataTable GetTableBySQL(string sql, SQLiteParameter[] ps = null)

{

try

{

//得到SQLCMD对象

SQLiteCommand cmd = GetSQLiteCmd();

if (cmd == null) throw new Exception("WsflyException:未找到数据库连接!");

//返回数据表

return QueryDataTable(cmd, sql, ps);

}

catch (Exception ex)

{

throw ex;

}

}

/// <summary>

/// 数据表查询

/// </summary>

/// <param name="query">查询参数</param>

/// <param name="tbName">数据表名</param>

/// <returns></returns>

public static DataTable GetTable(SQLParam query)

{

string sql = string.Empty;

try

{

//参数检查

if (query == null) throw new Exception("WsflyException:解析参数异常!");

//得到SQLCMD对象

SQLiteCommand cmd = GetSQLiteCmd();

if (cmd == null) throw new Exception("WsflyException:未找到数据库连接!");

//SQL

sql = @"select * from [" + query.TableName + @"]

where $wsfly.where$

order by $wsfly.order$" + (query.Top > 0 ? " limit " + query.Top : "");

//查询条件

string where = string.Empty;

//生成查询SQL及参数

List<SQLiteParameter> ps = BuildQuerySQLAndParameters(query, ref sql, ref where);

//查询数据

DataTable dt = QueryDataTable(cmd, sql, ps.ToArray());

//是否有查询结果

if (dt == null || dt.Rows.Count <= 0) return null;

//返回数据表

return dt;

}

catch (Exception ex)

{

throw ex;

}

}

/// <summary>

/// 数据表查询

/// </summary>

/// <param name="tableName"></param>

/// <returns></returns>

public static DataTable GetTable(string tableName)

{

string sql = string.Empty;

try

{

//参数检查

if (string.IsNullOrWhiteSpace(tableName)) throw new Exception("WsflyException:解析参数异常!");

//得到SQLCMD对象

SQLiteCommand cmd = GetSQLiteCmd();

if (cmd == null) throw new Exception("WsflyException:未找到数据库连接!");

//SQL

sql = @"select * from [" + tableName + @"]";

//查询数据

DataTable dt = QueryDataTable(cmd, sql);

//是否有查询结果

if (dt == null || dt.Rows.Count <= 0) return null;

//返回数据表

return dt;

}

catch (Exception ex)

{

throw ex;

}

}

/// <summary>

/// 数据表查询

/// </summary>

/// <param name="tableName"></param>

/// <param name="parentId"></param>

/// <returns></returns>

public static DataTable GetTable(string tableName, long parentId)

{

string sql = string.Empty;

try

{

//参数检查

if (string.IsNullOrWhiteSpace(tableName)) throw new Exception("WsflyException:解析参数异常!");

//得到SQLCMD对象

SQLiteCommand cmd = GetSQLiteCmd();

if (cmd == null) throw new Exception("WsflyException:未找到数据库连接!");

//SQL

sql = @"select * from [" + tableName + @"] where [ParentId]=" + parentId;

//查询数据

DataTable dt = QueryDataTable(cmd, sql);

//是否有查询结果

if (dt == null || dt.Rows.Count <= 0) return null;

//返回数据表

return dt;

}

catch (Exception ex)

{

throw ex;

}

}

/// <summary>

/// 查询分页

/// </summary>

/// <param name="query"></param>

/// <returns></returns>

public static DataTable GetPaging(SQLParam query, ref int totalCount)

{

string sql = string.Empty;

//参数检查

if (query == null) throw new Exception("WsflyException:解析参数异常!");

//得到SQLCMD对象

SQLiteCommand cmd = GetSQLiteCmd();

if (cmd == null) throw new Exception("WsflyException:未找到数据库连接!");

try

{

//分页开始数量

int beginCount = query.PageSize * (query.PageIndex - 1);

//SQL

sql = @"select * from [" + query.TableName + @"]

where $wsfly.where$

order by $wsfly.order$

limit " + query.PageSize + @"

offset " + beginCount;

//查询条件

string where = string.Empty;

//生成查询SQL及参数

List<SQLiteParameter> ps = BuildQuerySQLAndParameters(query, ref sql, ref where);

//查询数量

totalCount = GetCount(query);

//是否打开数据库连接

if (cmd.Connection.State != ConnectionState.Open) cmd.Connection.Open();

//命令:文本类型 & 执行SQL

cmd.CommandType = CommandType.Text;

cmd.CommandText = sql;

cmd.Parameters.Clear();

if (ps != null && ps.Count > 0)

{

foreach (SQLiteParameter p in ps)

{

cmd.Parameters.Add(p);

}

}

//查询数据

DataTable dt = new DataTable();

SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);

da.Fill(dt);

if (dt == null || dt.Rows.Count <= 0) return null;

//返回数据表

return dt;

}

catch (Exception ex)

{

throw ex;

}

finally

{

//关闭数据库连接

if (cmd.Connection.State == ConnectionState.Open) cmd.Connection.Close();

}

}

/// <summary>

/// 查询数据集

/// </summary>

/// <param name="sql">查询语句</param>

/// <returns>DataSet</returns>

private static DataSet QueryDataSet(SQLiteCommand cmd, string sql, SQLiteParameter[] ps = null)

{

try

{

//数据集

DataSet ds = new DataSet();

//是否打开数据库连接

if (cmd.Connection.State != ConnectionState.Open) cmd.Connection.Open();

//命令:文本类型 & 执行SQL

cmd.CommandType = CommandType.Text;

cmd.CommandText = sql;

cmd.Parameters.Clear();

if (ps != null && ps.Length > 0)

{

foreach (SQLiteParameter p in ps)

{

cmd.Parameters.Add(p);

}

}

//数据适配器 将查询出的数据填充到DataSet

SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);

da.Fill(ds);

//返回

return ds;

}

catch (Exception ex)

{

throw ex;

}

finally

{

//关闭数据库连接

if (cmd.Connection.State == ConnectionState.Open) cmd.Connection.Close();

}

}

/// <summary>

/// 查询数据表

/// </summary>

/// <returns></returns>

private static DataTable QueryDataTable(SQLiteCommand cmd, string sql, SQLiteParameter[] ps = null)

{

try

{

//数据表

DataTable dt = new DataTable();

//是否打开数据库连接

if (cmd.Connection.State != ConnectionState.Open) cmd.Connection.Open();

//命令:文本类型 & 执行SQL

cmd.CommandType = CommandType.Text;

cmd.CommandText = sql;

cmd.Parameters.Clear();

if (ps != null && ps.Length > 0)

{

foreach (SQLiteParameter p in ps)

{

cmd.Parameters.Add(p);

}

}

//数据适配器 将查询出的数据填充到DataSet

SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);

da.Fill(dt);

//返回

return dt;

}

catch (Exception ex)

{

throw ex;

}

finally

{

//关闭数据库连接

if (cmd.Connection.State == ConnectionState.Open) cmd.Connection.Close();

}

}

#endregion

#region 执行SQL

/// <summary>

/// 添加

/// </summary>

/// <param name="insert"></param>

/// <returns></returns>

public static long Insert(SQLParam insert)

{

insert.Action = Actions.添加;

List<SQLParam> paramList = new List<SQLParam>();

paramList.Add(insert);

ExecuteSQL(paramList);

return paramList[0].ReturnId;

}

/// <summary>

/// 修改

/// </summary>

/// <param name="update"></param>

/// <returns></returns>

public static bool Update(SQLParam update)

{

update.Action = Actions.修改;

List<SQLParam> paramList = new List<SQLParam>();

paramList.Add(update);

return ExecuteSQL(paramList);

}

/// <summary>

/// 删除

/// </summary>

/// <param name="delete"></param>

/// <returns></returns>

public static bool Delete(SQLParam delete)

{

delete.Action = Actions.删除;

List<SQLParam> paramList = new List<SQLParam>();

paramList.Add(delete);

return ExecuteSQL(paramList);

}

/// <summary>

/// 执行SQL

/// </summary>

/// <param name="sql"></param>

public static bool ExecuteSQL(string sql)

{

return ExecuteNonQuery(sql);

}

/// <summary>

/// 执行SQL

/// </summary>

public static bool ExecuteSQL(string sql, SQLiteParameter[] ps)

{

//SQLiteCommand

SQLiteCommand cmd = GetSQLiteCmd();

return ExecuteNonQuery(cmd, sql, ps);

}

/// <summary>

/// 执行SQL命令

/// </summary>

/// <param name="paramList">SQL参数</param>

/// <param name="dbname">数据库名</param>

/// <returns></returns>

public static bool ExecuteSQL(List<SQLParam> paramList)

{

//是否有SQL参数

if (paramList == null || paramList.Count <= 0) throw new Exception("WsflyException:需要SQL参数!");

//得到SQLCMD对象

SQLiteCommand cmd = GetSQLiteCmd();

if (cmd == null) throw new Exception("WsflyException:未找到数据库连接!");

try

{

foreach (SQLParam param in paramList)

{

//生成SQL

string where = string.Empty;

string sql = string.Empty;

//SQL参数

List<SQLiteParameter> ps = new List<SQLiteParameter>();

if (param.Action == Actions.添加)

{

//生成插入参数及SQL

sql = "insert into [" + param.TableName + "]($wsfly.cells$) values($wsfly.values$)";

if (!param.OpreateCells.Exists(p => p.Key.Equals("Id")))

{

param.OpreateCells.Insert(0, new KeyValue("Id", null));

}

ps = BuildInsertSQLAndParameters(param.OpreateCells, ref sql);

}

else if (param.Action == Actions.修改)

{

//生成更新参数及SQL

sql = "update [" + param.TableName + "] set $wsfly.cells.values$ $wsfly.where$";

if (!string.IsNullOrWhiteSpace(param.Ids)) param.Wheres.Add(new Where("Id", param.Ids, WhereType.包含));

ps = BuildUpdateSQLAndParameters(param.OpreateCells, param.Id, param.Wheres, ref sql, ref where);

}

else if (param.Action == Actions.删除)

{

//生成删除参数及SQL

sql = "delete from [" + param.TableName + "] $wsfly.where$";

ps = BuildDeleteSQLAndParameters(param.Id, param.Ids, param.Wheres, ref sql, ref where);

}

else if (param.Action == Actions.SQL)

{

//执行SQL

sql = param.ExecSQL;

}

//是否有条件

if (!string.IsNullOrWhiteSpace(param.WhereSQL))

{

if (!string.IsNullOrWhiteSpace(where)) sql += " and " + param.WhereSQL;

else sql += " where " + param.WhereSQL;

}

//SQLiteCommand为SQL脚本模式

cmd.CommandType = CommandType.Text;

//执行SQL

if (param.Action == Actions.添加)

{

//执行添加

param.ReturnSql = sql;

object objResult = ExecuteScalar(cmd, sql, ps.ToArray());

long newId = DataType.Long(objResult, 0);

//返回Id

param.ReturnId = newId;

if (newId <= 0) throw new Exception("执行(" + param.Action.ToString() + ")失败,SQL:" + sql);

}

else

{

//执行修改、删除

param.ReturnSql = sql;

bool flag = ExecuteNonQuery(cmd, sql, ps.ToArray());

if (!flag) throw new Exception("执行(" + param.Action.ToString() + ")失败,SQL:" + sql);

}

}

return true;

}

catch (Exception ex)

{

AppLog.WriteBugLog(ex, "执行SQL异常");

}

return false;

}

/// <summary>

/// 执行非查询语句带参数

/// </summary>

/// <param name="sql"></param>

/// <param name="paramters"></param>

/// <returns></returns>

public static bool ExecuteNonQuery(SQLiteCommand cmd, string sql, SQLiteParameter[] ps = null)

{

try

{

cmd.CommandText = sql;

cmd.Parameters.Clear();

//是否打开数据库连接

if (cmd.Connection.State != ConnectionState.Open) cmd.Connection.Open();

//遍历参数

if (ps != null && ps.Length > 0)

{

foreach (SQLiteParameter p in ps)

{

cmd.Parameters.Add(p);

}

}

//是否有影响行数

if (cmd.ExecuteNonQuery() <= 0) return false;

return true;

}

catch (Exception ex)

{

throw ex;

}

finally

{

//关闭数据库连接

if (cmd.Connection.State == ConnectionState.Open) cmd.Connection.Close();

}

}

/// <summary>

/// 执行非查询语句带参数

/// </summary>

/// <param name="sql"></param>

/// <param name="paramters"></param>

/// <returns></returns>

public static bool ExecuteNonQuery(string sql, SQLiteParameter[] ps = null)

{

//得到SQLCMD对象

SQLiteCommand cmd = GetSQLiteCmd();

if (cmd == null) throw new Exception("WsflyException:未找到数据库连接!");

try

{

cmd.CommandText = sql;

cmd.Parameters.Clear();

//是否打开数据库连接

if (cmd.Connection.State != ConnectionState.Open) cmd.Connection.Open();

//遍历参数

if (ps != null && ps.Length > 0)

{

foreach (SQLiteParameter p in ps)

{

cmd.Parameters.Add(p);

}

}

//是否有影响行数

if (cmd.ExecuteNonQuery() <= 0) return false;

return true;

}

catch (Exception ex)

{

throw ex;

}

finally

{

//关闭数据库连接

if (cmd.Connection.State == ConnectionState.Open) cmd.Connection.Close();

}

}

/// <summary>

/// 执行查询,返回第一行第一列

/// </summary>

/// <param name="sql"></param>

/// <param name="paramters"></param>

/// <returns></returns>

public static object ExecuteScalar(SQLiteCommand cmd, string sql, SQLiteParameter[] ps = null)

{

try

{

//设置SQL及参数

cmd.CommandText = sql;

cmd.Parameters.Clear();

if (ps != null && ps.Length > 0)

{

foreach (SQLiteParameter p in ps)

{

cmd.Parameters.Add(p);

}

}

//是否打开数据库连接

if (cmd.Connection.State != ConnectionState.Open) cmd.Connection.Open();

if (sql.ToLower().StartsWith("insert into"))

{

//插入SQL

cmd.ExecuteNonQuery();

cmd.CommandText = "select last_insert_rowid()";

return cmd.ExecuteScalar();

}

else

{

//非插入SQL

return cmd.ExecuteScalar();

}

}

catch (Exception ex)

{

throw ex;

}

finally

{

//关闭数据库连接

if (cmd.Connection.State == ConnectionState.Open) cmd.Connection.Close();

}

}

/// <summary>

/// 执行查询,返回第一行第一列

/// </summary>

/// <param name="sql"></param>

/// <param name="paramters"></param>

/// <returns></returns>

public static object ExecuteScalar(string sql, SQLiteParameter[] ps = null)

{

//得到SQLCMD对象

SQLiteCommand cmd = GetSQLiteCmd();

if (cmd == null) throw new Exception("WsflyException:未找到数据库连接!");

try

{

//设置SQL及参数

cmd.CommandText = sql;

cmd.Parameters.Clear();

if (ps != null && ps.Length > 0)

{

foreach (SQLiteParameter p in ps)

{

cmd.Parameters.Add(p);

}

}

//是否打开数据库连接

if (cmd.Connection.State != ConnectionState.Open) cmd.Connection.Open();

if (sql.ToLower().StartsWith("insert into"))

{

//插入SQL

cmd.ExecuteNonQuery();

cmd.CommandText = "select last_insert_rowid()";

return cmd.ExecuteScalar();

}

else

{

//非插入SQL

return cmd.ExecuteScalar();

}

}

catch (Exception ex)

{

throw ex;

}

finally

{

//关闭数据库连接

if (cmd.Connection.State == ConnectionState.Open) cmd.Connection.Close();

}

}

#endregion

#region 事务执行SQL

/// <summary>

/// 执行事务:执行SQL

/// </summary>

/// <param name="cmd"></param>

/// <param name="sql"></param>

/// <param name="ps"></param>

/// <returns></returns>

public static bool TranExecute(SQLiteCommand cmd, string sql, SQLiteParameter[] ps = null)

{

try

{

if (cmd.Connection.State != ConnectionState.Open)

{

//打开数据库连接

cmd.Connection.Open();

}

if (cmd.Transaction == null)

{

//执行事务

SQLiteTransaction tran = cmd.Connection.BeginTransaction();

cmd.Transaction = tran;

}

//执行SQL的参数

if (cmd.Parameters != null)

{

//清空参数

cmd.Parameters.Clear();

}

if (ps != null && ps.Length > 0)

{

foreach (SQLiteParameter p in ps)

{

cmd.Parameters.Add(p);

}

}

//执行的SQL

cmd.CommandText = sql;

//执行SQL

if (cmd.ExecuteNonQuery() <= 0) return false;

return true;

}

catch (Exception ex)

{

throw ex;

}

}

/// <summary>

/// 执行事务查询:返回第一行第一列数据

/// </summary>

/// <param name="cmd"></param>

/// <param name="sql"></param>

/// <param name="ps"></param>

/// <returns></returns>

public static object TranExecuteScalar(SQLiteCommand cmd, string sql, SQLiteParameter[] ps = null)

{

try

{

if (cmd.Connection.State != ConnectionState.Open)

{

//打开数据库连接

cmd.Connection.Open();

}

if (cmd.Transaction == null)

{

//执行事务

SQLiteTransaction tran = cmd.Connection.BeginTransaction();

cmd.Transaction = tran;

}

//执行SQL的参数

if (cmd.Parameters != null)

{

//清空参数

cmd.Parameters.Clear();

}

if (ps != null && ps.Length > 0)

{

foreach (SQLiteParameter p in ps)

{

cmd.Parameters.Add(p);

}

}

//执行的SQL

cmd.CommandText = sql;

if (sql.ToLower().Trim().StartsWith("insert into"))

{

//插入SQL

cmd.ExecuteNonQuery();

cmd.CommandText = "select last_insert_rowid()";

return cmd.ExecuteScalar();

}

else

{

//执行SQL 返回第一行第一列

return cmd.ExecuteScalar();

}

}

catch (Exception ex) { }

return null;

}

/// <summary>

/// 执行事务查询:返回DataRow

/// </summary>

/// <param name="cmd"></param>

/// <param name="sql"></param>

/// <param name="ps"></param>

/// <returns></returns>

public static DataRow TranQueryRow(SQLiteCommand cmd, string sql, SQLiteParameter[] ps = null)

{

try

{

//查询数据表

DataTable dt = TranQueryTable(cmd, sql, ps);

if (dt == null || dt.Rows.Count <= 0) return null;

//返回第一行

return dt.Rows[0];

}

catch (Exception ex) { }

return null;

}

/// <summary>

/// 执行事务查询:返回DataTable

/// </summary>

/// <param name="cmd"></param>

/// <param name="sql"></param>

/// <param name="ps"></param>

/// <returns></returns>

public static DataTable TranQueryTable(SQLiteCommand cmd, string sql, SQLiteParameter[] ps = null)

{

try

{

if (cmd.Connection.State != ConnectionState.Open)

{

//打开数据库连接

cmd.Connection.Open();

}

if (cmd.Transaction == null)

{

//执行事务

SQLiteTransaction tran = cmd.Connection.BeginTransaction();

cmd.Transaction = tran;

}

//执行SQL的参数

if (cmd.Parameters != null)

{

//清空参数

cmd.Parameters.Clear();

}

if (ps != null && ps.Length > 0)

{

foreach (SQLiteParameter p in ps)

{

cmd.Parameters.Add(p);

}

}

//执行的SQL

cmd.CommandText = sql;

//要填充的数据表

DataTable dt = new DataTable();

//DataAdapter填充数据

SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);

//填充数据

da.Fill(dt);

//返回结果

return dt;

}

catch (Exception ex) { }

return null;

}

/// <summary>

/// 提交事务

/// </summary>

public static void TranCommit(SQLiteCommand cmd)

{

try

{

//是否有命令及事务

if (cmd == null || cmd.Connection.State == ConnectionState.Closed || cmd.Transaction == null) return;

//提交事务

cmd.Transaction.Commit();

}

catch (Exception ex)

{

throw ex;

}

finally

{

//关闭数据库连接

if (cmd.Connection.State != ConnectionState.Closed)

{

cmd.Connection.Close();

}

}

}

/// <summary>

/// 回滚事务

/// </summary>

public static void TranRollback(SQLiteCommand cmd)

{

try

{

//是否有命令及事务

if (cmd == null || cmd.Connection.State == ConnectionState.Closed || cmd.Transaction == null) return;

//回滚事务

cmd.Transaction.Rollback();

}

catch (Exception ex)

{

string sql = cmd == null ? "" : string.IsNullOrWhiteSpace(cmd.CommandText) ? "" : cmd.CommandText;

AppLog.WriteBugLog(ex, "回滚事务异常,执行脚本:" + sql);

}

finally

{

//关闭数据库连接

if (cmd.Connection.State != ConnectionState.Closed)

{

cmd.Connection.Close();

}

}

}

#endregion

#region 生成SQL

/// <summary>

/// 生成查询SQL&参数

/// </summary>

/// <param name="param">查询参数</param>

/// <param name="sql"></param>

/// <param name="whereRef">条件SQL</param>

/// <returns></returns>

public static List<SQLiteParameter> BuildQuerySQLAndParameters(SQLParam param, ref string sql, ref string whereRef)

{

//参数

List<SQLiteParameter> ps = new List<SQLiteParameter>();

//查询条件

string where = "1=1";

if (param.Wheres != null && param.Wheres.Count > 0)

{

BuildWhere(param.Wheres, ref where, ref ps);

}

//过滤SQL

if (!string.IsNullOrWhiteSpace(param.WhereSQL))

{

where += " and " + param.WhereSQL;

}

//排序SQL

string order = "1";

if (param.OrderBys != null && param.OrderBys.Count > 0)

{

//清空排序

order = "";

//生成排序

foreach (OrderBy ob in param.OrderBys)

{

//随机排序

if (ob.Type == OrderType.随机) { order += " NEWID()"; continue; }

//排序条件

order += "[" + ob.CellName + "] " + ob.TypeSQL + ",";

}

//清除最后“,”

order = order.Trim(',').Trim();

}

//排序SQL

if (!string.IsNullOrWhiteSpace(param.OrderSQL))

{

if (order.Equals("1")) order = param.OrderSQL;

else if (!string.IsNullOrWhiteSpace(order)) order += "," + param.OrderSQL;

else order = param.OrderSQL;

}

//SQL替换

sql = sql.Replace("$wsfly.where$", where);

sql = sql.Replace("$wsfly.order$", order);

//返回查询条件

whereRef = where;

return ps;

}

/// <summary>

/// 生成插入SQL&参数

/// </summary>

/// <param name="insertCells">插入参数</param>

/// <param name="sql">要执行的SQL</param>

/// <returns></returns>

public static List<SQLiteParameter> BuildInsertSQLAndParameters(List<KeyValue> insertCells, ref string sql)

{

string cells = string.Empty;

string values = string.Empty;

List<SQLiteParameter> ps = new List<SQLiteParameter>();

//取得所有对象

foreach (KeyValue kv in insertCells)

{

//非ID列 过滤NULL值

if (!kv.Key.ToUpper().Equals("ID"))

{

//空

if (kv.Value == null) continue;

}

//SQL

cells += "[" + kv.Key + "],";

values += "@" + kv.Key + ",";

//是否需要加密

kv.Value = IsNeedEncrypt(kv.Value);

//参数值

SQLiteParameter param = new SQLiteParameter() { ParameterName = "@" + kv.Key, Value = kv.Value };

ps.Add(param);

}

//SQL

cells = cells.Trim(',').Trim();

values = values.Trim(',').Trim();

//SQL替换

sql = sql.Replace("$wsfly.cells$", cells);

sql = sql.Replace("$wsfly.values$", values);

return ps;

}

/// <summary>

/// 生成插入SQL&参数

/// </summary>

/// <param name="row"></param>

/// <param name="sql"></param>

/// <returns></returns>

public static List<SQLiteParameter> BuildInsertSQLAndParameters(DataRow row, ref string sql)

{

string cells = string.Empty;

string values = string.Empty;

List<SQLiteParameter> ps = new List<SQLiteParameter>();

//取得所有对象

foreach (DataColumn col in row.Table.Columns)

{

string colName = col.ColumnName;

object value = row[colName];

//过滤列插入

if (colName.ToUpper().Equals("ID")) continue;

//空

if (value == null || value == DBNull.Value) continue;

//列

cells += "[" + colName + "],";

//值

values += "@" + colName + ",";

//是否需要加密

value = IsNeedEncrypt(value);

//参数

ps.Add(new SQLiteParameter() { ParameterName = "@" + colName, Value = value });

}

//SQL

cells = cells.Trim(',').Trim();

values = values.Trim(',').Trim();

//SQL替换

sql = sql.Replace("$wsfly.cells$", cells);

sql = sql.Replace("$wsfly.values$", values);

return ps;

}

/// <summary>

/// 生成更新SQL&参数

/// </summary>

/// <param name="updateCells">更新参数</param>

/// <param name="wheres">查询条件</param>

/// <param name="sql">要执行的SQL</param>

/// <returns></returns>

public static List<SQLiteParameter> BuildUpdateSQLAndParameters(List<KeyValue> updateCells, long id, List<Where> wheres, ref string sql, ref string whereStr)

{

string where = string.Empty;

string cellsValues = string.Empty;

List<SQLiteParameter> ps = new List<SQLiteParameter>();

//取得所有对象

foreach (KeyValue kv in updateCells)

{

//过滤列

if (kv.Key.ToUpper().Equals("ID") || kv.Key.ToUpper().Equals("CREATEDATE")) continue;

if (kv.Value == null)

{

//空

cellsValues += "[" + kv.Key + "]=null,";

continue;

}

//SQL

cellsValues += "[" + kv.Key + "]=@" + kv.Key + ",";

//参数值

ps.Add(new SQLiteParameter() { ParameterName = "@" + kv.Key, Value = kv.Value });

}

if (id > 0)

{

//根据主键修改

where = " [Id]=@Id";

SQLiteParameter paramId = new SQLiteParameter() { ParameterName = "@Id", Value = id };

ps.Add(paramId);

}

else if (wheres != null && wheres.Count > 0)

{

//根据条件修改

BuildWhere(wheres, ref where, ref ps);

}

//SQL

cellsValues = cellsValues.Trim(',').Trim();

//是否有条件

if (!string.IsNullOrWhiteSpace(where)) where = " where " + where;

//判断条件

whereStr = where;

//SQL替换

sql = sql.Replace("$wsfly.cells.values$", cellsValues);

sql = sql.Replace("$wsfly.where$", where);

return ps;

}

/// <summary>

/// 生成更新参数

/// </summary>

/// <param name="updateCells"></param>

/// <param name="sql"></param>

/// <returns></returns>

public static List<SQLiteParameter> BuildUpdateParameters(List<KeyValue> updateCells, ref string sql)

{

//参数列表

List<SQLiteParameter> ps = new List<SQLiteParameter>();

//取得所有对象

foreach (KeyValue kv in updateCells)

{

//过滤列

if (kv.Key.ToUpper().Equals("ID") || kv.Key.ToUpper().Equals("CREATEDATE")) continue;

if (kv.Value == null)

{

//空

sql += "[" + kv.Key + "]=null,";

continue;

}

//SQL

sql += "[" + kv.Key + "]=@" + kv.Key + ",";

//参数值

ps.Add(new SQLiteParameter() { ParameterName = "@" + kv.Key, Value = kv.Value });

}

return ps;

}

/// <summary>

/// 生成删除SQL&参数

/// </summary>

/// <param name="id">要删除的Id主键</param>

/// <param name="ids">要删除的Id列表</param>

/// <param name="wheres">查询条件</param>

/// <param name="sql">要执行的SQL</param>

/// <param name="whereStr">条件SQL</param>

/// <returns></returns>

public static List<SQLiteParameter> BuildDeleteSQLAndParameters(long id, string ids, List<Where> wheres, ref string sql, ref string whereStr)

{

string where = string.Empty;

List<SQLiteParameter> ps = new List<SQLiteParameter>();

//生成删除条件

if (id > 0)

{

where = "[Id]=@Id";

ps.Add(new SQLiteParameter() { ParameterName = "@Id", Value = id });

}

else if (!string.IsNullOrWhiteSpace(ids))

{

where = string.Format("[Id] in ({0})", ids);

}

else if (wheres != null && wheres.Count > 0)

{

BuildWhere(wheres, ref where, ref ps);

}

//是否有条件

if (!string.IsNullOrWhiteSpace(where)) where = " where " + where;

//SQL替换

sql = sql.Replace("$wsfly.where$", where);

//删除条件

whereStr = where;

return ps;

}

/// <summary>

/// 生成查询条件

/// </summary>

/// <param name="wheres">查询条件</param>

/// <param name="where">条件SQL</param>

/// <param name="ps">参数</param>

public static void BuildWhere(List<Where> wheres, ref string where, ref List<SQLiteParameter> ps)

{

//是否有条件

if (wheres == null || wheres.Count <= 0) return;

//清空条件

where = "1=1";

//是否有左括号

int leftBracketCount = 0;

bool isLeftBracketFirst = false;

foreach (Where w in wheres)

{

//右括号

if (w.Type == WhereType.右括号 && leftBracketCount > 0)

{

where += " ) ";

leftBracketCount--;

continue;

}

//查询条件

if (leftBracketCount > 0 && isLeftBracketFirst)

{

//前一个为括号,不加并列条件

isLeftBracketFirst = false;

}

else

{

//And 或 Or

where += " " + w.Parallel.ToString() + " ";

}

//左括号

if (w.Type == WhereType.左括号)

{

where += " ( ";

leftBracketCount++;

isLeftBracketFirst = true;

continue;

}

//参数索引 避免重复

int parameterIndex = wheres.IndexOf(w);

//查询参数

SQLiteParameter p = new SQLiteParameter() { ParameterName = "@" + w.CellName + parameterIndex, Value = w.CellValue };

switch (w.Type)

{

case WhereType.模糊查询:

p.Value = "%" + w.CellValue + "%";

break;

case WhereType.模糊前:

p.Value = "%" + w.CellValue;

break;

case WhereType.模糊后:

p.Value = w.CellValue + "%";

break;

case WhereType.包含:

case WhereType.不包含:

where += string.Format(w.WhereSQL, w.CellValue.ToString().Trim(','));

continue;

case WhereType.空:

case WhereType.非空:

where += w.WhereSQL;

continue;

}

//添加条件

where += w.WhereSQL + parameterIndex;

//添加参数

ps.Add(p);

}

}

/// <summary>

/// 生成排序SQL

/// </summary>

/// <param name="orderBys"></param>

/// <param name="orderBy"></param>

public static void BuildOrderBy(List<OrderBy> orderBys, ref string orderBy)

{

//排序SQL

string order = "1";

if (orderBys != null && orderBys.Count > 0)

{

//清空排序

order = "";

foreach (OrderBy ob in orderBys)

{

//随机排序

if (ob.Type == OrderType.随机) { order += " NEWID()"; continue; }

//排序条件

order += "[" + ob.CellName + "] " + ob.TypeSQL + ",";

}

//清除最后“,”

order = order.Trim(',').Trim();

}

orderBy = order;

}

/// <summary>

/// 是否需要加密

/// </summary>

/// <param name="value"></param>

/// <returns></returns>

public static object IsNeedEncrypt(object value)

{

if (value is string)

{

//值

string val = value.ToString();

//是否需要加密

if (val.StartsWith("WsEncoding_"))

{

val = val.Replace("WsEncoding_", "");

val = EncryptionDES.Decrypt(val);

return "WsEncrypt_" + val;

}

}

return value;

}

#endregion

#region 序列号相关

static object _lockSerialNo = new object();

/// <summary>

/// 序列号

/// </summary>

/// <returns></returns>

public static string GetSerialNo(long tableId, long relativeId, SerialNoType subType, int serialNoLength = 4, string prefix = "")

{

lock (_lockSerialNo)

{

string sql = "select * from [Sys_SerialNos] where [TableId]=" + tableId + " and [RelativeId]=" + relativeId + " and [SubType]=" + (int)subType;

DataRow rowSerialNo = GetTableRowBySql(sql);

long num = 1;

string relativeNo = "";

string currentDateValue = "";

switch (subType)

{

case SerialNoType.年号:

relativeNo = DateTime.Now.ToString("yyyy");

currentDateValue = DateTime.Now.ToString("yyyy");

break;

case SerialNoType.月号:

relativeNo = DateTime.Now.ToString("yyyyMM");

currentDateValue = DateTime.Now.ToString("yyyyMM");

break;

case SerialNoType.日号:

relativeNo = DateTime.Now.ToString("yyyyMMdd");

currentDateValue = DateTime.Now.ToString("yyyyMMdd");

break;

case SerialNoType.年全日期:

relativeNo = DateTime.Now.ToString("yyyyMMdd");

currentDateValue = DateTime.Now.ToString("yyyy");

break;

case SerialNoType.月全日期:

relativeNo = DateTime.Now.ToString("yyyyMMdd");

currentDateValue = DateTime.Now.ToString("yyyyMM");

break;

}

if (rowSerialNo == null)

{

//创建

Insert(new SQLParam()

{

TableName = "Sys_SerialNos",

OpreateCells = new List<KeyValue>()

{

new KeyValue("TableId", tableId),

new KeyValue("RelativeId", relativeId),

new KeyValue("SubType", (int)subType),

new KeyValue("Type", 0),

new KeyValue("Length", serialNoLength),

new KeyValue("Prefix", prefix),

new KeyValue("DateValue", currentDateValue),

new KeyValue("SerialValue", num),

new KeyValue("LastBuildDate", DateTime.Now),

}

});

}

else

{

//号码加1

num = rowSerialNo.GetLong("SerialValue");

num++;

//上一个号的日期

string dateValue = rowSerialNo.GetString("DateValue");

//日期号时需要判断是否重置

if (subType == SerialNoType.年号 || subType == SerialNoType.月号 || subType == SerialNoType.日号 || subType == SerialNoType.年全日期 || subType == SerialNoType.月全日期)

{

if (dateValue != currentDateValue)

{

num = 1;

}

}

//更新序列号表

Update(new SQLParam()

{

TableName = "Sys_SerialNos",

OpreateCells = new List<KeyValue>()

{

new KeyValue("SerialValue", num),

new KeyValue("DateValue", currentDateValue),

new KeyValue("Length", serialNoLength),

new KeyValue("Prefix", prefix),

new KeyValue("LastBuildDate", DateTime.Now),

},

Id = rowSerialNo.GetId()

});

}

string prefixStr = (string.IsNullOrWhiteSpace(prefix) ? "" : prefix + "-");

string numString = num.ToString().PadLeft(serialNoLength, '0');

//流水号

if (subType == SerialNoType.流水号)

{

if (relativeId > 0)

{

relativeNo = relativeId.ToString();

}

else

{

if (string.IsNullOrWhiteSpace(prefix))

{

int beginNum = 1;

for (var i = 1; i < serialNoLength; i++) beginNum *= 10;

beginNum += DataType.Int(numString, 1);

numString = beginNum.ToString();

}

}

}

relativeNo = (string.IsNullOrWhiteSpace(relativeNo) ? "" : relativeNo + "-");

return prefixStr + relativeNo + numString;

}

}

/// <summary>

/// 序列号

/// </summary>

/// <returns></returns>

public static string TranGetSerialNo(SQLiteCommand cmd, long tableId, long relativeId, SerialNoType subType, int serialNoLength = 4, string prefix = "")

{

lock (_lockSerialNo)

{

string sql = "select * from [Sys_SerialNos] where [TableId]=" + tableId + " and [RelativeId]=" + relativeId + " and [SubType]=" + (int)subType;

DataRow rowSerialNo = TranQueryRow(cmd, sql);

long num = 1;

string relativeNo = "";

string currentDateValue = "";

switch (subType)

{

case SerialNoType.年号:

relativeNo = DateTime.Now.ToString("yyyy");

currentDateValue = DateTime.Now.ToString("yyyy");

break;

case SerialNoType.月号:

relativeNo = DateTime.Now.ToString("yyyyMM");

currentDateValue = DateTime.Now.ToString("yyyyMM");

break;

case SerialNoType.日号:

relativeNo = DateTime.Now.ToString("yyyyMMdd");

currentDateValue = DateTime.Now.ToString("yyyyMMdd");

break;

case SerialNoType.年全日期:

relativeNo = DateTime.Now.ToString("yyyyMMdd");

currentDateValue = DateTime.Now.ToString("yyyy");

break;

case SerialNoType.月全日期:

relativeNo = DateTime.Now.ToString("yyyyMMdd");

currentDateValue = DateTime.Now.ToString("yyyyMM");

break;

}

if (rowSerialNo == null)

{

//创建

sql = @"insert into [Sys_SerialNos]([TableId],[RelativeId],[SubType],[Type],[Length],[Prefix],[DateValue],[SerialValue],[LastBuildDate])

values(@TableId,@RelativeId,@SubType,@Type,@Length,@Prefix,@DateValue,@SerialValue,@LastBuildDate)";

SQLiteParameter[] ps = {

new SQLiteParameter() { ParameterName="@TableId", Value=tableId },

new SQLiteParameter() { ParameterName="@RelativeId", Value=relativeId },

new SQLiteParameter() { ParameterName="@SubType", Value=(int)subType },

new SQLiteParameter() { ParameterName="@Type", Value=0 },

new SQLiteParameter() { ParameterName="@Length", Value=serialNoLength },

new SQLiteParameter() { ParameterName="@Prefix", Value=prefix },

new SQLiteParameter() { ParameterName="@DateValue", Value=currentDateValue },

new SQLiteParameter() { ParameterName="@SerialValue", Value=num },

new SQLiteParameter() { ParameterName="@LastBuildDate", Value=DateTime.Now },

};

TranExecute(cmd, sql, ps);

}

else

{

//号码加1

num = rowSerialNo.GetLong("SerialValue");

num++;

//上一个号的日期

string dateValue = rowSerialNo.GetString("DateValue");

//日期号时需要判断是否重置

if (subType == SerialNoType.年号 || subType == SerialNoType.月号 || subType == SerialNoType.日号 || subType == SerialNoType.年全日期 || subType == SerialNoType.月全日期)

{

if (dateValue != currentDateValue)

{

num = 1;

}

}

//更新序列号表

sql = "update [Sys_SerialNos] set [SerialValue]=@SerialValue,[DateValue]=@DateValue,[Length]=@Length,[Prefix]=@Prefix,[LastBuildDate]=@LastBuildDate where Id=" + rowSerialNo.GetId();

SQLiteParameter[] ps = {

new SQLiteParameter() { ParameterName="@SerialValue", Value=num },

new SQLiteParameter() { ParameterName="@DateValue", Value=currentDateValue },

new SQLiteParameter() { ParameterName="@Length", Value=serialNoLength },

new SQLiteParameter() { ParameterName="@Prefix", Value=prefix },

new SQLiteParameter() { ParameterName="@LastBuildDate", Value=DateTime.Now },

};

TranExecute(cmd, sql, ps);

}

string prefixStr = (string.IsNullOrWhiteSpace(prefix) ? "" : prefix + "-");

string numString = num.ToString().PadLeft(serialNoLength, '0');

//流水号

if (subType == SerialNoType.流水号)

{

if (relativeId > 0)

{

relativeNo = relativeId.ToString();

}

else

{

if (string.IsNullOrWhiteSpace(prefix))

{

int beginNum = 1;

for (var i = 1; i < serialNoLength; i++) beginNum *= 10;

beginNum += DataType.Int(numString, 1);

numString = beginNum.ToString();

}

}

}

relativeNo = (string.IsNullOrWhiteSpace(relativeNo) ? "" : relativeNo + "-");

return prefixStr + relativeNo + numString;

}

}

/// <summary>

/// 序列号类型

/// </summary>

public enum SerialNoType

{

/// <summary>

/// 一直向上增

/// </summary>

流水号 = 0,

/// <summary>

/// 每年重置

/// 格式:yyyy-0001

/// </summary>

年号 = 1,

/// <summary>

/// 每月重置

/// 格式:yyyyMM-0001

/// </summary>

月号 = 2,

/// <summary>

/// 每日重置

/// 格式:yyyyMMdd-0001

/// </summary>

日号 = 3,

/// <summary>

/// 每年重置

/// 格式:yyyyMMdd-0001

/// </summary>

年全日期 = 4,

/// <summary>

/// 每月重置

/// 格式:yyyyMMdd-0001

/// </summary>

月全日期 = 5

}

#endregion

#region 数据表

/*

不存在则插入行

insert into Users(UserID,UserName)

select '001','张三' where not exists(select 1 from Users where UserID = '001')

不存在表就创建

create table if not exists Users(

AutoID integer primary key autoincrement,

UserID varhcar(32) default '',

UserName varchar(64) default ''

)

*/

/// <summary>

/// 删除表

/// </summary>

public static void DropTable(string tableName)

{

try

{

string sql = "drop table if exists [" + tableName + "]";

ExecuteSQL(sql);

}

catch { }

}

/// <summary>

/// 表是否存在

/// </summary>

/// <returns></returns>

public static bool TableIsExists(string tableName)

{

try

{

string sql = "select count(*) from [sqlite_master] where [type]='table' AND [name]='" + tableName + @"';";

object obj = GetScalar(sql);

if (obj != null && DataType.Int(obj, 0) > 0) return true;

}

catch (Exception ex) { }

return false;

}

/// <summary>

/// 表重命名

/// </summary>

/// <returns></returns>

public static bool RenameTable(string orgTableName, string newTableName)

{

try

{

string sql = "ALTER TABLE '" + orgTableName + "' RENAME TO '" + newTableName + "';";

return ExecuteSQL(sql);

}

catch (Exception ex) { }

return false;

}

/// <summary>

/// 获取创建表的SQL

/// </summary>

/// <returns></returns>

public static string GetBuildTableSQL(string tableName)

{

try

{

string sql = "SELECT * FROM [sqlite_master] WHERE type='table' and name='" + tableName + "'";

DataRow row = GetTableRowBySql(sql);

if (row != null) return row["sql"].ToString();

}

catch (Exception ex) { }

return string.Empty;

}

/// <summary>

/// 获取表结构

/// </summary>

/// <param name="tableName"></param>

/// <returns></returns>

public static DataTable GetTableStruct(string tableName)

{

try

{

string sql = "SELECT * FROM [" + tableName + "] limit 1";

DataTable dt = GetTableBySQL(sql);

if (dt.Rows.Count > 0) dt.Rows.RemoveAt(0);

return dt;

}

catch (Exception ex) { }

return null;

}

/// <summary>

/// 列是否存在

/// </summary>

/// <returns></returns>

public static bool TableColumnIsExists(string tableName, string columnName)

{

try

{

//得到SQLCMD对象

SQLiteCommand cmd = GetSQLiteCmd();

if (cmd == null) throw new Exception("WsflyException:未找到数据库连接!");

//查询数据

string sql = "select * from [" + tableName + "] limit 0;";

DataTable dt = QueryDataTable(cmd, sql);

return dt.Columns.Contains(columnName);

}

catch (Exception ex) { }

return false;

}

/// <summary>

/// 添加列

/// </summary>

/// <returns></returns>

public static bool AddColumn(string tableName, string columnName, string type = "varchar")

{

try

{

bool isExists = TableColumnIsExists(tableName, columnName);

if (isExists) return true;

string sql = "alter table [" + tableName + "] add column [" + columnName + "] " + type + ";";

ExecuteSQL(sql);

return true;

}

catch (Exception ex) { }

return false;

}

#endregion

#region 年月日计数

/// <summary>

/// 检查年月日计数表

/// </summary>

/// <param name="tableName"></param>

/// <returns></returns>

public static bool CheckYMDTable(string tableName)

{

try

{

string sql = "select count(*) from [sqlite_master] where [type]='table' AND [name]='" + tableName + @"';";

object obj = GetScalar(sql);

if (obj != null && DataType.Int(obj, 0) > 0) return true;

sql = @"

CREATE TABLE [" + tableName + @"] (

Id INTEGER PRIMARY KEY UNIQUE,

[Y] INT, [M] INT,

[D1] INT,[D2] INT,[D3] INT,[D4] INT,[D5] INT,[D6] INT,[D7] INT,[D8] INT,[D9] INT,[D10] INT,

[D11] INT,[D12] INT,[D13] INT,[D14] INT,[D15] INT,[D16] INT,[D17] INT,[D18] INT,[D19] INT,[D20] INT,

[D21] INT,[D22] INT,[D23] INT,[D24] INT,[D25] INT,[D26] INT,[D27] INT,[D28] INT,[D29] INT,[D30] INT,[D31] INT

);

";

ExecuteSQL(sql);

return true;

}

catch (Exception ex) { }

return false;

}

/// <summary>

/// 年月日计数表递增

/// </summary>

/// <param name="date"></param>

/// <param name="increment"></param>

/// <returns></returns>

public static bool YMDTableIncrement(string tableName, DateTime date, int increment = 1)

{

try

{

int year = date.Year;

int month = date.Month;

int day = date.Day;

string sql = "select count(*) from [" + tableName + "] where [Y]=" + year + " and [M]=" + month;

object obj = GetScalar(sql);

sql = "";

if (obj == null || DataType.Int(obj, 0) < 1)

{

sql += @"

insert into [" + tableName + @"]([Y],[M],[D1],[D2],[D3],[D4],[D5],[D6],[D7],[D8],[D9],[D10],[D11],[D12],[D13],[D14],[D15],[D16],[D17],[D18],[D19],[D20],[D21],[D22],[D23],[D24],[D25],[D26],[D27],[D28],[D29],[D30],[D31])

values(" + year + @"," + month + @",0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0);

";

}

sql += @"update [" + tableName + @"] set [D" + day + @"]=[D" + day + @"]+" + increment + @" where [Y]=" + year + @" and [M]=" + month + ";";

return ExecuteSQL(sql);

}

catch (Exception ex) { }

return false;

}

#endregion

#region 扩展

/// <summary>

/// 字段值递增

/// </summary>

/// <returns></returns>

public static bool Incremental(string tableName, string colName, long id, int increment = 1)

{

try

{

string sql = @"update [" + tableName + "] set [" + colName + "]=[" + colName + "]+1 where Id=" + id;

return ExecuteSQL(sql);

}

catch { }

return false;

}

#endregion

}

}

下载地址:

https://download.csdn.net/download/lyp1215/87577299

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

MOZ-Soft

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值