Databasing framework with LINQ support

• Download source code - 118 KB

Introduction

I would like to present my database framework in this article. I decided to create it when a friend of mine asked me what Object Relational Model framework I would choose. The ones I saw didn't please me, and also, I wanted to study LINQ a little and create something simple to use.

I have my own goals in mind:

1. Make the database be a consequence of the code, not the opposite, and also make the framework work with any transactional database. So, I create my classes, and the database can be generated from it.
2. Support user created types. In general, I don't use decimal to store monetary values. I use a struct that has the basic validation for monetary values and display them correctly. For documents that are used in Brasil (CPF, CNPJ, RG), I have classes that validate them and show them correctly. So, I use these types in the classes.
3. Be really multi-tiered, so the "user interface program" can run on one computer, and the business rules can run in another one.
4. Use very few attributes. They are needed to mark the primary key, to make the database fields nullable, and specify the length of strings in the database. But, I didn't want attributes for everything.
5. Support LINQ. This is a must now, but trickier to use over remoting, as Expressions are not serializable.
6. Make the references be lazy loaded in the easiest possible way.
7. Use less possible parameters in function calls, but have everything that is needed at hand.
8. Be consistent in the usage mode, and at least try to avoid "common" mistakes.

Well, with these goals in mind, I have to take some decisions, so:

1. I have a remoting framework that keeps object states, but only works with interfaces. So, everything that could be called from a remote host must begin with an interface.
2. The database record types are also interfaces. Why? Because the framework does an auto-implementation for them at run-time. This way, fields that are read-only are all validated without the need for the developer to remember, and also the lazy-load can be done automatically, without the need of special structs like RecordReference<recordtype>.
3. Updates are done by cloning. This way, on validation rules, you can check the actual object and the old object, with everything rightly typed. No need to test things by name. This also makes Refactoring easier, and allows the update command to affect only the changed fields.
4. ThreadScopes. The actual connection, the actual transaction, and the actual "validation error packet" all use ThreadScopes. Also, a special connection object is created so it creates commands with the active transaction, as a way to avoid commands to be created without them. The threadscope for errors is very useful if you want to process the import of a file, but don't expect to "catch" exceptions generated by every record to generate an error list. An exception is only thrown at the end of the Thread-Scope error validations, if you don't clear it.
5. Fields must be marked as DatabaseNullable if they allow nulls in the database. Even a Nullable<int> must be marked as DatabaseNullable to accept nulls. But, this is not an error. The database interface can have a Nullable<int> as a way to tell that an int is not initialized, and not to tell that the field must be nullable in the database. Also, making everything required by default makes seeing errors easier than making everything nullable by default.
6. LINQ. Full LINQ support is really hard to achieve. So, I focused on what I think to be the most usefull aspects of it:
• Select of entire objects, of partial objects (select only some fields), and of single fields, be the fields direct or through automatic relationships.
• Where clauses composed of a "database property" operation and a value.

The value can be a constant or a local variable, but can't be an expression like value * 2. Also, the property can be from a relationship. And, as there is no LIKE operator, I added support for:

• string.BeginsWith;
• string.EndsWith;
• string.Contains;

The Contains() operator from lists work in the inverse pattern, so the List.Contains(Property) is converted into DatabaseField IN (List values).

Well, I already talked too much about the ideas, so let's see it in work.

Using the code

First, we need to setup our project.

• Create a new project.
• Add references to Pfz.dll and Pfz.Databasing.dll.
• Create an empty database and create a connection string for it in the app.config.

Mine, for example, contains:

XML

<configuration>
  <connectionstrings>
    <add name="Default" 
      connectionstring="DataBase=Pfz.Databasing;user id=sa;password=12345" 
      providername="System.Data.SqlClient" />
  </connectionstrings>
</configuration>

• And now, we can run this simple example that will create the table FirstRecord for us:
C#

using System.Reflection;
using Pfz.Databasing;

namespace FirstSample
{
    [DatabasePersisted]
    [AlternateKey("ByName", "Name")]
    public interface FirstTable:
        IRecord
    {
        [PrimaryKey]
        int Id { get; set; }
        
        [DatabaseLength(255)]
        string Name { get; set; }
    }
    
    class Program
    {
        static void Main(string[] args)
        {
            DatabaseManager.Value = LocalDatabaseManager.Value;
            LocalDatabaseManager.Value.AddDefaultDataTypeConverters();
        
            DatabaseScriptGenerator generator = new DatabaseScriptGenerator();
            
            // Will generate script for all the persisted
            // records in the given assembly.
            generator.GenerateFor(Assembly.GetEntryAssembly());
            
            using(Record.CreateThreadConnection())
            {
                // Here a local connection works.
                // It is not returned as LocalConnection as it
                // will not be one over the network.
                // Direct command execution is only available locally.
                LocalDatabaseConnection connection = 
                    (LocalDatabaseConnection)ThreadConnection.Value;
                
                using(var command = connection.CreateCommand())
                {
                    foreach(var sql in generator.AllScripts)
                    {
                        command.CommandText = sql;
                        command.ExecuteNonQuery();
                    }
                }
            }
        }
    }
}

Thinks to understand:

• In the record declaration:
• The IRecord is the base interface for any object supported by Pfz.Databasing.
• The [DatabasePersisted] attribute must be used in any interface that creates a table in the database. Inheriting from IRecord is not enough, as you can have base interfaces for your objects. For example, I always create an IIdRecord interface, with has an ID as primary key, instead of putting the attribute in each one of my tables.
• A persisted interface must have at least one [PrimaryKey] property.
• [AlternateKey], as the name says, creates an alternate key (unique index) in the database.
• [DatabaseLength] can be used with strings to set the size of its varchar representation in the database.
• In the DatabaseManager initialization:
• In this sample version, the RemoteDatabaseManager is not provided, but the idea is that DatabaseManager.Value must be set to a LocalDatabaseManager or to a RemoteDatabaseManager.
• The AddDefaultDataTypeConverters adds converters that are able to convert values to and from the database and generate the right scripts, for int, long, string, and other basic types. They are not added by default because you may only want to use your own DataTypeConverters. See the IDataTypeConverter interface if you want to create your own type converter for specific types or support different databases with different naming conventions.
• In the script generator:
• The DatabaseScriptGenerator is very simple, but still powerful. It is capable of discovering all the persisted types in a project, and generates the script to create the tables, the alternate keys, and the foreign keys.
• After generating the scripts, you can access each one individually by the properties CreateTableScripts, AlternateKeyScripts, and ForeignKeyScripts. The AllScripts property is best for first time creation.

The effective creation: Well, the cast to get the LocalDatabaseConnection is ugly, but the manager by default does not expose the type of the connection used, as during remoting, it can be another type, without the capacity to create commands directly. The idea is to give an extra level of security here. But then, the code is simple. foreach through the SQL strings and execute them. A database with only one table is generated.

Let's see how we insert, update, and delete records, before seeing more complex objects and the tiers.

using System;
using System.Linq;
using Pfz.Databasing;

namespace SecondSample
{
    [DatabasePersisted]
    [AlternateKey("ByName", "Name")]
    public interface FirstTable:
        IRecord
    {
        [PrimaryKey]
        int Id { get; set; }
        
        [DatabaseLength(255)]
        string Name { get; set; }
    }
    
    class Program
    {
        static void Main(string[] args)
        {
            DatabaseManager.Value = LocalDatabaseManager.Value;
            LocalDatabaseManager.Value.AddDefaultDataTypeConverters();
        
            using(Record.CreateThreadConnection())
            {
                using(var transaction = new ThreadTransaction())
                {
                    for (int i=0; i<100; i++)
                    {
                        FirstTable item = Record.Create<firsttable>();
                        item.Id = i;
                        item.Name = i.ToString();
                        item.Apply();
                    }
                    
                    var linqQuery1 = 
                        from
                            record1
                        in
                            Record.GetQuery<firsttable>()
                        where
                            record1.Name.StartsWith("5") || 
                              record1.Name.EndsWith("7") || record1.Id == 1
                        select
                            record1.Name;
                    
                    Console.WriteLine("Executing LinqQuery1");
                    foreach(string name in linqQuery1)
                        Console.WriteLine(name);
                        
                    var linqQuery2 =
                        from
                            record2
                        in
                            Record.GetQuery<firsttable>()
                        where
                            record2.Id > 50
                        select
                            record2;

                    Console.WriteLine();
                    Console.WriteLine();
                    Console.WriteLine("Executing LinqQuery2 and updating records.");
                    
                    // ToList is called because, at least in SqlServer,
                    // we can't execute queries while
                    // a datareader is opened. And the ToList
                    // copies all of them to the memory and closes
                    // the record. In other situations
                    // a secondary connection can be opened, but that's
                    // not the idea here, as we are still inside one transaction.
                    foreach(var record in linqQuery2.ToList())
                    {
                        var updateRecord = record.CreateUpdateRecord();
                        updateRecord.Name = "Test " + updateRecord.Name;
                        updateRecord.Apply();
                    }
                    
                    Console.WriteLine();
                    Console.WriteLine();
                    Console.WriteLine("Executing LinqQuery3 and deleting records.");

                    var linqQuery3 = 
                        from
                            record2
                        in
                            Record.GetQuery<firsttable>()
                        where
                            record2.Id <= 50
                        select
                            record2;
                            
                    foreach(var record in linqQuery3.ToList())
                    {
                        record.Delete();
                        record.Apply();
                    }

                    // if I don't commit, everything is rollbacked.
                    transaction.Commit();
                }
            }
        }
    }
}

I created a new example using the same class from the previous one. So, we can now insert, select, update, and delete. See how simple it is? I don't need to create a transaction if I don't want. But it was there to show how it works. Well, with these examples done, let's see some other functionalities, attributes, and classes.

There are three attributes that aren't used in the previous examples:

• [DatabaseName] - which allows you to specify a different name for the table or the field created in the database.
• [DatabaseNullable] - which tells the script generator to create the field in the database allowing null. Of course, if used in conjunction with value types, declare it like: int? (or Nullable<int32>).
• [DatabasePrecisionAndScale] - which tells the script generator to use the given precision and scale for decimal fields.

Also, there is a [DatabaseIgnored] attribute which tells the framework to ignore a property as a database one. But, it is reserved for future use, as the auto-implementation will fail with non-implemented properties.

OK. With those attributes, you can already build a better example. But, I would also like to show how relationships are made very simple with this framework, how useful it is to use base interfaces, and to show the usage of a tier, more specifically, the BusinessRulesTier.

using System;
using System.Reflection;
using System.Threading;
using Pfz.Databasing;
using Pfz.Databasing.BusinessRules;
using Pfz.Databasing.Tiers;

namespace ThirdSample
{
    [DatabasePersisted]
    public interface IIdRecord:
        IRecord
    {
        [PrimaryKey]
        long Id { get; set; }
    }
    public interface INamedRecord:
        IIdRecord
    {
        // If the length is not set, it defaults to 255.
        string Name { get; set; }
    }
    
    [DatabasePersisted]
    [AlternateKey("ByName", "Name")]
    public interface Country:
        INamedRecord
    {
    }
    
    [DatabasePersisted]
    [AlternateKey("ByCountryAndName", 
                  "Country", "Name")]
    public interface State:
        INamedRecord
    {
        Country Country { get; set; }
    }
    
    [DatabasePersisted]
    [AlternateKey("ByStateAndName", 
                  "State", "Name")]
    public interface City:
        INamedRecord
    {
        State State { get; set; }
    }
    
    public sealed class AutoGenerateIdRule:
        Rule<iidrecord>,
        ICreateRule<iidrecord>
    {
        private long fGenerator = DateTime.Now.Ticks;
        public override void Execute(BusinessRulesTier rulesTier, 
               IDatabaseConnection connection, IIdRecord record)
        {
            record.Id = Interlocked.Increment(ref fGenerator);
        }
    }

    class Program
    {
        static void Main(string[] args)
        {
            DatabaseManager.Value = LocalDatabaseManager.Value;
            LocalDatabaseManager.Value.AddDefaultDataTypeConverters();
            DatabaseManager.AddTier<inheritancetier>();
            DatabaseManager.AddTier<businessrulestier>();
        
            var generator = new DatabaseScriptGenerator();
            generator.GenerateFor(Assembly.GetEntryAssembly());
        
            using(Record.CreateThreadConnection())
            {
                var connection = (LocalDatabaseConnection)ThreadConnection.Value;
                using(var command = connection.CreateCommand())
                {
                    foreach(string sql in generator.AllScripts)
                    {
                        command.CommandText = sql;
                        command.ExecuteNonQuery();
                    }
                }

                // I will not use transaction in this sample.
                var country = Record.Create<country>();
                country.Name = "Brasil";
                country = country.Apply();
                
                var state = Record.Create<state>();
                state.Country = country;
                state.Name = "Paraná";
                state = state.Apply();
                
                var city = Record.Create<city>();
                city.State = state;
                city.Name = "Curitiba";
                city.Apply();
                
                var query =
                    from
                        record
                    in
                        Record.GetQuery<city>()
                    where
                        record.State.Country.Name == "Brasil"
                    select
                        new {City = record.Name, State = record.State.Name, 
                             Country = record.State.Country.Name};
                        
                foreach(var record in query)
                    Console.WriteLine(record);
                    
                var query2 =
                    from
                        record2
                    in
                        Record.GetQuery<inamedrecord>()
                    select
                        record2.Name;

                Console.WriteLine();
                Console.WriteLine("Showing all INamedRecord names found in database:");
                foreach(var name in query2)
                    Console.WriteLine(name);
                    
                Console.ReadLine();
            }
        }
    }
}

Thinks to see:

• I created the IIdRecord and INamedRecord base interfaces.
• Country, State, and City are all INamedRecord and persisted.
• I created a rule to generate IDs automatically using DateTime.Now and then increment by one. This way, I avoid creating "complex" or database specific generators. The rule must only implement ICreateRule, but inheriting from the Rule class implements the non-generic method for you, pointing to the generic.
• During initialization, I added a tier named InheritanceTier and another named BusinessTier. At this moment, they don't have priorities, but the correct order is that. Without the BusinessTier, the AutoGenerateIdRule will never be invoked. They need to be added because some projects may not want to use it and, specially because it can be in the client, in the server, or even be a middle-tier in a really multi-tiered remoted application. And, the last query uses the InheritanceTier, as I do a select over any INamedObject.
• I put the create script again in this code, didn't use a transaction, and created some records. See how easy it is to create the relationship and then use the relationship in select and where clauses?

Well, that's all for the moment. I hope you like and use it.