DataAccess.CustomSqlQuery2

Home / DataAccess

This example demonstrates how to specify SQL query for different data providers by using XML.

CustomSqlQuery2.cs

using System;
using System.Collections.Generic;
using System.IO;
using System.Reflection;
using System.Xml;

using NUnit.Framework;

using BLToolkit.Data;
using BLToolkit.DataAccess;

namespace HowTo.DataAccess
{
    [TestFixture]
    public class CustomSqlQuery2
    {
        public abstract class TestAccessorBase<T> : DataAccessor
            where T : TestAccessorBase<T>
        {
            const int Sql    = 0;
            const int Access = 1;
            const int Oracle = 2;
            const int Fdp    = 3;
            const int SQLite = 4;

            Dictionary<int, string> _sql = new Dictionary<int,string>();

            private string GetSql(string providerName, int provider, int queryID)
            {
                Stream stream = Assembly.GetCallingAssembly().GetManifestResourceStream(
                    "HowTo.DataAccess.Sql." + providerName + ".xml");

                XmlDocument doc = new XmlDocument();

                doc.Load(stream);

                XmlNode node = doc.SelectSingleNode(string.Format("/sql/query[@id={0}]", queryID));

                return node != null? node.InnerText: null;

            }

            protected override string PrepareSqlQuery(DbManager db, int queryID, int uniqueID, string sqlQuery)
            {
                int    provider     = Sql;
                string providerName = db.DataProvider.Name;

                switch (providerName)
                {
                    case "Sql"   : provider = Sql;    break;
                    case "Access": provider = Access; break;
                    case "Oracle": provider = Oracle; break;
                    case "Fdp"   : provider = Fdp;    break;
                    case "SQLite": provider = SQLite; break;
                    default:
                        throw new ApplicationException(
                            string.Format("Unknown data provider '{0}'", providerName));
                }

                string text;
                int    key = provider * 1000000 + uniqueID;

                if (_sql.TryGetValue(key, out text))
                    return text;

                _sql[key] = text = GetSql(providerName, provider, queryID) ?? GetSql("Sql", Sql, queryID);

                return text;
            }

            public static T CreateInstance()
            {
                return DataAccessor.CreateInstance<T>();
            }
        }

        public abstract class PersonAccessor : TestAccessorBase<PersonAccessor>
        {
            [SqlQuery(ID = 1)]
            public abstract List<Person> SelectByLastName(string lastName);

            [SqlQuery(ID = 2)]
            public abstract List<Person> SelectBy([Format] string fieldName, string value);

            [SqlQuery(ID = 3)]
            public abstract List<Person> SelectByLastName(string lastName, [Format(0)] int top);

            [SqlQuery(ID = 4)]
            public abstract List<Person> SelectID(int @id);
        }

        [Test]
        public void Test1()
        {
            PersonAccessor da = PersonAccessor.CreateInstance();

            List<Person> list = da.SelectByLastName("Testerson");

            Assert.AreNotEqual(0, list.Count);
        }

        [Test]
        public void Test2()
        {
            PersonAccessor da = PersonAccessor.CreateInstance();

            List<Person> list = da.SelectBy("FirstName", "John");

            Assert.AreNotEqual(0, list.Count);
        }

        [Test]
        public void Test3()
        {
            PersonAccessor da = PersonAccessor.CreateInstance();

            List<Person> list = da.SelectByLastName("Testerson", 1);

            Assert.AreNotEqual(0, list.Count);
        }

        [Test]
        public void Test4()
        {
            PersonAccessor da = PersonAccessor.CreateInstance();

            List<Person> list = da.SelectID(42);

            Assert.AreEqual(42, list[0].ID);
        }
    }
}

Sql.xml

<?xml version="1.0" encoding="utf-8" ?>
<sql>
    <query id="1">SELECT * FROM Person WHERE LastName = @lastName</query>
    <query id="2">SELECT * FROM Person WHERE {0} = @value</query>
    <query id="3">SELECT TOP {0} * FROM Person WHERE LastName = @lastName</query>
    <query id="4">SELECT @id as PersonID</query>
</sql>

Access.xml

<?xml version="1.0" encoding="utf-8" ?>
<sql>
</sql>

Oracle.xml

<?xml version="1.0" encoding="utf-8" ?>
<sql>
    <query id="1">SELECT * FROM Person WHERE LastName = :lastName</query>
    <query id="2">SELECT * FROM Person WHERE {0} = :value</query>
    <query id="3">SELECT * FROM Person WHERE LastName = :lastName AND rownum &lt;= {0}</query>
    <query id="4">SELECT :id PersonID FROM Dual</query>
</sql>

Fdp.xml

<?xml version="1.0" encoding="utf-8" ?>
<sql>
    <query id="3">SELECT FIRST {0} * FROM Person WHERE LastName = @lastName</query>
    <query id="4">SELECT CAST(@id AS INTEGER) PersonID FROM Dual</query>
</sql>

SQLite.xml

<?xml version="1.0" encoding="utf-8" ?>
<sql>
    <query id="3">SELECT * FROM Person WHERE LastName = @lastName LIMIT {0}</query>
</sql>

Person.cs

using System;

using BLToolkit.DataAccess;
using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    public class Person
    {
        [MapField("PersonID"), PrimaryKey, NonUpdatable]
        public int    ID;

        public string LastName;
        public string FirstName;
        public string MiddleName;
        public Gender Gender;
    }
}

Gender.cs

using System;

using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    public enum Gender
    {
        [MapValue("F")] Female,
        [MapValue("M")] Male,
        [MapValue("U")] Unknown,
        [MapValue("O")] Other
    }
}

App.config

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <connectionStrings>
        <add
            name             = "DemoConnection"
            connectionString = "Server=.;Database=BLToolkitData;Integrated Security=SSPI"
            providerName     = "System.Data.SqlClient" />
    </connectionStrings>
</configuration>

CreateSql