How to pass parameters generically

Topics: Data Access Application Block
Apr 26, 2007 at 6:41 PM
Below is a shell of a generic DB call I want to create, but the one stumbling block I can't figure out is how to pass my parameters from the UI layer to this next layer? Any help would be appreciated.

Public Shared Function ExecuteSP(ByVal spName As String , ???Parameters????) As Integer

Dim selCommand As Data.Common.DbCommand
Dim p As SqlClient.SqlParameter

Try
Dim db As Database = DatabaseFactory.CreateDatabase("AppConn")
selCommand = db.GetStoredProcCommand(spName)

???? how to pass parameters?????

ExecuteSP = db.ExecuteNonQuery(selCommand)

Catch exp As Exception
Throw exp

Finally
If Not selCommand Is Nothing Then
selCommand.Dispose()
End If

End Try

End Function
Apr 26, 2007 at 8:16 PM
I'd use a dataset / collection that encapsulates your UI data, pass that in, and pass that directly into the db table, since the dataset can access its datarow property directly, keeping most of the data pretty generic as it moves between layers.
Apr 26, 2007 at 9:41 PM
Are you saying put my parameters info (i.e. parameter name,data type,value) into a dataset and then iterate through the datatable to create all the parameters within my generic db call?
Apr 26, 2007 at 11:00 PM
The DAAB has a parameter discovery option where you can pass it an optional object array of parameter values:

int ExecuteNonQuery(string storedProcedureName, params object[] parameterValues)

My guess is that you can save yourself some work by following the same method signature. In C# this could look like:

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            int results = ExecuteSp("InsertCustomer", new object[] {"David Hayden","test@test.com"});
        }
 
        static int ExecuteSp(string storedProcedureName, params object[] parameterValues)
        {
            Database db = DatabaseFactory.CreateDatabase("AppConn");
            return db.ExecuteNonQuery(storedProcedureName, parameterValues);
        }
    }
}

The key is that the parameter values must be in the proper order as coded in the stored procedure.

Regards,

Dave

__________________________________

David Hayden
Microsoft MVP C#
Apr 26, 2007 at 11:25 PM
I've tried using the discovery option before and when you have many parameters (i.e. 15 and greater) the performance takes a hit (which I can't afford to take). That is why the discovery option is that a viable solution. I'd like to have a way to pass in the parameters without having to use the discovery option.

Thanks
Ian
Apr 27, 2007 at 1:09 PM
That was your best bet to keep things easy and database generic. Now you have to create the parameters somewhere and ideally you would want access to an instance of Database and use the AddInParameter and AddOutParameter to assign parameters to a command. Of course doing so outside the data access layer exposes your data access layer specifics.

Options like these are less ideal IMHO:

int ExecuteSp(string storedProcedureName, DbParameter[] parameters)

or

int ExecuteSp(DbCommand command)

Your best bet is to look at the Data Access Guidance Package in the Web Service Sofware Factory to see how it creates Repository Classes and Other Factory Classes to help create a DAL that uses the DAAB. I talk a little about it here:

Web Services Software Factory Data Access Guidance Package - Blog Engine Data Access Layer Tutorial

Regards,

Dave

____________________________________

David Hayden
Microsoft MVP C#