Update and Delete on Database Table through Windows Form Using Stored Procedures

In my last article I explained how to insert a record into database table through windows form, in this article we will see how we can extend it to update and delete functionality.

Below is the windows form where we will update the data already present in database:

As previously explained, we require a table, I have named my table as "blog_test_up" which has three fields-name , age and phone.

The code is as follows:

Create your table--

CREATE TABLE [dbo].[blog_test_up]

(

    [name] [char](50) NOT NULL,

    [age] [numeric](5, 0) NOT NULL,

    [phone] [varchar](50) NULL

)

Second, we need to create three different stored procedures for insert update and delete. I have already explained about insert stored procedure in my previous article, so I will proceed with update and delete.

Stored procedure for Update:

CREATE PROCEDURE [dbo].[blog_update_up]

(@name char(50),

@age numeric(5,0),

@phone varchar(50),

@msg varchar(100) output

)

    -- Add the parameters for the stored procedure here

AS

begin try

begin

BEGIN tran

 

update blog_test_up set age=@age, phone=@phone where name=@name

commit

select @msg='Updation Successful'

return 0

end

 

End try

 

BEGIN catch

    

    SET NOCOUNT ON;

 

    select @msg='Some Problem Ocured In Processing'

    rollback

    return 1

END catch

//********************************************************************

Stored procedure for Delete

CREATE PROCEDURE [dbo].[blog_delete_up]

(@name char(50),

@msg varchar(100) output

)

    AS

begin try

begin

BEGIN tran

delete from blog_test_up where name=@name;

commit

select @msg='Deletion Successful'

return 0

end

 

End try

 

BEGIN catch

    

    SET NOCOUNT ON;

    select @msg='Some Problem Ocured In Processing'

    rollback

    return 1

END catch

 

Finally, we will code our windows form application in C#:

Eg:

update blog_test_up set age=@age, phone=@phone where name=@name

delete from blog_test_up where name=@name;

Therefore supplying name field for update and delete becomes necessary, although one can have his/her own style of implementing functionality which completely depends on programmer.

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

using System.IO;

using System.Data.SqlClient;

 

namespace blog

{

public partial class Form1 : Form

{

SqlConnection con;

string constr=@"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=your catalog;Data Source=your data source";

string ntfic;

SqlCommand cmd;

public Form1()

{

InitializeComponent();

}

 

private void label2_Click(object sender, EventArgs e)

{

 

}

//**********************************insert***************************

private void button1_Click(object sender, EventArgs e)

{

try

{

con = new SqlConnection();

con.ConnectionString = constr;

con.Open();

 

cmd = new SqlCommand("blog_insert_up", con);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("@name", SqlDbType.NVarChar).Value = textBox1.Text ;

cmd.Parameters.Add("@age", SqlDbType.NVarChar).Value = textBox2.Text;

cmd.Parameters.Add("@phone", SqlDbType.NVarChar).Value = textBox3.Text;

//**************************************************************************

SqlParameter param = new SqlParameter("@returnval", SqlDbType.Int);

param.Size = 50;

param.Direction = ParameterDirection.ReturnValue;

//param.ParameterName = "returnValue";

//add to parameter to collection

cmd.Parameters.Add(param);

//***********************************************************************

SqlParameter param1 = new SqlParameter("@msg", SqlDbType.VarChar);

param1.Size = 50;

param1.Direction = ParameterDirection.Output;

//param.ParameterName = "returnValue";

//add to parameter to collection

cmd.Parameters.Add(param1);

//*************************************************************************

cmd.ExecuteNonQuery();

ntfic = cmd.Parameters["@msg"].Value.ToString();

label4.Text = cmd.Parameters["@msg"].Value.ToString();

ntfic = cmd.Parameters["@msg"].Value.ToString() + "--" + cmd.Parameters["@returnval"].Value.ToString();

 

}

 

catch (Exception ex)

{

label4.Text= ntfic + ex.ToString();

}

finally

{

con.Close();

con.Dispose();

cmd.Dispose();

 

}

}

 

//********************************delete**************************************

private void button3_Click(object sender, EventArgs e)

{

try

{

con = new SqlConnection();

con.ConnectionString = constr;

con.Open();

 

cmd = new SqlCommand("blog_delete_up", con);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("@name", SqlDbType.NVarChar).Value = textBox1.Text;

 

//**************************************************************************

SqlParameter param = new SqlParameter("@returnval", SqlDbType.Int);

param.Size = 50;

param.Direction = ParameterDirection.ReturnValue;

//param.ParameterName = "returnValue";

//add to parameter to collection

cmd.Parameters.Add(param);

//***********************************************************************

SqlParameter param1 = new SqlParameter("@msg", SqlDbType.VarChar);

param1.Size = 50;

param1.Direction = ParameterDirection.Output;

//param.ParameterName = "returnValue";

//add to parameter to collection

cmd.Parameters.Add(param1);

//*************************************************************************

cmd.ExecuteNonQuery();

ntfic = cmd.Parameters["@msg"].Value.ToString();

label4.Text = cmd.Parameters["@msg"].Value.ToString();

ntfic = cmd.Parameters["@msg"].Value.ToString() + "--" + cmd.Parameters["@returnval"].Value.ToString();

 

}

 

catch (Exception ex)

{

label4.Text = ntfic + ex.ToString();

}

finally

{

con.Close();

con.Dispose();

cmd.Dispose();

 

}

}

 

//*********************update***************************************

private void button2_Click(object sender, EventArgs e)

{

try

{

con = new SqlConnection();

con.ConnectionString = constr;

con.Open();

 

cmd = new SqlCommand("blog_update_up", con);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("@name", SqlDbType.NVarChar).Value = textBox1.Text;

cmd.Parameters.Add("@age", SqlDbType.NVarChar).Value = textBox2.Text;

cmd.Parameters.Add("@phone", SqlDbType.NVarChar).Value = textBox3.Text;

//**************************************************************************

SqlParameter param = new SqlParameter("@returnval", SqlDbType.Int);

param.Size = 50;

param.Direction = ParameterDirection.ReturnValue;

//param.ParameterName = "returnValue";

//add to parameter to collection

cmd.Parameters.Add(param);

//***********************************************************************

SqlParameter param1 = new SqlParameter("@msg", SqlDbType.VarChar);

param1.Size = 50;

param1.Direction = ParameterDirection.Output;

//param.ParameterName = "returnValue";

//add to parameter to collection

cmd.Parameters.Add(param1);

//*************************************************************************

cmd.ExecuteNonQuery();

ntfic = cmd.Parameters["@msg"].Value.ToString();

label4.Text = cmd.Parameters["@msg"].Value.ToString();

ntfic = cmd.Parameters["@msg"].Value.ToString() + "--" + cmd.Parameters["@returnval"].Value.ToString();

 

}

 

catch (Exception ex)

{

label4.Text = ntfic + ex.ToString();

}

finally

{

con.Close();

con.Dispose();

cmd.Dispose();

 

}

}

}

}

 

Tags: , , , ,

Windows Form