How to build SQL Generator by means of C#

Updated 27 Oct 2022

2 Min

5722 Views

This article is devoted to my experience of creating a full-fledged SQL query constructor using C# language. In my case, this tool was necessary for the building of various reports in one of the projects. It was a huge platform for the staff management in one of the international corporations. There are hundreds of tables in the database, tens of thousands of code lines and a vast collection of UI libraries. And all this for only one reason: to provide fast and correct operation of the entire service.

Statement of the problem

For example, we have 20 tables, each of them has it's own set of columns. The problem is that we can make a selection in these tables, with all connections, sortings, filters, etc. Of course, for those who are familiar with IT, it will not be difficult to insert an SQL snippet and run it. However, in this case we had to provide a convenient service for usual users of the resource (network share).

Generally, the interface is pretty simple: we select tables, fields, drag the desired and click 'Refresh'. Data set results will be shown in the table. If desired, you can save the resulting report or download the data.

How To Work It Out

Parameters are transmitted to the server:

string fields - the list of fields separated by '|'
string name - the name of the report
int? take - how many records to show
int? skip - how many are already shown in the page

Apart from this, we should find out what kind of relationships is shown between the selected fields. For this purpose, we have created a separate table in the database, which stores information about the tables: what type of relation to use, what basic filter is required (for example, show active users only), sorting, etc.

As a result, when appending the customer data with the data about the table in the database, we have a ReportDetail instance, which displays all the information about the field:

public class ReportDetail {
    public string FieldName;
    public string TableName;
    public string Subquery;
    public System.Nullable<int> DisplayOrder;
    public System.DateTime CreateDateUtc;
    public bool IsDeleted;
    public string Filter;
    public string GroupBy;
    public string OrderBy;
    public bool IsOuter;
    public string JoinType;
    public string ValueType;
    public ReportTable ReportTable;
}

Having gathered together List<ReportDetail>, we pass it to the method, which will build us an SQL query:

string sql = BuildSql (reportDetails.ToList());
private string BuildSql (List<ReportDetail> details)
{
    /* declare variables
for working with strings we use StringBuilder, instead of String, since we need to repeatedly modify the string.   */  
StringBuilder sql = new StringBuilder();
var tables = new List<string>();
StringBuilder select = new StringBuilder();
StringBuilder from = new StringBuilder();
StringBuilder where = new StringBuilder();
StringBuilder defaultFilters = new StringBuilder();

// build select and from sections:
foreach (var detail in details)
{
var field = detail.FieldName;
var table = detail.TableName;
var filter = detail.Filter;
var joinType = detail.JoinType;
var isLocal = crt.IsLocal;
var defaultFilter = crt.DefaultFilter;
var field = detail.FieldName;
var filter = detail.Filter;
var rt = detail.ReportTable;
var joinOn = rt.JoinOn;
var localPrefix = "@server@.dbo.";
var defaultJoinType = rt.DefaultJoinType;
var joinType = joinType ?? defaultJoinType;
var friendlyName = GetFriendlyName(table, field);

if (select.Length == 0)
{
    select.Append(string
          .Format("select [{0}].[{1}] AS {2}", table, field, friendlyName))
}
else
{
    select.Append(",[").Append(table).Append("].[")
          .Append(field).Append("] AS ").Append(friendlyName);
}

if (!tables.Contains(table))
{
// default filters
      if (!string.IsNullOrEmpty(defaultFilter))
     {
         if (defaultFilters.Length == 0)
         {
             defaultFilters = defaultFilters.Append((")  
                             .Append(defaultFilter).Append(")");
         }
         else
         {
             defaultFilters.Append(" and (").Append(defaultFilter).Append(")");
         }
     }
     if (from.Length == 0)
     {
            from = from.Clear().Append("from ").Append(localPrefix)
                              .Append("[") .Append(table).Append("]");
     }
     else
     {
            from.Append(" ").Append(joinType)) .Append(" join ")             
                .Append(localPrefix).Append("[").Append(table)
                .Append("] on ").Append(joinOn);
     }
}
     if (!tables.Contains(table))
     {
         tables.Add(table);
     }
}

//add filters to the where section
int clauseCount = details.Where(x => !string.IsNullOrEmpty(x.Filter)).Count();
int order = 1;
foreach (var detail in details.Where(x => !string.IsNullOrEmpty(x.Filter)).ToList())
{
if (where.Length == 0)
     {
            where = where.Clear().Append("(" + detail.Filter + ")")
                         .Append((clauseCount != order ? " AND" : "") + " ");
     }
     else
     {
         where.Append("(" + detail.Filter + ")")
         .Append((clauseCount != order ? " AND" : "") + " ");
     }
     order++;
}
var subWhere = where.ToString();
where.Clear().Append(" \nwhere ")
       .Append((subWhere.ToString() != "" ? subWhere.ToString() : ""))
       .Append((defaultFilters
       .ToString() != "" ? (subWhere.ToString() != "" ? " and " : "") + defaultFilters.ToString() : ""));

// append the query string
sql = select.CopyTo(sql).Append("\n").Append(from)
            .Append("\n").Append(where).Append("\n").Append("\n");

return sql.ToString();
}
As a result, we got a ready SQL query code. It remains only to run it and get a data set:

using (var connection = new SqlConnection(connectionString))
{
    var dataSet = new DataSet();
    var dataAdapter = new SqlDataAdapter(sql, connection);
    dataAdapter.Fill(dataSet);
    return dataSet.Tables[0];
}

Per page loading and unloading of the number of all entries.

To implement this part, we will use the previously created SQL code, simply by wrapping it in the additional code.

For example, let's create a query string to obtain the number of all entries:

var countQuery = "select count(1) from ("+ sql +") sub";

Per page loading is implemented in much the same:

string sqlWithoutPaging = string
.Format("select top {0} * from (select row_number()  over(order by {1}) as rowid, {2}) sub",
take,
GetFriendlyName(details[0].TableName,details[0].FieldName)
,sql.Substring(6));

sql = string.Format("{0} where rowid between {1} and {2}", sqlWithoutPaging, (skip + 1), (skip + take));

Summing up

In this article, we looked at a fairly simple way to create small SQL queries. Of course, we have created only a skeleton, but it will be enough for most users. However, you may be able to add your own conditional expressions, calculated fields, but we'll discuss this in the next article.

If you have any questions, don't hesitate to contact us.