Introduction

The ADO.NET Entity Framework was introduced as part of Microsoft Visual Studio 2008 SP1 and is a new piece of the .NET framework that comes with the .NET 3.5 SP1 update. The Entity Framework is a set of technologies that extends the already robust functionality found in ADO.NET. For those not familiar with its purpose, the ADO.NET Entity Framework creates a layer of abstraction between the application and the data. The power in creating the layer of abstraction is that a developer can develop against the model, using and consuming objects as any other object in code, while behind the scenes the ADO.NET Entity Framework handles the interactions with the data source. For applications using a relational database, this means that the developer does not need to worry about column names, tables and relationships.

SQL Server Compact Edition (CE) is a small, light weight and free embedded database engine that is purposed well for desktop and Windows mobile device applications. SQL Server CE is the smallest offering of the SQL Server family with full blown SQL Server at one end, SQL Server Express in the middle and SQL Server CE on the small end. While many differences exist, the major differences between SQL Server CE and other versions SQL Server are that CE does not have an installed database engine service (footprint is less than 3MB of DLLs), stored procedures are not supported and simultaneous use by more than one user is limited. Additionally, SQL Server CE database files have the extension of ".sdf" instead of the ".mdf" extension found on SQL Server and SQL Server Express database files.

As mentioned previously, the ADO.NET Entity Framework was made available as part of Microsoft Visual Studio 2008 SP1 update. Updating VS 2008 to SP1 will enable the use of the ADO.NET Entity Framework as well as numerous other valuable enhancements. SQL Server CE can be downloaded (if not already installed as part of your Visual Studio SP1 installation) from the Microsoft Download Center.

The following walkthrough example will be an application used to manage suppliers and the products that they supply. The database used in the walkthrough will be the Northwind SQL Server CE database. The database file "Northwind.sdf" can typically be found in the following directory depending on how you installed SQL Server CE 3.5. "C:\Program Files (x86)\Microsoft SQL Server Compact Edition\v3.5\Samples"

Walkthrough Example Setup

Open up Visual Studio 2008 SP1 and create a new Windows Forms project. On the form, add the following controls:

• DataGridView - dgvSuppliers
• DataGridView - dgvProducts
• Button - cmdSave
• Button - cmdClose

When finished your forms should look similar to the one captured in the screen shot below.

Figure 1

Next, create and wire-up the events for the Form load and the Close click. Inside the Close click event add the line 'this.Close();'. Before we can wire up the Form load event to populate the Supplier's data grid, we must create and configure the ADO.NET Entity Data Model for the Northwind database.

Creating the ADO.NET Entity Data Model

In the Solution Explorer window, right-click on the Project and select Add -> New Item... From the list of templates, select the ADO.NET Entity Data Model and name it 'Northwind.edmx'.

[newitem2.jpg]
Figure 2

When you click Add, Visual Studio will launch the Entity Data Model Wizard. This wizard is helpful in walking you through the necessary steps to either generate a data model from an existing database or to create one manually. It is highly recommended that if you have a defined database before beginning development that you use the 'Generate from database' option.

The 'Empty Model' option allows developers to define an entity data model without connecting it to a data source at the time of creation. While this approach takes a considerable amount of time to setup compared to generating the model from an existing database it allows development to continue before having a defined and built data source. Once the data source is built, or available, the entity data model can be mapped to the data source without consequence to the developer code. In addition, the data source could be switched from SQL Server to Oracle without affecting the application's business logic and causing a great deal of programming.

For the purpose of this walkthrough, since there is an existing database, select the 'Generate from database' option and click Next.

[model3.jpg]
Figure 3

In the next step, you configure your connection to the Northwind database. Click the New Connection button in the top right of the screen. Depending on your settings in Visual Studio, you may have a default data provider already selected. If this is the case, and it is not Microsoft SQL Server Compact 3.5, then you will need to click the Change button to change the provider to the .NET Framework Data Provider for Microsoft SQL Server Compact 3.5. The Choose Data Source dialog will pop-up automatically if you don't have a pre-selected data provider. In either case, you will want to select the Microsoft SQL Server Compact 3.5 data provider before continuing to configure the connection properties.

[source4.jpg]
Figure 4

[connection5.jpg]
Figure 5

For this walkthrough example the only connection property that needs to be set for the Northwind database is the location of the Northwind.sdf file.

NOTE: If you are running Windows 7 and accessing the Northwind.sdf file still in the Sample folder under the Program Files location you may need to run Visual Studio as an Administrator to avoid getting access permission errors when connecting. The other option would be to move the Northwind.sdf file to another location.

When finished entering all the properties, click the Test Connection button to ensure that the properties entered create a valid connection to the database. Once the connection test is successful click OK and then Next on the wizard screen.

[entity6.jpg]
Figure 6

The final step in the Entity Data Model Wizard is to select which database objects (tables, views, stored procedures) are going to be mapped in the data model. The Northwind database has 8 tables, but for this example you will only need to select the Products and Suppliers tables. After selecting the two tables, click Finish. This step in the wizard also allows you to enter a namespace for the data model, if you so choose. After clicking Finish the wizard will create the data model automatically, adding all tables that were selected as well as mapping the relationships between the tables. Finally, the wizard adds a reference to System.Data.Entity in the project. When the wizard is finished processing, Microsoft Visual Studio displays the data model as shown in the example below for the Northwind database.

[sql7.jpg]
Figure 7

In the code view of the form, create a new form level variable for the Northwind entity data model, called 'NorthwindContext'. The form level variable is used rather than a local variable due to the need to update the data later in the example. In the form's load event, initialize the form variable by using the new command. From here there are numerous ways the data can be accessed and bound to the data grid views created earlier. The quickest approach may just be to use LINQ and a var variable to query and bind the data. While this works, some nice Visual Studio IntelliSense features are lost without performing some casting that can be confusing depending on your knowledge of LINQ and Extension Methods.

This example will use an ObjectQuery to return an Owner type that can then be bound to the owner's data grid view. Before doing so the following using statements have to be added to the form.

using System.Data.Objects;
using System.Data.Objects.DataClasses;

Once the using statements are inserted, add the following code the form's load event. Note that in the query for Suppliers, the Product objects are also being included. This is a very nice feature of the Entity Data Model that allows objects to be nested when a relationship exists in the database. By including the Product objects now, you save yourself from having to write more code later to query the database again to get the Pet data.

private void MainForm_Load(object sender, EventArgs e)
{
      NorthwindContext = new NorthwindEntities();

      ObjectQuery<SUPPLIERS> suppliers = 
            NorthwindContext.Suppliers.Include("Products");

      dgvSuppliers.DataSource = suppliers;
      dgvSuppliers.Columns["Supplier_ID"].Visible = false;
      dgvSuppliers.Columns["Contact_Title"].Visible = false;
      dgvSuppliers.Columns["Address"].Visible = false;
      dgvSuppliers.Columns["Region"].Visible = false;
      dgvSuppliers.Columns["Postal_Code"].Visible = false;
      dgvSuppliers.Columns["Phone"].Visible = false;
      dgvSuppliers.Columns["Fax"].Visible = false;
      dgvSuppliers.Columns["Products"].Visible = false;

      dgvSuppliers.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
      dgvSuppliers.AutoResizeColumns();
}

Run the application and you will see the supplier's data grid view populated with the Supplier table's data. This is a good example of the Entity Framework's power: With only ~2 lines of code (the additional lines are primarily for formatting), this was possible. Now you will want to populate the product's data grid view based on the selected supplier. To do so first create the event handler for the SelectionChanged event on the supplier's data grid view. Inside this event you will retrieve the selected supplier object from the control and use it to bind the linked products to the product's data grid view. Add the following code to the SelectionChanged event.

  private void dgvSuppliers_SelectionChanged(object sender, EventArgs e)
{
      if (dgvSuppliers.SelectedRows.Count <= 0)
          return;

      Suppliers selectedSupplier = 
          (Suppliers)dgvSuppliers.SelectedRows[0].DataBoundItem;

      dgvProducts.DataSource = selectedSupplier.Products;
      dgvProducts.Columns["Product_ID"].Visible = false;
      dgvProducts.Columns["Category_ID"].Visible = false;
      dgvProducts.Columns["English_Name"].Visible = false;
      dgvProducts.Columns["Quantity_Per_Unit"].Visible = false;
      dgvProducts.Columns["Units_In_Stock"].Visible = false;
      dgvProducts.Columns["Suppliers"].Visible = false;

      dgvProducts.AutoResizeColumns();
}

Run the application and click through the suppliers to retrieve and display the list of products that each supplier supplies. The next step will be to code the form to save changes made in the grids.

Updating a SQL Server CE database via the ADO.NET Entity Data Model

Reading data from a database is typically fairly simple and straightforward. Complications traditionally introduce themselves when trying to write data back to a database in the form of insert and update statements. Constraints, data types, default values, auto-incrementing values, and relationships all come to mind as some of the little nuances that make updating data more complicated than reading data. The good news is that this is not the case with the Entity Framework. The Entity Framework removes the developer from having to deal with those little nuances.

In fact it could be argued that updating is less complicated with the Entity Framework than reading. This is because while it took ~2 lines of code to read in the data, it will only take 1 line of code to update the data.

private void cmdSave_Click(object sender, EventArgs e)
{
     NorthwindContext.SaveChanges();
}

With that line of code all changes to the data, supplier and products, as well as any new supplier and product records will be updated back to the Northwind database.

A Few Helpful Tips

If you run a LINQ query against an entity data model that contains a string or binary parameter that connects to a SQL Server CE database you will get the following error: "The ntext and image data types cannot be used in WHERE, HAVING, GROUP BY, ON, or IN clauses". Since using a string parameter in a WHERE clauses is not that uncommon, there is a good chance you could get snagged by this issue. Microsoft has released a Hotfix for the issue located here: Hotfix Download.

Microsoft SQL Server Management Studio 2005 can create, open and modify SQL Server CE 3.1 databases, but not version 3.5. To create, open or modify a SQL Server CE 3.5 database you will need Management Studio 2008 (available free for download at Microsoft's website) or you can use the Server Explorer in Visual Studio 2008 SP1.

Keep in mind performance requirements when using the ADO.NET Entity Framework and a SQL Server CE database. As with most layers of abstraction there is an impact to performance. The entity data model is no exception that it causes a hit to performance over direct connection using ADO.NET. Coupling that with a lightweight database like SQL Server CE can cause significant performance issues if your application is data intensive and requires complex queries.

Conclusion

You have just created a Windows Forms application that uses the ADO.NET Entity Framework to consume and update data from a Microsoft SQL Server CE database. From the example, you can see that the ADO.NET Entity Data Model is a powerful addition to Visual Studio that allows quick and efficient access to SQL Server CE databases.

Related Article

• Using the ADO.NET Entity Framework with the Advantage Database Server
• Using Connection Pooling in ADO.NET
• SQL Server 2005: Creating Scalar User-Defined Functions from Assemblies