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";
   
       LoadMetadata();
   
       foreach (var t in Tables.Values)
       {
           //we don't normally select ROWID from our Oracle tables
           if (t.Columns.ContainsKey("ROWID"))
           {
               t.Columns.Remove("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.

Working with Oracle in .NET – part I : SSMA

There are many ways to connect to an Oracle database from a .NET application. You can use ADO .NET directly, or use an ORM solution, such as NHibernate or the likes. In my workplace, previously we are using NHibernate. But for some reason, we stop using it and revert back to the old ADO .NET way.

However, after some experiments, I’ve found my own way to work with Oracle in a .NET environment. I’m using a library called BLToolkit, along with several other tools. After reading their documentation, I’m convinced enough to use it, and so far I’m very satisfied with the result.

Our development approach is database-first, and this article will only cover that. So if you are using code-first approach, this article would not be relevant to you.

The first thing we need is a data model class that maps to our Oracle database. Since we are already spending our precious time designing the database, we want the class to be auto-generated from the database. BLToolkit provides this kind of functionality using a T4 template. Unfortunately, they don’t support generating data model class directly from an Oracle schema. Since I’m not capable enough to write a T4 template to achieve this, I need a workaround.

My workaround here, is by using SQL Server and a utility called SSMA for Oracle. The idea here is by importing our Oracle database to an SQL Server database, and generate a data model from there. For this purpose, the SQL Server Express Edition is sufficient, so you don’t have to worry about licensing issues.

Importing the database

1. Open SSMA for Oracle and create a new project.
2. Click Connect to Oracle, and provide the information to connect to the Oracle server.

   

   Depends on what user you use to connect to Oracle, you might be shown this warning. In my case, I can safely ignore it and click Continue.

   

3. Click Connect to SQL Server, and provide the information to connect to the SQL Server.

   

   If you are connecting to SQL Server Express, like me, you will get this warning. Just ignore it and click Continue.

   

4. Optionally, you can change the type mapping between Oracle and SQL Server. I usually change the mapping of number to decimal instead of the default float. The reason for this is because I usually use number as a primary key in Oracle. If it is mapped as float in SQL Server, then it would lost its primary key status because SSMA doesn’t seems to allow float to be used a primary key.

   

5. If you made a change to the Oracle schema after connecting to Oracle, you can refresh them by right-clicking the schema name and select Refresh from Database.

   

6. Tick the checkbox next to the schema(s) you want to import, and click Convert Schema.

   

   Optionally, you could also change the target schema in SQL Server.

   While the process is running, you can inspect it through the Output window.

7. On the SQL Server Metadata Explorer, you’ll see the target schema produced by the conversion process. Right click on it, and select Sync with Database. This process will actually update our SQL Server with the new definition.

   

8. Done. Now you should be able to see your shiny new database (or an up-to-date version of the existing ones) in SQL Server.

Handling the problems

You may encounter some issues while doing this conversion process (you can see them in the Error List Window). Once, I got an error setting a float column as a primary key. Changes the type mapping of number from float to decimal (as described in step 4 above) solve this.

However, some issues might not be so important to us. Keep in mind, the main reason we are doing this process are merely to enable us to auto-generate our data model class. So as long as the SQL Server tables has the same column name, column type, primary key, and foreign key specification as their counterpart in Oracle, then it should be good enough for us.

On the next article, I will cover on how to generate the data model class using the T4 template provided by BLToolkit.