ADO.NET SQL Parameters

[Guest]

In my C# code, I want to SELECT rows first based upon one column's values and
then different rows based upon a different column's values. Using Query
Builder, I construct: SELECT * FROM myTable WHERE (Var1 = ?) OR (Var2 = ?)
Then in code:
MyDataAdapter.GetFillParameters()[0].Value = myValue;
MyDataAdapter.GetFillParameters()[1].Value = -9999;

There's got to be a better way? What can I do without having a second data
adapter with a different SELECT SQL? Is there anyway to update the SELECT
statement in the code to reference the other column's value?

 

[Miha Markic [MVP C#]]

Hi,

IMO the fastest way is to use two selects.
Other then that you might do something like:
WHERE (Var1 = ? OR ? is NULL) AND (Var2 = ? OR ? IS NULL)
(introduce two new parameters that serve as flags whether the criteria is
valid).
However, you might run into performance drawbacks doing it in this way...

 

[William \(Bill\) Vaughn]

Did you consider using the UNION operator that can return a single rowset
built from two SELECT products?

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Guest]

Thank you. I'm not quite sure how that would help in this case. I really only
want to execute one SELECT with one WHERE at a time. Trouble is I can't
easily switch the SQL statement created by the IDE to another defferent WHERE
clause. After Miha's reply, I tried updating the SQL SELECT statement in my
code but then I had to clear parameter characteristics and re-add the new
parameter to the data adapter. That works fine, albeit inconvenient. I
suppose the other possibility is to have another data adapter containing the
second SELECT statement and switch between them as needed in my code.

Did you consider using the UNION operator that can return a single rowset
built from two SELECT products?

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

In my C# code, I want to SELECT rows first based upon one column's values
and
then different rows based upon a different column's values. Using Query
Builder, I construct: SELECT * FROM myTable WHERE (Var1 = ?) OR (Var2 = ?)
Then in code:
MyDataAdapter.GetFillParameters()[0].Value = myValue;
MyDataAdapter.GetFillParameters()[1].Value = -9999;

There's got to be a better way? What can I do without having a second data
adapter with a different SELECT SQL? Is there anyway to update the SELECT
statement in the code to reference the other column's value?

 

[Guest]

Thank you. I figured out how to update the SQL SELECT statement from the IDE
in the data adapter but then had to clear the old parameter characteristics
and re-add my new parameter. That worked fine, albeit inconvenient. Perhapps
for instances like this I should just create a "new" data adapter in my code
with a hardcoded parameterless SQL SELECT... WHERE clause.

Hi,

IMO the fastest way is to use two selects.
Other then that you might do something like:
WHERE (Var1 = ? OR ? is NULL) AND (Var2 = ? OR ? IS NULL)
(introduce two new parameters that serve as flags whether the criteria is
valid).
However, you might run into performance drawbacks doing it in this way...

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
SLODUG - Slovene Developer Users Group
www.rthand.com

In my C# code, I want to SELECT rows first based upon one column's values
and
then different rows based upon a different column's values. Using Query
Builder, I construct: SELECT * FROM myTable WHERE (Var1 = ?) OR (Var2 = ?)
Then in code:
MyDataAdapter.GetFillParameters()[0].Value = myValue;
MyDataAdapter.GetFillParameters()[1].Value = -9999;

There's got to be a better way? What can I do without having a second data
adapter with a different SELECT SQL? Is there anyway to update the SELECT
statement in the code to reference the other column's value?