Walkthrough: Display data from a SQL Server database in a DataGrid control

In this walkthrough, you retrieve data from a SQL Server database and display that data in a DataGrid control. You use the ADO.NET Entity Framework to create the entity classes that represent the data, and use LINQ to write a query that retrieves the specified data from an entity class.

Prerequisites

You need the following components to complete this walkthrough:

• Visual Studio.

• Access to a running instance of SQL Server or SQL Server Express that has the AdventureWorks sample database attached to it. You can download the AdventureWorks database from the GitHub.

Create entity classes

1. Create a new WPF Application project in Visual Basic or C#, and name it DataGridSQLExample.

2. In Solution Explorer, right-click your project, point to Add, and then select New Item.

   The Add New Item dialog box appears.

3. In the Installed Templates pane, select Data and in the list of templates, select ADO.NET Entity Data Model.

   

4. Name the file AdventureWorksModel.edmx and then click Add.

   The Entity Data Model Wizard appears.

5. In the Choose Model Contents screen, select EF Designer from database and then click Next.

6. In the Choose Your Data Connection screen, provide the connection to your AdventureWorksLT2008 database. For more information, see Choose Your Data Connection Dialog Box.

   Make sure that the name is AdventureWorksLT2008Entities and that the Save entity connection settings in App.Config as check box is selected, and then click Next.

7. In the Choose Your Database Objects screen, expand the Tables node, and select the Product and ProductCategory tables.

   You can generate entity classes for all of the tables; however, in this example you only retrieve data from those two tables.

   

8. Click Finish.

   The Product and ProductCategory entities are displayed in the Entity Designer.

   

Retrieve and present the data

1. Open the MainWindow.xaml file.

2. Set the Width property on the Window to 450.

3. In the XAML editor, add the following DataGrid tag between the <Grid> and </Grid> tags to add a DataGrid named dataGrid1.

   <DataGrid Name="dataGrid1" />
   

   

4. Select the Window.

5. Using the Properties window or XAML editor, create an event handler for the Window named Window_Loaded for the Loaded event. For more information, see How to: Create a Simple Event Handler.

   The following shows the XAML for MainWindow.xaml.

   Note

   If you are using Visual Basic, in the first line of MainWindow.xaml, replace x:Class="DataGridSQLExample.MainWindow" with x:Class="MainWindow".

   <Window x:Class="DataGridSQLExample.MainWindow"
           xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
           xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
           Title="MainWindow" Height="350" Width="450" 
           Loaded="Window_Loaded">
       <Grid>
           <DataGrid Name="dataGrid1" />
       </Grid>
   </Window>
   

6. Open the code-behind file (MainWindow.xaml.vb or MainWindow.xaml.cs) for the Window.

7. Add the following code to retrieve only specific values from the joined tables and set the ItemsSource property of the DataGrid to the results of the query.

   using System.Data.Entity.Core.Objects;
   using System.Linq;
   using System.Windows;
   
   namespace DataGridSQLExample
   {
       /// <summary>
       /// Interaction logic for MainWindow.xaml
       /// </summary>
       public partial class MainWindow : Window
       {
           AdventureWorksLT2008Entities dataEntities = new AdventureWorksLT2008Entities();
   
           public MainWindow()
           {
               InitializeComponent();
           }
   
           private void Window_Loaded(object sender, RoutedEventArgs e)
           {
               var query =
               from product in dataEntities.Products
               where product.Color == "Red"
               orderby product.ListPrice
               select new { product.Name, product.Color, CategoryName = product.ProductCategory.Name, product.ListPrice };
   
               dataGrid1.ItemsSource = query.ToList();
           }
       }
   }
   

   Imports System.Data.Objects
   
   Class MainWindow
       Dim dataEntities As AdventureWorksLT2008Entities = New AdventureWorksLT2008Entities
   
       Private Sub Window_Loaded(ByVal sender As System.Object, ByVal e As System.Windows.RoutedEventArgs) Handles MyBase.Loaded
           Dim products As ObjectQuery(Of Product) = dataEntities.Products
   
           Dim query = _
               From product In products _
               Where product.Color = "Red" _
               Order By product.ListPrice _
               Select product.Name, product.Color, CategoryName = product.ProductCategory.Name, product.ListPrice
   
           dataGrid1.ItemsSource = query.ToList()
       End Sub
   End Class
   

8. Run the example.

   You should see a DataGrid that displays data.

   

See also

• DataGrid