The Perceiveit.Data library is designed to allow access to different databases using the same code base. It constructs the SQL on the fly and negates the need for the standard boiler plate code when accessing these external systems.

All the code samples below infer the library has been added as a reference, and the namespaces Perceiveit.Data and Perceiveit.Data.Query have been declared at the top of the file.

Creating a database connection

The DynaSQL library has at its centre an instance of the the class Perceiveit.Data.DBDatabase for accessing the external resources.

DBDatabase db1 = DBDatabase.Create("my config connection name");
DBDatabase db2 = DBDatabase.Create("my connection string","my provider name");
The DBDatabase does not store any active connections and does not need to implement IDisposable. Therefore simple static and instance variables can be defined as and when required without worry.

A Simple Query

Once we have a database we can create querys to execute against it.

DBQuery sel = DBQuery.Select()
                      .Fields("CustomerID","CustomerName")
                      .From("Customers");
this is an example, and it is not best practice to use inline string constants

Reading the results

To execute the query all we need to do is pass this query to the database along with the method to do your applications heavy lifting.

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

db1.ExecuteRead(sel, delegate(DbDataReader reader){
     while(reader.Read())
     {
         allcustomers.Add(reader.GetInt32(0), reader.GetString(1));
     }
});

So how does this work?

  • The DBDatabase performed the creation and opening of the connections required.
  • The DBQuery 'sel' was converted to a provider specific string and executed against the database connection.
  • Our (anonymous) callback method was then called with the resultant DbDataReader which populated the dictionary variable.
  • Finally control was returned to our DBDatabase to perform any clean up.

Adding Parameters

The previous example is as simple as it comes, but what about filtering the returned data with values and parameters.
The DBQuery supports a fluid statement definition and constructs are added within the application code.
Below is a query that selects all the customer ids and their number of orders after a specified Date.

//date limit
DateTime ordersAfter = new DateTime(1990,01,01);
            
//Create the select query
DBSelectQuery sel = DBQuery.Select()
                              .Field("CustomerID")
                              .Count("OrderID")
                .From("Orders")
                .GroupBy("CustomerID")
                .OrderBy("CustomerID")
                .WhereField("OrderDate", 
                                      Compare.GreaterThan, DBConst.DateTime(ordersAfter));
Note how similar the statement is to standard SQL. This reduces the learning curve, and increases flexibility.

To change this to a parameter replace the DateTime with a DBParam.

DBParam ordersAfter = DBParam.ParamWithValue(new DateTime(1990,01,01));

...
                  .WhereField("OrderDate", 
                                      Compare.GreaterThan, ordersAfter);

Obviously the value of the parameter can be any variable, instance or parameter.

How about CRUD

The DBQuery supports the standard CRUD statements for modifying data

For an insert:
DBParam name = DBParam.ParamWithValue("new name");
DBParam desc = DBParam.ParamWithValue("new description");

DBInsertQuery insert = DBQuery.InsertInto("Categories")
                                  .Fields("CategoryName", "Description")
                                  .Values(name, desc);

int count = db.ExecuteNonQuery(insert);

What about UPDATE
DBParam cname = DBParam.ParamWithValue("test 2");
DBParam cdesc = DBParam.ParamWithValue("this is the new description");

DBQuery update = DBQuery.Update("Categories")
                        .Set("CategoryName", cname)
                        .AndSet("Description", cdesc)
                        .WhereField("CategoryID", Compare.Equals, DBConst.Const(1));

And DELETE:

DBQuery del = DBQuery.DeleteFrom("Categories")
                            .WhereIn("CategoryName", "new name", "another name");
                           //using the WhereIn construct for more than one deletion

Datatabase agnostic?

The current solution supports a number of Database providers - MySQL, MS SQL Server (inc. Express), SQLite and OLEDb (specifically MS Access), so what is actually getting executed?

If you want to inspect the output of a Query before executing then use the ToSQLString() method to return the sql that would be executed against the DBDatabase.

For MS SQL Server the above query generates the following:

SELECT [CustomerID], 
	COUNT([OrderID]) 
	FROM [Orders]
	WHERE  ([OrderDate] > @_param1) 
	GROUP BY [CustomerID]
	ORDER BY [CustomerID];

Parameter : @_param1 ( Type:DateTime, Direction:Input, Size:0, Source:, Value:01/01/1990 00:00:00)

However in MySql the code is generated as:

SELECT `CustomerID`, 
	COUNT(`OrderID`) 
	FROM `Orders`
	WHERE  (`OrderDate` > ?_param1) 
	GROUP BY `CustomerID`
	ORDER BY `CustomerID`;

Parameter : ?_param1 ( Type:DateTime, Direction:Input, Size:0, Source:, Value:01/01/1990 00:00:00)

As can be seen the parameter identifer prefixes, and field delimiters are implementation specific and only rely on the provider from the DBDatabase.
No change in code is required.

At this stage in development nothing is absolutely agnostic, and certain requirements may be slipping through. Test, test, test as usual ;-)

Error handling

Handling database errors is obviously important. And you can still use the standard Try Catch blocks around your code. However...

All the execute methods on the DBDatabase can be overloaded with an error delegate.
This will be called if any exception is raised during the execution of the command. You can then decide how to handle it in your code.

           DBQuery selectall = DBQuery.Select().Field("CategoryName")
                                        .From("Categories")
                                        .OrderBy("CateogryName");

            List<string> categories = new List<string>();

            db.ExecuteReadEach(selectall, reader =>
            {
                categories.Add(reader.GetString(0));
            },
            onerror =>
            {
                throw new Exception("Could not load the categories", onerror.Exception);
            });


The onerror instance here has a Message property that can be set and will be thrown again internally with the new message, or set onerror.Handled = true and do your own processing.

       .
       .
        onerror =>
            {
                   var errRef = this.RecordMyError(onerror.Exception);
                   this.MyShowError("Could not load the categories",errRef);
                   onerror.Handled = true;
            });


NOTE: An exception will only caught inside this handler if the command cannot be executed against the database. If the query is wrong or a null argument is sent through, then this must be handled independently.

Where to go from here.

The source code download contains both the library, and a set of unit tests (NUnit) that execute a wide range of selects, inserts, updates and deletes on the standard Northwind database.

Last edited Oct 10, 2012 at 12:09 PM by perceiveit, version 10

Comments

No comments yet.