Windows applications using Linq to SQL and data binding

By: John Charles (Juan Carlos) Olamendy Turruellas

Introduction

Linq to SQL is an ORM technology shipped within Microsoft.NET 3.5 enabling to map the object model of your solution with the relational data schema in the database system. You can also use this technology to query the database and execute transactional operations such as update/insert/delete.

In this article, I will illustrate how to use the new features of ASP.NET 3.5 to develop Windows application using data binding techniques along with Linq to SQL.

Getting started with the solution

This is an example solution for showing a list of products. The users will be able to insert, edit (in a detail view of the products) and delete products from the list.

This Windows application will be implemented using the three-layer approach. The business rules, business process logic and domain model will be in the business logic layer. The UI controls, Windows forms and binding mechanism is part of the presentation layer. And finally, the data access logic and the underlying mapping to the application domain model is part of the data access layer. For this application, the ORM technology is Linq to SQL.

Now let’s get started with the solution by opening the Visual Studio.NET 2008 IDE and creating an Windows Forms Application for the presentation layer and a Class Library project for the business logic and data access layers. The main data source for this solution is the Production.Product and Production.ProductSubcategory tables in the AdventureWorks database shipped with Microsoft SQL Server 2005/2008.

First step is to define the data model in the Class Library project by adding a “Linq to SQL Classes” artifact (see Figure 1).

Figure 1

Then connect to the back-end database, and define the object model and the underlying mappings to the underlying relational data source (see Figure 2).

Figure 2

Then compile this project and add a reference to it from the Windows application and open to the main windows form of the application to display information about the products.

Next step is to glue the presentation layer’s components with the business logic’s components by using data binding techniques. To use the Linq to SQL         components in the data binding techniques, we must first add them as a data source. Open the Data Sources window, and click on the “Add New Data Source” link.

In the “Choose a Data Source Type” page, select the Object option and click on Next button (see Figure 3).

Figure 3

In the “Select the Object You Wish to Bind to” page, navigate through the reference to the Class Library project and select the Product entity type (see Figure 4) and click on the Finish button.

Figure 4

Repeat the same steps to add the ProductSubcategory entity type as a data source.

Next step is to drag and drop the Product (in Detail view) entity from the Data Sources window onto the Windows form. After that, the underlying controls are created and bound to the fields of the Product business entity, a BindingNavigator control, with features for moving between the entities, adding new entities, deleting and updating the existing entities and saving the changes back to the database, is added to the top of the Windows form and the non-visual control BindingSource instance (in the component tray) to transfer data between the data source and the data bound controls on the form.

Now let’s add the code to load the data and display in the associated controls on the form. The data loading process will be implemented in the Load event of the form and the data updates process will be implemented in the event handler of the save icon on the binding navigator on the form. Right-click on the Save button and select Enable option from the context menu (see Figure 5).

Figure 5

Then double-click on the Save button to generate the event handler.

The final code is shown in the Listing 1.

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

using LinqToSQLDataBindingDAL;

namespace WinAppDatabingTest

{

public partial class Form1 : Form

{

private ProductionDataContext m_dcProduction = new ProductionDataContext();

public Form1()

{

InitializeComponent();

}

private void Form1_Load(object sender, EventArgs e)

{

var arrProducts = from objProduct in this.m_dcProduction.Products

orderby objProduct.Name

select objProduct;

this.m_bsProductBindingSource.DataSource = arrProducts;

}

private void productBindingNavigatorSaveItem_Click(object sender, EventArgs e)

{

this.Validate();

this.m_bsProductBindingSource.EndEdit();

this.m_dcProduction.SubmitChanges();

}

}

}

Listing 1

Now let’s do a little cosmetic change to the application. Bind the ProductSubcategoryID to a Combobox control, go to the Databinding | Advanced properties and open the “Formatting and Advanced Binding” window. Then bind the “SelectedValue” property to the m_bsProductBindingSource.ProductSubcategoryID property (see Figure 6).

Figure 6

Then drag and drop the ProductSubcategory data source onto the Combobox to create another binding source and the underlying binding. That is, in the Combobox control the DataSource property to the binding source, the DisplayMember property to Name and the ValueMember to ProductSubcategoryID (see Figure 7).

Figure 7

Now let’s add the code for the data load process of the ProductSubcategory entities (see Listing 2).

private void Form1_Load(object sender, EventArgs e)

{

var arrProducts = from objProduct in this.m_dcProduction.Products

orderby objProduct.Name

select objProduct;

this.m_bsProductBindingSource.DataSource = arrProducts;

var arrProductSubcategories = from objProdSubc in this.m_dcProduction.ProductSubcategories

orderby objProdSubc.Name

select objProdSubc;

this.m_bsProductSubcategoryBindingSource.DataSource = arrProductSubcategories;

}

Listing 2

Conclusion

In this article, I’ve illustrated how to use the new features of ASP.NET 3.5 to develop Windows application using data binding techniques along with Linq to SQL.

Advertisements

One thought on “Windows applications using Linq to SQL and data binding

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s