public void TestMethod2()
{
string xmlFolder = "C:\\PDXML_Repo\\PDXML\\bin\\Debug\\XML\\SE";
string sqlFolder = "C:\\PDXML_Repo\\PDXML\\bin\\Debug\\XML\\SE\\SE_SQL";
List <Material> materials = new List <Material>();
materials.Add(new Material("NKW815", "test-code"));
//XMLManipulator.UpdateXML(materials, xmlFolder);
SQLCreator.CreateSQLFile(materials, sqlFolder);
}
/// <summary> 保存无效数据
/// 恢复事务性提交代码 hegf at 20101229
/// </summary>
/// <param name="entityList"></param>
public static void InsertUselessData(List<GPSDataEntity> entityList)
{
DbConnection dbConnection = null;
string tableName = AppSetting.UselessDataTable;
SQLCreator sqlCreator = new SQLCreator();
//将list中的数据写入数据库，每次写入IntMinExcuteCount条，直到将list中的数据全部写入为止
StringBuilder stringBuilder = null;
try
{
//构造批量sql语句
stringBuilder = sqlCreator.BuildInsertUselessSQL(tableName, entityList);
if (stringBuilder != null)
{
dbConnection = GetConnection(EnumDBOperationType.UselessDataDB);
Logger.Info("InsertUselessData GetConnection执行完毕");
if (dbConnection == null)
return;
if (dbConnection.State != ConnectionState.Open)
dbConnection.Open();
//事务注释掉是由于多线程调用引起IO争用。如果考虑负载则需要。
DbTransaction trans = dbConnection.BeginTransaction();
DbCommand cmd = dbConnection.CreateCommand();
cmd.Transaction = trans;
cmd.CommandText = stringBuilder.ToString();
try
{
cmd.ExecuteNonQuery();
trans.Commit();
Logger.Info("InsertUselessData执行成功");
}
catch (Exception ex)
{
trans.Rollback();
Loggers.LogInfo("PES.GPS.GPSStorage.GPSDataHelper.InsertUselessData:" + ex);
}
}
}
catch (Exception ex)
{
//Loggers.LogInfo("PES.GPS.GPSStorage.GPSDataHelper.InsertUselessData:" + stringBuilder);
Loggers.LogInfo("PES.GPS.GPSStorage.GPSDataHelper.InsertUselessData:" + ex);
}
finally
{
stringBuilder = null;
CloseDBConnection(dbConnection);
}
}
/// <summary> 保存拍照数据
///
/// </summary>
/// <param name="entityList"></param>
public static void InsertCameraPhoto(List<GPSDataEntity> entityList)
{
DbConnection dbConnection = null;
string tableName = "gps_cameraphotoinfo";
SQLCreator sqlCreator = new SQLCreator();
StringBuilder stringBuilder = null;
try
{
if (null == entityList || entityList.Count == 0) return;
IList<string> gpsCodeList = entityList.Select(item => item.GPSCode).Distinct().ToList();
Dictionary<string, string> vehicleTenantDict = GetGPSTenantCode(gpsCodeList);
stringBuilder = sqlCreator.BuildInsertPhotoSQL(tableName, entityList, vehicleTenantDict);
if (stringBuilder != null)
{
dbConnection = GetConnection(EnumDBOperationType.CameraPhotoDB);
if (dbConnection == null)
return;
if (dbConnection.State != ConnectionState.Open)
dbConnection.Open();
DbTransaction trans = dbConnection.BeginTransaction();
DbCommand cmd = dbConnection.CreateCommand();
cmd.Transaction = trans;
cmd.CommandText = stringBuilder.ToString();
try
{
cmd.ExecuteNonQuery();
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
Loggers.LogInfo("PES.GPS.GPSStorage.GPSDataHelper.InsertCameraPhoto:" + ex);
}
}
}
catch (Exception ex)
{
//Loggers.LogInfo("PES.GPS.GPSStorage.GPSDataHelper.InsertCameraPhoto:" + stringBuilder);
Loggers.LogInfo("PES.GPS.GPSStorage.GPSDataHelper.InsertCameraPhoto:" + ex);
}
finally
{
stringBuilder = null;
CloseDBConnection(dbConnection);
}
}
/// <summary> 保存补传数据
/// 恢复事务性提交代码 hegf at 20101229
/// </summary>
public static void InsertFetchData(List<GPSDataEntity> entityList)
{
DbConnection dbConnection = null;
string tableName = "gps_fetchdata";
SQLCreator sqlCreator = new SQLCreator();
StringBuilder stringBuilder = null;
try
{
stringBuilder = sqlCreator.BuildInsertFetchSQL(tableName, entityList);
if (stringBuilder != null)
{
dbConnection = GetConnection(EnumDBOperationType.FetchDataDB);
Logger.Info("InsertFetchData GetConnection执行完毕");
if (dbConnection == null)
return;
if (dbConnection.State != ConnectionState.Open)
dbConnection.Open();
//事务注释掉是由于多线程调用引起IO争用。如果考虑负载则需要。
DbTransaction trans = dbConnection.BeginTransaction();
DbCommand cmd = dbConnection.CreateCommand();
cmd.Transaction = trans;
cmd.CommandText = stringBuilder.ToString();
try
{
cmd.ExecuteNonQuery();
trans.Commit();
Logger.Info("InsertFetchData 执行成功");
}
catch (Exception ex)
{
trans.Rollback();
Logger.Warn("保存补传数据错误 PES.GPS.GPSStorage.GPSDataHelper.InsertFetchData:" + ex);
}
}
}
catch (Exception ex)
{
Logger.Warn("PES.GPS.GPSStorage.GPSDataHelper.InsertFetchData:" + ex);
}
finally
{
stringBuilder = null;
CloseDBConnection(dbConnection);
}
}
/// <summary> 保存实时数据
/// 恢复事务性提交代码 hegf at 20101229
/// </summary>
public static void UpdateDynamicData(List<GPSDataEntity> entityList)
{
DbConnection dbConnection = null;
string tableName = "gps_currentinfo";
SQLCreator sqlCreator = new SQLCreator();
StringBuilder stringBuilder = null;
try
{
stringBuilder = sqlCreator.BuildUpdateCurSQL(tableName, entityList);
Logger.Warn("savecurrentdata-Sql:" + stringBuilder.ToString());
if (stringBuilder != null)
{
dbConnection = GetConnection(EnumDBOperationType.CurrentDataDB);
if (dbConnection == null)
return;
if (dbConnection.State != ConnectionState.Open)
dbConnection.Open();
//事务注释掉是由于多线程调用引起IO争用。如果考虑负载则需要。
DbTransaction trans = dbConnection.BeginTransaction();
DbCommand cmd = dbConnection.CreateCommand();
cmd.Transaction = trans;
cmd.CommandText = stringBuilder.ToString();
try
{
//执行语句
cmd.ExecuteNonQuery();
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
Logger.Warn("PES.GPS.GPSStorage.GPSDataHelper.UpdateDynamicData:" + ex);
}
}
}
catch (Exception ex)
{
Logger.Warn("PES.GPS.GPSStorage.GPSDataHelper.UpdateDynamicData:" + stringBuilder);
Logger.Warn("PES.GPS.GPSStorage.GPSDataHelper.UpdateDynamicData:" + ex);
}
finally
{
stringBuilder = null;
CloseDBConnection(dbConnection);
}
}
/// <summary> 保存单个车辆的历史记录
///
/// </summary>
private static void InsertHistoryData(GPSDataEntity entity)
{
List<GPSDataEntity> entityList = new List<GPSDataEntity>();
entityList.Add(entity);
HistoryStoreConfig config = CacheUtility.GetGPSHistoryStoreTable(entity.VehicleCode.ToString());
if (config == null)
{
string log = string.Format("PES.GPS.GPSStorage.GPSDataHelper.InsertHistoryData:车辆编号为{0}找不到历史存储表。", entity.VehicleCode);
Loggers.LogInfo(log);
return;
}
//DbConnection dbConnection = GetHistoryConnection(config);
DbConnection dbConnection = null;
string tableName = config.TableName;
SQLCreator sqlCreator = new SQLCreator();
//将list中的数据写入数据库，每次写入IntMinExcuteCount条，直到将list中的数据全部写入为止
StringBuilder stringBuilder = null;
try
{
stringBuilder = sqlCreator.BuildInsertHistSQL(config, entityList);
Logger.Warn("PES.GPS.GPSStorage.GPSDataHelper.InsertHistoryData:" + stringBuilder.ToString());
if (stringBuilder != null)
{
dbConnection = GetConnection(EnumDBOperationType.HistoryDataDB);
if (dbConnection == null)
return;
if (dbConnection.State != ConnectionState.Open)
dbConnection.Open();
DbTransaction trans = dbConnection.BeginTransaction();
DbCommand cmd = dbConnection.CreateCommand();
cmd.Transaction = trans;
cmd.CommandText = stringBuilder.ToString();
try
{
cmd.ExecuteNonQuery();
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
Loggers.LogInfo("PES.GPS.GPSStorage.GPSDataHelper.InsertHistoryData rollback:" + ex);
}
}
}
catch (Exception ex)
{
Loggers.LogInfo("PES.GPS.GPSStorage.GPSDataHelper.InsertHistoryData error:" + stringBuilder.ToString());
Loggers.LogInfo("PES.GPS.GPSStorage.GPSDataHelper.InsertHistoryData error:" + ex);
}
finally
{
stringBuilder = null;
CloseDBConnection(dbConnection);
}
}
private static void InsertBatchHistoryData(List<GPSDataEntity> entityList)
{
HistoryStoreConfig config = CacheUtility.GetHistoryConfigByVehicleCodeFromHT(entityList[0].VehicleCode.ToString());
if (config == null)
{
string log = string.Format("PES.GPS.GPSStorage.GPSDataHelper.InsertHistoryData:车辆编号为{0}找不到历史存储表。", entityList[0].VehicleCode);
Loggers.LogInfo(log);
return;
}
else
{
Loggers.LogInfo("数据库连接：" + config.AddConStr);
}
DbConnection dbConnection = null;
string tableName = config.TableName;
int beginIndex = 0;
int endIndex = Math.Min(ExecuteSQLCount, entityList.Count);
SQLCreator sqlCreator = new SQLCreator();
//将list中的数据写入数据库，每次写入IntMinExcuteCount条，直到将list中的数据全部写入为止
StringBuilder stringBuilder = null;
try
{
List<List<GPSDataEntity>> lists = new List<List<GPSDataEntity>>();
if (entityList.Count <= ExecuteSQLCount)
lists.Add(entityList);
else
{
int count = entityList.Count / ExecuteSQLCount;
int orthercount = entityList.Count % ExecuteSQLCount;
for (int i = 0; i < count; i++)
{
List<GPSDataEntity> tempEntityList = null;
GPSDataEntity[] gpsdatas = new GPSDataEntity[ExecuteSQLCount];
entityList.CopyTo(i * ExecuteSQLCount, gpsdatas, 0, ExecuteSQLCount);
tempEntityList = new List<GPSDataEntity>(gpsdatas);
lists.Add(tempEntityList);
}
if (orthercount != 0)
{
int orthercountx = entityList.Count - count * ExecuteSQLCount;
GPSDataEntity[] gpsdatas = new GPSDataEntity[orthercountx];
entityList.CopyTo(count * ExecuteSQLCount, gpsdatas, 0, orthercountx);
List<GPSDataEntity> tempEntityList = new List<GPSDataEntity>(gpsdatas);
lists.Add(tempEntityList);
}
}
Logger.Warn("PES.GPS.GPSStorage.GPSDataHelper.InsertHistoryData ConnStr=" + config.AddConStr.Trim());
dbConnection = GetConnection(config.AddConStr.Trim());
if (dbConnection == null)
return;
if (dbConnection.State != ConnectionState.Open)
dbConnection.Open();
foreach (List<GPSDataEntity> entitys in lists)
{
stringBuilder = sqlCreator.BuildInsertHistSQL(config, entitys);
Logger.Warn("savehistorydata-Sql:" + stringBuilder.ToString());
if (stringBuilder != null)
{
DbTransaction trans = dbConnection.BeginTransaction();
DbCommand cmd = dbConnection.CreateCommand();
cmd.CommandText = stringBuilder.ToString();
cmd.Transaction = trans;
try
{
cmd.ExecuteNonQuery();
trans.Commit();
Loggers.LogInfo("PES.GPS.GPSStorage.GPSDataHelper.InsertHistoryData Commit");
}
catch (Exception ex)
{
trans.Rollback();
Loggers.LogInfo("PES.GPS.GPSStorage.GPSDataHelper.InsertHistoryData rollback:" + ex);
}
}
stringBuilder = null;
}
}
catch (Exception ex)
{
Logger.Warn("PES.GPS.GPSStorage.GPSDataHelper.InsertHistoryData error:" + stringBuilder.ToString());
Logger.Warn("PES.GPS.GPSStorage.GPSDataHelper.InsertHistoryData error:" + ex);
}
finally
{
CloseDBConnection(dbConnection);
}
}
/// <summary> 保存无效数据
/// 恢复事务性提交代码 hegf at 20101229
/// </summary>
/// <param name="entityList"></param>
public void InsertUselessData(List<GPSDataEntity> entityList, string tableName)
{
DbConnection dbConnection = null;
SQLCreator sqlCreator = new SQLCreator();
//将list中的数据写入数据库，每次写入IntMinExcuteCount条，直到将list中的数据全部写入为止
StringBuilder stringBuilder = null;
try
{
using (dbConnection = GetConnection(EnumDBOperationType.UselessDataDB))
{
//构造批量sql语句
stringBuilder = sqlCreator.BuildInsertUselessSQL(tableName, entityList);
if (stringBuilder != null)
{
//事务注释掉是由于多线程调用引起IO争用。如果考虑负载则需要。
DbTransaction trans = dbConnection.BeginTransaction();
DbCommand cmd = dbConnection.CreateCommand();
cmd.Transaction = trans;
cmd.CommandText = stringBuilder.ToString();
try
{
cmd.ExecuteNonQuery();
trans.Commit();
Logger.Info("InsertUselessData执行成功");
}
catch (Exception ex)
{
trans.Rollback();
Logger.Warn("FetchData.InsertUselessData:" + ex);
}
}
}
}
catch (Exception ex)
{
Logger.Warn("FetchData.InsertUselessData:" + ex);
}
}
/// <summary> 保存补传数据
/// 恢复事务性提交代码 hegf at 20101229
/// </summary>
public void InsertFetchData(List<GPSDataEntity> entityList)
{
DbConnection dbConnection = null;
string tableName = "gps_fetchdata";
SQLCreator sqlCreator = new SQLCreator();
StringBuilder stringBuilder = null;
try
{
using (dbConnection = GetConnection(EnumDBOperationType.FetchDataDB))
{
stringBuilder = sqlCreator.BuildInsertFetchSQL(tableName, entityList);
if (stringBuilder != null)
{
//事务注释掉是由于多线程调用引起IO争用。如果考虑负载则需要。
DbTransaction trans = dbConnection.BeginTransaction();
DbCommand cmd = dbConnection.CreateCommand();
cmd.Transaction = trans;
cmd.CommandText = stringBuilder.ToString();
try
{
cmd.ExecuteNonQuery();
trans.Commit();
Logger.Info("InsertFetchData 执行成功");
}
catch (Exception ex)
{
trans.Rollback();
Logger.Warn("保存补传数据错误 FetchData.InsertFetchData:" + ex);
}
}
}
}
catch (Exception ex)
{
Logger.Warn("FetchData.InsertFetchData:" + ex);
}
}
/// <summary>
/// 保存历史表数据
/// </summary>
/// <param name="entityList"></param>
/// <param name="HistoryConfigHT"></param>
public void InsertHistoryData(List<GPSDataEntity> entityList, IDictionary<string, HistoryStoreConfig> HistoryConfigHT)
{
Dictionary<string, List<GPSDataEntity>> HistoryDataDict = new Dictionary<string, List<GPSDataEntity>>();
DbConnection dbConnection = null;
foreach (GPSDataEntity entity in entityList)
{
string vehicleCode = entity.VehicleCode.ToString();
if (string.IsNullOrEmpty(vehicleCode))
{
Logger.Info(string.Format("GPSCODE[{0}]InsertHistoryData(List<GPSDataEntity> entityList):找不到车辆编号.", entity.GPSCode));
continue;
}
HistoryStoreConfig config = HistoryConfigHT[vehicleCode];
if (config == null) continue;
if (HistoryDataDict.ContainsKey(config.TableName))
{
List<GPSDataEntity> list = HistoryDataDict[config.TableName];
list.Add(entity);
}
else
{
List<GPSDataEntity> list = new List<GPSDataEntity>();
list.Add(entity);
HistoryDataDict.Add(config.TableName, list);
}
}
foreach (string key in HistoryDataDict.Keys)
{
//InsertBatchHistoryData(HistoryDataDict[key].ToList<GPSDataEntity>());
List<GPSDataEntity> HistoryList = HistoryDataDict[key];
HistoryStoreConfig config = HistoryConfigHT[HistoryList[0].VehicleCode.ToString()];
if (config == null)
{
string log = string.Format("PES.GPS.GPSStorage.GPSDataHelper.InsertHistoryData:车辆编号为{0}找不到历史存储表。", entityList[0].VehicleCode);
Logger.Info(log);
return;
}
SQLCreator sqlCreator = new SQLCreator();
StringBuilder stringBuilder = sqlCreator.BuildInsertHistSQL(config, HistoryList);
if (stringBuilder == null)
{
continue;
}
using (dbConnection = GetConnection(config.AddConStr.Trim()))
{
try
{
DbCommand cmd = dbConnection.CreateCommand();
cmd.CommandText = stringBuilder.ToString();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
//Liuhuaihai Update on 2012/4/25
try
{
string commandTxt = stringBuilder.ToString();
Logger.Trace("InsertHistoryData, before insert into history: " + commandTxt);
string[] splitCommandTxt = commandTxt.Split(',');
string reportTimeString = splitCommandTxt[22].Substring(1, splitCommandTxt[22].Length - 2);
DateTime reportTime = DateTime.Parse(reportTimeString);
reportTime = reportTime.AddSeconds(1);
commandTxt = commandTxt.Replace(splitCommandTxt[22], "'" + reportTime.ToString() + "'");
Logger.Trace("InsertHistoryData, after insert into history: " + commandTxt);
DbCommand cmd = dbConnection.CreateCommand();
cmd.CommandText = commandTxt;
cmd.ExecuteNonQuery();
}
catch (Exception ex2)
{
Logger.Error(ex2);
}
//Logger.Error(ex);
//
}
}
}
}
/// <summary>
/// 保存当前表数据
/// </summary>
/// <param name="entityList"></param>
public void UpdateDynamicData(List<GPSDataEntity> entityList)
{
SQLCreator sqlCreator = new SQLCreator();
StringBuilder stringBuilder = null;
DbConnection dbConnection = null;
try
{
stringBuilder = sqlCreator.BuildUpdateCurSQL(entityList);
if (stringBuilder != null)
{
using (dbConnection = GetConnection(_DefaultConnectionString))
{
DbCommand cmd = dbConnection.CreateCommand();
cmd.CommandText = stringBuilder.ToString();
cmd.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
Logger.Error(ex);
}
}
private void btnAddPhoto_Click(object sender, EventArgs e)
{
GPSDataEntity entity = GetDataEntity2();
entity.IsPhotoData = true;
SQLCreator sql = new SQLCreator();
List<GPSDataEntity>list=new List<GPSDataEntity>();
list.Add(entity);
sql.BuildInsertPhotoSQL("gps_cameraphotoinfo", list);
}