generic function to execute a sql stored procedure

  • Thread starter Thread starter karlid
  • Start date Start date
K

karlid

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?
 
Does anyone have code for executing any stored procedure using a
generalized function? Is this even possible?
Click to expand...

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
 
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,
 
karlid said:
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.
Click to expand...

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
 
Back
Top