Create complex, flexible SQL queries/commands in C #

Sql

The Selectquerybuilder class allows you to create complex SQL statements and commands in your code. It also helps to avoid SQL injection attacks.

　　 introduce

Admit, and we've all done it, and think the following way is the best and the only way. Is that we build a lot of strings that contain all the WHERE clauses and then commit them to the database to execute it. The addition of statements to our SQL string is highly likely to pose a risk of bugs and SQL injection attacks. And it makes our code uglier and less manageable.

This must stop, but how to stop? Some people say that using stored procedures. But it does not really solve the problem. You also have to build your SQL statements dynamically, but the problem moves to the database level, and there is still the risk of SQL injection. Beyond this "solution", there may be a lot of options for you to consider, but they all pose a fundamental challenge: making SQL statements work better and more securely.

When I build a C # template from my online dal (data access Layer) generation tool http://www.code-engine.com/, I want to provide an easy-to-use way to customize the query data. I no longer want to use the string query (the template I developed earlier) to query the data. I'm tired of this messy way to get the data. I want to use a clear, intuitive, flexible, simple way to select data from a table, join some other statements, use a large number of where clauses, group data with columns, and return the first X records.

I started to develop what I thought was the Selectquerybuilder class with this sort of tight function. It exposes a number of properties and methods that you can easily use in a SELECT statement. Once the Buildquery () and Buildcommand () methods are invoked, it can provide a better old "string query" or a DbCommand object that can use command arguments to query the data.

　　 Using Code

The old way of the code

The following code illustrates a previous method of creating a SELECT statement that uses many class variables to indicate which connection operations (WHERE, or OR) should be used, as well as a possible SQL injection attack on your database.

String statement = "SELECT top" + maxrecords + "* from Customers";
String whereconcatenator = "WHERE";
if (CompanyNameTextBox.Text.Length > 0)
{
statement + = Whereconcatenator;
Statement + + "CompanyName like" + Companynametextbox.text + "%";
Whereconcatenator = "and";
}

if (CityTextBox.Text.Length > 0)
{
statement + = Whereconcatenator;
Statement + + Citytextbox.text + "%";
Whereconcatenator = "and";
}
if (Countrycombobox.selecteditem!= null)
{
statement + = Whereconcatenator;
Statement + = "Country = '" + Countrycombobox.selecteditem + "'";
Whereconcatenator = "and";
}
I believe the code above is very familiar to you, you may have been using it for the past more than 10 years, or you have encoded a database-driven search function. Let me tell you the idea: this way of querying your database is no longer available, it's ugly and unsafe.

Selectquerybuilder the code of the way

The same query can be built using the Selectquerybuilder class.

Selectquerybuilder query = new Selectquerybuilder ();
Query. Selectfromtable ("Customers");
Query. Selectallcolumns ();
Query. Toprecords = maxrecords;
if (CompanyNameTextBox.Text.Length > 0)
Query. Addwhere ("CompanyName", Comparison.like,companynametextbox.text + "%");
if (CityTextBox.Text.Length > 0)
Query. Addwhere ("City", Comparison.like,
Citytextbox.text + "%");
if (Countrycombobox.selecteditem!= null)
Query. Addwhere ("Country", Comparison.equals,
Countrycombobox.selecteditem);
String statement = query. Buildquery ();
Or, have a DbCommand object built
For even more safety against SQL injection attacks:
Query. Setdbproviderfactory (
Dbproviderfactories.getfactory (
"System.Data.SqlClient"));
DbCommand command = query. Buildcommand ();
As you can see, this approach is more intuitive than using the connection string directly. Given the risk of SQL injection for the first example, a select query created through Selectquerybuilder is very secure and does not need to worry about the content of the textboxs used. In fact it's also very simple!

　　 Using SQL Functions

If you want to use SQL functions in your query, you can use the Sqlliteral class to package the call to the function. The best way to illustrate what this class can do is to show you a little code example:

Selectquerybuilder query = new Selectquerybuilder ();
Query. Selectfromtable ("Orders");
Query. Addwhere ("OrderDate", Comparison.lessorequals,new sqlliteral ("GetDate ()"));
If we do not package the GetDate () function call into the Sqlliteral class, the created query produces a WHERE clause: orderdate<= ' getDate () '. Of course, we want this function in the statement to be not enclosed in quotation marks. Then sqlliteral can come in handy: it simply copies the string to the output and does not format it as a string. The current output WHERE clause should be orderdate<=getdate ()!

　　 using joins in queries

To create a joins to another table, you can use the Addjoin method. The following code shows how to create a inner JOIN from the Ordres table to the Customers table.

Selectquerybuilder query = new Selectquerybuilder ();
Query. Selectfromtable ("Orders");
Query. Addjoin (Jointype.innerjoin, "Customers", "CustomerID", Comparison.equals, "Orders", "CustomerID");
Query. Addwhere ("Customers.city", Comparison.equals, "London");
This code selects all orders for customers who live in London. Once the Buildquery method is invoked, the following SQL statement is generated:

SELECT orders.*
From Orders
INNER JOIN Customers on orders.customerid = Customers.CustomerID
WHERE (customers.city = ' London ')
Note that the default query only builds the selects * Statement of the selected table (orders.* in this example). If you also want to select columns that connect to the table, you have to choose them explicitly. You can call query universal. Selectcolumns ("orders.*", "customers.*").

　　 set up a calculation query

If you want to execute a calculation query on your database. You can use the SelectCount method as shown below:

Query.selectcount ();

In more complex computational queries, you may want to use the group by statement. Take a look at the following example, which shows how to use the GroupBy and Addhaving methods.

Selectquerybuilder query = new Selectquerybuilder ();
Query. Selectcolumns ("Count (*) as Count", "ShipCity");
Query. Selectfromtable ("Orders");
Query. GroupBy ("ShipCity");
Query. Addhaving ("ShipCity", Comparison.notequals, "Amsterdam");
Query. Addorderby ("Count (*)", sorting.descending);
The code above selects the number of orders per city, sorted by order number, regardless of order from the Amsterdam, the output of the Buildquery method should be:

SELECT Count (*) as Count, ShipCity
From Orders
GROUP by ShipCity
Having (ShipCity <> ' Amsterdam ')
Order BY Count (*) DESC
　　 a complex where statement

If you have ever used Microsoft Access or SQL Server's built-in query builder, are you surprised that you can create a query that contains multiple layers of ands and Ors, and no sign of concern ()? Yes, me too!

You can use the Selectquerybuilder class implementation! You can add multiple layers of where statements to your query. Default, all to query. The Addwhere call is placed on the first level of the query. You can compare it to the ' Criteria ' column of SQL Server Query Builder, and the combined second layer corresponds to the ' Or ... ' column accordingly.

Take a look at the following snapshot of SQL Server Query Builder, through which I can quickly put together a simple fake SELECT statement:

As you can see, I created a query that selects all orders for customers ' Vinet ' before the 1-1-2005 date, and orders for customers ' Tomsp ' before the 30-6-2004 date or 1-1-2006 date (please don't ask why anyone wants to inquire about a person's order, This is just one example). This query can establish:

Selectquerybuilder query = new Selectquerybuilder ();
Query. Selectfromtable ("Orders");
Add ' Criteria ' column to Level 1
Query. Addwhere ("CustomerID", Comparison.equals, "Vinet", 1);
Query. Addwhere ("OrderDate", Comparison.lessthan,new DateTime (2005,1,1), 1);
Add ' Or ... ' column to Level 2
Query. Addwhere ("CustomerID", Comparison.equals, "Tomsp", 2);
Query. Addwhere ("OrderDate", Comparison.lessthan,new DateTime (2004,6,30), 2);
Add second ' Or ... ' column to Level 3
Query. Addwhere ("CustomerID", Comparison.equals, "Tomsp", 3);
Query. Addwhere ("OrderDate", Comparison.greaterthan,new DateTime (2006,1,1), 3);
When Buildquery is invoked, all defined layers will be together with or, almost as SQL Server generates.

If you want the query to be more complex when you arrive at the resulting statement, you might say, "I put two subsequent statements together in one statement, using or during two days." You can do that. In SQL Server Query Builder, this query looks like this:

Similarly, it may be implemented using Selectquerybuilder by creating a ' nested WHERE clause '.

Selectquerybuilder query = new Selectquerybuilder ();
Query. Selectfromtable ("Orders");
Add ' Criteria ' column to Level 1
Query. Addwhere ("CustomerID", Comparison.equals, "Vinet", 1);
Query. Addwhere ("OrderDate", Comparison.lessthan,
New DateTime (2005,1,1), 1);
Add ' Or ... ' column to Level 2
Query. Addwhere ("CustomerID",
Comparison.equals, "Tomsp", 2);
ADD the date selection clause
Whereclause clause =query. Addwhere ("OrderDate", Comparison.lessthan,
New DateTime (2004,6,30), 2);
ADD a nested clause to the captured clause
Clause. Addclause (Logicoperator.or,
Comparison.greaterthan, New DateTime (2006,1,1));
Notice that I used a Whereclause object that was returned by the Addwhere call. Then call clause. Addclause creates a nested clause handle and optionally assigns the logicoperator.or to it or to the first clause. The resulting statement is as follows:

SELECT orders.*
From Orders
WHERE
(
(CustomerID = ' vinet ')
and (OrderDate < ' 2005/01/01 12:00:00 ')
)
OR
(
(CustomerID = ' Tomsp ')
and (OrderDate < ' 2004/06/30 12:00:00 ' OR
OrderDate > ' 2006/01/01 12:00:00 ')
)
Note that the date in this example contains ' 12:00:00 ' because I ignored the time in the DateTime constructor. But it's just because of my habits. If I use new DateTime (2006,1,1,0,0,0), the date string will contain ' 00:00:00 '.

　　 Conclusions

As I mentioned in the introduction, Selectquerybuilder is part of the Codeengine framework. This framework also contains deletequerybuilder,updatequerybuilder,insertquerybuilder. I use these generators in the code generated by my C#dal generator. You can download a copy of the frame DLL from the www.code-engine.com. During this time I will also publish the source code for other query generators. At the same time if you have any questions, comments or suggestions, please contact me in time.