public static void CreateSqliteDB(string dbPath) { //string = "D:\\Demo.db3"; //如果不存在改数据库文件,则创建该数据库文件 if (!System.IO.File.Exists(dbPath)) { CSqliteWrapper db = new CSqliteWrapper(dbPath); string sql = @"CREATE TABLE ConsInfo(ProjectName integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,Name char(3),TypeName varchar(50),addDate datetime,UpdateTime Date,Time time,Comments blob)"; db.ExecuteNonQuery(sql, null); } }
public static void SaveProjectToSqlite(string dbPath, List <CRailwayProject> cl) { string sql = "INSERT INTO ProjectInfo(ProjectID,ParentID, ProjectName,ProfessionalName,ProfessionalCategoryCode,ShorName,Mileage_Start_Des,Mileage_End_Des, Mileage_Start,Mileage_End,SerialNo,UpdateTime,Direction,avgProgress,IsValid, PhotoUrl)" + "values(@ProjectID,@ParentID, @ProjectName,@ProfessionalName,@ProfessionalCategoryCode,@ShorName,@Mileage_Start_Des,@Mileage_End_Des,@Mileage_Start,@Mileage_End,@SerialNo,@UpdateTime,@Direction,@avgProgress,@IsValid,@PhotoUrl)"; CSqliteWrapper db = new CSqliteWrapper(dbPath); db.ExecuteNonQuery("DELETE from ProjectInfo", null); using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + dbPath)) { connection.Open(); using (SQLiteTransaction transaction = connection.BeginTransaction()) { using (SQLiteCommand command = new SQLiteCommand(connection)) { command.CommandText = sql; foreach (CRailwayProject p in cl) { SQLiteParameter[] parameters = new SQLiteParameter[] { new SQLiteParameter("@ProjectID", p.mProjectID), new SQLiteParameter("@ParentID", p.mParentID), new SQLiteParameter("@ProjectName", p.ProjectName), new SQLiteParameter("@ProfessionalName", p.ProfessionalName), new SQLiteParameter("@ProfessionalCategoryCode", p.mProfessionalCode), new SQLiteParameter("@ShorName", p.SegmentName), new SQLiteParameter("@Mileage_Start_Des", p.Mileage_Start_Discription), new SQLiteParameter("@Mileage_End_Des", p.Mileage_End_Discription), new SQLiteParameter("@Mileage_Start", p.mMileage_Start), new SQLiteParameter("@Mileage_End", p.mMileage_End), new SQLiteParameter("@SerialNo", p.mSerialNo), new SQLiteParameter("@UpdateTime", p.UpdateTime.ToShortDateString()), new SQLiteParameter("@Direction", p.mDirection), new SQLiteParameter("@avgProgress", p.mAvgProgress), new SQLiteParameter("@IsValid", p.mIsValid), new SQLiteParameter("@PhotoUrl", p.mPhotoUrl) }; command.Parameters.AddRange(parameters); command.ExecuteNonQuery(); //command.Parameters.Clear(); } transaction.Commit(); } } } Console.WriteLine("工点信息存储完成"); }
public static void SavePolePosToSqlite(string dbPath, List <CRailwayLine> rlist) { CSqliteWrapper db = new CSqliteWrapper(dbPath); db.ExecuteNonQuery("DELETE from PolePos", null); string sql = "INSERT INTO PolePos"; sql += " (longitude,latitude,altitude,yaw,name)" + "values(@Longitude,@Latitude,@Altitude,@YawOffset,@Name)"; int count = 0; using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + dbPath)) { connection.Open(); using (SQLiteTransaction transaction = connection.BeginTransaction()) { using (SQLiteCommand command = new SQLiteCommand(connection)) { command.CommandText = sql; foreach (CRailwayLine r in rlist) { double[] x, y, z, d; r.getPolePos(out x, out y, out z, out d); if (x == null) { continue; } for (int i = 0; i < x.Length; i++) { SQLiteParameter[] parameters = new SQLiteParameter[] { new SQLiteParameter("@Longitude", x[i]), new SQLiteParameter("@Latitude", y[i]), new SQLiteParameter("@Altitude", z[i]), new SQLiteParameter("@YawOffset", d[i] - 90), new SQLiteParameter("@Name", "" + count++), }; command.Parameters.AddRange(parameters); command.ExecuteNonQuery(); //command.Parameters.Clear(); } } transaction.Commit(); } } } Console.WriteLine("线杆信息存储完毕"); }
public static void SavePierToSqlite(string dbPath, List <CRailwayProject> cl1, List <CRailwayProject> cl2) { //string dbPath = CGisDataSettings.gDataPath + @"\jiqing\JQGis.db"; CSqliteWrapper db = new CSqliteWrapper(dbPath); db.ExecuteNonQuery("DELETE from ProjFBInfo", null); db.ExecuteNonQuery("DELETE from PierInfo", null); db.ExecuteNonQuery("DELETE from PierContInfo", null); db.ExecuteNonQuery("DELETE from BeamInfo", null); SaveBeamToSqliteHelper(dbPath, cl1); SavePierToSqliteHelper(dbPath, "PierInfo", cl1); SavePierToSqliteHelper(dbPath, "PierContInfo", cl2); Console.WriteLine("桥墩信息存储完成"); }
public static void SavePrjectForLoaded(string dbPath, List <OnePrj> ls) { string sql = "INSERT INTO ProjectLoad(ProjectType,ProjectName,ProjectIndex,ProjectModel,LongitudeS,LatitudeS,AltitudeS,ProjectLength) " + "values(@ProjectType,@ProjectName,@ProjectIndex,@ProjectModel,@LongitudeS,@LatitudeS,@AltitudeS,@ProjectLength)"; CSqliteWrapper db = new CSqliteWrapper(dbPath); db.ExecuteNonQuery("DELETE from ProjectLoad", null); using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + dbPath)) { connection.Open(); using (SQLiteTransaction transaction = connection.BeginTransaction()) { using (SQLiteCommand command = new SQLiteCommand(connection)) { command.CommandText = sql; foreach (OnePrj p in ls) { SQLiteParameter[] parameters = new SQLiteParameter[] { new SQLiteParameter("@ProjectType", p.prjType), new SQLiteParameter("@ProjectName", p.prjName), new SQLiteParameter("@ProjectIndex", p.prjIdx), new SQLiteParameter("@ProjectModel", p.ModelName), new SQLiteParameter("@LongitudeS", p.sx), new SQLiteParameter("@LatitudeS", p.sy), new SQLiteParameter("@AltitudeS", p.sz), new SQLiteParameter("@ProjectLength", p.prjLength) }; command.Parameters.AddRange(parameters); command.ExecuteNonQuery(); //command.Parameters.Clear(); } transaction.Commit(); } } } Console.WriteLine("模型加载信息存储完成"); }
public static void SaveFirmToSqlite(string dbPath, List <CRailwayFirm> cl) { string sql = "INSERT INTO FirmInfo(Num,firmid,ShorName,Longitude,Latitude,FirmType,FirmIcon)values(@Num,@firmid,@ShorName,@Longitude,@Latitude,@FirmType,@FirmIcon)"; CSqliteWrapper db = new CSqliteWrapper(dbPath); db.ExecuteNonQuery("DELETE from FirmInfo", null); using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + dbPath)) { connection.Open(); using (SQLiteTransaction transaction = connection.BeginTransaction()) { using (SQLiteCommand command = new SQLiteCommand(connection)) { command.CommandText = sql; foreach (CRailwayFirm p in cl) { SQLiteParameter[] parameters = new SQLiteParameter[] { new SQLiteParameter("@Num", p.NumStaff), new SQLiteParameter("@firmid", p.mFirmID), new SQLiteParameter("@ShorName", p.FirmName), new SQLiteParameter("@Longitude", p.CenterLongitude), new SQLiteParameter("@Latitude", p.CenterLatitude), new SQLiteParameter("@FirmType", p.FirmType), new SQLiteParameter("@FirmIcon", p.mLabelImage) }; command.Parameters.AddRange(parameters); command.ExecuteNonQuery(); } } transaction.Commit(); } } Console.WriteLine("单位信息存储完成"); }
public static void SaveConsToSqlite(string dbPath, List <ConsLocation> cl) { string sql = "INSERT INTO ConsInfo(ProjectName,DwName,Longitude,Latitude,StaffNum,fromDate,toDate)values(@ProjectName,@DwName,@Longitude,@Latitude,@StaffNum,@fromDate,@toDate)"; CSqliteWrapper db = new CSqliteWrapper(dbPath); db.ExecuteNonQuery("DELETE from ConsInfo", null); using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + dbPath)) { connection.Open(); using (SQLiteTransaction transaction = connection.BeginTransaction()) { using (SQLiteCommand command = new SQLiteCommand(connection)) { command.CommandText = sql; foreach (ConsLocation p in cl) { SQLiteParameter[] parameters = new SQLiteParameter[] { new SQLiteParameter("@ProjectName", p.ProjName), new SQLiteParameter("@DwName", p.ProjDWName), new SQLiteParameter("@Longitude", p.Longitude), new SQLiteParameter("@Latitude", p.Latitude), new SQLiteParameter("@StaffNum", p.Number), new SQLiteParameter("@fromDate", p.FromDate), new SQLiteParameter("@toDate", p.ToDate) }; command.Parameters.AddRange(parameters); command.ExecuteNonQuery(); //command.ExecuteReader() } } transaction.Commit(); } } Console.WriteLine("实名制信息存储完成"); }
public static void SaveProgressToSqlite(string dbPath, List <CRailwayProject> cl) { string sql = "INSERT INTO FXDict(ProjectID,ProjectDictID,ProjectDictName,DesignNum,InitNum) values (@ProjectID,@ProjectDictID,@ProjectDictName,@DesignNum,@InitNum)"; string sql2 = "INSERT INTO FXData(ProjectID,ProjectDictID,ReportDate,DictTotal) values (@ProjectID,@ProjectDictID,@ReportDate,@DictTotal)"; CSqliteWrapper db = new CSqliteWrapper(dbPath); db.ExecuteNonQuery("DELETE from FXDict", null); db.ExecuteNonQuery("DELETE from FXData", null); using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + dbPath)) { connection.Open(); using (SQLiteTransaction transaction = connection.BeginTransaction()) { using (SQLiteCommand command = new SQLiteCommand(connection)) { command.CommandText = sql; foreach (CRailwayProject p in cl) { foreach (CFXProj fx in p.FXProgress) { SQLiteParameter[] parameters = new SQLiteParameter[] { new SQLiteParameter("@ProjectID", p.mProjectID), new SQLiteParameter("@ProjectDictID", fx.fxID), new SQLiteParameter("@ProjectDictName", fx.FxName), new SQLiteParameter("@DesignNum", fx.TotalAmount), new SQLiteParameter("@InitNum", fx.initAmount) }; command.Parameters.AddRange(parameters); command.ExecuteNonQuery(); } } } using (SQLiteCommand command = new SQLiteCommand(connection)) { command.CommandText = sql2; foreach (CRailwayProject p in cl) { foreach (CFXProj fx in p.FXProgress) { for (int i = 0; i < fx.strDate.Count; i++) { SQLiteParameter[] para = new SQLiteParameter[] { new SQLiteParameter("@ProjectID", p.mProjectID), new SQLiteParameter("@ProjectDictID", fx.fxID), new SQLiteParameter("@ReportDate", fx.strDate[i]), new SQLiteParameter("@DictTotal", fx.doneAmount[i]) }; command.Parameters.AddRange(para); command.ExecuteNonQuery(); } } } } transaction.Commit(); } } Console.WriteLine("分项工程信息存储完成"); }