DAL Comparison Part 2

Okay I promised a part 2 examination of LLBLGen a while ago. So I thought I would get this done in a timely manner.  Okay, maybe not so timely of a manner.  I’ve actually changed this from a comparison to an examination.  This should give some ideas of how to use a widely available open source DAL.  Besides the standard ideas of how to actually use LLBLGen, I’ll go over how to modify this in another part of the series. 

 

Data Access Layer (DAL) generators can cut time down when building your applications.  As the DAL has evolved in .NET many options have  become available since I originally started this article.  I will touch on a new closely related tool, the O/R mapper.  I will also walk through creation of a DAL with the open source version of LLBLgen, and go over the output.

 

O/R Mappers

O/R mappers create a Data Persistance Layer between your data and your business logic.  http://www.asp.net/Forums/ShowPost.aspx?tabindex=1&PostID=382083 is a link on the asp.net architecture forums about this problem.  While an examination of O/R mappers is a best left for another series of articles, I wanted to mention them, along with a simple explanation. 

 

An O/R mapper should allow you to specify your tables, views etc. and create business objects that you can program against.  Normal CRUD operations are handled (not usually through Stored Procedures).   The 2 .NET O/R mappers I am aware of are LLGLGen Pro and Entity Broker.

 

LLBLGen Open Source

I first became aware of this generator early in 2002 and played with it for a little while.  LLBLGen is an open source DAL generator with source in C# (can be found at http://www.sd.nl/software/ ) that interacts with SQL Server.  You can download either an executable or a zip file with source code.  The source is in C#, and comes in a Visual Studio project. 

 

After starting up either the executable, or the project in Visual Studio, you are presented with a Windows form.   The GUI allows you to connect to a server by specifying the server name, database, and login information.

 

Once you connect, you specify which tables or views you want to create classes and stored procedures for.

After selecting which tables or views to create go to the .Net Code tab.  Here you can select the code style of the class.  These options include style options like, language, Namespace, Hungarian or Camel Case code, whether to include comments in the class and where your connection string will be stored.

The T-SQL Code tab has similar options for creating the stored procedures that the LLBLGen created classes utilize.  Your options include types of SP’s, prefix name, format of your select statements and more.   

 

Finally, there is the Generator tab.  Use this tab for generating the classes and stored procedures, once you are finished selecting the T-SQL and .Net Code options.  You can specify the directories where the generator will create your SQL scripts and classes.

 

Once you have generated the classes and stored procedures, you are ready to integrate these into your application.  Before you can use the generated DAL classes, you must run the generated stored procedure scripts against your database.  That means running them using osql.exe, or using Query Analyzer. 

 

A great customization of this program would be to create a script that would utilize osql to run the scripts against your database.  But that is beyond the scope of this article.

 

Utilizing the DAL in your application

Now we can use the DAL in an application.  I’ll show a sample of code binding the dal to a datagrid.  The DAL generated was for the pubs database that ships with SQL Server.  There are many ways to do this, what I have done is to put the DAL into a separate project in Visual Studio called PubsDAL.

One of the classes generated is called Authors, based on the Authors table in Pus.  For a web page I want to display all the authors.  To do this, I create a Web Page with a datagrid.  To display the authors, I’ll  utilize the Authors class and bind it to a datagrid.

 

Here’s how to create the code to get all the data and bind it to the grid (in VB.NET). 

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        'Put user code to initialize the page here

        Dim myAuthors As New pubs.DAL.Authors

        Me.DataGrid1.DataSource = myAuthors.SelectAll()

        Me.DataGrid1.DataBind()

 

 

End Sub

With only 3 lines, I have bound to a datagrid.  Doing editing etc. can be similarly done as well. 

The connection string can be found in the web.config.  In this instance I am using the SelectAll method to return a DataTable that is bindable.  LLBLGen can generate a SelectOne that is bindable, as well as Insert, Delete, Update methods.  If there’s any interest we can see how to do those as well in a future article. 

 

The class contains the field names as properties.  These properties are SQL data types.  In the next part of this examination, I’ll look at changing that through customizing the generating code.  Setting .Net data types to the SQL data types takes using the op_implicit and op_explicit undocumented methods.  I find this method cumbersome to use from standard applications. 

 

This is a flaw that should be changed, if LLBLGen actually cost money.  (LLBLGen Pro does, but this is the open source version). Since I can change it myself, that’s why we’ll look at this in a future article.

 

Summary

We’ve gone through creating the autogenerated Stored Procedures and Classes. I’ve then shown you how to use the DAL generated by LLBLGen.  The next part in this series will demonstrate how to modify the source for LLBLGen and implement that as your DAL generator.  Until then, KEEP ON CODING!

 

EricLandes

Your CorporateCoder