Using a LLBLGen-generated data-access tier

An article which describes how to use the data-access tier, generated by the free, open source data-access tier generator for .NET: LLBLGen. The sourcecode for LLBLGen v1.2 in C# is included, plus the generated data-access tier used in this article (C# and T-SQL) and the creation script for the data

Introduction

This article describes the usage of a generated data-access tier, using LLBLGen, which is a
free, open source data-access tier generator for .NET. The source code for LLBLGen v1.2, the
generated data-access tier used in this article and the script to create the database used
to generate the data-access tier, are downloadable at the top of the article. The
code snippets
are meant to illustrate the ease of use of the generated data-access tier, not to illustrate
how to write a windows forms application. Therefore this windows forms application is not discussed
deeply, since that would make this article too big.

Data model

For this example, we're using a simple but feature rich enough data model
which has 3 tables: one for Employees, one for Departments, and one table
which is the result of the n:m relation between Employees and Departments.
Totally not usable in production environments, but the tables have all
ingredients production databases will have, so these tables will illustrate
the usage of LLBLGen and how to use the produced code. The data model is
shown below:

The example data model

The fields document themselves, but there are two FK constraints that need some comments.
The FK constraint 'FK_DepartmentEmployees_Departments' is a FK constraint between
DepartmentEmployees.DepartmentID (FK) and Departments.DepartmentID (PK). The FK
constraint 'FK_DepartmentEmployees_Employees' is a FK constraint between
DepartmentEmployees.EmployeeID (FK) and Employees.EmployeeID (PK).
DepartmentID and
EmployeeID are both Identity fields.

The data model contains an Image field, NULL values, Unicode fields, text fields, identity fields
and a date-time field, plus the Primary Key in DepartmentEmployees contains more than one field, which
will result in more Delete and Select routines, so enough material to check what LLBLGen can do for us.

To set up this database, create a new database called 'TestDBLLBLGen' in SQLServer or MSDE
and run the script create_testdbllblgen_database.sql, which comes with this article. If you want to re-generate the
data-access tier which comes with this article, compile the LLBLGen v1.2
source code which is linked at the top
of this article. The following steps assume you've compiled that source code or have downloaded the LLBLGen
executable from it's website at: http://www.sd.nl/software.

Generating code.

We start LLBLGen and connect to the test database, TestDBLLBLGEn with the three tables.
We select all three tables and on the .NET code tab we choose for C# as the output language, 'TestDBLLBL' for
the namespace, 'cls' for the Class prefix, comment support, NULL value support (one of our tables has
NULL values) no COM+ services, Hungarian Coding style, no prefixing of properties and for the
connection string we choose for the app.config option.

On the T-SQL code tab, we select all options, use as prefix 'pr_' and no fields will be marked
as Excluded. On the Generator tab, we select a directory for the C# classes and the T-SQL file and
hit 'Start generation'. After a second or so, the generation is completed and our data-access tier
is ready. This will result in the same code found in the zip file: TestDB_data_access_tier.zip, linked
at the top of the article, which forms the total data-access tier we're going to use in the following
code snippets.

Compiling the generated .NET classes

We open a cmd window (dos box) and type 'vsvars32.bat', which will load up shell variables and will adjust
the path to the .NET SDK tools. At the command line we 'cd' to the directory where our generated C# classes
are stored and type:

csc /out:testdbllbl.dll /target:library /optimize *.cs

This will result in a dll, testdbllbl.dll, which contains the assembly for the
generated code. We also could have created a C# library project in Visual Studio.NET and could have
added the .cs files to that project and compile it. It's more convenient to do it that way, because
you then can add that project to your application solution and step into the generated classes when debugging
your application. Because we didn't include any COM+ services, we don't have to specify additional
assemblies at the command line. When you do include COM+ services using the LLBLGen GUI, you have to
specify addition assemblies in the csc.exe command, but that's a no-brainer.

Installing the T-SQL code

We open Query Analyzer and load the generated T-SQL file. Connect to the
test database - TestDBLLBLGen
and run the script. It should take a second or two to install all stored procedures. In
Enterprise Manager or in Query Analyzer (SQLServer 2000 version only) you can see the list of
stored procedures installed. You'll notice that the table DepartmentEmployees doesn't have any
Update stored procedures. This is because all fields in the table are part of the Primary Key,
and therefore can't be updated. (Some developers say that this should be possible, but you're then
creating new relations between the tables Departments and Employees, not updating
a current relation, which should be the purpose of the Update command in this context).

Using the data-access layer

After the installation of the stored procedures and the compilation of the .NET classes, the data-tier
is ready to rock and roll, thus can be used in an application that targets the
test database.
We start a new application in Visual Studio.NET, a C# windows forms application. First we add a reference
to our data-access tier assembly, testdbllbl.dll. Then we add the app.config file, generated by LLBLGen
to the new project. This way, the project is able to access the database using the connection string
located in the app.config file. The reason why you have to add the app.config
file to the data-access
tier using project and not to the data-access tier project, is because the data-access tier
code is run inside the application domain of the data-access tier using application, and thus
will result in the usage of the app.config file of that project, in this example our C# windows
forms application. In each code file which uses the generated data-access .NET classes, we have to
add a reference to the namespace of our data-access tier: 'using TestDBLLBL;'

Storing new records

We define a dull form which has controls for all necessary data to be stored in the Employees table. For
the image, we simply have a textbox where the filename should be typed in. To illustrate the NULL value
support, we simply pass a NULL value for the middle name for each Employee we create. We could have build in
checks for each field, but that would enlarge the code snippet but wouldn't add extra info to the example.
The method to create a new employee record in the database, reading the values on the form would look like this:

Let's walk through this code to make you understand it.
First a new object of type clsEmployees is created. This is in fact a class we just generated,
clsEmployees, which holds all code needed to maintain the Employees table.
The 4 lines below the comment line are loading a file into a byte array which will be passed on as the Image.
The 8 lines following that code are initializing the properties of the data-access tier object. The loaded
image-data is passed to the property 'Picture', we've chosen a date-time-picker control for the Started date, that
value is passed to the property StartedOn, etc. Notice the
SqlString.Null value passed to MiddleName. We said earlier
that this field would be set to NULL no matter what. This illustrates how to pass
NULL values to the data-access tier.
Because MiddleName is of type SqlString, we use
SqlString.Null, but we should use another Sql* type when the property
is of another type, of course.

The try block will call the Insert() method of the data-access tier object which will insert the data into a new row
in the database. When an error occurs, an exception will be thrown by the data-access tier class, and this exception
is caught in the catch() block. If no errors occur, the next statement is executed, which reads the new
EmployeeID
from the property, since EmployeeID is an Identity field, the new value is returned in the property of that field.
The whole creation of the new employee is now finished.

Reading lists of records

To view which employees we've already stored in the database, we can select them from the database and
view them on a form. We define a form where the user clicks a button and all the employee records are
shown in a data grid control, called dgEmployees, on the form. The code below does all that magic for you,
and is called from the click event handler delegate of the button:

Deleting rows

We want to delete a row from the Department table, because a
Department is closed. This will take
two actions: first we have to remove all rows with the department in question from the
DepartmentEmployees
table, and then the row for the department from the Departments table. The following code does all that:

This will remove the given Department from the database without violations of FK constraints
and illustrates the power of the generated code. Production code should use COM+ transactions
for this code, since it will hurt database integrity if the first call succeeds, but the
second one fails. To avoid this inconsistency, we can use COM+ transactions, but we can also
use the ConnectionProvider object functionality of LLBLGen to make the two calls run inside
one transaction which is rolled back when something goes wrong.

Using the ConnectionProvider class

Below is a code snippet which does the same as the code snippet above, but now it uses the
ConnectionProvider class, also generated by LLBLGen, to share a
SqlConnection object and
transaction among the two data-access objects so both calls of the two methods are ran in one
transaction (using ADO.NET transaction functionality).

The code is a little longer than the routine without the ConnectionProvider object,
but it clearly shows the ease of use of this new class, generated by LLBLGen. For
business logic layers which are not part of a COM+ enabled application, this is the way
to go to make use of the generated data-access tier in a robust, reliable way, when transactions
are needed.

History

13 Aug 2002 - updated source code

25 Aug 2002 - updated source code

License

This article, along with any associated source code and files, is licensed under The BSD License