Click here to Skip to main content
Click here to Skip to main content

DbSharp

, 11 Jul 2014
Rate this:
Please Sign up or sign in to vote.
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.

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 2013, and SQL Server 2012.

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.

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
  • 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
  •  

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 to your feedback.

How to use DbSharp?

Here are 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 VisualStudio, add generated file and compile it to DLL
  • Use DLL in your application

In the sample project (DbSharpSample.sln), you can see this 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 ManagementStudio 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->Import Object.

Press connect button, ensure all object selected and press execute button.

Table, StoredProcedure, UserDefinedTableType 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 StoredProcedure 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 cover 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 about 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 to this same file. It might 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 same TFS for some reason (different company, poor environment, etc.) So, I redesign the TypeName class only manage about the information of data and the SourceCodeGenerator class only handle 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 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 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 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 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 call 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 object 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 compare 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 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 (DatabaseContext dc = new DatabaseContext(db))
    {
        dc.BeginTransaction(IsolationLevel.ReadCommitted);
        for (int i = 0; i < 3; i++)
        {
            var sp = new MyTaskTableInsert();
            //...Set property of MyTaskTableInsert object
            var result = sp.ExecuteNonQuery();
        }
        dc.CommitTransaction();
    }

It looks simple to use. All ExecuteNonQuery, GetResultSets, GetDataTable methods executed Inside using(DataContext ...) { } bracket are executed on same transaction. The only thing you must do is to bracket your code by using(DataContext ...) { }. Inside constructor of

    using (DatabaseContext dc = new DatabaseContext(db))

is same as

    using (DatabaseContext dc = new DatabaseContext(db, ""))

You use empty string as transaction key.

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 (DatabaseContext dc1 = new DatabaseContext(db1, "tx1")))
    {
        using (DatabaseContext dc2 = new DatabaseContext(db2, "tx2")))
        {
            dc1.BeginTransaction(IsolationLevel.ReadCommitted);
            dc2.BeginTransaction(IsolationLevel.ReadCommitted);
            for (int i = 0; i < 3; i++)
            {
                var sp = new MyTaskTable1Insert();
                sp.TransactionKey = "tx1";
                //...Set property of MyTaskTableInsert object
                var result = sp.ExecuteNonQuery();
            }
            for (int i = 0; i < 3; i++)
            {
                var sp = new MyTaskTable2Insert();
                sp.TransactionKey = "tx2";
                //...Set property of MyTaskTableInsert object
                var result = sp.ExecuteNonQuery();
            }
            dc1.CommitTransaction();
            dc2.CommitTransaction();
        }
    }

Multi database, Transaction is supported by 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 schema 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 (DatabaseContext dc = new DatabaseContext(db2))
    {
        dc.BeginTransaction(IsolationLevel.ReadCommitted);
        var sp2 = new MyTaskTableInsert();
        //...Set property of MyTaskTableInsert object
        var result2 = sp2.ExecuteNonQuery();//Executed to db2 because of inside DatabaseContext bracket.
        dc.CommitTransaction();
    }

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

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

Each class has each reponsibility.

  • StoredProcedure class has common operation of stored procedure. This class's common operation are GetStoredProcedureName, ExecuteNonQuery, CreateCommand, SetOutputParameterValue method.
  • MyTaskTableInsert class will be generate 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 are 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 inherit from DatabaseRecord class too.)
  • StoredProcedureWithResultSet class inherit from StoredProcedure class. This class add some common operation of stored procedure that return result set. This class's common operation are GetDataTable, GetResultSets, EnumerateResultSets method.
  • StoredProcedureWithResultSet<T> class inherit from StoredProcedureWithResultSet class. This class's common operation are CreateResultSet, SetResultSet method. This class also add 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 defined at 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, set value to field, and raise PropertyChanged event.

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

Next, I explain StoredProcedureWithResultSet class that return 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 represent 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 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 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 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 me easy to implement generator. In the next section, I will explain the detail 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 is 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 these information from schema.

  • ColumnName
  • Type
  • Length
  • Precision
  • Scale
  • IsOutput parameter
  • UserTableTypeName
  • UdtTypeName

So, I create a T-Sql script file to get 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 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 thee code blocks for each type in above.

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

For example, I create it against to 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 to 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 does not implement Oracle, PostgreSql. If you are a specialist on 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 above list for Oracle, PostgreSql, I greatly appreciate and I will merge your code to my library.

Enum support

What is a Enum support? I design Enum support like this.

  • C# property is defined as Enum and you can get 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 prevent you from inserting invalid value to database. I must implement ToEnum method in StoredProcedure class that create 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 exist in database, StoredProcedure.ToEnum<MyEnum> throw InvalidEnumDataException

UserDefinedType

In this section, I will show you how 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 can not 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 can not UserDefinedType as output parameter

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

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

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

Design API for CRUD operation

This section, I will show you how 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 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 provide feature for common CRUD operation with TableRecord class. Table<T> class provide implementation of CRUD operation with strongly typed record class. This class has SelectAll, SelectByPrimaryKey, Insert, Update, Delete method for CRUD operation. Table<T> use StoredProcedure classes for each CRUD operation. Five stored procedure and StoredProcedure classes will be generated by schema editor. The five stored procedure 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 represent of 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 minimum working because all common operation 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 next section.

Record class feature and implementation

With these schema information, I can generate IdentityTable.Record class.This class provide 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 keep the previous value before you set a value to above properties. At first, this property is null and you must call SetOldRecordProperty method before access this property. You can set 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 column has same value.

The IsChanged method compare object itself to OldRecord object. This method defined in TableRecord<T> class and simply call 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 class 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 set 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 return 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 set a value to property if that value can be convertible to the type of property. And return bool whether you 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 can not be null, value is not set and return false (case 0). If the property that can be null, null is set to the property and return true (case 1).

GetValues method is defined in TableRecord<T> and return 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 set 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 return SetValueResult[]. SetValueResult is a enum that has three value Success, Skip, Failure.

    public enum SetValueResult
    {
        Success,
        Skip,
        Failure,
    }

You can easily create values by calling 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 value 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 below feature.

  • 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 database 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 method has a 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 very fast 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 on below.

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

You can not update Indentity, Timestamp column, but you can update RowGuid column. So, you don't have to set a value to these column 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 a attribute that indicate this static field is unique for each thread.

One thing you must be care 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 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 live 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 get 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 get 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 thrown.

If you set sp.TransactionKey to "Transaction2" like below, TransactionKeyNotFoundException will 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 above sample, db is used when sp execute ExecuteNonQuery method. Here is a priority about these rule.

  • 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 point than DbSharp. There are so many library which helps you to access data source. Here is a list I found that called ORM, Micro-ORM, DAL Generator.

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

The main feature of DbSharp is

  • 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 feature, please contact me. I improve DbSharp with you!

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Higty
Web Developer
Japan Japan
I'm Working at Software Company in Tokyo.

Comments and Discussions

 
QuestionDB Engine support PinmemberMy Name Is GUID14-Jul-14 11:25 
AnswerRe: DB Engine support PinpremiumHigty14-Jul-14 15:46 
QuestionError happened when I compiling the sourcecode in vs2012 -- namespace { HigLabo.Component } missing. Pinmemberdjhuang23-May-14 15:16 
AnswerRe: Error happened when I compiling the sourcecode in vs2012 -- namespace { HigLabo.Component } missing. PinpremiumHigty23-May-14 22:43 
AnswerRe: Error happened when I compiling the sourcecode in vs2012 -- namespace { HigLabo.Component } missing. PinpremiumHigty23-May-14 22:47 
QuestionExcellent!! PinmemberMickt198523-May-14 12:01 
AnswerRe: Excellent!! PinpremiumHigty23-May-14 13:54 
GeneralRe: Excellent!! PinmemberMickt198525-May-14 8:19 
GeneralRe: Excellent!! PinpremiumHigty25-May-14 22:27 
GeneralRe: Excellent!! PinpremiumHigty1-Jun-14 21:20 
GeneralRe: Excellent!! PinmemberMickt19853-Jun-14 11:41 
GeneralMy vote of 5 PinmemberPraneet Nadkar22-May-14 20:37 
GeneralRe: My vote of 5 PinpremiumHigty22-May-14 21:41 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140821.2 | Last Updated 11 Jul 2014
Article Copyright 2014 by Higty
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid