SQL Statement Class Helper request

  • Thread starter Thread starter ABC
  • Start date Start date
A

ABC

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"
 
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
 
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.
 
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"
Click to expand...

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 :)
 
Nicholas Paldino said:
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.
Click to expand...

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...)
 
*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]
- (e-mail address removed)

P.S. Mojito...
 
Nicholas Paldino said:
*sigh* perhaps you had too much to drink the night Ian and I went over
this with you =)
Click to expand...

Not at all - but I disagreed with you then too, and see no reason not
to do so publicly :)
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).
Click to expand...

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?)
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.
Why?

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.
Click to expand...

And it's really helpful (IMO) to generate the SQL with a tried and
tested component.
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.
Click to expand...

Indeed - we don't know enough at the moment to know whether it's an
advantage in this case or not.
 
Jon,
One advantage of using Java for the connection pool - this doesn't seem
to be an issue using c3p0+jtds.
Click to expand...

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.
Why?
Click to expand...

Because functions give you the perf advantage that stored procs give
you.
And it's really helpful (IMO) to generate the SQL with a tried and
tested component.
Click to expand...

Bleh, learn SQL =)
 
Nicholas Paldino said:
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.
Click to expand...

That's good to hear.
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.
Click to expand...

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.
Because functions give you the perf advantage that stored procs give
you.
Click to expand...

And how much is that, given cached prepared statements?
Bleh, learn SQL =)
Click to expand...

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...
 
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
If you already know the form of the query though, what benefit is a
Click to expand...
stored proc over a parameterised query?<
 
Back
Top