Adding code to the console application, Adapter

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 Adapter specific. If you've generated SelfServicing code instead, please use the SelfServicing 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.DatabaseSpecific;
  using Northwind.Tutorial.EntityClasses;
  using Northwind.Tutorial.FactoryClasses;
  using Northwind.Tutorial.HelperClasses;
  using Northwind.Tutorial.TypedViewClasses;
  using Northwind.Tutorial;
  using SD.LLBLGen.Pro.QuerySpec;
  using SD.LLBLGen.Pro.QuerySpec.Adapter;
  

  Imports Northwind.Tutorial.DatabaseSpecific
  Imports Northwind.Tutorial.EntityClasses
  Imports Northwind.Tutorial.FactoryClasses
  Imports Northwind.Tutorial.HelperClasses
  Imports Northwind.Tutorial.RelationClasses
  Imports Northwind.Tutorial.TypedViewClasses
  Imports Northwind.Tutorial
  Imports SD.LLBLGen.Pro.QuerySpec
  Imports SD.LLBLGen.Pro.QuerySpec.Adapter
  

• 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 an EntityCollection 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"));
  EntityCollection<CustomerEntity> customers;
  
  // fetch them using a DataAccessAdapter instance
  using(var adapter = new DataAccessAdapter())
  {
      customers = adapter.FetchQuery(q, new EntityCollection<CustomerEntity>());
  }
  
  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 EntityCollection(Of CustomerEntity)
  
  ' fetch them using a DataAccessAdapter instance
  Using Adapter = New DataAccessAdapter()
      customers = adapter.FetchQuery(q, New EntityCollection(Of CustomerEntity)())
  EndUsing
  
  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 however, it's a great resource to see what's going on. In the Solution Explorer, open the console application's 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 EntityView2 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 EntityView2 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 EntityView2<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. The adapter will automatically create a transaction
  using(DataAccessAdapter adapter = new DataAccessAdapter())
  {
      adapter.SaveEntityCollection(customers);
  }
  

  ' Create a view from the in-memory customer collection and filter on 
  ' Customer.ContactTitle == "Owner".
  Dim owners As New EntityView2(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. The adapter will automatically create a transaction
  Using adapter As New DataAccessAdapter()
      adapter.SaveEntityCollection(customers)
  End Using
  

• 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 background of Visual Studio and select View Code. Add the following method to the Viewer 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. We'll now return to our Main method in Program.cs / Module1.vb.

• We'll fetch the second 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;
  using(var adapter = new DataAccessAdapter())
  {
      var q = qf.Invoice.OrderBy(InvoiceFields.OrderId.Ascending()).Page(2, 25);
      invoices = adapter.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)
  Using adapter As new DataAccessAdapter()
      Dim q = qf.Invoice.OrderBy(InvoiceFields.OrderId.Ascending()).Page(2, 25)
      invoices = adapter.FetchQuery(q)
  End Using
  
  ' 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