patterncsharpMinor

Generic class to call table parametered stored procedures

Submitted by: @import:stackexchange-codereview·Mar 10, 2026·

Viewed 0 times

storedgenericproceduresparameteredcallclasstable

Problem

This code is to allow calling any stored procedure with one user defined table as the parameter.

I usually use Entity Framework, but I just don't need it for my current solution, so I am rolling my own connection here:

```
public class GenericDataModel
{
public GenericDataModel(string connectionString)
{
this.connectionString = connectionString;
}

///
/// Connection string for the database
///
private readonly String connectionString;

///
/// Calls a stored procedure with a single table as the parameter
///
/// Name of the stored procedure to call (ie integration.UpsertTestOrderTrackingNum)
/// Name of the parameter (ie "@TestOrderTrackingNumObjects")
/// Parameter for the sproc
/// name of the table valued parameter. (ie. integration.TestOrderTrackingNumTableType)
/// The connection to use. This is optional and is there to allow transactions.
public void ExecuteTableParamedProcedure(string storedProcedureName, string parameterName, string tableParamTypeName, IEnumerable sprocParamObjects, SqlConnection connection = null)
{
// If we don't have a connection, then make one.
// The reason this is optionally passed in is so we can do a transaction if needed.
bool connectionCreated = false;
if (connection == null)
{
connection = new SqlConnection(connectionString);
connection.Open();
connectionCreated = true;
}

// Create the command that we are going to be sending
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = storedProcedureName;
command.CommandType = CommandType.StoredProcedure;

SqlParameter parameter = command.Parameters.AddWithValue(parameterName, CreateDataTable(sprocParamObjects));
parameter.SqlDbType = SqlDbType.Structured;
parameter.TypeName = tableParamTypeName;

//

Solution

/// 
/// Calls a stored procedure with a single table as the parameter
/// 
/// Name of the stored procedure to call (ie integration.UpsertTestOrderTrackingNum)
/// Name of the parameter (ie "@TestOrderTrackingNumObjects")
/// Parameter for the sproc
/// name of the table valued parameter.  (ie. integration.TestOrderTrackingNumTableType)
/// The connection to use.  This is optional and is there to allow transactions.
public void ExecuteTableParamedProcedure(string storedProcedureName, string parameterName, string tableParamTypeName, IEnumerable sprocParamObjects, SqlConnection connection = null)

This is probably highly arguable, but I don't like C# optional parameters. The language supports method overloading, which produces methods that are more focused/cohesive.

Thus, I would consider:

/// 
/// Calls a stored procedure with a single table as the parameter.
/// 
public void ExecuteTableParamedProcedure(string storedProcedureName, 
                                            string parameterName, 
                                            string tableParamTypeName, 
                                            IEnumerable sprocParamObjects)
{
    using (var connection = new SqlConnection(this.connectionString))
    {
        connection.Open();
        ExecuteTableParamedProcedure(storedProcedureName, 
                                     parameterName, 
                                     tableParamTypeName, 
                                     sprocParamObjects, 
                                     connection); // multiline to avoid side-scrolling
    }
}

/// 
/// Calls a stored procedure with a single table as the parameter,
/// using the specified connection.
/// 
/// 
/// Useful when executing a stored procedure within a transaction.
/// 
public void ExecuteTableParamedProcedure(string storedProcedureName, 
                                            string parameterName, 
                                            string tableParamTypeName, 
                                            IEnumerable sprocParamObjects,
                                            SqlConnection connection)
{
   // do your thing, you *do* have a connection, and don't need to care about cleaning up.
}

This way the overload that does take a connection parameter can do away with bool connectionCreated and the comments that explain why a connection needs to be created and closed (what about disposed?).

The overload that does not take a connection parameter wraps it in a using block, so you're always sure it gets disposed correctly.

Naming

I think there's possibly a typo in the method's name: ExecuteTableParamedProcedure doesn't look right. Either Parameterized, or the commonly recognized (and used elsewhere) Param - Paramed makes me wonder where the ambulance is.

Comments

// Create the command that we are going to be sending

and

// Call the sproc.

These comments say nothing that the code doesn't say already. They should be removed.

Code Snippets

/// <summary>
/// Calls a stored procedure with a single table as the parameter
/// </summary>
/// <param name="storedProcedureName">Name of the stored procedure to call (ie integration.UpsertTestOrderTrackingNum)</param>
/// <param name="parameterName">Name of the parameter (ie "@TestOrderTrackingNumObjects")</param>
/// <param name="sprocParamObjects">Parameter for the sproc</param>
/// <param name="tableParamTypeName">name of the table valued parameter.  (ie. integration.TestOrderTrackingNumTableType)</param>
/// <param name="connection">The connection to use.  This is optional and is there to allow transactions.</param>
public void ExecuteTableParamedProcedure<T>(string storedProcedureName, string parameterName, string tableParamTypeName, IEnumerable<T> sprocParamObjects, SqlConnection connection = null)

/// <summary>
/// Calls a stored procedure with a single table as the parameter.
/// </summary>
public void ExecuteTableParamedProcedure<T>(string storedProcedureName, 
                                            string parameterName, 
                                            string tableParamTypeName, 
                                            IEnumerable<T> sprocParamObjects)
{
    using (var connection = new SqlConnection(this.connectionString))
    {
        connection.Open();
        ExecuteTableParamedProcedure(storedProcedureName, 
                                     parameterName, 
                                     tableParamTypeName, 
                                     sprocParamObjects, 
                                     connection); // multiline to avoid side-scrolling
    }
}

/// <summary>
/// Calls a stored procedure with a single table as the parameter,
/// using the specified connection.
/// </summary>
/// <remarks>
/// Useful when executing a stored procedure within a transaction.
/// </remarks>
public void ExecuteTableParamedProcedure<T>(string storedProcedureName, 
                                            string parameterName, 
                                            string tableParamTypeName, 
                                            IEnumerable<T> sprocParamObjects,
                                            SqlConnection connection)
{
   // do your thing, you *do* have a connection, and don't need to care about cleaning up.
}

// Create the command that we are going to be sending

// Call the sproc.

Context

StackExchange Code Review Q#44991, answer score: 5

Revisions (0)

No revisions yet.