private bool RetrieveParameters(ref Bob.DataClasses.Parameters.spD_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); } return(true); } catch (System.Data.SqlClient.SqlException sqlException) { parameters.internal_UpdateExceptionInformation(sqlException); return(false); } catch (System.Exception exception) { parameters.internal_UpdateExceptionInformation(exception); return(false); } }
/// <summary> /// This method allows you to execute the [spD_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.spD_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); }
private bool InitializeConnection(ref Bob.DataClasses.Parameters.spD_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.spD_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.spD_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.spD_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.spD_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 = "spD_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); } }
private bool DeclareParameters(ref Bob.DataClasses.Parameters.spD_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.Input; 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("@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); } }
private void ResetParameter(ref Bob.DataClasses.Parameters.spD_Job parameters) { parameters.internal_Set_RETURN_VALUE(System.Data.SqlTypes.SqlInt32.Null); }