Пример #1
0
        }//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
Пример #2
0
        }//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
Пример #3
0
        }//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
Пример #4
0
        } //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
Пример #5
0
        }//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.
Пример #6
0
        }//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
Пример #7
0
        } //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
Пример #8
0
        }// 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.