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

In this blog we will learn how to 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:

Username varchar(50) primary key
Password varchar(50)

The Stored Procedure code Is as follows:

USE [bind]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create PROCEDURE [dbo].[spinsert]
@username varchar(50),
@password varchar(50)
AS
BEGIN
SET NOCOUNT ON;
insert into test values(@username,@password)
END

Step 2

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

<Window x:Class="WpfApplication17.MainWindow"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
Title="MainWindow" Height="350" Width="525">

<Grid>
  <Button x:Name="submit" FontSize="20" Content="submit" HorizontalAlignment="Left"   VerticalAlignment="Top" Width="75" Margin="131,219,0,0" Click="Button_Click_1"/>
  <TextBox x:Name="uname" FontSize="20" HorizontalAlignment="Left" Height="35"   TextWrapping="Wrap" VerticalAlignment="Top" Width="287" Margin="205,54,0,0"/>
  <TextBox x:Name="pass" FontSize="20" HorizontalAlignment="Left" Height="30"   TextWrapping="Wrap" VerticalAlignment="Top" Width="287" Margin="205,111,0,0"/>
  <TextBlock FontSize="20" HorizontalAlignment="Left" Margin="76,54,0,0"   TextWrapping="Wrap" Text="username" VerticalAlignment="Top"/>
  <TextBlock FontSize="20" HorizontalAlignment="Left" Margin="76,111,0,0"   TextWrapping="Wrap" Text="password" VerticalAlignment="Top"/>
  <Button x:Name="cancel" FontSize="20" Content="cancel" HorizontalAlignment="Left"   Margin="317,219,0,0" VerticalAlignment="Top" Width="75" Click="Button_Click"/>
</Grid>

</Window>

Step 3

The insertButton_Click event contains the following code:

private void Button_Click_1(object sender, RoutedEventArgs e)
{
  mc.username = uname.Text;
  mc.password = pass.Text;
  mc.insert();
  uname.Text = "";
  pass.Text = "";
  MessageBox.Show("record inserted");
}

Step 4

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

class myclass
{
   #region decleratons
   public string username { get;set;}
   public string password { get; set; }
   public string error { get; set; }
   #endregion
   #region methods
   public void insert()
   {
      SqlConnection con = new SqlConnection(ConfigurationSettings.AppSettings["constr"]);
      SqlCommand cmd = new SqlCommand("spinsert", con);
      cmd.CommandType = CommandType.StoredProcedure;
      SqlParameter puname = new SqlParameter("@username", SqlDbType.VarChar, 50);
      SqlParameter ppass = new SqlParameter("@password", SqlDbType.VarChar, 50);
      puname.Value = username;
      ppass.Value = password;
      cmd.Parameters.Add(puname);
      cmd.Parameters.Add(ppass);
      try
      {
         con.Open();
         cmd.ExecuteNonQuery();
      }

      catch (Exception ex)
      {
         error = ex.ToString();
      }

      finally
      {
         cmd.Dispose();
         con.Close();
      }

   }
   #endregion
}

Step 5

The app.config file contains the following code:

<configuration>
 <appSettings>
   <add key="constr" value="Data Source=server name; Initial Catalog= database name;Integrated Security=true;"/>
 </appSettings>
</configuration>

Step 6

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

“Record inserted”