This blog post series will show you how to create a new LLBLGen project, generate the entities, implement everything in VisualStudio, and write some basic queries.

Introduction

This is the first of three parts to get you started with LLBLGen. We will import a sample database, set up our test project, generate the code using the LLBLGen desktop application, and finally include the generated projects in Visual Studio.

In the second part, we’ll learn about queries and classes used throughout the examples, use projects generated in the first part, and write some queries in the console test application.

The third (and final) part is about projections and different ways how we can write them.

To follow the examples you’ll need the LLBLGen Pro desktop application and VisualStudio. You can download the free trial version or buy the full version from the official website (the version used in this post is 5.3.3.). In this example, we will use the Pro Runtime Framework (Entity Framework, NHibernate, and Linq to Sql are also supported).

To write and execute queries, we’ll use the free version of Visual Studio - Visual Studio Community 2017. The latest version is available for download here. Once you have everything installed, you can move on to the next chapter.

Importing sample data

If you already have the sample database you want to use in the LLBGen project, skip this part and go straight to the Creating the LLBLGen Project and Generating Code.

To generate the test project using the LLBLGen we first need a database with some sample entities. The examples are based on the Adventure Works sample database, and you can download the backup database file here.

To keep things simple, we can restore the backup database on the local SQL server that comes with the Visual Studio. If you don’t have it installed, open Visual Studio Installer and click Modify. Select the Individual Components tab and under the Cloud, database, and server section, check the SQL Server Express 2016 LocalDB option. You can also select the SQL Server Data Tools and SQL Server Command Line Utilities to create and edit databases directly from the Visual Studio. Once you select the required tools, click the Modify button to install them.

The instructions on how the restore the .bak files using the Visual Studio or the SQL Server Management Studio can be found here.

Creating the LLBLGen Project and Generating Code

First, let’s create a blank solution where we’ll add generated projects and the test console project. Open the Visual Studio, click File, New and select Project. Select the Blank Solution from the New Project window and give it a name.

Now that we have the solution and the test database, we can start working with the LLBLGen desktop application. Open the application and click Create New Project. In the popup window enter the Name of the project, Creator, and Path. From the Target Framework drop-down menu select the LLBLGen Pro Runtime Framework and from the Initial contents select the Relational model data retrieved from a database (Database First), then click Create. If you select the Empty project as initial contents, you can always right-click the Relational Model Data node and select Add Relational Model Data From Database to get to the Relational Model Data Retrieval Wizard.

In the next window, select SQL Server Driver (Sql Client) from the Database Driver drop-down. Next, write the Server Name where your database is stored (in my case (localdb)\MSSQLLocalDB) and click Next.

Now, select the whole database or individual elements you want to retrieve, and click Next. If you have any stored procedures checked, select the ones you want to sync and click Next - otherwise, you are taken to the last wizard step and can click the Finish button. I chose to retrieve all the tables from the sample database, but if you are using your own database, please note that you have to select at least one table with some fields to continue.

Now that the relational model is in the project, you can see the retrieved tables in the Catalog Explorer on the right side of the Desktop application. To generate the entities, in the Catalog Explorer, right-click the database name and select Reverse-engineer Tables to Entity Definitions… and Reverse Engineering Element Editor will open. Here you can select which tables you want to add to your project as well as change their names. Check the tables you want and click Add to Project. Again, at least one row has to be selected to continue.

Now we have the reverse engineered tables, which you can see in Project Explorer under the Entities node. If you expand that node, you can see all the tables you included in your project, and if you expand the tables, you can see all the fields and their primary and foreign key constraints.

Lastly, we have to generate the Source Code by right-clicking the project name and selecting Generate Source Code (shortcut is F7). In the Generate Code Window, click Edit Selected Task Specifics to configure the specifics of the task; select C# as the Target language, and in the Framework tab select your framework version (for now, select Adapter as your Template Group; we’ll explain template groups in the next chapter). Finally, select your Root Destination Folder. Remember where you are generating your source code because we’ll need to add generated projects to the solution later.

When you’re done, click Ok and then the Perform Tasks button. The application will now generate your source code and create two projects in the destination folder called DatabaseGeneric and DatabaseSpecific. After the task is completed, the log viewer pops up. Here you can see if all the source code has generated successfully or you had some errors.

Although I used the Database First approach in my example, you can also use Model First, but the process is slightly different. For more information on how to use the Model first approach, please read this.

Templates in LLBLGen

Before we move on, we need to briefly mention the LLBLGen templates and the main difference between them.

There are two template groups: SelfServicing and Adapter. To put it simply, the code generated by the SelfServicing template is easier to use, while Adapter gives you more control.

For the SelfServicing template group, no extra object instance is required to save or fetch the data from the database. The official documentation explains that the template was named that way because the entity, typed list, and typed view classes contain all the logic and data to interact with the persistent storage.

Unlike SelfServicing, in the Adapter template all persistence actions are performed by an adapter object. In this case, entities don’t contain any persistence logic or information, but the adapter object, called DataAccessAdapter, is used to persist the entity object.

If you want to create a project using the SelfServicing template, the only difference is the last step - in the Template Group dropdown, select SelfServicing instead of Adapter. In that case, LLBLGen is going to generate only one VS .Net project instead of two which are generated when using the Adapter template.

In our example, we’re using the Adapter template, which is the recommended one, but here are some basic tips from the official documentation on when to use which template group:

When to use SelfServicing:

• When the project targets a single database type.
• When you’re not using a distributed scenario like remoting, web services, WebAPI or WCF services.
• When you need navigation through your object model in a data binding scenario, and you want to load the data using lazy loading.
• When you don’t need to extend the framework.
• When you like to have the persistence logic into the entity classes.
• When you don’t require fine-grained database access control, like targeting another database per call.

When to use Adapter:

• When the project targets multiple database types, or you want to be able to do that in the future.
• When you are using a distributed scenario like remoting, web services, WebAPI or WCF services.
• When you don’t need lazy loading scenarios in relation traversals. You can still navigate relations, but you have to fetch the data up-front.
• When you need to extend the framework with derived classes.
• When you like to see persistence logic as a ‘service,’ which is offered by an external object (session/context, adapter).
• When you require fine-grained database access control.
• When you want to convert Entities to XML and back, and performance and XML size are important.

For more information about the template groups, click here.

Including generated projects to Visual Studio

To test our queries we need a test project, so let’s add a new console project to our solution. Open the solution in VisualStudio, right-click the solution name, go to Add and select New Project. Under Visual C# templates, select Console App and give it a name (I’ve named my test application ConsoleTester).

Now we have to add the previously generated projects. When you go to the folder you selected in the previous step as the Root Destination Folder, you can see that the LLBLGen generated two VS .Net projects called DatabaseGeneric and DatabaseSpecific. The first one is named after your LLBLGen project name (in my case AdventureWorksLLBLGen) and the second one has the DbSpecific suffix added to the project’s name.

If we want to work with our database, we have to include generated projects to the solution where our Console Application project is. To do that, right-click the solution name, go to Add and select Existing Project.., then go to the root destination folder and select generated projects one by one.

Next, to add the references to our Console App project, expand the Console App project, right-click the References and select Add References. Under Projects node, check both projects and click Ok.

Your solution should now look like this:

Now that everything is set up, join me in the next part where we will use generated projects along with the test application to write and execute some queries.

The source code from this first part can be downloaded here.