Adding code to the console application, SelfServicing

In the previous tutorial, we set up Visual Studio 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.

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 Visual Studio 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

  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;
  using SD.LLBLGen.Pro.QuerySpec;
  using SD.LLBLGen.Pro.QuerySpec.SelfServicing;
  

  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
  Imports SD.LLBLGen.Pro.QuerySpec
  using SD.LLBLGen.Pro.QuerySpec.SelfServicing;
  

• 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

  // Define QuerySpec query
  var qf = new QueryFactory();
  var q = qf.Customer
              .Where(CustomerFields.Country.Equal("USA"));
  var customers = new CustomerCollection();
  // fetch the query.
  customers.GetMulti(q);
  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);
  }
  

  ' Define QuerySpec query
  Dim qf = New QueryFactory()
  Dim q = qf.Customer.Where(CustomerFields.Country.Equal("USA"))
  Dim customers As New CustomerCollection()
  ' fetch the query
  customers.GetMulti(q)
  
  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 </connectionStrings> 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 SQL Server, 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 Ctrl-F5. The logged trace messages will be shown in the Output window of Visual Studio. 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

  // Create a view from the in-memory customer collection and filter on 
  // Customer.ContactTitle == "Owner".
  var owners = new EntityView<CustomerEntity>(
          customers, CustomerFields.ContactTitle.Equal("Owner"));
  
  Console.WriteLine("Number of customers which are owners: {0}", owners.Count);
  
  // loop over the owners and append to the contact name '(O)'
  foreach(var c in owners)
  {
      c.ContactName += "(O)";
  }
  
  // Show the changed customers
  foreach(var c in owners)
  {
      Console.WriteLine("{0} {1} {2}", c.CustomerId, c.CompanyName, c.ContactName);
  }
  
  // save the changes to the database. GetMulti will automatically create a transaction
  customers.SaveMulti();
  

  ' 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.Equal("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. GetMulti will automatically create a transaction
  customers.SaveMulti()
  

• 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 Visual Studio Toolbox, 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 Visual Studio 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 Visual Studio and select View Code. Add the following method to the class
  C#VB.NET

  public void BindTypedView(List<InvoiceRow> toBind)
  {
      _theGrid.DataSource = toBind;
  }
  

  Public Sub BindTypedView(ByRef toBind As List(Of InvoiceRow))
      _theGrid.DataSource = toBind
  End Sub
  

• At the top, add the following reference to the proper namespace:
  C#VB.NET

  using Northwind.Tutorial.TypedViewClasses;
  

  Imports Northwind.Tutorial.TypedViewClasses
  

• 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

  var qf = new QueryFactory();
  // simply fetch the 2nd page of 25 rows. We specify no filter, 
  // We do specify a sort expression, as paging without a sorter isn't really reliable. 
  List<InvoiceRow> invoices;
  var q = qf.Invoice.OrderBy(InvoiceFields.OrderId.Ascending()).Page(2, 25);
  invoices = new TypedListDAO().FetchQuery(q);
  // Now that invoices is filled with data, we'll show it in the viewer.
  var v = new Viewer();
  v.BindTypedView(invoices);
  v.ShowDialog();
  

  Dim qf As New QueryFactory()
  ' simply fetch the 2nd page of 25 rows. We specify no filter, 
  ' We do specify a sort expression, as paging without a sorter isn't really reliable. 
  Dim invoices As List(Of InvoiceRow)
  Dim q = qf.Invoice.OrderBy(InvoiceFields.OrderId.Ascending()).Page(2, 25)
  invoices = New TypedListDAO().FetchQuery(q)
  
  ' Now that invoices is filled with data, we'll show it in the viewer.
  Dim v As new Viewer()
  v.BindTypedView(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
• In the Viewer class, add the following method to bind a DataTable to the grid:
  C#VB.NET

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

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

• 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

  // 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
  var 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();
  
  

  ' 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