writing SQL queries - methods to write complicated ones!

Hey guys, does anyone have any suggestions that might help with the following?

I am rewriting some software, which I did fo开发者_StackOverflowr a prototype for where I work, I am turning it into a more OOP compliant program :)

I have just written a custom database handler class to deal with my connections, my queries etc. The idea is that this database handler does everything needed to deal with the DB and only returns the result set of the query being run.

Anyways, I have just written a few methods which write my SQL queries for me - the idea being that I pass it some arguments in the form of an Array and the class writes the SQL String needed to query, which removes SQL injection problems.

The problem I have is; with normal selects (with where arguments and order/group by ) and insert and update, These all work fine. But if I want to pass a query which might have a join, or a multi-table join or a where-clause that contains a like or a sub select on the where (this one might be doable with running the select method twice!)

I can't work out how to get the method to produce these queries. Does anyone have any suggestions? - Might have to build custom ones where there is no way around not writing the query myself.

The other idea is over complicating things and to just perform a call that removes slashes contained in the passed string.

Thanks in advance,

vade

btw if it doesnt make much sense, been coding since 7 this morning, brain dying slowly! :)T

I would encourage you to consider writing stored procedures for the functionality you need rather than trying to write some kind of generic query building mechanism.

You could just use sqlcommand with parameters instead http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson06.aspx

If you need a complex query-building mechanism, consider one of the many ORM frameworks already developed, such as NHibernate or MSEF. These allow you to create some pretty complex queries using Linq (compiler-checked; gotta love it) that then are translated to SQL.

If you have the option on the table you could look into doing a LINQ to SQL datalayer. That will let you work with your tables and query results as classes.

Its very easy to get started making a .dbml of your database, check out a walk through on MSDN

It also leaves the dynamic SQL authoring to the MS Language team, so thats nice.