This article will show you in a simple manner how to read data from SQL data base by using stored procedures and how to insert data through a stored procedure.
Background
There is a description to explain how to write stored procedures as well. Since it is a complex area for beginners I have explained it in very simple manner.Using the code
Step 1 : Create the table(user_tab) to insert data
CREATE TABLE [dbo].[user_tab](
[U_name] [varchar](50) NULL,
[U_pwd] [varchar](50) NULL,
[U_type] [varchar](10) NULL
)
Step 2 : Create the stored procedure to insert data to user table
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE AddUser
@name varchar(50),
@pwd varchar(50),
@type varchar(10)
AS
BEGIN
SET NOCOUNT ON;
insert into user_tab (U_name,U_pwd,U_type)
values(@name,@pwd,@type)
END
GO
Step 3 :Add windows form
Create a windows form to enter the details which we are going to save in database.
textbox1=> to enter name
textbox2=> to enter password
combobox => to select user type
button(btnSave)=> to save data to database through stored procedure
Step 3 : Add a class called DBConnect
class DBConnect
{
public static SqlConnection myCon = null;
public void CreateConnection()
{
myCon = new SqlConnection("Data Source=GAYANI-PC;Initial Catalog=soft1;Integrated Security=True");
myCon.Open();
}
}
Step 4 : Button click event
Click on button. Then add this code inside that event.
private void btnAdd_Click(object sender, EventArgs e) { SqlConnection con = DBConnect.myCon; SqlCommand cmd = con.CreateCommand(); cmd.CommandText = "Execute AddUser @name,@pwd,@type"; cmd.Parameters.Add("@name", SqlDbType.VarChar, 50).Value = textBox1.Text.ToString(); cmd.Parameters.Add("@pwd", SqlDbType.VarChar, 50).Value = textBox2.Text.ToString(); cmd.Parameters.Add("@type", SqlDbType.VarChar, 10).Value = comboBox1.Text.ToString(); con.Open(); cmd.ExecuteNonQuery(); con.Close(); }You can insert data to the database now ! Check it.
Step 5 : Stored procedure to Read data
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE ReadUser
AS
BEGIN
SET NOCOUNT ON;
select U_name,U_type from user_tab
END
GO
Step 6 : Read data through Stored procedure(ReadUser) and display on a grid view
public void LoadGrid()
{
SqlConnection con = DBConnect.myCon;
SqlDataReader rd;
using(con)
{
SqlCommand cmd = new SqlCommand("ReadUser",con); // Read user-> stored procedure name
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
rd = cmd.ExecuteReader();
while (rd.Read())
{
dataGridView1.Rows.Add(rd[0].ToString(), rd[1].ToString()); //gridview has 2 columns only(name, type)
}
rd.Close();
}
con.Close();
}
Step 7 : Call above method to form load event or any button click event.
private void Form1_Load(object sender, EventArgs e)
{
this.LoadGrid();
}
Now you can read and insert data through a stored procedure.
