HLinq -ORMapping tool -

Download HLinqSample.zip - 1.21 MB

Overview

You can get HLinq at codeplex
HLinq is a ORM framework like Linq to sql.HLinq generate stored procedure and csharp source code from schema file.The schema file has a definition of table,column,relation of database. You can edit this file with HLinqSchemaEditor.HLinq generate sqlscript file for sqlserver2005,sqlserver2008 and csharp source code for .NET3.5. Sqlscript file includes create statement of table,relation,and stored procedure. Stored procedure is generated from table schema and relation. You will be able to operate record of table with these stored procedure.For example,TableNameSelectAll,TableNameSelectBy_PrimaryKey,TableNameInsert,TableNameUpdate,TableNameSelectBy_ColumnName..etc. Additional, you can add your custom stored procedure with HLinqSchemaEditor.
HLinq also generate accessible csharp source code,that contain database class,stored procedure class,resultset class,table class,and record class. You can call stored procedure with stored procedure class and get query resultset as List<T> of resultset class's instance. You can operate record value with record class,and submit your operation to database with table class very easily.

Index of document

About document index.

• Quick start
• Quick start with existing database
• HLinqSchemaEditor
• Generated files overview
• How to create dll
• Coding of CRUD operation
• Transaction
• Custom stored procedure
• Database column mapping to C#Enum
• On memory cache
• Multi database
• Validation
• Meta data of database schema
• Interface
• Class extension
• TimeZone
• OnPropertyChanging partial method
• StringEqualOperator
• Replace IDatabase object
• Customize IDbCommand initialization

Quick start

At first,you run HLinqSchemaEditor.exe.



Select database type,and push ok button.



Next,input ApplicationName,NamespaceName,ClassName.

• ApplicationName---This name is used for T-SQL script file naming.
• Namespacename---That will be a root namespace of generated C# files.
• Classname---That will be a class name of database class.

Input table information.



Input column information.



File->Save,you can save as xml file.
Tool->ObjectGenerator,select directory for output files,and push ok button.
Folder and files are generated as below.



Create database and execute script file to this database.Table,stored procedure,relation would create.



Create C# class library project by visual studio and add files of red marked directory show in below.



Add reference of Higuchi.Core.dll,Higuchi.Data.dll,Higuchi.HLinq.dll,and build as HLinqSampleDatabase.
Create new windows form project and add reference of Higuchi.Core.dll,Higuchi.Data.dll,Higuchi.HLinq.dll,HLinqSampleDatabase.dll.



Now you can write CRUD code as below.

Quick start with existing database

At first,you run HLinqSchemaEditor.exe,select databasetype and Tool->SchemaFileGenerator.
Input connection information and push read button.Table,storedprocedure,relation will load to listbox,you select object to create schema.
Tool->ObjectGenerator,select directory for output files,and push ok button.
And do same operation above QuickStart tutorial.

Generated files overview

All generated files are shown in below.

Specification html file is like below.CustomStoredProcedure html is now under construction.I would release it future version.

You can create table,stored procedure,relation object to database with script files in SqlServer folder. And you can access these database object with files in CSharp folder.

How to create dll

There is many complilation pattern.I will show you these pattern with below diagram.
There is sometimes slow when you load big size dll.
You can avoid slow loading of your application with selecting source files in your requirement.

• only stored procedure class(except for custom stored procedure)
• only stored procedure class(including custom stored procedure)
• table & record class(except for custom stored procedure)
• table & record class(including custom stored procedure)
• add functionality of cache update to pattern4
• add functionality of Json serialization to pattern4
• add functionality of table schema information to pattern4
• Record class and schema information(for silverlight)
• only include Table1 and Table1.Record class(Table1.dll)
• only include Database class(Database.dll)
• create new dll with Table1.dll,Database.dll
• create new dll with Database.Core.cs,Table1.dll
• include all files

Coding of CRUD operation

At first,you must initialize database object and set connection string.

DB.Register();
DB.Default.ConnectionString = "myConnectionString"; 

You can get data as Record object from database.

Guid MyGuid = new Guid("402d222c-4a9f-4808-9285-d14e88edba28");
MUser.Record rUser = DB.Default.MUser.SelectBy_PrimaryKey(MyGuid); 

Since Record class is strong typed,intellisence tell you the column name of table.

You can get all record data using SelectAll method of Table class.

List<MUser.Record> rUserList = DB.Default.MUser.SelectAll();
List<MUser.Record> rUserList = MUser.SelectAll(DB.Default); 

SelectBy_PrimaryKey method throw exception when no record is selected.To prevent this,you assign second parameter false.

MUser.Record rUser = DB.Default.MUser.SelectBy_PrimaryKey(MyGuid, false);

To insert record

MUser.Record rUser = new MUser.Record();
rUser.UserCD = Guid.NewGuid();
rUser.FamilyName = "Higuchi";
rUser.FirstName = "Shoutaro";
rUser.Birthday = new DateTime(1978, 4, 16);
rUser.UserType = MUser.UserType.Generic;
rUser.Insert();

To update record

MUser.Record rUser = DB.Default.MUser.SelectBy_PrimaryKey(MyGuid);
rUser.UserType = MUser.UserType.Special;
rUser.Update();

To delete record

MUser.Record rUser = DB.Default.MUser.SelectBy_PrimaryKey(MyGuid);
rUser.Delete();

Transaction

You can insert multi record using Insert method overload version of Table class.

List<MUser.Record> rUserList = new List<MUser.Record>();
MUser.Record rUser = null;

rUser = new MUser.Record();
rUser.UserCD = Guid.NewGuid();
rUser.FamilyName = "Higuchi";     
rUser.FirstName = "Shoutaro";
rUser.Birthday = new DateTime(1978, 4, 16);             
rUser.UserType = MUser.UserType.Generic;
rUserList.Add(rUser);
rUser = new MUser.Record();       

rUser.UserCD = Guid.NewGuid();
rUser.FamilyName = "Tiger";     
rUser.FirstName = "Woods";
rUser.Birthday = new DateTime(1970, 6, 16);             
rUser.UserType = MUser.UserType.Temporary;
rUserList.Add(rUser);
DB.Default.MUser.Insert(IsolationLevel.ReadCommitted, rUserList);

You can insert header detail records using InsertWithChildRecord method of Table class.

DSale.Record rSale = null;
DSaleItem.Record rSaleItem = null;

rSale = new DSale.Record();
rSale.SaleCD = Guid.NewGuid();
rSale.UserCD = MyUserCD;                       
rSale.SaleTime = DateTime.Now;

rSaleItem.SaleCD = new DSaleItem.Record(Guid.NewGuid(), MyItemCD1);
rSaleItem.ItemPrice = 136;
rSaleItem.ItemCount = 2;
rSale.DSaleItemRecords.Add(rSaleItem);

rSaleItem.SaleCD = new DSaleItem.Record(Guid.NewGuid(), MyItemCD1);
rSaleItem.ItemPrice = 147;
rSaleItem.ItemCount = 1;
rSale.DSaleItemRecords.Add(rSaleItem);

DB.Default.DSale.InsertWithChildRecord(rSale, IsolationLevel.ReadCommitted);

UpdateWithChildRecord method is also defined in Table class.
You can execute insert record,update record,execute stored procedure in same transaction using save method of Database class.

List<ITransaction> l = new List<ITransaction>();
rSale.CommandMode = CommandMode.Insert;
///set property...
l.Add(rSale);
rSale.CommandMode = CommandMode.Update;
///set property...
l.Add(rSale);
MUser_UserType_Update sp = new MUser_UserType_Update();
sp.UserType = MUser.UserType.Generic;
l.Add(sp);
///execute same transaction...
DB.Default.Save(IsolationLevel.ReadCommitted, l.ToArray());

Other way,use TransactionContext class.In the using statement bracket,all Insert,Update,Delete method of Table class and Record class,and ExecuteCommand,GetResultSet method of StoredProcedure executed on same transaction.

using (TransactionContext tx = new TransactionContext(IsolationLevel.ReadCommitted)
{
	///create MUser.Record object and set property...
	rUser.Insert();
	MUser_UserType_Update sp = new MUser_UserType_Update();
	sp.UserType = MUser.UserType.Generic;
	sp.ExecuteCommand();
	tx.Commit();
}

If you don't want to join transaction,you set JoinTransaction property of Record class or StoredProcedure class.
By default,JoinTransaction property is false of StoredProcedure of Select data

rUser = new MUser.Record();
(rUser as ITransaction).JoinTransaction = false;
MUser_UserType_Update sp = new MUser_UserType_Update();
(sp as ITransaction).JoinTransaction = false;

Custom stored procedure

You can add custom stored procedure with HLinqSchemaEditor.Run HLinqSchemaEditor View->StoredProcedure and input information.



Click edit body text,and write sql script that you want to execute.



Tool->ObjectGenerator and execute generate,you can find new file in CustomStoredProcedure folder.
Add this file to project and compile,you can use CustomStoredProcedure class.

If you want to create a stored procedure that has some result set,input result set information



You can easily add column Edit->CopyColumn




This functionality provide you perfect control to database what you want to do.And you can call this stored procedure easily with generated C# files.

Database column mapping to C#Enum

HLinq can map database column to Enum of C#.
Select Enum and click right button.



Input enum information.



C# source code of Enum would generate MUser.cs in Core folder.



UserType property of MUser.Record is strong typed by MUser.UserType enum.



If you select UserType column type to int,a interger value parse from enum is send to database.
And if you select UserType column type to string,string value getting by enum.ToString() is send to database.

You can map existing C# enum such as DayOfWeek.To do this,you input DayOfWeek to EnumName column and select Default at EditType.

On memory cache

You can cache table data in memory.To cache data on memory,you write such code.

DB.Default.MUser.IsCache = true;
DB.Default.MUser.LoadData();

Get record from cache,write below.

List<MUser.Record> rUserList = DB.Default.MUser.SelectAll();

You don't need to change any code when getting data from database.It's perfectly transparent.

To get data from database,write below.

List<MUser.Record> rUserList = DB.Default.MUser.SelectAll(false);

You must update cache data when database data is updated.C# source code of Dependency folder provide cache dependency functionality.

To register dependency event,write below.

DB.Default.DependencyChanged("MUser", null, CacheItemRemovedReason.DependencyChanged);

You must be careful to select table to cache.To cache or not,you must consider these things.

• Few data in table
• Not frequently changed

To cache a table that has many data,you might be involved memory consumption trouble.
And to cache frequently changed table,LoadData method would be called very frequently and it might cause performance issue.
But with proper use of this cache functionality,you can make significant improvement in your application's performance.

Multi database

Multi database has two pattern.Same schema database or not.
To access other database instance that has same shema,write code as below.

DB.Register("Japan", "Japan database connection string");
DB.Register("USA", "USA database connection string");
var rJapanUserList = DB.List["Japan"].MUser.SelectAll();
var rUSAUserList = DB.List["USA"].MUser.SelectAll();

If database doesn't have same schema,you would create xml file with HLinqSchemaEditor and assign other class name such as DBLog.
And write code as below.

DBLog.Register();
DBLog.Default.ConnectionString = "log database connection string";
var l = DBLog.Default.LogTable.SelectAll();

Validation

Record class has static Validation property that provide validation rule.You can customize this validation rule with your requirement.
If you change validation rule of Birthday column,you write lambda expresion shown below.

MUser.Record.Validation.Birthday = r => r.Birthday.Year > DateTime.Now.AddYear(-20);

And add multi column related validation rule,use RecordValidatoins property.

MUser.Record.Validation.RecordValidations.Add(r => r.UserType != MUser.UserType.Special || r.Organization != null);

Validation class has indexer.You can access each column's validation with column name.
Validation class defined event and the event would be fired when validatoin is caused.
You can get Event<ValidationResult> object from second parameter of event handler.
You can known which validation of column failure with this object.

Meta data of database schema

Three class generated for providing meta data,Table.Column class,StoredProcedure.ParameterSchema class,StoredProcedure.ResultSet class.

You can get ColumnSchema<T1, T2> from these classes.T1 is a DbType of database,T2 is a C# type of column.

Interface

• Database---IHLinqDatabase
• Table---ITableRecordService
• Record---ITransaction,INotifyChanged,TableName.IRecord,IDatabaseRecord,IIndexer<String, Object>,IIndexer<Int32, Object>
• StoredProcedure---ITransaction,IStoredProcedure
• StoredProcedure that has resultset---IStoredProcedureResultSet
• StoredProcedure that doesn't have resultset---IStoredProcedureCommand
• ResultSet---TableName.IRecord,IResultSet,IIndexer<String, Object>,IIndexer<Int32, Object>

If you add file in Dependency folder,these interface is added.

• Database---IHLinqDatabaseCache
• Table---ICacheTable

Database class implement IHLinqDatabase.This interface have indexer.

You can get ITableRecordService object by assignig table name to this indexer.

If you add Table.cs in dependency folder,Table class implement ICacheTable interface.

You can operate IDatabase object by using method of ITableRecordService interface.
IDatabase record represent a record of database.

You would use IStoredProcedure interface to access stored procedure.

If stored procedure has result set,the StoredProcedure class implement IStoredProcedureResultSet interface.

And the result set class implement IResultSet class.

If stored procedure does not have result set,the StoredProcedure class implement IStoredProcedureCommand interface.

ITransaction is implemented by Record class and StoredProcedure class.That class do great work under the hood on transaction process.

IIndexer<K, V> is a great interface that enable us to access class transparently that has indexer.
Record class,StoredProcedure classs,ResultSet class implement IIndexer<String, Object>,IIndexer<Int32, Object>.

INotifyChanged interface enable to bind these object to control and other bindable object.
Table class implement IEnuberable<Record>.You can enumerate all record by this interface.
And StoredProcedure class implement IEnuberable<ResultSet>.You can enumerate all resultset by this interface with DataReader.

Class extension

All class are generated as partial class,so you can add your own functionality to these classes.
For example,you want to add DisplayName property to MUser.Record class,you write code such as...

public partial class MUser
{
	public partial class Record
	{
		public String DisplayName
		{
			get { return this._FamilyName + " " + this._FirstName; }
		}
	}
}

And you also add interface implementation to CustomStoredProcedure.

public partial class MUser_SelectBy_BirthdayYear
{
	public partial class ResultSet : MUser.IRecord { }
}

TimeZone

HLinq provide timezone support.To create world wide application,you might support different timezone.
The basic theory is when you save DateTime data to database,you change the DateTime object to UTC timezone.
And get DateTime data from database,you change the DateTime to Local timezone that your application is executing.
HLinq has a layer to execute DateTime translation.To use this functionality,you write as below.

DB.Default.SetDateTimeConverterToDatabase(d => d.ToUniversalTime());
DB.Default.SetDateTimeConverterFromDatabase(d => d.ToLocalTime());

ConvertDateTimeToDatabase method and ConvertDateTimeFromDatabase method are called inside of StoredProcedure class.
You can control all DateTime object pass to or from database,and easily resolve timezone issue.

OnPropertyChanging partial method

Inside Record class property,OnPropertyChanging partial method is defined.You can hook value and manipulate it.
In MUser.Record class,OnFamilyNameChanging method is defined as partial method.

set
{
	this.OnFamilyNameChanging(ref value);
	if (this._FamilyName != value)
	{
		this._FamilyName = value;
		this.OnPropertyChanged("FamilyName");
	}
}

StringEqualOperator

When you get data from cache,String is compare case sensitive.You can customize compare operation with StringEqualOperator property of Database class.

DB.Default.StringEqualOperator = (x, y) => String.Equals(x, y, StringComparison.CurrentCultureIgnoreCase);

Replace IDatabase object

Under the hood,StoredProcedure class use IDatabase object by calling Func<IDatabase> that get from CreateDatabase property of Database class.
Here is a diagram of IDatabase in below.

You can replace IDatabase object which StoredProcedure class would use.At first,create class and implement IDatabase.
Next replace by set CreateDatabase property of Database class.

DB.Default.CreateDatabase = () => new YourIDatabaseClass();

An implementaion example is in SqlServerDatabase class.That is included in Higuchi.Data.dll of Higuchi.Core project on codeplex

Customize IDbCommand initialization

You can replace IDbCommand initialization logic.Same as above IDatabase,you use CreateDefaultCommand property of Database class.

DB.Default.CreateDefaultCommand = () => new SqlCommand() { CommandTimeout = 90; };

History

2007.12.21 first post 2009.10.07 updated