Working with Oracle in .NET – part II : T4

In the previous article, we have successfully generate an SQL Server database which has identical properties to our Oracle schema. With this SQL Server database in our disposal, generating the data access layer (DAL) is a trivial task. BLToolkit has provided a set of T4 template to achieve this. Basically here I’m only rephrasing the steps documented here by the BLToolkit team themselves, adding some adjustments as necessary to fit my needs.

Generating the DAL

  1. Download BLToolkit source code, and unzip it somewhere.
  2. Start Visual Studio, create a new class library project.


  3. Add everything under the Source\Templates folder of BLToolkit source code into your project.


  4. Add a new T4 file (*.tt) into your project.


  5. Edit your new T4 file into the following :
    <#@ template language="C#" hostspecific="True"        #>
    <#@ output extension=".generated.cs"                #>
    <#@ include file="BLToolkit.ttinclude"                #>
    <#@ include file="BLT4Toolkit.ttinclude"            #>
    <#@ include file="MSSQL.ttinclude"                    #>
    <#@ include file="Renamer.ttinclude"                #>
    <#@ include file="ValidationAttributes.ttinclude"    #>
    <#@ include file="WCFAttributes.ttinclude"            #>
        ConnectionString = "Data Source=.;Initial Catalog=your_database_name;Persist Security Info=True;User ID=sa;Password=your_password";
        foreach (var t in Tables.Values)
            //we don't normally select ROWID from our Oracle tables
            if (t.Columns.ContainsKey("ROWID"))
        }    GenerateModel();

    Needless to say, you should modify the connection string to match your SQL Server database setting.

    Here is some list of what the include files are good for :

    1 BLToookit.ttinclude Required.
    2 BLT4Toolkit.ttinclude Optional. You need T4 Toolbox to use this file. This is useful to automatically set the namespace and the class name of the generated class to your project name and your T4 file name respectively. If you omit this, you should specify them manually using Namespace and DataContextName variable.
    3 MSSQL.ttinclude Required. Used to connect to the SQL Server database.
    4 Renamer.ttinclude Optional. I use this to re-style the naming convention of Oracle database (using all-capital and underscore as word separator) to .NET name styling (using PascalCase).

    A little note, I have to modify this file a bit so it produce the result I desired. If you are wondering what changes I made, it was on the 10th line of the file, I change the name to name.ToLower(). And also, I added a few code to rename the foreign keys too. You can find the complete code here.

    5 ValidationAttributes.ttinclude Optional. Use this if you want to decorate your class with data validation attribute.
    6 WCFAttributes.ttinclude Optional. Use this if you want your class ready to be exchanged using WCF technology. It adds DataContract and DataMember attribute to your class. You will need a reference to System.Runtime.Serialization assembly in your project in order to successfully compiled the generated class.
  6. By now, your T4 files should ready for action. Open the properties window for the file, and set the custom tool to TextTemplatingFileGenerator.


    You should see this dialog box :


    Click OK, and your class should be generated.

  7. Done, at least for the generation part :). You should see the generated class right under the *.tt file.


    The next time you need to re-generate the class, you could right-click on the *.tt file, and select Run Custom Tool.


  8. To make our newly generated class successfully compiled, we need to add a reference to the BLToolkit assembly in our project. Personally, I do this using NuGet to ensure I got the latest version.

    If you opted to include WCFAttributes.ttinclude in your T4 file, you will also need a reference to System.Runtime.Serialization assembly.


  9. Now we are really done. To verify this, try compiling your project.

Solving the issues

Some problem might occurred, and your class might still not be compilable. You should inspect your compiler error messages to know what the problems are, and try to solve them accordingly.

You got two options to fix an uncompilable class. You can edit the class manually. This is simpler, but you have to repeat this step every time the class is re-generated. The more sophisticated one is by updating the T4 to fix the problem. This way, you get a more permanent fix.

Once everything is set up this way, our workflow is much simpler now. If you ever need to update your Oracle database definition in the future, synchronize the SQL Server database using SSMA for Oracle, and simply run custom tool on your T4 file to update the DAL.

On the next article, I will cover on how to utilize our generated data model class in a .NET application.