static void Main(string[] args) { String fileName = @"D:\test.db"; // create a database "context" object t String connectionString = String.Format("Data Source={0};Version=3", fileName); DbProviderFactory sqlFactory = new System.Data.SQLite.SQLiteFactory(); PetaPoco.Database db = new PetaPoco.Database(connectionString, sqlFactory); #region 创建测试表1 //string dropQuery = "DROP TABLE qrtz_operatelog"; //db.Execute(dropQuery); //String createQuery = // @"CREATE TABLE IF NOT EXISTS // [User] ( // [id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, // [Name] VARCHAR(20) NOT NULL DEFAULT 'tww', // [Phone] VARCHAR(20) NOT NULL, // [Email] VARCHAR(20) NOT NULL, // [Password] VARCHAR(20) NOT NULL, // [Active] INTEGER NOT NULL DEFAULT 1, // [CreateTime] TEXT NOT NULL // )"; String createQuery = @"DROP TABLE qrtz_operatelog; CREATE TABLE IF NOT EXISTS [qrtz_operatelog] ( [id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, [TableName] VARCHAR(255) , [Describe] VARCHAR(255) , [Type] INTEGER NOT NULL, [CreateTime] datetime NOT NULL, [UpdateTime] datetime )"; db.Execute(createQuery); createQuery = @"DROP TABLE Schedule; CREATE TABLE IF NOT EXISTS [Schedule] ( [JobId] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, [JobName] VARCHAR(255) , [JobGroup] VARCHAR(255) , [JobStatus] INTEGER NOT NULL, [Cron] varchar(50) , [AssemblyName] varchar(50) NOT NULL, [ClassName] varchar(50) NOT NULL, [Remark] VARCHAR(255) , [CreateTime] datetime NOT NULL, [UpdateTime] datetime , [RunTimes] INTEGER NOT NULL, [BeginTime] datetime NOT NULL, [EndTime] datetime , [TriggerType] INTEGER NOT NULL, [IntervalSecond] INTEGER NOT NULL, [Url] VARCHAR(255) )"; db.Execute(createQuery); }
/// <summary> /// 添加或替换项目 /// </summary> /// <param name="zipFile">压缩包</param> /// <param name="catalogNumber">编号</param> /// <param name="sourceFile">源文件</param> /// <returns>CatalogID</returns> public string addOrReplaceProject(string zipFile, string catalogNumber, string sourceFile) { //SQLite数据库工厂 System.Data.SQLite.SQLiteFactory factory = new System.Data.SQLite.SQLiteFactory(); //NDEY数据库连接 Noear.Weed.DbContext context = new Noear.Weed.DbContext("main", "Data Source = " + sourceFile, factory); //是否在执入后执行查询(主要针对Sqlite) context.IsSupportSelectIdentityAfterInsert = false; //是否在Dispose后执行GC用于解决Dispose后无法删除的问题(主要针对Sqlite) context.IsSupportGCAfterDispose = true; try { return(importDB(zipFile, catalogNumber, sourceFile, context)); } catch (Exception ex) { PublicManager.Modules.Module_A.PkgImporter.Forms.ImporterForm.writeImportLog(PublicManager.Modules.Module_A.PkgImporter.Forms.ImporterForm.errorlogFilePath, "错误", "对不起,压缩文件(" + sourceFile + ")导入时出错!请检查!Ex:" + ex.ToString()); BaseModuleMainFormWithNoUIConfig.writeLog(ex.ToString()); return(string.Empty); } finally { factory.Dispose(); context.Dispose(); } }
public SQLite(string connectionstring) { DbProviderFactory fact = new System.Data.SQLite.SQLiteFactory(); //DbProviderFactories.GetFactory("System.Data.SQLite"); this.connection = fact.CreateConnection(); this.connection.ConnectionString = connectionstring; }
/// <summary> /// 添加或替换项目 /// </summary> /// <param name="catalogNumber">catalogNumber</param> /// <param name="sourceFile">源文件</param> /// <returns>CatalogID</returns> public string addOrReplaceProject(string catalogNumber, string sourceFile) { //SQLite数据库工厂 System.Data.SQLite.SQLiteFactory factory = new System.Data.SQLite.SQLiteFactory(); //NDEY数据库连接 Noear.Weed.DbContext context = new Noear.Weed.DbContext("main", "Data Source = " + sourceFile, factory); //是否在执入后执行查询(主要针对Sqlite) context.IsSupportSelectIdentityAfterInsert = false; //是否在Dispose后执行GC用于解决Dispose后无法删除的问题(主要针对Sqlite) context.IsSupportGCAfterDispose = true; try { return(importDB(catalogNumber, sourceFile, context)); } catch (Exception ex) { System.Console.WriteLine(ex.ToString()); return(string.Empty); } finally { factory.Dispose(); context.Dispose(); } }
/// <summary> /// 初始化数据库连接 /// </summary> /// <param name="dbFile"></param> public static void Open(string dbFile) { factory = new System.Data.SQLite.SQLiteFactory(); Context = new DbContext("main", "Data Source=" + dbFile, factory); Context.IsSupportInsertAfterSelectIdentity = false; Context.IsSupportGCAfterDispose = true; }
private static PetaPoco.Database GetDatabase() { // A sqlite database is just a file. string connectionString = "Data Source=" + HttpContext.Current.Server.MapPath("~/ingress.db") + ";Version=3;"; DbProviderFactory sqlFactory = new System.Data.SQLite.SQLiteFactory(); PetaPoco.Database db = new PetaPoco.Database(connectionString, sqlFactory); return(db); }
static void Test1(string connString) { Console.WriteLine("Begin Test1"); using (var factory = new System.Data.SQLite.SQLiteFactory()) using (System.Data.Common.DbConnection dbConn = factory.CreateConnection()) { dbConn.ConnectionString = connString; dbConn.Open(); using (System.Data.Common.DbCommand cmd = dbConn.CreateCommand()) { //create table cmd.CommandText = @"CREATE TABLE IF NOT EXISTS T1 (ID integer primary key, T text);"; cmd.ExecuteNonQuery(); //parameterized insert cmd.CommandText = @"INSERT INTO T1 (ID,T) VALUES(@id,@t)"; var p1 = cmd.CreateParameter(); p1.ParameterName = "@id"; p1.Value = 1; var p2 = cmd.CreateParameter(); p2.ParameterName = "@t"; p2.Value = "test1"; cmd.Parameters.Add(p1); cmd.Parameters.Add(p2); cmd.ExecuteNonQuery(); //read from the table cmd.CommandText = @"SELECT ID, T FROM T1"; using (System.Data.Common.DbDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { long id = reader.GetInt64(0); string t = reader.GetString(1); Console.WriteLine("record read as id: {0} t: {1}", id, t); } } cmd.Dispose(); } if (dbConn.State != System.Data.ConnectionState.Closed) dbConn.Close(); dbConn.Dispose(); factory.Dispose(); } Console.WriteLine("End Test1"); }
public static void Close() { try { factory.Dispose(); } catch (Exception ex) { } factory = null; try { Context.Dispose(); } catch (Exception ex) { } Context = null; }
public static void OpenDB(string DBFile = @"veekun-pokedex.sqlite") { if (con != null) return; try { var connString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=true;", DBFile); using (var factory = new System.Data.SQLite.SQLiteFactory()) con = factory.CreateConnection(); con.ConnectionString = connString; con.Open(); } catch (Exception ex) { Debug.WriteLine("Error opening database: {0}", ex.Message); } }
public static void OpenDB(string DBFile) { if (con != null) { return; } try { var connString = string.Format(@"Data Source=""{0}""; Pooling=false; FailIfMissing=false;", DBFile); using (var factory = new System.Data.SQLite.SQLiteFactory()) con = factory.CreateConnection(); con.ConnectionString = connString; con.Open(); } catch (Exception ex) { System.Diagnostics.Debug.WriteLine("Error opening database: {0}", ex.Message); } }
/// <summary> /// 获得项目信息 /// </summary> /// <param name="projectDir"></param> /// <returns></returns> public JiBenXinXiBiao getProjectObject(string projectDir) { JiBenXinXiBiao proj = null; string dbFile = System.IO.Path.Combine(projectDir, "static.db"); if (System.IO.File.Exists(dbFile)) { System.Data.SQLite.SQLiteFactory factory = new System.Data.SQLite.SQLiteFactory(); Noear.Weed.DbContext context = new Noear.Weed.DbContext("main", "Data Source=" + dbFile, factory); context.IsSupportInsertAfterSelectIdentity = false; context.IsSupportGCAfterDispose = true; try { proj = context.table("JiBenXinXiBiao").select("*").getItem <JiBenXinXiBiao>(new JiBenXinXiBiao()); } catch (Exception ex) { throw new Exception(ex.ToString()); } finally { try { factory.Dispose(); } catch (Exception ex) { } factory = null; try { context.Dispose(); } catch (Exception ex) { } context = null; } } return(proj); }
/// <summary> /// 添加或替换项目 /// </summary> /// <param name="catalogNumber">catalogNumber</param> /// <param name="sourceFile">源文件</param> /// <returns>CatalogID</returns> public static string addOrReplaceProject(string catalogNumber, string sourceFile) { //SQLite数据库工厂 System.Data.SQLite.SQLiteFactory factory = new System.Data.SQLite.SQLiteFactory(); //NDEY数据库连接 Noear.Weed.DbContext context = new Noear.Weed.DbContext("main", "Data Source = " + sourceFile, factory); //是否在执入后执行查询(主要针对Sqlite) context.IsSupportInsertAfterSelectIdentity = false; //是否在Dispose后执行GC用于解决Dispose后无法删除的问题(主要针对Sqlite) context.IsSupportGCAfterDispose = true; try { //提取所有旧的Project数据 List <Project> oldProjects = context.table("Project").select("*").getList <Project>(new Project()); //项目信息 Project mainProject = context.table("Project").where ("Type = '项目'").select("*").getItem <Project>(new Project()); //判断数据库是否有内容 if (oldProjects != null && oldProjects.Count >= 1 && !string.IsNullOrEmpty(mainProject.ID)) { //项目名称 string projectName = mainProject.Name; //申请人 string projectCreater = context.table("Person").where ("ID in (select PersonID from Task where Type = '项目' and Role = '负责人')").select("Name").getValue <string>(string.Empty); //目录ID string currentCatalogID = string.Empty; //查询Catalog记录 Catalog catalogItem = ConnectionManager.Context.table("Catalog").where ("ProjectName='" + projectName + "' and ProjectCreater='" + projectCreater + "'").select("*").getItem <Catalog>(new Catalog()); //如果没有找到则添加新的 if (string.IsNullOrEmpty(catalogItem.CatalogID)) { //创建一个CatalogID currentCatalogID = Guid.NewGuid().ToString(); //添加记录 catalogItem.CatalogID = currentCatalogID; catalogItem.CatalogNumber = catalogNumber; catalogItem.ProjectName = projectName; catalogItem.ProjectCreater = projectCreater; catalogItem.ProjectCreaterUnitID = mainProject.UnitID; catalogItem.copyTo(ConnectionManager.Context.table("Catalog")).insert(); } else { //设置CatalogID currentCatalogID = catalogItem.CatalogID; //更新记录 catalogItem.CatalogNumber = catalogNumber; catalogItem.ProjectCreaterUnitID = mainProject.UnitID; catalogItem.copyTo(ConnectionManager.Context.table("Catalog")).where ("CatalogID = '" + currentCatalogID + "'").update(); } //根据CatalogID清空本地数据库(除了Catalog表) clearProjectDataWithCatalogID(currentCatalogID); //先导入除Task表之外的所有表格 #region 直接导入Project ExtFileList MoneyAndYear Step ProjectAndStep WhiteList 这些表格的数据 //插入Project表 foreach (Project p in oldProjects) { //设置CatalogID p.CatalogID = currentCatalogID; //插入Project p.copyTo(ConnectionManager.Context.table("Project")).insert(); } //插入ExtFileList表 List <ExtFileList> oldExtFileLists = context.table("ExtFileList").select("*").getList <ExtFileList>(new ExtFileList()); foreach (ExtFileList efl in oldExtFileLists) { //设置CatalogID efl.CatalogID = currentCatalogID; //插入ExtFileList efl.copyTo(ConnectionManager.Context.table("ExtFileList")).insert(); } //插入MoneyAndYear表 List <MoneyAndYear> oldMoneyAndYears = context.table("MoneyAndYear").select("*").getList <MoneyAndYear>(new MoneyAndYear()); foreach (MoneyAndYear efl in oldMoneyAndYears) { //设置CatalogID efl.CatalogID = currentCatalogID; //插入MoneyAndYear efl.copyTo(ConnectionManager.Context.table("MoneyAndYear")).insert(); } //插入ProjectAndStep表 List <ProjectAndStep> oldProjectAndSteps = context.table("ProjectAndStep").select("*").getList <ProjectAndStep>(new ProjectAndStep()); foreach (ProjectAndStep efl in oldProjectAndSteps) { //设置CatalogID efl.CatalogID = currentCatalogID; //插入ProjectAndStep efl.copyTo(ConnectionManager.Context.table("ProjectAndStep")).insert(); } //插入Step表 List <Step> oldSteps = context.table("Step").select("*").getList <Step>(new Step()); foreach (Step efl in oldSteps) { //设置CatalogID efl.CatalogID = currentCatalogID; //插入Step efl.copyTo(ConnectionManager.Context.table("Step")).insert(); } //插入WhiteList表 List <WhiteList> oldWhiteLists = context.table("WhiteList").select("*").getList <WhiteList>(new WhiteList()); foreach (WhiteList efl in oldWhiteLists) { //设置CatalogID efl.CatalogID = currentCatalogID; //插入WhiteList efl.copyTo(ConnectionManager.Context.table("WhiteList")).insert(); } #endregion #region 对于Person和Unit表进行选择性导入 //插入Person表 List <Person> oldPersons = context.table("Person").select("*").getList <Person>(new Person()); foreach (Person efl in oldPersons) { //设置CatalogID efl.CatalogID = currentCatalogID; try { //是否被使用 object objResult = context.table("Task").where ("PersonID = '" + efl.ID + "'").select("count(*)").getValue(); if (objResult != null && int.Parse(objResult.ToString()) >= 1) { //有被使用,直接插入Person就好 efl.copyTo(ConnectionManager.Context.table("Person")).insert(); } } catch (Exception ex) { } } //插入Unit表 List <Unit> oldUnits = context.table("Unit").select("*").getList <Unit>(new Unit()); foreach (Unit efl in oldUnits) { //设置CatalogID efl.CatalogID = currentCatalogID; //查询Person,Project,WhiteList检查Unit有没有被使用 object objUnitExist11 = context.table("Person").where ("UnitID='" + efl.ID + "'").select("count(*)").getValue(); object objUnitExist22 = context.table("Project").where ("UnitID='" + efl.ID + "'").select("count(*)").getValue(); object objUnitExist33 = context.table("WhiteList").where ("UnitID='" + efl.ID + "'").select("count(*)").getValue(); int useUnitCount = 0; try { if (objUnitExist11 != null && int.Parse(objUnitExist11.ToString()) >= 1) { useUnitCount++; } } catch (Exception ex) { } try { if (objUnitExist22 != null && int.Parse(objUnitExist22.ToString()) >= 1) { useUnitCount++; } } catch (Exception ex) { } try { if (objUnitExist33 != null && int.Parse(objUnitExist33.ToString()) >= 1) { useUnitCount++; } } catch (Exception ex) { } if (useUnitCount >= 1) { //插入Unit efl.copyTo(ConnectionManager.Context.table("Unit")).insert(); } } #endregion //处理Task表 #region 处理Task错误 /** * 第一步先检查Task表是否正常(Role字段是否只有成员或负责人这二个词) * 如果正常直接导入 * 如果不正常则尝试修复,操作如下: * 优先处理负责人 * 先找到正确的那条负责人记录,以这条记录为基准,把错误的那条记录的任务分工和每年工作时间(月)往这个里面合并 * 再处理成员 * 先找到正确的那条成员记录,以这条记录为基准,把错误的那条记录的任务分工和每年工作时间(月)往这个里面合并 */ //取出所有旧的Task记录 List <Task> oldTasks = context.table("Task").select("*").getList <Task>(new Task()); //正确的职务信息数量 int rightPersonCount = 0; //给所有记录设置CatalogID并且检查数据是否正常或被修复过 foreach (Task t in oldTasks) { //设置CatalogID t.CatalogID = currentCatalogID; //判断Role里的数值是否正确并计数 if (t.Role == "成员" || t.Role == "负责人") { rightPersonCount++; } } //判断数据库是否正常或被修复过 if (rightPersonCount == oldTasks.Count) { #region 可以正常导入 //1 导入项目负责人 foreach (Task t in oldTasks) { if (t.Type == "项目" && t.Role == "负责人") { t.copyTo(ConnectionManager.Context.table("Task")).insert(); break; } } //2 导入课题负责人 foreach (Project p in oldProjects) { if (p.ID == mainProject.ID) { //忽略项目 continue; } else { foreach (Task t in oldTasks) { if (t.Type == "课题" && t.Role == "负责人" && t.ProjectID == p.ID) { t.copyTo(ConnectionManager.Context.table("Task")).insert(); break; } } } } //3 导入课题成员 foreach (Project p in oldProjects) { if (p.ID == mainProject.ID) { //忽略项目 continue; } else { foreach (Task t in oldTasks) { if (t.Type == "课题" && t.Role == "成员" && t.ProjectID == p.ID) { object objExistResult = ConnectionManager.Context.table("Task").where ("IDCard='" + t.IDCard + "' and ProjectID = '" + t.ProjectID + "'").select("count(*)").getValue(); if (objExistResult != null && int.Parse(objExistResult.ToString()) >= 1) { continue; } else { t.copyTo(ConnectionManager.Context.table("Task")).insert(); } } } } } #endregion } else { List <Task> tempTasks = new List <Task>(); tempTasks.AddRange(oldTasks); oldTasks.Clear(); #region 修复课题负责人及成员 //过滤出项目负责人和课题成员 foreach (Task t in tempTasks) { if (t.Type == "项目") { if (t.Role == "负责人") { oldTasks.Add(t); } } else if (t.Type == "课题") { if (t.Role.Contains("成员")) { t.Role = "成员"; oldTasks.Add(t); } } else { throw new Exception("对不起,数据库中Task表有错误,请检查!文件路径:" + sourceFile); } } //修复课题负责人 foreach (Project p in oldProjects) { if (p.Type == "项目") { continue; } Task master = null; List <Task> masterList = new List <Task>(); foreach (Task t in tempTasks) { if (t.ProjectID == p.ID && t.Type == "课题" && t.Role.Contains("负责人")) { if (t.Role == "负责人") { master = t; } else { masterList.Add(t); } } } //如果没有找到不正常的负责人,只有一个正常的那就直接添加进去 if (masterList.Count == 0 && master != null) { oldTasks.Add(master); } else { //判断是不是有角色名称包括负责人的记录 if (masterList.Count >= 1) { //如果没有找到正确的记录,就取最后一个 if (master == null && masterList.Count >= 1) { //取最后一个记录 master = masterList[masterList.Count - 1]; //删除这个记录 masterList.Remove(master); } //设置Role为负责人 master.Role = "负责人"; //如果只有一个不正常的负责人,那就把Role替换成负责人直接加进去 if (masterList.Count == 0 && master != null) { oldTasks.Add(master); } else { //反转MasterList,从最后一个记录开始取数 masterList.Reverse(); //检查课题金额、工作时间(月)、任务分工这三个属性是否需要填充,这个时候就需要从错的那些记录里找金额 foreach (Task t in masterList) { //检查是否需要填充金额 if (t.TotalMoney > 0 && master.TotalMoney <= 0) { master.TotalMoney = t.TotalMoney; } //检查是否需要填充工作时间 if (t.TotalTime > 0 && master.TotalTime <= 0) { master.TotalTime = t.TotalTime; } //检查是否需要填充任务分工 if (t.Content != null && t.Content.Trim().Length >= 1 && string.IsNullOrEmpty(master.Content)) { master.Content = t.Content; } } //将修改过的负责人添加到列表中 oldTasks.Add(master); } } else { throw new Exception("对不起,数据库中Task表有错误,请检查!文件路径:" + sourceFile); } } } #endregion #region 导入数据 //1 导入项目负责人 foreach (Task t in oldTasks) { if (t.Type == "项目" && t.Role == "负责人") { t.copyTo(ConnectionManager.Context.table("Task")).insert(); break; } } //2 导入课题负责人 foreach (Project p in oldProjects) { if (p.ID == mainProject.ID) { //忽略项目 continue; } else { foreach (Task t in oldTasks) { if (t.Type == "课题" && t.Role == "负责人" && t.ProjectID == p.ID) { t.copyTo(ConnectionManager.Context.table("Task")).insert(); break; } } } } //3 导入课题成员 foreach (Project p in oldProjects) { if (p.ID == mainProject.ID) { //忽略项目 continue; } else { foreach (Task t in oldTasks) { if (t.Type == "课题" && t.Role == "成员" && t.ProjectID == p.ID) { object objExistResult = ConnectionManager.Context.table("Task").where ("IDCard='" + t.IDCard + "' and ProjectID = '" + t.ProjectID + "'").select("count(*)").getValue(); if (objExistResult != null && int.Parse(objExistResult.ToString()) >= 1) { continue; } else { t.copyTo(ConnectionManager.Context.table("Task")).insert(); } } } } } #endregion } #endregion return(currentCatalogID); } else { //空数据库 throw new Exception("对不起,数据库有错误,请检查!文件路径:" + sourceFile); } } finally { factory.Dispose(); context.Dispose(); } }
public void switchDB(string dbFile) { System.Data.SQLite.SQLiteFactory factory = new System.Data.SQLite.SQLiteFactory(); DbContext context = new DbContext("main", "Data Source=" + dbFile, factory); //是否在执入后执行查询(主要针对Sqlite) context.IsSupportInsertAfterSelectIdentity = false; //是否在Dispose后执行GC用于解决Dispose后无法删除的问题(主要针对Sqlite) context.IsSupportGCAfterDispose = true; //ID字典(Key=表名,Value=<Key=旧的ID,新的ID>) Dictionary <string, Dictionary <string, string> > keyDicts = new Dictionary <string, Dictionary <string, string> >(); try { #region 更新Project表的项目ID List <Project> lProjects = context.table("Project").select("*").getList <Project>(new Project()); //更新项目主键ID foreach (Project p in lProjects) { //检查是否需要创建字典 if (keyDicts.ContainsKey("Project") == false) { keyDicts["Project"] = new Dictionary <string, string>(); } //旧的记录ID string oldID = p.ID; //生成新的ID p.ID = Guid.NewGuid().ToString(); //保存新的ID keyDicts["Project"][oldID] = p.ID; //更新记录ID p.copyTo(context.table("Project")).where ("ID=\"" + oldID + "\"").update(); } //更新课题的ParentID foreach (Project p in lProjects) { //遇到项目信息就不处理 if (string.IsNullOrEmpty(p.ParentID)) { continue; } else { //在字典中查找指定的项目ID,获得新的项目ID if (keyDicts["Project"].ContainsKey(p.ParentID)) { p.ParentID = keyDicts["Project"][p.ParentID]; p.copyTo(context.table("Project")).where ("ID=\"" + p.ID + "\"").update(); } else { writeLog("对不起,没有找到这个Project,ID为" + p.ParentID); } } } #endregion #region 更新所有包含ProjectID的表格(除了Step和ProjectAndStep表) List <ExtFileList> lFileList = context.table("ExtFileList").select("*").getList <ExtFileList>(new ExtFileList()); List <MoneyAndYear> lAndYear = context.table("MoneyAndYear").select("*").getList <MoneyAndYear>(new MoneyAndYear()); List <Task> lTaskList = context.table("Task").select("*").getList <Task>(new Task()); List <WhiteList> lWhiteListList = context.table("WhiteList").select("*").getList <WhiteList>(new WhiteList()); //更新表ExtFileList的ID字段 foreach (ExtFileList obj in lFileList) { //旧的ID string oldID = obj.ID; //生成新的ID obj.ID = Guid.NewGuid().ToString(); //替换ProjectID if (keyDicts["Project"].ContainsKey(obj.ProjectID)) { obj.ProjectID = keyDicts["Project"][obj.ProjectID]; } else { writeLog("对不起,没有找到ProjectID为" + obj.ProjectID + "的记录!"); } //更新记录 obj.copyTo(context.table("ExtFileList")).where ("ID=\"" + oldID + "\"").update(); } //更新表MoneyAndYear的ID字段 foreach (MoneyAndYear obj in lAndYear) { //旧的ID string oldID = obj.ID; //生成新的ID obj.ID = Guid.NewGuid().ToString(); //替换ProjectID if (keyDicts["Project"].ContainsKey(obj.ProjectID)) { obj.ProjectID = keyDicts["Project"][obj.ProjectID]; } else { writeLog("对不起,没有找到ProjectID为" + obj.ProjectID + "的记录!"); } //更新记录 obj.copyTo(context.table("MoneyAndYear")).where ("ID=\"" + oldID + "\"").update(); } //更新表Task的ID字段 foreach (Task obj in lTaskList) { //旧的ID string oldID = obj.ID; //生成新的ID obj.ID = Guid.NewGuid().ToString(); //替换ProjectID if (keyDicts["Project"].ContainsKey(obj.ProjectID)) { obj.ProjectID = keyDicts["Project"][obj.ProjectID]; } else { writeLog("对不起,没有找到ProjectID为" + obj.ProjectID + "的记录!"); } //更新记录 obj.copyTo(context.table("Task")).where ("ID=\"" + oldID + "\"").update(); } //更新表WhiteList的ID字段 foreach (WhiteList obj in lWhiteListList) { //旧的ID string oldID = obj.ID; //生成新的ID obj.ID = Guid.NewGuid().ToString(); //替换ProjectID if (keyDicts["Project"].ContainsKey(obj.ProjectID)) { obj.ProjectID = keyDicts["Project"][obj.ProjectID]; } else { writeLog("对不起,没有找到ProjectID为" + obj.ProjectID + "的记录!"); } //更新记录 obj.copyTo(context.table("WhiteList")).where ("ID=\"" + oldID + "\"").update(); } #endregion #region 更新阶段数据 List <Step> lStepList = context.table("Step").select("*").getList <Step>(new Step()); List <ProjectAndStep> lAndStepList = context.table("ProjectAndStep").select("*").getList <ProjectAndStep>(new ProjectAndStep()); //更新Step数据 foreach (Step obj in lStepList) { //检查是否需要创建字典 if (keyDicts.ContainsKey("Step") == false) { keyDicts["Step"] = new Dictionary <string, string>(); } //旧的ID string oldID = obj.ID; //生成新的ID obj.ID = Guid.NewGuid().ToString(); //保存新的StepID keyDicts["Step"][oldID] = obj.ID; //替换ProjectID if (keyDicts["Project"].ContainsKey(obj.ProjectID)) { obj.ProjectID = keyDicts["Project"][obj.ProjectID]; } else { writeLog("对不起,没有找到ProjectID为" + obj.ProjectID + "的记录!"); } //更新记录 obj.copyTo(context.table("Step")).where ("ID=\"" + oldID + "\"").update(); } //更新ProjectAndStep数据 foreach (ProjectAndStep obj in lAndStepList) { //旧的ID string oldID = obj.ID; //生成新的ID obj.ID = Guid.NewGuid().ToString(); //替换StepID if (keyDicts["Step"].ContainsKey(obj.StepID)) { obj.StepID = keyDicts["Step"][obj.StepID]; } else { writeLog("对不起,没有找到StepID为" + obj.StepID + "的记录!"); } //更新记录 obj.copyTo(context.table("ProjectAndStep")).where ("ID=\"" + oldID + "\"").update(); } #endregion } catch (Exception ex) { writeLog(ex.ToString()); } finally { context.Dispose(); } }
/// <summary> /// 从申报包中解压文档 /// </summary> /// <param name="projectNumber">项目编号</param> private void unZipDocFiles(string projectNumber) { try { //查找指定申报包 string[] subDirs = Directory.GetDirectories(MainForm.Config.TotalDir); if (subDirs != null) { //循环子目录,查找与项目编号相符的目录名称 foreach (string s in subDirs) { //获得目录信息 DirectoryInfo di = new DirectoryInfo(s); //判断目录是否符合条件 if (di.Name.Contains(projectNumber) && di.Name.Length >= 12) { MainForm.writeLog("开始进行项目" + projectNumber + "的解包......"); //查找子文件 string[] subFiless = Directory.GetFiles(s); if (subFiless != null && subFiless.Length >= 1) { //解压这个Zip包 string pkgDir = Path.Combine(MainForm.Config.UnZipDir, projectNumber); //删除解压目录 try { if (Directory.Exists(pkgDir)) { Directory.Delete(pkgDir, true); } } catch (Exception ex) { MainForm.writeLog(ex.ToString()); } //创建解压目录 try { Directory.CreateDirectory(pkgDir); } catch (Exception ex) { MainForm.writeLog(ex.ToString()); } //ZIP文件 string pkgZipFile = string.Empty; //查找ZIP文件 foreach (string sssss in subFiless) { if (sssss.EndsWith(".zip")) { pkgZipFile = sssss; break; } } //判断ZIP文件是否存在 if (pkgZipFile != null && pkgZipFile.EndsWith(".zip")) { MainForm.writeLog("项目" + projectNumber + "的解包操作,开始ZIP文件解压"); //解压这个包 new NdeyDocFilesUnZip().UnZipFile(pkgZipFile, pkgDir, string.Empty, true); MainForm.writeLog("项目" + projectNumber + "的解包操作,结束ZIP文件解压"); //文件目录 string fileDir = pkgDir; //判断申报包是否有效 //生成DB文件路径 string dbFile = System.IO.Path.Combine(fileDir, "myData.db"); //判断文件是否存在 if (System.IO.File.Exists(dbFile)) { //SQLite数据库工厂 System.Data.SQLite.SQLiteFactory factory = new System.Data.SQLite.SQLiteFactory(); //NDEY数据库连接 Noear.Weed.DbContext context = new Noear.Weed.DbContext("main", "Data Source = " + dbFile, factory); try { MainForm.writeLog("项目" + projectNumber + "的解包操作,读取论文详细,科技奖项,专利情况信息..."); //提取论文详细 DataList dlRTreatises = context.table("RTreatises").orderBy("RTreatisesOrder desc").select("RTreatisesName,RTreatisesPDF").getDataList(); //提取科技奖项 DataList dlTechnologyAwards = context.table("TechnologyAwards").orderBy("TechnologyAwardsOrder desc").select("TechnologyAwardsPName,TechnologyAwardsPDF").getDataList(); //提取专利情况 DataList dlNDPatent = context.table("NDPatent").orderBy("NDPatentOrder desc").select("NDPatentName,NDPatentPDF").getDataList(); //提取推荐意见 DataList dlBaseInfor = context.table("BaseInfor").select("UnitRecommend,ExpertRecommend1,ExpertRecommend2,ExpertRecommend3").getDataList(); //提取UnitID string unitID = context.table("BaseInfor").select("UnitID").getValue <string>(string.Empty); string personName = context.table("BaseInfor").select("UserName").getValue <string>(string.Empty); //需要删除的文件列表 List <string> needDeleteList = new List <string>(); needDeleteList.AddRange(Directory.GetFiles(fileDir)); //移除正常的申报包文件,把不属于本次申报内容的文件留下 needDeleteList.Remove(dbFile); MainForm.writeLog("项目" + projectNumber + "的解包操作,开始替换附件名称..."); //附件序号 int fileIndex = 0; //整理附件名称 foreach (DataItem item in dlRTreatises.getRows()) { //移除正常的申报包文件,把不属于本次申报内容的文件留下 needDeleteList.Remove(Path.Combine(fileDir, item.getString("RTreatisesPDF"))); //获得文件扩展名 string extName = new FileInfo(Path.Combine(fileDir, item.getString("RTreatisesPDF"))).Extension; //文件序号+1 fileIndex++; //文件重命名 try { renameFile(Path.Combine(fileDir, item.getString("RTreatisesPDF")), fileIndex, item.getString("RTreatisesName"), extName); } catch (Exception ex) { MainForm.writeLog(ex.ToString()); addErrorFile(projectNumber, item.getString("RTreatisesPDF")); } } foreach (DataItem item in dlTechnologyAwards.getRows()) { //移除正常的申报包文件,把不属于本次申报内容的文件留下 needDeleteList.Remove(Path.Combine(fileDir, item.getString("TechnologyAwardsPDF"))); //获得文件扩展名 string extName = new FileInfo(Path.Combine(fileDir, item.getString("TechnologyAwardsPDF"))).Extension; //文件序号+1 fileIndex++; //文件重命名 try { renameFile(Path.Combine(fileDir, item.getString("TechnologyAwardsPDF")), fileIndex, item.getString("TechnologyAwardsPName"), extName); } catch (Exception ex) { MainForm.writeLog(ex.ToString()); addErrorFile(projectNumber, item.getString("TechnologyAwardsPDF")); } } foreach (DataItem item in dlNDPatent.getRows()) { //移除正常的申报包文件,把不属于本次申报内容的文件留下 needDeleteList.Remove(Path.Combine(fileDir, item.getString("NDPatentPDF"))); //获得文件扩展名 string extName = new FileInfo(Path.Combine(fileDir, item.getString("NDPatentPDF"))).Extension; //文件序号+1 fileIndex++; //文件重命名 try { renameFile(Path.Combine(fileDir, item.getString("NDPatentPDF")), fileIndex, item.getString("NDPatentName"), extName); } catch (Exception ex) { MainForm.writeLog(ex.ToString()); addErrorFile(projectNumber, item.getString("NDPatentPDF")); } } //整理推荐意见附件 string unitFileA = dlBaseInfor.getRow(0).get("UnitRecommend") != null?dlBaseInfor.getRow(0).get("UnitRecommend").ToString() : string.Empty; string personFileA = dlBaseInfor.getRow(0).get("ExpertRecommend1") != null?dlBaseInfor.getRow(0).get("ExpertRecommend1").ToString() : string.Empty; string personFileB = dlBaseInfor.getRow(0).get("ExpertRecommend2") != null?dlBaseInfor.getRow(0).get("ExpertRecommend2").ToString() : string.Empty; string personFileC = dlBaseInfor.getRow(0).get("ExpertRecommend3") != null?dlBaseInfor.getRow(0).get("ExpertRecommend3").ToString() : string.Empty; //查找单位推荐意见附件 if (File.Exists(Path.Combine(fileDir, unitFileA))) { //移除正常的申报包文件,把不属于本次申报内容的文件留下 needDeleteList.Remove(Path.Combine(fileDir, unitFileA)); //文件序号+1 fileIndex++; //文件重命名 try { renameFile(Path.Combine(fileDir, unitFileA), fileIndex, "单位推荐意见", new FileInfo(Path.Combine(fileDir, unitFileA)).Extension); } catch (Exception ex) { MainForm.writeLog(ex.ToString()); addErrorFile(projectNumber, unitFileA); } } //查找专家提名附件 if (File.Exists(Path.Combine(fileDir, personFileA))) { //移除正常的申报包文件,把不属于本次申报内容的文件留下 needDeleteList.Remove(Path.Combine(fileDir, personFileA)); //文件序号+1 fileIndex++; //文件重命名 try { renameFile(Path.Combine(fileDir, personFileA), fileIndex, "专家提名", new FileInfo(Path.Combine(fileDir, personFileA)).Extension); } catch (Exception ex) { MainForm.writeLog(ex.ToString()); addErrorFile(projectNumber, personFileA); } } //查找专家提名附件 if (File.Exists(Path.Combine(fileDir, personFileB))) { //移除正常的申报包文件,把不属于本次申报内容的文件留下 needDeleteList.Remove(Path.Combine(fileDir, personFileB)); //文件序号+1 fileIndex++; //文件重命名 try { renameFile(Path.Combine(fileDir, personFileB), fileIndex, "专家提名", new FileInfo(Path.Combine(fileDir, personFileB)).Extension); } catch (Exception ex) { MainForm.writeLog(ex.ToString()); addErrorFile(projectNumber, personFileB); } } //查找专家提名附件 if (File.Exists(Path.Combine(fileDir, personFileC))) { //移除正常的申报包文件,把不属于本次申报内容的文件留下 needDeleteList.Remove(Path.Combine(fileDir, personFileC)); //文件序号+1 fileIndex++; //文件重命名 try { renameFile(Path.Combine(fileDir, personFileC), fileIndex, "专家提名", new FileInfo(Path.Combine(fileDir, personFileC)).Extension); } catch (Exception ex) { MainForm.writeLog(ex.ToString()); addErrorFile(projectNumber, personFileC); } } MainForm.writeLog("项目" + projectNumber + "的解包操作,结束替换附件名称..."); MainForm.writeLog("项目" + projectNumber + "的解包操作,开始处理保密资质附件和申报书文档转PDF..."); //是否已经找到Doc文件 bool isFoundDocFile = false; //上一个保密资质修改时间 DateTime lastExtFileModifyTime = DateTime.MinValue; //整理保密资质命名,查找Doc文件 string[] extFiles = Directory.GetFiles(fileDir); foreach (string sss in extFiles) { FileInfo fii = new FileInfo(sss); if (fii.Name.StartsWith("extFile_")) { try { //查看最后创建的文件 if (fii.LastWriteTime > lastExtFileModifyTime) { //只有第一个extFile才分配文件序号 if (lastExtFileModifyTime == DateTime.MinValue) { //文件序号+1 fileIndex++; } //记录当前创建时间 lastExtFileModifyTime = fii.LastWriteTime; } else { continue; } //移除正常的申报包文件,把不属于本次申报内容的文件留下 needDeleteList.Remove(sss); //移动文件 renameFile(sss, fileIndex, "保密资质复印件", ".png"); } catch (Exception ex) { MainForm.writeLog(ex.ToString()); addErrorFile(projectNumber, fii.Name); } } else if (fii.Name.EndsWith(".doc")) { //移除正常的申报包文件,把不属于本次申报内容的文件留下 needDeleteList.Remove(sss); if (fii.Name.StartsWith(unitID) && fii.Name.Contains(personName)) { //找到Doc文件 isFoundDocFile = true; //真实的申报书路径 string destDocFile = Path.Combine(fii.DirectoryName, "项目申报书.doc"); //文件改名 try { File.Move(sss, destDocFile); } catch (Exception ex) { MainForm.writeLog(ex.ToString()); addErrorFile(projectNumber, fii.Name); } //转换成PDF convertToPDF(projectNumber, destDocFile); } else { if (fii.Name.EndsWith("项目申报书.doc")) { try { File.Delete(sss); } catch (Exception ex) { MainForm.writeLog(ex.ToString()); } } } } } //判断是否存在不属于本次申报包的文件 if (needDeleteList != null && needDeleteList.Count >= 1) { MainForm.writeLog("项目" + projectNumber + "的解包操作,开始移除不属于本次申报包的文件"); //删除不属于本次申报包的内容 foreach (string needdelete in needDeleteList) { try { File.Delete(needdelete); } catch (Exception ex) { MainForm.writeLog(ex.ToString()); } } MainForm.writeLog("项目" + projectNumber + "的解包操作,开始移除不属于本次申报包的文件"); } //判断是否找到Doc文件 if (isFoundDocFile) { //找到DOC文件 //MainForm.writeLog("项目申报书已找到!"); } else { //没有找到Doc文件 MainForm.writeLog("对不起,没有找到项目申报书!"); //输出缺失的文件 addErrorFile(projectNumber, "项目申报书.doc"); } MainForm.writeLog("项目" + projectNumber + "的解包操作,结束处理保密资质附件和申报书文档转PDF..."); } catch (Exception ex) { MainForm.writeLog(ex.ToString()); } finally { factory.Dispose(); context.Dispose(); context = null; //**链接close()和dispose()之后任然不能释放与db文件的连接,原因是sqlite在执行SQLiteConnectionHandle.Dispose()操作时候,其实并没有真正的释放连接,只有显式调用 CLR垃圾回收之后才能真正释放连接 GC.Collect(); GC.WaitForPendingFinalizers(); //删除数据库文件 try { File.Delete(dbFile); } catch (Exception ex) { } } } else { MainForm.writeLog("没有找到DB文件__" + projectNumber); } } else { MainForm.writeLog("没有找到ZIP文件__" + projectNumber); } } else { MainForm.writeLog("没有找到ZIP文件__" + projectNumber); } MainForm.writeLog("结束进行项目" + projectNumber + "的解包......"); } } } } catch (Exception ex) { MainForm.writeLog("项目编号" + projectNumber + "解包错误,Ex:" + ex.ToString()); } }