VB.NET 2005 TableAdapter

[ken]

In VB.NET 2005, the ADO.NET provide TableAdapter for Developer eaily code
the
query and parameter query by "Query Builder".

I can build a single parameter or multi parameters SQL Query by using "Query
Builder"
(WHERE ClientCode = @ClientCode ....etc)
But how about if I wanna to build a programmatic SQL statament,
say if the User select ClientCode and OrderType buttom on UI,
I will processing the SQL statment as: WHERE ClientCode ... AND OrderType
.....;
if the User select ClientCode and OrderType and Status, the SQL statement
now processing as:
WHERE ClientCode ... AND OrderType .... AND Status ......

Is that anyway to code the above SQL by "Query Builder" in TableAdapter !?

 

[W.G. Ryan eMVP]

In VB.NET 2005, the ADO.NET provide TableAdapter for Developer eaily code
the
query and parameter query by "Query Builder".

I can build a single parameter or multi parameters SQL Query by using
"Query Builder"
(WHERE ClientCode = @ClientCode ....etc)
But how about if I wanna to build a programmatic SQL statament,
say if the User select ClientCode and OrderType buttom on UI,
I will processing the SQL statment as: WHERE ClientCode ... AND OrderType
....;
if the User select ClientCode and OrderType and Status, the SQL statement
now processing as:
WHERE ClientCode ... AND OrderType .... AND Status ......

Is that anyway to code the above SQL by "Query Builder" in TableAdapter
!?

Sure, you can build your sql statement (however, please use Parameters even
if you're building dynamic sql b/c otherwise there's a lot of headaches
associated with not using parameters) and then set the string to the
TableAdapter's SelectCommand (or UpdateCommand/DeleteCommand/InsertCommand)

 

[ken]

Sure, you can build your sql statement (however, please use Parameters
even if you're building dynamic sql b/c otherwise there's a lot of
headaches associated with not using parameters) and then set the string to
the TableAdapter's SelectCommand (or
UpdateCommand/DeleteCommand/InsertCommand)

for example, I wanna to use daynamic SQL condition, say @Condition,
sometimes I only search with ClientCode, sometimes I search with 3 or 4
criteria...
" Select * from TABLE1 WHERE @Condition "
how can it works?

for now, I can only use Query Builder in TableAdapter, build the
parameterize SQL statement
one by one, can I only make 1 wildcard SQL statement instead of build many
of them!?

thx

 

[W.G. Ryan eMVP]

for example, I wanna to use daynamic SQL condition, say @Condition,
sometimes I only search with ClientCode, sometimes I search with 3 or 4
criteria...
" Select * from TABLE1 WHERE @Condition "
how can it works?

for now, I can only use Query Builder in TableAdapter, build the
parameterize SQL statement
one by one, can I only make 1 wildcard SQL statement instead of build many
of them!?

-With the Visual tools you can only do one at a time. However nothing is
stopping you from creating the command (I'd actually advise using the
DataAccess Application Block or Enterprise LIbrary since they have a nice
way of handling parameters)

 

[ken]

-With the Visual tools you can only do one at a time. However nothing is
stopping you from creating the command (I'd actually advise using the
DataAccess Application Block or Enterprise LIbrary since they have a nice
way of handling parameters)

thx Ryan,

I just wonder can I use the embedded tools keep it simple, instead of
written SQL and Command by myself.
I will try to build my own SQL data access component. Last question, do you
have any suggestion
free license/ coding example about DataAccess Block whether I can find on
internet ?

 

[W.G. Ryan eMVP]

Microsoft's Enterprise Library is free to use so it's probably your best
bet.