Click here to Skip to main content
15,510,281 members
Articles / Web Development / ASP.NET
Tip/Trick
Posted 6 Oct 2014

Stats

122.8K views
4 bookmarked

Read & Insert Data Using Stored Procedures in C#

Rate me:
Please Sign up or sign in to vote.
2.95/5 (10 votes)
6 Oct 2014CPOL1 min read
Read & Insert Data Using Stored Procedures

Introduction

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

SQL
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

SQL
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

C#
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

SQL
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

C#
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.

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

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

History

Keep a running update of any changes or improvements you've made here.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Student
Sri Lanka Sri Lanka
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralMy vote of 5 Pin
srilekhamenon17-Nov-22 18:18
professionalsrilekhamenon17-Nov-22 18:18 
GeneralMy vote of 1 Pin
Hitesh Asodiya1-Jun-22 3:04
Hitesh Asodiya1-Jun-22 3:04 
Questionstep 3 issue Pin
Member 1431499517-Apr-19 21:57
Member 1431499517-Apr-19 21:57 
Questionhow do I insert records using userid Pin
Member 103674354-Jan-15 4:15
Member 103674354-Jan-15 4:15 
GeneralMy vote of 1 Pin
Amol_B6-Oct-14 23:18
professionalAmol_B6-Oct-14 23:18 
Are you writing Article just to gain CP points? what is new the it ? You are just repeating something whihc is very basic and it is there from long time.
GeneralRe: My vote of 1 Pin
gayani dassa12-Oct-14 5:07
gayani dassa12-Oct-14 5:07 
GeneralMy vote of 2 Pin
Hasan Habib Surzo6-Oct-14 21:17
Hasan Habib Surzo6-Oct-14 21:17 
GeneralRe: My vote of 2 Pin
gayani dassa12-Oct-14 5:07
gayani dassa12-Oct-14 5:07 
GeneralMy vote of 1 Pin
Antonio Ripa6-Oct-14 5:30
professionalAntonio Ripa6-Oct-14 5:30 
GeneralRe: My vote of 1 Pin
gayani dassa12-Oct-14 5:08
gayani dassa12-Oct-14 5:08 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.