Loading a stored procedure based on index?

[mjpdatadev]

I have a few sprocs and I want to create a generic handler for them.
Basically, I want to call any sproc and leave it up to the developer
to know the parameters. To keep it simple, I would like to do it so
that the only thing that the developer has to know is the appropriate
datatype and NOT the name of the param.

So, what I am trying to do is this...

Right now, we do it this way (this works, of course):
sqlComm.CommandType = CommandType.StoredProcedure;
sqlComm.Parameters.AddWithValue("@SLast", "EmpLastName");
sqlComm.Parameters.AddWithValue("@SFirst", "EmpFirstName");
SqlDataReader sqlReader = sqlComm.ExecuteReader();

I want to be able to do it something like this (hiding the param
names):
sqlComm.CommandType = CommandType.StoredProcedure;
sqlComm.Parameters[0].Value = "EmpLastName";
sqlComm.Parameters[1].Value = "EmpFirstName";
// With this method the developer has to know the sprocs he wants to
call and
// the datatypes of the param.
SqlDataReader sqlReader = sqlComm.ExecuteReader();

Has anyone done this before?
As an alternative, is it possible to get the metadata of a sproc to
find the param names?

Thanks,
Mark

 

[Miha Markic [MVP C#]]

Why would you want to in first place?
Isnt't fare more readable if you use name and value of parameter?
As per metadata you could use GetSchema method on your database connection,
assuming you are on .net 2.0. and/or OleDbConnection.GetOleDbSchemaTable if
you are not.

 

[mjpdatadev]

Why would you want to in first place?
Isnt't fare more readable if you use name and value of parameter?

Basically, the reason is to reduce typing. The idea is to have a
generic handler (method) that can accept somthing like,
uspMyStorProc(Value1, Value2). You could overload it to accept as
many params as necessary. This alleviates the developer from saying, -
Oh, what did I call that parm-? Then, going back to the sp and then
back to code. If he/she remembers the order of the indexed parm and
the datatype then they can just pass it through my method.

If you have a better idea or I am missing something please give me
your suggestion.

Thanks for the help.

Mark

 

[Miha Markic [MVP C#]]

The best way would be to (auto)genereta a method call per stored procedure.
This way you would have it crystal clear, i.e.:
GetUsers()
GetSingleUser(int userId)
.....
Check out CodeSmith, it can do this an much much more.

 

[mjpdatadev]

The best way would be to (auto)genereta a method call per storedprocedure.

A call per stored procedure? Don't you think that that goes against
everything we have been taught about code reuse? Wouldn't that bloat
your code? Why use objects all? We can just create a function per
sp.

I have seen and used CodeSmith. It makes coding faster but you have
to recompile your objects whenever you add/change anything. It can
make things more confusing on large projects.

My two cents...

Thank again for the help.

Mark

 

[Miha Markic [MVP C#]]

No, it is exactly the contrary.
The bloating isn't important in this case as you won't have to modify it at
all - everything is done automatically.
And the strong typing support at design time is priceless.
Imagine just these two situations:
- A guy modifies a stored procedure and forgets to tell every developer on
the project, so your late-bounding calls aren't updated. How will you find
the problem? Or will it be found by your customer?
- A guy mismatches the order of parameters. Again, who will find the
problem? Certainly not the compiler at design time.

And no, it has nothing to do with code reuse. It has everything to do with
"find the problem as soon as possible" and "make code more readable".
Also, recompilation is fine. Why not?