How to Boost Database Development Productivity on Linux, Docker, and Kubernetes with Microsoft SQL Server 2017
For this walkthrough example the only connection property that needs to be set for the Northwind database is the location of the
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.
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.
Consuming SQL Server CE data via the ADO.NET Entity Data Model
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.
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 =
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;
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)
Suppliers selectedSupplier =
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;
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.
Create the event handler for the Save button's click event. Inside the event add the following code.
private void cmdSave_Click(object sender, EventArgs e)
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.
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.