public void Synchronize_vehicleCategory(handHeldService.SynchronizeConfigDataResponseItem test) { try { SqlCeConnection con = ((VSDApp.com.rta.vsd.hh.db.IDBManager)VSDApp.com.rta.vsd.hh.db.DBConnectionManager.GetInstance()).GetConnection(); string sqlQuery; if (con.State == ConnectionState.Closed) { con.Open(); } SqlCeCommand command; SqlCeResultSet rs; int rowCount; int temp = -1; //check for Vehicle Category Defect updates if (test.vehicleCategory != null) { handHeldService.VehicleCategory1[] WebServiceDefects = new VSDApp.handHeldService.VehicleCategory1[test.vehicleCategory.Length]; WebServiceDefects = test.vehicleCategory; foreach (handHeldService.VehicleCategory1 x in WebServiceDefects) { if (con.State == ConnectionState.Closed) { con.Open(); } /* * IF EXISTS (SELECT * FROM VSD_Defect WHERE DefectID = @DefectID) UPDATE Table1 SET (...) WHERE Column1='SomeValue' ELSE INSERT INTO Table1 VALUES (...) */ sqlQuery = "SELECT Vehicle_Category_ID FROM VSD_Vehicle_Category ORDER BY Vehicle_Category_ID DESC"; command = new SqlCeCommand(sqlQuery, con); SqlCeResultSet res = command.ExecuteResultSet(ResultSetOptions.Scrollable); int vInfoID = -1; if (res.HasRows) { res.Read(); vInfoID = Convert.ToInt32(res["VEHICLE_CATEGORY_ID"]) + 1; res.Close(); } sqlQuery = "Select vehicle_category_id from vsd_vehicle_category where category_code = '" + x.code + "'"; command = new SqlCeCommand(sqlQuery, con); SqlCeDataReader tempReader = command.ExecuteReader(); if (tempReader.Read()) { vInfoID = Int32.Parse(tempReader[0].ToString()); } if (vInfoID == -1) { vInfoID = 1; } sqlQuery = "Select Vehicle_Category_ID from vsd_vehicle_category where category_code ='" + x.parentCategoryCode + "'"; command = new SqlCeCommand(sqlQuery, con); tempReader = command.ExecuteReader(); string parentCatCode; if (tempReader.Read()) { parentCatCode = tempReader[0].ToString(); } else { parentCatCode = null; } sqlQuery = "SELECT COUNT(*) AS Expr1" + " FROM VSD_Vehicle_Category" + " WHERE (Vehicle_Category_ID = @Vehicle_Category_ID)"; command = new SqlCeCommand(sqlQuery, con); command.Parameters.Add("@Vehicle_Category_ID", SqlDbType.Int).Value = vInfoID; rs = command.ExecuteResultSet(ResultSetOptions.Scrollable); rowCount = ((System.Collections.ICollection)rs.ResultSetView).Count; //rountine for Defect Table if (rowCount > 0) { rs.ReadFirst(); temp = rs.GetInt32(0); } if (temp == 0) { sqlQuery = " INSERT INTO VSD_Vehicle_Category ( Vehicle_Category_ID,Parent_Vehicle_Category_ID,Record_Type,Category_Code,Category_Name,Category_Name_A,Category_Desc,Category_Desc_A,IsDisabled ) VALUES ( " + vInfoID.ToString() + "," + (parentCatCode != null ? "'" + parentCatCode + "'" : "NULL") + "," + (x.recordType != null ? "'" + x.recordType + "'" : "NULL") + "," + (x.code != null ? "'" + x.code + "'" : "NULL") + "," + (x.name != null ? "'" + x.name.Replace("'", "`") + "'" : "NULL") + "," + (x.nameArabic != null ? "'" + x.nameArabic.Replace("'", "`") + "'" : "NULL") + "," + (x.description != null ? "'" + x.description.Replace("'", "`") + "'" : "NULL") + "," + (x.descriptionArabic != null ? "'" + x.descriptionArabic.Replace("'", "`") + "'" : "NULL") + "," + (x.isDisabled != null ? "'" + x.isDisabled + "'" : "NULL") + " )"; } else { sqlQuery = " UPDATE VSD_Vehicle_Category SET Vehicle_Category_ID = " + vInfoID.ToString() + ",Parent_Vehicle_Category_ID = " + (parentCatCode != null ? "'" + parentCatCode + "'" : "NULL") + ",Record_Type = " + (x.recordType != null ? "'" + x.recordType + "'" : "NULL") + ",Category_Code = " + (x.code != null ? "'" + x.code + "'" : "NULL") + ",Category_Name = " + (x.name != null ? "'" + x.name.Replace("'", "`") + "'" : "NULL") + ",Category_Name_A = " + (x.nameArabic != null ? "'" + x.nameArabic.Replace("'", "`") + "'" : "NULL") + ",Category_Desc = " + (x.description != null ? "'" + x.description.Replace("'", "`") + "'" : "NULL") + ",Category_Desc_A = " + (x.descriptionArabic != null ? "'" + x.descriptionArabic.Replace("'", "`") + "'" : "NULL") + ",IsDisabled = " + (x.isDisabled != null ? "'" + x.isDisabled + "'" : "NULL") + " WHERE Vehicle_Category_ID =" + vInfoID.ToString(); } command = new SqlCeCommand(sqlQuery, con); command.Parameters.Add("@Vehicle_Category_ID", SqlDbType.Int).Value = vInfoID; if (x.parentCategoryCode != null) { command.Parameters.Add("@Parent_Vehicle_Category_ID", Int32.Parse(x.parentCategoryCode)); } else { command.Parameters.Add("@Parent_Vehicle_Category_ID", DBNull.Value); } command.Parameters.Add("@Record_Type", SqlDbType.NChar, 25).Value = x.recordType; command.Parameters.Add("@Category_Code", SqlDbType.NChar, 25).Value = x.code; command.Parameters.Add("@Category_Name", SqlDbType.NChar, 100).Value = x.name; command.Parameters.Add("@Category_Name_A", SqlDbType.NChar, 100).Value = x.nameArabic; command.Parameters.Add("@Category_Desc", SqlDbType.NChar, 250).Value = x.description; command.Parameters.Add("@Category_Desc_A", SqlDbType.NChar, 250).Value = x.descriptionArabic; command.Parameters.Add("@IsDisabled", SqlDbType.NChar, 1).Value = (x.isDisabled.ToString())[0]; rs = command.ExecuteResultSet(ResultSetOptions.Scrollable); rs.Close(); con.Close(); } } } catch (Exception ex) { CommonUtils.WriteLog(ex.StackTrace); } }
public void Synchronize_vehicleCatDefectSeverity(handHeldService.SynchronizeConfigDataResponseItem test) { try { SqlCeConnection con = ((VSDApp.com.rta.vsd.hh.db.IDBManager)VSDApp.com.rta.vsd.hh.db.DBConnectionManager.GetInstance()).GetConnection(); string sqlQuery; if (con.State == ConnectionState.Closed) { con.Open(); } SqlCeCommand command; SqlCeResultSet rs; int rowCount; int temp = -1; /////////////////////////// //check for Vehicle Category Defect updates if (test.vehicleCatDefectSeverity != null) { handHeldService.VehicleCategoryDefectSeverity[] WebServiceDefects = new VSDApp.handHeldService.VehicleCategoryDefectSeverity[test.vehicleCatDefectSeverity.Length]; WebServiceDefects = test.vehicleCatDefectSeverity; foreach (handHeldService.VehicleCategoryDefectSeverity x in WebServiceDefects) { if (con.State == ConnectionState.Closed) { con.Open(); } /* * IF EXISTS (SELECT * FROM VSD_Defect WHERE DefectID = @DefectID) UPDATE Table1 SET (...) WHERE Column1='SomeValue' ELSE INSERT INTO Table1 VALUES (...) */ sqlQuery = "SELECT COUNT(*) AS Expr1" + " FROM VSD_Veh_Cat_Defect" + " WHERE (Veh_Cat_Defect_id = @VehCatDefectID)"; command = new SqlCeCommand(sqlQuery, con); command.Parameters.Add("@VehCatDefectID", SqlDbType.Int).Value = x.id; rs = command.ExecuteResultSet(ResultSetOptions.Scrollable); rowCount = ((System.Collections.ICollection)rs.ResultSetView).Count; //rountine for Defect Table if (rowCount > 0) { rs.ReadFirst(); temp = rs.GetInt32(0); } if (temp == 0) { sqlQuery = " INSERT INTO VSD_Veh_Cat_Defect (Veh_Cat_Defect_ID,Defect_ID,Severity_Level_ID,Vehicle_category_ID,IsDisabled) VALUES (" + x.id + "," + (x.defectId != null ? "" + x.defectId + "" : "NULL") + "," + (x.severityLevelId != null ? "" + x.severityLevelId + "" : "NULL") + "," + (x.vehicleCategoryId != null ? "" + x.vehicleCategoryId + "" : "NULL") + "," + (x.isDisabled != null ? "'" + x.isDisabled + "'" : "NULL") + ")"; } else { sqlQuery = " UPDATE VSD_Veh_Cat_Defect SET Veh_Cat_Defect_ID = " + x.id + ",Defect_ID = " + (x.defectId != null ? "" + x.defectId + "" : "NULL") + ",Severity_Level_ID = " + (x.severityLevelId != null ? "" + x.severityLevelId + "" : "NULL") + ",Vehicle_category_ID = " + (x.vehicleCategoryId != null ? "" + x.vehicleCategoryId + "" : "NULL") + ",IsDisabled = " + (x.isDisabled != null ? "'" + x.isDisabled + "'" : "NULL") + " WHERE Veh_Cat_Defect_ID = " + x.id; } command = new SqlCeCommand(sqlQuery, con); command.Parameters.Add("@Veh_Cat_Defect_ID", SqlDbType.Int).Value = x.id; command.Parameters.Add("@Defect_ID", SqlDbType.Int).Value = x.defectId; command.Parameters.Add("@Severity_Level_ID", SqlDbType.Int).Value = x.severityLevelId; command.Parameters.Add("@Vehicle_category_ID", SqlDbType.Int).Value = x.vehicleCategoryId; command.Parameters.Add("@IsDisabled", SqlDbType.NChar, 1).Value = (x.isDisabled.ToString())[0]; rs = command.ExecuteResultSet(ResultSetOptions.Scrollable); rs.Close(); con.Close(); } } } catch (Exception ex) { CommonUtils.WriteLog(ex.StackTrace); } }
public void Synchronize_severityLevel(handHeldService.SynchronizeConfigDataResponseItem test) { try { SqlCeConnection con = ((VSDApp.com.rta.vsd.hh.db.IDBManager)VSDApp.com.rta.vsd.hh.db.DBConnectionManager.GetInstance()).GetConnection(); string sqlQuery; if (con.State == ConnectionState.Closed) { con.Open(); } SqlCeCommand command; SqlCeResultSet rs; int rowCount; int temp = -1; //check for severity value updates if (test.severityLevel != null) { handHeldService.SeverityLevel1[] WebServiceDefects = new VSDApp.handHeldService.SeverityLevel1[test.severityLevel.Length]; WebServiceDefects = test.severityLevel; foreach (handHeldService.SeverityLevel1 x in WebServiceDefects) { if (con.State == ConnectionState.Closed) { con.Open(); } /* * IF EXISTS (SELECT * FROM VSD_Defect WHERE DefectID = @DefectID) UPDATE Table1 SET (...) WHERE Column1='SomeValue' ELSE INSERT INTO Table1 VALUES (...) */ sqlQuery = "SELECT COUNT(*) AS Expr1" + " FROM VSD_Severity_Level" + " WHERE (Severity_Level_ID = " + x.id + ")"; command = new SqlCeCommand(sqlQuery, con); rs = command.ExecuteResultSet(ResultSetOptions.Scrollable); rowCount = ((System.Collections.ICollection)rs.ResultSetView).Count; //rountine for Defect Table if (rowCount > 0) { rs.ReadFirst(); temp = rs.GetInt32(0); } if (temp == 0) { sqlQuery = " INSERT INTO VSD_Severity_Level (Severity_Level_ID,Severity_Level,Severity_Level_Code,Severity_Level_Code_Prefix,Severity_Level_Name,Severity_Level_Name_A,Severity_Level_Desc,Severity_Level_Desc_A) VALUES (" + x.id + "," + (x.value != null ? "'" + x.value.Replace("'", "`") + "'" : "NULL") + "," + (x.code != null ? "'" + x.code + "'" : "NULL") + "," + (x.codePrefix != null ? "'" + x.codePrefix + "'" : "NULL") + "," + (x.name != null ? "'" + x.name.Replace("'", "`") + "'" : "NULL") + "," + (x.nameArabic != null ? "'" + x.nameArabic.Replace("'", "`") + "'" : "NULL") + "," + (x.description != null ? "'" + x.description.Replace("'", "`") + "'" : "NULL") + "," + (x.descriptionArabic != null ? "'" + x.descriptionArabic.Replace("'", "`") + "'" : "NULL") + ")"; } else { sqlQuery = " UPDATE VSD_Severity_Level SET Severity_Level_ID = " + x.id + ", Severity_Level = " + (x.value != null ? "'" + x.value.Replace("'", "`") + "'" : "NULL") + ", Severity_Level_Code = " + (x.code != null ? "'" + x.code + "'" : "NULL") + ",Severity_Level_Code_Prefix = " + (x.codePrefix != null ? "'" + x.codePrefix + "'" : "NULL") + ",Severity_Level_Name = " + (x.name != null ? "'" + x.name.Replace("'", "`") + "'" : "NULL") + ",Severity_Level_Name_A = " + (x.nameArabic != null ? "'" + x.nameArabic.Replace("'", "`") + "'" : "NULL") + ",Severity_Level_Desc = " + (x.description != null ? "'" + x.description.Replace("'", "`") + "'" : "NULL") + ",Severity_Level_Desc_A = " + (x.descriptionArabic != null ? "'" + x.descriptionArabic.Replace("'", "`") + "'" : "NULL") + " WHERE Severity_Level_ID = " + x.id; } command = new SqlCeCommand(sqlQuery, con); command.Parameters.Add("@SeverityLevelID", SqlDbType.Int).Value = x.id; command.Parameters.Add("@SeverityLevel", SqlDbType.Int).Value = x.value; command.Parameters.Add("@SeverityLevelCode", SqlDbType.NChar, 25).Value = x.code; command.Parameters.Add("@SeverityLevelCodePrefix", SqlDbType.NChar, 25).Value = x.codePrefix; command.Parameters.Add("@SeverityLevelName", SqlDbType.NChar, 100).Value = x.name; command.Parameters.Add("@SeverityLevelNameArabic", SqlDbType.NChar, 100).Value = x.nameArabic; command.Parameters.Add("@SeverityLevelDesc", SqlDbType.NChar, 250).Value = x.description; command.Parameters.Add("@SeverityLevelDescA", SqlDbType.NChar, 250).Value = x.descriptionArabic; rs = command.ExecuteResultSet(ResultSetOptions.Scrollable); rs.Close(); con.Close(); } } } catch (Exception ex) { CommonUtils.WriteLog(ex.StackTrace); } }
public void Synchronize_severityLevelProperty(handHeldService.SynchronizeConfigDataResponseItem test) { try { SqlCeConnection con = ((VSDApp.com.rta.vsd.hh.db.IDBManager)VSDApp.com.rta.vsd.hh.db.DBConnectionManager.GetInstance()).GetConnection(); string sqlQuery; if (con.State == ConnectionState.Closed) { con.Open(); } SqlCeCommand command; SqlCeResultSet rs; int rowCount; int temp = -1; //check for severity level property updates if (test.severityLevelProperty != null) { handHeldService.SeverityLevelProperties[] WebServiceDefects = new VSDApp.handHeldService.SeverityLevelProperties[test.severityLevelProperty.Length]; WebServiceDefects = test.severityLevelProperty; foreach (handHeldService.SeverityLevelProperties x in WebServiceDefects) { if (con.State == ConnectionState.Closed) { con.Open(); } /* * IF EXISTS (SELECT * FROM VSD_Defect WHERE DefectID = @DefectID) UPDATE Table1 SET (...) WHERE Column1='SomeValue' ELSE INSERT INTO Table1 VALUES (...) */ sqlQuery = "SELECT COUNT(*) AS Expr1" + " FROM VSD_Severity_Level_Prop" + " WHERE (Severity_Level_Prop_ID = @SeverityLevelPropID)"; command = new SqlCeCommand(sqlQuery, con); command.Parameters.Add("@SeverityLevelPropID", SqlDbType.Int).Value = x.id; rs = command.ExecuteResultSet(ResultSetOptions.Scrollable); rowCount = ((System.Collections.ICollection)rs.ResultSetView).Count; int SeverityLevelPropID = x.id; //rountine for Defect Table if (rowCount > 0) { rs.ReadFirst(); temp = rs.GetInt32(0); } if (temp == 0) { sqlQuery = " INSERT INTO VSD_Severity_Level_Prop (Severity_Level_Prop_ID,Severity_Level_ID,Min_No_Of_Defects,Max_No_Of_Defects,Due_Days,Vehicle_Service_Suspension_Days,Comp_Service_Suspension_Days,Req_Plate_Confiscation,Receipt_Title,Receipt_Title_A,IsDisabled) VALUES (" + x.id + "," + (x.severityId != null ? "" + x.severityId + "" : "NULL") + "," + (x.minDefects != null ? "" + x.minDefects + "" : "NULL") + "," + (x.maxDefects != null ? "" + x.maxDefects + "" : "NULL") + "," + (x.dueDays != null ? "" + x.dueDays + "" : "NULL") + "," + (x.vehicleServiceSuspendDays != null ? "" + x.vehicleServiceSuspendDays + "" : "NULL") + "," + (x.companyServiceSuspenseDays != null ? "" + x.companyServiceSuspenseDays + "" : "NULL") + "," + (x.requirePlateConfiscation != null ? "'" + x.requirePlateConfiscation + "'" : "NULL") + "," + (x.receiptTitle != null ? "'" + x.receiptTitle.Replace("'", "`") + "'" : "NULL") + "," + (x.receiptTitleArabic != null ? "'" + x.receiptTitleArabic.Replace("'", "`") + "'" : "NULL") + "," + (x.isDisabled != null ? "'" + x.isDisabled + "'" : "NULL") + ")"; } else { sqlQuery = " UPDATE VSD_Severity_Level_Prop SET Severity_Level_Prop_ID = " + x.id + ",Severity_Level_ID = " + (x.severityId != null ? "" + x.severityId + "" : "NULL") + ",Min_No_Of_Defects = " + (x.minDefects != null ? "" + x.minDefects + "" : "NULL") + ",Max_No_Of_Defects = " + (x.maxDefects != null ? "" + x.maxDefects + "" : "NULL") + ",Due_Days = " + (x.dueDays != null ? "" + x.dueDays + "" : "NULL") + ",Vehicle_Service_Suspension_Days = " + (x.vehicleServiceSuspendDays != null ? "" + x.vehicleServiceSuspendDays + "" : "NULL") + ",Comp_Service_Suspension_Days = " + (x.companyServiceSuspenseDays != null ? "" + x.companyServiceSuspenseDays + "" : "NULL") + ",Req_Plate_Confiscation = " + (x.requirePlateConfiscation != null ? "'" + x.requirePlateConfiscation + "'" : "NULL") + ",Receipt_Title = " + (x.receiptTitle != null ? "'" + x.receiptTitle.Replace("'", "`") + "'" : "NULL") + ",Receipt_Title_A = " + (x.receiptTitleArabic != null ? "'" + x.receiptTitleArabic.Replace("'", "`") + "'" : "NULL") + ",IsDisabled = " + (x.isDisabled != null ? "'" + x.isDisabled + "'" : "NULL") + " WHERE Severity_Level_Prop_ID = " + x.id; } // command.Parameters.Add("@SeverityLevelPropID", SqlDbType.Int).Value = SeverityLevelPropID; command.Parameters.Add("@SeverityLevelID", SqlDbType.Int).Value = SeverityLevelPropID; command.Parameters.Add("@MinNoOfDefects", SqlDbType.Int).Value = x.minDefects; command.Parameters.Add("@MaxNoOfDefects", SqlDbType.Int).Value = x.maxDefects; command.Parameters.Add("@CompServiceSuspensionDays", SqlDbType.Int).Value = x.companyServiceSuspenseDays; command.Parameters.Add("@ReqPlateConfiscation", SqlDbType.NChar, 1).Value = (null == x.requirePlateConfiscation) ? "F" : "T"; command.Parameters.Add("@VehRegBlockDays", SqlDbType.Int).Value = x.vehicleServiceSuspendDays; command.Parameters.Add("@ReceiptTitle", SqlDbType.NChar, 100).Value = x.receiptTitle; command.Parameters.Add("@ReceiptTitleA", SqlDbType.NChar, 100).Value = x.receiptTitleArabic; command.Parameters.Add("@IsDisabled", SqlDbType.Int).Value = ("F" == x.isDisabled) ? "F" : "T"; command.Parameters.Add("@ConfiscationDays", SqlDbType.Int).Value = x.plateConfiscationDays; command = new SqlCeCommand(sqlQuery, con); rs = command.ExecuteResultSet(ResultSetOptions.Scrollable); rs.Close(); con.Close(); } } } catch (Exception ex) { CommonUtils.WriteLog(ex.StackTrace); } }
public void Synchronize_location(handHeldService.SynchronizeConfigDataResponseItem test) { try { SqlCeConnection con = ((VSDApp.com.rta.vsd.hh.db.IDBManager)VSDApp.com.rta.vsd.hh.db.DBConnectionManager.GetInstance()).GetConnection(); string sqlQuery; if (con.State == ConnectionState.Closed) { con.Open(); } SqlCeCommand command; SqlCeResultSet rs; int rowCount; int temp = -1; //check for location updates if (test.location != null) { handHeldService.Location[] WebServiceDefects = new VSDApp.handHeldService.Location[test.location.Length]; WebServiceDefects = test.location; foreach (handHeldService.Location x in WebServiceDefects) { if (con.State == ConnectionState.Closed) { con.Open(); } /* * IF EXISTS (SELECT * FROM VSD_Defect WHERE DefectID = @DefectID) UPDATE Table1 SET (...) WHERE Column1='SomeValue' ELSE INSERT INTO Table1 VALUES (...) */ sqlQuery = "SELECT COUNT(*) AS Expr1" + " FROM VSD_Location" + " WHERE (Location_ID = @LocationID)"; command = new SqlCeCommand(sqlQuery, con); command.Parameters.Add("@LocationID", SqlDbType.Int).Value = Int32.Parse(x.id); rs = command.ExecuteResultSet(ResultSetOptions.Scrollable); rowCount = ((System.Collections.ICollection)rs.ResultSetView).Count; //rountine for Defect Table if (rowCount > 0) { rs.ReadFirst(); temp = rs.GetInt32(0); } if (temp == 0) { sqlQuery = " INSERT INTO VSD_Location (Location_Area_Code , Location_Area_Name , Location_Area_Name_A ,Country_ID , Location_ID , IsDisabled ,Parent_Location_ID ) VALUES ( " + (x.areaCode != null ? "'" + x.areaCode + "'" : "NULL") + ", " + (x.areaName != null ? "'" + x.areaName.Replace("'", "`") + "'" : "NULL") + ", " + (x.areaNameArabic != null ? "'" + x.areaNameArabic.Replace("'", "`") + "'" : "NULL") + "," + (x.country.id != null ? "" + x.country.id + "" : "NULL") + ", " + (x.id != null ? "" + x.id + "" : "NULL") + ", " + (x.isDisabled != null ? "'" + x.isDisabled + "'" : "NULL") + "," + (x.parentId != null ? "" + x.parentId + "" : "NULL") + ")"; } else { sqlQuery = " UPDATE VSD_Location SET Location_Area_Code = " + (x.areaCode != null ? "'" + x.areaCode + "'" : "NULL") + ", Location_Area_Name = " + (x.areaName != null ? "'" + x.areaName.Replace("'", "`") + "'" : "NULL") + ", Location_Area_Name_A = " + (x.areaNameArabic != null ? "'" + x.areaNameArabic.Replace("'", "`") + "'" : "NULL") + ",Country_ID = " + (x.country.id != null ? "" + x.country.id + "" : "NULL") + ", Location_ID = " + (x.id != null ? "" + x.id + "" : "NULL") + ", IsDisabled = " + (x.isDisabled != null ? "'" + x.isDisabled + "'" : "NULL") + ",Parent_Location_ID = " + (x.parentId != null ? "" + x.parentId + "" : "NULL") + " WHERE Location_ID = " + (x.id != null ? "" + x.id + "" : "NULL") + " "; } command = new SqlCeCommand(sqlQuery, con); command.Parameters.Add("@LocationAreaCode", SqlDbType.NChar, 50).Value = x.areaCode; command.Parameters.Add("@LocationAreaName", SqlDbType.NChar, 100).Value = x.areaName; command.Parameters.Add("@LocationAreaNameArabic", SqlDbType.NChar, 200).Value = x.areaNameArabic; command.Parameters.Add("@CountryID", SqlDbType.Int).Value = x.country.id; command.Parameters.Add("@LocationID", SqlDbType.Int).Value = x.id; command.Parameters.Add("@IsDisabled", SqlDbType.Int).Value = x.isDisabled; if (x.parentId != null) { command.Parameters.Add("@ParentLocationID", Int32.Parse(x.parentId)); } else { command.Parameters.Add("@ParentLocationID", DBNull.Value); } rs = command.ExecuteResultSet(ResultSetOptions.Scrollable); rs.Close(); con.Close(); } } } catch (Exception ex) { CommonUtils.WriteLog(ex.StackTrace); } }
public void Synchronize_propertyValue(handHeldService.SynchronizeConfigDataResponseItem test) { try { SqlCeConnection con = ((VSDApp.com.rta.vsd.hh.db.IDBManager)VSDApp.com.rta.vsd.hh.db.DBConnectionManager.GetInstance()).GetConnection(); string sqlQuery; if (con.State == ConnectionState.Closed) { con.Open(); } SqlCeCommand command; SqlCeResultSet rs; int rowCount; int temp = -1; //check for property value updates if (test.propertyValue != null) { handHeldService.PropertyValue[] WebServiceDefects = new VSDApp.handHeldService.PropertyValue[test.propertyValue.Length]; WebServiceDefects = test.propertyValue; foreach (handHeldService.PropertyValue x in WebServiceDefects) { if (con.State == ConnectionState.Closed) { con.Open(); } /* * IF EXISTS (SELECT * FROM VSD_Defect WHERE DefectID = @DefectID) UPDATE Table1 SET (...) WHERE Column1='SomeValue' ELSE INSERT INTO Table1 VALUES (...) */ sqlQuery = "SELECT COUNT(*) AS Expr1" + " FROM VSD_Property_Value" + " WHERE (Property_Type_Value_ID = @PropertyValueID)"; command = new SqlCeCommand(sqlQuery, con); command.Parameters.Add("@PropertyValueID", SqlDbType.Int).Value = Int32.Parse(x.id); rs = command.ExecuteResultSet(ResultSetOptions.Scrollable); rowCount = ((System.Collections.ICollection)rs.ResultSetView).Count; //rountine for Defect Table if (rowCount > 0) { rs.ReadFirst(); temp = rs.GetInt32(0); } if (temp == 0) { sqlQuery = " INSERT INTO VSD_Property_Value (Property_Type_Value_ID ,Property_Type_ID,Property_Type_Name,Property_Type_Value,IsDisabled) VALUES (" + x.id + " ," + (x.propertyTypeId != null ? "" + x.propertyTypeId + "" : "NULL") + "," + (x.valueName != null ? "'" + x.valueName.Replace("'", "`") + "'" : "NULL") + "," + (x.value != null ? "'" + x.value.Replace("'", "`") + "'" : "NULL") + "," + (x.isDisabled != null ? "'" + x.isDisabled + "'" : "NULL") + ")"; } else { sqlQuery = " UPDATE VSD_Property_Value SET Property_Type_Value_ID = " + (x.id != null ? "" + x.id + "" : "NULL") + " ,Property_Type_ID = " + (x.propertyTypeId != null ? "" + x.propertyTypeId + "" : "NULL") + ", Property_Type_Name = " + (x.valueName != null ? "'" + x.valueName.Replace("'", "`") + "'" : "NULL") + " ,Property_Type_Value = " + (x.value != null ? "'" + x.value.Replace("'", "`") + "'" : "NULL") + ",IsDisabled = " + (x.isDisabled != null ? "'" + x.isDisabled + "'" : "NULL") + " WHERE PROPERTY_TYPE_VALUE_ID = " + (x.id != null ? "" + x.id + "" : "NULL") + " "; } command = new SqlCeCommand(sqlQuery, con); command.Parameters.Add("@PropertyTypeValueID", SqlDbType.Int).Value = x.id; command.Parameters.Add("@PropertyTypeID", SqlDbType.Int).Value = x.propertyTypeId; command.Parameters.Add("@PropertyTypeName", SqlDbType.NChar, 50).Value = x.valueName; command.Parameters.Add("@PropertyTypeValue", SqlDbType.NChar, 50).Value = x.value; command.Parameters.Add("@IsDisabled", SqlDbType.NChar, 1).Value = x.isDisabled; rs = command.ExecuteResultSet(ResultSetOptions.Scrollable); rs.Close(); con.Close(); } } } catch (Exception ex) { CommonUtils.WriteLog(ex.StackTrace); } }
public void Synchronize_country(handHeldService.SynchronizeConfigDataResponseItem test) { try { SqlCeConnection con = ((VSDApp.com.rta.vsd.hh.db.IDBManager)VSDApp.com.rta.vsd.hh.db.DBConnectionManager.GetInstance()).GetConnection(); string sqlQuery; if (con.State == ConnectionState.Closed) { con.Open(); } SqlCeCommand command; SqlCeResultSet rs; int rowCount; int temp = -1; //check for country updates if (test.country != null) { handHeldService.Country[] WebServiceDefects = new VSDApp.handHeldService.Country[test.country.Length]; WebServiceDefects = test.country; foreach (handHeldService.Country x in WebServiceDefects) { if (con.State == ConnectionState.Closed) { con.Open(); } /* * IF EXISTS (SELECT * FROM VSD_Defect WHERE DefectID = @DefectID) UPDATE Table1 SET (...) WHERE Column1='SomeValue' ELSE INSERT INTO Table1 VALUES (...) */ sqlQuery = "SELECT COUNT(*) AS Expr1" + " FROM VSD_Country" + " WHERE (Country_ID = @CountryID)"; command = new SqlCeCommand(sqlQuery, con); command.Parameters.Add("@CountryID", SqlDbType.Int).Value = Int32.Parse(x.id); rs = command.ExecuteResultSet(ResultSetOptions.Scrollable); rowCount = ((System.Collections.ICollection)rs.ResultSetView).Count; //rountine for Defect Table if (rowCount > 0) { rs.ReadFirst(); temp = rs.GetInt32(0); } if (temp == 0) { sqlQuery = " INSERT INTO VSD_Country (Country_ID ,Country_Code,Country_Name,Parent_Country_ID) VALUES (" + (x.id != null ? x.id : "NULL") + "," + (x.code != null ? "'" + x.code + "'" : "NULL") + "," + (x.name != null ? "'" + x.name.Replace("'", "`") + "'" : "NULL") + "," + (x.parentId != null ? "" + x.parentId + "" : "NULL") + ") "; } else { sqlQuery = " UPDATE VSD_Country SET Country_Code = " + (x.code != null ? "'" + x.code + "'" : "NULL") + ",Country_Name = " + (x.name != null ? "'" + x.name.Replace("'", "`") + "'" : "NULL") + ",Parent_Country_ID = " + (x.parentId != null ? "" + x.parentId + "" : "NULL") + " WHERE Country_ID = " + (x.id != null ? "" + x.id + "" : "NULL"); } command = new SqlCeCommand(sqlQuery, con); command.Parameters.Add("@CountryID", SqlDbType.Int).Value = Int32.Parse(x.id); command.Parameters.Add("@CountryCode", SqlDbType.NChar, 25).Value = x.code; command.Parameters.Add("@CountryName", SqlDbType.NChar, 50).Value = x.name; rs = command.ExecuteResultSet(ResultSetOptions.Scrollable); rs.Close(); con.Close(); } } } catch (Exception) { throw; } }
public void Synchronize_countryProperty(handHeldService.SynchronizeConfigDataResponseItem test) { try { SqlCeConnection con = ((VSDApp.com.rta.vsd.hh.db.IDBManager)VSDApp.com.rta.vsd.hh.db.DBConnectionManager.GetInstance()).GetConnection(); string sqlQuery; if (con.State == ConnectionState.Closed) { con.Open(); } SqlCeCommand command; SqlCeResultSet rs; int rowCount; int temp = -1; //check for country property updates if (test.countryProperty != null) { handHeldService.CountryProperty[] WebServiceDefects = new VSDApp.handHeldService.CountryProperty[test.countryProperty.Length]; WebServiceDefects = test.countryProperty; foreach (handHeldService.CountryProperty x in WebServiceDefects) { if (con.State == ConnectionState.Closed) { con.Open(); } /* * IF EXISTS (SELECT * FROM VSD_Defect WHERE DefectID = @DefectID) UPDATE Table1 SET (...) WHERE Column1='SomeValue' ELSE INSERT INTO Table1 VALUES (...) */ sqlQuery = "SELECT COUNT(*) AS Expr1" + " FROM VSD_Country_Prop" + " WHERE (Country_Prop_ID = @CountryPropID)"; command = new SqlCeCommand(sqlQuery, con); command.Parameters.Add("@CountryPropID", SqlDbType.Int).Value = Int32.Parse(x.id); rs = command.ExecuteResultSet(ResultSetOptions.Scrollable); rowCount = ((System.Collections.ICollection)rs.ResultSetView).Count; //rountine for Defect Table if (rowCount > 0) { rs.ReadFirst(); temp = rs.GetInt32(0); } if (temp == 0) { sqlQuery = " INSERT INTO VSD_Country_Prop (Can_Confiscate_Plates,Can_Fetch_Info,Can_Force_Vehicle_Testing,Can_Inspect,Can_Print_Viol_Notice,Can_Raise_Violation, Can_Send_Fine,Country_ID,Country_Prop_ID,IsDefault,IsDisabled) VALUES (" + (x.canConfiscatePlate != null ? "'" + x.canConfiscatePlate + "'" : "NULL") + "," + (x.canFetchInfo != null ? "'" + x.canFetchInfo + "'" : "NULL") + "," + (x.canForceVehicleTesting != null ? "'" + x.canForceVehicleTesting + "'" : "NULL") + "," + (x.canInspect != null ? "'" + x.canInspect + "'" : "NULL") + "," + (x.canPrintViolationTicket != null ? "'" + x.canPrintViolationTicket + "'" : "NULL") + "," + (x.canRaiseViolation != null ? "'" + x.canRaiseViolation + "'" : "NULL") + "," + (x.canSendFine != null ? "'" + x.canSendFine + "'" : "NULL") + "," + (x.countryId != null ? "" + x.countryId + "" : "NULL") + "," + (x.id != null ? "'" + x.id + "'" : "NULL") + "," + (x.isDefault != null ? "'" + x.isDefault + "'" : "NULL") + "," + (x.isDisabled != null ? "'" + x.isDisabled + "'" : "NULL") + ") "; } else { sqlQuery = " UPDATE VSD_Country_Prop SET Can_Confiscate_Plates = " + (x.canConfiscatePlate != null ? "'" + x.canConfiscatePlate + "'" : "NULL") + " , Can_Fetch_Info = " + (x.canFetchInfo != null ? "'" + x.canFetchInfo + "'" : "NULL") + ",Can_Force_Vehicle_Testing = " + (x.canForceVehicleTesting != null ? "'" + x.canForceVehicleTesting + "'" : "NULL") + ",Can_Inspect = " + (x.canInspect != null ? "'" + x.canInspect + "'" : "NULL") + ",Can_Print_Viol_Notice = " + (x.canPrintViolationTicket != null ? "'" + x.canPrintViolationTicket + "'" : "NULL") + ",Can_Raise_Violation = " + (x.canRaiseViolation != null ? "'" + x.canRaiseViolation + "'" : "NULL") + ", Can_Send_Fine = " + (x.canSendFine != null ? "'" + x.canSendFine + "'" : "NULL") + ",Country_ID = " + (x.canConfiscatePlate != null ? "" + x.countryId + "" : "NULL") + ",Country_Prop_ID = " + (x.id != null ? "'" + x.id + "'" : "NULL") + ",IsDefault = " + (x.isDefault != null ? "'" + x.isDefault + "'" : "NULL") + ",IsDisabled = " + (x.isDisabled != null ? "'" + x.isDisabled + "'" : "NULL") + " WHERE Country_Prop_ID = " + (x.id != null ? "'" + x.id + "'" : "NULL") + " "; } command = new SqlCeCommand(sqlQuery, con); command.Parameters.Add("@CanConfiscatePlates", SqlDbType.NChar, 1).Value = x.canConfiscatePlate; command.Parameters.Add("@CanFetchInfo", SqlDbType.NChar, 1).Value = x.canFetchInfo; command.Parameters.Add("@CanForceVehicleTesting", SqlDbType.NChar, 1).Value = x.canForceVehicleTesting; command.Parameters.Add("@CanInspect", SqlDbType.NChar, 1).Value = x.canInspect; command.Parameters.Add("@CanPrintViolTicket", SqlDbType.NChar, 1).Value = x.canPrintViolationTicket; command.Parameters.Add("@CanRaiseViolation", SqlDbType.NChar, 1).Value = x.canRaiseViolation; command.Parameters.Add("@CanSendFine", SqlDbType.NChar, 1).Value = x.canSendFine; command.Parameters.Add("@CountryID", SqlDbType.Int).Value = x.countryId; command.Parameters.Add("@CountryPropID", SqlDbType.Int).Value = x.id; command.Parameters.Add("@IsDefault", SqlDbType.NChar, 1).Value = x.isDefault; command.Parameters.Add("@IsDisabled", SqlDbType.NChar, 1).Value = (x.isDisabled.ToString())[0]; rs = command.ExecuteResultSet(ResultSetOptions.Scrollable); rs.Close(); con.Close(); } } } catch (Exception ex) { CommonUtils.WriteLog(ex.StackTrace); } }
public void Synchronize_defect(handHeldService.SynchronizeConfigDataResponseItem test) { try { SqlCeConnection con = ((VSDApp.com.rta.vsd.hh.db.IDBManager)VSDApp.com.rta.vsd.hh.db.DBConnectionManager.GetInstance()).GetConnection(); string sqlQuery; if (con.State == ConnectionState.Closed) { con.Open(); } SqlCeCommand command; SqlCeResultSet rs; int rowCount; int temp = -1; if (test.defect != null) { handHeldService.Defect1[] WebServiceDefects = new VSDApp.handHeldService.Defect1[test.defect.Length]; WebServiceDefects = test.defect; int count = 0; foreach (handHeldService.Defect1 x in WebServiceDefects) { if (con.State == ConnectionState.Closed) { con.Open(); } /* * IF EXISTS (SELECT * FROM VSD_Defect WHERE DefectID = @DefectID) UPDATE Table1 SET (...) WHERE Column1='SomeValue' ELSE INSERT INTO Table1 VALUES (...) */ sqlQuery = "SELECT COUNT(*) AS Expr1" + " FROM VSD_Defect" + " WHERE (Defect_ID = @DefectID)"; command = new SqlCeCommand(sqlQuery, con); command.Parameters.Add("@DefectID", SqlDbType.Int).Value = Int32.Parse(x.id); rs = command.ExecuteResultSet(ResultSetOptions.Scrollable); rowCount = ((System.Collections.ICollection)rs.ResultSetView).Count; //rountine for Defect Table if (rowCount > 0) { rs.ReadFirst(); temp = rs.GetInt32(0); } if (temp == 0) { sqlQuery = " INSERT INTO VSD_Defect (Defect_ID ,Defect_Code_Prefix ,Defect_Code ,Defect_Desc ,Defect_Desc_A ,IsDisabled ,Defect_Name ,Defect_Name_A ,Defect_Type,Defect_Category_ID ) VALUES (@DefectID," + (x.codePrefix != null ? "'" + x.codePrefix + "'" : "NULL") + "," + (x.code != null ? "'" + x.code + "'" : "NULL") + "," + (x.description != null ? "'" + x.description.Replace("'", "`") + "'" : "NULL") + "," + (x.descriptionArabic != null ? "'" + x.descriptionArabic.Replace("'", "`") + "'" : "NULL") + ",'" + x.isDisabled + "'," + (x.name != null ? "'" + x.name.Replace("'", "`") + "'" : "NULL") + "," + (x.nameArabic != null ? "'" + x.nameArabic.Replace("'", "`") + "'" : "NULL") + "," + (x.type != null ? "'" + x.type + "'" : "NULL") + "," + x.category + ")"; } else { sqlQuery = " UPDATE VSD_Defect SET Defect_Code_Prefix = " + (x.codePrefix != null ? "'" + x.codePrefix + "'" : "NULL") + ",Defect_Code = " + (x.code != null ? "'" + x.code + "'" : "NULL") + ",Defect_Desc = " + (x.description != null ? "'" + x.description.Replace("'", "`") + "'" : "NULL") + ",Defect_Desc_A = " + (x.descriptionArabic != null ? "'" + x.descriptionArabic.Replace("'", "`") + "'" : "NULL") + ",IsDisabled = " + (x.isDisabled != null ? "'" + x.isDisabled + "'" : "NULL") + ",Defect_Name = " + (x.name != null ? "'" + x.name.Replace("'", "`") + "'" : "NULL") + ",Defect_Name_A = " + (x.nameArabic != null ? "'" + x.nameArabic.Replace("'", "`") + "'" : "NULL") + ",Defect_Type = " + (x.type != null ? "'" + x.type + "'" : "NULL") + ", Defect_Category_ID = " + x.category + " WHERE Defect_ID = " + x.id; } command = new SqlCeCommand(sqlQuery, con); command.Parameters.Add("@DefectCodePrefix", SqlDbType.NVarChar).Value = x.codePrefix; command.Parameters.Add("@DefectCode", SqlDbType.NVarChar).Value = x.code; command.Parameters.Add("@DefectDescription", SqlDbType.NVarChar).Value = x.description; command.Parameters.Add("@DefectDescriptionArabic", SqlDbType.NVarChar).Value = x.descriptionArabic; command.Parameters.Add("@IsDisabled", SqlDbType.NVarChar).Value = x.isDisabled; command.Parameters.Add("@DefectName", SqlDbType.NVarChar).Value = x.name; command.Parameters.Add("@DefectNameArabic", SqlDbType.NVarChar).Value = x.nameArabic; command.Parameters.Add("@DefectType", SqlDbType.NVarChar).Value = x.type; command.Parameters.Add("@DefectID", SqlDbType.Int).Value = Int32.Parse(x.id); command.Parameters.Add("@DefectCategoryID", SqlDbType.Int).Value = Int32.Parse(x.category); try { rs = command.ExecuteResultSet(ResultSetOptions.Scrollable); count++; } catch (Exception ex) { // CommonUtils.WriteLog(ex.StackTrace); throw; } rs.Close(); con.Close(); } } } catch (Exception) { throw; } }
int IDBManager.SynchronizeConfig(handHeldService.SynchronizeConfigDataResponseItem test) { if (test != null) { SqlCeConnection con = ((VSDApp.com.rta.vsd.hh.db.IDBManager)VSDApp.com.rta.vsd.hh.db.DBConnectionManager.GetInstance()).GetConnection(); string sqlQuery; if (con.State == ConnectionState.Closed) { con.Open(); } SqlCeCommand command; SqlCeResultSet rs; int rowCount; int temp = -1; Synchronize_defectCategoty(test); Synchronize_defect(test); Synchronize_country(test); Synchronize_countryProperty(test); Synchronize_location(test); Synchronize_propertyType(test); Synchronize_propertyValue(test); Synchronize_severityLevel(test); Synchronize_severityLevelProperty(test); Synchronize_vehicleCategory(test); Synchronize_vehicleCatDefectSeverity(test); Synchronize_testType(test); Synchronize_violationSeverityFeeRule(test); } return Int32.Parse((test.response.code)); }
public void Synchronize_violationSeverityFeeRule(handHeldService.SynchronizeConfigDataResponseItem test) { try { SqlCeConnection con = ((VSDApp.com.rta.vsd.hh.db.IDBManager)VSDApp.com.rta.vsd.hh.db.DBConnectionManager.GetInstance()).GetConnection(); string sqlQuery; if (con.State == ConnectionState.Closed) { con.Open(); } SqlCeCommand command; SqlCeResultSet rs; int rowCount; int temp = -1; //check for Vehicle Category Defect updates if (test.violationSeverityFeeRule != null) { handHeldService.ViolationSeverityFeeRule[] WebServiceDefects = new VSDApp.handHeldService.ViolationSeverityFeeRule[test.violationSeverityFeeRule.Length]; WebServiceDefects = test.violationSeverityFeeRule; foreach (handHeldService.ViolationSeverityFeeRule x in WebServiceDefects) { if (con.State == ConnectionState.Closed) { con.Open(); } /* * IF EXISTS (SELECT * FROM VSD_Defect WHERE DefectID = @DefectID) UPDATE Table1 SET (...) WHERE Column1='SomeValue' ELSE INSERT INTO Table1 VALUES (...) */ sqlQuery = "SELECT COUNT(*) AS Expr1" + " FROM VSD_Vio_Sev_Fee_Rule" + " WHERE (Vio_Sev_Fee_Rule_ID = @Vio_Sev_Fee_Rule_ID)"; command = new SqlCeCommand(sqlQuery, con); command.Parameters.Add("@Vio_Sev_Fee_Rule_ID", SqlDbType.Int).Value = x.id; rs = command.ExecuteResultSet(ResultSetOptions.Scrollable); rowCount = ((System.Collections.ICollection)rs.ResultSetView).Count; //rountine for Defect Table if (rowCount > 0) { rs.ReadFirst(); temp = rs.GetInt32(0); } if (temp == 0) { sqlQuery = " INSERT INTO VSD_Vio_Sev_Fee_Rule ( Vio_Sev_Fee_Rule_ID,Severity_Level_ID,Test_Type_ID,Test_Attempts,Min_No_Of_Defects,Max_No_Of_Defects,Is_Under_DueDate,Is_Fee_Applied,Fine_Amount,IsDisabled ) VALUES ( " + x.id + "," + (x.severityId != null ? "" + x.severityId + "" : "NULL") + "," + (x.testTypeId != null ? "" + x.testTypeId + "" : "NULL") + "," + (x.testAttepmts != null ? "" + x.testAttepmts + "" : "NULL") + "," + (x.minDefects != null ? "" + x.minDefects + "" : "NULL") + "," + (x.maxDefects != null ? "" + x.maxDefects + "" : "NULL") + "," + (x.isUnderDueDate != null ? "'" + x.isUnderDueDate + "'" : "NULL") + "," + (x.isFeeApplied != null ? "'" + x.isFeeApplied + "'" : "NULL") + "," + (x.feeAmount != null ? "" + x.feeAmount + "" : "NULL") + "," + (x.isDisabled != null ? "'" + x.isDisabled + "'" : "NULL") + " )"; } else { sqlQuery = " UPDATE VSD_Vio_Sev_Fee_Rule SET Vio_Sev_Fee_Rule_ID = " + x.id + ",Severity_Level_ID = " + (x.severityId != null ? "" + x.severityId + "" : "NULL") + ",Test_Type_ID = " + (x.testTypeId != null ? "" + x.testTypeId + "" : "NULL") + ",Test_Attempts = " + (x.testAttepmts != null ? "" + x.testAttepmts + "" : "NULL") + ",Min_No_Of_Defects = " + (x.minDefects != null ? "" + x.minDefects + "" : "NULL") + ",Max_No_Of_Defects = " + (x.maxDefects != null ? "" + x.maxDefects + "" : "NULL") + ",Is_Under_DueDate = " + (x.isUnderDueDate != null ? "'" + x.isUnderDueDate + "'" : "NULL") + ",Is_Fee_Applied = " + (x.isFeeApplied != null ? "'" + x.isFeeApplied + "'" : "NULL") + ",Fine_Amount = " + (x.feeAmount != null ? "" + x.feeAmount + "" : "NULL") + ",IsDisabled = " + (x.isDisabled != null ? "'" + x.isDisabled + "'" : "NULL") + " WHERE Vio_Sev_Fee_Rule_ID =" + x.id; } command = new SqlCeCommand(sqlQuery, con); command.Parameters.Add("@Vio_Sev_Fee_Rule_ID", SqlDbType.Int).Value = x.id; command.Parameters.Add("@Severity_Level_ID", SqlDbType.Int).Value = x.severityId; command.Parameters.Add("@Test_Type_ID", SqlDbType.Int).Value = x.testTypeId; command.Parameters.Add("@Test_Attempts", SqlDbType.Int).Value = x.testAttepmts; command.Parameters.Add("@Min_No_Of_Defects", SqlDbType.Int).Value = x.minDefects; command.Parameters.Add("@Max_No_Of_Defects", SqlDbType.Int).Value = x.maxDefects; command.Parameters.Add("@Is_Under_DueDate", SqlDbType.NVarChar).Value = x.isUnderDueDate; command.Parameters.Add("@Is_Fee_Applied", SqlDbType.NVarChar).Value = x.isFeeApplied; command.Parameters.Add("@Fine_Amount", SqlDbType.Int).Value = x.feeAmount; command.Parameters.Add("@IsDisabled", SqlDbType.NVarChar).Value = x.isDisabled; rs = command.ExecuteResultSet(ResultSetOptions.Scrollable); rs.Close(); con.Close(); } } } catch (Exception ex) { CommonUtils.WriteLog(ex.StackTrace); } }
public void Synchronize_testType(handHeldService.SynchronizeConfigDataResponseItem test) { try { SqlCeConnection con = ((VSDApp.com.rta.vsd.hh.db.IDBManager)VSDApp.com.rta.vsd.hh.db.DBConnectionManager.GetInstance()).GetConnection(); string sqlQuery; if (con.State == ConnectionState.Closed) { con.Open(); } SqlCeCommand command; SqlCeResultSet rs; int rowCount; int temp = -1; //check for test type updates if (test.testType != null) { handHeldService.TestType[] WebServiceDefects = new VSDApp.handHeldService.TestType[test.testType.Length]; WebServiceDefects = test.testType; foreach (handHeldService.TestType x in WebServiceDefects) { if (con.State == ConnectionState.Closed) { con.Open(); } /* * IF EXISTS (SELECT * FROM VSD_Defect WHERE DefectID = @DefectID) UPDATE Table1 SET (...) WHERE Column1='SomeValue' ELSE INSERT INTO Table1 VALUES (...) */ sqlQuery = "SELECT COUNT(*) AS Expr1" + " FROM VSD_Test_type" + " WHERE (test_type_id = " + x.id + ")"; command = new SqlCeCommand(sqlQuery, con); rs = command.ExecuteResultSet(ResultSetOptions.Scrollable); rowCount = ((System.Collections.ICollection)rs.ResultSetView).Count; //rountine for Defect Table if (rowCount > 0) { rs.ReadFirst(); temp = rs.GetInt32(0); } if (temp == 0) { sqlQuery = " INSERT INTO VSD_test_type (test_type_ID,record_type,test_type_code,test_type_name,test_type_name_a,test_type_desc,test_Type_desc_a,is_comprehensive_test,days_valid,isdisabled) VALUES (" + x.id + "," + (x.recordType != null ? "'" + x.recordType + "'" : "NULL") + "," + (x.code != null ? "'" + x.code + "'" : "NULL") + "," + (x.name != null ? "'" + x.name.Replace("'", "`") + "'" : "NULL") + "," + (x.nameArabic != null ? "'" + x.nameArabic.Replace("'", "`") + "'" : "NULL") + "," + (x.Description != null ? "'" + x.Description.Replace("'", "`") + "'" : "NULL") + "," + (x.DescriptionArabic != null ? "'" + x.DescriptionArabic.Replace("'", "`") + "'" : "NULL") + "," + (x.isComprehensive != null ? "'" + x.isComprehensive + "'" : "NULL") + "," + (x.daysValid != null ? "" + x.daysValid.ToString() + "" : "NULL") + "," + (x.isDisabled != null ? "'" + x.isDisabled + "'" : "NULL") + ")"; } else { sqlQuery = " UPDATE vsd_test_type SET test_type_id = " + x.id + ", record_type = " + (x.recordType != null ? "'" + x.recordType + "'" : "NULL") + ", test_type_code = " + (x.code != null ? "'" + x.code + "'" : "NULL") + ",test_type_name = " + (x.name != null ? "'" + x.name.Replace("'", "`") + "'" : "NULL") + ",test_type_name_a = " + (x.nameArabic != null ? "'" + x.nameArabic.Replace("'", "`") + "'" : "NULL") + ",test_type_desc = " + (x.Description != null ? "'" + x.Description.Replace("'", "`") + "'" : "NULL") + ",test_type_desc_a = " + (x.DescriptionArabic != null ? "'" + x.DescriptionArabic.Replace("'", "`") + "'" : "NULL") + ",is_comprehensive_test = " + (x.isComprehensive != null ? "'" + x.isComprehensive + "'" : "NULL") + ",days_valid = " + (x.daysValid != null ? "" + x.daysValid.ToString() + "" : "NULL") + ",isdisabled = " + (x.isDisabled != null ? "'" + x.isDisabled + "'" : "NULL") + " WHERE test_type_id = " + x.id; } command = new SqlCeCommand(sqlQuery, con); rs = command.ExecuteResultSet(ResultSetOptions.Scrollable); rs.Close(); con.Close(); } } } catch (Exception ex) { CommonUtils.WriteLog(ex.StackTrace); } }
int IDBManager.SynchronizeConfig(handHeldService.SynchronizeConfigDataResponseItem test) { throw new NotImplementedException(); }