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”