LLBLGen Pro - Tutorial: Adding code to the console application, SelfServicing.

Tutorial: Adding code to the console application, SelfServicing

Preface

In the previous tutorial, we set up VS.NET 2005 to have our generated sourcecode in one solution, have the right references and we added a console application project, NorthwindConsole. That last project is our target for this tutorial: we'll add some code to that project so you can see the generated code in action! This tutorial is SelfServicing specific. If you've generated Adapter code instead, please use the Adapter variant of this tutorial.

The main goal of this tutorial is to show you how to get a complete solution setup and where to add your own code to utilize the LLBLGen Pro generated code so you can for example connect to the database, read some entities, manipulate them and save their changes. The LLBLGen Pro manual contains a lot of small different code snippets which illustrate the many different features of LLBLGen Pro's runtime library and generated code. With this tutorial you'll learn a starting point where to test these snippets out.

Using the generated code

Setting up the using / Imports statements

Before we will continue, we'll setup the using / Imports statements at the top of the Program.cs/ Module1.vb file.

If you haven't loaded the solution created in the previous tutorial, do so by starting VS.NET 2005 and loading the solution
If you're using C#: Open the Program.cs file of the NorthwindConsole project. If you're using VB.NET, open Module1.vb. You might want to rename that one, but for now, it's ok.

At the top of the file, you should add the following code: (assuming you used the Northwind.Tutorial root namespace).

C#
VB.NET

// C#
using Northwind.Tutorial.CollectionClasses;
using Northwind.Tutorial.DaoClasses;
using Northwind.Tutorial.EntityClasses;
using Northwind.Tutorial.FactoryClasses;
using Northwind.Tutorial.HelperClasses;
using Northwind.Tutorial.RelationClasses;
using Northwind.Tutorial.TypedViewClasses;
using Northwind.Tutorial.StoredProcedureCallerClasses;
using Northwind.Tutorial;
using SD.LLBLGen.Pro.ORMSupportClasses;

' VB.NET
Imports Northwind.Tutorial.CollectionClasses
Imports Northwind.Tutorial.DaoClasses
Imports Northwind.Tutorial.EntityClasses
Imports Northwind.Tutorial.FactoryClasses
Imports Northwind.Tutorial.HelperClasses
Imports Northwind.Tutorial.RelationClasses
Imports Northwind.Tutorial.TypedViewClasses
Imports Northwind.Tutorial.StoredProcedureCallerClasses
Imports Northwind.Tutorial
Imports SD.LLBLGen.Pro.ORMSupportClasses

We didn't add a using/Imports for the TypedListClasses as we don't have defined a Typed List so the namespace isn't available.

You're now setup for adding some code.

Using Entities

In the first tutorial you've mapped a couple of different elements to database elements: entities, a typed view and a retrieval stored procedure. In this part of the tutorial we'll utilize the entities. First we'll fetch a subset of the entities and display them in the console. After that we'll manipulate them and save the changes.

We'll start with something simple: we'll fetch all customers from the Northwind database from the USA and display them in the console window. We'll add this code to the Main method in the Programs.cs file (VB.NET: Module1.vb file). To do that, we'll define a filter on Customer.Country, fetch the customers matching the filter in a CustomerCollection instance and loop through the entity collection to display their CustomerID and CompanyName property values. The following code makes that happen.

C#
VB.NET

// C#				
// setup filter. 
PredicateExpression filter = new PredicateExpression(CustomerFields.Country == "USA");
CustomerCollection customers = new CustomerCollection();
customers.GetMulti(filter);

Console.WriteLine("Number of entities fetched: {0}", customers.Count);
// Display for each customer fetched the CustomerId and the CompanyName.
foreach(CustomerEntity c in customers)
{
	Console.WriteLine("{0} {1}", c.CustomerId, c.CompanyName);

}

' VB.NET
Dim filter As New PredicateExpression(CustomerFields.Country = "USA")
Dim customers As New CustomerCollection()
customers.GetMulti(filter)

Console.WriteLine("Number of entities fetched: {0}", customers.Count)
' Display for each customer fetched the CustomerId and the CompanyName.
For Each c As CustomerEntity In customers
	Console.WriteLine("{0} {1}", c.CustomerId, c.CompanyName)
Next

Compile the project and run it. It should show the number of entities fetched and for each entity fetched the CustomerID and the CompanyName.
To see what's going on under the hood, we'll now enable Tracing. For production systems, tracing shouldn't be switched on (simply don't specify any trace switches in the .config file of your application). For this tutorial and for debugging, it's a great resource to see what's going on. In the Solution Explorer, open App.config.

Direct below the </appSettings> element, place the following code:

<system.diagnostics>
    <switches>
        <add name="SqlServerDQE" value="0" />
        <add name="ORMGeneral" value="0" />
        <add name="ORMStateManagement" value="0" />
        <add name="ORMPersistenceExecution" value="4" />
    </switches>
</system.diagnostics>

If you're using a different database than SqlServer, please add the correct trace switch setting for that database. See Troubeshooting and debugging for details.

We've setup the trace switch for Persistence Execution to level 4 (verbose). This level will show us the Query executed, together with other actions. If you just want to see the query SQL, you can also set the SqlServerDQE trace switch to 4 (in the above example it's 0) and the ORMPersistenceExecution trace switch to 0. Recompile the solution and run it in Debug mode. To do this, press F5 instead of Cntrl-F5. The logged trace messages will be shown in the Output window of VS.NET. If you want, you can add a trace listener to the .config file to pipeline the output to a different destination. See the MSDN documentation about Trace listeners how to add one of the default .NET trace listeners to the config file.

Now that we've fetched some data from the database, we can manipulate these entities and save the changes back. Right below the code we've added in the first step, you should add the following code. This code creates an EntityView instance on the entity collection we've fetched and filters the collection in-memory so only the customer entities which ContactTitle field is equal to 'Owner'. This set is the set of entities in the EntityView instance. We'll then loop over that set and append to the contact name '(O)'. After that, we'll save the collection back to the database. As we've switched on tracing, we'll be able to see which queries the LLBLGen Pro runtime framework will generate for us.

C#
VB.NET

// C# 
// Create a view from the in-memory customer collection and filter on 
// Customer.ContactTitle == "Owner".
EntityView<CustomerEntity> owners = new EntityView<CustomerEntity>(
		customers, (CustomerFields.ContactTitle == "Owner"));

Console.WriteLine("Number of customers which are owners: {0}", owners.Count);

// loop over the owners and append to the contact name '(O)'
foreach(CustomerEntity c in owners)
{
	c.ContactName += "(O)";
}

// Show the changed customers
foreach(CustomerEntity c in owners)
{
	Console.WriteLine("{0} {1} {2}", c.CustomerId, c.CompanyName, c.ContactName);
}

// save the changes to the database. The adapter will automatically create a transaction
customers.SaveMulti(true);

' VB.NET
' Create a view from the in-memory customer collection and filter on 
' Customer.ContactTitle == "Owner".
Dim owners As New EntityView(Of CustomerEntity)(customers, (CustomerFields.ContactTitle = "Owner"))
Console.WriteLine("Number of customers which are owners: {0}", owners.Count)
' loop over the owners and append to the contact name '(O)'
For Each c As CustomerEntity In owners
	c.ContactName &= "(O)"
Next

' Show the changed customers
For Each c As CustomerEntity In owners
	Console.WriteLine("{0} {1} {2}", c.CustomerId, c.CompanyName, c.ContactName)
Next

' save the changes to the database. The adapter will automatically create a transaction
customers.SaveMulti(True)

Compile and run the project in Debug mode. You'll see that although the complete collection is saved, only two UPDATE queries are generated, namely for the two customers which have 'Owner' as contact title.

Using Typed Views

We'll leave the land of Entities for now and will instead look at how to use the Typed View we've mapped, Invoices. This Typed View is mapped onto the database view 'Invoices' and initially it will return over 2000 rows. We'll create a Windows Forms form and bind the 2nd page of 25 rows of the Typed View Invoices to a grid on the form.

To avoid having to run the code for entities over and over again, comment out the code you've added to the Main method first, or you can simply remove that code as it's not needed for this part of the tutorial.
We'll add a generic Windows Forms form which will have a DataGridView on it. To do so, in Solution Explorer Right-click the NorthwindConsole project -> Add -> Windows form. Specify as name Viewer.cs (or if you're using VB.NET: Viewer.vb)
From the VS.NET Toolbox (if it's not visible, press Cntrl-W-X), drag a DataGridView onto the form. We won't use any datasources, so you can press ESC or click inside the form. Resize the DataGridView so that it covers the form equally with just a small border left around the DataGridView.
Be sure the DataGridView is selected and open the Properties window in VS.NET or press F4. Scroll down to (Name) and specify _theGrid as the name. Scroll down to Anchor and anchor the grid to all 4 borders. You should now be able to resize the dialog and have the datagridview be resized with it.

Right-click the white background of VS.NET and select View Code. Add the following method to the class

C#
VB.NET

// C#
public void BindDataTable(DataTable toBind)
{
	_theGrid.DataSource = toBind;
}

' VB.NET
Public Sub BindDataTable(ByVal toBind As DataTable)
	_theGrid.DataSource = toBind
End Sub

Our viewer is now ready to be used. A Typed View is a derived class of the .NET DataTable class so we can pass it to this viewer and have its contents show up in the viewer's DataGridView. We'll also use this same method in the tutorial section with the retrieval stored procedure call. We'll now return to our Main method in Program.cs / Module1.vb.

We'll fetch the 2nd page of 25 rows of the Invoices Typed View and we'll show that in the viewer. To do that, put the following code in the Main method. You can comment out any code you've left there or remove the code you previously added to the Main method.

C#
VB.NET

// C#
InvoicesTypedView invoices = new InvoicesTypedView();
// simply fetch the 2nd page of 25 rows. We specify no filter, 
// no group by collection and we specify 0 for the max number of elements to return which means
// all of them. We also specify that we don't care about duplicate rows. 
// We do specify a sort expression, as paging without a sorter isn't really reliable. 
SortExpression sorter = new SortExpression();
sorter.Add(InvoicesFields.OrderId | SortOperator.Ascending);
invoices.Fill(0, sorter, true, null, null, null, 2, 25);

// Now that invoices is filled with data, we'll show it in the viewer.
Viewer v = new Viewer();
v.BindDataTable(invoices);
v.ShowDialog();

' VB.NET
Dim invoices As New InvoicesTypedView()
' simply fetch the 2nd page of 25 rows. We specify no filter, 
' no group by collection and we specify 0 for the max number of elements to return which means
' all of them. We also specify that we don't care about duplicate rows. 
' We do specify a sort expression, as paging without a sorter isn't really reliable. 
Dim sorter As New SortExpression()
sorter.Add(InvoicesFields.OrderId Or SortOperator.Ascending)
invoices.Fill(0, sorter, True, Nothing, Nothing, Nothing, 2, 25)

' Now that invoices is filled with data, we'll show it in the viewer.
Dim v As New Viewer()
v.BindDataTable(invoices)
v.ShowDialog()

Compile and run the project.

Using Retrieval Stored Procecure Calls

We also mapped a retrieval stored procedure call, SalesByYear. This stored procedure accepts two dates and will return the list of orders which have their ShippedDate between the two specified dates. In the next few steps we'll call this SalesByYear procedure and will show its results in the Viewer we've created in the Using Typed Views section above. If you haven't followed that section yet, at least create the viewer class as illustrated there.

Clean the Main method in Program.cs / Module1.vb or comment out the code you have there.
Add to the using / Imports section at the top a using / Imports statement for System.Data

Add to the Main method the following code. It will first call the stored procedure with two dates, whcih will result in a filled DataTable. This DataTable is then passed to the Viewer class to display the data on screen.

C#
VB.NET

// C#
// we'll grab the results from January 1st 1997 till July 1st 1997. 
// Call the procedure with these two dates as parameters. The procedure method will
// return a DataTable with the results
DataTable results = RetrievalProcedures.SalesByYear(new DateTime(1997, 1, 1), new DateTime(1997, 7, 1));

// We've just fetched the data from the database, so we can view it in the viewer
Viewer v = new Viewer();
v.BindDataTable(results);
v.ShowDialog();

' VB.NET
' we'll grab the results from January 1st 1997 till July 1st 1997. 
' Call the procedure with these two dates as parameters. The procedure method will
' return a DataTable with the results
Dim results As DataTable = RetrievalProcedures.SalesByYear(New DateTime(1997, 1, 1), New DateTime(1997, 7, 1))
' Now that invoices is filled with data, we'll show it in the viewer.
Dim v As New Viewer()
v.BindDataTable(results)
v.ShowDialog()

Compile and run the project