• Download source - 50.24 KB

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:

1. The instance is loaded by using Ldarg_0 opcode.
2. The fieldBuilder aka private fields value has to be loaded to the memory. Hence OpCodes.LdFld opcode is used to load the value.
3. 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:

1. From MapType helper class, we get all the methods available.
2. Once the methods list is available, we then get all CustomAttributes for that method. Please note that a search criteria is necessary for GetCustomAttributes() methods. Type SQLToManagedTypeMappingAttribute 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 for System.Object class methods sometimes.
3. For each of those custom attributes, a where loop is run to match the type we want.
4. When attrList has 1 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.
5. The matching method is invoked using method.Invoke API.

Feel free to use, add features, etc. Thanks! Enjoy!!