Strongly Typed Stored Procedure Resolution In VB.NET

Question

Currently we generate classes in App_Code by returning all the sprocs from all of our databases and adding the correct parameter types and names to a SqlCommand object and then returning it in one giant class. This means we can do things like MyCmd.cmd.Parameters("MyParam").Value = "whatever" when we want to pass parameters to a SqlCommand for a stored procedure without having to add the parameters with their relative data types every single time we make a call. It also means that we have to look up the exposed parameters for each sproc because they are merely strings.

To put it into perspective, for every parameter you want to deal with in a sproc you'd have to do this:

cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Param", 3, 10, 1, False, CType(0, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))

Really this is undesirable as it'd mean an Intranet/Internet application would explode into gazillions of lines of code to achieve really quite simple tasks.

We're looking into refactoring this in such a way where we can do something like MyDatabase.MySproc.MyParam("value") instead by making it strongly typed. Unfortunately this means our App_Code folder will be twice the size it already is because such a large quantity of code would be required.

I've written a short example of what I mean:

Public Class MyProc

    Dim cmd As SqlCommand

    Public Sub New()
      ' uses the ConfigurationManager to get the connection string for a certain DB'
      Dim cmd As New SqlCommand("MyProc", GetSqlConnection())
    End Sub

    Public Sub Param1(ByVal param As String)
      cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Param1", 3, 10, 1, False, CType(0, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
      cmd.Parameters("Param1").Value = param
    End Sub

    Public Sub Param2(ByVal param As String)
      cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Param2", 22, 7000, 1, False, CType(0, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
      cmd.Parameters("Param1").Value = param
    End Sub

    Public Sub Param3(ByVal param As String)
      cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Param3", 3, 1, 1, False, CType(0, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
      cmd.Parameters("Param3").Value = param
    End Sub

    Public Function GetReturnValue() As String
      cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@RETURN_VALUE", 8, 0, 6, False, CType(0, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
      Return cmd.Parameters("RETURN_VALUE").Value
    End Function
  End Class

Are there alternatives to having to do this that we are unaware of? I'm sure this is a common problem amongst many businesses developing inter/intranet applications.

Answer 1

Why don't you use LINQ to SQL? Even if you don't want to use query stuff, it can generate methods to call stored procedures for you.

Answer 2

Or SubSonic, which generates a very nice SPs static class where each sproc is a callable function and returns (I believe) a DataTable.