DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

Aniruddha has posted 40 posts at DZone. View Full User Profile

Generate Dynamic SQL Using C#

04.23.2012
| 6969 views |
  • submit to reddit
        // This class can be used to generate SQL.
Usage:
Query q = Query.Instance().Select("Associates.AID", "Associates.City", "Associates.Country", "Associates.Created_By", "Entities.Name")
                            .From("Associates", "Entities")
                            .Where("Associates.AID", Operator.Equals, "Entities.AID")
                            .And("Associates.AID", Operator.Equals, "12345")
                            .GroupBy("Associates.AID", "Entities.EID");
public class Query
    {
        private StringBuilder _sql = new StringBuilder(1024);
        private string[] _selectParams;
        private string[] _tableParams;
        private string[] _groupByParams;
        private List<string> _filterParams = new List<string>();

        public Query Select(params string[] selectParams)
        {
            _selectParams = selectParams;
            return this;
        }

        public static Query Instance()
        {
            return new Query();
        }

        public Query Where(string key, Operator op, string value)
        {
            _filterParams.Add(string.Format("{0} {1} {2}", key, GetOperator(op), value));
            return this;
        }

        public Query From(params string[] tableParams)
        {
            _tableParams = tableParams;
            return this;
        }

        private string GetOperator(Operator op)
        {
            switch (op)
            {
                case Operator.Equals: return "=";
                case Operator.GreaterThan: return ">";
                case Operator.GreaterThanEqualTo: return ">=";
                case Operator.LessThan: return "<";
                case Operator.LessThanEqualTo: return "<=";
                default: return "";
            }
        }

        public Query And(string key, Operator op, string value)
        {
            _filterParams.Add(string.Format("{0} {1} {2}", key, GetOperator(op), value));
            return this;
        }

        public Query GroupBy(params string[] groupByParams)
        {
            _groupByParams = groupByParams;
            return this;
        }

        public string ToSql()
        {
            _sql.Append(string.Format(" SELECT {0}", string.Join(", ", _selectParams)));
            _sql.Append("\r\n");
            _sql.Append(" FROM " + string.Join(", ", _tableParams));
            _sql.Append("\r\n");
            _sql.Append(" WHERE ");
            _sql.Append(string.Join(" AND ", _filterParams.ToArray()));
            _sql.Append("\r\n");
            _sql.Append(" GROUP BY " + string.Join(", ", _groupByParams));
            return _sql.ToString();
        }
    }

public enum Operator
    {
        Equals,
        LessThan,
        LessThanEqualTo,
        GreaterThan,
        GreaterThanEqualTo
    }