Пример #1
0
        private bool RetrieveParameters(ref Bob.DataClasses.Parameters.spI_Job parameters, ref System.Data.SqlClient.SqlCommand sqlCommand)
        {
            try {
                if (sqlCommand.Parameters["@RETURN_VALUE"].Value != System.DBNull.Value)
                {
                    parameters.internal_Set_RETURN_VALUE((System.Int32)sqlCommand.Parameters["@RETURN_VALUE"].Value);
                }
                else
                {
                    parameters.internal_Set_RETURN_VALUE(System.Data.SqlTypes.SqlInt32.Null);
                }

                if (sqlCommand.Parameters["@JobId"].Value != System.DBNull.Value)
                {
                    try {
                        parameters.Param_JobId = (System.Data.SqlTypes.SqlInt32)sqlCommand.Parameters["@JobId"].Value;
                    }
                    catch (System.InvalidCastException) {
                        parameters.Param_JobId = (System.Int32)sqlCommand.Parameters["@JobId"].Value;
                    }
                }
                else
                {
                    parameters.Param_JobId = System.Data.SqlTypes.SqlInt32.Null;
                }
                parameters.internal_Param_JobId_UseDefaultValue = false;

                return(true);
            }

            catch (System.Data.SqlClient.SqlException sqlException) {
                parameters.internal_UpdateExceptionInformation(sqlException);
                return(false);
            }

            catch (System.Exception exception) {
                parameters.internal_UpdateExceptionInformation(exception);
                return(false);
            }
        }
Пример #2
0
        private bool DeclareParameters(ref Bob.DataClasses.Parameters.spI_Job parameters, ref System.Data.SqlClient.SqlCommand sqlCommand)
        {
            try {
                System.Data.SqlClient.SqlParameter sqlParameter;

                sqlParameter            = new System.Data.SqlClient.SqlParameter("@RETURN_VALUE", System.Data.SqlDbType.Int, 4);
                sqlParameter.Direction  = System.Data.ParameterDirection.ReturnValue;
                sqlParameter.IsNullable = true;
                sqlParameter.Value      = System.DBNull.Value;
                sqlCommand.Parameters.Add(sqlParameter);

                sqlParameter = new System.Data.SqlClient.SqlParameter("@JobId", System.Data.SqlDbType.Int, 4);
                sqlParameter.SourceColumn = "JobId";
                sqlParameter.Direction    = System.Data.ParameterDirection.InputOutput;
                if (parameters.internal_Param_JobId_UseDefaultValue)
                {
                    sqlParameter.Value = null;
                }
                else if (!parameters.Param_JobId.IsNull)
                {
                    sqlParameter.Value = parameters.Param_JobId;
                }
                else
                {
                    sqlParameter.IsNullable = true;
                    sqlParameter.Value      = System.DBNull.Value;
                }
                sqlCommand.Parameters.Add(sqlParameter);

                sqlParameter = new System.Data.SqlClient.SqlParameter("@Description", System.Data.SqlDbType.VarChar, 255);
                sqlParameter.SourceColumn = "Description";
                sqlParameter.Direction    = System.Data.ParameterDirection.Input;
                if (parameters.internal_Param_Description_UseDefaultValue)
                {
                    sqlParameter.Value = null;
                }
                else if (!parameters.Param_Description.IsNull)
                {
                    sqlParameter.Value = parameters.Param_Description;
                }
                else
                {
                    sqlParameter.IsNullable = true;
                    sqlParameter.Value      = System.DBNull.Value;
                }
                sqlCommand.Parameters.Add(sqlParameter);

                sqlParameter = new System.Data.SqlClient.SqlParameter("@Price", System.Data.SqlDbType.Money, 21);
                sqlParameter.SourceColumn = "Price";
                sqlParameter.Direction    = System.Data.ParameterDirection.Input;
                if (parameters.internal_Param_Price_UseDefaultValue)
                {
                    sqlParameter.Value = null;
                }
                else if (!parameters.Param_Price.IsNull)
                {
                    sqlParameter.Value = parameters.Param_Price;
                }
                else
                {
                    sqlParameter.IsNullable = true;
                    sqlParameter.Value      = System.DBNull.Value;
                }
                sqlCommand.Parameters.Add(sqlParameter);

                sqlParameter = new System.Data.SqlClient.SqlParameter("@StartDate", System.Data.SqlDbType.DateTime, 16);
                sqlParameter.SourceColumn = "StartDate";
                sqlParameter.Direction    = System.Data.ParameterDirection.Input;
                if (parameters.internal_Param_StartDate_UseDefaultValue)
                {
                    sqlParameter.Value = null;
                }
                else if (!parameters.Param_StartDate.IsNull)
                {
                    sqlParameter.Value = parameters.Param_StartDate;
                }
                else
                {
                    sqlParameter.IsNullable = true;
                    sqlParameter.Value      = System.DBNull.Value;
                }
                sqlCommand.Parameters.Add(sqlParameter);

                sqlParameter = new System.Data.SqlClient.SqlParameter("@EndDate", System.Data.SqlDbType.DateTime, 16);
                sqlParameter.SourceColumn = "EndDate";
                sqlParameter.Direction    = System.Data.ParameterDirection.Input;
                if (parameters.internal_Param_EndDate_UseDefaultValue)
                {
                    sqlParameter.Value = null;
                }
                else if (!parameters.Param_EndDate.IsNull)
                {
                    sqlParameter.Value = parameters.Param_EndDate;
                }
                else
                {
                    sqlParameter.IsNullable = true;
                    sqlParameter.Value      = System.DBNull.Value;
                }
                sqlCommand.Parameters.Add(sqlParameter);

                sqlParameter = new System.Data.SqlClient.SqlParameter("@CustomerId", System.Data.SqlDbType.Int, 4);
                sqlParameter.SourceColumn = "CustomerId";
                sqlParameter.Direction    = System.Data.ParameterDirection.Input;
                if (parameters.internal_Param_CustomerId_UseDefaultValue)
                {
                    sqlParameter.Value = null;
                }
                else if (!parameters.Param_CustomerId.IsNull)
                {
                    sqlParameter.Value = parameters.Param_CustomerId;
                }
                else
                {
                    sqlParameter.IsNullable = true;
                    sqlParameter.Value      = System.DBNull.Value;
                }
                sqlCommand.Parameters.Add(sqlParameter);


                return(true);
            }

            catch (System.Data.SqlClient.SqlException sqlException) {
                parameters.internal_UpdateExceptionInformation(sqlException);
                return(false);
            }

            catch (System.Exception exception) {
                parameters.internal_UpdateExceptionInformation(exception);
                return(false);
            }
        }
Пример #3
0
        private bool InitializeConnection(ref Bob.DataClasses.Parameters.spI_Job parameters, out System.Data.SqlClient.SqlCommand sqlCommand, ref bool connectionMustBeClosed)
        {
            try {
                this.sqlConnection     = null;
                sqlCommand             = null;
                connectionMustBeClosed = true;

                if (parameters.ConnectionType == ConnectionType.None)
                {
                    throw new InvalidOperationException("No connection information was supplied. Consider calling the 'SetUpConnection' method of the Bob.DataClasses.Parameters.spI_Job object before doing this call.");
                }

                if (parameters.ConnectionType == ConnectionType.SqlConnection && parameters.SqlConnection == null)
                {
                    throw new InvalidOperationException("No connection information was supplied (SqlConnection == null). Consider calling the 'SetUpConnection' method of the Bob.DataClasses.Parameters.spI_Job object before doing this call.");
                }

                if (parameters.ConnectionType == ConnectionType.SqlTransaction && parameters.SqlTransaction == null)
                {
                    throw new InvalidOperationException("No connection information was supplied (SqlTransaction == null). Consider calling the 'SetUpConnection' method of the Bob.DataClasses.Parameters.spI_Job object before doing this call.");
                }

                switch (parameters.ConnectionType)
                {
                case ConnectionType.ConnectionString:

                    string connectionString;

                    if (parameters.ConnectionString.Length == 0)
                    {
                        connectionString = Information.GetConnectionStringFromConfigurationFile;
                        if (connectionString.Length == 0)
                        {
                            connectionString = Information.GetConnectionStringFromRegistry;
                        }
                    }

                    else
                    {
                        connectionString = parameters.ConnectionString;
                    }

                    if (connectionString.Length == 0)
                    {
                        throw new System.InvalidOperationException("No connection information was supplied (ConnectionString == \"\")! (Bob.DataClasses.Parameters.spI_Job)");
                    }

                    parameters.internal_SetErrorSource(ErrorSource.ConnectionOpening);
                    this.sqlConnection = new System.Data.SqlClient.SqlConnection(connectionString);
                    this.sqlConnection.Open();

                    sqlCommand = sqlConnection.CreateCommand();
                    break;

                case ConnectionType.SqlConnection:

                    sqlConnection = parameters.SqlConnection;

                    if (this.sqlConnection.State != System.Data.ConnectionState.Open)
                    {
                        this.sqlConnection.Open();
                    }
                    else
                    {
                        connectionMustBeClosed = false;
                    }
                    sqlCommand = sqlConnection.CreateCommand();
                    break;

                case ConnectionType.SqlTransaction:

                    sqlCommand         = new System.Data.SqlClient.SqlCommand();
                    this.sqlConnection = parameters.SqlTransaction.Connection;
                    if (this.sqlConnection == null)
                    {
                        throw new InvalidOperationException("The transaction is no longer valid.");
                    }

                    if (this.sqlConnection.State != System.Data.ConnectionState.Open)
                    {
                        this.sqlConnection.Open();
                    }
                    else
                    {
                        connectionMustBeClosed = false;
                    }
                    sqlCommand.Connection  = sqlConnection;
                    sqlCommand.Transaction = parameters.SqlTransaction;                                             break;
                }

                sqlCommand.CommandTimeout = parameters.CommandTimeOut;
                sqlCommand.CommandType    = System.Data.CommandType.StoredProcedure;
                sqlCommand.CommandText    = "spI_Job";

                return(true);
            }

            catch (System.Data.SqlClient.SqlException sqlException) {
                sqlConnection = null;
                sqlCommand    = null;
                parameters.internal_UpdateExceptionInformation(sqlException);
                return(false);
            }

            catch (System.Exception exception) {
                sqlConnection = null;
                sqlCommand    = null;
                parameters.internal_UpdateExceptionInformation(exception);
                return(false);
            }
        }
Пример #4
0
        /// <summary>
        /// This method allows you to execute the [spI_Job] stored procedure.
        /// </summary>
        /// <param name="parameters">
        /// Contains all the necessary information to execute correctly the stored procedure, i.e.
        /// the database connection to use and all the necessary input parameters to be supplied
        /// for this stored procedure execution. After the execution, this object will allow you
        /// to retrieve back the stored procedure return value and all the output parameters.
        /// </param>
        /// <returns>True if the call was successful. Otherwise, it returns False.</returns>
        public bool Execute(ref Bob.DataClasses.Parameters.spI_Job parameters)
        {
            System.Data.SqlClient.SqlCommand sqlCommand = null;
            System.Boolean returnStatus           = false;
            System.Boolean connectionMustBeClosed = true;

            try {
                ResetParameter(ref parameters);

                parameters.internal_SetErrorSource(ErrorSource.ConnectionInitialization);
                returnStatus = InitializeConnection(ref parameters, out sqlCommand, ref connectionMustBeClosed);
                if (!returnStatus)
                {
                    return(false);
                }

                parameters.internal_SetErrorSource(ErrorSource.ParametersSetting);
                returnStatus = DeclareParameters(ref parameters, ref sqlCommand);
                if (!returnStatus)
                {
                    return(false);
                }

                parameters.internal_SetErrorSource(ErrorSource.QueryExecution);
                sqlCommand.ExecuteNonQuery();

                parameters.internal_SetErrorSource(ErrorSource.ParametersRetrieval);
                returnStatus = RetrieveParameters(ref parameters, ref sqlCommand);
            }

            catch (System.Data.SqlClient.SqlException sqlException) {
                parameters.internal_UpdateExceptionInformation(sqlException);
                returnStatus = false;

                if (this.throwExceptionOnExecute)
                {
                    throw sqlException;
                }
            }

            catch (System.Exception exception) {
                parameters.internal_UpdateExceptionInformation(exception);
                returnStatus = false;
                parameters.internal_SetErrorSource(ErrorSource.Other);

                if (this.throwExceptionOnExecute)
                {
                    throw exception;
                }
            }

            finally {
                if (sqlCommand != null)
                {
                    sqlCommand.Dispose();
                }

                if (parameters.SqlTransaction == null)
                {
                    if (this.sqlConnection != null && connectionMustBeClosed && this.sqlConnection.State == System.Data.ConnectionState.Open)
                    {
                        this.sqlConnection.Close();
                        this.sqlConnection.Dispose();
                    }
                }

                if (returnStatus)
                {
                    parameters.internal_SetErrorSource(ErrorSource.NoError);
                }
                else
                {
                    if (this.throwExceptionOnExecute)
                    {
                        if (parameters.SqlException != null)
                        {
                            throw parameters.SqlException;
                        }
                        else
                        {
                            throw parameters.OtherException;
                        }
                    }
                }
            }
            return(returnStatus);
        }