}//END StoreProcUpdate method // ===================================================================================== /// <summary> /// public QueryUpdate method /// /// Execute a SqlCommand (that returns no resultset) against the database specified in the connection string /// using a stored procedure and the provided parameters. /// </summary> /// <remarks> /// e.g.: /// int result = StoreProcUpdate("PublishOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="UpdateCommand">the name of the stored procedure to be used.)</param> /// <param name="CommandParameters">an array of SqlParamters used to execute the groupCommand</param> /// <returns>an int representing the number of rows affected by the groupCommand</returns> // ------------------------------------------------------------------------------------- public static int QueryUpdate(string UpdateCommand, params SqlParameter [] CommandParameters) { // // Initialise method variables and objects. // int iResult = 0; using (SqlConnection conn = new SqlConnection(_connectionString)) { conn.Open( ); using (SqlTransaction trans = conn.BeginTransaction( )) { try { iResult = EvSqlMethods.ExecuteNonQuery( trans, CommandType.Text, UpdateCommand, CommandParameters); trans.Commit( ); } catch (Exception Ex) { trans.Rollback( ); string eventMessage = "Status: + " + EvSqlMethods.Status + "\r\n Connection String" + _connectionString + "\r\n Query: \r\n" + UpdateCommand + "\r\n Exception: \r\n" + Evado.Model.EvStatics.getException(Ex); Evado.Model.EvStatics.WriteToEventLog(_eventLogSource, eventMessage, EventLogEntryType.Error); throw (Ex); } } return(iResult); } }//END QueryUpdate method
}//END getBoolean static method //++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ #endregion #region Reader column count methods /* * // ===================================================================================== * /// <summary> * /// public getGuid static method * /// * /// Description: * /// Reads the row object and returns the newField name as a Gu * /// </summary> * /// <param name="Row">OdbcDataReader</param> * /// <param name="_id. * /// Document">Document</param> * // ------------------------------------------------------------------------------------- * public static DocumentGuid getGuid ( SqlDataReader Reader, int Column ) * { * if ( Reader [ Column ] != null ) * { * string sValue = Reader [ Column ].ToString( ).Trim( ); * if ( sValue.Length == 36 ) * { * return new DocumentGuid( sValue ); * } * } * * return DocumentGuid.Empty; * * }//END getGuid static method * * // ===================================================================================== * /// <summary> * /// public getInteger static method * /// * /// Description: * /// Reads the row object and returns the newField name as a Integer. * /// * /// </summary> * /// <param name="Row">OdbcDataReader</param> * /// <param name="document">Document</param> * // ------------------------------------------------------------------------------------- * public static int getInteger ( SqlDataReader Reader, int Column ) * { * int iValue = 0; * * if ( Reader [ Column ] != null ) * { * if ( int.TryParse( Reader [ Column ].ToString( ), out iValue ) == false ) * { * return 0; * } * } * * return iValue; * * }//END getInteger static method * * // ===================================================================================== * /// <summary> * /// public getDateTime static method * /// * /// Description: * /// Reads the row object and returns the newField name as a DateTime object. * /// * /// </summary> * /// <param name="Row">OdbcDataReader</param> * /// <param name="document">Document</param> * // ------------------------------------------------------------------------------------- * public static DateTime getDateTime ( SqlDataReader Reader, int Column ) * { * DateTime dValue = DateTime.Parse( "1 Jan 1900" ); * * if ( Reader [ Column ] != null ) * { * if ( DateTime.TryParse( Reader [ Column ].ToString( ), out dValue ) == false ) * { * return DateTime.Parse( "1 Jan 1900" ); * } * } * * return dValue; * * }//END getDateTime static method * * // ===================================================================================== * /// <summary> * /// public getString static method * /// * /// Description: * /// Reads the row object and returns the newField name as a string. * /// * /// </summary> * /// <param name="Row">OdbcDataReader</param> * /// <param name="document">Document</param> * // ------------------------------------------------------------------------------------- * public static string getString ( SqlDataReader Reader, int Column ) * { * if ( Reader [ Column ] != null ) * { * return Reader [ Column ].ToString( ).Trim( ); * } * return String.Empty; * * }//END getString static method * * // ===================================================================================== * /// <summary> * /// public getFloat static method * /// * /// Description: * /// Reads the row object and returns the newField name as a FormUid. * /// * /// </summary> * /// <param name="Row">OdbcDataReader</param> * /// <param name="document">Document</param> * // ------------------------------------------------------------------------------------- * public static float getFloat ( SqlDataReader Reader, int Column ) * { * float fValue = 0 ; * * if ( Reader [ Column ] != null ) * { * if ( float.TryParse( Reader [ Column ].ToString( ), out fValue ) == false ) * { * return 0 ; * } * } * * return fValue; * * }//END getFloat static method */ //++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ #endregion //++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ #endregion #region Update Methods // ===================================================================================== /// <summary> /// public StoreProcUpdate method /// /// Execute a SqlCommand (that returns no resultset) against the database specified in the connection string /// using a stored procedure and the provided parameters. /// </summary> /// <remarks> /// e.g.: /// int result = StoreProcUpdate("PublishOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="StoreProcedureName">the name of the stored procedure to be used.)</param> /// <param name="CommandParameters">an array of SqlParamters used to execute the groupCommand</param> /// <returns>an int representing the number of rows affected by the groupCommand</returns> // ------------------------------------------------------------------------------------- public static int StoreProcUpdate(string StoreProcedureName, params SqlParameter [] CommandParameters) { int iResult = 0; using (SqlConnection conn = new SqlConnection(_connectionString)) { conn.Open( ); using (SqlTransaction trans = conn.BeginTransaction( )) { try { iResult = EvSqlMethods.ExecuteNonQuery( trans, CommandType.StoredProcedure, StoreProcedureName, CommandParameters); trans.Commit( ); } catch (Exception Ex) { trans.Rollback( ); EventLog.WriteEntry(_eventLogSource, Ex.Message.ToString( ), EventLogEntryType.Error); throw (Ex); } } return(iResult); } }//END StoreProcUpdate method
}//END UpdateItem method // ===================================================================================== /// <summary> /// This method adds a record to the ULD_REGISTERED_DEVICES tables. /// </summary> /// <param name="Device">Object: A Device object</param> /// <returns>Object: Evado.Model.EvEventCodes </returns> /// <remarks> /// This method consists of following steps. /// /// 1. Define the local variables. /// /// 2. Define the GUID for the device of one is not allocated. /// /// 3. Define the query parameters and load the query values. /// /// 4. Define the add record update query. /// /// 5. Execute the update groupCommand. /// /// </remarks> // ------------------------------------------------------------------------------------- private Evado.Model.EvEventCodes AddItem(Device Device) { // // Define the local variables. // this._DebugLog += "Evado.Digital.WebService.Dal.Services.addItem method. DeviceId: " + Device.DeviceId + "\r\n"; Device oldDevice = this.GetItem(Device.DeviceId); if (oldDevice.Identifier != Guid.Empty) { this._DebugLog += "\r\n Duplicate device"; } // // Define the GUID for the device of one is not allocated. // if (Device.Identifier == Guid.Empty) { Device.Identifier = Guid.NewGuid( ); } this._DebugLog += "\r\n Adding Device."; // // Define the SQL query parameters and load the query values. // SqlParameter [] cmdParms = getItemsParameters( ); setUsersParameters(cmdParms, Device); // // Define the add record update query // String addQuery = "Insert Into ULD_REGISTERED_DEVICES \r\n" + " (ULD_IDENTIFIER, USER_ID, SERVICE_ID, DEVICE_ID, ULD_DEVICE_NAME, ULD_DEVICE_OS, ULD_REGISTRATION_DATE ) \r\n" + "values \r\n" + " (@IDENTIFIER, @USER_ID, @SERVICE_ID, @DEVICE_ID, @DEVICE_NAME, @DEVICE_OS, @REGISTRATION_DATE);"; this._DebugLog += "\r\n " + addQuery; // // Execute the update groupCommand. // if (EvSqlMethods.QueryUpdate(addQuery, cmdParms) == 0) { return(Evado.Model.EvEventCodes.Database_Record_Update_Error); } return(Evado.Model.EvEventCodes.Ok); } //END AddItem method
} //END saveItem method // ===================================================================================== /// <summary> /// This method updates the Device table. /// </summary> /// <param name="MobileDevice">Object: A Device object</param> /// <returns>Object: Evado.Model.EvEventCodes </returns> /// <remarks> /// This method consists of following steps. /// /// 1. Define the local variables. /// /// 2. Get the old device id for update. if old device is not found, return an error messege. /// /// 3. Define the SQL query parameters and load the query values. /// /// 4. Define the update record query. /// /// 5. Execute the update groupCommand. /// /// </remarks> // ------------------------------------------------------------------------------------- private Evado.Model.EvEventCodes UpdateItem(Device MobileDevice) { this._DebugLog += "Evado.Digital.WebService.Dal.Services.UpdateItem method. " + " DeviceId: " + MobileDevice.DeviceId + "\r\n"; // // Define the local variables. // Device oldDevice = new Device( ); // // Get the old device id for to verify that the device exists and instrument differential // comparision. // oldDevice = this.GetItem(MobileDevice.DeviceId); if (oldDevice.Identifier == Guid.Empty) { return(Evado.Model.EvEventCodes.Data_InvalidId_Error); } // // Define the SQL query parameters and load the query values. // SqlParameter [] cmdParms = getItemsParameters( ); setUsersParameters(cmdParms, MobileDevice); // // Define the update record query // String addQuery = "UPDATE ULD_REGISTERED_DEVICES \r\n" + "SET\r\n" + " ULD_DEVICE_NAME = @ULD_DEVICE_NAME, \r\n" + " ULD_DEVICE_OS = @ULD_DEVICE_OS, \r\n" + " UR_REGISTRATION_DATE = @REGISTRATION_DATE \r\n" + " WHERE UR_IDENTIFIER = @IDENTIFIER;"; // // Execute the update groupCommand. // if (EvSqlMethods.QueryUpdate(addQuery, cmdParms) == 0) { return(Evado.Model.EvEventCodes.Database_Record_Update_Error); } return(Evado.Model.EvEventCodes.Ok); }//END UpdateItem method
}//END setUsersParameters. //++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ #endregion #region Data Reader methods // ===================================================================================== /// <summary> /// This method reads the content of the SqlDataReader into the Facility data object. /// </summary> /// <param name="Row">Object: Represents a row of data in a System.Data.DataTable.</param> /// <returns>A Device Object</returns> /// <remarks> /// This method consists of following stpes. /// /// 1. Initialise the local varaible. /// /// 2. Update the object properties. /// /// 3. Retrun the device object. /// /// </remarks> // ------------------------------------------------------------------------------------- public Device readRow(DataRow Row) { // // Initialise the local variable. // Device device = new Device( ); // // Update the object properties. // device.Identifier = EvSqlMethods.getGuid(Row, "ULD_IDENTIFIER"); device.ServiceId = EvSqlMethods.getString(Row, "SERVICE_ID"); device.ServiceId = EvSqlMethods.getString(Row, "USER_ID"); device.DeviceId = EvSqlMethods.getString(Row, "DEVICE_ID"); device.DeviceName = EvSqlMethods.getString(Row, "ULD_DEVICE_NAME"); device.DeviceOs = EvSqlMethods.getString(Row, "ULD_DEVICE_OS"); device.RegistrationDate = EvSqlMethods.getDateTime(Row, "ULD_REGISTRATION_DATE"); // // Retrun the device object. // return(device); }// End readRow method.
}//END GetItem method // ===================================================================================== /// <summary> /// This method gets the information for a device. /// </summary> /// <param name="Identifier">GUID: An Identifier for a device</param> /// <returns>Object: A Device Data object</returns> /// <remarks> /// This method consists of following steps. /// /// 1. Define local variables. /// /// 2. Check that the identifier Id is valid or not. if identifier is equal to a empty GUID, return a device object. /// /// 3. Define the SQL query parameters and load the query values. /// /// 4. Generate the SQL query string. /// /// 5. Execute the query against the database. /// /// 6. If no rows found, return a device object. /// /// 7. Extract the table row. /// /// 8. Fill the role object. /// /// 9. Return the device data object. /// /// </remarks> // ------------------------------------------------------------------------------------- public Device GetItem(Guid Identifier) { this._DebugLog += "Evado.Digital.WebService.Dal.Services.GetItem method Identifier: " + Identifier; // // Define local variables // string sqlQueryString; Device userProfile = new Device( ); // // Check that the identifier Id is valid or not. if identifier is equal to a empty GUID, return a device object. // if (Identifier == Guid.Empty) { return(userProfile); } // // Define the SQL query parameters and load the query values. // SqlParameter cmdParms = new SqlParameter(PARM_IDENTIFIER, SqlDbType.UniqueIdentifier); cmdParms.Value = Identifier; // // Generate the SQL query string // sqlQueryString = _sqlQuery_View + " WHERE (ULD_IDENTIFIER = @IDENTIFIER);"; this._DebugLog = "\r\n" + sqlQueryString; // // Execute the query against the database // using (DataTable table = EvSqlMethods.RunQuery(sqlQueryString, cmdParms)) { // // If no rows found, return a device object // if (table.Rows.Count == 0) { return(userProfile); } // // Extract the table row // DataRow row = table.Rows [0]; // // Fill the role object. // userProfile = this.readRow(row); }//END Using // // Return the device data object. // return(userProfile); }//END GetItem method
} //END GetView method. //++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ #endregion #region Retrieve Query methods // ===================================================================================== /// <summary> /// This method gets the information for a device. /// </summary> /// <param name="DeviceId">String: A device Id of a device </param> /// <returns>Object: A device object. </returns> /// <remarks> /// This method consists of following steps. </remarks> /// /// 1. Define local variables. /// /// 2. Check the whether device id is valid or not. If DeviceId is equal to an empty string, return a Device object. /// /// 3. Define the SQL query parameters and load the query values. /// /// 4. Generate the SQL query string. /// /// 5. Execute the query against the database. /// /// 6. If no rows found, return a device object. /// /// 7. Extract the table row. /// /// 8. Fill the Device object. /// /// 9. Return the Device data object. /// // ------------------------------------------------------------------------------------- public Device GetItem(String DeviceId) { this._DebugLog += "Evado.Digital.WebService.Dal.Services.GetItem. method DeviceId: " + DeviceId; // // Define local variables // string sqlQueryString; Device userRegistration = new Device( ); // // Check the whether device id is valid or not. If DeviceId is equal to an empty string, return a Device object. // if (DeviceId == String.Empty) { this._DebugLog += "\r\nUser Id null"; return(userRegistration); } // // Define the SQL query parameters and load the query values. // SqlParameter cmdParms = new SqlParameter(PARM_DEVICE_ID, SqlDbType.Char, 100); cmdParms.Value = DeviceId; // // Generate the SQL query string // sqlQueryString = _sqlQuery_View + " WHERE (DEVICE_ID = @DEVICE_ID);"; this._DebugLog += "\r\n" + sqlQueryString; // // Execute the query against the database // using (DataTable table = EvSqlMethods.RunQuery(sqlQueryString, cmdParms)) { // // If no rows found, return a device object // if (table.Rows.Count == 0) { this._DebugLog += "\r\n Query result empty."; return(userRegistration); } // // Extract the table row // DataRow row = table.Rows [0]; // // Fill the Device object. // userRegistration = this.readRow(row); this._DebugLog += "\r\nUserRegistration.DeviceId: " + userRegistration.DeviceId; }//END Using // // Return the Device data object. // return(userRegistration); }//END GetItem method
}// End readRow method. //++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ #endregion #region Device Query methods // ===================================================================================== /// <summary> /// This method gets a list of a device object from a database. /// </summary> /// <param name="DeviceName">String: A device name</param> /// <param name="DeviceOS">String: A device OS</param> /// <param name="UserId">String: A user id for a user</param> /// <returns>List: A list of a device object.</returns> /// <remarks> /// This method consists of following steps. /// /// 1. Define the local variables. /// /// 2. Define the SQL query parameters and load the query values. /// /// 3. Generate the SQL query string. /// /// 4. Add the filter parameters if needed. /// /// 5. Execute the query against the database. /// /// 6. Iterate through the results extracting the role information. /// /// 7. Extract the table row. /// /// 8. Return the ArrayList containing the User data object. /// /// </remarks> // ------------------------------------------------------------------------------------- public List <Device> GetView(String DeviceName, String DeviceOS, String UserId) { this._DebugLog = "Evado.Digital.WebService.Dal.Services.GetView method. " + " DeviceName: " + DeviceName + " DeviceOS: " + DeviceOS; // // Define the local variables // string sqlQueryString; List <Device> view = new List <Device>( ); // // Define the SQL query parameters and load the query values. // SqlParameter [] cmdParms = new SqlParameter [] { new SqlParameter(PARM_DEVICE_NAME, SqlDbType.NVarChar, 100), new SqlParameter(PARM_DEVICE_OS, SqlDbType.NVarChar, 100), new SqlParameter(PARM_USER_ID, SqlDbType.NVarChar, 100), }; cmdParms [0].Value = DeviceName; cmdParms [1].Value = DeviceOS; cmdParms [2].Value = UserId; // // Generate the SQL query string // sqlQueryString = _sqlQuery_View; // // Add the filter parameters if needed. // if (DeviceName != String.Empty) { sqlQueryString += " WHERE ULD_DEVICE_NAME = @DEVICE_NAME "; if (DeviceOS != String.Empty) { sqlQueryString += " WHERE ULD_DEVICE_OS = @DEVICE_OS "; } sqlQueryString += " ORDER BY USER_ID"; } else { sqlQueryString += " WHERE USER_ID = @USER_ID "; sqlQueryString += " ORDER BY ULD_DEVICE_NAME"; } this._DebugLog += "\r\n" + sqlQueryString; this._DebugLog += "\r\nSQLHelper Status: " + EvSqlMethods.Status; // // Execute the query against the database // using (DataTable table = EvSqlMethods.RunQuery(sqlQueryString, cmdParms)) { // // Iterate through the results extracting the role information. // for (int count = 0; count < table.Rows.Count; count++) { // // Extract the table row // DataRow row = table.Rows [count]; Device profile = this.readRow(row); view.Add(profile); }//END count iteration } this._DebugLog += "\r\n View count: " + view.Count.ToString( ); // // Return the ArrayList containing the User data object. // return(view); } //END GetView method.