Using a LLBLGen-generated data-access tier

• Download LLBLGen v1.2 source code (C#) - 306 Kb
• Download generated data-access tier source - 16 Kb
• Download test database create script (T-SQL) - 2 Kb

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:

/// <summary>

/// Purpose: creates a new employee record.

/// </summary>

private void CreateNewEmployee()
{
    clsEmployees oEmployees = new clsEmployees();

    // load image from file

    FileStream fsBLOBFile = new FileStream(tbxImageFile.txt, 
        FileMode.Open, FileAccess.Read);
    Byte[] bytBLOBData = new Byte[fsBLOBFile.Length]; 
    fsBLOBFile.Read(bytBLOBData, 0, bytBLOBData.Length);
    fsBLOBFile.Close();

    oEmployees.Picture = bytBLOBData;
    oEmployees.StartedOn = dtpEmployeeStartedOn.Value;
    oEmployees.Description = tbxEmployeeDescription.Text;
    oEmployees.FirstName = tbxEmployeeFirstname.Text;
    oEmployees.Initials = tbxEmployeeInitials.Text;
    oEmployees.MiddleName = SqlString.Null;
    oEmployees.LastName = tbxEmployeeLastName.Text;
    oEmployees.SecurityID = int.Parse(tbxSecurityID.Text);

    try
    {
        bool bResult = oEmployees.Insert();
        int iEmployeeID = (int)oEmployees.EmployeeID;

        // Do something with the iEmployeeID value

        // ...

    }
    catch(Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

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:

/// <summary>

/// Purpose: refreshes the list of employees 

/// in the grid from the list

/// stored in the database

/// <summary>

private void RefreshEmployeeList()
{
    clsEmployees oEmployees = new clsEmployees();

    try
    {
        DataTable dtEmployees = oEmployees.SelectAll();
        dgEmployees.DataSource = dtEmployees;
    }
    catch(Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

It can't get any simpler than this, folks!

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:

/// <summary>

/// Purpose: Deletes a department from the database.

/// <param name="iDepartmentIDToDelete">

/// The ID of the department which should be deleted.

/// </param>

/// <summary>

private void DeleteDepartment(int iDepartmentIDToDelete)
{
    clsDepartments oDepartments = new clsDepartments();
    clsDepartmentEmployees oDepartmentEmployees = 
        new clsDepartmentEmployees();
    bool bResult = false;

    try
    {
        // first delete the rows from the 

        // DepartmentEmployees table

        oDepartmentEmployees.DepartmentID = 
            iDepartmentIDToDelete;
        bResult = 
            oDepartmentEmployees.DeleteAllWDepartmentIDLogic();

        // then delete the row from the Department table

        oDepartments.DepartmentID = iDepartmentIDToDelete;
        bResult = oDepartments.Delete();

        // done!

    }
    catch(Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

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).

/// <summary>

/// Purpose: Deletes a department from the database. 

/// Now using the new ConnectionProvider class 

/// functionality to make the code more reliable.

/// <param name="iDepartmentIDToDelete">

/// The ID of the department which should be deleted.

/// </param>

/// <summary>

private void DeleteDepartment(int iDepartmentIDToDelete)
{
    bool bResult = false;

    // Create the objects   

    clsDepartments oDepartments = new clsDepartments();
    clsConnectionProvider oConnectionProvider = 
        new clsConnectionProvider();
    clsDepartmentEmployees oDepartmentEmployees = 
        new clsDepartmentEmployees();

    // Pass the created ConnectionProvider object 

    // to the data-access objects.

    oDepartments.cpMainConnectionProvider = 
        oConnectionProvider;
    oDepartmentEmployees.cpMainConnectionProvider = 
        oConnectionProvider;

    // initialize the data-access objects with 

    // the key for the department to delete.

    oDepartments.iDepartmentID = iDepartmentIDToDelete;
    oDepartmentEmployees.iDepartmentID = 
        iDepartmentIDToDelete;

    try
    {
        // open the connection provider

        bResult = oConnectionProvider.OpenConnection();

        // Start a transaction and give it a 

        // name so we can reference it later.

        oConnectionProvider.BeginTransaction(
            "transDelDepartment");

        // do the actual deletion.

        try
        {
            // First the Foreign Key rows. 

            bResult = 
                oDepartmentEmployees.DeleteWDepartmentIDLogic();

            // Then the Primary Key row.

            bResult = oDepartments.Delete();

            // Done, commit transaction

            bResult = oConnectionProvider.CommitTransaction();
        }
        catch(Exception ex)
        {
            // Something went wrong using the deletes, 

            // we can safely roll back.

            // Use the name to reference the transaction.

            bResult = 
                oConnectionProvider.RollbackTransaction(
                "transDelDepartment");

            // Bubble error.

            throw ex;
        }
        // Done. clean up is done in finally block

    }
    catch(Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
    finally
    {
        // Close the connection, and don't commit 

        // pending transactions.

        oConnectionProvider.CloseConnection(false);
    }
}

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