public int SaveDataDataset(DataSet ds) { string SQL = new SQLGenerator().DataSetSQL(ds); SQLAccess sqla = new SQLAccess(); return(sqla.ExecuteSQL(SQL)); }
/// <summary> /// This Method is not used anymore because we switched to a GUID keys /// </summary> /// <param name="TableName"></param> /// <returns></returns> public static long GetSingleTableKey(string TableName) { SQLAccess da = new SQLAccess(); long KeyValue = -1; StringBuilder SQLi9Table = new StringBuilder(); SQLi9Table.AppendLine(" BEGIN TRAN T1 "); SQLi9Table.AppendLine(" Update i9TableKey set KeyValue = KeyValue + 1 WHERE TableName = " + SQLUtility.SQLString(TableName) + " "); SQLi9Table.AppendLine(" SELECT KeyValue FROM i9TableKey WHERE TableName = " + SQLUtility.SQLString(TableName) + " "); SQLi9Table.AppendLine(" COMMIT TRAN T1 "); string SQL = SQLi9Table.ToString(); DataTable dt = da.GetDataTable(SQLi9Table.ToString(), "Results"); if (dt.Rows.Count > 0) { if (DBNull.Value != dt.Rows[0]["KeyValue"]) { if (String.IsNullOrEmpty(dt.Rows[0]["KeyValue"].ToString()) == false) { KeyValue = (long)dt.Rows[0]["KeyValue"]; } } } return(KeyValue); }
private i9Message GetEntireTable(i9Message RequestMessage) { string TableName = RequestMessage.MsgBody; i9Message response = new i9Message(); string sql = "SELECT * FROM " + TableName; string personnelID = RequestMessage.MessageSecurity.LoginPersonnelID; switch (TableName.ToUpper()) { case "i9SysPersonnel": //Leave out the password from the result set. sql = "SELECT BadgeNumber, i9SysPersonnelID, FirstName, LastName, MiddleName, OfficerORI, Officer FROM i9SysPersonnel"; break; //case "i9SecurityGroupTask": // sql = "SELECT * FROM i9SecurityGroupTask WHERE i9SysPersonnelID = " + SQLUtility.SQLString(personnelID); // break; //case "i9SecurityGroupModule": // sql = "SELECT * FROM i9SecurityGroupModule WHERE i9SysPersonnelID = " + SQLUtility.SQLString(personnelID); // break; default: sql = "SELECT * FROM " + TableName; break; } DataSet ss = new SQLAccess().GetDataSet(sql, TableName); response.MsgBodyDataSet = ss; return response; }
private i9Message DeleteAgency(i9Message requestMessage) { string AgencyID = requestMessage.MsgBody.ToString(); i9Message response = new i9Message(); SQLAccess da = new SQLAccess(); string SQL = "DELETE FROM i9Agency WHERE i9AgencyID = " + SQLUtility.SQLString(AgencyID) + " "; int UpdatedRows = da.ExecuteSQL(SQL); response.MsgBody = UpdatedRows.ToString(); return response; }
public int SaveDataTable(DataTable dt) { int UpdateCount = 0; if (dt.Rows.Count > 0) { string SQL = new SQLGenerator().DataTableSQL(dt); SQLAccess sqla = new SQLAccess(); UpdateCount = sqla.ExecuteSQL(SQL); } return(UpdateCount); }
public static void LogThis(string ErrorMessage, LogEventType LogEvent, string BadgeNumber, string AgencyName) { bool LogMessage = true; switch ( LogEvent) { case LogEventType.Error: break; case LogEventType.Warning: break; case LogEventType.Info: break; case LogEventType.InMessages: LogMessage = false; break; case LogEventType.OutMessages: LogMessage = false; break; case LogEventType.MessagePath: LogMessage = false; break; } if (LogMessage == false) return; try { if (ErrorMessage.Length > 1000) ErrorMessage = ErrorMessage.Substring(0, 999); if (BadgeNumber.Length > 23) BadgeNumber = BadgeNumber.Substring(0, 22); if (AgencyName.Length > 60) AgencyName = AgencyName.Substring(0, 59); AgencyName = ""; string SQL = "INSERT INTO i9SysLog (LogDateTime, LogDescription, LogType, AgencyName, BadgeNumber) VALUES ('" + DateTime.Now.ToString() + "', " + SQLUtility.SQLString(ErrorMessage) + ", " + SQLUtility.SQLString(LogEvent.ToString()) + ", " + SQLUtility.SQLString(AgencyName) + ", " + SQLUtility.SQLString(BadgeNumber) + ")"; SQLAccess da = new SQLAccess(); da.ExecuteSQL(SQL); } catch (Exception ex) { Console.WriteLine(ex.Message); } }
private i9Message GetDetail(i9Message requestMessage) { string AgencyID = requestMessage.MsgBody.ToString(); i9Message response = new i9Message(); SQLAccess da = new SQLAccess(); string SQL = " Select * from i9Agency WHERE i9AgencyID = " + SQLUtility.SQLString(AgencyID) + " order by AgencyName "; Dictionary<string, string> tableMapping = new Dictionary<string, string>() { {"Table", "i9Agency"}, }; DataSet ds = da.GetDataSet(SQL, tableMapping); response.MsgBodyDataSet = ds; return response; }
public static string GetReportNumber(string i9ModuleID) { SQLAccess da = new SQLAccess(); string ReportNumber = ""; //need a transaction StringBuilder SQLReportNumber = new StringBuilder(); SQLReportNumber.AppendLine(" BEGIN TRAN T1 "); SQLReportNumber.AppendLine(" if EXISTS( select * from i9ModuleReportNumber where i9ModuleID = " + SQLUtility.SQLString(i9ModuleID) + " and ResetReportNumber = 'DAY' ) "); SQLReportNumber.AppendLine(" BEGIN "); SQLReportNumber.AppendLine(" if EXISTS( select * from i9ModuleReportNumber where i9ModuleID = " + SQLUtility.SQLString(i9ModuleID) + " and CONVERT(date, LastUpdate) != CONVERT(date, getdate()) ) "); SQLReportNumber.AppendLine(" BEGIN "); SQLReportNumber.AppendLine(" update i9ModuleReportNumber set LastUpdate = GetDate(), ReportNumber = StartNumber WHERE i9ModuleID = " + SQLUtility.SQLString(i9ModuleID) + " "); SQLReportNumber.AppendLine(" END "); SQLReportNumber.AppendLine(" END "); SQLReportNumber.AppendLine(" ELSE "); SQLReportNumber.AppendLine(" BEGIN "); SQLReportNumber.AppendLine(" if EXISTS( select * from i9ModuleReportNumber where i9ModuleID = " + SQLUtility.SQLString(i9ModuleID) + " and year(LastUpdate) != year(getdate()) ) "); SQLReportNumber.AppendLine(" BEGIN "); SQLReportNumber.AppendLine(" update i9ModuleReportNumber set LastUpdate = GetDate(), ReportNumber = StartNumber WHERE i9ModuleID = " + SQLUtility.SQLString(i9ModuleID) + " "); SQLReportNumber.AppendLine(" END "); SQLReportNumber.AppendLine(" END "); SQLReportNumber.AppendLine(" Update i9ModuleReportNumber set ReportNumber = ReportNumber + 1 WHERE i9ModuleID = " + SQLUtility.SQLString(i9ModuleID) + " "); SQLReportNumber.AppendLine(" select * from i9ModuleReportNumber "); SQLReportNumber.AppendLine(" COMMIT TRAN T1 "); string SQL = SQLReportNumber.ToString(); DataTable dt = da.GetDataTable(SQLReportNumber.ToString(), "Results"); if (dt.Rows.Count > 0) { ReportNumber = dt.Rows[0]["ReportNumber"].ToString(); if (DBNull.Value != dt.Rows[0]["NumberPrefix"]) if (String.IsNullOrEmpty(dt.Rows[0]["NumberPrefix"].ToString()) == false) ReportNumber = DateTime.Now.ToString(dt.Rows[0]["NumberPrefix"].ToString()) + ReportNumber; if (DBNull.Value != dt.Rows[0]["NumberSubFix"]) if (String.IsNullOrEmpty(dt.Rows[0]["NumberSubFix"].ToString()) == false) ReportNumber = ReportNumber + DateTime.Now.ToString(dt.Rows[0]["NumberSubFix"].ToString()); } return ReportNumber; }
private i9Message DeleteIncident(i9Message requestMessage) { i9Message response = new i9Message(); SQLAccess da = new SQLAccess(); LawIncidentMessage lim = (LawIncidentMessage)i9Message.XMLDeserializeMessage(typeof(LawIncidentMessage), requestMessage.MsgBody); string i9EventID = lim.i9EventID; string DeleteIncidentSql = " DELETE FROM i9LawIncident WHERE i9EventID = " + SQLUtility.SQLString(i9EventID) + Environment.NewLine + " DELETE FROM i9PersonSMT WHERE i9EventID = " + SQLUtility.SQLString(i9EventID) + Environment.NewLine + " DELETE FROM i9Property WHERE i9EventID = " + SQLUtility.SQLString(i9EventID) + Environment.NewLine + " DELETE FROM i9Narrative WHERE i9EventID = " + SQLUtility.SQLString(i9EventID) + Environment.NewLine + " DELETE FROM i9VehicleRecovery WHERE i9EventID = " + SQLUtility.SQLString(i9EventID) + Environment.NewLine + " DELETE FROM i9VehicleTowed WHERE i9EventID = " + SQLUtility.SQLString(i9EventID) + Environment.NewLine + " DELETE FROM i9AttachmentLink WHERE i9EventID = " + SQLUtility.SQLString(i9EventID) + Environment.NewLine + " DELETE FROM i9Attachment WHERE i9EventID = " + SQLUtility.SQLString(i9EventID) + Environment.NewLine + " DELETE FROM i9AttachmentData WHERE i9EventID = " + SQLUtility.SQLString(i9EventID) + Environment.NewLine + " DELETE FROM i9Person WHERE i9EventID = " + SQLUtility.SQLString(i9EventID) + Environment.NewLine + " DELETE FROM i9Vehicle WHERE i9EventID = " + SQLUtility.SQLString(i9EventID) + Environment.NewLine + " DELETE FROM i9Location WHERE i9EventID = " + SQLUtility.SQLString(i9EventID) + Environment.NewLine + " DELETE FROM i9Event WHERE i9EventID = " + SQLUtility.SQLString(i9EventID) + Environment.NewLine; //" DELETE FROM i9Offense WHERE i9EventID = " + SQLUtility.SQLString(i9EventID) + Environment.NewLine + //" DELETE FROM i9CADServiceCall WHERE i9EventID = " + SQLUtility.SQLString(i9EventID); //Stopwatch stopWatch = new Stopwatch(); //stopWatch.Start(); DeleteIncidentSql = SQLUtility.WrapInTransaction(DeleteIncidentSql); response.MsgBody = da.ExecuteSQL(DeleteIncidentSql).ToString(); //stopWatch.Stop(); //TimeSpan ts = stopWatch.Elapsed; //string elapsedTime = String.Format("{0:00}:{1:00}:{2:00}.{3:00}", //ts.Hours, ts.Minutes, ts.Seconds, //ts.Milliseconds / 10); //Console.WriteLine(elapsedTime); response.ErrorStatus.IsError = false; return response; }
/// <summary> /// This Method is not used anymore because we switched to a GUID keys /// </summary> /// <param name="TableName"></param> /// <returns></returns> public static long GetSingleTableKey(string TableName) { SQLAccess da = new SQLAccess(); long KeyValue = -1; StringBuilder SQLi9Table = new StringBuilder(); SQLi9Table.AppendLine(" BEGIN TRAN T1 "); SQLi9Table.AppendLine(" Update i9TableKey set KeyValue = KeyValue + 1 WHERE TableName = " + SQLUtility.SQLString(TableName) + " "); SQLi9Table.AppendLine(" SELECT KeyValue FROM i9TableKey WHERE TableName = " + SQLUtility.SQLString(TableName) + " "); SQLi9Table.AppendLine(" COMMIT TRAN T1 "); string SQL = SQLi9Table.ToString(); DataTable dt = da.GetDataTable(SQLi9Table.ToString(), "Results"); if (dt.Rows.Count > 0) { if (DBNull.Value != dt.Rows[0]["KeyValue"]) if (String.IsNullOrEmpty(dt.Rows[0]["KeyValue"].ToString()) == false) KeyValue = (long) dt.Rows[0]["KeyValue"] ; } return KeyValue; }
private i9Message Security_PersonnelGroupTaskGet(i9Message requestMessage) { i9Message response = new i9Message(); SQLAccess da = new SQLAccess(); string i9SysPersonnelID = requestMessage.MsgBody; string SQL1 = @" SELECT sgm.ModuleName FROM i9SecurityGroupPersonnel sgp INNER JOIN i9SecurityGroup sg ON sgp.i9SecurityGroupID = sg.i9SecurityGroupID LEFT OUTER JOIN i9SecurityGroupModule sgm ON sgm.SecurityGroupName = sg.SecurityGroupName LEFT OUTER JOIN i9Agency a on a.i9AgencyID = sgp.i9AgencyID INNER JOIN i9SysPersonnel per on per.i9SysPersonnelID = sgp.i9SysPersonnelID WHERE per.i9SysPersonnelID = @i9SysPersonnelID GROUP BY sgm.ModuleName ORDER BY sgm.ModuleName "; SQL1 = SQL1.Replace("@i9SysPersonnelID", SQLUtility.SQLString(i9SysPersonnelID)); string SQL2 = @" SELECT * FROM i9SecurityGroupTask WHERE 1=2"; SQL2 = SQL2.Replace("@i9SysPersonnelID", SQLUtility.SQLString(i9SysPersonnelID)); string SQL = SQL1 + " " + Environment.NewLine + " " + SQL2; Dictionary<string, string> tableMapping = new Dictionary<string, string>() { {"Table", "ModuleName"}, {"Table1", "TaskName"}, }; DataSet ds = da.GetDataSet(SQL, tableMapping); response.MsgBodyDataSet = ds; return response; }
/// <summary> /// Get Next Table Key from Main Database. /// </summary> /// <param name="TableName"></param> /// <returns></returns> public i9Message NextTableKey(i9Message requestMessage) { string TableName = requestMessage.MsgBody; //int KeyValue = 0; i9Message response = new i9Message(); SQLAccess cda = new SQLAccess(); string sql = @" BEGIN TRAN T1 " + " SELECT * FROM i9TableKey WHERE TableName = " + SQLUtility.SQLString(TableName) + " " + Environment.NewLine + " Update i9TableKey Set KeyValue = KeyValue + 1 WHERE TableName = " + SQLUtility.SQLString(TableName) + " " + Environment.NewLine + " COMMIT TRAN T1 "; DataSet ds = cda.GetDataSet(sql, "i9TableKey"); if (ds != null) { //if (ds.Tables.Count > 0) // if (ds.Tables[0].Rows.Count > 0) // KeyValue = Convert.ToInt32(ds.Tables["i9TableKey"].Rows[0]["KeyValue"].ToString()); response.MsgBodyDataSet = ds; return response; } else { throw new Exception("Unable to get the next table key for: " + TableName); } }
/// <summary> /// CodeSaveCodeDetailAdmin /// </summary> /// <param name="requestMessage"></param> /// <returns></returns> private i9Message CodeAdminSaveDetail(i9Message requestMessage) { i9Message ResponseMessage = new i9Message(); try { if (requestMessage.MsgBodyDataSet != null) { if (requestMessage.MsgBodyDataSet.Tables.Count > 0) { SQLAccess da = new SQLAccess(); da.SaveDataTable(requestMessage.MsgBodyDataSet.Tables["i9Code"]); ResponseMessage.ErrorStatus.IsError = false; } } } catch (Exception ex) { ServiceLogManager.LogThis("Error saving dataset", LogEventType.Error, ex, "", ""); ResponseMessage.ErrorStatus.IsError = true; ResponseMessage.ErrorStatus.ErrorMsg = ex.Message; } return ResponseMessage; }
private i9Message SaveIncident(i9Message mMessage) { i9Message response = new i9Message(); DataSet ds = mMessage.MsgBodyDataSet; StringBuilder sbSQL = new StringBuilder(); SQLGenerator SqlGen = new SQLGenerator(); sbSQL.Append(SqlGen.DataTableSQL(ds.Tables["i9Event"])); sbSQL.Append(SqlGen.DataTableSQL(ds.Tables["i9LawIncident"])); sbSQL.Append(SqlGen.DataTableSQL(ds.Tables["i9CADServiceCall"])); sbSQL.Append(SqlGen.DataTableSQL(ds.Tables["i9Offense"])); sbSQL.Append(SqlGen.DataTableSQL(ds.Tables["i9Person"])); sbSQL.Append(SqlGen.DataTableSQL(ds.Tables["i9Vehicle"])); sbSQL.Append(SqlGen.DataTableSQL(ds.Tables["i9Location"])); sbSQL.Append(SqlGen.DataTableSQL(ds.Tables["i9Property"])); sbSQL.Append(SqlGen.DataTableSQL(ds.Tables["i9VehicleRecovery"])); sbSQL.Append(SqlGen.DataTableSQL(ds.Tables["i9VehicleTowed"])); sbSQL.Append(SqlGen.DataTableSQL(ds.Tables["i9Narrative"])); sbSQL.Append(SqlGen.DataTableSQL(ds.Tables["i9PersonAKA"])); sbSQL.Append(SqlGen.DataTableSQL(ds.Tables["i9PersonSMT"])); sbSQL.Append(SqlGen.DataTableSQL(ds.Tables["i9AttachmentLink"])); sbSQL.Append(SqlGen.DataTableSQL(ds.Tables["i9Attachment"])); sbSQL.Append(SqlGen.DataTableSQL(ds.Tables["i9AttachmentData"])); SQLAccess sqla = new SQLAccess(); string SQL = SQLUtility.WrapInTransaction( sbSQL.ToString()); sqla.ExecuteSQL(SQL); return response; }
public i9Message Login(i9Message RequestMessage) { i9Message response = new i9Message(); LoginMessage oLoginMessage = (LoginMessage)i9Message.XMLDeserializeMessage(typeof(LoginMessage), RequestMessage.MsgBody); SQLAccess da = new SQLAccess(); string SQLLogin = "******" + SQLUtility.SQLString(oLoginMessage.UserName) + " and Password = "******"i9SysPersonnel"); if (dt.Rows.Count <= 0) { //Just send back an empty table return response; } string i9SysPersonnelID = dt.Rows[0]["i9SysPersonnelID"].ToString(); //Give an activision code to the login user string UpdateSql = "UPDATE i9SysPersonnel SET ActivationGuid = '" + Guid.NewGuid() + "' WHERE i9SysPersonnelID = " + SQLUtility.SQLString(i9SysPersonnelID); if (da.ExecuteSQL(UpdateSql) <= 0) { //Nothing was updated. } //SyncModuleManagerToDB(); Dictionary<string, string> tableMapping = new Dictionary<string, string>() { {"Table", "i9SysPersonnel"}, {"Table1", "xxSecurityGroupTask"}, {"Table2", "xxSecurityGroupModule"}, }; string SQL = "SELECT * FROM i9SysPersonnel WHERE i9SysPersonnelID = @i9SysPersonnelID"; SQL = SQL + Environment.NewLine + @" SELECT sgt.TaskName, sp.BadgeNumber FROM i9SecurityGroup sg inner join i9SecurityGroupTask sgt on sgt.SecurityGroupName = sg.SecurityGroupName inner join i9SecurityGroupPersonnel sgp on sgp.i9SecurityGroupID = sg.i9SecurityGroupID inner join i9SysPersonnel sp on sp.i9SysPersonnelID = sgp.i9SysPersonnelID WHERE sp.i9SysPersonnelID = @i9SysPersonnelID Group by TaskName, BadgeNumber order by BadgeNumber, TaskName "; SQL = SQL + Environment.NewLine + @" SELECT sgm.ModuleName, sp.BadgeNumber FROM i9SecurityGroup sg inner join i9SecurityGroupModule sgm on sgm.SecurityGroupName = sg.SecurityGroupName inner join i9SecurityGroupPersonnel sgp on sgp.i9SecurityGroupID = sg.i9SecurityGroupID inner join i9SysPersonnel sp on sp.i9SysPersonnelID = sgp.i9SysPersonnelID WHERE sp.i9SysPersonnelID = @i9SysPersonnelID Group by ModuleName, BadgeNumber order by BadgeNumber, ModuleName "; SQL = SQL.Replace("@i9SysPersonnelID", SQLUtility.SQLString(i9SysPersonnelID) ); response.MsgBodyDataSet = response.MsgBodyDataSet = da.GetDataSet(SQL, tableMapping); response.ErrorStatus.IsError = false; return response; }
/// <summary> /// GetDynamicEntryAdmin /// </summary> /// <param name="mMessage"></param> /// <returns></returns> private i9Message GetDynamicEntryAdmin(i9Message mMessage) { SQLAccess da = new SQLAccess(); i9Message response = new i9Message(); string SQLLogin = "******" + Environment.NewLine + " SELECT * FROM i9DynamicEntryConfig " + Environment.NewLine + " select * from i9DynamicEntryRule " + Environment.NewLine; Dictionary<string, string> tableMapping = new Dictionary<string, string>() { {"Table", "i9DynamicEntry"}, {"Table1", "i9DynamicEntryConfig"}, {"Table2", "i9DynamicEntryRule"}, }; response.MsgBodyDataSet = da.GetDataSet(SQLLogin, tableMapping); response.ErrorStatus.IsError = false; return response; }
/// <summary> /// Sync Module Manager To DB /// </summary> private void SyncModuleManagerToDB() { SQLAccess da = new SQLAccess(); //DataTable dt = da.GetDataTable("SELECT * FROM i9Module WHERE ModuleKey = " + SQLUtility.SQLString(mi.i9ModuleID), "i9Module"); DataTable dt = da.GetDataTable("SELECT * FROM i9Module", "i9Module"); for (int i = 0; i < ModuleManager.Instance.Modules.Length - 1; i++) { ModuleItem mi = ModuleManager.Instance.Modules[i]; bool found = false; if (mi.ModuleName.ToUpper() == "CRIME WATCH") { Console.Write(""); } if (mi.ModuleName.ToUpper() == "LOGIN" || mi.ModuleName.ToUpper() == "MAIN" || mi.ModuleName.ToUpper() == "MAIN MENU" || mi.ModuleName.ToUpper() == "SPLASH SCREEN") { continue; } else { foreach (DataRow ModuleRow in dt.Rows) { if (ModuleRow["ModuleName"].ToString().ToUpper() == mi.ModuleName.ToUpper()) { found = true; break; } } if (found == false) { //Why am I adding a row if the table is blank this is strange DataRow NewModuleRow = dt.NewRow(); NewModuleRow["ClassName"] = mi.ClassName; NewModuleRow["ModuleName"] = mi.ModuleName; NewModuleRow["Section"] = mi.Section; NewModuleRow["PopupPage"] = mi.PopupPage; NewModuleRow["DesktopEnabled"] = mi.DesktopEnabled; NewModuleRow["MobileEnabled"] = mi.MobileEnabled; NewModuleRow["ModuleType"] = mi.ModuleType; NewModuleRow["ModuleKey"] = mi.i9ModuleID; NewModuleRow["i9ModuleID"] = Guid.NewGuid(); NewModuleRow["FileName"] = mi.FileName; dt.Rows.Add(NewModuleRow); } } } if (dt.DataSet.HasChanges()) { da.SaveDataTable(dt); } }
private i9Message LogGetTop100(i9Message requestMessage) { i9Message response = new i9Message(); SQLAccess da = new SQLAccess(); string SQL = " SELECT TOP 500 * FROM i9SysLog ORDER BY LogDateTime DESC " + Environment.NewLine; Dictionary<string, string> tableMapping = new Dictionary<string, string>() { {"Table", "i9SysLog"} }; DataSet ds = da.GetDataSet(SQL, tableMapping); response.MsgBodyDataSet = ds; return response; }
public int SaveDataTable(DataTable dt) { int UpdateCount = 0; if (dt.Rows.Count > 0) { string SQL = new SQLGenerator().DataTableSQL(dt); SQLAccess sqla = new SQLAccess(); UpdateCount = sqla.ExecuteSQL(SQL); } return UpdateCount; }
public int SaveDataDataset(DataSet ds) { string SQL = new SQLGenerator().DataSetSQL(ds); SQLAccess sqla = new SQLAccess(); return sqla.ExecuteSQL(SQL); }
private i9Message NewIncident(i9Message requestMessage) { i9Message response = new i9Message(); SQLAccess da = new SQLAccess(); string SQLIncident = " SELECT * FROM i9Event WHERE 1=2 " + Environment.NewLine + " SELECT * FROM i9LawIncident WHERE 1=2 " + Environment.NewLine + " SELECT * FROM i9Person WHERE 1=2 " + Environment.NewLine + " SELECT * FROM i9Vehicle WHERE 1=2 " + Environment.NewLine + " SELECT * FROM i9Location WHERE 1=2 " + Environment.NewLine + " SELECT * FROM i9Property WHERE 1=2 " + Environment.NewLine + " SELECT * FROM i9Offense WHERE 1=2 " + Environment.NewLine + " SELECT * FROM i9CADServiceCall WHERE 1=2" + Environment.NewLine + " SELECT * FROM i9AttachmentLink WHERE 1=2" + Environment.NewLine + " SELECT * FROM i9Attachment WHERE 1=2" + Environment.NewLine + " SELECT * FROM i9AttachmentData WHERE 1=2" + Environment.NewLine + " SELECT * FROM i9VehicleRecovery WHERE 1=2" + Environment.NewLine + " SELECT * FROM i9VehicleTowed WHERE 1=2" + Environment.NewLine + " SELECT * FROM i9Narrative WHERE 1=2" + Environment.NewLine + " SELECT * FROM i9Person WHERE 1=2" + Environment.NewLine + " SELECT * FROM i9PersonSMT WHERE 1=2" + Environment.NewLine; Dictionary<string, string> tableMapping = new Dictionary<string, string>() { {"Table", "i9Event"}, {"Table1", "i9LawIncident"}, {"Table2", "i9Person"}, {"Table3", "i9Vehicle"}, {"Table4", "i9Location"}, {"Table5", "i9Property"}, {"Table6", "i9Offense"}, {"Table7", "i9CADServiceCall"}, {"Table8", "i9AttachmentLink"}, {"Table9", "i9Attachment"}, {"Table10", "i9AttachmentData"}, {"Table11", "i9VehicleRecovery"}, {"Table12", "i9VehicleTowed"}, {"Table13", "i9Narrative"}, {"Table14", "i9PersonAKA"}, {"Table15", "i9PersonSMT"} }; response.MsgBodyDataSet = da.GetDataSet(SQLIncident, tableMapping); //*************************************************************************** //* Adding standard table rows - need to move this to the common dll //*************************************************************************** //Adding Event Information: DataRow dri9Event = response.MsgBodyDataSet.Tables["i9Event"].NewRow(); dri9Event["i9EventID"] = ServerKeyManager.NewGuid(); dri9Event["i9EventType"] = "Incident"; dri9Event["i9AgencyID"] = requestMessage.MessageSecurity.AgencyID; response.MsgBodyDataSet.Tables["i9Event"].Rows.Add(dri9Event); //Adding Incident Information: DataRow dr = response.MsgBodyDataSet.Tables["i9LawIncident"].NewRow(); dr["i9LawIncidentID"] = ServerKeyManager.NewGuid(); dr["i9EventID"] = dri9Event["i9EventID"]; dr["IncidentNumber"] = ReportNumberManager.GetReportNumber("Incident"); dr["SupplementNumber"] = 1; dr["i9AgencyID"] = requestMessage.MessageSecurity.AgencyID; //This is the Login user agency (default Agency) response.MsgBodyDataSet.Tables["i9LawIncident"].Rows.Add(dr); //Add location incident dr = response.MsgBodyDataSet.Tables["i9Location"].NewRow(); dr["i9EventID"] = dri9Event["i9EventID"]; dr["i9AgencyID"] = requestMessage.MessageSecurity.AgencyID; dr["i9LocationID"] = Guid.NewGuid(); dr["i9ModuleSectionID"] = i9ModuleSection.LawIncidentLocation.ToString(); dr["LocationMVI"] = 0; response.MsgBodyDataSet.Tables["i9Location"].Rows.Add(dr); //Add i9Narrative to law incident dr = response.MsgBodyDataSet.Tables["i9Narrative"].NewRow(); dr["i9EventID"] = dri9Event["i9EventID"]; dr["i9NarrativeID"] = Guid.NewGuid(); response.MsgBodyDataSet.Tables["i9Narrative"].Rows.Add(dr); //========================================================================= //Relations //========================================================================= response.MsgBodyDataSet.Relations.Add("PersonToLocation", response.MsgBodyDataSet.Tables["i9Person"].Columns["i9PersonID"], response.MsgBodyDataSet.Tables["i9Location"].Columns["i9PersonID"]); //response.MsgBodyDataSet.Relations.Add("PersonLocationToPerson", // response.MsgBodyDataSet.Tables["i9PersonLocation"].Columns["i9PersonID"], // response.MsgBodyDataSet.Tables["i9Person"].Columns["i9PersonID"]); //response.MsgBodyDataSet.Relations.Add("PersonLocationToLocation", // response.MsgBodyDataSet.Tables["i9PersonLocation"].Columns["i9LocationID"], // response.MsgBodyDataSet.Tables["i9Location"].Columns["i9LocationID"]); //========================================================================= //DO NOT EXCEPT CHANGES TO THE DataSet //========================================================================= //Because I will not know if the row is new or old. //response.MsgBodyDataSet.AcceptChanges(); response.ErrorStatus.IsError = false; return response; }
/// <summary> /// CodeGetCodeListAdmin /// </summary> /// <param name="requestMessage"></param> /// <returns></returns> private i9Message CodeGetCodeListAdmin(i9Message requestMessage) { //------------------------------------------------------------------- //Testing //SELECT CodeName, CodeValue FROM i9Code group by CodeName, CodeValue having count (*) > 1 order by CodeName //------------------------------------------------------------------- i9Message response = new i9Message(); SQLAccess da = new SQLAccess(); string SQL = //" Select * FROM i9Code order by CodeSetName "+ Environment.NewLine + " Select CodeSetName, i9AgencyID FROM i9Code Group By CodeSetName, i9AgencyID order by CodeSetName " + Environment.NewLine + " Select * from i9Agency order by AgencyName "; Dictionary<string, string> tableMapping = new Dictionary<string, string>() { //{"Table", "i9Code"}, {"Table", "i9Code"}, {"Table1", "i9Agency"}, }; DataSet ds = da.GetDataSet(SQL, tableMapping); response.MsgBodyDataSet = ds; return response; }
private i9Message PersonnelGetList(i9Message requestMessage) { i9Message response = new i9Message(); SQLAccess da = new SQLAccess(); string SQL = " Select LastName +', ' + FirstName + ' - ' + BadgeNumber as DisplayName, * FROM i9SysPersonnel order by DisplayName " + Environment.NewLine + " Select * from i9Agency order by AgencyName "; Dictionary<string, string> tableMapping = new Dictionary<string, string>() { {"Table", "i9SysPersonnel"}, {"Table1", "i9Agency"}, }; DataSet ds = da.GetDataSet(SQL, tableMapping); response.MsgBodyDataSet = ds; return response; }
private i9Message PersonnelDelete(i9Message requestMessage) { i9Message response = new i9Message(); string i9SysPersonnelID = requestMessage.MsgBody; SQLAccess da = new SQLAccess(); string SQL = " BEGIN TRAN T1 " + " DELETE FROM i9SysPersonnelPhone WHERE i9SysPersonnelID = " + SQLUtility.SQLString(i9SysPersonnelID) + " " + Environment.NewLine + " DELETE FROM i9SysPersonnelAddress WHERE i9SysPersonnelID = " + SQLUtility.SQLString(i9SysPersonnelID) + " " + Environment.NewLine + " DELETE FROM i9SysPersonnelAssignment WHERE i9SysPersonnelID = " + SQLUtility.SQLString(i9SysPersonnelID) + " " + Environment.NewLine + " DELETE FROM i9SysPersonnel WHERE i9SysPersonnelID = " + SQLUtility.SQLString(i9SysPersonnelID) + " " + Environment.NewLine + " COMMIT TRAN T1 "; int UpdateCount = da.ExecuteSQL(SQL); // should check the count. if zero then that is bad response.MsgBody = UpdateCount.ToString(); return response; }
/// <summary> /// DynamicEntry_GetTableColumns /// </summary> /// <param name="mMessage"></param> /// <returns></returns> private i9Message DynamicEntry_GetTableColumns(i9Message mMessage) { SQLAccess da = new SQLAccess(); i9Message response = new i9Message(); string ModuleSection = mMessage.MsgBody.Split(new Char[] {','})[0]; string TableName = mMessage.MsgBody.Split(new Char[] { ',' })[1]; string sql = " EXEC TableSchema_Get '" + TableName + "' " + Environment.NewLine ; //" SELECT * FROM i9DynamicEntryConfig where ModuleSection = '" + ModuleSection + "' " + Environment.NewLine + //" SELECT * FROM i9DynamicEntryRule der, i9DynamicEntryConfig dec WHERE der.i9DynanicEntryConfigID = dec.i9DynanicEntryConfigID AND dec.ModuleSection = '" + ModuleSection + "' " + Environment.NewLine; Dictionary<string, string> tableMapping = new Dictionary<string, string>() { {"Table", "i9TableSchema"}, //{"Table1", "i9DynamicEntryConfig"}, //{"Table2", "i9DynamicEntryRule"}, }; response.MsgBodyDataSet = da.GetDataSet(sql, tableMapping); response.ErrorStatus.IsError = false; return response; }
private i9Message GetSyncData(string LatChanged) { i9Message response = new i9Message(); string sql = ""; int TableCounter = 0; List<string> tables = DataAccessUtilities.SyncTableNames(); Dictionary<string, string> tableMapping = new Dictionary<string, string>(); tables.ForEach(delegate(String TableName) { sql += " SELECT * FROM " + TableName + " WHERE LastUpdate >= " + SQLUtility.SQLString(LatChanged.Trim()) + " or LastUpdate is null " + Environment.NewLine; tableMapping.Add("Table" + (TableCounter == 0 ? "" : TableCounter.ToString()), TableName); TableCounter++; }); DataSet ds = new SQLAccess().GetDataSet(sql, tableMapping); response.MsgBodyDataSet = ds; return response; }
private i9Message SaveDynamicEntryAdmin(i9Message mRequestMessage) { SQLAccess da = new SQLAccess(); i9Message response = new i9Message(); da.SaveDataTable(mRequestMessage.MsgBodyDataSet.Tables["i9DynamicEntryConfig"]); return response; }
private i9Message PersonnelGet(i9Message requestMessage) { i9Message response = new i9Message(); string i9SysPersonnelID = requestMessage.MsgBody; SQLAccess da = new SQLAccess(); string SQL = " Select * FROM i9SysPersonnel where i9SysPersonnelID = " + SQLUtility.SQLString(i9SysPersonnelID) + " " + Environment.NewLine + " Select * from i9SysPersonnelAddress where i9SysPersonnelID = " + SQLUtility.SQLString(i9SysPersonnelID) + " " + Environment.NewLine + " Select * from i9SysPersonnelAssignment where i9SysPersonnelID = " + SQLUtility.SQLString(i9SysPersonnelID) + " " + Environment.NewLine + " Select * from i9SysPersonnelPhone where i9SysPersonnelID = " + SQLUtility.SQLString(i9SysPersonnelID); Dictionary<string, string> tableMapping = new Dictionary<string, string>() { {"Table", "i9SysPersonnel"}, {"Table1", "i9SysPersonnelAddress"}, {"Table2", "i9SysPersonnelAssignment"}, {"Table3", "i9SysPersonnelPhone"} }; DataSet ds = da.GetDataSet(SQL, tableMapping); response.MsgBodyDataSet = ds; return response; }
private i9Message CreateUser(i9Message requestMessage) { i9Message response = new i9Message(); string CodeSetName = requestMessage.MsgBody; CreateUserMessage oCreateUserMessage = (CreateUserMessage)i9Message.XMLDeserializeMessage(typeof(CreateUserMessage), requestMessage.MsgBody); SQLAccess da = new SQLAccess(); string SQL = " Select * FROM i9SysPersonnel WHERE Email = " + SQLUtility.SQLString(oCreateUserMessage.Email) + " "; Dictionary<string, string> tableMapping = new Dictionary<string, string>() { {"Table", "i9SysPersonnel"} }; DataSet ds = da.GetDataSet(SQL, tableMapping); if (ds.Tables["i9SysPersonnel"].Rows.Count > 0) { response.ErrorStatus.ErrorMsg = "Email already exists in system"; response.ErrorStatus.IsError = true; return response; } else { DataRow dr = ds.Tables["i9SysPersonnel"].NewRow(); dr["FirstName"] = oCreateUserMessage.FirstName; dr["LastName"] = oCreateUserMessage.LastName; dr["DateTimeInserted"] = DateTime.Now; dr["DateTimeUpdated"] = DateTime.Now; dr["Password"] = oCreateUserMessage.Password; dr["Email"] = oCreateUserMessage.Email; dr["BadgeNumber"] = oCreateUserMessage.Email; dr["i9SysPersonnelID"] = Guid.NewGuid(); dr["ActivationGuid"] = Guid.NewGuid(); dr["Enabled"] = 1; dr["DemoUser"] = 1; dr["OfficerORI"] = ""; dr["Officer"] = oCreateUserMessage.FirstName + ", " + oCreateUserMessage.LastName; if (oCreateUserMessage.i9AgencyID == Guid.Empty) { dr["i9AgencyID"] = "53A05F38-FC9C-4260-B939-CB1F3998C4D4"; } else { dr["i9AgencyID"] = oCreateUserMessage.i9AgencyID; } ds.Tables["i9SysPersonnel"].Rows.Add(dr); da.SaveDataTable(ds.Tables["i9SysPersonnel"]); //=============================================================== // Send Email Confirmation //=============================================================== //string EmailMessage = ""; //string EmailSubject = "Invert911 Activation Confirmation"; //string EmailFrom = "*****@*****.**"; //string EmailTo = oCreateUserMessage.Email; //EmailUtility.SendEmail(EmailTo,EmailFrom,EmailSubject, EmailMessage); } response.ErrorStatus.IsError = false; return response; }
/// <summary> /// CodeGetCodeDetailAdmin /// </summary> /// <param name="requestMessage"></param> /// <returns></returns> private i9Message CodeAdminGetDetail(i9Message requestMessage) { i9Message response = new i9Message(); string CodeSetName = requestMessage.MsgBody; SQLAccess da = new SQLAccess(); string SQL = " Select * FROM i9Code where CodeSetName = " + SQLUtility.SQLString(CodeSetName) + " order by CodeText "; Dictionary<string, string> tableMapping = new Dictionary<string, string>() { {"Table", "i9Code"} }; DataSet ds = da.GetDataSet(SQL, tableMapping); response.MsgBodyDataSet = ds; return response; }
private i9Message PersonnelAdd(i9Message requestMessage) { i9Message response = new i9Message(); SQLAccess da = new SQLAccess(); string BlankSQL = " Select * FROM i9SysPersonnel where 1 = 2 "; Dictionary<string, string> tableMapping = new Dictionary<string, string>() { {"Table", "i9SysPersonnel"} }; DataSet ds = da.GetDataSet(BlankSQL, tableMapping); DataTable PersonDT = ds.Tables["i9SysPersonnel"]; DataRow NewRD = PersonDT.NewRow(); NewRD["i9SysPersonnelID"] = Guid.NewGuid(); NewRD["FirstName"] = "@NewFirst"; NewRD["LastName"] = "@NewLast " + DateTime.Now.ToString("MM/dd/yyyy hh:mm"); NewRD["i9AgencyID"] = requestMessage.MsgBody.ToString(); NewRD["BadgeNumber"] = DateTime.Now.ToString("MMddyyyyhhmm"); NewRD["Enabled"] = 1; PersonDT.Rows.Add(NewRD); string i9SysPersonnelID = NewRD["i9SysPersonnelID"].ToString(); if (da.SaveDataTable(ds.Tables["i9SysPersonnel"]) <= 0) { //unable to save new personnel response.MsgBody = ""; response.ErrorStatus.IsError = true; response.ErrorStatus.ErrorMsg = "Unable to add new user"; return response; } requestMessage.ToBizLayerMsgType = AdminType.SysPer_PersonGet; requestMessage.MsgBody = i9SysPersonnelID; return PersonnelGet(requestMessage); }
private i9Message SearchIncident(i9Message mMessage) { i9Message response = new i9Message(); SQLAccess da = new SQLAccess(); string SQLIncident = " SELECT i.i9LawIncidentID, i.i9EventID, i.IncidentNumber, i.SupplementNumber, i.StatusCode, i.IncidentReportDate, i.ORI, 'N3,V1,P4', a.AgencyName " + " FROM i9LawIncident i join i9Agency a on i.i9AgencyID = a.i9AgencyID "; response.MsgBodyDataSet = da.GetDataSet(SQLIncident, "i9LawIncident"); return response; }
public static void LogThis(string ErrorMessage, LogEventType LogEvent, string BadgeNumber, string AgencyName) { bool LogMessage = true; switch (LogEvent) { case LogEventType.Error: break; case LogEventType.Warning: break; case LogEventType.Info: break; case LogEventType.InMessages: LogMessage = false; break; case LogEventType.OutMessages: LogMessage = false; break; case LogEventType.MessagePath: LogMessage = false; break; } if (LogMessage == false) { return; } try { if (ErrorMessage.Length > 1000) { ErrorMessage = ErrorMessage.Substring(0, 999); } if (BadgeNumber.Length > 23) { BadgeNumber = BadgeNumber.Substring(0, 22); } if (AgencyName.Length > 60) { AgencyName = AgencyName.Substring(0, 59); } AgencyName = ""; string SQL = "INSERT INTO i9SysLog (LogDateTime, LogDescription, LogType, AgencyName, BadgeNumber) VALUES ('" + DateTime.Now.ToString() + "', " + SQLUtility.SQLString(ErrorMessage) + ", " + SQLUtility.SQLString(LogEvent.ToString()) + ", " + SQLUtility.SQLString(AgencyName) + ", " + SQLUtility.SQLString(BadgeNumber) + ")"; SQLAccess da = new SQLAccess(); da.ExecuteSQL(SQL); } catch (Exception ex) { Console.WriteLine(ex.Message); } }
private i9Message PersonnelSave(i9Message requestMessage) { i9Message ResponseMessage = new i9Message(); try { if (requestMessage.MsgBodyDataSet != null) { if (requestMessage.MsgBodyDataSet.Tables.Count > 0) { SQLAccess da = new SQLAccess(); //TODO: Need to wrap this in a transaction da.SaveDataTable(requestMessage.MsgBodyDataSet.Tables["i9SysPersonnel"]); da.SaveDataTable(requestMessage.MsgBodyDataSet.Tables["i9SysPersonnelAddress"]); da.SaveDataTable(requestMessage.MsgBodyDataSet.Tables["i9SysPersonnelAssignment"]); da.SaveDataTable(requestMessage.MsgBodyDataSet.Tables["i9SysPersonnelPhone"]); ResponseMessage.ErrorStatus.IsError = false; } } } catch (Exception ex) { ServiceLogManager.LogThis("Error saving dataset", LogEventType.Error, ex, "", ""); ResponseMessage.ErrorStatus.IsError = true; ResponseMessage.ErrorStatus.ErrorMsg = ex.Message; } return ResponseMessage; }