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.

create database WPFReadCustomers
go
use WPFReadCustomers
Go
CREATE TABLE [dbo].[Persons](
	[PersonID] [int] NOT NULL,
	[LastName] [varchar](255) NULL,
	[FirstName] [varchar](255) NULL,
	[Address] [varchar](255) NULL,
	[City] [varchar](255) NULL,
 CONSTRAINT [PK_Persons] PRIMARY KEY CLUSTERED 
 ([PersonID] ASC)
 )
 go
 INSERT INTO [Persons] (PersonId, LastName, FirstName, Address, City)
 VALUES (1, 'Roberts', 'Bob', '123 Any Street', 'Sometown')
 INSERT INTO [Persons] (PersonId, LastName, FirstName, Address, City)
 VALUES (2, 'Johnson', 'John', '998 Pine Street', 'Anytown')
 INSERT INTO [Persons] (PersonId, LastName, FirstName, Address, City)
 VALUES (3, 'Jackson', 'Jack', '12 Maple Street', 'Someville')
 go

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

<Window x:Class="SQLServer1.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
        xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
        xmlns:local="clr-namespace:SQLServer1"
        mc:Ignorable="d"
        WindowStartupLocation="CenterScreen"
        Title="SQLServer1" Height="350" Width="500">
    <StackPanel>
        <Button Name="btnRead" Height="50" Width="120" Click="BtnRead_Click">Read</Button>
        <DataGrid x:Name="sqlDataGrid" HorizontalAlignment="Left" AutoGenerateColumns="True"/>
    </StackPanel>
</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.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Windows;
namespace SQLServer1
{
    public partial class MainWindow : Window
    {
        public MainWindow()
        {
            InitializeComponent();
        }
        private void BtnRead_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                SqlConnection conn = new SqlConnection("Server=(localdb)\\MSSQLLocalDB;Database=WPFReadCustomers;Integrated Security=true;");
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                string Get_Data = "SELECT * FROM dbo.Persons";
                SqlCommand cmd = new SqlCommand(Get_Data, conn);
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable("Personss");
                sda.Fill(dt);
                sqlDataGrid.ItemsSource = dt.DefaultView;
            }
            catch (Exception ex)
            {
                MessageBox.Show("Database Error: " + ex.Message);
            }
        }
    }
}

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.

@"Server=(localdb)\MSSQLLocalDB;Database=WPFReadCustomers;Integrated Security=true;"