WPF: Data Storage using SQL Server

Any development domain i.e. mobile or web or desktop or service base etc. Data storage is essential component whether it is done on server side or client side. WPF application is a client side application and it also supports many data storage resources which depends on business requirement or business choice.
Today, I shall be demonstrating the implementation of data storage using SQL Server with WPF application.

Prerequisites:

Following are some prerequisites before you proceed further in this tutorial:

1. Knowledge about Windows Presentation Form (WPF).
2. Knowledge about T-SQL Programming
3. Knowledge about C# programming.
4. Knowledge about C# LINQ.

You can download the complete source code for this tutorial or you can follow the step by step discussion below. The sample code is developed in Microsoft Visual Studio 2015 Enterprise.

Download Now!

Let's begin now.

1) Create a simple database with table in your SQL Server for storing the data from WPF application, I am using SQL Server 2014 as shown below i.e.

USE [WpfWalkthrough]
GO
/****** Object:  Table [dbo].[Register]    Script Date: 3/13/2018 5:36:30 PM ******/
DROP TABLE [dbo].[Register]
GO
/****** Object:  Table [dbo].[Register]    Script Date: 3/13/2018 5:36:30 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Register](
 [id] [int] IDENTITY(1,1) NOT NULL,
 [fullname] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_Register] PRIMARY KEY CLUSTERED 
(
 [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

In the above script I have simply created my main database for this tutorial and I have created a simple table for storing information.

2) You can see that your table is empty as shown below i.e.

3) Now, create a new WPF application project and name it "Data Storage using SQL server".
4) Now, create "Helper_Code\Common\DAL.cs" file and replace following code in it i.e.

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Data_Storage_using_SQL_server.Helper_Code.Common
{
    public class DAL
    {
        public static int executeQuery(string query)
        {
            // Initialization.
            int rowCount = 0;
            string strConn = "Data Source=SQL Server Name(e.g. localhost);Database=SQL Database Name;User Id=SQL User Name;Password=SQL Password;";
            SqlConnection sqlConnection = new SqlConnection(strConn);
            SqlCommand sqlCommand = new SqlCommand();

            try
            {
                // Settings.
                sqlCommand.CommandText = query;
                sqlCommand.CommandType = CommandType.Text;
                sqlCommand.Connection = sqlConnection;
                sqlCommand.CommandTimeout = 12 * 3600; //// Setting timeeout for longer queries to 12 hours.

                // Open.
                sqlConnection.Open();

                // Result.
                rowCount = sqlCommand.ExecuteNonQuery();

                // Close.
                sqlConnection.Close();
            }
            catch (Exception ex)
            {
                // Close.
                sqlConnection.Close();

                throw ex;
            }

            return rowCount;
        }
    }
}

The above piece of code will allow us to communicate with SQL server in order to perform related queries. For this method to work, you need to replace the SQL server database connection string with your own credentials and settings.

5) Create "Model\BusinessLogic\HomeBusinessLogic.cs" file and replace following code in it i.e.

using Data_Storage_using_SQL_server.Helper_Code.Common;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace Data_Storage_using_SQL_server.Model.BusinessLogic.Helper_Code.Common
{
    public class HomeBusinessLogic
    {
        public static void SaveInfo(string fullname)
        {
            try
            {
                // Query.
                string query = "INSERT INTO [Register] ([fullname])" +
                                " Values ('" + fullname + "')";

                // Execute.
                DAL.executeQuery(query);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}

In the above code, I have created a simple wrapper method that will communicate with SQL server using my previously created "executeQuery(...)" method from DAL(Data Access Layer) class. This "SaveInfo(...)" method will perform SQL server database insertion query and store the target data from the user into SQL server table as targeted.

6) Now, create a new page "Views\HomePage.xaml" file and replace the following code in it i.e.

<Page x:Class="Data_Storage_using_SQL_server.Views.HomePage"
      xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
      xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
      xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" 
      xmlns:d="http://schemas.microsoft.com/expression/blend/2008" 
      xmlns:local="clr-namespace:Data_Storage_using_SQL_server.Views"
      mc:Ignorable="d" 
      d:DesignHeight="480" d:DesignWidth="640"
      Title="HomePage">

    <Grid>
        <DockPanel>
            <Grid>
                <Grid.RowDefinitions>
                    <RowDefinition Height="0.05*" />
                    <RowDefinition Height="*" />
                    <RowDefinition Height="0.05*" />
                </Grid.RowDefinitions>

                <Border Grid.Row="1"
                        Width=" 400"
                        Height="300" 
                        BorderThickness="1" 
                        BorderBrush="Black" 
                        CornerRadius="20" 
                        Opacity="1">
                    <Border.Background>
                        <ImageBrush ImageSource="/Data Storage using SQL server;component/Content/img/bg_2.png">
                            <ImageBrush.RelativeTransform>
                                <TransformGroup>
                                    <ScaleTransform CenterY="0.5" CenterX="0.5" ScaleX="1.5" ScaleY="1.5"/>
                                    <SkewTransform CenterY="0.5" CenterX="0.5"/>
                                    <RotateTransform CenterY="0.5" CenterX="0.5"/>
                                    <TranslateTransform/>
                                </TransformGroup>
                            </ImageBrush.RelativeTransform>
                        </ImageBrush>
                    </Border.Background>

                    <StackPanel Orientation="Vertical" 
                                HorizontalAlignment="Center"
                                VerticalAlignment="Center"
                                Width=" 400"
                                Height="300" >

                        <TextBlock Text="Enter Your Full Name"
                                   VerticalAlignment="Center"
                                   HorizontalAlignment="Center" 
                                   Margin="0,50,0,0" 
                                   FontWeight="Bold" 
                                   FontSize="18" 
                                   Foreground="Black" />

                        <Border Width="220"
                                Height="50"
                                Margin="0,10,0,0">

                            <Border.Background>
                                <ImageBrush ImageSource="/Data Storage using SQL server;component/Content/img/text-box_bg.png"/>
                            </Border.Background>

                            <TextBox x:Name="txtName" 
                                    BorderThickness="0"
                                    FontSize="18"
                                    Width="220"
                                    Height="50" 
                                    Background="{x:Null}" 
                                    Padding="10,12,0,0" 
                                    Foreground="Black"
                                    HorizontalAlignment="Center"/>
                        </Border>

                        <Button x:Name="btnReg"
                                Content="Register"
                                Width="220" 
                                Height="50"
                                Margin="0,10,0,0"
                                FontSize="18" 
                                FontWeight="Bold" 
                                Click="BtnReg_Click" />


                    </StackPanel>
                </Border>
            </Grid>

        </DockPanel>
    </Grid>
</Page>

In the above code, I have created a simple text box to enter user full name and a button which will store the information into the SQL server.

7) Open the "Views\HomePage.xaml\HomePage.xaml.cs" file and replace following code in it i.e.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using Data_Storage_using_SQL_server.Helper_Code.Common;
using Data_Storage_using_SQL_server.Model.BusinessLogic.Helper_Code.Common;

namespace Data_Storage_using_SQL_server.Views
{
    /// <summary>
    /// Interaction logic for HomePage.xaml
    /// </summary>
    public partial class HomePage : Page
    {
        public HomePage()
        {
            InitializeComponent();
        }

        private void BtnReg_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                // Initialization.
                string fullname = this.txtName.Text;

                // Verification.
                if (string.IsNullOrEmpty(fullname))
                {
                    // Display Message
                    MessageBox.Show("This field can not be empty. Please Enter Full Name", "Fail", MessageBoxButton.OK, MessageBoxImage.Error);

                    // Info
                    return;
                }

                // Save Info.
                HomeBusinessLogic.SaveInfo(fullname);

                // Display Message
                MessageBox.Show("You are Successfully! Registered", "Success", MessageBoxButton.OK, MessageBoxImage.Information);
            }
            catch (Exception ex)
            {
                Console.Write(ex);

                // Display Message
                MessageBox.Show("Something goes wrong, Please try again later.", "Fail", MessageBoxButton.OK, MessageBoxImage.Error);
            }
        }
    }
}

In the above code, I have created the action method for the register button and perform database insertion action to store the data into SQL server.I have also code on screen display messages to prompt user about his/her action.

8) We need to attach the page inside the main window so, open the "MainWindow.xaml" file and replace following in it i.e.

<Window x:Class="Data_Storage_using_SQL_server.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:Data_Storage_using_SQL_server"
        mc:Ignorable="d"
        Title="WPF Walkthrough" d:DesignHeight="480" d:DesignWidth="640">
    <Grid>
        <Grid.Background>
            <ImageBrush ImageSource="Content/img/main_bg.jpg"/>
        </Grid.Background>
        <Frame x:Name="mainFrame" 
               NavigationUIVisibility="Hidden"/>
    </Grid>
</Window>

In the above code, I have add a default background image taken from freepike and a frame which contains my page. The window will immediately navigate to the main page.

9) Now, open the "MainWindow.xaml\MainWindow.cs" file and replace the following code in it i.e.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;

namespace Data_Storage_using_SQL_server
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {
        public MainWindow()
        {
            InitializeComponent();

            this.Loaded += MainWindow_Loaded;
        }

        private void MainWindow_Loaded(object sender, RoutedEventArgs e)
        {
            this.mainFrame.Navigate(new Uri("/Views/HomePage.xaml", UriKind.Relative));
        }
    }
}

The above piece of code will navigate the frame to my main page at the time of launching of the main window of the WPF application.

10) Execute the project and you will be able to see following i.e.

The user input data will be stored inside SQL server table as shown below i.e.

If the user click the register button without providing the data then he/she will be prompted with error message i.e.

Conclusion

In this article, you will learn to connect to SQL Server in order to store the data.. You will also learn about storing data from WPF application directly into SQL server by performing SQL queries at Data Access Layer of WPF application. You will also learn to display success & error messages on WPF UI screen to prompt the user for his/her actions with your WPF application. You will learn to add background to the main window and you will learn to add page within the window by using frame control

Disclaimer

The background image use in this article has been taken from freepike.