Create a Query Provider

A query library is a .NET file that implements the IDatabaseQueryProvider interface. One or more instances of a query provider are used to construct an the query library for an instance of a database service (or filled into the static query library - see Fill Static Query Library). The interface defines one property (the Prefix property) and one method (the Queries() method).

Queries provided can either have the type of DatabaseQuery or FragmentedQuery (a subclass of DatabaseQuery). The DatabaseQuery will be executed as-is, with the SQL and parameters that are specified. The FragmentedQuery will be assembled when the runtime library is filled.

An example DatabaseQuery-only library with CRUD (Create / Retrieve / Update / Delete), plus a query to retrieve all, for an example class is below:

namespace DocExample.Queries
{
    using System.Collections.Generic;
    using System.Data;
    using DatabaseAbstraction.Interfaces;
    using DatabaseAbstraction.Models;

    /// <summary>
    /// This provides queries to manipulate examples.
    /// It uses the "example" query namespace.
    /// </summary>
    public sealed class ExampleQueryProvider : IDatabaseQueryProvider
    {
        public string Prefix
        {
            get { return _prefix; }
            set { _prefix = value; }
        }
        private string _prefix = "example.";

        /// <summary>
        /// Fill queries
        /// </summary>
        /// <param name="queryLibrary">
        /// The query library being built
        /// </param>
        public void Queries(IDictionary<string, DatabaseQuery> queryLibrary)
        {
            queryLibrary.Add(Prefix + "select", Select());
            queryLibrary.Add(Prefix + "insert", Insert());
            queryLibrary.Add(Prefix + "update", Update());
            queryLibrary.Add(Prefix + "delete", Delete());
            queryLibrary.Add(Prefix + "all", All());
        }

        /// <summary>
        /// example.select
        /// </summary>
        /// <returns>
        /// The populated query
        /// </param>
        private DatabaseQuery Select()
        {
            var query = new DatabaseQuery();

            query.SQL = @"SELECT
                    example_id, column_1, column_2, column_3
                FROM example_table
                WHERE example_id = @example_id";

            query.Parameters.Add("example_id", DbType.Int32);

            return query;
        }

        /// <summary>
        /// example.insert
        /// </summary>
        /// <returns>
        /// The populated query
        /// </param>
        private DatabaseQuery Insert()
        {
            var query = new DatabaseQuery();

            query.SQL = @"INSERT INTO example_table
                    (example_id, column_1, column_2, column_3)
                VALUES
                    (@example_id, @column_1, @column_2, @column_3)";

            query.Parameters.Add("example_id", DbType.Int32);
            query.Parameters.Add("column_1", DbType.String);
            query.Parameters.Add("column_2", DbType.Boolean);
            query.Parameters.Add("column_3", DbType.DateTime);

            return query;
        }

        /// <summary>
        /// example.update
        /// </summary>
        /// <returns>
        /// The populated query
        /// </param>
        private DatabaseQuery Update()
        {
            var query = new DatabaseQuery();

            query.SQL = @"UPDATE example_table
                SET column_1 = @column_1,
                    column_2 = @column_2,
                    column_3 = @column_3
                WHERE example_id = @example_id";

            query.Parameters.Add("column_1", DbType.String);
            query.Parameters.Add("column_2", DbType.Boolean);
            query.Parameters.Add("column_3", DbType.DateTime);
            query.Parameters.Add("example_id", DbType.Int32);

            return query;
        }

        /// <summary>
        /// example.delete
        /// </summary>
        /// <returns>
        /// The populated query
        /// </param>
        private DatabaseQuery Delete()
        {
            var query = new DatabaseQuery();

            query.SQL = @"DELETE FROM example_table
                WHERE example_id = @example_id";

            query.Parameters.Add("example_id", DbType.Int32);

            return query;
        }

        /// <summary>
        /// example.all
        /// </summary>
        /// <returns>
        /// The populated query
        /// </param>
        private DatabaseQuery All()
        {
            return new DatabaseQuery
            {
                SQL = @"SELECT
                    example_id, column_1, column_2, column_3
                FROM example_table
                ORDER BY example_id"
            };
        }
    }
}
TIP: The order of the parameters are unimportant; they will be substituted by name. You can also use the same parameter more than once in the SQL query.

You'll notice that there is a lot of duplication in these queries. Select() and All() both have the same SELECT and FROM clauses, and Select(), Update(), and Delete() all have the same WHERE clause and parameter. In the IQueryFragmentProvider example, we have defined fragments for these two parts of the query. Utilizing those, the above would look more like the following:

namespace DocExample.Queries
{
    using System.Collections.Generic;
    using System.Data;
    using DatabaseAbstraction.Interfaces;
    using DatabaseAbstraction.Models;

    /// <summary>
    /// This provides queries to manipulate examples.
    /// It uses the "example" query namespace.
    /// </summary>
    public sealed class ExampleQueryProvider : IDatabaseQueryProvider
    {
        public string Prefix
        {
            get { return _prefix; }
            set { _prefix = value; }
        }
        private string _prefix = "example.";

        /// <summary>
        /// Fill queries
        /// </summary>
        /// <param name="queryLibrary">
        /// The query library being built
        /// </param>
        public void Queries(IDictionary<string, DatabaseQuery> queryLibrary)
        {
            queryLibrary.Add(Prefix + "select", Select());
            queryLibrary.Add(Prefix + "insert", Insert());
            queryLibrary.Add(Prefix + "update", Update());
            queryLibrary.Add(Prefix + "delete", Delete());
            queryLibrary.Add(Prefix + "all", All());
        }

        /// <summary>
        /// example.select
        /// </summary>
        /// <returns>
        /// The populated query
        /// </param>
        private FragmentedQuery Select()
        {
            var query = new FragmentedQuery();

            query.Fragments.Add(QueryFragmentType.Select, "example.select");
            query.Fragments.Add(QueryFragmentType.Where, "example.where.example_id");

            return query;
        }

        /// <summary>
        /// example.insert
        /// </summary>
        /// <returns>
        /// The populated query
        /// </param>
        private DatabaseQuery Insert()
        {
            var query = new DatabaseQuery();

            query.SQL = @"INSERT INTO example_table
                    (example_id, column_1, column_2, column_3)
                VALUES
                    (@example_id, @column_1, @column_2, @column_3)";

            query.Parameters.Add("example_id", DbType.Int32);
            query.Parameters.Add("column_1", DbType.String);
            query.Parameters.Add("column_2", DbType.Boolean);
            query.Parameters.Add("column_3", DbType.DateTime);

            return query;
        }

        /// <summary>
        /// example.update
        /// </summary>
        /// <returns>
        /// The populated query
        /// </param>
        private FragmentedQuery Update()
        {
            var query = new FragmentedQuery
            {
                SQL = @"UPDATE example_table
                    SET column_1 = @column_1,
                        column_2 = @column_2,
                        column_3 = @column_3"
            };
            query.Fragments.Add(QueryFragmentType.Where, "example.where.example_id");
            
            query.Parameters.Add("column_1", DbType.String);
            query.Parameters.Add("column_2", DbType.Boolean);
            query.Parameters.Add("column_3", DbType.DateTime);

            return query;
        }

        /// <summary>
        /// example.delete
        /// </summary>
        /// <returns>
        /// The populated query
        /// </param>
        private FragmentedQuery Delete()
        {
            var query = new FragmentedQuery { SQL = "DELETE FROM example_table" };
            query.Fragments.Add(QueryFragmentType.Where, "example.where.example_id");

            return query;
        }

        /// <summary>
        /// example.all
        /// </summary>
        /// <returns>
        /// The populated query
        /// </param>
        private FragmentedQuery All()
        {
            var query = new FragmentedQuery();

            query.Fragments.Add(QueryFragmentType.Select, "example.select");
            query.AfterFragment.Add(QueryFragmentType.Select, "ORDER BY example_id");

            return query;
        }
    }
}

The above code illustrates how to create a fragmented query.
  • Fragments are appended after any defined SQL for the query. Notice in Update(), we start with SQL, then specify a fragment; when this query is assembled, the fragment will appear after the SQL. As Select() and All() illustrate, SQL is not required for a fragmented query.
  • When adding a query fragment, the type of the fragment must be specified. The QueryFragmentType class is an enumeration that contains the types of fragments allowable. There are 7 types, and the order of the list below is the order in which fragments are assembled:
    • Select
    • Insert
    • Update
    • Delete
    • From
    • Where
    • OrderBy
  • There is no content check performed on any query fragment. This means that a Select fragment does not have to start with the word "SELECT" (in some cases, this may actually be preferable), and can have FROM or JOIN clauses, as our example does. However, to help your code make the most sense, you should use the most applicable query fragment type for your content.
  • If a fragment is specified, SQL to append after the fragment may be added via the AfterFragment property. All() illustrates this use.

<<

Last edited May 6, 2012 at 5:46 AM by danielsummers, version 1

Comments

No comments yet.