public static DirectCollection <SPParam> ExecuteStoredProcedure(DbConnectionComponent connection, string storedProcedure, DirectCollection <SPParam> spParams) { //connection.ConnectionString if (LogDeveloper.IsDebugEnabled) { LogDeveloper.DebugFormat("DBOps.ExecuteStoredProcedure - Connection Details provider {0}, connection string {1}", connection.Provider, connection.ConnectionString); } if (connection.Provider != DbConnectionComponent.Providers.Sql) { //We only support Oracle Connection. LogDeveloper.ErrorFormat("DBOps.ExecuteStoredProcedure - The given DB connection was not SQL. Provider is {0}", connection.Provider); return(new DirectCollection <SPParam>()); } if (string.IsNullOrEmpty(storedProcedure)) { LogDeveloper.Error("DBOps.ExecuteStoredProcedure - The given Stored Procedure was empty"); return(new DirectCollection <SPParam>()); } return(ExecuteSP(connection, storedProcedure, spParams)); }
private static DirectCollection <SPParam> ExecuteSP(DbConnectionComponent connection, string storedProcedure, DirectCollection <SPParam> oParams) { SqlConnection cn = null; if (string.IsNullOrEmpty(storedProcedure)) { LogDeveloper.Error("DBOps.ExecuteStoredProcedure - Procedure name can't be empty."); return(new DirectCollection <SPParam>()); } try { cn = new SqlConnection(connection.ConnectionString); SqlCommand sqlCommand = new SqlCommand(storedProcedure, cn); sqlCommand.CommandType = CommandType.StoredProcedure; if (LogDeveloper.IsInfoEnabled) { LogDeveloper.Info("DBOps.ExecuteStoredProcedure - about to parse and add parameters"); } bool fail; foreach (SPParam par in oParams) { SqlParameter param; SqlDbType sqlDBTyp = Trans(par.Type, out fail); if (fail) { LogDeveloper.ErrorFormat("DBOps.ExecuteStoredProcedure - DB Type Conversion Failed."); return(new DirectCollection <SPParam>()); } if (LogDeveloper.IsDebugEnabled) { LogDeveloper.DebugFormat("DBOps.ExecuteStoredProcedure - Adding param name {0} of type {1} and value {2}", par.Name, par.Type, par.Value); } param = new SqlParameter(par.Name, sqlDBTyp, _bufferSize); param.Value = par.Value; if (par.OutputParameter) { param.Direction = ParameterDirection.InputOutput; if (LogDeveloper.IsDebugEnabled) { LogDeveloper.DebugFormat("DBOps.ExecuteStoredProcedure - Parameter is out parameter."); } // op.Size = _bufferSize; } else { param.Direction = ParameterDirection.Input; } sqlCommand.Parameters.Add(param); } if (LogDeveloper.IsDebugEnabled) { LogDeveloper.DebugFormat("DBOps.ExecuteStoredProcedure - About to execute command."); } cn.Open(); sqlCommand.ExecuteNonQuery(); cn.Close(); DirectCollection <SPParam> ret = new DirectCollection <SPParam>(); foreach (SqlParameter par in sqlCommand.Parameters) { if ((par.Direction == ParameterDirection.Output) || (par.Direction == ParameterDirection.InputOutput)) { ret.Add(new SPParam(par.ParameterName, par.SqlDbType.ToString(), par.Value.ToString())); } } return(ret); } catch (Exception e) { LogDeveloper.Error("DBOps.ExecuteStoredProcedure - Error. ", e); if (cn != null && cn.State == ConnectionState.Open) { cn.Close(); } return(new DirectCollection <SPParam>()); } finally { if (cn != null && cn.State == ConnectionState.Open) { cn.Close(); } } }
public static void ExcecuteSPAsync(DbConnectionComponent connection, string storedProcedure, DirectCollection <SPParam> spParams) { ExecuteQuery dlgt = new ExecuteQuery(ExecuteStoredProcedure); dlgt.BeginInvoke(connection, storedProcedure, spParams, new AsyncCallback(ExcecuteSPCallback), null); }