SQL Statement Class Helper request

Is there any products/public/share source codes or like-as case which can
dynamic generate SQL Statement?

for example,

SQL.SelectTable("TableA")

SQL.SelectFields("FieldA, "FieldB", "FieldC")

SQL.AddWhere("FieldA", "AA")

SQL.AddWhere("FieldB", "C", "E")

return SQL.SQLStatement

it will return as Select FieldA, FieldB, FieldC From TableA Where FieldA =
"AA" and FieldB Between "C" and "E"

re: SQL Statement Class Helper request

It is possible to do this graphically using findable controls and tokens
such as
[=], and [LIKE%]. As long as you understand the risk of SQL injection,
you can
write your own controls and bind each control to a field, then iterate
over the
containment hierarchy to retrieve all of the fields and then query each
control
for the user entered WHERE clause. So each control would implement the
following interfaces:

public enum FormMode {Data,Find}
public interface IStatefulUI
{
void SaveState();
void Clear();
void RestoreState();
}
public interface IParameter
{
string Condition {get;}
string Name {get;}
string Text {get;}
string Pre {get;}
string Post {get;}
bool NeedsText {get;}
string Filter {get;}
}
public interface IFindable
{
Parameter Parameter {get;}
bool IsFindable {get;set;}
bool ShowFindableContextMenu {get;set;}
}

And call it like this:

Parameter[] parameters= queryEngine.GetParameters
(this.panel1.Controls);
bool isParameter= false;
string command= "";
foreach (Parameter p in parameters)
{
if (p.Text.Length > 0)
{
if (isParameter)
{
command+= " AND ";
}
command+= p.Filter;
isParameter= true;
}
}

The safer method is to use a parameterized query as in:

this.sqlSelectCommand1.Parameters["@"+p.Name].Value=p.Text.Trim()+"%";

Regards,
Jeff

*** Sent via Developersdex http://www.developersdex.com ***

re: SQL Statement Class Helper request

I am curious why you want to do something like this? You are better off
creating stored procedures and then calling those from in your code.

Granted, it forces you to be better in your design, but it's worth it,
really.

What are your reasons for generating the SQL in this fashion? If you
are going to take this approach, you might as well fashion the SQL yourself,
and use parameterized queries.

Hope this helps.

--
- Nicholas Paldino [.NET/C# MVP]
- mvp@spam.guard.caspershouse.com

"ABC" <abc.abc.com> wrote in message
news:%23uHcT4IyFHA.2348@TK2MSFTNGP15.phx.gbl...[color=blue]
> Is there any products/public/share source codes or like-as case which can
> dynamic generate SQL Statement?
>
> for example,
>
> SQL.SelectTable("TableA")
>
> SQL.SelectFields("FieldA, "FieldB", "FieldC")
>
> SQL.AddWhere("FieldA", "AA")
>
> SQL.AddWhere("FieldB", "C", "E")
>
> return SQL.SQLStatement
>
> it will return as Select FieldA, FieldB, FieldC From TableA Where FieldA =
> "AA" and FieldB Between "C" and "E"
>
>
>
>
>
>[/color]

re: SQL Statement Class Helper request

<"ABC" <abc.abc.com>> wrote:[color=blue]
> Is there any products/public/share source codes or like-as case which can
> dynamic generate SQL Statement?
>
> for example,
>
> SQL.SelectTable("TableA")
>
> SQL.SelectFields("FieldA, "FieldB", "FieldC")
>
> SQL.AddWhere("FieldA", "AA")
>
> SQL.AddWhere("FieldB", "C", "E")
>
> return SQL.SQLStatement
>
> it will return as Select FieldA, FieldB, FieldC From TableA Where FieldA =
> "AA" and FieldB Between "C" and "E"[/color]

Well, you might like to try NHibernate: http://www.nhibernate.org

It's rather more than the above though - you will have to effectively
describe how your schema works, and how to relate it to objects.

You may well find it does rather more for you than you're currently
envisaging though :)

--
Jon Skeet - <skeet@pobox.com>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
If replying to the group, please do not mail me too

re: SQL Statement Class Helper request

Nicholas Paldino [.NET/C# MVP] <mvp@spam.guard.caspershouse.com> wrote:[color=blue]
> I am curious why you want to do something like this? You are better off
> creating stored procedures and then calling those from in your code.[/color]

In my experience, that's only true in limited situations - such as
where you know what kind of queries you need in advance. This often
isn't the case, eg in a situation where the user can create their own
queries from multiple criteria. In that situation, an ORM such as
NHibernate (or DLINQ) is much better than writing the dynamic SQL
yourself either in C# or in a stored proc.

If you already know the form of the query though, what benefit is a
stored proc over a parameterised query?

(Just to mention: an ORM type solution is much better in portability
terms, IME. Porting stored procs can be quite painful...)

--
Jon Skeet - <skeet@pobox.com>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
If replying to the group, please do not mail me too

re: SQL Statement Class Helper request

*sigh* perhaps you had too much to drink the night Ian and I went over
this with you =)

On SQL Server (and most other relational database servers) stored procs
will give you a perf increase because the server will have the execution
plan cached. SQL Server will do this as well for regular queries issued
against the DB, however, those caches are invalidated when you close the
connection (there is also the notion of "preparing" a query for use multiple
times, but once again, connection-specific, if you close the connection, or
retrieve it from a pool, the cache goes bye-bye).

I agree with you that there are ways you might want to filter the query.
In the case where you want to filter on the result set only, it is better to
place the code performing the select/logic in a function that returns a
table, and then select from the function, filtering the return values.

In the case where you need to place a filter on values that are not
exposed through the final result set (which is not easily predictable so
that you can't parameterize the function to provide those filters), then I
would agree that dynamic sql is the way to go.

Depending on how complex your logic is, portability of stored procs can
be very difficult or very easy. Typcially, I don't place much logic in my
database, and stick to the basics. It's not perfect, but it helps in
portability. An ORM is much easier to port, I'll give you that, but it's
very possible that it won't be a requirement.

--
- Nicholas Paldino [.NET/C# MVP]
- mvp@spam.guard.caspershouse.com

P.S. Mojito...

"Jon Skeet [C# MVP]" <skeet@pobox.com> wrote in message
news:MPG.1daceadcfdc26b0998c85c@msnews.microsoft.c om...[color=blue]
> Nicholas Paldino [.NET/C# MVP] <mvp@spam.guard.caspershouse.com> wrote:[color=green]
>> I am curious why you want to do something like this? You are better
>> off
>> creating stored procedures and then calling those from in your code.[/color]
>
> In my experience, that's only true in limited situations - such as
> where you know what kind of queries you need in advance. This often
> isn't the case, eg in a situation where the user can create their own
> queries from multiple criteria. In that situation, an ORM such as
> NHibernate (or DLINQ) is much better than writing the dynamic SQL
> yourself either in C# or in a stored proc.
>
> If you already know the form of the query though, what benefit is a
> stored proc over a parameterised query?
>
> (Just to mention: an ORM type solution is much better in portability
> terms, IME. Porting stored procs can be quite painful...)
>
> --
> Jon Skeet - <skeet@pobox.com>
> http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
> If replying to the group, please do not mail me too[/color]

re: SQL Statement Class Helper request

Nicholas Paldino [.NET/C# MVP] <mvp@spam.guard.caspershouse.com> wrote:[color=blue]
> *sigh* perhaps you had too much to drink the night Ian and I went over
> this with you =)[/color]

Not at all - but I disagreed with you then too, and see no reason not
to do so publicly :)
[color=blue]
> On SQL Server (and most other relational database servers) stored procs
> will give you a perf increase because the server will have the execution
> plan cached. SQL Server will do this as well for regular queries issued
> against the DB, however, those caches are invalidated when you close the
> connection (there is also the notion of "preparing" a query for use multiple
> times, but once again, connection-specific, if you close the connection, or
> retrieve it from a pool, the cache goes bye-bye).[/color]

One advantage of using Java for the connection pool - this doesn't seem
to be an issue using c3p0+jtds.

(I must check this behaviour some time by the way - it would seem to be
a silly way for MS to implement the connection pool... Is it documented
anywhere?)
[color=blue]
> I agree with you that there are ways you might want to filter the query.
> In the case where you want to filter on the result set only, it is better to
> place the code performing the select/logic in a function that returns a
> table, and then select from the function, filtering the return values.[/color]

Why?
[color=blue]
> In the case where you need to place a filter on values that are not
> exposed through the final result set (which is not easily predictable so
> that you can't parameterize the function to provide those filters), then I
> would agree that dynamic sql is the way to go.[/color]

And it's really helpful (IMO) to generate the SQL with a tried and
tested component.
[color=blue]
> Depending on how complex your logic is, portability of stored procs can
> be very difficult or very easy. Typcially, I don't place much logic in my
> database, and stick to the basics. It's not perfect, but it helps in
> portability. An ORM is much easier to port, I'll give you that, but it's
> very possible that it won't be a requirement.[/color]

Indeed - we don't know enough at the moment to know whether it's an
advantage in this case or not.

--
Jon Skeet - <skeet@pobox.com>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
If replying to the group, please do not mail me too

re: SQL Statement Class Helper request

Jon,
[color=blue]
> One advantage of using Java for the connection pool - this doesn't seem
> to be an issue using c3p0+jtds.[/color]

I just did some checking. I was mistaken. SQL Server will not throw
away execution plans (they are not user specific). Rather, as long as you
parameterize them properly, the execution plan will not be cached.

Also, the connection pool implmentation for SQL Server is more a server
side implementation than a client side one. There is a specific SPROC that
is called to reset a connection in a pool. Chances are the DB drivers for
Java use that as well for SQL Server.
[color=blue]
>[color=green]
>> I agree with you that there are ways you might want to filter the
>> query.
>> In the case where you want to filter on the result set only, it is better
>> to
>> place the code performing the select/logic in a function that returns a
>> table, and then select from the function, filtering the return values.[/color]
>
> Why?[/color]

Because functions give you the perf advantage that stored procs give
you.
[color=blue]
>[color=green]
>> In the case where you need to place a filter on values that are not
>> exposed through the final result set (which is not easily predictable so
>> that you can't parameterize the function to provide those filters), then
>> I
>> would agree that dynamic sql is the way to go.[/color]
>
> And it's really helpful (IMO) to generate the SQL with a tried and
> tested component.[/color]

Bleh, learn SQL =)

--
- Nicholas Paldino [.NET/C# MVP]
- mvp@spam.guard.caspershouse.com

re: SQL Statement Class Helper request

Nicholas Paldino [.NET/C# MVP] <mvp@spam.guard.caspershouse.com> wrote:[color=blue][color=green]
> > One advantage of using Java for the connection pool - this doesn't seem
> > to be an issue using c3p0+jtds.[/color]
>
> I just did some checking. I was mistaken. SQL Server will not throw
> away execution plans (they are not user specific). Rather, as long as you
> parameterize them properly, the execution plan will not be cached.[/color]

That's good to hear.
[color=blue]
> Also, the connection pool implmentation for SQL Server is more a server
> side implementation than a client side one. There is a specific SPROC that
> is called to reset a connection in a pool. Chances are the DB drivers for
> Java use that as well for SQL Server.[/color]

Yup. The driver I use actually has options about how queries are
performed:
1) No preparation
2) Temporary stored proc is used
3) sp_executesql
4) sp_prepare/sp_cursorprepare/sp_execute/sp_cursorexecute

I can't remember which we use at the moment - but we've benchmarked it
appropriately.
[color=blue][color=green][color=darkred]
> >> I agree with you that there are ways you might want to filter the
> >> query.
> >> In the case where you want to filter on the result set only, it is better
> >> to
> >> place the code performing the select/logic in a function that returns a
> >> table, and then select from the function, filtering the return values.[/color]
> >
> > Why?[/color]
>
> Because functions give you the perf advantage that stored procs give
> you.[/color]

And how much is that, given cached prepared statements?
[color=blue][color=green]
> > And it's really helpful (IMO) to generate the SQL with a tried and
> > tested component.[/color]
>
> Bleh, learn SQL =)[/color]

While it's not hard to do simple stuff, looking at the queries
generated by Hibernate for some of our operations, I really, really
wouldn't like to make sure that I got everything right myself...

--
Jon Skeet - <skeet@pobox.com>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
If replying to the group, please do not mail me too

re: SQL Statement Class Helper request

I would say security and modularity. The caller only needs permission to
call the
sp and may have no direct access to the underlying database table, which
may
even change with time. I also look as stored procedures as the fore
runner of
business objects. They add a layer of indirection between the GUI or
console
based client and the database and can enforce the business rules along
with
database triggers. It allows the developer to change the database
without
changing the GUI client. It allows the developer a single point of entry
for
updating business logic for multiple clients.

Regards,
Jeff[color=blue]
>If you already know the form of the query though, what benefit is a[/color]
stored proc over a parameterised query?<

*** Sent via Developersdex http://www.developersdex.com ***