public static SQLDBResult Execute(
string procedureName,
ArrayList procedureParameters, string strConnection)
{
SQLDBResult result = null;
SqlConnection connection = null;
try
{
// Get Connection and Procedure
connection = new SqlConnection(strConnection);
connection.Open();
DbUtilProcedure procedure = Procedure(connection, procedureName, null);
// Create Command
SqlCommand command = procedure.Command(connection, procedureParameters);
command.CommandTimeout = 0;
result = new SQLDBResult();
SqlDataAdapter adapter = new SqlDataAdapter(command);
// Execute and Fill Results
adapter.Fill(result.Contents);
foreach (SqlParameter cparam in command.Parameters)
{
switch (cparam.Direction)
{
case ParameterDirection.InputOutput:
case ParameterDirection.Output:
case ParameterDirection.ReturnValue:
result.Parameters[cparam.ParameterName] = cparam.Value;
break;
case ParameterDirection.Input:
default:
// do nothing
break;
}
}
}
catch (System.Exception e)
{
throw new DbUtilException(e.Message, e);
}
finally
{
if ((connection != null) &&
(connection.State == ConnectionState.Open))
{
connection.Close();
}
}
return(result);
}
/// <summary>
///
/// Return the cached procedure.
///
/// </summary>
/// <param name="connection">
///
/// The connection to use to get the procedure detail
///
/// </param>
/// <param name="procedureName">
///
/// The procedure identifier.
///
/// </param>
/// <returns>
///
/// A procedure.
///
/// </returns>
private static DbUtilProcedure Procedure(
SqlConnection connection,
string procedureName, SqlTransaction oTransaction)
{
DbUtilProcedure procedure = null;
if (!m_ProcedureCache.Contains(procedureName))
{
SqlDataReader reader = null;
try
{
m_ProcedureCacheLock.AcquireWriterLock(Timeout.Infinite);
if (!m_ProcedureCache.Contains(procedureName))
{
procedure = new DbUtilProcedure();
SqlCommand command = new SqlCommand("spUTL_GetProcedureMetaData", connection);
command.Parameters.AddWithValue("@objname", procedureName);
command.CommandType = CommandType.StoredProcedure;
command.Transaction = oTransaction;
reader = command.ExecuteReader();
procedure.ProcedureName = procedureName;
while (reader.Read())
{
DbUtilParameter parameter = new DbUtilParameter();
parameter.ParameterName = reader.GetString(0);
parameter.ParameterLength = reader.GetInt32(2);
parameter.ParameterType =
DbUtilParameter.StringToType(reader.GetString(1));
parameter.ParameterDirection =
DbUtilParameter.IntToDirection(reader.GetInt32(5));
procedure.ProcedureParameters.Add(parameter);
}
{
//Adding a Return Type as the SP can't return that value.
DbUtilParameter parameter = new DbUtilParameter();
parameter.ParameterName = "@RETURN_VALUE";
parameter.ParameterLength = 4;
parameter.ParameterType = SqlDbType.Int;
parameter.ParameterDirection = ParameterDirection.ReturnValue;
procedure.ProcedureParameters.Add(parameter);
}
// Update Cache
m_ProcedureCache[procedureName] = procedure;
}
}
catch (System.Exception ex)
{
throw ex;
}
finally
{
if (reader != null)
{
reader.Close();
}
m_ProcedureCacheLock.ReleaseWriterLock();
}
}
try
{
m_ProcedureCacheLock.AcquireReaderLock(Timeout.Infinite);
procedure = (DbUtilProcedure)(m_ProcedureCache[procedureName]);
}
catch (System.Exception)
{
// do nothing
}
finally
{
m_ProcedureCacheLock.ReleaseReaderLock();
}
return(procedure);
}
/// <summary>
///
/// Return the cached procedure.
///
/// </summary>
/// <param name="connection">
///
/// The connection to use to get the procedure detail
///
/// </param>
/// <param name="procedureName">
///
/// The procedure identifier.
///
/// </param>
/// <returns>
///
/// A procedure.
///
/// </returns>
private static DbUtilProcedure Procedure(
SqlConnection connection,
string procedureName, SqlTransaction oTransaction)
{
DbUtilProcedure procedure = null;
if (!m_ProcedureCache.Contains(procedureName))
{
SqlDataReader reader = null;
try
{
m_ProcedureCacheLock.AcquireWriterLock(Timeout.Infinite);
if (!m_ProcedureCache.Contains(procedureName))
{
procedure = new DbUtilProcedure();
SqlCommand command = new SqlCommand("spUTL_GetProcedureMetaData", connection);
command.Parameters.AddWithValue("@objname", procedureName);
command.CommandType = CommandType.StoredProcedure;
command.Transaction = oTransaction;
reader = command.ExecuteReader();
procedure.ProcedureName = procedureName;
while (reader.Read())
{
DbUtilParameter parameter = new DbUtilParameter();
parameter.ParameterName = reader.GetString(0);
parameter.ParameterLength = reader.GetInt32(2);
parameter.ParameterType =
DbUtilParameter.StringToType(reader.GetString(1));
parameter.ParameterDirection =
DbUtilParameter.IntToDirection(reader.GetInt32(5));
procedure.ProcedureParameters.Add(parameter);
}
{
//Adding a Return Type as the SP can't return that value.
DbUtilParameter parameter = new DbUtilParameter();
parameter.ParameterName = "@RETURN_VALUE";
parameter.ParameterLength = 4;
parameter.ParameterType = SqlDbType.Int;
parameter.ParameterDirection = ParameterDirection.ReturnValue;
procedure.ProcedureParameters.Add(parameter);
}
// Update Cache
m_ProcedureCache[procedureName] = procedure;
}
}
catch (System.Exception ex)
{
throw ex;
}
finally
{
if (reader != null)
{
reader.Close();
}
m_ProcedureCacheLock.ReleaseWriterLock();
}
}
try
{
m_ProcedureCacheLock.AcquireReaderLock(Timeout.Infinite);
procedure = (DbUtilProcedure)(m_ProcedureCache[procedureName]);
}
catch (System.Exception)
{
// do nothing
}
finally
{
m_ProcedureCacheLock.ReleaseReaderLock();
}
return procedure;
}
