<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 7.0">
<TITLE>Example: very simple database</TITLE>
<link rel="STYLESHEET" type="text/css" href="general.css">
</HEAD>
<BODY>
<br>
<h3>Example: very simple database</h3>
<h4>Data model</h4>
<p>
For this example, we're using a simple but feature rich enough datamodel
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 datamodel is
shown below:
<p>
<img src="datamodel.gif" border="0">
<p class="SmallFontREF" align="center">
<b>The example datamodel.</b>
</p>
</p>
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.
</p>
<p>
The datamodel contains an Image field, NULL values, unicode fields, text fields, identity fields
and a datetime 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.
</p>
<h4>Generating code.</h4>
<p>
We start LLBLGen and connect to the testdatabase with the three tables.
When you look at the usage documentation, you'll notice that all screenshots are taken using this database.
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. <br>
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.
</p>
<h4>Compiling the generated .NET classes</h4>
<p>
We open a cmd window (dosbox) and type 'vsvars32.bat', which will load up shell variables and will adjust
the path to the .NET SDK tools. At the commandline we 'cd' to the directory where our generated C# classes
are stored and type:
<code>
<pre>csc /out:testdbllbl.dll /target:library /optimize *.cs</pre>
</code>
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 convieniant 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 commandline. When you do include COM+ services you have to specify addition assemblies
in the csc.exe command, but that's a no-brainer.
</p>
<h4>Installing the T-SQL code</h4>
<p>
We open Query Analyser and load the generated T-SQL file. We also could have used isql.exe, but that tool
is a burden to use, and if you can, use Query Analyzer or lookalike tool (there are several available, free
most of the time) to execute T-SQL code. Connect to the testdatabase 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 therefor can't be updated. (Some developers say that this should be possible,
but you're then creating <i>new</i> relations between the tables Departments and Employees, not updating
a current relation, which should be the purpose of the Update command in this context).
</p>
<h4>Using the data-access layer</h4>
<p>
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 testdatabase.
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 <i>using</i> 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 <i>using</i> application, and thus
will result in the usage of the app.config file of <i>that</i> 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;'
<br><br>
<b class="Smallhdr">Storing new records</b><br>
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 <b>illustrate</b> 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 codesnippet 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:
<code>
<pre>
/// <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);
}
}
</pre>
</code>
Let's walk through this code to make you understand it. <br>
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 datetimepicker 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.
<br><br>
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 catched in the catch() block. If no error 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.
<br><br>
Notes for VB.NET users: when reading back the value from EmployeeID is a little different. Instead, do this:
<code><pre>
' ...
Dim iEmployeeID as Integer = oEmployees.EmployeeID.Value
' ...
</pre></code>
<br>
<b class="SmallHdr">Reading lists of records</b>.<br>
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 datagrid control on the form. The code below does all that magic for you, and is called from
the click event handler delegate of the button:
<code><pre>
/// <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);
}
}
</pre></code>
It can't get any simpler than this, folks!
<br><br>
<b class="SmallHdr">Deleting rows</b><br>
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:
<code><pre>
/// <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);
}
}
</pre></code>
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 new ConnectionProvider object functionality of LLBLGen to make the two calls run inside
one transaction which is rolled back when something goes wrong.
<br><br>
<b class="SmallHdr">Using the ConnectionProvider class</b><br>
Below is a codesnippet which does the same as the codesnippet above, but now it uses the
ConnectionProvider object 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).
<code><pre>
/// <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);
}
}
</pre></code>
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. Transactions are not always needed, f.e. when you're simply selecting a row from
the database, you don't have to use transactions.
</p>
<div align="right" class="SmallFontTOC">
<hr size="1" width="60%" align="right">
LLBLGen v1.2 documentation. © 2002 <a href="http://www.sd.nl/" target="_blank">Solutions Design</a>
</div>
<br><br>
</BODY>
</HTML>
Submit an article or tip