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[i], 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[i], 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[i], 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;
}

--
Jonathan Wood
SoftCircuits Programming
http://www.softcircuits.com

Hi Jonathan,

I preffer doing a strong typed approach - i.e. use a code generator (think
CodeSmith, or LINQ Designer if you work with VS2008) that generates strong
typed methods for me.

--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

"Jonathan Wood" <jwood (AT) softcircuits (DOT) com> wrote

I'm using VS2008, so I could try LINQ. However, my understanding is that you
get better database performance with stored procedures.

But, if you'd care to provide some reasons for your preferences, that's
something I would be interested in.

--
Jonathan Wood
SoftCircuits Programming
http://www.softcircuits.com


"Miha Markic" <miha at rthand com> wrote

Hi Jonathan,

I was talking about capability of LINQ to SQL designer to create wrapper
methods for stored procedure (you just drag & drop a stored procedure from
server explorer onto designer surface) invocation IOW it creates a C#/VB
method with all the parameters that sp expects. This method in turn calls
stored procedure with given parameters.

--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

"Jonathan Wood" <jwood (AT) softcircuits (DOT) com> wrote

Sounds cool, but then I guess I'd need to create one for each stored
procedure.

Not sure that's what I want, but sounds like something I should definitely
check out sometime. The designer is still something I haven't yet become
familiar with.

--
Jonathan Wood
SoftCircuits Programming
http://www.softcircuits.com

"Miha Markic" <miha at rthand com> wrote

"Jonathan Wood" <jwood (AT) softcircuits (DOT) com> wrote

Yes, that's an annoyance when you have many sprocs. That's why you might use
CodeSmith or some other code generator...Check that out, too.

--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/