generic function to execute a sql stored procedure

hello,

In my asp.net projects I like to seperate the database connection and
lookups/executes from the code-behind pages using shared functions.

I find myself often using a function that returns a datatable given a
sql query and it works great. The function is contained in my
'dataaccess' class and is a shared function.

I would really like to do the same thing with stored procedures.
However, I'm running into problems handling the paramaters since there
can be zero or more and of varying types/names.

Ideally, the stored procedure's parameters can be defined in the
parameters of the function (using an array?) and then the stored
procedure is executed using the parameters within the function.

Does anyone have code for executing any stored procedure using a
generalized function? Is this even possible?

Derek
Thu Feb 19 16:03:14 CST 2004

On Thu, 19 Feb 2004 13:28:31 -0800, karlid wrote:

> Does anyone have code for executing any stored procedure using a
> generalized function? Is this even possible?

Depending on the level of convenience you desire, you could write a
function to take an array of IDbParameter instances that you construct in
the calling code, or simply pass an array (could be varargs) of Object
representing the values ... this presents parameter ordering and
readability issues, however.

For my most recent project, I wrote a code generator that generates
stored procedure wrappers based on database metadata. This allowed me to
conveniently call the stored procedures, and I got some help from the
compiler along the way. It also makes output parameters pretty simple. The
client code looks something like this:

SpAddEmployee sp = new SpAddEmployee(conn, tx);
sp.EmpName = "Derek Slager";
sp.EmpDept = "Software";
sp.ExecuteNonQuery();
SqlInt32 empId = sp.EmpId; // output parameter

The corresponding procedure would resemble the following:

... AddEmployee
@emp_name varchar(100)
@emp_dept varchar(100)
@emp_id int output
AS
insert into ... (@emp_name, @emp_dept)
select @emp_id = @@identity

This approach has been quite effective.

-Derek

karlid2000
Thu Feb 26 13:09:16 CST 2004

Very interesting. The main drawback to your approach is that you
need to build custom wrappers for each stored procedure and they are
very sensitive to change. However, it does allow for simplified use
within the code.

I'm going to keep playing around with a truly generic function to call
sp's. If I come up with something decent I'll post to this board.

Cheers,

David
Thu Feb 26 19:12:47 CST 2004


"karlid" <karlid2000@yahoo.com> wrote in message
news:e39bf9bc.0402261109.4b121f64@posting.google.com...
> Very interesting. The main drawback to your approach is that you
> need to build custom wrappers for each stored procedure and they are
> very sensitive to change. However, it does allow for simplified use
> within the code.
>

But that's a good thing. Stored procedures are source code and you should
always use type-safe binding between bits of source code.

If you have a generic stored procedure runner, you just push the problem out
into your application code, mess up your type safety and introduce runtime
errors. To handle the changes to the stored procedures, the wrappers could
be dynamically created at design-time. Then when a stored procedure
changes, just regenerate the wrappers and deal with any resulting
compilation errors.

David

karlid2000
Fri Mar 05 17:02:24 CST 2004

Good point. That sounds like the way to go.

Thanks!