Post
Ask Question

Insert Data into SQL Server using Stored Procedure and Textbox in WPF

Initial Steps

  • Create a table in sql server.
  • And insert stored procedure.
  • A WPF web form with 2 text boxes to edit the parameter values.
  • A message box to display the inserted or not inserted report from the execution of the ExecuteNonQuery() command.

I am using Visual Studio express 2012 and SQL Server 2008.

My goal is to insert text boxes data into the table using the stored procedure.

I have taken only two textboxes to keep this procedure as simple as possible.

The code is bellow:

Step 1: The table name in sql server is test and the table definition is as follow:

  1. Username varchar(50) primary key  
  2. Password varchar(50)  

The Stored Procedure code Is as follows:

  1. USE [bind]  
  2. GO  
  3. SET ANSI_NULLS ON  
  4. GO  
  5. SET QUOTED_IDENTIFIER ON  
  6. GO  
  7.   
  8. create PROCEDURE [dbo].[spinsert]  
  9. @username varchar(50),  
  10. @password varchar(50)  
  11. AS  
  12. BEGIN  
  13. SET NOCOUNT ON;  
  14. insert into test values(@username,@password)  
  15. END  

Step 2

I have defined 2 textboxs, and 2 Button in WPF as Below:

  1. <Window x:Class="WpfApplication17.MainWindow"  
  2. xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"  
  3. xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"  
  4. Title="MainWindow" Height="350" Width="525">  
  5.   
  6. <Grid>  
  7.   <Button x:Name="submit" FontSize="20" Content="submit" HorizontalAlignment="Left"   VerticalAlignment="Top" Width="75" Margin="131,219,0,0" Click="Button_Click_1"/>  
  8.   <TextBox x:Name="uname" FontSize="20" HorizontalAlignment="Left" Height="35"   TextWrapping="Wrap" VerticalAlignment="Top" Width="287" Margin="205,54,0,0"/>  
  9.   <TextBox x:Name="pass" FontSize="20" HorizontalAlignment="Left" Height="30"   TextWrapping="Wrap" VerticalAlignment="Top" Width="287" Margin="205,111,0,0"/>  
  10.   <TextBlock FontSize="20" HorizontalAlignment="Left" Margin="76,54,0,0"   TextWrapping="Wrap" Text="username" VerticalAlignment="Top"/>  
  11.   <TextBlock FontSize="20" HorizontalAlignment="Left" Margin="76,111,0,0"   TextWrapping="Wrap" Text="password" VerticalAlignment="Top"/>  
  12.   <Button x:Name="cancel" FontSize="20" Content="cancel" HorizontalAlignment="Left"   Margin="317,219,0,0" VerticalAlignment="Top" Width="75" Click="Button_Click"/>  
  13. </Grid>  
  14.   
  15. </Window>  
Step 3

The insertButton_Click event contains the following code:

  1. private void Button_Click_1(object sender, RoutedEventArgs e)  
  2. {  
  3.   mc.username = uname.Text;  
  4.   mc.password = pass.Text;  
  5.   mc.insert();  
  6.   uname.Text = "";  
  7.   pass.Text = "";  
  8.   MessageBox.Show("record inserted");  
  9. }  

Step 4

The class file (.cs) contains the following code:

  1. class myclass  
  2. {  
  3.    #region decleratons  
  4.    public string username { get;set;}  
  5.    public string password { get; set; }  
  6.    public string error { get; set; }  
  7.    #endregion  
  8.    #region methods  
  9.    public void insert()  
  10.    {  
  11.       SqlConnection con = new SqlConnection(ConfigurationSettings.AppSettings["constr"]);  
  12.       SqlCommand cmd = new SqlCommand("spinsert", con);  
  13.       cmd.CommandType = CommandType.StoredProcedure;  
  14.       SqlParameter puname = new SqlParameter("@username", SqlDbType.VarChar, 50);  
  15.       SqlParameter ppass = new SqlParameter("@password", SqlDbType.VarChar, 50);  
  16.       puname.Value = username;  
  17.       ppass.Value = password;  
  18.       cmd.Parameters.Add(puname);  
  19.       cmd.Parameters.Add(ppass);  
  20.       try  
  21.       {  
  22.          con.Open();  
  23.          cmd.ExecuteNonQuery();  
  24.       }  
  25.   
  26.       catch (Exception ex)  
  27.       {  
  28.          error = ex.ToString();  
  29.       }  
  30.   
  31.       finally  
  32.       {  
  33.          cmd.Dispose();  
  34.          con.Close();  
  35.       }  
  36.   
  37.    }  
  38.    #endregion  
  39. }  
Step 5

The app.config file contains the following code:
  1. <configuration>  
  2.  <appSettings>  
  3.    <add key="constr" value="Data Source=server name; Initial Catalog= database name;Integrated Security=true;"/> 
  4.  </appSettings>  
  5. </configuration>  
Step 6

Finally, When we click the button, it displays in the message box the following text:

“Record inserted”