}//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.
