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
  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">
  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>
  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. }
  26. catch (Exception ex)
  27. {
  28. error = ex.ToString();
  29. }
  31. finally
  32. {
  33. cmd.Dispose();
  34. con.Close();
  35. }
  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”