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("分项工程信息存储完成");
}
