J
Jonathan Wood
I would appreciate any comments on the following code--particularly
criticism that is constructive. It is some of my first data-layer code.
I felt these helper routines would be helpful in their own data-layer class.
There are a few issues that came up, mostly related to using an
SqlDataReader. Also, the compiler complains that all my trailing, default
return statements are unreachable, which doesn't seem right. But these
routines appears to work okay.
Thanks!
/////////////////////////////////////////////////////////////////
/// <summary>
/// Executes a stored procedure and returns it's return value.
/// </summary>
/// <param name="proc">Name of the stored procedure to execute</param>
/// <param name="args">Procedure arguments (one name and one value for each
argument)</param>
/// <returns>The integer value returned from the stored procedure or -1 if
as error
/// occurred</returns>
public static int ExecProcInt(string proc, params object[] args)
{
using (SqlConnection conn = new SqlConnection(_connStr))
{
using (SqlCommand cmd = new SqlCommand(proc, conn))
{
cmd.CommandType = CommandType.StoredProcedure;
// Add parameter arguments
for (int i = 0; i < args.Length; i += 2)
cmd.Parameters.AddWithValue((string)args, args[i + 1]);
// Add return value parameter
var retVal = new SqlParameter("@RETURN_VALUE", SqlDbType.Int);
retVal.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(retVal);
// Execute stored procedure
cmd.Connection.Open();
cmd.ExecuteReader();
if (retVal.Value != null)
return (int)retVal.Value;
}
}
//
return -1;
}
/// <summary>
/// Executes a stored procedure and returns the resulting DataReader. Be
certain to
/// close this DataReader when finished.
/// </summary>
/// <param name="proc">Name of the stored procedure to execute</param>
/// <param name="args">Procedure arguments (one name and one value for each
argument)</param>
/// <returns>The DataReader returned from the stored procedure or null if an
error occurred</returns>
public static SqlDataReader ExecProcReader(string proc, params object[]
args)
{
SqlConnection conn = new SqlConnection(_connStr);
using (SqlCommand cmd = new SqlCommand(proc, conn))
{
cmd.CommandType = CommandType.StoredProcedure;
// Add parameter arguments
for (int i = 0; i < args.Length; i += 2)
cmd.Parameters.AddWithValue((string)args, args[i + 1]);
// Execute stored procedure
cmd.Connection.Open();
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
//
return null;
}
/// <summary>
/// Executes a stored procedure and returns the resulting DataSet.
/// </summary>
/// <param name="proc">Name of the stored procedure to execute</param>
/// <param name="args">Procedure arguments (one name and one value for each
argument)</param>
/// <returns>The DataSet returned from the stored procedure or null if an
error occurred</returns>
public static DataSet ExecProcData(string proc, params object[] args)
{
using (SqlConnection conn = new SqlConnection(_connStr))
{
using (SqlCommand cmd = new SqlCommand(proc, conn))
{
cmd.CommandType = CommandType.StoredProcedure;
// Add parameter arguments
for (int i = 0; i < args.Length; i += 2)
cmd.Parameters.AddWithValue((string)args, args[i + 1]);
// Execute stored procedure
cmd.Connection.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
return ds;
}
}
// An error occured
return null;
}
/// <summary>
/// Executes an SQL query. Be certain to close the returned DataReader when
finished.
/// </summary>
/// <param name="query">SQL query to execute</param>
/// <returns>SqlDataReader with the results of the query, or null if an
error occurred</returns>
public static SqlDataReader ExecQueryReader(string query)
{
SqlConnection conn = new SqlConnection(_connStr);
using (SqlCommand cmd = new SqlCommand(query, conn))
{
cmd.CommandType = CommandType.StoredProcedure;
// Execute stored procedure
cmd.Connection.Open();
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
//
return null;
}
/// <summary>
/// Executes an SQL query.
/// </summary>
/// <param name="query">SQL query to execute</param>
/// <returns>DataSet with the results of the query, or null if an error
occurred</returns>
public static DataSet ExecQueryData(string query)
{
using (SqlConnection conn = new SqlConnection(_connStr))
{
using (SqlCommand cmd = new SqlCommand(query, conn))
{
cmd.CommandType = CommandType.Text;
// Execute stored procedure
cmd.Connection.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
return ds;
}
}
// An error occured
return null;
}
criticism that is constructive. It is some of my first data-layer code.
I felt these helper routines would be helpful in their own data-layer class.
There are a few issues that came up, mostly related to using an
SqlDataReader. Also, the compiler complains that all my trailing, default
return statements are unreachable, which doesn't seem right. But these
routines appears to work okay.
Thanks!
/////////////////////////////////////////////////////////////////
/// <summary>
/// Executes a stored procedure and returns it's return value.
/// </summary>
/// <param name="proc">Name of the stored procedure to execute</param>
/// <param name="args">Procedure arguments (one name and one value for each
argument)</param>
/// <returns>The integer value returned from the stored procedure or -1 if
as error
/// occurred</returns>
public static int ExecProcInt(string proc, params object[] args)
{
using (SqlConnection conn = new SqlConnection(_connStr))
{
using (SqlCommand cmd = new SqlCommand(proc, conn))
{
cmd.CommandType = CommandType.StoredProcedure;
// Add parameter arguments
for (int i = 0; i < args.Length; i += 2)
cmd.Parameters.AddWithValue((string)args, args[i + 1]);
// Add return value parameter
var retVal = new SqlParameter("@RETURN_VALUE", SqlDbType.Int);
retVal.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(retVal);
// Execute stored procedure
cmd.Connection.Open();
cmd.ExecuteReader();
if (retVal.Value != null)
return (int)retVal.Value;
}
}
//
return -1;
}
/// <summary>
/// Executes a stored procedure and returns the resulting DataReader. Be
certain to
/// close this DataReader when finished.
/// </summary>
/// <param name="proc">Name of the stored procedure to execute</param>
/// <param name="args">Procedure arguments (one name and one value for each
argument)</param>
/// <returns>The DataReader returned from the stored procedure or null if an
error occurred</returns>
public static SqlDataReader ExecProcReader(string proc, params object[]
args)
{
SqlConnection conn = new SqlConnection(_connStr);
using (SqlCommand cmd = new SqlCommand(proc, conn))
{
cmd.CommandType = CommandType.StoredProcedure;
// Add parameter arguments
for (int i = 0; i < args.Length; i += 2)
cmd.Parameters.AddWithValue((string)args, args[i + 1]);
// Execute stored procedure
cmd.Connection.Open();
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
//
return null;
}
/// <summary>
/// Executes a stored procedure and returns the resulting DataSet.
/// </summary>
/// <param name="proc">Name of the stored procedure to execute</param>
/// <param name="args">Procedure arguments (one name and one value for each
argument)</param>
/// <returns>The DataSet returned from the stored procedure or null if an
error occurred</returns>
public static DataSet ExecProcData(string proc, params object[] args)
{
using (SqlConnection conn = new SqlConnection(_connStr))
{
using (SqlCommand cmd = new SqlCommand(proc, conn))
{
cmd.CommandType = CommandType.StoredProcedure;
// Add parameter arguments
for (int i = 0; i < args.Length; i += 2)
cmd.Parameters.AddWithValue((string)args, args[i + 1]);
// Execute stored procedure
cmd.Connection.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
return ds;
}
}
// An error occured
return null;
}
/// <summary>
/// Executes an SQL query. Be certain to close the returned DataReader when
finished.
/// </summary>
/// <param name="query">SQL query to execute</param>
/// <returns>SqlDataReader with the results of the query, or null if an
error occurred</returns>
public static SqlDataReader ExecQueryReader(string query)
{
SqlConnection conn = new SqlConnection(_connStr);
using (SqlCommand cmd = new SqlCommand(query, conn))
{
cmd.CommandType = CommandType.StoredProcedure;
// Execute stored procedure
cmd.Connection.Open();
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
//
return null;
}
/// <summary>
/// Executes an SQL query.
/// </summary>
/// <param name="query">SQL query to execute</param>
/// <returns>DataSet with the results of the query, or null if an error
occurred</returns>
public static DataSet ExecQueryData(string query)
{
using (SqlConnection conn = new SqlConnection(_connStr))
{
using (SqlCommand cmd = new SqlCommand(query, conn))
{
cmd.CommandType = CommandType.Text;
// Execute stored procedure
cmd.Connection.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
return ds;
}
}
// An error occured
return null;
}