Create complex, flexible SQL queries/commands in C #

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

Introduced

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 += "City like '" + 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.