How do I pass an array of varying types to a function?

A

Alan Silver

Hello,

I'm a bit surprised at the amount of boilerplate code required to do
standard data access in .NET and was looking for a way to improve
matters. In Classic ASP, I used to have a common function that was
included in all pages that took an SQL query and returned a disconnected
recordset. This meant that data access could be achieved in a single
line. I would like to do something similar in ASP.NET.

I know I could just duplicate the code, but that means passing the
complete SQL query in as a parameter. I have recently read more about
SQL injection, and would like to take steps to avoid this, so *don't*
want to do...

string SQL = "select * from mtTable where empName='";
SQL += txtEmpName.Text + "'";

as this is open to injection. (Obviously I would escape single quotes in
the text box, but it's still not so secure.)

I would like to set up an array of some type that has two members, a
name and a value. This array could be populated with the parameter names
and values for the query. Thus, the query could be...

"select * from myTable where empName=@empName"

and the (in this case only) parameter would be named "empName" and have
a value of (say) "Fred".

So, my problem is, how do I do this? I can't work out how to allow the
value part of the parameter type to be able to store any data type
(which would probably only be one of string, int or double, but you
never know), without falling into problems when trying to pass the value
to the SqlParameter object that is going to expect it to be of the
correct type.

The end result is that I would like to pass this array into a function
that could loop through the members of the array and create a new
SqlParameter for each element, using the name and value as appropriate.

TIA for any help you can give. I hope this was all clear!!
 
K

Karl Seguin

You should consider taking a look at the DataAccess block from Microsoft for
some ideas (or even to use it in your code):
http://www.microsoft.com/downloads/...0A-9877-4A7B-88EC-0426B48DF275&displaylang=en

The way they do it is allow a param to be passed to their function (variable
length array) and use reflection against the sproc to match it up....it's
kinda clever!

Otherwise,quickly thinking, I think I'd pass a hashtable as a paramter.
Have the key be the parameter name, and the value be, well, the value. You
can look through the hashtable and add paramters.

foreach (DictionaryEntry entry in hash)
{
command.Parameters.Add(entry.Key).Value = entry.Value;
}


I just have to say, that this entire approach feels weak. Doesn't sound
like you have much of a business object. You DAL seems strictly a querying
engine as opposed to a bridge between your data store and business
entities...

Karl
 
H

Hans Kesting

Alan said:
Hello,

I'm a bit surprised at the amount of boilerplate code required to do
standard data access in .NET and was looking for a way to improve
matters. In Classic ASP, I used to have a common function that was
included in all pages that took an SQL query and returned a
disconnected recordset. This meant that data access could be achieved
in a single line. I would like to do something similar in ASP.NET.

I know I could just duplicate the code, but that means passing the
complete SQL query in as a parameter. I have recently read more about
SQL injection, and would like to take steps to avoid this, so *don't*
want to do...

string SQL = "select * from mtTable where empName='";
SQL += txtEmpName.Text + "'";

as this is open to injection. (Obviously I would escape single quotes
in the text box, but it's still not so secure.)

I would like to set up an array of some type that has two members, a
name and a value. This array could be populated with the parameter
names and values for the query. Thus, the query could be...

"select * from myTable where empName=@empName"

and the (in this case only) parameter would be named "empName" and
have a value of (say) "Fred".

So, my problem is, how do I do this? I can't work out how to allow the
value part of the parameter type to be able to store any data type
(which would probably only be one of string, int or double, but you
never know), without falling into problems when trying to pass the
value to the SqlParameter object that is going to expect it to be of
the correct type.

The end result is that I would like to pass this array into a function
that could loop through the members of the array and create a new
SqlParameter for each element, using the name and value as
appropriate.
TIA for any help you can give. I hope this was all clear!!

And what if you use *three* values in the list? Name, value and *sqltype*.
It's easy to make your own "MyParameter" class for this, which you put
into the list. The "value" has type "object", so you can put anything into it.

For the rest your idea is similar to what I already have implemented
(sorry, can't let you have that, company property), but I use it only
to call stored procedures.
There are basically three methods I can call in this way, depending
on the output: none, scalar (returns an "object") or dataset.

Hans Kesting
 
T

thechaosengine

Hi there,

The basic answer to your question is that you need the "value" part to be
of type "object". If you declare your value as an object - it can hold any
other type as well.

Have a look into inheritance for more information on this works.

Also, as mentioned above, looking at the microsoft data access application
block will take care of a lot of this crap for you. It's good to understand
whats going on, but after a while you just wish that data access code would
bugger off and leave you alone. The application block helps a little towards
that end.

I hope that helps

t
 
A

Alan Silver

And what if you use *three* values in the list? Name, value and
*sqltype*. It's easy to make your own "MyParameter" class for this,
which you put into the list. The "value" has type "object", so you can
put anything into it.
<snip>

That's a good idea, thanks.
 
A

Alan Silver

You should consider taking a look at the DataAccess block from Microsoft for
some ideas (or even to use it in your code):
http://www.microsoft.com/downloads/details.aspx?FamilyId=F63D1F0A-9877-4
A7B-88EC-0426B48DF275&displaylang=en

I looked at it briefly ages ago, but wasn't experienced enough to
understand it. Maybe I should look again.

I did se some posts here where MS were quoted as being surprised how
many people used the data access block in their code when it wasn't
supposed to be production code. They don't seem to be able to decide if
we should or shouldn't use it!!

Anyway, I'll have another look.

I just have to say, that this entire approach feels weak. Doesn't sound
like you have much of a business object. You DAL seems strictly a querying
engine as opposed to a bridge between your data store and business
entities...

Well, it's not just for queries, that was just to make the post simpler.
I assumed that once I had it working for queries, I could get it working
for any SQL. However, if you feel the approach is weak, I'm open to
suggestions. I readily admit to being new at .NET, and I'm always
willing to learn (even when I'm not new).

How would you go about this problem? I just find that writing the same
lines of code over and over again a waste of time. It's also a right
royal pain if you need to change anything. That's why I had one
centralised data access module in Classic ASP. I'm hoping to do the same
here.

Thanks for the reply.
 
A

Alan Silver

Well....code generation is one solution (which I use)
o/r mappers is another (which i don't use)

So what do you do?

Seriously, I'm trying to learn best practices here, but all the code I
see uses the same lines of code over and over again. Surely there must
be a more efficient way of doing it?
 
A

Alan Silver

Like i said, code generation: http://www.codesmithtools.com/

Ah, didn't get it, now I do!!
write a template once, have the tool generate those mundane repetitive lines
of code over and over again...

But you still end up with almost identical bits of code everywhere. That
still seems an inefficient way to do it.

Thanks for the reply
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,731
Messages
2,569,432
Members
44,832
Latest member
GlennSmall

Latest Threads

Top