WPF Read a SQL Server Table into a DataGrid

 in WPF  tagged open / data / table read / database / connection / server / itemssource / sql / T-SQL / table / read / grid / datagrid by

This post is about how to connect to a local SQL Server database and read a table from that database into a DataGrid that’s in a Windows Presentation Foundation (WPF) project.

The screenshot below shows the result of the program after the user clicks the Read button.

On a non-production SQL Server database, you could run the following SQL script. It will create a database for this example.

You could set this up on your computer if you install SQL Server and SQL Server Management Studio.

view source
print?
1create database WPFReadCustomers
2go
3use WPFReadCustomers
4Go
5CREATE TABLE [dbo].[Persons](
6    [PersonID] [int] NOT NULL,
7    [LastName] [varchar](255) NULL,
8    [FirstName] [varchar](255) NULL,
9    [Address] [varchar](255) NULL,
10    [City] [varchar](255) NULL,
11 CONSTRAINT [PK_Persons] PRIMARY KEY CLUSTERED
12 ([PersonID] ASC)
13 )
14 go
15 INSERT INTO [Persons] (PersonId, LastName, FirstName, Address, City)
16 VALUES (1, 'Roberts', 'Bob', '123 Any Street', 'Sometown')
17 INSERT INTO [Persons] (PersonId, LastName, FirstName, Address, City)
18 VALUES (2, 'Johnson', 'John', '998 Pine Street', 'Anytown')
19 INSERT INTO [Persons] (PersonId, LastName, FirstName, Address, City)
20 VALUES (3, 'Jackson', 'Jack', '12 Maple Street', 'Someville')
21 go

Below is the XAML for our project. The project’s name is SQLServer1.

view source
print?
1<Window x:Class="SQLServer1.MainWindow"
2        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
3        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
4        xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
5        xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
6        xmlns:local="clr-namespace:SQLServer1"
7        mc:Ignorable="d"
8        WindowStartupLocation="CenterScreen"
9        Title="SQLServer1" Height="350" Width="500">
10    <StackPanel>
11        <Button Name="btnRead" Height="50" Width="120" Click="BtnRead_Click">Read</Button>
12        <DataGrid x:Name="sqlDataGrid" HorizontalAlignment="Left" AutoGenerateColumns="True"/>
13    </StackPanel>
14</Window>

Below is the real “meat” of this post. I’ve tested the code below on my computer and it works. Getting the right connection string can be a bit tricky. The Server is a local server on the same computer as my WPF project. If your SQL Server is on a different computer then the conneection string below will not work. Integrated Security is set to true because the security for the database is integrated with Windows security. In other words, I just log in to Windows and I have access to the SQL Server database. Alternatively you would set a user name and password for the database access. If that is the case then Integrated Security would be false and you would need to specify a user name and password in the connection string.

view source
print?
1using System;
2using System.Data;
3using System.Data.SqlClient;
4using System.Windows;
5namespace SQLServer1
6{
7    public partial class MainWindow : Window
8    {
9        public MainWindow()
10        {
11            InitializeComponent();
12        }
13        private void BtnRead_Click(object sender, RoutedEventArgs e)
14        {
15            try
16            {
17                SqlConnection conn = new SqlConnection("Server=(localdb)\\MSSQLLocalDB;Database=WPFReadCustomers;Integrated Security=true;");
18                if (conn.State != ConnectionState.Open)
19                {
20                    conn.Open();
21                }
22                string Get_Data = "SELECT * FROM dbo.Persons";
23                SqlCommand cmd = new SqlCommand(Get_Data, conn);
24                SqlDataAdapter sda = new SqlDataAdapter(cmd);
25                DataTable dt = new DataTable("Personss");
26                sda.Fill(dt);
27                sqlDataGrid.ItemsSource = dt.DefaultView;
28            }
29            catch (Exception ex)
30            {
31                MessageBox.Show("Database Error: " + ex.Message);
32            }
33        }
34    }
35}

For the above code, we need to know the following information.

  • Server Name
  • Database Name
  • Table Name
  • Column Names (optional is you use * for all columns)
  • DataGrid Name

As a side note, you could use a verbatim string in the connection. Use the @ symbol and remove one of the foreward slashes. This verbatim string works also.

view source
print?
1@"Server=(localdb)\MSSQLLocalDB;Database=WPFReadCustomers;Integrated Security=true;"