Connect SQL Server Database to WPF Application and Perform CRUD Operations

In this post, we'll walk you through the step-by-step process of establishing a connection between your WPF application and a SQL Server database. By the end of this tutorial, you'll have a solid understanding of how to harness the power of SQL databases to manage your application's data efficiently. Let's get started!

Step 1: Setting Up Your Project

Open Visual Studio: Launch Visual Studio and create a new WPF project.

<Window x:Class="WpfTutorialSeries.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:WpfTutorialSeries"

        mc:Ignorable="d"

        Title="Registration" SizeToContent="WidthAndHeight">

       <Grid>

        <Grid.RowDefinitions>

            <RowDefinition Height="Auto" />

            <RowDefinition Height="Auto" />

            <RowDefinition Height="Auto" />

            <RowDefinition Height="Auto" />

            <RowDefinition Height="Auto" />

            <RowDefinition Height="Auto" />

            <RowDefinition Height="Auto" />

            <RowDefinition Height="Auto" />

        </Grid.RowDefinitions>

        <Grid.ColumnDefinitions>

            <ColumnDefinition Width="150" />

            <ColumnDefinition Width="*" />

        </Grid.ColumnDefinitions>

        <TextBlock Grid.Row="0" Grid.Column="0" Text="Emp ID:" Margin="15"/>

        <TextBox Grid.Row="0" Grid.Column="1" x:Name="txtEmpID"  Margin="15"/>

        <TextBlock Grid.Row="1" Grid.Column="0" Text="Name:" Margin="15"/>

        <TextBox Grid.Row="1" Grid.Column="1" x:Name="txtName" Margin="15"/>

        <TextBlock Grid.Row="2" Grid.Column="0" Text="Date of Birth:" Margin="15"/>

        <DatePicker Grid.Row="2" Grid.Column="1" x:Name="dpDOB" Margin="15"/>

        <TextBlock Grid.Row="3" Grid.Column="0" Text="Address:" Margin="15"/>

        <TextBox Grid.Row="3" Grid.Column="1" x:Name="txtAddress" Margin="15" AcceptsReturn="False" Height="80"/>

        <TextBlock Grid.Row="4" Grid.Column="0" Text="Select Country:" Margin="15"/>

        <ComboBox Grid.Row="4" Grid.Column="1" x:Name="cbCountry" Margin="15">

           India

        </ComboBox>

        <TextBlock Grid.Row="5" Grid.Column="0" Text="Gender:" Margin="15"/>

        <StackPanel Grid.Row="5" Grid.Column="1" Orientation="Horizontal" Margin="15">

            <RadioButton x:Name="rbMale" Content="Male" Margin="15"/>

            <RadioButton x:Name="rbFemale" Content="Female" Margin="15"/>

        </StackPanel>

     

        <StackPanel Orientation="Horizontal" Grid.Row="7" Grid.Column="1" Margin="5" HorizontalAlignment="Left">

            <Button  Width="100" Height="30" Margin="5" x:Name="btnUpdate" Content="Update" Click="Update"/>

            <Button  Width="100" Height="30" Margin="5" x:Name="btnDelete" Content="Delete" Click="Delete" />

        </StackPanel>

        <StackPanel Orientation="Horizontal" Grid.Row="6" Grid.Column="1" Margin="5" HorizontalAlignment="Left">

            <Button  Width="100" Height="30" Margin="5" x:Name="btnRegister" Content="Register" Click="Register"  />

            <Button  Width="100" Height="30" Margin="5" x:Name="btnDetails" Content="Get Details" Click="GetDetails" />

        </StackPanel>

    </Grid>

</Window>

Step 2: Create Database

Got to Microsoft SQL Server Management Studio and create a database EmployeeDB.

Step 3: Create Table

Create table Employee and add the required column with respective data type.

Step 4:Connect to Database

Go to visual studio, click on tool menu and select Connect to Database.

Add Connection Dialog will open. Here we have to select the Database.

Click on test connection

To connect to the Database we need the connection string.

Step 5: Get the connection string

Open Server Explorer

Right click on Database and select properties

Copy connection string from properties and pass this as an argument for SqlConnection constructor.

Step 6:Code for the buttons

 public partial class MainWindow : Window

    {

        public MainWindow()

        {

            InitializeComponent();

        }

        private void Register(object sender, RoutedEventArgs e)

        {

//create a SqlConnection by passing the connection string

            SqlConnection connection = new SqlConnection("Data Source=.;Initial Catalog=EmployeeDB;Integrated Security=True");

//Open sql connection           

 connection.Open();

  //Create  SqlCommand object and the the sql query

         SqlCommand command = new SqlCommand("insert into Employee (EmpID,EmpName,DOB,Address,Country,Gender,) values" +

                "(@EmpID,@EmpName,@DOB,@Address,@Country,@Gender)", connection);

            var empId = Convert.ToInt32(txtEmpID.Text);

            var dob = Convert.ToDateTime(dpDOB.Text);

            var gender = (bool)rbMale.IsChecked ? "Male" : "Female";

            command.Parameters.AddWithValue("@EmpID", empId);

            command.Parameters.AddWithValue("@EmpName", txtName.Text);

            command.Parameters.AddWithValue("@DOB", dob);

            command.Parameters.AddWithValue("@Address", txtAddress.Text);

            command.Parameters.AddWithValue("@Country", cbCountry.SelectedItem);

            command.Parameters.AddWithValue("@Gender", gender);

            command.ExecuteNonQuery();

            MessageBox.Show("Successfully Saved", "Success");

        }

        private void GetDetails(object sender, RoutedEventArgs e)

        {

            SqlConnection connection = new SqlConnection("Data Source=.;Initial Catalog=EmployeeDB;Integrated Security=True");

            connection.Open();

            SqlCommand command = new SqlCommand("select * from Employee where EmpID=@EmpID", connection);

            var empId = Convert.ToInt32(txtEmpID.Text);

            command.Parameters.AddWithValue("@EmpID", empId);

            var reader=command.ExecuteReader();

            while(reader.Read())

            {

                txtName.Text = reader["EmpName"].ToString();

                dpDOB.Text = reader["DOB"].ToString();

                txtAddress.Text = reader["Address"].ToString();

                var country = reader["Country"].ToString();

                var gender = reader["Gender"].ToString();

                cbCountry.SelectedItem = country;

                if (gender == "Male")

                    rbMale.IsChecked = true;

                else

                    rbFemale.IsChecked = true;

            }

        }

        private void Update(object sender, RoutedEventArgs e)

        {

            SqlConnection connection = new SqlConnection("Data Source=.;Initial Catalog=EmployeeDB;Integrated Security=True");

            connection.Open();

            SqlCommand command = new SqlCommand("update Employee set EmpID=@EmpID,EmpName=@EmpName," +

                "DOB=@DOB,Address=@Address,Country=@Country,Gender=@Gender where EmpID=@EmpID", connection);

            var empId = Convert.ToInt32(txtEmpID.Text);

            var dob = Convert.ToDateTime(dpDOB.Text);

            var gender = (bool)rbMale.IsChecked ? "Male" : "Female";

            command.Parameters.AddWithValue("@EmpID", empId);

            command.Parameters.AddWithValue("@EmpName", txtName.Text);

            command.Parameters.AddWithValue("@DOB", dob);

            command.Parameters.AddWithValue("@Address", txtAddress.Text);

            command.Parameters.AddWithValue("@Country", cbCountry.SelectedItem);

            command.Parameters.AddWithValue("@Gender", gender);

            command.ExecuteNonQuery();

            MessageBox.Show("Successfully Updated", "Success");

        }

        private void Delete(object sender, RoutedEventArgs e)

        {

            SqlConnection connection = new SqlConnection("Data Source=.;Initial Catalog=EmployeeDB;Integrated Security=True");

            connection.Open();

            SqlCommand command = new SqlCommand("delete from Employee where EmpID=@EmpID", connection);

            var empId = Convert.ToInt32(txtEmpID.Text);

            command.Parameters.AddWithValue("@EmpID", empId);

            command.ExecuteNonQuery();

            MessageBox.Show("Sucessfully deleted", "Sucess");

        }

    }

And there you have it, a fully functional WPF application with CRUD operations with  SQL Server Database.

Watch now

If you have any questions or need further assistance, feel free to reach out. 

Happy coding!🙋