Post
Ask Question

WPF - Data Storage Using SQL Server

For any development domain, be it mobile or web or desktop or service-based, data storage is an essential component whether it is done on the server side or the client side. WPF application is a client-side application and it also supports many data storage resources which depend on the 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 of Windows Presentation Form (WPF).
  2. Knowledge of T-SQL Programming
  3. Knowledge of C# programming.
  4. Knowledge of 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.

Let's begin now.

Step 1

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

  1. USE [WpfWalkthrough]  
  2. GO  
  3. /****** Object:  Table [dbo].[Register]    Script Date: 3/13/2018 5:36:30 PM ******/  
  4. DROP TABLE [dbo].[Register]  
  5. GO  
  6. /****** Object:  Table [dbo].[Register]    Script Date: 3/13/2018 5:36:30 PM ******/  
  7. SET ANSI_NULLS ON  
  8. GO  
  9. SET QUOTED_IDENTIFIER ON  
  10. GO  
  11. CREATE TABLE [dbo].[Register](  
  12.  [id] [int] IDENTITY(1,1) NOT NULL,  
  13.  [fullname] [nvarchar](max) NOT NULL,  
  14.  CONSTRAINT [PK_Register] PRIMARY KEY CLUSTERED   
  15. (  
  16.  [id] ASC  
  17. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
  18. ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]  
  19.   
  20. GO 

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

Step 2

You can see that your table is empty.

Step 3

Now, create a new WPF application project and name it "Data Storage using SQL server".

Step 4

Now, create "Helper_Code\Common\DAL.cs" file and replace the code with the following code in it.

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Data;  
  4. using System.Data.SqlClient;  
  5. using System.Linq;  
  6. using System.Text;  
  7. using System.Threading.Tasks;  
  8.   
  9. namespace Data_Storage_using_SQL_server.Helper_Code.Common  
  10. {  
  11.     public class DAL  
  12.     {  
  13.         public static int executeQuery(string query)  
  14.         {  
  15.             // Initialization.  
  16.             int rowCount = 0;  
  17.             string strConn = "Data Source=SQL Server Name(e.g. localhost);Database=SQL Database Name;User Id=SQL User Name;Password=SQL Password;";  
  18.             SqlConnection sqlConnection = new SqlConnection(strConn);  
  19.             SqlCommand sqlCommand = new SqlCommand();  
  20.   
  21.             try  
  22.             {  
  23.                 // Settings.  
  24.                 sqlCommand.CommandText = query;  
  25.                 sqlCommand.CommandType = CommandType.Text;  
  26.                 sqlCommand.Connection = sqlConnection;  
  27.                 sqlCommand.CommandTimeout = 12 * 3600; //// Setting timeeout for longer queries to 12 hours.  
  28.   
  29.                 // Open.  
  30.                 sqlConnection.Open();  
  31.   
  32.                 // Result.  
  33.                 rowCount = sqlCommand.ExecuteNonQuery();  
  34.   
  35.                 // Close.  
  36.                 sqlConnection.Close();  
  37.             }  
  38.             catch (Exception ex)  
  39.             {  
  40.                 // Close.  
  41.                 sqlConnection.Close();  
  42.   
  43.                 throw ex;  
  44.             }  
  45.   
  46.             return rowCount;  
  47.         }  
  48.     }  
  49. } 

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.

Step 5

Create "Model\BusinessLogic\HomeBusinessLogic.cs" file and replace the code with the following.

  1. using Data_Storage_using_SQL_server.Helper_Code.Common;  
  2. using System; 
  3. using System.Collections.Generic;  
  4. using System.IO;  
  5. using System.Linq;  
  6. using System.Reflection;  
  7. using System.Text;  
  8. using System.Threading.Tasks;  
  9.   
  10. namespace Data_Storage_using_SQL_server.Model.BusinessLogic.Helper_Code.Common  
  11. {  
  12.     public class HomeBusinessLogic  
  13.     {  
  14.         public static void SaveInfo(string fullname)  
  15.         {  
  16.             try  
  17.             {  
  18.                 // Query.  
  19.                 string query = "INSERT INTO [Register] ([fullname])" +  
  20.                                 " Values ('" + fullname + "')";  
  21.   
  22.                 // Execute.  
  23.                 DAL.executeQuery(query);  
  24.             }  
  25.             catch (Exception ex)  
  26.             {  
  27.                 throw ex;  
  28.             }  
  29.         }  
  30.     }  
  31. } 

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.

Step 6

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

  1. <Page x:Class="Data_Storage_using_SQL_server.Views.HomePage"  
  2.       xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"  
  3.       xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"  
  4.       xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"   
  5.       xmlns:d="http://schemas.microsoft.com/expression/blend/2008"   
  6.       xmlns:local="clr-namespace:Data_Storage_using_SQL_server.Views"  
  7.       mc:Ignorable="d"   
  8.       d:DesignHeight="480" d:DesignWidth="640"  
  9.       Title="HomePage">  
  10.   
  11.     <Grid>  
  12.         <DockPanel>  
  13.             <Grid>  
  14.                 <Grid.RowDefinitions>  
  15.                     <RowDefinition Height="0.05*" />  
  16.                     <RowDefinition Height="*" />  
  17.                     <RowDefinition Height="0.05*" />  
  18.                 </Grid.RowDefinitions>  
  19.   
  20.                 <Border Grid.Row="1"  
  21.                         Width=" 400"  
  22.                         Height="300"   
  23.                         BorderThickness="1"   
  24.                         BorderBrush="Black"   
  25.                         CornerRadius="20"   
  26.                         Opacity="1">  
  27.                     <Border.Background>  
  28.                         <ImageBrush ImageSource="/Data Storage using SQL server;component/Content/img/bg_2.png">  
  29.                             <ImageBrush.RelativeTransform>  
  30.                                 <TransformGroup>  
  31.                                     <ScaleTransform CenterY="0.5" CenterX="0.5" ScaleX="1.5" ScaleY="1.5"/>  
  32.                                     <SkewTransform CenterY="0.5" CenterX="0.5"/>  
  33.                                     <RotateTransform CenterY="0.5" CenterX="0.5"/>  
  34.                                     <TranslateTransform/>  
  35.                                 </TransformGroup>  
  36.                             </ImageBrush.RelativeTransform>  
  37.                         </ImageBrush>  
  38.                     </Border.Background>  
  39.   
  40.                     <StackPanel Orientation="Vertical"   
  41.                                 HorizontalAlignment="Center"  
  42.                                 VerticalAlignment="Center"  
  43.                                 Width=" 400"  
  44.                                 Height="300" >  
  45.   
  46.                         <TextBlock Text="Enter Your Full Name"  
  47.                                    VerticalAlignment="Center"  
  48.                                    HorizontalAlignment="Center"   
  49.                                    Margin="0,50,0,0"   
  50.                                    FontWeight="Bold"   
  51.                                    FontSize="18"   
  52.                                    Foreground="Black" />  
  53.   
  54.                         <Border Width="220"  
  55.                                 Height="50"  
  56.                                 Margin="0,10,0,0">  
  57.   
  58.                             <Border.Background>  
  59.                                 <ImageBrush ImageSource="/Data Storage using SQL server;component/Content/img/text-box_bg.png"/>  
  60.                             </Border.Background>  
  61.   
  62.                             <TextBox x:Name="txtName"   
  63.                                     BorderThickness="0"  
  64.                                     FontSize="18"  
  65.                                     Width="220"  
  66.                                     Height="50"   
  67.                                     Background="{x:Null}"   
  68.                                     Padding="10,12,0,0"   
  69.                                     Foreground="Black"  
  70.                                     HorizontalAlignment="Center"/>  
  71.                         </Border>  
  72.   
  73.                         <Button x:Name="btnReg"  
  74.                                 Content="Register"  
  75.                                 Width="220"   
  76.                                 Height="50"  
  77.                                 Margin="0,10,0,0"  
  78.                                 FontSize="18"   
  79.                                 FontWeight="Bold"   
  80.                                 Click="BtnReg_Click" />  
  81.   
  82.   
  83.                     </StackPanel>  
  84.                 </Border>  
  85.             </Grid>  
  86.   
  87.         </DockPanel>  
  88.     </Grid>  
  89. </Page> 

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

Step 7

Open the "Views\HomePage.xaml\HomePage.xaml.cs" file and replace with the following code.

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Text;  
  5. using System.Threading.Tasks;  
  6. using System.Windows;  
  7. using System.Windows.Controls;  
  8. using System.Windows.Data;  
  9. using System.Windows.Documents;  
  10. using System.Windows.Input;  
  11. using System.Windows.Media;  
  12. using System.Windows.Media.Imaging;  
  13. using System.Windows.Navigation;  
  14. using System.Windows.Shapes;  
  15. using Data_Storage_using_SQL_server.Helper_Code.Common;  
  16. using Data_Storage_using_SQL_server.Model.BusinessLogic.Helper_Code.Common;  
  17.   
  18. namespace Data_Storage_using_SQL_server.Views  
  19. {  
  20.     /// <summary>  
  21.     /// Interaction logic for HomePage.xaml  
  22.     /// </summary>  
  23.     public partial class HomePage : Page  
  24.     {  
  25.         public HomePage()  
  26.         {  
  27.             InitializeComponent();  
  28.         }  
  29.   
  30.         private void BtnReg_Click(object sender, RoutedEventArgs e)  
  31.         {  
  32.             try  
  33.             {  
  34.                 // Initialization.  
  35.                 string fullname = this.txtName.Text;  
  36.   
  37.                 // Verification.  
  38.                 if (string.IsNullOrEmpty(fullname))  
  39.                 {  
  40.                     // Display Message  
  41.                     MessageBox.Show("This field can not be empty. Please Enter Full Name", "Fail", MessageBoxButton.OK, MessageBoxImage.Error);  
  42.   
  43.                     // Info  
  44.                     return;  
  45.                 }  
  46.   
  47.                 // Save Info.  
  48.                 HomeBusinessLogic.SaveInfo(fullname);  
  49.   
  50.                 // Display Message  
  51.                 MessageBox.Show("You are Successfully! Registered", "Success", MessageBoxButton.OK, MessageBoxImage.Information);  
  52.             }  
  53.             catch (Exception ex)  
  54.             {  
  55.                 Console.Write(ex);  
  56.   
  57.                 // Display Message  
  58.                 MessageBox.Show("Something goes wrong, Please try again later.", "Fail", MessageBoxButton.OK, MessageBoxImage.Error);  
  59.             }  
  60.         }  
  61.     }  
  62. } 

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

Step 8

We need to attach the page inside the main window. So, open the "MainWindow.xaml" file and replace it with the following:

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

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

Step 9

Now, open the "MainWindow.xaml\MainWindow.cs" file and replace the code with the following.

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Text;  
  5. using System.Threading.Tasks;  
  6. using System.Windows;  
  7. using System.Windows.Controls;  
  8. using System.Windows.Data;  
  9. using System.Windows.Documents;  
  10. using System.Windows.Input;  
  11. using System.Windows.Media;  
  12. using System.Windows.Media.Imaging;  
  13. using System.Windows.Navigation;  
  14. using System.Windows.Shapes;  
  15.   
  16. namespace Data_Storage_using_SQL_server  
  17. {  
  18.     /// <summary>  
  19.     /// Interaction logic for MainWindow.xaml  
  20.     /// </summary>  
  21.     public partial class MainWindow : Window  
  22.     {  
  23.         public MainWindow()  
  24.         {  
  25.             InitializeComponent();  
  26.   
  27.             this.Loaded += MainWindow_Loaded;  
  28.         }  
  29.   
  30.         private void MainWindow_Loaded(object sender, RoutedEventArgs e)  
  31.         {  
  32.             this.mainFrame.Navigate(new Uri("/Views/HomePage.xaml", UriKind.Relative));  
  33.         }  
  34.     }  
  35. } 

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

Step 10

Execute the project and you will be able to see the following screen.







The user input data will be stored in SQL Server table as shown below.

 

If the user clicks the Register button without providing the data, then he/she will be prompted with an error message.



Conclusion

In this article, you have learned to connect to SQL Server in order to store the data. And also you learned about storing the data from WPF applications directly into the SQL server by performing SQL queries at Data Access Layer of WPF application. We also saw how to display success & error messages on WPF UI screen to prompt the user for his/her actions with our WPF application.

Disclaimer

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


Similar Articles