C#: SQL Query Builder Class

Question

Where can I find a good SQL Query builder class. I just need a simple class to build a SQL string and that is it. I will need it for C# and MySql. I really don't need anything like Linq or NHibernate. Thanks

Answer 1

Since Google leads me to this page, I would suggest SqlKata, a simple but powerful SqlQuery Builder, that supports nested where conditions, subqueries and joins.

Currently it supports SqlServer, MySql and PostgreSql

var query = new Query("Users")
     .LeftJoin("Countries", "Users.CountryId", "Countries.Id")
     .Where("Status", "blocked")
     .OrWhereIn("Id", new [] {10, 11, 12})
     .OrWhere("LastLogin", ">", DateTime.UtcNow.AddMonths(-5));

Note: I am the owner of it

Difference between different compilers output
MySql: https://sqlkata.com/playground/mysql?code=var%20query%20=%20new%20Query(%22Posts%22).Limit(10).Offset(20)%3B

SqlServer: https://sqlkata.com/playground/sqlserver?code=var%20query%20=%20new%20Query(%22Posts%22).Limit(10).Offset(20)%3B

Oracle: https://sqlkata.com/playground/oracle?code=var%20query%20=%20new%20Query(%22Posts%22).Limit(10).Offset(20)%3B

Answer 2

I use this code..It Escapes the strings too i hope it Helps:

 class Mysql
{
    public static string INSERT(string INTO, NameValueCollection VALUES)
    {
        string queryString = "INSERT INTO " + INTO + " (";
        for (int i = 0; i < VALUES.Count; i++)
        {
            queryString += VALUES.Keys[i] + (i + 1 == VALUES.Count ? "" : ",");
        }
        queryString += ") VALUES (";

        for (int i = 0; i < VALUES.Count; i++)
        {
            queryString += Escape(VALUES[VALUES.Keys[i]]) + (i + 1 == VALUES.Count ? ("") : (","));
        }
        queryString += ");";
        return queryString;
    }
    public static string DELETE(string FROM, NameValueCollection WHERE)
    {

        string queryString = "DELETE FROM " + FROM + " WHERE";
        for (int i = 0; i < WHERE.Count; i++)
        {
            queryString += " " + WHERE.Keys[i] + "=" + Escape(WHERE[WHERE.Keys[i]]);

        }

        queryString += ";";
        return queryString;
    }
    public static string UPDATE(string UPDATE, NameValueCollection SET, NameValueCollection WHERE)
    {

        string queryString = "UPDATE " + UPDATE + " SET";
        for (int i = 0; i < SET.Count; i++)
        {
            queryString += " " + SET.Keys[i] + "=" + data.Escape(SET[SET.Keys[i]]) + (i + 1 == SET.Count ? ("") : (","));

        }
        queryString += " WHERE";
        for (int i = 0; i < WHERE.Count; i++)
        {
            queryString += " " + WHERE.Keys[i] + "=" + data.Escape(WHERE[WHERE.Keys[i]]);

        }
        queryString += ";";
        return queryString;

    }
    public static string SELECT(string[] SELECT, string FROM, NameValueCollection WHERE)
    {

        string queryString = "SELECT ";

        for (int i = 0; i < SELECT.Length; i++)
        {
            queryString += SELECT[i] + (i + 1 == SELECT.Length ? ("") : (","));

        }
        queryString += " FROM " + FROM + " WHERE ";
        for (int i = 0; i < WHERE.Count; i++)
        {
            queryString += " " + WHERE.Keys[i] + "=" + Escape(WHERE[WHERE.Keys[i]]);

        }

        queryString += ";";
        return queryString;

    }
    public static string Escape(string input)
    {
        using (var writer = new StringWriter())
        {
            using (var provider = CodeDomProvider.CreateProvider("CSharp"))
            {
                provider.GenerateCodeFromExpression(new CodePrimitiveExpression(input), writer, null);
                return writer.ToString();
            }
        }
    }
}

You use it like this:

        NameValueCollection nvc_for_SET_and_VALUES=new NameValueCollection();
        NameValueCollection nvc_for_WHERE= new NameValueCollection();
        nvc_for_WHERE.Add("arg1","value1");
        nvc_for_WHERE.Add("AND arg2","value2");
        nvc_for_WHERE.Add("OR arg2","value3");
        nvc_for_SET_and_VALUES.Add("arg", "value");
        nvc_for_SET_and_VALUES.Add("arg2", "value2");
        string[] fieldsToSelect= { "arg1", "arg2" };
        Mysql.DELETE("mytable", nvc_for_WHERE);
        Mysql.INSERT("mytable", nvc_for_SET_and_VALUES);
        Mysql.SELECT(fieldsToSelect, "mytable", nvc_for_WHERE);
        Mysql.UPDATE("mytable", nvc_for_SET_and_VALUES, nvc_for_WHERE);