Insertion on Database Table through Windows Form Using Stored Procedures

Suppose we have database table named as "blog_test_up" and we have to insert records into this through an windows application form.

First, we will require a table, here I have named my table as "blog_test_up" which has three fields-name ,age and phone.

The code is as follows:

CREATE TABLE [dbo].[blog_test_up]

(

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

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

    [phone] [varchar](50) NULL

)

Second, We should have stored procedure which is as follows:

CREATE PROCEDURE [dbo].[blog_insert_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

insert into blog_test_up values(@name,@age,@phone);

commit

select @msg='Insertion 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#:

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;//for database connectivity

 

namespace blog

{

public partial class Form1 : Form

{

SqlConnection con;

string constr=@"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=blog;Data Source=your Database name or IP";

string ntfic;

SqlCommand cmd;

public Form1()

{

InitializeComponent();

}

 

//**********************************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;

//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: , , ,

SQL Server | Windows Form