Have you ever wondered why calling a stored procedure in SQL Server is not as simple as calling a function in your code? If you have, you’re not alone. Many developers have encountered this issue and have had to deal with the complexity of calling stored procedures using ADO.NET.

In a typical scenario, you would need to define a command object, specify the stored procedure name, define all the parameters with their names, types, and directions, and then invoke the stored procedure through the command object. If the stored procedure returns output through its arguments or a result set, you would need to retrieve them as well. All of this code needs to be wrapped in exception handling logic and properly dispose of database objects to manage connections and resources.

However, with the advent of .NET Framework and languages like C# and VB.NET, there is a simpler way to interact with a SQL Server database. Calling a stored procedure can now be as easy as a one-line method call that is validated at compile time. This not only simplifies the code but also reduces the chances of runtime bugs caused by changes in stored procedure names or argument types.

In this article, we will explore a .NET component called ActiveDB that makes calling stored procedures in SQL Server a breeze. ActiveDB allows you to generate concise C# or VB.NET function stubs that can be called from your application code. These stubs provide IntelliSense support, giving you the names of the stored procedure, its arguments, and their types.

Let’s take a look at how ActiveDB works. The first step is to retrieve a list of all user stored procedures in the SQL Server database. This can be done using a simple query. Once we have the stored procedure names and their parameters, we can derive the corresponding .NET types for each parameter based on their SQL Server types. If a parameter is an output parameter, we can declare it as a ref argument in C# (ByRef in VB.NET).

One interesting situation arises when a stored procedure parameter accepts a default value. In such cases, we can generate overloaded signatures for the stored procedure method call, omitting the parameters with default values from the list of arguments. This approach works if the parameters with default values are at the end of the stored procedure’s parameter list.

To determine if a stored procedure returns a result set, we can use the “SET FMTONLY ON” statement in an EXEC query. This statement allows us to analyze the columns returned by the query and extract their names and types. This information can be used to generate static (Shared in VB.NET) classes that provide actual column names as properties. By using these properties, programmers can avoid typing column names manually and reduce the chances of errors.

Here’s a sample code generated by ActiveDB for the “CustOrdersDetail” stored procedure in the Northwind database:

internal static class @CustOrdersDetail_Table0
{
    /// <summary>Type: String</summary>
    internal static string @ProductName { get { return "ProductName"; } }
    /// <summary>Type: Decimal</summary>
    internal static string @UnitPrice { get { return "UnitPrice"; } }
    /// <summary>Type: Int16</summary>
    internal static string @Quantity { get { return "Quantity"; } }
    /// <summary>Type: Int32</summary>
    internal static string @Discount { get { return "Discount"; } }
    /// <summary>Type: Decimal</summary>
    internal static string @ExtendedPrice { get { return "ExtendedPrice"; } }
}

decimal nPrice = (decimal)dr[Northwind.CustOrdersDetail_Table0.ExtendedPrice];

As you can see, the generated code provides properties for each column, along with their types. Programmers can use these properties to access the actual column names without worrying about typos or changes in the underlying database.

Finally, calling the stored procedure in your application’s code is as simple as this:

foreach (DataRow dr in Northwind.CustOrdersDetail(nOrderID).Tables[0].Rows)
{
    // Code to process each row
}

The actual stub for the stored procedure is concise and straightforward:

internal static DataSet @CustOrdersDetail(Int32 @OrderID)
{
    DataSet ds = m_DBEngine.ExecDataSetSP(ConnectionString, "CustOrdersDetail", @OrderID);
    return ds;
}

In conclusion, while it would be ideal for Microsoft to address the discontinuity between application code and the database, there are tools like ActiveDB that can make calling stored procedures in SQL Server much easier. These tools bridge the gap between the program and its database, providing design-time references that generate compile-time errors if anything in the underlying database changes.

If you’re interested in learning more about ActiveDB, you can visit the official website at www.mfbyte.com.