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.
NEWS
2nd February, 2024: I released a new version for .NET8. Please check out this article:
31st December, 2021: I updated all source code to .NET6. Please check it out!
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
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.
You can download DbSharpApplication on .NET6 from the link below:
Sample code:
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.
SelectAllSelectByPrimaryKeyInsertUpdateDelete
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 filesDatabaseKey
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;
var x1 = t.Insert(r);
You may read later sections with DbSharpSample.sln to understand DbSharp.
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:
TypeNameFieldAccessModifierMethodAccessModifierFieldModifierConstructorModifierConstructorMethodModifierMethodParameterMethodPropertyBodyPropertyClassModifierClassInterfacePropertyInterfaceMethodInterfaceNamespaceSourceCode
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.
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()
{
}
public String WriteVB()
{
}
}
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.
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(">");
}
}
}
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(")");
}
}
}
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.
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");
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 result = sp.ExecuteNonQuery(db);
var sp1 = new MyTaskTableSelectBy_TaskId();
sp1.TaskId = sp.TaskId;
var recordList = sp.GetResultSets();
foreach(var record in recordList)
{
}
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");
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);
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();
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();
var result = sp.ExecuteNonQuery(tx1);
}
for (int i = 0; i < 3; i++)
{
var sp = new MyTaskTable2Insert();
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.
DatabaseFactory.Current.SetCreateDatabaseMethod
("DbSharpSample", () => new HigLabo.Data.SqlServerDatabase("connection string to db"));
var sp = new MyTaskTableInsert();
sp.TaskId = Guid.NewGuid();
var result = sp.ExecuteNonQuery();
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.
DatabaseFactory.Current.SetCreateDatabaseMethod
("DbSharpSample", () => new HigLabo.Data.SqlServerDatabase("connection string to db1"));
var db2 = new HigLabo.Data.SqlServerDatabase("connection string");
var sp1 = new MyTaskTableInsert();
var result1 = sp1.ExecuteNonQuery();
using (TransactionContext tx = new TransactionContext(db2))
{
tx.BeginTransaction(IsolationLevel.ReadCommitted);
var sp2 = new MyTaskTableInsert();
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;
}
}
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 = "";
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 = "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);
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)
{
}
}
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 classGetDataTable methodGetResultSets methodEnumerateResultSets 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 = "";
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());
}
}
}
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(); }
}
}
}
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 methodSetOutputParameterValue methodCreateResultSet methodSetResultSet 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);
}
}
}
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.
From the above design, I must get schema data when I generate some of the method's implementation.
CreateCommandSetOutputParameterValueSetResultSet
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)EnumUserTable
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;
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);
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;
}
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);
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[];
}
catch (InvalidCastException ex)
{
throw new StoredProcedureSchemaMismatchedException(this, index, ex);
}
}
To create these methods, I must get this information from schema:
ColumnNameTypeLengthPrecisionScaleIsOutput parameterUserTableTypeNameUdtTypeName
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);
p.SourceColumn = p.ParameterName;
p.Direction = ParameterDirection.InputOutput;
p.Size = -1;
p.SetUdtTypeName("geometry");
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.
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 EnumColumn 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.
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;
udt.Records.Add(record);
var sp = new Usp_Structure();
sp.StructuredColumn = udt;
sp.ExecuteNonQuery();
I design the below four classes:
UserDefinedTableTypeUserDefinedTableType<T>MyTableTypeMyTableType.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));
return dt;
}
public partial class Record : UserDefinedTableTypeRecord
{
private Int64 _BigIntColumn;
public Int64 BigIntColumn
{
get
{
return _BigIntColumn;
}
set
{
this.SetPropertyValue
(ref _BigIntColumn, value, this.GetPropertyChangedEventHandler());
}
}
public Record()
{
}
public override Object[] GetValues()
{
Object[] oo = new Object[28];
oo[0] = this.BigIntColumn;
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
) 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.
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.
You can call asynchronously by using ExecuteNonQuery, GetResultSetsAsync, GetResultSetsListAsync method.
var sp = new MyStoredProcedure();
var rs = await sp.GetResultSetsAsync();
foreach (var item in rs)
{
}
Pretty simple to use.
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);
foreach(var record in recordList)
{
}
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();
record.IntColumn = null;
record.NVarCharColumn = "MyText";
var t = new IdentityTable();
t.Insert(db, record);
Console.WriteLine(record.IntColumn);
record.NVarCharColumn = "MyNewText";
t.Update(db, record);
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);
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();
t.Insert(record);
}
dc.CommitTransaction();
}
That is all I want to do from the caller side.
I designed 6 classes to CRUD operation.IdentityTable, IdentityTable.Record class will be generated. Others are defined in HigLabo.DbSharp.dll.
ITableTable<T>- StoredProcedures for CRUD
IdentityTableTableRecordIdentityTable.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:
IdentityTableSelectAllIdentityTableSelectByPrimaryKeyIdentityTableInsertIdentityTableUpdateIdentityTableDelete
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.
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.
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 methodCompareAllColumn, IsChanged methodSetProperty methodConstructorExecuted partial methodGetValue, GetValues, SetValue, SetValues methodCreateValueArray methodGetTableName, 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.NVarCharColumn = "MyValue1";
r.SetOldRecordProperty();
Console.WriteLine((r.NVarCharColumn == r.OldRecord.NVarCharColumn).ToString());
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);
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.
I generate table class with the below features:
SelectAll methodSelectByPrimaryKey methodInsert methodUpdate methodDelete methodSave methodBulkCopy
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)
{
}
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)
{
}
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);
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);
You can insert a record by calling Insert method.
var t = new IdentityTable();
var record = new IdentityTable.Record();
record.NVarCharColumn = "MyValue1";
t.Insert(record);
After insert, you can get a value that is created on database like below:
t.Insert(record);
Console.WriteLine(record.IntColumn);
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;
records.Add(r1);
var r2 = new IdentityTable.Record();
r2.SaveMode = SaveMode.Update;
records.Add(r2);
var r3 = new IdentityTable.Record();
r3.SaveMode = SaveMode.Delete;
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();
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();
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.
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.NVarCharColumn = "MyValue1";
sp.ExecuteNonQuery();
Console.WriteLine(sp.IntColumn);
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);
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.
[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.
using (DatabaseContext dc = new DatabaseContext(db, "Transaction1"))
{
}
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";
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";
var result = sp.ExecuteNonQuery();
}
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();
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 parameterDatabaseContext if TransactionKey is matchedDatabase object that is created by DatabaseFactory.Current.CreateDatabase method
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
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 supportUserDefinedType, RowGuid, Geometry, Geography, HierarchyId (SqlServer)BulkInsert support (SqlServer)Enum supportSet 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.
- 22nd May, 2014: Initial version
- 29th December, 2021: Updated all source code to .NET6
