Read & Insert Data Using Stored Procedures in C#

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

 Collapse | Copy Code

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

 Collapse | Copy Code

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

 Collapse | Copy Code

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.

 Collapse | Copy Code

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

 Collapse | Copy Code

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

 Collapse | Copy Code

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.

 Collapse | Copy Code

private void Form1_Load(object sender, EventArgs e)
        {
            this.LoadGrid();           
        }

 Now you can read and insert data through a stored procedure.

ga dassa, 6 Oct 2014  on codeproject