/// <summary> /// 获取所有用户信息表 /// </summary> /// <returns></returns> public static List <string> GetAllTables(SqliteContext context) { try { var tables = context.Find(new SQLiteString("select * from sqlite_master where type = 'table'")); return(tables.Select(table => DynamicConvert.ToSafeString(table.name)).Cast <string>().ToList()); } catch { LogHelper.Error("上层获取数据库表信息失败. 转底层获取!"); return(ButtomGetAllTables(context.DataSource, "")); } }
/// <summary> /// 获取表中的主键信息; /// </summary> /// <param name="context"></param> /// <param name="tableName"></param> /// <returns></returns> private static List <string> GetPrimaryKey(SqliteContext context, string tableName) { var keyAll = new List <string>(); try { var sqlite = new SqliteContext("") { DataSource = context.DataSource.Replace("_recovery", "") }; var data = sqlite.Find( new SQLiteString("select * from sqlite_master where type = 'table' and tbl_name = '" + tableName + "';")); if (data == null || data.Count == 0) { return(keyAll); } var lists = Regex.Matches(DynamicConvert.ToSafeString(data[0].sql), @"\(.*\)"); if (lists.Count == 0) { return(keyAll); } var columns = lists[0].Value.TrimStart('(').TrimEnd(')').Split(','); foreach (var column in columns) { if (!column.Contains("PRIMARY KEY")) { continue; } var result = Regex.Matches(column, "'.*'"); if (result.Count == 0 || result.Count > 1) { continue; } keyAll.Add(DynamicConvert.ToSafeString(result[0].Value).Replace("'", "")); } } catch (Exception ex) { LogHelper.Error(string.Format("获取表{0}主键信息失败", tableName), ex); } return(keyAll); }
/// <summary> /// 创建表构架。 /// </summary> private static bool CreateTable(SqliteContext context, string tableName, IList <string> allColumnNames, IList <string> allColumnTypes) { // 获取当前表中主键字段; var primaryKeys = GetPrimaryKey(context, tableName); //拼接创建表的SQL语句 var fieldBuilder = new StringBuilder(); for (int i = 0; i < allColumnNames.Count; i++) { //if (i == 0 && (allColumnNames[0].ToUpper().Equals("_ID") || allColumnNames[0].ToUpper().Equals("ID")) && allColumnTypes[0].Contains("INT")) if (primaryKeys.Contains(allColumnNames[0].ToUpper())) { fieldBuilder.Append(allColumnNames[i] + " " + allColumnTypes[i] + " PRIMARY KEY,"); } else { fieldBuilder.Append(allColumnNames[i] + " " + allColumnTypes[i] + ","); } } fieldBuilder.Append(NewColumnName + " INTEGER"); //创建数据表 string createTableSql = string.Format("create table {0}({1})", tableName, fieldBuilder); try { context.ExecuteNonQuery(createTableSql); } catch (Exception ex) { LogHelper.Error(string.Format("创建表{0}构造失败,创建表的SQL语句:\n{1}", tableName, createTableSql), ex); return(false); } return(true); }
/// <summary> /// 上层C#数据处理。 /// 上传恢复的数据只有删除的数据。 /// </summary> /// <param name="sourcedb">数据源db。</param> /// <param name="newfile">新备份文件db。</param> /// <param name="tableArray">表列表。</param> /// <returns>返回新的备份文件db。</returns> private static string TopDataRecovery(string sourcedb, string newfile, IEnumerable <string> tableArray) { var oldcontext = new SqliteContext(sourcedb); System.IO.File.Copy(sourcedb, newfile, true); var newcontext = new SqliteContext(newfile); foreach (var tableName in tableArray) { //判断原库中是否含有表,若存在该表则处理,否则不处理。 if (!IsExistTable(oldcontext, tableName)) { LogHelper.Warn(string.Format("Sqlite数据库恢复-上层C#恢复库【{0}】的表【{1}】失败,可能是表名拼写不准确(如大小写或表名存在特殊字符等)", sourcedb, tableName)); continue; } //添加新的一列 string alterTablesql = string.Format("ALTER TABLE {0} ADD COLUMN {1} INTEGER default 2", tableName, NewColumnName); newcontext.ExecuteNonQuery(alterTablesql); } newcontext.Dispose(); return(newfile); }
/// <summary> /// 底层c++处理数据库。 /// 数据库处理后,请使用新数据库进行查找。 /// </summary> /// <param name="sourceDb">源数据库路径。</param> /// <param name="charatorPath">特征库文件路径。</param> /// <param name="newDbPath">临时数据库路径。</param> /// <param name="tableNames">表名(特别注意表名拼写正确)集合,多个表之间请按照","分隔,如 t1,t2,t3</param> /// <returns>处理结果,成功则SqliteReturn对象的IsSucess = true,StackMsg为空。 /// 注意若是多个表进行同时处理,只要有一个表处理成功,IsSucess= true。 /// 其他异常错误信息,可从StackMsg属性中获取概要信息;出错时查看日志文件,可得到更多栈信息。</returns> private static SqliteReturn ButtomDataRecovery(string sourceDb, string charatorPath, string newDbPath, string[] tableNames) { var sqliteReturn = new SqliteReturn { IsSucess = false, StackMsg = string.Empty }; if (sourceDb.IsNullOrEmpty() || newDbPath.IsNullOrEmpty() || tableNames.Length <= 0) { sqliteReturn.StackMsg = "传入参数【源数据库路径,备份路径,表列表】不能存在空值。"; return(sqliteReturn); } var context = new SqliteContext(newDbPath); bool isInit = false; IntPtr dbBase = IntPtr.Zero; var stackMsgBuilder = new StringBuilder(); bool isSuccess = true; foreach (var tableName in tableNames) { //判断是否含有表,则直接跳过处理。 if (IsExistTable(context, tableName)) { stackMsgBuilder.AppendLine(string.Format("副本数据库 【{0}】中已经存在表【{1}】,系统未对该表进行处理。", newDbPath, tableName)); continue; } //判断是否初始化,若初始化底层失败,则返回。 if (isInit == false) { if (InitDb(sourceDb, charatorPath, ref dbBase)) { isInit = true; } else { stackMsgBuilder.AppendLine("SQLite底层DLl初始化失败。可能原因"); stackMsgBuilder.AppendLine("1:程序未使用管理员权限运行。"); stackMsgBuilder.AppendLine("2:底层DLL缺少必要的Key文件。"); isSuccess = false; break; } } //获取表定义 IList <string> allColumnNames; IList <string> allColumnTypes; GetTableDefin(dbBase, tableName, out allColumnNames, out allColumnTypes); if (allColumnNames.Count == 0) { isSuccess = false; stackMsgBuilder.AppendLine(string.Format("无法获取表【{0}】的定义,可能原因:", tableName)); stackMsgBuilder.AppendLine(string.Format("1:原数据库中不存在表【{0}】,请注意表名大小写(用SQLite工具查看核实)。", tableName)); stackMsgBuilder.AppendLine("2:c++底层DLL存在错误。"); break; } #region 创建表,插入数据 //创建表架构 if (CreateTable(context, tableName, allColumnNames, allColumnTypes)) { //从底层获取当前表的数据。 GetTableAllData(dbBase, tableName); //若底层获取了数据,则把底层的数据写入副本对应的表中。 if (_AllNewRowData.Count > 0) { //高效批量插入多条数据(采用事务机制) // 高效事务批量插入数据库。 // 由于SQLite特殊性,它是文件存储的,每一次插入都是一次IO操作 //为了高效插入,引入事务机制,先在内存中插入,最后一次性提交到数据库。 try { context.UsingSafeTransaction(command => { //获取插入表的SQL(多条)语句。 var notIdColumns = new StringBuilder(); allColumnNames.Skip(1).ForEach(name => notIdColumns.Append(name + ",")); notIdColumns.Append(NewColumnName); var hasIdColumns = new StringBuilder(); // 过滤字段中特殊符号(`); for (int i = 0; i < allColumnNames.Count; i++) { allColumnNames[i] = allColumnNames[i].Replace("`", ""); } allColumnNames.ForEach(name => hasIdColumns.Append(name + ",")); hasIdColumns.Append(NewColumnName); //对数据进行分组合并,按正常、删除、碎片顺序存储。 var allRowData = GetAllRowData(); foreach (var row in allRowData) { var parameters = new List <SQLiteParameter>(); var valuesHead = string.Empty; StringBuilder columns; if (row[0].Value.ToString().IsNullOrEmpty()) {//主键为空,一般为Integer的数字 columns = notIdColumns; } else {//主键为存在 columns = hasIdColumns; string primaryKeyId = "@" + allColumnNames[0]; var parameter = new SQLiteParameter(primaryKeyId, DbType.String); if (row[0].Type == ColumnType.BLOB) { parameter.DbType = DbType.Binary; } parameter.Value = row[0].Value; valuesHead = primaryKeyId + ","; parameters.Add(parameter); } var valuesBody = new StringBuilder(); valuesBody.Append(valuesHead); var formTwoDatas = row.Skip(1); int columnIndex = 1; foreach (var cell in formTwoDatas) { string paramName = "@" + allColumnNames[columnIndex]; var parameter = new SQLiteParameter(paramName, DbType.String); if (cell.Type == ColumnType.BLOB) { parameter.DbType = DbType.Binary; } parameter.Value = cell.Value; parameters.Add(parameter); valuesBody.Append(paramName + ","); columnIndex++; } valuesBody.Append(row[0].DataState); var insertSql = string.Format("insert into {0}({1}) values({2})", tableName, columns, valuesBody); command.CommandText = insertSql; command.Parameters.AddRange(parameters.ToArray()); try { command.ExecuteNonQuery(); } catch (Exception ex) { var msg = string.Format("Sqlite插入表【{0}】发生异常 \n SQL语句为: {1}", tableName, insertSql); LogHelper.Warn(msg, ex); } } }); } catch (Exception ex) { var msg = string.Format("Sqlite数据库恢复-Sqlite插入表【{0}】发生异常", tableName); stackMsgBuilder.AppendLine(msg); LogHelper.Warn(msg, ex); isSuccess = false; break; } } } else { isSuccess = false; stackMsgBuilder.AppendLine(string.Format("无法在备份库中创建表【{0}】,详细信息可在日志文件中查看。", tableName)); break; } #endregion } //清理资源 DisposeSource(dbBase); sqliteReturn.IsSucess = isSuccess; sqliteReturn.StackMsg = stackMsgBuilder.ToString(); return(sqliteReturn); }
/// <summary> /// 是否存在某个表。 /// </summary> /// <param name="context">SQLite上下文对象。</param> /// <param name="tableName">表名字(注意大小写)。</param> /// <returns>存在返回True,反之返回false。</returns> public static bool IsExistTable(SqliteContext context, string tableName) { return(context.Exist(tableName)); }