Download latest source code from GitHub

NEWS

2^nd February, 2024: I released a new version for .NET8. Please check out this article:

DbSharpApplication - C# Source Code Generator of DAL for Stored Procedure on .NET8

31^st December, 2021: I updated all source code to .NET6. Please check it out!

DbSharp on .NET6 supports DateOnly, TimeOnly to Date, Time type of database.
DbSharp removes the support for Microsoft.Data.SqlTypes because they are not supported on .NET6.
You can download DbSharpApplicationXXXXXXXX.zip.
https://github.com/higty/higlabo/tree/master/Net6/Tools/DbSharp/Compiled

Please download latest version .zip file. I continue to fix some bugs these days.

Summary

This article teaches you about OOP, TestFirst, SRP, by creating DAL generator (not ORM ...) to call stored procedures easily. I will show you the implementation. As a starting point, please know that I use Visual Studio 2022, and SQL Server.

DbSharp is a DAL Generator. It generates a StoredProcedure client class that enables you to call stored procedure easily. DbSharp also creates Table and Record classes that enables you to CRUD operation.

We released .NET6 version to GitHub and Nuget.

Table of Contents

How to Use DbSharp?
Test First Development
Single Responsibility Principle
StringBuilder vs TextWriter
Design StoredProcedure Class
Read Schema from Database (including SqlGeometry,SqlGeography,HierarchyId)
Enum Support
UserDefinedType
Async
Multi ResultSet
Design API for CRUD Operation
Design Table, Record Classes
Get Schema from Database
Record Class Feature and Implementation
Table Class Feature and Implementation
Indentity, RowGuid, Timestamp Column
Deep Dive to DatabaseContext and Database Class
Advanced Usage
Alternative Library
Conclusion
.NET6 Version Release (NEW!!!)
History

You may use other ORM libraries (NHibernate, EntityFramework, DataObject.net) than DbSharp, but if you like DbSharp, it is free to use. I greatly appreciate your feedback.

How to Use DbSharp?

You can download DbSharpApplication on .NET6 from the link below:

https://github.com/higty/higlabo/tree/master/Net6/Tools/DbSharp/Compiled

Sample code:

https://github.com/higty/higlabo/tree/master/Net6/Tools/DbSharp/HigLabo.DbSharp.Sample

Here is a list to use DbSharp:

Setup sample database
Launch DbSharpApplication.exe
Select DatabaseType(SqlServer, MySql) and input connection string
Import StoredProcedure, UserDefinedType, Table. MenuBar->Edit->Import XXX
Generate C# code. MenuBar->Edit->Generate C# Code
Create class library project by Visual Studio, add generated file and compile it to DLL
Use DLL in your application

In the sample project (DbSharpSample.sln), you can see the solution files include:

HigLaboSample.Data.MySql.CSharp (ClassLibrary with generated files)
HigLaboSample.Data.SqlServer.CSharp (ClassLibrary with generated files)
HigLaboSampleApp.MultiDatabase (some of Console application...)
HigLaboSampleApp.MySql (for MySql)
HigLaboSampleApp.SqlServer (for SqlServer)

These projects are created by the following steps:

First, I create a DbSharpSample database by Management Studio and execute script DbSharp\HigLaboSample.Data.SqlServer.CSharp\DbSharp_SqlServer.sql to this database.

Then launch DbSharpApplication.exe and select target database to SqlServer.

Select MenuBar->Edit->Manage Connection.
Add connection string of your database.

Select MenuBar->Edit->Read Database Scheme

Press Connect button, ensure all objects selected and press Execute button.

Table, StoredProcedure, UserDefinedTableType are imported.
You can see imported Table by clicking Table tab.

You can see imported StoredProcedure by clicking StoredProcedure tab.

You can see imported UserDefinedTableType by clicking UserDefinedTableType tab.

When you import table, 5 StoredProcedures per 1 Table are generated for CRUD operation.

SelectAll
SelectByPrimaryKey
Insert
Update
Delete

You can see these StoredProcedures by clicking StoredProcedure tab.

You can manage Enum type to column. Set EnumName=MyEnum to EnumColumn, NotNullEnumColumn of AllDataTypeTable.

You can confirm EnumName is set automatically to generated StroedProcedure.

You can see Usp_SelectMultiTable return multiple ResultSets to client.

You can change these class names from ResultSetX to your own name.

Save these schema as file by MenuBar->File->Save. You can load your file by MenuBar->File->Open File.

Now you can generate C# source code from these schema by MenuBar->Edit->Generate C# Code.

Set the path where to output C# source code files
RootNamespace of generated files
DatabaseKey

I will explain about DatabaseKey later. Press the Execute button and C# files are generated in the output directory path.

Create new class library project, and add these generated files:

Define MyEnum like this:

    public enum MyEnum
    {
        Default,
        Value1,
        Value2,
    }

And add MyEnum to class library:

Add reference to HigLabo.Core, HigLabo.Data, HigLabo.DbSharp. If you use SqlGeometry, SqlGeography, HierarchyId, you must add Microsoft.SqlServer.Types.

And compile it. HigLaboSample.Data.SqlServer.CSharp.dll is created.

Create new console application named HigLaboSampleApp.SqlServer, add reference HigLabo.Core, HigLabo.Data, HigLabo.DbSharp, Microsoft.SqlServer.Types(optional...), HigLaboSample.Data.SqlServer.CSharp.

Now you can execute like this:

    var ss = Environment.GetCommandLineArgs();
    String connectionString = ss[1];
    DatabaseFactory.Current.SetCreateDatabaseMethod
         ("DbSharpSample", () => new SqlServerDatabase(connectionString));

    AllDataTypeTable t = new AllDataTypeTable();
    var r = new AllDataTypeTable.Record();
    r.PrimaryKeyColumn = 11;
    r.IntColumn = 2;
    //Set properties...
    var x1 = t.Insert(r);

You may read later sections with DbSharpSample.sln to understand DbSharp.

Test First Development

This section explains "test first" development by creating a test case for the generated C# code. This section's goal is to generate a sample class as shown below:

    public class Person
    {
        private Int32 _Age = 0;

        public String Name { get; set; }
        public Int32 Age
        {
            get { return _Age; }
        }
        public List<Person> Children { get; private set; }

        public Person(String name)
        {
            this.Children = new List<Person>();
            this.Name = name;
        }
        public void ShowName()
        {
            Console.WriteLine(this.Name);
        }
        public void AddAge(Int32 value)
        {
            _Age += value;
        }
    }

I divide this class like below elements and create generator for each element:

TypeName
Field
AccessModifier
MethodAccessModifier
FieldModifier
ConstructorModifier
Constructor
MethodModifier
MethodParameter
Method
PropertyBody
Property
ClassModifier
Class
InterfaceProperty
InterfaceMethod
Interface
Namespace
SourceCode

TypeName covers the name of Type such as Int32, Person, List<String>, Dictionary<String, List<Person>>. At first, I created a test case about TypeName class (see HigLabo.CodeGenerator.Version0 project).

    [TestClass]
    public class TypeNameTest
    {
        [TestMethod]
        public void TypeNameWithoutGenericTypes()
        {
            var tp = new TypeName("Int32");
            Assert.AreEqual("Int32", tp.Write());
        }
        [TestMethod]
        public void TypeNameWithGenericTypes()
        {
            var tp = new TypeName("Func");
            tp.GenericTypes.Add(new TypeName("String"));
            tp.GenericTypes.Add(new TypeName("Int32"));
            Assert.AreEqual("Func<String, Int32>", tp.Write());
        }
        [TestMethod]
        public void TypeNameWithNestedGenericTypes()
        {
            var tp = new TypeName("Func");
            tp.GenericTypes.Add(new TypeName("String"));
            var tp1 = new TypeName("Action");
            tp1.GenericTypes.Add(new TypeName("String"));
            tp1.GenericTypes.Add(new TypeName("Int32"));
            tp.GenericTypes.Add(tp1);
            Assert.AreEqual("Func<String, Action<String, Int32>>", tp.Write());
        }
    }

I created TypeName class to manage type info and generate source code to pass test case.

    public class TypeName
    {
        public String Name { get; set; }
        public List<TypeName> GenericTypes { get; private set; }
        public TypeName(String name)
        {
            this.Name = name;
            this.GenericTypes = new List<TypeName>();
        }
        public String Write()
        {
            StringBuilder sb = new StringBuilder();

            sb.Append(this.Name);
            if (this.GenericTypes.Count > 0)
            {
                sb.Append("<");
                for (int i = 0; i < this.GenericTypes.Count; i++)
                {
                    var tp = this.GenericTypes[i];
                    sb.Append(tp.Write());
                    if (i < this.GenericTypes.Count - 1)
                    {
                        sb.Append(", ");
                    }
                }
                sb.Append(">");
            }
            return sb.ToString();
        }
    }

And run the test. You can confirm all the tests have passed. I created another element and you can see how to implement and test it.

Single Responsibility Principle

This section explains Single Responsibility Principle to achieve efficient work against multi language support. I plan to implement a VB version too. But I found a design problem in the above section's class design. To add a VB version, I will change the TypeName class as shown below:

    public class TypeName
    {
        public String Name { get; set; }
        public List<TypeName> GenericTypes { get; private set; }
        public TypeName(String name)
        {
            this.Name = name;
            this.GenericTypes = new List<TypeName>();
        }
        public String WriteCSharp()
        {
            //..method body
        }
        public String WriteVB()
        {
            //..method body
        }
    }

If you plan to add Java, F# or another language, you add the WriteXXX method to TypeName class. This causes some problem. C# programmers and VB programmers must work against this same file. It might be better if each programmer worked against different files to avoid version control issues. Imagine that one programmer works in the USA and another works in Japan and they don't share source code by the same TFS for some reason (different company, poor environment, etc.) So, I redesign the TypeName class to only manage the information of data and the SourceCodeGenerator class only handles generating source code text and format settings. If you don't divide it, TypeName class will have a property about format settings (for example, insert line break automatic property getter).

After a redesign, both classes will be responsible for single feature. I create a test case for it (maybe it would be better to divide a test case class for C# and VB, but I don't have time to do it).

    [TestClass]
    public class TypeNameTest
    {
        [TestMethod]
        public void TypeNameWithoutGenericTypes()
        {
            var tp = new TypeName("Int32");
            {
                var g = new CSharpSourceCodeGenerator();
                Assert.AreEqual("Int32", g.Write(tp));
            }
            {
                var g = new VisualBasicSourceCodeGenerator();
                Assert.AreEqual("Int32", g.Write(tp));
            }
        }
        [TestMethod]
        public void TypeNameWithGenericTypes()
        {
            var tp = new TypeName("Func");
            tp.GenericTypes.Add(new TypeName("String"));
            tp.GenericTypes.Add(new TypeName("Int32"));
            {
                var g = new CSharpSourceCodeGenerator();
                Assert.AreEqual("Func<String, Int32>", g.Write(tp));
            }
            {
                var g = new VisualBasicSourceCodeGenerator();
                Assert.AreEqual("Func(Of String, Int32)", g.Write(tp));
            }
        }
        [TestMethod]
        public void TypeNameWithNestedGenericTypes()
        {
            var tp = new TypeName("Func");
            tp.GenericTypes.Add(new TypeName("String"));
            var tp1 = new TypeName("Action");
            tp1.GenericTypes.Add(new TypeName("String"));
            tp1.GenericTypes.Add(new TypeName("Int32"));
            tp.GenericTypes.Add(tp1);
            {
                var g = new CSharpSourceCodeGenerator();
                Assert.AreEqual("Func<String, Action<String, Int32>>", g.Write(tp));
            }
            {
                var g = new VisualBasicSourceCodeGenerator();
                Assert.AreEqual("Func(Of String, Action(Of String, Int32))", g.Write(tp));
            }
        }
    }

So, I create CSharpSourceCodeGenerator class to solve the above issue (see HigLabo.CodeGenerator.Version1 project).

    public class CSharpSourceCodeGenerator
    {
        public CSharpSourceCodeGenerator()
        {
        }
        public String Write(TypeName typeName)
        {
            StringBuilder sb = new StringBuilder();

            sb.Append(typeName.Name);
            if (typeName.GenericTypes.Count > 0)
            {
                sb.Append("<");
                for (int i = 0; i < typeName.GenericTypes.Count; i++)
                {
                    var tp = typeName.GenericTypes[i];
                    sb.Append(this.Write(tp));
                    if (i < typeName.GenericTypes.Count - 1)
                    {
                        sb.Append(", ");
                    }
                }
                sb.Append(">");
            }
            return sb.ToString();
        }
    }

And VisualBasicSourceCodeGenerator is like below:

    public class VisualBasicSourceCodeGenerator
    {
        public String Write(TypeName typeName)
        {
            StringBuilder sb = new StringBuilder();

            sb.Append(typeName.Name);
            if (typeName.GenericTypes.Count > 0)
            {
                sb.Append("(Of ");
                for (int i = 0; i < typeName.GenericTypes.Count; i++)
                {
                    var tp = typeName.GenericTypes[i];
                    sb.Append(this.Write(tp));
                    if (i < typeName.GenericTypes.Count - 1)
                    {
                        sb.Append(", ");
                    }
                }
                sb.Append(")");
            }
            return sb.ToString();
        }
    }

Now TypeName class is like below. A little bit simple before.

    public class TypeName
    {
        public String Name { get; set; }
        public List<TypeName> GenericTypes { get; private set; }
        public TypeName(String name)
        {
            this.Name = name;
            this.GenericTypes = new List<TypeName>();
        }
    }

Now the file is separated and both programmers can work against each file without version control pains (or at least, less pain than before). And you can extend the CodeGenerator class (such as format settings) without changing meta data classes (TypeName, Field, etc.). You can extend HTML generator from meta data classes if you want. Or you can divide the DLL like HigLabo.ClassSchema.dll, HigLabo.CSharpCodeGenerator.dll, HigLabo.HtmlGenerator.dll easily.

StringBuilder vs TextWriter

This section explains that TextWriter is better than StringBuilder for performance improvement. Before you start creating other elements (Field, AccessModifier, etc.), I change CSharpSourceCodeGenerator design. In the future, I will generate many files from the StoredProcedure schema.

I must also consider performance, memory usage and file I/O. StringBuilder is better than String, but once you call ToString method, StringBulider will allocate data to heap memory. If you use TextWriter, you can output text data directory to file system like this.

    using (TextWriter writer = File.CreateText("MyFile.txt"))
    {
        writer.WriteLine("my data");
    }

Because of the above reason, I change test case like this (StringWriter class inherited TextWriter class).

    [TestClass]
    public class TypeNameTest
    {
        [TestMethod]
        public void TypeNameWithoutGenericTypes()
        {
            var tp = new TypeName("Int32");
            Assert.AreEqual("Int32", SourceCodeGenerator.Write(SourceCodeLanguage.CSharp, tp));
            Assert.AreEqual("Int32", SourceCodeGenerator.Write(SourceCodeLanguage.VB, tp));
        }
        [TestMethod]
        public void TypeNameWithGenericTypes()
        {
            var tp = new TypeName("Func");
            tp.GenericTypes.Add(new TypeName("String"));
            tp.GenericTypes.Add(new TypeName("Int32"));

            Assert.AreEqual("Func<String, Int32>", 
                             SourceCodeGenerator.Write(SourceCodeLanguage.CSharp, tp));
            Assert.AreEqual("Func(Of String, Int32)", 
                             SourceCodeGenerator.Write(SourceCodeLanguage.VB, tp));
        }
        [TestMethod]
        public void TypeNameWithNestedGenericTypes()
        {
            var tp = new TypeName("Func");
            tp.GenericTypes.Add(new TypeName("String"));
            var tp1 = new TypeName("Action");
            tp1.GenericTypes.Add(new TypeName("String"));
            tp1.GenericTypes.Add(new TypeName("Int32"));
            tp.GenericTypes.Add(tp1);

            Assert.AreEqual("Func<String, Action<String, Int32>>"
                , SourceCodeGenerator.Write(SourceCodeLanguage.CSharp, tp));
            Assert.AreEqual("Func(Of String, Action(Of String, Int32))"
                , SourceCodeGenerator.Write(SourceCodeLanguage.VB, tp));
        }
    }

And change CodeGenerator classes like this.

CSharpSourceCodeGenerator

    public class CSharpSourceCodeGenerator : SourceCodeGenerator
    {
        public override SourceCodeLanguage Language
        {
            get { return SourceCodeLanguage.CSharp; }
        }

        public CSharpSourceCodeGenerator(TextWriter textWriter)
            : base(textWriter)
        {
        }
        public override void Write(TypeName typeName)
        {
            var writer = this.TextWriter;

            writer.Write(typeName.Name);
            if (typeName.GenericTypes.Count > 0)
            {
                writer.Write("<");
                for (int i = 0; i < typeName.GenericTypes.Count; i++)
                {
                    var tp = typeName.GenericTypes[i];
                    this.Write(tp);
                    if (i < typeName.GenericTypes.Count - 1)
                    {
                        writer.Write(", ");
                    }
                }
                writer.Write(">");
            }
        }
        //Other method...
    }

VisualBasicSourceCodeGenerator

    public class VisualBasicSourceCodeGenerator: SourceCodeGenerator
    {
        public override SourceCodeLanguage Language
        {
            get { return SourceCodeLanguage.VB; }
        }

        public VisualBasicSourceCodeGenerator(TextWriter textWriter)
            : base(textWriter)
        {
        }
        public override void Write(TypeName typeName)
        {
            var writer = this.TextWriter;

            writer.Write(typeName.Name);
            if (typeName.GenericTypes.Count > 0)
            {
                writer.Write("(Of ");
                for (int i = 0; i < typeName.GenericTypes.Count; i++)
                {
                    var tp = typeName.GenericTypes[i];
                    this.Write(tp);
                    if (i < typeName.GenericTypes.Count - 1)
                    {
                        writer.Write(", ");
                    }
                }
                writer.Write(")");
            }
        }
        //Other method...
    }

SourceCodeGenerator abstract base class has common property and method of XXXSourceCodeGenerator classes.

    public abstract class SourceCodeGenerator
    {
        public String Indent { get; set; }
        public Int32 CurrentIndentLevel { get; set; }
        public TextWriter TextWriter { get; private set; }
        public abstract SourceCodeLanguage Language { get; }

        protected SourceCodeGenerator(TextWriter textWriter)
        {
            this.Indent = "    ";
            this.CurrentIndentLevel = 0;
            this.TextWriter = textWriter;
        }
        public abstract void Write(TypeName typeName);
        public abstract void Write(CodeBlock codeBlock);

        public abstract void Write(AccessModifier modifier);
        public abstract void Write(MethodAccessModifier modifier);
        public abstract void Write(FieldModifier modifier);
        public abstract void Write(Field field);

        public abstract void Write(ConstructorModifier modifier);
        public abstract void Write(Constructor constructor);
        
        public abstract void Write(MethodModifier modifier);
        public abstract void Write(MethodParameter parameter);
        public abstract void Write(Method method);
        
        public abstract void Write(PropertyBody propertyBody);
        public abstract void Write(Property property);
        
        public abstract void Write(ClassModifier modifier);
        public abstract void Write(Class @class);

        public abstract void Write(InterfaceProperty property);
        public abstract void Write(InterfaceMethod method);
        public abstract void Write(Interface @interface);
        
        public abstract void Write(Namespace @namespace);
        public abstract void Write(SourceCode sourceCode);

        protected void WriteIndent()
        {
            for (int i = 0; i < this.CurrentIndentLevel; i++)
            {
                this.TextWriter.Write(this.Indent);
            }
        }
        protected void WriteLineAndIndent()
        {
            this.WriteLineAndIndent("");
        }
        protected void WriteLineAndIndent(String text)
        {
            this.TextWriter.WriteLine(text);
            for (int i = 0; i < this.CurrentIndentLevel; i++)
            {
                this.TextWriter.Write(this.Indent);
            }
        }

        public void Flush()
        {
            this.TextWriter.Flush();
        }
    }

You can see the complete C# implementation of these classes HigLabo.CodeGenerator project. You can also confirm generated C# text by the HigLabo.CodeGenerator.Sample project. VB version is not completed. If you are a VB programmer, I would really appreciate if somebody created a VB version test case and code generator.

Design StoredProcedure Class

In this chapter, I will show you how to design a stored procedure client class by C# and OOP. There are two types of stored procedures. One only executes commands to the database, the other gets data from the database. I create a sample table and stored procedure by this script.

    Create Table MyTaskTable
    (TaskId UniqueIdentifier not null
    ,Title Nvarchar(100) Not Null
    ,[Priority] Int Not Null
    ,[State] Nvarchar(10) Not Null
    ,CreateTime Datetime Not Null
    ,ScheduleDate Date 
    ,Detail Nvarchar(max) Not Null
    ,TimestampColumn Timestamp
    )

    Go

    Alter Table [dbo].[MyTaskTable] Add Constraint [PK_MyTaskTable] 
    Primary Key Clustered (TaskId)

    Go

    Create Procedure MyTaskTableInsert
    (@TaskId UniqueIdentifier 
    ,@Title Nvarchar(100)
    ,@Priority Int
    ,@State Nvarchar(10)
    ,@CreateTime Datetime
    ,@ScheduleDate Date 
    ,@Detail Nvarchar(max)
    ) As

    Insert Into MyTaskTable 
           (TaskId, Title, [Priority], [State], CreateTime, ScheduleDate, Detail)
    Values (@TaskId, @Title, @Priority, @State, @CreateTime, @ScheduleDate, @Detail)

    Go

    Create Procedure MyTaskTableSelectBy_TaskId
    (@TaskId UniqueIdentifier 
    ) As

    select * from MyTaskTable with(nolock) 
    where TaskId = @TaskId

    Go

You must create some code to call a stored procedure. I want to generate a code that calls stored procedure. I design these classes from caller position like below:

    var db = new HigLabo.Data.SqlServerDatabase("connection string");

    //Execute stored procedure
    var sp = new MyTaskTableInsert();//Same name to stored procedure on database 
    sp.TaskId = Guid.NewGuid();      //Strongly typed property corresponding to 
                                     //stored procedure's parameter
    sp.Title = "Post article to CodeProject";
    sp.Priority = 2;
    sp.State = "Executing";
    sp.CreateTime = DateTime.Now;
    sp.ScheduleDate = new DateTime(2014, 3, 25);
    sp.Detail = "...Draft...";
    //Execute MyTaskTableInsert stored procedure on database and get affected record count.
    var result = sp.ExecuteNonQuery(db);
    //or call like this
    //var result1 = db.Execute(sp);

    var sp1 = new MyTaskTableSelectBy_TaskId();
    sp1.TaskId = sp.TaskId;
    var recordList = sp.GetResultSets();        //Get list of POCO objects that represent 
                                                //a record of table on database.
    foreach(var record in recordList)
    {
        //Do something...
    }

It looks intuitive to use. And it is strongly typed compared with DataTable or DataReader. Because of strongly typed properties, you can get great help by intellisense.

You can execute stored procedures to multi database (that has the same schema) like this:

var db1 = new HigLabo.Data.SqlServerDatabase("connection string to DB1");
var db2 = new HigLabo.Data.SqlServerDatabase("connection string to DB2");

//Execute stored procedure
var sp = new MyTaskTableInsert();
sp.TaskId = Guid.NewGuid();
sp.Title = "Post article to CodeProject";
sp.Priority = 2;
sp.State = "Executing";
sp.CreateTime = DateTime.Now;
sp.ScheduleDate = new DateTime(2014, 3, 25);
sp.Detail = "...Draft...";

var db1Result1 = sp.ExecuteNonQuery(db1);
var db2Result1 = sp.ExecuteNonQuery(db2);
//Or call like this
var db1Result2 = db1.Execute(sp);
var db2Result2 = db2.Execute(sp);

Next, I consider transaction. I design the Transaction feature like this:

var db = new HigLabo.Data.SqlServerDatabase("connection string");
using (TransactionContext tx = new TransactionContext(db))
{
    tx.BeginTransaction(IsolationLevel.ReadCommitted);
    for (int i = 0; i < 3; i++)
    {
        var sp = new MyTaskTableInsert();
        //...Set property of MyTaskTableInsert object
        var result = sp.ExecuteNonQuery(tx);
    }
    tx.CommitTransaction();
}

Sometimes, you may execute command with multi transactions to the database. I will design multi transaction features as shown below:

var db1 = new HigLabo.Data.SqlServerDatabase("connection string to DB1");
var db2 = new HigLabo.Data.SqlServerDatabase("connection string to DB2");

using (TransactionContext tx1 = new TransactionContext(db1)))
{
    using (TransactionContext tx2 = new TransactionContext(db2)))
    {
        tx1.BeginTransaction(IsolationLevel.ReadCommitted);
        tx2.BeginTransaction(IsolationLevel.ReadCommitted);
        for (int i = 0; i < 3; i++)
        {
            var sp = new MyTaskTable1Insert();
            //...Set property of MyTaskTableInsert object
            var result = sp.ExecuteNonQuery(tx1);
        }
        for (int i = 0; i < 3; i++)
        {
            var sp = new MyTaskTable2Insert();
            //...Set property of MyTaskTableInsert object
            var result = sp.ExecuteNonQuery(tx2);
        }
        tx1.CommitTransaction();
        tx2.CommitTransaction();
    }
}

Multi database, Transaction is supported by the above spec. These are very basic features for the database access library.

Next, I think about the case where I use one database. I often have such cases especially by creating small web applications. Then, I feel that it is redundant to assign Database object for each StoredProcedure.ExecuteNonQuery, GetResultSet method. So, I add default database feature. You can set the default database by calling SetCreateDatabaseMethod of DatabaseFactory object. ExecuteNonQuery, GetResultSets method will be executed to the Database that you specify by SetCreateDatabaseMethod method. This design makes things simple if you use one database.

//Call once on application start what database you use...
DatabaseFactory.Current.SetCreateDatabaseMethod
    ("DbSharpSample", () => new HigLabo.Data.SqlServerDatabase("connection string to db"));

var sp = new MyTaskTableInsert();
//sp.GetDatabaseKey() returns "DbSharpSample".
//You can specify DatabaseKey when you generate code.
//That makes you set different factory method for each database that has different schema.

sp.TaskId = Guid.NewGuid();
//Set other properties...
var result = sp.ExecuteNonQuery();//Executed to db

You can generate code for each two database schemas with different DatabaseKey.

When the default database and transaction are mixed, the transaction has a priority.

//Call once on application start what database you use...
DatabaseFactory.Current.SetCreateDatabaseMethod
    ("DbSharpSample", () => new HigLabo.Data.SqlServerDatabase("connection string to db1"));

var db2 = new HigLabo.Data.SqlServerDatabase("connection string");
var sp1 = new MyTaskTableInsert();
//...Set property of MyTaskTableInsert object
var result1 = sp1.ExecuteNonQuery();//Executed to db1
using (TransactionContext tx = new TransactionContext(db2))
{
    tx.BeginTransaction(IsolationLevel.ReadCommitted);
    var sp2 = new MyTaskTableInsert();
    //...Set property of MyTaskTableInsert object
    var result2 = sp2.ExecuteNonQuery(tx);
    tx.CommitTransaction();
}

OK, these features are what I want to implement first.

I designed classes and implement all the above feature. Class diagram is shown below:

Each class has each responsibility.

StoredProcedure class has common operation of stored procedure. This class's common operation are GetStoredProcedureName, ExecuteNonQuery, CreateCommand, SetOutputParameterValue method.
MyTaskTableInsert class will be generated from stored procedure schema in database. This class has property corresponding to stored procedure's parameter. And this class has concrete implementation of GetStoredProcedureName, CreateCommand, SetOutputParameterValue.
DatabaseRecord and StoredProcedureResultSet class is the base class of MyTaskTableSelectBy_TaskId.ResultSet class. MyTaskTableSelectBy_TaskId.ResultSet class is a POCO class that represent a record of table in database. (Later, I will create MyTaskTable.Record class from table schema that class inherits from DatabaseRecord class too.)
StoredProcedureWithResultSet class inherits from StoredProcedure class. This class adds some common operation of stored procedure that returns result set. This class's common operations are GetDataTable, GetResultSets, EnumerateResultSets method.
StoredProcedureWithResultSet<T> class inherit from StoredProcedureWithResultSet class. This class's common operations are CreateResultSet, SetResultSet method. This class also adds strongly typed method to improve type safety.
MyTaskTableSelectBy_TaskId will be generated from stored procedure schema in database. This class has property corresponding to stored procedure's parameter. And this class has GetStoredProcedureName, CreateCommand, SetOutputParameterValue, CreateResultSet, SetResultSet concrete implementation.

As you can see, each class has each responsibility. At first, I explain about the StoredProcedure class and MyTaskTableInsert class. The StoredProcedure class is like below:

    public abstract class StoredProcedure : INotifyPropertyChanged, ITransaction
    {
        public abstract DbCommand CreateCommand();
        protected abstract void SetOutputParameterValue(DbCommand command);

        public Int32 ExecuteNonQuery()
        {
            return this.ExecuteNonQuery(this.GetDatabase());
        }

        public Int32 ExecuteNonQuery(Database database)
        {
            if (database == null) throw new ArgumentNullException("database");
            var affectedRecordCount = -1;
            var previousState = database.ConnectionState;

            try
            {
                var cm = CreateCommand();
                affectedRecordCount = database.ExecuteCommand(cm);
                this.SetOutputParameterValue(cm);
            }
            finally
            {
                if (previousState == ConnectionState.Closed && 
                  database.ConnectionState == ConnectionState.Open) { database.Close(); }
                if (database.OnTransaction == false) { database.Dispose(); }
            }
            return affectedRecordCount;
        }
        //...abbreviated other elements
    }

The StoredProcedure class has all the common operations of a generated stored procedure class. You can see ExecuteNonQuery method is defined. And you can also see CreateCommand, SetOutputParameterValue abstract method is defined. The only thing I must do when I implement a generator is to create three elements.

Create properties corresponding to stored procedure's parameter
Create CreateCommand method
Create SetOutputParameterValue method

MyTaskTableInsert class will be generated like below:

    public partial class MyTaskTableInsert : StoredProcedure
    {
        private Guid? _TaskId;
        private String _Title = "";
        //...abbreviated other field

        public Guid? TaskId
        {
            get
            {
                return _TaskId;
            }
            set
            {
                this.SetPropertyValue
                     (ref _TaskId, value, this.GetPropertyChangedEventHandler());
            }
        }
        //...abbreviated other properties

        public override DbCommand CreateCommand()
        {
            var db = new SqlServerDatabase("");
            var cm = db.CreateCommand();
            cm.CommandType = CommandType.StoredProcedure;
            cm.CommandText = "MyTaskTableInsert";

            DbParameter p = null;

            p = db.CreateParameter("@TaskId", SqlDbType.UniqueIdentifier, 0, 0);
            p.SourceColumn = p.ParameterName;
            p.Direction = ParameterDirection.Input;
            p.Size = 16;
            p.Value = this.TaskId;
            cm.Parameters.Add(p);

            //...abbreviated other parameter

            for (int i = 0; i < cm.Parameters.Count; i++)
            {
                if (cm.Parameters[i].Value == null) cm.Parameters[i].Value = DBNull.Value;
            }
            return cm;
        }
        protected override void SetOutputParameterValue(DbCommand command)
        {
            //Set property value from command object if output parameter exists.
        }
        //...abbreviated other elements
    }

GetPropertyChangedEventHandler returns PropertyChangedEventHandler defined in base class(StoredProcedure class). SetPropertyValue is a extension method that is defined in the INotifyPropertyChangedExtensions class in HigLabo.Core.dll. Inside of SetPropertyValue method is:

    public static void SetPropertyValue<T, TProperty>
        (this T obj, ref TProperty field, TProperty value
        , PropertyChangedEventHandler onPropertyChanged, 
        [CallerMemberName]  String propertyName = "")
        where T : INotifyPropertyChanged
    {
        if (Object.Equals(field, value) == true) return;
        field = value;
        var eh = onPropertyChanged;
        if (eh != null)
        {
            eh(obj, new PropertyChangedEventArgs(propertyName));
        }
    }

Simply stated, it validates object equality, sets value to field, and raises PropertyChanged event.

These class designs create minimal work because all common operations are already defined in StoredProcedure class.

Next, I explain StoredProcedureWithResultSet class that returns resultset and other. Here is the list:

MyTaskTableSelectBy_TaskId.ResultSet class
GetDataTable method
GetResultSets method
EnumerateResultSets method

The MyTaskTableSelectBy_TaskId.ResultSet class is a POCO object that represents a record of database table. To map values to strongly typed POCO object's properties, there is a restriction that the result set must have the same schema whenever you execute stored procedure.

Here is a generated ResultSet class.

        public partial class ResultSet : StoredProcedureResultSet
        {
            private Guid? _TaskId;
            private String _Title = "";
            //...abbreviated other field

            public Guid? TaskId
            {
                get
                {
                    return _TaskId;
                }
                set
                {
                    this.SetPropertyValue
                      (ref _TaskId, value, this.GetPropertyChangedEventHandler());
                }
            }
            public String Title
            {
                get
                {
                    return _Title;
                }
                set
                {
                    this.SetPropertyValue
                      (ref _Title, value, this.GetPropertyChangedEventHandler());
                }
            }
            //...abbreviated other property

            //...abbreviated other elements
        }

I design StoredProcedureWithResultSet,StoredProcedureWithResultSet<T> class and implement all common feature on the above list. Here is a StoredProcedureWithResultSet class. You can see GetDataTable, GetResultSets, EnumerateResultSets methods are defined.

    public abstract class StoredProcedureWithResultSet : StoredProcedure
    {
        protected StoredProcedureWithResultSet()
        {
        }
        protected abstract StoredProcedureResultSet CreateResultSets(IDataReader reader);
        public List<StoredProcedureResultSet> GetResultSets()
        {
            return EnumerateResultSets().ToList();
        }
        public List<StoredProcedureResultSet> GetResultSets(Database database)
        {
            return EnumerateResultSets(database).ToList();
        }
        public IEnumerable<StoredProcedureResultSet> EnumerateResultSets()
        {
            return EnumerateResultSets(this.GetDatabase());
        }
        public IEnumerable<StoredProcedureResultSet> EnumerateResultSets(Database database)
        {
            if (database == null) throw new ArgumentNullException("database");
            DbDataReader dr = null;
            var previousState = database.ConnectionState;

            try
            {
                var resultsets = new List<StoredProcedureResultSet>();
                var cm = CreateCommand();
                dr = database.ExecuteReader(cm);
                while (dr.Read())
                {
                    var rs = CreateResultSets(dr);
                    resultsets.Add(rs);
                    yield return rs;
                }
                dr.Close();
                this.SetOutputParameterValue(cm);
            }
            finally
            {
                if (dr != null) { dr.Dispose(); }
                if (previousState == ConnectionState.Closed && 
                    database.ConnectionState == ConnectionState.Open) { database.Close(); }
                if (database.OnTransaction == false) { database.Dispose(); }
            }
        }
        public DataTable GetDataTable()
        {
            return GetDataTable(this.GetDatabase());
        }
        public DataTable GetDataTable(Database database)
        {
            if (database == null) throw new ArgumentNullException("database");
            try
            {
                var cm = CreateCommand();
                var dt = database.GetDataTable(cm);
                return dt;
            }
            finally
            {
                if (database.OnTransaction == false) { database.Dispose(); }
            }
        }
        //...abbreviated other method
    }

And StoredProcedureWithResultSet<T> is like below. You can see more strongly typed methods are defined.

    public abstract class StoredProcedureWithResultSet<T> : StoredProcedureWithResultSet
        where T : StoredProcedureResultSet, new()
    {
        protected abstract void SetResultSet(T resultSet, IDataReader reader);
        public abstract T CreateResultSet();
        protected override StoredProcedureResultSet CreateResultSets(IDataReader reader)
        {
            var rs = this.CreateResultSet();
            SetResultSet(rs, reader);
            return rs;
        }
        public new List<T> GetResultSets()
        {
            return EnumerateResultSets().ToList();
        }
        public new List<T> GetResultSets(Database database)
        {
            return EnumerateResultSets(database).ToList();
        }
        public new IEnumerable<T> EnumerateResultSets()
        {
            return base.EnumerateResultSets().Cast<T>();
        }
        public new IEnumerable<T> EnumerateResultSets(Database database)
        {
            return base.EnumerateResultSets(database).Cast<T>();
        }
    }

This design creates minimal working because all common operations are already defined in these classes. So, I only create these elements in the below list:

Field and property of stored procedure
CreateCommand method
SetOutputParameterValue method
CreateResultSet method
SetResultSet method

Here is some code from it:

    public partial class MyTaskTableSelectBy_TaskId : 
         StoredProcedureWithResultSet<MyTaskTableSelectBy_TaskId.ResultSet>
    {
        private Guid? _TaskId;

        public Guid? TaskId
        {
            get
            {
                return _TaskId;
            }
            set
            {
                this.SetPropertyValue
                  (ref _TaskId, value, this.GetPropertyChangedEventHandler());
            }
        }

        public override DbCommand CreateCommand()
        {
            var db = new SqlServerDatabase("");
            var cm = db.CreateCommand();
            cm.CommandType = CommandType.StoredProcedure;
            cm.CommandText = this.GetStoredProcedureName();

            DbParameter p = null;

            p = db.CreateParameter("@TaskId", SqlDbType.UniqueIdentifier, 0, 0);
            p.SourceColumn = p.ParameterName;
            p.Direction = ParameterDirection.Input;
            p.Size = 16;
            p.Value = this.TaskId;
            cm.Parameters.Add(p);

            for (int i = 0; i < cm.Parameters.Count; i++)
            {
                if (cm.Parameters[i].Value == null) cm.Parameters[i].Value = DBNull.Value;
            }
            return cm;
        }
        protected override void SetOutputParameterValue(DbCommand command)
        {
        }
        public override MyTaskTableSelectBy_TaskId.ResultSet CreateResultSet()
        {
            return new ResultSet(this);
        }
        protected override void SetResultSet
              (MyTaskTableSelectBy_TaskId.ResultSet resultSet, IDataReader reader)
        {
            var r = resultSet;
            Int32 index = -1;
            try
            {
                index += 1; 
                if (reader[index] != DBNull.Value) r.TaskId = reader.GetGuid(index);
                index += 1; 
                if (reader[index] != DBNull.Value) r.Title = reader[index] as String;
                index += 1; 
                if (reader[index] != DBNull.Value) r.Priority = reader.GetInt32(index);
                index += 1; 
                if (reader[index] != DBNull.Value) r.State = reader[index] as String;
                index += 1; 
                if (reader[index] != DBNull.Value) r.CreateTime = reader.GetDateTime(index);
                index += 1; 
                if (reader[index] != DBNull.Value) r.ScheduleDate = reader.GetDateTime(index);
                index += 1; 
                if (reader[index] != DBNull.Value) r.Detail = reader[index] as String;
                index += 1; 
                if (reader[index] != DBNull.Value) r.TimestampColumn = reader[index] as Byte[];
            }
            catch (InvalidCastException ex)
            {
                throw new StoredProcedureSchemaMismatchedException(this, index, ex);
            }
        }
        //...abbreviated other elements
   }

As you can see, CreateResultSet is generated same code whatever stored procedure, and SetOutputParameterValue is empty in many cases. It makes it easy for me to implement generator. In the next section, I will explain the details to get schema data from database and generate C# code from schema.

Read Schema from Database

From the above design, I must get schema data when I generate some of the method's implementation.

CreateCommand
SetOutputParameterValue
SetResultSet

I plan to support these general types in Microsoft SqlServer.

    bigint
    timestamp
    bigint
    binary
    image
    varbinary
    bit
    char
    nchar
    ntext
    nvarchar
    text
    varchar
    xml
    datetime
    smalldatetime
    date
    time
    datetime2
    decimal
    money
    smallmoney
    float
    int
    real
    uniqueidentifier
    smallint
    tinyint
    datetimeoffset
    variant

Also support these types specific Microsoft SqlServer.

    udt(SqlGeometry)
    udt(SqlGeography)
    udt(HierarchyId)
    UserDefinedTable column

I also want to support a parameter that type is UserDefinedType. Though it is not about a type, I plan to support C# Enum feature. Here are all contents listed in this section.

General column type (Bit, Int, NVarchar, etc.)
Udt(SqlGeometry, SqlGeography, HierarchyId)
Enum
UserTable

I prepare the database by executing DbSharp_SqlServer.sql file (in HigLaboSample.Data.SqlServer.CSharp project) to my local database.

Inside of CreateCommand method of Usp_Structure stored procedure must be like below:

    public override DbCommand CreateCommand()
    {
        var db = new SqlServerDatabase("");
        var cm = db.CreateCommand();
        cm.CommandType = CommandType.StoredProcedure;
        cm.CommandText = this.GetStoredProcedureName();

        DbParameter p = null;

        //General parameter
        p = db.CreateParameter("@BigIntColumn", SqlDbType.BigInt, 19, 0);
        p.SourceColumn = p.ParameterName;
        p.Direction = ParameterDirection.InputOutput;
        p.Size = 8;
        p.Value = this.BigIntColumn;
        cm.Parameters.Add(p);

        //UserDefinedTable column
        p = db.CreateParameter("@StructuredColumn", SqlDbType.Structured, 0, 0);
        p.SourceColumn = p.ParameterName;
        p.Direction = ParameterDirection.Input;
        p.Size = -1;
        p.SetTypeName("MyTableType");
        var dt = this.StructuredColumn.CreateDataTable();
        foreach (var item in this.StructuredColumn.Records)
        {
            dt.Rows.Add(item.GetValues());
        }
        p.Value = dt;
        cm.Parameters.Add(p);

        for (int i = 0; i < cm.Parameters.Count; i++)
        {
            if (cm.Parameters[i].Value == null) cm.Parameters[i].Value = DBNull.Value;
        }
        return cm;
    }

Inside of SetOutputParameterValue method must be like below:

    protected override void SetOutputParameterValue(DbCommand command)
    {
        var cm = command;
        DbParameter p = null;
        p = cm.Parameters[0] as DbParameter;
        if (p.Value != DBNull.Value && p.Value != null) this.BigIntColumn = (Int64)p.Value;
        //Other parameters...
    }

Inside of SetResultSet method must be like below:

    protected override void SetResultSet
        (AllDataTypeTableSelectBy_PrimaryKey.ResultSet resultSet, IDataReader reader)
    {
        var r = resultSet;
        Int32 index = -1;
        try
        {
            index += 1; r.PrimaryKeyColumn = reader.GetInt64(index);
            index += 1; r.TimestampColumn = reader[index] as Byte[];
            index += 1; 
            if (reader[index] != DBNull.Value) r.BigIntColumn = reader.GetInt64(index);
            //Other parameters...
            index += 1; 
            if (reader[index] != DBNull.Value) 
                r.SqlVariantColumn = reader[index] as Object;
            index += 1; 
            if (reader[index] != DBNull.Value) 
                r.GeometryColumn = (Microsoft.SqlServer.Types.SqlGeometry)reader[index];
            index += 1; 
            if (reader[index] != DBNull.Value) 
                r.GeographyColumn = (Microsoft.SqlServer.Types.SqlGeography)reader[index];
            index += 1; 
            if (reader[index] != DBNull.Value) 
                r.HierarchyIDColumn = (Microsoft.SqlServer.Types.SqlHierarchyId)reader[index];
            index += 1; 
            if (reader[index] != DBNull.Value) 
                r.EnumColumn = StoredProcedure.ToEnum<MyEnum>
                               (reader[index] as String) ?? r.EnumColumn;
            index += 1; 
            r.NotNullBigIntColumn = reader.GetInt64(index);
            index += 1; 
            r.NotNullBinaryColumn = reader[index] as Byte[];
            //Other parameters...
        }
        catch (InvalidCastException ex)
        {
            throw new StoredProcedureSchemaMismatchedException(this, index, ex);
        }
    }

To create these methods, I must get this information from schema:

ColumnName
Type
Length
Precision
Scale
IsOutput parameter
UserTableTypeName
UdtTypeName

So, I create a T-Sql script file to get the above schema.

Select T01.name as StoredProcedureName 
,T02.name as ParameterName
,CASE T03.is_table_type 
    When 1 Then 'structured' 
    Else 
        Case T03.is_assembly_type
        When 1 Then 'udt' 
        Else
            Case T03.name 
            When 'sql_variant' Then 'variant'
            Else T03.name 
            End
    End
End as ParameterType
,Case T02.max_length 
    When -1 Then -1 
    Else
    Case T03.name 
        When 'nvarchar' Then T02.max_length / 2
        When 'nchar' Then T02.max_length / 2
        Else T02.max_length 
    End
End as ParameterLength
,T02.precision as ParameterPrecision
,T02.scale as ParameterScale
,T02.is_output as IsOutput
,T02.default_value as DefaultValue
,CASE T03.is_table_type 
    When 1 Then T03.name 
    Else ''
End as UserTableTypeName
,Case T03.is_assembly_type
When 1 Then T03.name
Else '' 
End as UdtTypeName 
From sys.procedures as T01 
Inner Join sys.parameters as T02 
ON T01.object_id = T02.object_id 
Inner Join sys.types as T03 
ON T02.user_type_id = T03.user_type_id
Where T01.name = 'MyStoredProcedureName'
Order By T02.parameter_id

The result is like below:

I worked against Microsoft SqlServer and found that all I must do for each database are the below things:

Create T-Sql script that get schema data (MySql, Oracle, PostgreSql, etc.)
List up all types I want to support (MySql, Oracle, PostgreSql, etc.)

Next, I must create these three code blocks for each type in the above:

Create SetOutputParameterValue method code block
Create CreateCommand method code block
Create SetResultSet method code block

For example, I create it against NVarchar.

CreateCommand

    p = db.CreateParameter("@NVarCharColumn", SqlDbType.NVarChar, 0, 0);
    p.SourceColumn = p.ParameterName;
    p.Direction = ParameterDirection.InputOutput;
    p.Size = 100;
    p.Value = this.NVarCharColumn;
    cm.Parameters.Add(p);

SetResultSet

    r.NVarCharColumn = reader[index] as String;

SetOutputParameterValue

    this.NVarCharColumn = (String)p.Value;

One more example against Geometry.

CreateCommand

    p = db.CreateParameter("@GeometryColumn", SqlDbType.Udt, 0, 0);//SqlDbType is Udt
    p.SourceColumn = p.ParameterName;
    p.Direction = ParameterDirection.InputOutput;
    p.Size = -1;
    p.SetUdtTypeName("geometry");//Set UdtTypeName property.
    p.Value = this.GeometryColumn;
    cm.Parameters.Add(p);

SetResultSet

    r.GeometryColumn = (Microsoft.SqlServer.Types.SqlGeometry)reader[index];

SetOutputParameterValue

    this.GeometryColumn = (Microsoft.SqlServer.Types.SqlGeometry)p.Value;

I created all implementations against Microsoft SqlServer and MySql, but do not implement Oracle, PostgreSql. If you are a specialist in these databases, please let me know.

Create T-Sql script that get schema data
List up all types
Create CreateCommand method code block
Create SetResultSet method code block
Create SetOutputParameterValue method code block

If you create the above list for Oracle, PostgreSql, I would greatly appreciate it and I will merge your code to my library.

Enum Support

What is an Enum support? I design Enum support like this:

C# property is defined as Enum and you can get the benefit of strongly typed
Enum value is inserted to database as String by calling ToString method of Enum
Column type must be NVarchar, NChar or other text type

These Enum property prevents you from inserting invalid value to database. I must implement ToEnum method in StoredProcedure class that creates Enum from Object.

    public static T? ToEnum<T>(Object value)
        where T : struct
    {
        if (value == null) return null;
        if (typeof(T).IsEnum == false) throw new ArgumentException("T must be Enum type");
        T result;
        var tp = value.GetType();
        if (tp == typeof(T)) return (T)value;
        if (tp == typeof(String) && Enum.TryParse((String)value, true, out result))
        {
            return result;
        }
        throw new InvalidEnumDataException(typeof(T), value);
    }

I create method code block for Enum.

CreateCommand

    p = db.CreateParameter("@EnumColumn", SqlDbType.NVarChar, 0, 0);
    p.SourceColumn = p.ParameterName;
    p.Direction = ParameterDirection.Input;
    p.Size = 20;
    p.Value = this.EnumColumn.ToStringFromEnum();
    cm.Parameters.Add(p);

SetResultSet

    r.EnumColumn = StoredProcedure.ToEnum<MyEnum>(reader[index] as String) ?? r.EnumColumn;

SetOutputParameterValue

    this.EnumColumn = StoredProcedure.ToEnum<MyEnum>(p.Value as String) ?? this.EnumColumn;

If invalid value exists in database, StoredProcedure.ToEnum<MyEnum> throw InvalidEnumDataException.

UserDefinedType

In this section, I will show you how to design UserDefinedType class. You can see MyTableType in top of DbSharp_SqlServer.sql file (in HigLabo.DbSharp.CodeGenerator.Version1 project).

    Create Type MyTableType As Table
    (BigIntColumn bigint not null
    ,BinaryColumn binary(100)
    ,ImageColumn image
    ,VarBinaryColumn varbinary(100)
    ,BitColumn bit
    ,CharColumn char(100)
    ,NCharColumn nchar(100)
    ,NTextColumn ntext
    ,NVarCharColumn nvarchar(100)
    ,TextColumn text
    ,VarCharColumn varchar(100)
    ,XmlColumn xml
    ,DateTimeColumn datetime
    ,SmallDateTimeColumn smalldatetime
    ,DateColumn date
    ,TimeColumn time
    ,DateTime2Column datetime2
    ,DecimalColumn decimal
    ,MoneyColumn money
    ,SmallMoneyColumn smallmoney
    ,FloatColumn float
    ,IntColumn int
    ,RealColumn real
    ,UniqueIdentifierColumn uniqueidentifier
    ,SmallIntColumn smallint 
    ,TinyIntColumn tinyint
    ,DateTimeOffsetColumn datetimeoffset(7)
    ,EnumColumn nvarchar(20)
    )

I don't support TimeStamp, Geometry, Geography, HierarchyId. And I cannot support SqlVariant due to a bug. See bug detail.

You can use your own UserDefinedType as parameter of stored procedure. Here is a usage of MyTableType.

    Create Procedure Usp_Structure
    (@BigIntColumn bigint out
    ,@StructuredColumn as MyTableType readonly
    ) As

You will pass a DataTable as UserDefinedType parameter value to database. This means that you can send list of record to database. I design from caller position how to use UserDefinedType.

    var udt = new MyTableType();
    var record = new MyTableType.Record();
    record.BigIntColumn = 100;
    //Set other property...
    udt.Records.Add(record);

    var sp = new Usp_Structure();
    sp.StructuredColumn = udt;
    //Set other property...
    sp.ExecuteNonQuery();

I design the below four classes:

UserDefinedTableType
UserDefinedTableType<T>
MyTableType
MyTableType.Record

I design UserDefinedTableType that knows schema of parameter. UserDefinedTableType has GetDataTable abstract method. UserDefinedTableType<T> provides a feature to keep multi record. MyTableType is generated from database schema and has concrete implementation of GetDataTable. GetDataTable create DataTable exactly same schema of UserDefinedType on database. MyTableType.Record is a POCO object that represent a record of UserDefinedType.
Here is a class diagram about UserDefinedType.

All common features are defined at UserDefinedTableType, UserDefinedTableType<T> classes.

    public abstract class UserDefinedTableType
    {
        public abstract DataTable CreateDataTable();
    }
    public abstract class UserDefinedTableType<T> : UserDefinedTableType
        where T : UserDefinedTableTypeRecord
    {
        private List<T> _Records = new List<T>();
        public List<T> Records
        {
            get { return _Records; }
        }

        public DataTable CreateDataTable(IEnumerable<T> records)
        {
            var dt = this.CreateDataTable();
            foreach (var item in records)
            {
                dt.Rows.Add(item.GetValues());
            }
            return dt;
        }
    }

And generate a class of MyTableType from schema of UserDefinedType on database.

    public partial class MyTableType : UserDefinedTableType<MyTableType.Record>
    {
        public override DataTable CreateDataTable()
        {
            var dt = new DataTable();
            dt.Columns.Add("BigIntColumn", typeof(Int64));
            //abbreviated other code...
            return dt;
        }

        public partial class Record : UserDefinedTableTypeRecord
        {
            private Int64 _BigIntColumn;
            //abbreviated other field...

            public Int64 BigIntColumn
            {
                get
                {
                    return _BigIntColumn;
                }
                set
                {
                    this.SetPropertyValue
                        (ref _BigIntColumn, value, this.GetPropertyChangedEventHandler());
                }
            }
            //abbreviated other properties...

            public Record()
            {
            }

            public override Object[] GetValues()
            {
                Object[] oo = new Object[28];
                oo[0] = this.BigIntColumn;
                //abbreviated other...
                return oo;
            }
        }
    }

UserDefinedType is only used in parameter of stored procedure. So, I must create a CreateCommand, SetOutputParameter method.

CreateCommand

    p = db.CreateParameter("@StructuredColumn", SqlDbType.Structured, 0, 0);
    p.SourceColumn = p.ParameterName;
    p.Direction = ParameterDirection.Input;
    p.Size = -1;
    p.SetTypeName("MyTableType");
    var dt = this.StructuredColumn.CreateDataTable();
    foreach (var item in this.StructuredColumn.Records)
    {
        dt.Rows.Add(item.GetValues());
    }
    p.Value = dt;
    cm.Parameters.Add(p);

You cannot UserDefinedType as output parameter:

    Create Procedure Usp_Structure
    (@BigIntColumn bigint out
    ,@StructuredColumn as MyTableType out --Invalid!!!
    ) As

So you don't need to generate code of SetOutputParameterValue method. And you cannot use UserDefinedType as result set column. So you don't need to generate code of GetResultSets method.

Now you can use strongly typed UserDefinedType as a parameter of stored procedure.

Multiple ResultSets from Database

You can get multiple resultsets from database. For example, you create a stored procedure like below:

    Create Procedure MultipleResultSets_Procedure
    As

    select * from Table1
    select * from Table1
    select * from Table1

You cannot UserDefinedType as output parameter:

    var sp = new MultipleResultSets_Procedure();
    var rsl = sp.GetResultSetsList();

    foreach (var item in rsl.ResultSet1List)
    {
    }
    foreach (var item in rsl.ResultSet2List)
    {
    }

You can assign your own name to result set by DbSharpApplication.

Async API

You can call asynchronously by using ExecuteNonQuery, GetResultSetsAsync, GetResultSetsListAsync method.

    var sp = new MyStoredProcedure();
    var rs = await sp.GetResultSetsAsync();
    foreach (var item in rs)
    {
        ///Do something...
    }

Pretty simple to use.

Design API for CRUD Operation

In this section, I will show you how to design CRUD operation to a table on database. I create a sample table to database by this script.

    Create Table IdentityTable 
    (IntColumn int not null IDENTITY(1,1)
    ,NVarCharColumn nvarchar(100)
    )

    ALTER TABLE [dbo].[IdentityTable] ADD CONSTRAINT [PK_IdentityTable] 
    PRIMARY KEY CLUSTERED ([IntColumn])

I designed Table and Table.Record classes from caller position about getting data like below:

    var db = new HigLabo.Data.SqlServerDatabase("connection string");
    var t = new IdentityTable();
    var records = t.SelectAll(db);
    //Or you can call like this by extension method of DatabaseExtensions class
    //var records = db.SelectAll(t);
    foreach(var record in recordList)
    {
        //Do something...
    }
    //Get record that has a value of IntColumn = 1
    var record = t.SelectByPrimaryKey(db, 1);

And I designed Insert, Update, Delete like below:

    var db = new HigLabo.Data.SqlServerDatabase("connection string");
    var record = new IdentityTable.Record();

    //Insert sample
    //IntColumn's value is automatically inserted(Because IDENTITY(1,1)) on database.
    //So, you don't have to set value.
    record.IntColumn = null;
    record.NVarCharColumn = "MyText";
    var t = new IdentityTable();
    t.Insert(db, record);

    //You can get a IntColumn value that is created on database 
    //after Insert method is executed.
    Console.WriteLine(record.IntColumn);

    //Update sample
    record.NVarCharColumn = "MyNewText";
    t.Update(db, record);

    //Delete sample
    t.Delete(db, record);

You can abbreviate the parameter of database. If you abbreviate, table uses default database set by DatabaseFactory class (same as StoredProcedure class). You can set default database by calling SetCreateDatabaseMethod of DatabaseFactory object.

    DatabaseFactory.Current.SetCreateDatabaseMethod
     ("DbSharpSample", () => new HigLabo.Data.SqlServerDatabase("connection string to DB1"));

    var t = new IdentityTable();

    var record = new IdentityTable.Record();
    record.IntColumn = null;
    record.NVarCharColumn = "MyText";
    t.Insert(record);//Inserted to DB1

I design transaction features the same as StoredProcedures.

    var db = new HigLabo.Data.SqlServerDatabase("connection string");
    using (DatabaseContext dc = new DatabaseContext(db))
    {
        dc.BeginTransaction(IsolationLevel.ReadCommitted);
        var t = new IdentityTable();
        for (int i = 0; i < 3; i++)
        {
            var record = new IdentityTable.Record();
            //Set properties...
            t.Insert(record);//Inserted on transaction
        }
        dc.CommitTransaction();
    }

That is all I want to do from the caller side.

Design Table, Record Classes

I designed 6 classes to CRUD operation.IdentityTable, IdentityTable.Record class will be generated. Others are defined in HigLabo.DbSharp.dll.

ITable
Table<T>
StoredProcedures for CRUD
IdentityTable
TableRecord
IdentityTable.Record

ITable class provides feature for common CRUD operation with TableRecord class. Table<T> class provides implementation of CRUD operation with strongly typed record class. This class has SelectAll, SelectByPrimaryKey, Insert, Update, Delete method for CRUD operation. Table<T> uses StoredProcedure classes for each CRUD operation. Five stored procedures and StoredProcedure classes will be generated by schema editor. The five stored procedures are:

IdentityTableSelectAll
IdentityTableSelectByPrimaryKey
IdentityTableInsert
IdentityTableUpdate
IdentityTableDelete

IdentityTable class will be generated from schema of table in database. TableRecord class has common implementation for record class of each table. Record class is a class that represents a record of table on database. It is generated from schema of table. It is a simple POCO class to keep data of record.

The class diagram is here:

This design also makes me do minimum work because all common operations are defined in these classes.

Get Schema from Database

I must get schema data of columns to generate C# source code. This is a script to get schema data from Microsoft database.

SELECT T01.TABLE_NAME AS TableName
,T01.COLUMN_NAME AS ColumnName
,CASE T03.COLUMN_NAME 
    When T01.COLUMN_NAME Then convert(bit, 1) 
    Else convert(bit, 0) 
End As IsPrimaryKey
,CASE T06.is_table_type 
    When 1 Then 'structured' 
    Else
        Case T06.is_assembly_type
        When 1 Then 'udt' 
        Else 
            Case T01.DATA_TYPE 
            When 'sql_variant' Then 'variant'
            Else T01.DATA_TYPE End 
        End 
    End As DbType
,T01.CHARACTER_MAXIMUM_LENGTH AS ColumnLength
,T01.NUMERIC_PRECISION AS ColumnPrecision
,IsNull(T01.NUMERIC_SCALE,T01.DATETIME_PRECISION) AS ColumnScale
,Case T01.IS_NULLABLE When 'YES' Then convert(bit, 1) Else convert(bit, 0) End As AllowNull
,convert(bit, COLUMNPROPERTY(OBJECT_ID(QUOTENAME(T01.TABLE_SCHEMA) + '.' + _
QUOTENAME(T01.TABLE_NAME)), T01.COLUMN_NAME, 'IsIdentity')) as IsIdentity
,convert(bit, COLUMNPROPERTY(OBJECT_ID(QUOTENAME(T01.TABLE_SCHEMA) + '.' + _
QUOTENAME(T01.TABLE_NAME)), T01.COLUMN_NAME, 'IsRowGuidCol')) as IsRowGuid
,CASE T06.is_table_type 
    When 1 Then T06.name 
    Else
        Case T06.is_assembly_type
        When 1 Then T06.name
        Else '' 
    End
End as UdtTypeName
,'' as EnumValues
FROM INFORMATION_SCHEMA.COLUMNS AS T01
LEFT JOIN (
    SELECT T02.CONSTRAINT_NAME
    , T02.TABLE_NAME
    , T02.COLUMN_NAME
    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS T02
    LEFT JOIN sys.key_constraints AS S01
    ON T02.CONSTRAINT_NAME = S01.name
    WHERE S01.type = 'PK'
) AS T03
ON T01.TABLE_NAME = T03.TABLE_NAME
AND T01.COLUMN_NAME = T03.COLUMN_NAME
Inner Join sys.tables as T04 
ON T01.TABLE_NAME = T04.name 
Inner Join sys.columns as T05 
ON T04.object_id = T05.object_id AND T01.COLUMN_NAME = T05.name 
Inner Join sys.types as T06 
ON T05.user_type_id = T06.user_type_id
WHERE T01.TABLE_NAME = '{0}'
ORDER BY T01.ORDINAL_POSITION

The result of IdentityTable is here.

I will explain how to implement these classes in the next section.

Record Class Feature and Implementation

With these schema information, I can generate IdentityTable.Record class. This class provides the below features:

Properties correspond to table's columns
OldRecord property, SetOldRecordProperty method
CompareAllColumn, IsChanged method
SetProperty method
ConstructorExecuted partial method
GetValue, GetValues, SetValue, SetValues method
CreateValueArray method
GetTableName, GetColumnCount method

IdentityTable.Record has properties that correspond to columns.

    public String NVarCharColumn
    {
        get
        {
            return _NVarCharColumn;
        }
        set
        {
            this.SetPropertyValue(ref _NVarCharColumn, value, 
                                  this.GetPropertyChangedEventHandler());
        }
    }

OldRecord property keeps the previous value before you set a value to the above properties. At first, this property is null and you must call SetOldRecordProperty method before accessing this property. You can set the same value of Record object's property by calling SetOldRecordProperty method.

    var r = new IdentityTable.Record();
    //r.OldRecord is null
    r.NVarCharColumn = "MyValue1";
    r.SetOldRecordProperty();
    //r.OldRecord is not null
    Console.WriteLine((r.NVarCharColumn == r.OldRecord.NVarCharColumn).ToString());//True

Later, I will explain about OldRecord property that is used inside of SelectByPrimaryKey, Update, Delete method of Table class.

CompareAllColumn method is generated from schema like this:

    public override Boolean CompareAllColumn(Record record)
    {
        if (record == null) throw new ArgumentNullException("record");
        var r = record;
        return Object.Equals(this.IntColumn, r.IntColumn) && 
        Object.Equals(this.NVarCharColumn, r.NVarCharColumn);
    }

As you can see, this method compares all properties and returns the result that all columns have the same value.

The IsChanged method compares object itself to OldRecord object. This method is defined in TableRecord<T> class and simply calls CompareAllColumn method.

    public Boolean IsChanged()
    {
        if (this.OldRecord == null) throw new InvalidOperationException
           ("You must call SetOldRecordProperty method before call IsChanged method.");
        return this.CompareAllColumn(this.OldRecord);
    }

You can know some value has been changed or not by calling this method.

SetProperty method is generated from schema like this:

    public void SetProperty(IRecord record)
    {
        if (record == null) throw new ArgumentNullException("record");
        var r = record;
        this.IntColumn = r.IntColumn;
        this.NVarCharColumn = r.NVarCharColumn;
    }

You can easily set properties by using this method. Please note that IdentityTableSelectAll.ResultSet and IdentityTableSelectByPrimaryKey.ResultSet class implement IdentityTable.IRecord interface. You can pass these classes as IRecord parameter to this method.

You can see ConstructorExecuted partial method is called inside constructor of IdentityTable.Record class.

    public Record()
    {
        ConstructorExecuted();
    }
    public Record(IRecord record)
    {
        this.SetProperty(record);
        ConstructorExecuted();
    }
    partial void ConstructorExecuted();

You can define your own constructor that sets a default value of this record.

GetValue, SetValue is generated and GetValues, SetValues method is defined in TableRecord<T> class. With these methods, you can work with CSV, DataTable and other Object[]. GetValue method returns a value as object.

    public override Object GetValue(Int32 index)
    {
        switch (index)
        {
            case 0: return this.IntColumn;
            case 1: return this.NVarCharColumn;
        }
        throw new ArgumentOutOfRangeException();
    }

SetValue method sets a value to property if that value can be convertible to the type of property. And return bool whether you have success to set a value to property or failed.

    public override Boolean SetValue(Int32 index, Object value)
    {
        switch (index)
        {
            case 0:
                if (value == null)
                {
                    return false;
                }
                else
                {
                    var newValue = TableRecord.TypeConverter.ToInt32(value);
                    if (newValue == null) return false;
                    this.IntColumn = newValue.Value;
                    return true;
                }
            case 1:
                if (value == null)
                {
                    this.NVarCharColumn = null;
                    return true;
                }
                else
                {
                    var newValue = value as String;
                    if (newValue == null) return false;
                    this.NVarCharColumn = newValue;
                    return true;
                }
        }
        throw new ArgumentOutOfRangeException("index", index, "index must be 0-1");
    }

As you can see, IntColumn is not nullable, NVarCharColumn is nullable. The behavior is different that depends on the property is nullable or not when you pass null. If the property that cannot be null, value is not set and returns false (case 0). If the property can be null, null is set to the property and returns true (case 1).

GetValues method is defined in TableRecord<T> and returns all value of properties as Object[].

    public Object[] GetValues()
    {
        var count = this.GetColumnCount();
        var oo = new Object[count];
        for (int i = 0; i < count; i++)
        {
            oo[i] = this.GetValue(i);
        }
        return oo;
    }

For example, you can use this method when you work with CSV file.

SetValues method sets all values to properties.

    public SetValueResult[] SetValues(params Object[] values)
    {
        var count = values.Length;
        var bb = new SetValueResult[count];
        for (int i = 0; i < count; i++)
        {
            if (values[i] == TableRecord.SkipSetValue)
            {
                bb[i] = SetValueResult.Skip;
                continue;
            }
            if (this.SetValue(i, values[i]) == true)
            {
                bb[i] = SetValueResult.Success;
            }
            else
            {
                bb[i] = SetValueResult.Failure;
            }
        }
        return bb;
    }

You can skip some property by using TableRecord.SkipSetValue. This method returns SetValueResult[]. SetValueResult is an enum that has three values Success, Skip, Failure.

    public enum SetValueResult
    {
        Success,
        Skip,
        Failure,
    }

You can easily create values by calling the CreateValueArray method.

    public Object[] CreateValueArray()
    {
        return this.CreateValueArray(SkipSetValue);
    }
    public Object[] CreateValueArray(Object defaultValue)
    {
        var count = this.GetColumnCount();
        var oo = new Object[count];
        for (int i = 0; i < count; i++)
        {
            oo[i] = defaultValue;
        }
        return oo;
    }

You can use this method like below:

    var r = new IdentityTable.Record();
    var oo = TableRecord.CreateValueArray();
    oo[1] = "MyText1";
    var results = r.SetValues(oo);
    //Do something...

You can easily set all values from DataTable or CSV file, etc.

GetTableName method returns the TableName of this record, and GetColumnCount returns the column count of this table.

Table Class Feature and Implementation

I generate table class with the below features:

SelectAll method
SelectByPrimaryKey method
Insert method
Update method
Delete method
Save method
BulkCopy

SelectAll returns List<IdentityTable.Record> object. You can set default database by calling SetCreateDatabaseMethod method of DatabaseFactory.

    DatabaseFactory.Current.SetCreateDatabaseMethod
         ("DbSharpSample", () => new SqlServerDatabase("Connection string"));
    var t = new IdentityTable();
    var records = t.SelectAll();
    foreach(var record in records)
    {
        //Do something...
    }

You can use other databases like below:

    var db = new SqlServerDatabase("Connection string");
    var t = new IdentityTable();
    var records = t.SelectAll(db);
    foreach(var record in records)
    {
        //Do something...
    }

SelectByPrimaryKey method returns one IdentityTable.Record that has a same primary key value that you passed. If you pass a value that is not match any record, TableRecordNotFoundException is thrown.

    var t = new IdentityTable();
    var record = t.SelectByPrimaryKey(-123);//TableRecordNotFoundException will be thrown here.

If you want to get null instead of TableRecordNotFoundException, you can call SelectByPrimaryKeyOrNull method to achieve it.

    var t = new IdentityTable();
    var record = t.SelectByPrimaryKeyOrNull(1);//TableRecordNotFoundException is not thrown
    //record may be null

You can insert a record by calling Insert method.

    var t = new IdentityTable();
    var record = new IdentityTable.Record();
    //record.IntColumn is auto increment value.The value will be set on Database.
    record.NVarCharColumn = "MyValue1";
    t.Insert(record);

After insert, you can get a value that is created on database like below:

    //Code to prepare to insert...
    t.Insert(record);
    Console.WriteLine(record.IntColumn);//Show new value of auto increment column

You can update a record by calling Update method.

    var t = new IdentityTable();
    var record = t.SelectByPrimaryKey(1);
    record.NVarCharColumn = "MyValue1";
    t.Update(record);

Please note that record.OldRecord.IntColumn is used to determine what record you will update.

You can delete by calling Delete method like below:

    var t = new IdentityTable();
    var record = new IdentityTable.Record();
    record.SetOldRecordProperty();
    record.OldRecord.IntColumn = 1;
    t.Delete(record);

As Update method, record.OldRecord.IntColumn is used to determine what record you will delete.

All these methods have an overload to pass Database object.

    var db = new SqlServerDatabase("Connection string");
    var t = new IdentityTable();
    var records = t.SelectAll(db);
    var record = t.SelectByPrimaryKey(db);
    t.Insert(db, record);
    t.Update(db, record);
    t.Delete(db, record);

You can execute Insert, Update, Delete at once by using Save method.

    List<ISaveMode> records = new List<ISaveMode>();
    var r1 = new IdentityTable.Record();
    r1.SaveMode = SaveMode.Insert;
    //Set properties...
    records.Add(r1);

    var r2 = new IdentityTable.Record();
    r2.SaveMode = SaveMode.Update;
    //Set properties...
    records.Add(r2);

    var r3 = new IdentityTable.Record();
    r3.SaveMode = SaveMode.Delete;
    //Set properties...
    records.Add(r3);

    var t = new IdentityTable();
    t.Save(records);

DataAdapter is used inside of Save method. And it is faster than execute Insert, Update, Delete method in my roughly test.

You can use BuldCopy extension method of Table class when you use SqlServer.

    var records = new List<IdentityTable.Record>();
    var r1 = new IdentityTable.Record();
    //Set properties...
    records.Add(r1);

    var t = new IdentityTable();
    t.BulkCopy(records);

You can also execute bulk insert with SqlServerDatabase class like this:

    var records = new List<IdentityTable.Record>();
    var r1 = new IdentityTable.Record();
    //Set properties...
    records.Add(r1);

    var reader = new TableRecordReader(records);
    var db = new SqlServerDatabase("Connection string");
    db.BulkCopy(IdentityTable.Name, reader);

BulkCopy method is also faster than execute Insert method.

Indentity, RowGuid, Timestamp Column

In this section, I explain the spec about Indentity, RowGuid, Timestamp column. These columns have features listed below:

Value is generated automatically on database
We want to know the newly assigned value

You cannot update Indentity, Timestamp column, but you can update RowGuid column. So, you don't have to set a value to these columns when you insert record.

    var sp = new IndentityInsert();
    //sp.IntColumn = 1;             //You don't have to set a value
    sp.NVarCharColumn = "MyValue1";
    sp.ExecuteNonQuery();
    Console.WriteLine(sp.IntColumn);//You can get newly assigned values.

You can also do it by Indentity.Record class.

    var t = new Indentity();
    var r = new Indentity.Record();
    r.NVarCharColumn = "MyValue1";
    t.Insert(r);
    Console.WriteLine(r.IntColumn);//You can get newly assigned values 
                                   //after Insert method is executed.

Deep Dive to DatabaseContext, DatabaseFactory, Database Class

In this section, I will explain the inside of DatabaseContext class. You can manage transaction with DatabaseContext like below:

    var db = new HigLabo.Data.SqlServerDatabase("connection string");
    DatabaseContext dc = new DatabaseContext(db, "Transaction1");

When you initialize DatabaseContext object, the instance is assigned to DatabaseContext._Contexts thread static field.

You can see that DatabaseContext._Contexts is Dictionary<String, DatabaseContext> and marked as ThreadStaticAttribute.

    [ThreadStatic]
    private static Dictionary<String, DatabaseContext> _Contexts;

ThreadStatic is an attribute that indicates this static field is unique for each thread.

One thing you must be careful is that if you initialize _Contexts field like below, it will cause NullReferenceException.

    //Caution!! This is invalid code!!
    [ThreadStatic]
    private static Dictionary<String, DatabaseContext> _Contexts = 
                                   new Dictionary<String, DatabaseContext>();

Because at first, you access _Contexts on "Thread1", and next, you access _Contexts field on "Thread2". The initialization of static field is executed only once on "Thread1", _Contexts field is still null on "Thread2" and throw NullReferenceException.

So you must implement ThreadStatic field like below. And use Contexts property to access it.

    [ThreadStatic]
    private static Dictionary<String, DatabaseContext> _Contexts = null;
    private static Dictionary<String, DatabaseContext> Contexts
    {
        get
        {
            if (_Contexts == null)
            {
                _Contexts = new Dictionary<String, DatabaseContext>();
            }
            return _Contexts;
        }
    }

And access by property.

    var contexts = DatabaseContext.Contexts;

That is a pattern about ThreadStatic variable. And please note that you don't have to use lock statement because only the current thread can access this variable.

Next, I will explain about life cycle of DatabaseContext object. For example, you initialize DatabaseContext like below:

    var db = new HigLabo.Data.SqlServerDatabase("connection string");
    DatabaseContext dc = new DatabaseContext(db);

This instance is assigned to _Contexts field with Database object and transactionKey. You can see it in the constructor of DatabaseContext class.

    public DatabaseContext(Database database)
    {
        this.Initialize(database, "", null);
    }
    public DatabaseContext(Database database, String transactionKey)
    {
        this.Initialize(database, transactionKey, null);
    }
    public DatabaseContext(Database database, String transactionKey, 
                           IsolationLevel isolationLevel)
    {
        this.Initialize(database, transactionKey, isolationLevel);
    }
    private void Initialize(Database database, String transactionKey, 
                            IsolationLevel? isolationLevel)
    {
        this.TransactionKey = transactionKey;
        this.Database = database;
        DatabaseContext.SetDatabaseContext(this.TransactionKey, this);
        if (isolationLevel.HasValue == true)
        {
            this.BeginTransaction(isolationLevel.Value);
        }
    }
    private static void SetDatabaseContext(String transactionKey, DatabaseContext database)
    {
        var dcs = DatabaseContext.Contexts;
        if (dcs.ContainsKey(transactionKey) == true) 
                   throw new TransactionKeyAlreadyUsedException();
        dcs[transactionKey] = database;
    }

This instance will be removed from _Contexts field when you call Dispose method of DatabaseContext class.

    public void Dispose()
    {
        Database db = this.Database;

        var dcs = DatabaseContext.Contexts;
        if (dcs.ContainsKey(this.TransactionKey) == true)
        {
            dcs[this.TransactionKey] = null;
        }
        db.Dispose();
    }

So, your instance lives between bracket from instantiate to call Dispose method.

    //dc is assigned to _Contexts field from this line
    using (DatabaseContext dc = new DatabaseContext(db, "Transaction1")) 
    {
        //Do something...
    }   //dc is removed from _Contexts on this line

Here is a sample code to use transaction feature:

    var db = new HigLabo.Data.SqlServerDatabase("connection string");
    using (DatabaseContext dc = new DatabaseContext(db, "Transaction1"))
    {
        dc.BeginTransaction(IsolationLevel.ReadCommitted);
        for (int i = 0; i < 3; i++)
        {
            var sp = new MyTaskTableInsert();
            sp.TransactionKey = "Transaction1";
            //...Set property of MyTaskTableInsert object
            var result = sp.ExecuteNonQuery();
        }
        dc.CommitTransaction();
    }

Here is a code inside of ExecuteNonQuery method:

    public Int32 ExecuteNonQuery()
    {
        return this.ExecuteNonQuery(this.GetDatabase());
    }
    public Int32 ExecuteNonQuery(Database database)
    {
        if (database == null) throw new ArgumentNullException("database");
        var affectedRecordCount = -1;
        var previousState = database.ConnectionState;

        try
        {
            var cm = CreateCommand();
            affectedRecordCount = database.ExecuteCommand(cm);
            this.SetOutputParameterValue(cm);
        }
        finally
        {
            if (previousState == ConnectionState.Closed && 
                database.ConnectionState == ConnectionState.Open) { database.Close(); }
            if (previousState == ConnectionState.Closed && 
                database.OnTransaction == false) { database.Dispose(); }
        }
        return affectedRecordCount;
    }

As you can see, StoredProcedure class gets Database object by calling GetDatabase extension method of IDatabaseContext interface.

Here is a GetDatabase method code:

    public static Database GetDatabase(this IDatabaseContext context)
    {
        Database db = null;
        var dc = DatabaseContext.GetDatabaseContext(context.TransactionKey);
        if (db == null)
        {
            if (context.TransactionKey == "")
            {
                db = DatabaseFactory.Current.CreateDatabase(context.GetDatabaseKey());
            }
            else
            {
                throw new TransactionKeyNotFoundException();
            }
        }
        else
        {
            return dc.Database;
        }
        return db;
    }

IDatabaseContext.GetDatabase method gets DatabaseContext instance declared as dc from DatabaseContext_Contexts static field. And use Database that dc has in private property. Please note that you specify some value to TransactionKey and can't find DatabaseContext, TransactionKeyNotFoundException will be thrown.

If you set sp.TransactionKey to "Transaction2" like below, TransactionKeyNotFoundException will be thrown.

    var db = new HigLabo.Data.SqlServerDatabase("connection string");
    using (DatabaseContext dc = new DatabaseContext(db, "Transaction1"))
    {
        dc.BeginTransaction(IsolationLevel.ReadCommitted);
        for (int i = 0; i < 3; i++)
        {
            var sp = new MyTaskTableInsert();
            sp.TransactionKey = "Transaction2";
            //...Set property of MyTaskTableInsert object
            var result = sp.ExecuteNonQuery();//Throw exception!!!
        }
        dc.CommitTransaction();
    }

So try to get Database from DatabaseFactory.Current.CreateDatabase method when you set TransactionKey="".

    public static Database GetDatabase(this IDatabaseContext context)
    {
        Database db = null;
        var dc = DatabaseContext.GetDatabaseContext(context.TransactionKey);
        if (db == null)
        {
            if (context.TransactionKey == "")
            {
                db = DatabaseFactory.Current.CreateDatabase(context.GetDatabaseKey());
            }
            else
            {
                throw new TransactionKeyNotFoundException();
            }
        }
        else
        {
            return dc.Database;
        }
        return db;
    }

Please note that GetDatabaseKey will return the value that you set when generating source code.

You can set default Database by calling DatabaseFactory.Current.SetCreateDatabaseMethod.

    DatabaseFactory.Current.SetCreateDatabaseMethod
       ("DbSharpSample", () => new SqlServerDatabase("Connection string"));

If you pass Database object as method parameter, it is used.

    var db = new HigLabo.Data.SqlServerDatabase("connection string");
    var sp = new MyTaskTableInsert();
    //...Set property of MyTaskTableInsert object
    var result = sp.ExecuteNonQuery(db);

In the above sample, db is used when sp executes ExecuteNonQuery method. Here is a priority about these rules.

Database object that you pass as method parameter
DatabaseContext if TransactionKey is matched
Database object that is created by DatabaseFactory.Current.CreateDatabase method

Conclusion

DbSharp is my hobby library, so other libraries may have many superior points than DbSharp. There are so many libraries which help you to access data source. Here is a list I found that called ORM, Micro-ORM, DAL Generator.

NHibernate
Entity Framework
Dapper
Repository pattern
Other C# OpenSource library
- Persistence library
- ORM library

You can use these libraries as alternates. It might be better especially if you work with business application.

The main features of DbSharp are:

SqlServer, MySql support (Oracle, PostgreSql will support in the future...)
Transaction transparent
Timestamp, Identity support
UserDefinedType, RowGuid, Geometry, Geography, HierarchyId (SqlServer)
BulkInsert support (SqlServer)
Enum support
Set support (MySql)
No XML mapping files
No performance penalty
No LINQ

If you find other missing features, please contact me. I will improve DbSharp with you!
Now, you can get all the source code from GitHub.

https://github.com/higty/higlabo

History

22^nd May, 2014: Initial version
29^th December, 2021: Updated all source code to .NET6

DbSharpApplication (DAL Generator Tool on .NET6, and released new version on .NET8)