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”