.NET Data Modeler for Oracle 11g

• Download source - 52.7 KB

Introduction

This library is designed to model code objects to database data thereby abstracting SQL to the developer, (stored procedures… etc.). It is lightweight but also unpolished and a less robust version of the Entity Framework used in Visual Studio. The name given to it “TACo” is a spun off acronym of the company name I work for and the word “objects”. No, I do not work at a company named Taco.

Requirements

This library requires you to have an Oracle Data Provider Client installed on your system with Oracle Data Access Components http://www.oracle.com/technetwork/topics/dotnet/utilsoft-086879.html. This is, of course, unless you want to modify the source to work for a different database server type in which case this library has no special requirements other than that you have .NET 4.0.

Background

I designed this library in response to requirements at my place of work, a medium sized company with myself and another for software developers. We tried for the longest time to get Microsoft’s Entity Framework to work against our Oracle database but ran into issues with compatibility against Visual Studio 2010. We got it to work on Visual Studio 2012 but this was at our personal home computers, we were restricted to VS2010 and VB.NET (VB.NET I do not mind, I actually like it just as much as C#). Although a bit more effort would have led to success, I decided since I had 2 weeks of RND time that I’d start writing this baby up. The design of implementation for this library was inspired on Django’s modeling system. Now with permission to open source I decided to write on it.

As I mentioned before, this library is not polished. I highly encourage tweakers to give this a look into if interested. NOTE: I imagine it wouldn’t be hard at all to interface other types of database servers with this library (Microsoft SQL, MySQL, etc.), so that’s a huge plus if anyone wants to go for it.

Implementation

Setting Up Your Objects

So from here on out, I’ll refer to this library as “TACo”, the unfortunate name applied to this slim library of code. First, I’ll describe how to enable an object in code to convey itself as an abstraction of data in the database.

VB.NET Code

<EBSTable("Car_Table")>
Public Class Car
 
    <EBSPrimaryKey()>
    <EBSColumn("CARID")>
    Public Property CarID As Integer
 
    <EBSPrimaryKey()>
    <EBSColumn("CARID2")>
    Public Property CarID2 As Integer
 
    <EBSColumn("MAKE")>
    Public Property Make As String
 
    <EBSColumn("MODEL")>
    Public Property Model As String
 
    <EBSColumn("COLOR")>
    Public Property Color As String
 
    <EBSColumn("NOTES")>
    Public Property Notes As String
End Class

The object above is ready and set to be used by TACo and as you can see, this thing is using attributes. Every object used must have an EBSTABLE attribute and at least one Property marked with an EBSCOLUMN attribute. Properties marked with a column attribute can only be single primitive types with the exception of byte arrays and dates. The EBSPRIMARYKEY attribute denotes a property as being a primary key and thereby announcing that it will be used for look-ups and references. As you can see, in this particular example, I used two primary keys. I’m going to imagine a world where CarID and CarID2 for some reason are not distinct in my database but together are distinct combinations. This was one feature we needed for this library due to business requirements but I’m sure most of you will be happy with just one primary key. A primary key is not required to be specified unless you plan on updating your database with your objects.

Concerning the datatypes of properties marked for implementation: You should set your properties to the type of data that is found in the corresponding column in the table. However, any incoming data will be converted into the property type regardless and any data sent as an update to the table will be converted to the type that is in the table. BUT Exceptions will be thrown for differences between strings and chars AND any type of number.

Connecting to Oracle

Most of the connection part is handled for you. The only needed aspect on your end is the connection string. The connection string can be set explicitly via the EntityClientManager Static Class or by placing this config node in your configuration file under appsettings.

<add key="DB_Taco" value="connection string here"/>

Gates

I use the word “EntityGate” as a nuance name for the class in TACo. EntityGates each handle one type of object and are the means of getting the data in and out of the database. You will generally have at least one gate for every type of object you are willing to use. These gates are inheritances of the list object and are thereby iterable. One limitation is that they are not LINQ supported or otherwise iterable against the database, only against the objects retrieved. I’ll explain this in code. We will go ahead and skip the formalities and I’ll show you how to grab our car records from our database.

VB.NET Code

   Dim Cars As New EntityObjectsGate(GetType(Car))
        Cars.Data_Execute()
        For Each Car As Car In Cars
            Console.WriteLine(Car.Color)
        Next
        Console.ReadLine()

The code above will grab ALL car objects in my database and display to me their colors.

VB.NET Code

Dim Cars As New EntityObjectsGate(GetType(Car))
        Cars.Data_Filter("Color = Blue").Data_Or.Data_Filter _
("Color = Red").Data_Execute()
        For Each Car As Car In Cars
            Console.WriteLine(Car.Color)
        Next
        Console.ReadLine()

The above code here will only return cars that have either a color of blue or red.

As code can explain a thousand words, you can see that this library is implemented by declaring a gate and then performing data methods against the gate thereby finally ending with a Data_Execute() method where we actually hit the database. There is plenty of commentary within the library on all other data methods, but here you get the general idea.

It is probably a good thing for me to also mention that you should create all the gates you need at the beginning of your program. Also note that whenever a gate calls a Data_Execute that the gate’s internal list is cleared, then repopulated.

Updating

So after we retrieve the cars we need, we will go ahead and naturally add a note to the first car in the list of cars we got back. Now, we want to update the database with our new set of car objects.

VB.NET Code

Dim FirstCar As Car = Cars(0)
FirstCar.Notes = "Best car ever, not bad for a " & FirstCar.Make
 
Cars.Data_Update()

Given above is code that manipulates the notes property of the first car, and then updates the cars gate, effectively saving our changes in the database…simple. Gates have properties where you can specify the scope of updates, such as whether or not you want the gate to delete, insert, and update rows of objects. The default is to allow all of these things.

Note that there are Async equivalents for both the Update and Execute methods. Also, TACo by default checks for SQL injection.

Known Missing Wish Had Features

• Blobs can be read but not written, just something I have not implemented yet.
• Table relationships have yet to been implemented.
• Conditional lambda statements cannot yet be used for the Data_Filter method as I have not thought of a way of doing so without executing slow non-query SQL that iterates through a table.

Known Possible Improvements

• Reflection is expensive and there are a few parts where this can be optimized.
• The update methodology is pretty monolithic (in my opinion); implementation of writable blobs will probably require a revision of that section of code.
• I’d like to be able to implement the use of complex object types for properties being used in gate objects, perhaps by way of an interface on those types that work with the library. This will probably be in my next update of this article.