SQL Table to Managed Type Creator
Introduction
This application generates and saves assembly at run time which represents the SQL table structure passed as input. The generated assembly at run time has properties defined with set and get accessors as per the column of the DB table.
This application (tool) allows the application developer to integrate this library, generate run time assembly Types which represent SQL Table structure which is given as input. And thus, he can create a collection of those assembly Types and do the operations required.
How Does the Code Work?
The code retrieves the SQL table schema from the SQL DB (SQL Server mainly) and then returns to the DynamicCreator
. Upon each row iteration, the ColumnName
and Data Type is determined and an equivalent .NET type for SQL type is mapped. And then based on the information available, a run time assembly with properties is generated and saved to the disk.
The code has 3 parts/sections:
SqlWorker
SQLToMangedTypeMappingAttribute
DynamicTypeCreator
SqlWorker
The SqlWorker
class helps in connecting and retrieving the data from SQL based on the input values. It gets the schema of the input table from the SQL and returns to the DynamicCreator
.
command = new SqlCommand(string.Concat
("Select Column_name as ColumnName,data_type as DataType from
information_schema.columns where table_name='", tableName,"'"), connection);
SqlDataReader reader = command.ExecuteReader();
table.Load(reader);
As per the code above, I just try to retrieve the schema of the table since only the columns and its data type is all that matters to generate a type at run time and save as an assembly. This schema is then loaded onto the datatable.
SQLToMangedTypeMappingAttribute
The SQLToManagedTypeMapping
is a custom attribute wherein the data type from SQL to .NET conversion or mapping is done to generate the properties as columns.
[AttributeUsage(AttributeTargets.Method,AllowMultiple=true,Inherited=false)]
public class SQLToManagedTypeMappingAttribute : Attribute
{
private string _typeName;
public SQLToManagedTypeMappingAttribute(string typeName)
{
_typeName = typeName;
}
public string TypeName
{
get { return _typeName; }
}
}
This CustomAttribute
class helps in mapping of SQL Types to Managed (.NET/C#) types instead of storing it in the RESX or Txt files. Custom Attributes are chosen for not getting corrupted Resx files or other source. Although this way, we have to use Reflection which is slow process. But to generate Type
at run time, again Reflection has to be used. The matching pattern is done based on the string
input which is a SQL DB Type name, viz. bigint
, varchar
, etc. As of now, SQL Server 2005 types are also supported. Soon, I'll add SQL Server 2008 types as well. But hey, feel free to modify or do some refactoring on this. Thanks!
DynamicTypeCreator
The DynamicTypeCreator
uses Reflection
and Reflection.Emit
APIs to generate the Type
and assembly associating that Type
on run time based on the retrieved DataTable
from the SQL.
Type mapTypeHelper = Assembly.GetExecutingAssembly().GetType
("ConsoleApplication1.SqlToManagedTypeMappingHelper", true, true);
SQLMapping
helper class is loaded by using the above code. This helper class has all the mapping methods which need to be called up at a later point to determine which is the exact .NET type to be converted from SQL DB type.
AppDomain appDomain = Thread.GetDomain();
AssemblyName assmblyName = new AssemblyName();
assmblyName.Name = new StringBuilder(DBCatalog).Replace(DBCatalog[0],
DBCatalog[0].ToString().ToUpper()[0],0,1).ToString();
AssemblyBuilder assemBuilder = appDomain.DefineDynamicAssembly
(assmblyName, AssemblyBuilderAccess.RunAndSave);
ModuleBuilder module = assemBuilder.DefineDynamicModule
(tableName, assmblyName.Name + ".dll");
TypeBuilder typeBuilder = module.DefineType( new StringBuilder(tableName).Replace
(tableName[0],tableName[0].ToString().ToUpper()[0],0,1).ToString(),
TypeAttributes.Public);
mapTypeHelperInstance = Activator.CreateInstance(mapTypeHelper);
As per the above code, Assembly Name which is created on the fly is named with the catalog or DB name which is provided as input. A Dynamic assembly is defined with giving Run and Save access. For each assembly, there should be a module aka class. Hence a module builder is defined having the table name as class name. For the module just built, we need to provide the access modifier. Hence a typebuilder
is built for that module. Then the Mapping
class which was loaded earlier is instantiated, so that the CLR loads it up in memory for execution thus helping us call methods in it.
FieldBuilder fieldBuilder =
typeBuilder.DefineField(string.Concat("_", columnName.ToLower()),
dTableColumnType, FieldAttributes.Private);
PropertyBuilder propBuilder = typeBuilder.DefineProperty(
new StringBuilder(dRow["ColumnName"].ToString()).Replace
(columnName[0], columnName.ToUpper()[0], 0, 1).ToString(),
System.Reflection.PropertyAttributes.HasDefault,
dTableColumnType, Type.EmptyTypes);
MethodBuilder getMethodBuilder = typeBuilder.DefineMethod("Get" + propBuilder.Name,
MethodAttributes.Public | MethodAttributes.SpecialName |
MethodAttributes.HideBySig, dTableColumnType, null);
The Type
and module have been defined so far. Now it's time for us to define the Fields in the class. These fields are columns of the DB table. Hence we need to iterate for each row from the DataTable
which was loaded earlier from DB. All fields are defined as private
variables of this class. For each of those defined Fields using FieldBuilder
class, a property should also be defined. Hence a PropertyBuilder
class is used. Since we already know in C#, the Get
and Set
property is actually a method internally. Hence MethodBuilder
class is used to generate those methods. The special attributes such as MethodAttributes.Public | MethodAttributes.SpecialName | MethodAttributes.HideBySig
are passed as arguments to MethodBuilder
.
ILGenerator ilGenerator = getMethodBuilder.GetILGenerator();
ilGenerator.Emit(OpCodes.Ldarg_0);
ilGenerator.Emit(OpCodes.Ldfld, fieldBuilder);
ilGenerator.Emit(OpCodes.Ret);
propBuilder.SetGetMethod(getMethodBuilder);
So far, we have all defined Module, Type and Fields with Get
and Set
methods associated. Now for each of these fields generated based on the DB Table Column type and name, we need to generate the IL associated with it. Hence in the above code shown is one such example of Get
method for a property. Here, we use ILGenerator
class to generate the IL code. The Reflection.Emit
APIs are used to generate the IL code with the appropriate Opcodes. First since this is a Get
method, a corresponding private
fields value has to be returned. Hence the following steps are carried out:
- The instance is loaded by using
Ldarg_0
opcode. - The
fieldBuilder
akaprivate
fields value has to be loaded to the memory. HenceOpCodes.LdFld
opcode is used to load the value. - Then
OpCodes.Ret
is used to return the value which was loaded earlier.
Similarly, for Set
method along with this OpCodes.Stfld
code is used to set the value which was passed as an argument.
This building up of Set
and Get
methods is done for all the rows read from the datatable
.
Type myCustomType = typeBuilder.CreateType();
assemBuilder.Save(string.Concat(assmblyName.Name, ".dll"));
Since building up of all fields and associated properties are done, now is the time to Create the type we just generated and then save it up to the disk for later usage. Hence AssemblyBuilder
's method Save
is called.
private Type GetSqlToManagedType(object p)
{
Type type = null;
foreach (MethodInfo method in mapTypeHelper.GetMethods())
{
var attrList = method.GetCustomAttributes(typeof
(SQLToManagedTypeMappingAttribute), false).Where(item =>
(item as SQLToManagedTypeMappingAttribute).TypeName.Equals(p));
if (attrList.Count() == 1)
{
type = Type.GetType(method.Invoke(mapTypeHelperInstance, null).ToString());
break;
}
}
return type;
}
The above method shown does the mapping of each DB Table's column data type to the corresponding .NET typed to generate the Assembly at run time. To do that, we do the following steps:
- From
MapType
helper class, we get all the methods available. - Once the methods list is available, we then get all
CustomAttributes
for that method. Please note that a search criteria is necessary forGetCustomAttributes()
methods. TypeSQLToManagedTypeMappingAttribute
is passed as argument as a searching criteria. If this searching criteria is not provided, the CLR looks for all the methods in the object hierarchy, this throws an exception forSystem.Object
class methods sometimes. - For each of those custom attributes, a
where
loop is run to match the type we want. - When
attrList
has1
item in it, that means we have a matching method which needs to be invoked. If the list has more items, then there is a duplication of attributes which is wrong. - The matching method is invoked using
method.Invoke
API.
Feel free to use, add features, etc. Thanks! Enjoy!!
发表评论
P2c5I4 Thank you ever so for you post.Really thank you! Great.
xWf7GH This is one awesome article.Really thank you! Keep writing.