Tripous - Data Access

• Download source - 1.61 MB

Introduction

Tripous is an open source application framework, written in C#, for rapid implementation of WinForms data entry applications.

The official site can be found at http://tripous-net.com.
The project page at SourceForge can be found at http://sourceforge.net/projects/tripous. 

Tripous data access layer provides unified data access, that is, database server neutral SQL statements and, the most important, server neutral SQL parameters.

One may expect that a system like .NET provides a level of uniformity when accessing different sources of database data. Unfortunately, this is not the case. The only thing .NET provides in that direction is the notion of the "Data Provider". Initially, a Data Provider was just a set of interfaces like IDbConnection, IDbCommand and the like. In order to "implement" such a Data Provider, you had to implement all those interfaces writing your own classes from scratch. No common ancestor was provided. Not an easy task. That was a heroic era.

As you may know, one of the best ways to execute SQL statements is to use parameterized SQL statements. Tripous uses parameterized SQL statements extensively. Well, ADO.NET does its best to make it very difficult to write server neutral parameterized statements. There is a DbType property, of type System.Data.DbType, in the IDbDataParameter interface and in the (.NET 2) DbParameter class, but any Data Provider comes with its own set of data types. And in many cases, it is hard to say what corresponds to what. Worst than that, any Data Provider uses its own sign as a prefix to a parameter inside the text of a statement. One Data Provider uses the @ followed by the parameter name, while the other uses the : followed by the parameter name. And then there is another Data Provider not using named parameters at all. It just wants a ? in place of a parameter. Take a look. 

select * from Customer where Id = @Id
select * from Customer where Id = :Id
select * from Customer where Id = ?

So how to write server neutral parameterized statements in that system?  

After .NET 2.0 Data Provider classes are based on common base classes, DbConnection, DbCommand etc. Better late than never. And also .NET 2.0 brought the DbProviderFactories and the DbProviderFactory classes to us. The DbProviderFactories is just a factory for creating a DbProviderFactory instance based on a so-called Invariant Name. A string which most of the time is the full name, namespace included, for a specific DbProviderFactory class.

Now "a Data Provider that exposes a DbProviderFactory registers configuration information and a provider string in the machine.config file". Not exactly what I had in mind. It sounds too "static". And of course, you cannot register your own DbProviderFactory on the fly.

Anyway, after getting a DbProviderFactory instance, you use it in creating DbConnection, DbCom<code>mand instances and the like. So you can write server neutral SQL, provided that you do not use parameters.

The above was the first set of problems Tripous had to deal with.

Tripous Data Providers 

Tripous.Data namespace contains all the data access layer related classes.

The Tripous.Data.DataProviders class is actually a registry of Tripous.Data.DataProvider instances.

public static class DataProviders
{
    public static int Count { get; }
    public static char GlobalPrefix { get; set; }

    public static void Add(DataProvider Provider);
    public static DataProvider ByIndex(int Index);
    public static DataProvider ByName(string Alias);
    public static bool CanCreateDatabases(string Alias);
    public static bool Contains(string Alias);
    public static void Error(string Text);
    public static void Error(string Text, params object[] Args);
    public static DataProvider Find(string Alias);
    public static DataProvider[] GetCreatableProviders();
}

The GlobalPrefix property is where you set the character you want to be used as the prefix for parameters when writing parameterized SQL statements. Here is the Tripous.Data.DataProvider class.

public abstract class DataProvider
{
    public DataProvider();

    public string Alias { get; }
    public virtual bool CanCreateDatabases { get; }
    public string Description { get; }
    public MidwareType MidwareType { get; }
    public char NativePrefix { get; }
    public OidMode OidMode { get; }
    public PrefixMode PrefixMode { get; }
    public bool RequiresNativeParamNames { get; }
    public ServerType ServerType { get; }
    public bool SupportsGenerators { get; }
    public bool SupportsTransactions { get; }

    public virtual void AssignParameter(IDataParameter Parameter, object Value);
    public virtual void AssignParameter
	(DataColumn Column, IDataParameter Parameter, object Value);
    public virtual void AssignParameters(DbCommand Command, params object[] Params);
    public virtual void AssignParameters
	(IDataParameterCollection Parameters, params object[] Params);
        
    public abstract DbDataAdapter CreateAdapter();
    public DbCommand CreateCommand
	(DbConnection Connection, string SqlText, params object[] Params);
    public virtual DbCommandBuilder CreateCommandBuilder();
    public abstract DbConnection CreateConnection(string ConnectionString);
    public virtual DbParameter CreateParameter();
    public virtual DbConnectionStringBuilder CreateConnectionStringBuilder();
    public virtual ConnectionStringBuilder CreateConnectionStringBuilder
	(string ConnectionString);
        
    public virtual bool CreateDatabase(string ConnectionString);
    public virtual bool CreateDatabase
	(string ServerName, string DatabaseName, string UserName, string Password);
        
    public DataTable Select(DbCommand Command);
    public DataTable Select(string ConnectionString, string SqlText);
    public DataTable Select
	(string ConnectionString, string SqlText, params object[] Params);
                
    public void Exec(string ConnectionString, string SqlText);
    public void Exec(string ConnectionString, string SqlText, params object[] Params);
        
    public virtual string FormatConnectionString(string FileOrDatabaseName);
    public virtual string FormatConnectionString
	(string HostComputer, string FileOrDatabaseName);
    public virtual string NormalizeAlterTableColumnSql(string SqlText);
        
    public void PrefixRemove(DbCommand Command);
    public virtual string PrefixRemove(string ParameterName);
    public void PrefixToGlobal(DbCommand Command);
    public virtual string PrefixToGlobal(string ParameterName);
    public void PrefixToNative(DbCommand Command);
    public virtual string PrefixToNative(string ParameterName);
        
    public virtual string ReplaceDataTypePlaceholders(string SqlText);

    public void SetupCommand(DbCommand Command, string SqlText, params object[] Params);
    public virtual DbType TypeToDbType(Type Source);
}

There are already a number of DataProvider descendant classes (MsSqlProvider, FirebirdProvider, etc.) in Tripous and the static constructor of the DataProviders class registers them all. You may craft your own DataProvider classes and register them the same way. Send your DataProvider class to me and I will add it to the official Tripous sources so others may benefit from your work.

The DataProvider class is basically used in creating DbConnection, DbCommand instances and the like.

In Tripous, a DataProvider class is uniquely identified by its Alias, a string. The Tripous.Data.ConnectionStringBuilder helper class provides public constants for all provider aliases. Some DataProviders know how to create a new database. All of them know how to "format" a connection string.

A Tripous connection string is identical to an ADO.NET connection string, plus an Alias entry. Here is a Tripous connection string for MsSql.

Alias=MsSql; Data Source=localhost; Integrated Security=SSPI; Initial Catalog=DevApp

The ConnectionStringBuilder provides public constants with predefined connection string formats for the most common cases. Here is the one for MsSql.

Alias=MsSql; Data Source={0}; Integrated Security=SSPI; Initial Catalog={1};

The FormatConnectionString() method of the DataProvider or the ConnectionStringBuilder class can be used to easily create those Tripous connection strings.

DataProvider Provider = DataProviders.Find(Alias);
if (Provider != null)
{
    string ConnectionString = Provider.FormatConnectionString
				(HostComputer, FileOrDatabaseName);
    MessageBox.Show(ConnectionString);
}

The DataProvider.CreateDatabase() can be used to create a new database, provided that the DataProvider supports that operation. In that case, the CanCreateDatabases returns true.

DataProvider Provider = DataProviders.Find(Alias);
if (Provider != null)
{
    Provider.CreateDatabase(ServerName, DatabaseName, UserName, Password);
    MessageBox.Show("DONE");
}

Tripous DataProvider class has a number of useful read-only properties and a number of helper methods.

Warning: If you are going to use Firebird, you have to install it first and copy FirebirdSql.Data.FirebirdClient.dll from the ThirdParty Tripous folder to your bin folder.

Creating Database Schema

In Tripous parlance, the word datastore means a set of information that describes a database connection and attributes under a unique name. There is a Datastore class too and a static Datastores class which is a collection of all registered Datastore items.

Tripous provides a set of classes used to easy the creation and update of database schema. SchemaDatastores, SchemaDatastore and SchemaTable are the most important.

A SchemaDatastore instance represents a specific version of a schema of a specific datastore. You use the datastore name, say "MAIN", and a version number. SchemaDatastores is a collection of SchemaDatastore items and provides an Execute() method for applying those changes to the database or databases.

For each SchemaDatastore item registered under a certain name in the SchemaDatastores there must be a Datastore registered to Datastores under that same name. Otherwise, the SchemaDatastores.Execute() fails.

A successful call to SchemaDatastores.Execute() creates the registered schema plus an "ini" table, namely SYS_INI, that resembles a Microsoft Windows *.ini file. The Datastore class provides an Ini property of type DbIni.

That DbIni class provides some handy methods such as the following:

public class DbIni
{
    ...
    
    public void WriteString(string Entry, string Value);
    public void WriteInteger(string Entry, int Value);
    public void WriteFloat(string Entry, double Value);
    public void WriteDateTime(string Entry, DateTime Value);
    public void WriteBool(string Entry, bool Value);
    
    public string ReadString(string Entry, string Default);
    public int ReadInteger(string Entry, int Default);
    public double ReadFloat(string Entry, double Default);
    public DateTime ReadDateTime(string Entry, DateTime Default);
    public bool ReadBool(string Entry, bool Default);
    
    ...
    ...
}

After a SchemaDatastores.Execute() execution, there is an entry in that ini table regarding the just created schema and its version.

Let's go back to the database creation.

In order to create database tables in a database, you first create a SchemaDatastores instance. Then you Add() a SchemaDatastore under a certain name and version. Except Add() SchemaDatastores provides the ForceFind() method which returns the first SchemaDatastore with the specified Name and having a version equal to a specified Version, if any. If no such SchemaDatastore exists, a new one is created and returned.

Here is a method that creates a new empty Sqlite database, registers a Datastore, creates a SchemaDatastores and a SchemaDatastore, adds a SchemaTable and creates the schema in the database.

        void CreateDatabase()
        {          
            
            string DatabaseName = Path.GetFullPath(@"..\..\bin\Debug\Test.db3");
            if (!File.Exists(DatabaseName))
            {
                string Alias = ConnectionStringBuilder.Sqlite;
                DataProvider Provider = DataProviders.Find(Alias);

                /* create the database */
                Provider.CreateDatabase(string.Empty, DatabaseName, 
			string.Empty, string.Empty);

                /* add a Datastore */
                string ConnectionString = Provider.FormatConnectionString(DatabaseName);
                Datastores.Add(Sys.MAIN, Alias, ConnectionString);

                /* schema */
                int Version = 1;
                SchemaDatastores SchemaDatastores = new SchemaDatastores();
                SchemaDatastore Schema = SchemaDatastores.Add(Sys.MAIN, Version);

                string TableName = "Person";
                string SqlText = @"
create table Person (
   Id                        @PRIMARY_KEY
  ,Name          varchar(32) @NOT_NULL
  ,Birthday      @DATE       @NULL
  ,Salary        double      @NULL
  ,Rank          integer     @NULL
  ,Notes         @BLOB_TEXT  @NULL
);
";
                Schema.AddTable(TableName, SqlText);

                /* create the table */
                SchemaDatastores.Execute();
            }
        }

As you may see, the create table script contains some placeholders. Those words start with an at (@) character. Tripous strives to be server neutral. Those placeholders serve that purpose. The DataProvider.ReplaceDataTypePlaceholders() method replaces those placeholders according to the actual server used.

Please feel free to dig into those classes because there is a lot more to discover. And hopefully you may find any bug and send me some code to correct it. 

Datastores, Datastore and Executor 

Datastores class is a static class that acts as a register for Datastore instances.

public static class Datastores
{
    public static int Count { get; }
    public static Datastore Main { get; }
    
    public static void Add(Datastore Datastore);
    public static Datastore Add
	(string Name, string ProviderAlias, string ConnectionString);
    public static Datastore ByIndex(int Index);
    public static Datastore ByName(string Name);
    public static bool CanCreateDatabase(Datastore Datastore);
    public static bool CanCreateDatabase(string ConnectionString);
    public static void Clear();
    public static bool Contains(string Name);
    public static bool CreateDatabase(Datastore Datastore);
    public static bool DatabaseExists(Datastore Datastore);
    public static Datastore Find(string Name);
    public static void LoadFrom(DatastoreDescriptors Descriptors);
    public static string NextDatastoreName(string Prefix);
    public static void Remove(Datastore Datastore);
    public static void Remove(string Name);
    public static Datastore[] ToArray();
}

The most significant method is the Add() method.

The Datastore class is an abstract class. There are two descendants: the AdoNetDatastore and the ClientDatastore class.

Let's forget the ClientDatastore class for now. Just to say that it uses XML TCP commands to send its requests to an AdoNetDatastore through a socket connection. In short, it acts as a client side proxy to a server side AdoNetDatastore. You may use ClientDatastore from a Pda (Compact Framework) application in order to access a remote database, the same way you access a database in a desktop system.

Warning: The Datastores.Add(string Name, string ProviderAlias, string ConnectionString) creates AdoNetDatastore instances only.

Here is the Datastore class:

public abstract class Datastore
{
    public abstract bool ConnectionChecked { get; }
    public virtual DbIni Ini { get; }
    public abstract MidwareType MidwareType { get; }
    public abstract string Name { get; }
    public abstract OidMode OidMode { get; }
    public Dictionary<string, DataTable> SchemaTables { get; }
    public abstract ServerType ServerType { get; }
    public abstract bool SupportsGenerators { get; }
    public abstract bool SupportsTransactions { get; }
    public object SyncRoot { get; }
    public virtual int Version { get; }
    
    public abstract Executor CreateExecutor();
    public abstract void EnsureConnection();
    public void GeneratorAdd(string GenaratorName);
    public virtual bool GeneratorExists(string GenaratorName);
    public abstract DataTable GetSchema();
    public abstract DataTable GetSchema(string collectionName);
    public abstract DataTable GetSchema
	(string collectionName, string[] restrictionValues);
    public virtual string QSDate(DateTime Value);
    public override string ToString();
}

It's a poor class as you may see. Just a set of read-only informational properties and a few methods. As said before, the Datastore class represents a connection to a database under a unique name, unique among the other registered Datastore items, and some characteristics. The most important method is the CreateExecutor() which creates and returns an Executor instance. The Executor class is the most important class in this level.

public abstract class Executor
{
    public Executor(Datastore Datastore);
    
    public Datastore Datastore { get; }
    public virtual MidwareType MidwareType { get; }
    public virtual OidMode OidMode { get; }
    public virtual ServerType ServerType { get; }
    public virtual bool SupportsGenerators { get; }
    public virtual bool SupportsTransactions { get; }
    
    public abstract DataTransaction BeginTransaction();
    public abstract Executor CreateExecutor();
    
    public void CreateGenerator(string GeneratorName);
    public abstract bool CreateTable(string SqlText);
    
    public DataTable Select(string SqlText);
    public virtual DataTable Select(string SqlText, params object[] Params);
    public DataTable Select(DataTransaction Transaction, string SqlText);
    public abstract DataTable Select
	(DataTransaction Transaction, string SqlText, params object[] Params);
    
    public int SelectTo(DataTable Table, string SqlText);
    public int SelectTo(DataTable Table, string SqlText, params object[] Params);
    public int SelectTo(DataTransaction Transaction, DataTable Table, string SqlText);
    public int SelectTo(DataTransaction Transaction, 
	DataTable Table, string SqlText, params object[] Params);
            
    public void Exec(string SqlText);
    public virtual void Exec(string SqlText, params object[] Params);
    
    public void Execute(DataTransaction Transaction, string SqlText);
    public abstract void Execute(DataTransaction Transaction, 
	string SqlText, params object[] Params);
    
    public void ExecuteNested(DataTransaction ParentTransaction, string SqlText);
    public void ExecuteNested(DataTransaction ParentTransaction, 
	string SqlText, params object[] Params);
    
    public DataRow SelectResults(string SqlText);
    public DataRow SelectResults(DataTransaction Transaction, string SqlText);
    public DataRow SelectResults(string SqlText, params object[] Params);
    public DataRow SelectResults(DataTransaction Transaction, 
	string SqlText, params object[] Params);
    
    public object SelectResult(string SqlText, object Default);
    public object SelectResult(DataTransaction Transaction, 
	string SqlText, object Default);
    
    public int IntegerResult(string SqlText, int Default);
    public int IntegerResult(DataTransaction Transaction, string SqlText, int Default);
    
    public bool TableExists(string TableName);
    public bool TableIsEmpty(string TableName);
    public bool FieldExists(string TableName, string FieldName);
    public bool IndexExists(string IndexName);
    public bool GeneratorExists(string GeneratorName);
    
    public abstract IList<string> GetFieldNames(string TableName);
    public abstract IList<string> GetIndexNames();        
    public abstract IList<string> GetTableNames();
    
    public void GetNativeSchema(string SqlText, string TableName, 
	string SchemaName, DataTable Table);
    
    public int LastId(string TableName);
    public int LastId(DataTransaction Transaction, string TableName);
    public int NextId(string TableName);
    public int NextId(DataTransaction Transaction, string TableName);
    public int NextIdByGenerator(string GeneratorName);
    public int NextIdByGenerator(DataTransaction Transaction, string GeneratorName);
    
    public void ResetTable(string TableName);
    public void ResolveSql(ref string SqlText);
    
    public void SetGeneratorTo(string GeneratorName, int Value);
    public void SetTableGeneratorTo(string TableName, int Value);        
}

In Tripous, an application programmer never uses classes such as DbConnection, DbCommand, etc. directly. It uses a Datastore and an Executor.

As with the Datastore class, there is an AdoNetExecutor and a ClientExecutor class.

You never create an Executor directly. You always use a Datastore.CreateExecutor() call.

The Executor class can execute SELECT statements, using the various SelectXXX() methods and INSERT, UPDATE, DELETE, CREATE TABLE, etc. statements using the Exec() and the Execute() methods. Some of those overloads accept a last parameter marked with the params keyword. Executor has no knowledge in handling parameters. Instead, it passes that information to its DataProvider SetupCommand() and AssignParameters() methods.

As the documentation of the AssignParameters() method states, params can be:

• either a comma separated list of parameters
• or the Params[0] element, that is the first element in Params, may be a DataRow, generic IDictionary, IList or Array and in that case no other Params elements are used.

If you pass a DataRow, then Tripous uses Column names to match SQL parameter names. If you pass an IDictionary, then Tripous expects to be a generic one where the first type argument is string, the SQL parameter name, and the second is object, the value. In all other cases, a positional logic is used.

Here is a private method from a demo form that inserts a row in a database table using an Executor.

        void InsertRow()
        {
            EnsureDatastore();

            if (tblParams == null)
            {
                string TableName = "Person";
                string SchemaName = string.Empty;
                string SqlText = string.Empty;

                tblParams = new DataTable(TableName);

                executor.GetNativeSchema(SqlText, TableName, SchemaName, tblParams);
            }

            tblParams.Rows.Clear();

            DataRow Row = tblParams.NewRow();
            tblParams.Rows.Add(Row);

            Row["Name"] = edtName.Text;
            Row["Birthday"] = edtBirthday.Value;
            Row["Salary"] = edtSalary.Text;
            Row["Rank"] = edtRank.Text;
            Row["Notes"] = edtNotes.Text;


            string SqlInsert = @"
insert into Person 
(
    Name
   ,Birthday
   ,Salary
   ,Rank
   ,Notes
)
values
(
    :Name
   ,:Birthday
   ,:Salary
   ,:Rank
   ,:Notes
)
";

            executor.Exec(SqlInsert, Row);

            RefreshData();
        }

The above method prepares a DataTable with a single DataRow. That DataRow is used as the second parameter in an Executor.Exec() call that executes a parameterized SQL INSERT statement.

The Executor.GetNativeSchema() is used in fetching schema information from the database regarding a table or a SELECT statement.

Constructing SQL Statements for a Database Table

Tripous.Data.Db class is a static helper class containing some handy methods. The BuildSql() can build SQL statements (SELECT, INSERT, UPDATE, DELETE). Here is how to use it.

void BuildSql()
{
    SqlStatements Statements = new SqlStatements();
    Db.BuildSql("Person", "Id", false, executor, Statements, true);
    
    StringList List = new StringList();
    List.Add("================= Insert ================= ");
    List.Add(Statements.Insert);
    List.Add("");
    List.Add("================= Update ================= ");
    List.Add(Statements.Update);
    
    Sys.CourierBox(List.Text);
}

The BuildSql() needs an SqlStatements instance, among other parameters. After the call, that SqlStatements instance is filled up with the statements.

The StringList is just a handy string list class used in many places by Tripous.

The Sys static class sits on top of the Tripous system, and it is just a big collection of useful utility methods and properties.

History

• 6^th June, 2010: Initial post