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

[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=@empNam e"

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!!

--
Alan Silver
(anything added below this line is nothing to do with me)

[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/d...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,quick ly 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 (DictionaryEntr y entry in hash)
{
command.Paramet ers.Add(entry.K ey).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
--
MY ASP.Net tutorials
http://www.openmymind.net/ - New and Improved (yes, the popup is annoying)
http://www.openmymind.net/faq.aspx - unofficial newsgroup FAQ (more to
come!)
"Alan Silver" <al*********@no spam.thanx> wrote in message
news:KO******** ******@nospamth ankyou.spam...

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=@empNam e"

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!!

--
Alan Silver
(anything added below this line is nothing to do with me)

[Hans Kesting]

Alan Silver wrote:

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=@empNam e"

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 "MyParamete r" 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

[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

[Alan Silver]

>And what if you use *three* values in the list? Name, value and

*sqltype*. It's easy to make your own "MyParamete r" 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.

--
Alan Silver
(anything added below this line is nothing to do with me)

[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/d...63D1F0A-9877-4
A7B-88EC-0426B48DF275&di splaylang=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.

<snip>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.

--
Alan Silver
(anything added below this line is nothing to do with me)

[Karl Seguin]

Well....code generation is one solution (which I use)

o/r mappers is another (which i don't use)

Karl

--
MY ASP.Net tutorials
http://www.openmymind.net/ - New and Improved (yes, the popup is
annoying)
http://www.openmymind.net/faq.aspx - unofficial newsgroup FAQ (more to
come!)
"Alan Silver" <al*********@no spam.thanx> wrote in message
news:YI******** ******@nospamth ankyou.spam...

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/d...63D1F0A-9877-4
A7B-88EC-0426B48DF275&di splaylang=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.

<snip>

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.

--
Alan Silver
(anything added below this line is nothing to do with me)

[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?

--
Alan Silver
(anything added below this line is nothing to do with me)

[Karl Seguin]

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

write a template once, have the tool generate those mundane repetitive lines
of code over and over again...

Karl

--
MY ASP.Net tutorials
http://www.openmymind.net/ - New and Improved (yes, the popup is
annoying)
http://www.openmymind.net/faq.aspx - unofficial newsgroup FAQ (more to
come!)
"Alan Silver" <al*********@no spam.thanx> wrote in message
news:g6******** ******@nospamth ankyou.spam...

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?

--
Alan Silver
(anything added below this line is nothing to do with me)

[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

--
Alan Silver
(anything added below this line is nothing to do with me)