Resources » Code Snippets » DataGridView

How to bind a GridView control programmatically to data from a stored procedure


Posted Date: 27-Sep-2012  Last Updated:   Category: DataGridView    
Author: Member Level: Gold    Points: 60


Are you looking for the code to connect or bind data to the GridView control? This article will help you learn how to programmatically connect a GridView control to the data from a stored procedure using C# and VB.NET.



A GridView control can be connected to the data source control in two ways. First by setting its DataSourceID property as discussed in an earlier article: What is a GridView control and how to bind GridView to data and then selecting the columns from the control's task panel.

The other way to connect a GridView control to a data source is by coding. In this article we will discuss how to programmatically bind data form a stored procedure to a dataset and then populate the GridView control with this data using C# and VB.NET.

In order to bind a dataset to the GridView control basically one has to follow the below mentioned steps:
  1. Create an SQLCommand object.

  2. After the SQLCommand object is created pass the name of the stored procedure to which you want to connect it. Also add the input output parameters with values to this object created.
    In case you want to connect the GridView to a data table you should connect the command object to an sql query that would fetch data.

  3. Now the next step would be to execute the stored procedure using DataAdapter and then fill the dataset with the data received from the procedure.

  4. The last step would be to bind the dataset to populate the GridView.


To demonstrate how to programmatically connect and bind a GridView to data source I have taken an example where I have created a stored procedure as follows, to fetch the student details from a table tblStudents.

CREATE procedure GetStudentDetail(
@studentid Int //The input parameter which will be fetched from the textbox
)
As
Begin
Select Firstname + ' ' + Lastname as "Student Name", Course, Department,
[Total Fees ] from tbl_Students where Studentid = @studentid
End

To test the execution of the SP, use the following sql statement:
Execute a stored procedure

When executing it through the front end the parameter will be user defined as the user inputs the Student ID in a text box. At the click of the "Show Detail" button the code for binding the GridView to the stored procedure GetStudentDetail will be executed to display the required column details.
Lets first see the code to bind a GridView control using C#.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;

namespace gridview
{
public partial class WebForm4 : System.Web.UI.Page
{
SqlConnection sqlcon = new SqlConnection("Data Source=Local-host;Initial
Catalog=HRM_Login;Integrated Security=True");
SqlCommand sqlcmd = new SqlCommand();
SqlDataAdapter sqladp = new SqlDataAdapter();
DataSet ds = new DataSet();

protected void Page_Load(object sender, EventArgs e)
{
Label2.Visible = false;
GridView1.Visible = false;
Label2.Text = "The details for the student with Student ID " +
txt_studentid.Text;


}

protected void Button1_Click(object sender, EventArgs e)
{
Label2.Visible = true;
GridView1.Visible = true;

sqlcmd.Connection = sqlcon;
sqlcmd.CommandText = "GetStudentName";
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.Parameters.Add(new SqlParameter("@Studentid", SqlDbType.Int)).Value
= Int32.Parse(txt_studentid.Text);
sqladp.SelectCommand = sqlcmd;
sqladp.Fill(ds);
GridView1.DataSource = ds.Tables[0];
GridView1.DataBind();
}


}
}


The same code can be written in VB.Net as well in the following manner:

Dim sqlcon as SqlConnection = new SqlConnection("Data Source=Local-
host;Initial Catalog=HRM_Login;Integrated Security=True");
Dim sqlcmd as SqlCommand;
Dim sqladp as new SqlDataAdapter();
Dim ds as new DataSet();



The code in the page load and button click event will remain similar in VB.NET with a few modifications in the syntax if required.

Binding a stored procedure to GridView

On executing the above code the following output is achieved as shown in the screenshot. The user will be required to enter the student ID in the text box. Taking that value as parameter the Stored procedure would fetch the data from the source and bind it to the GridView.
Related Resources:


Read related articles: GridView DataBinding    GridView    

Did you like this resource? Share it with your friends and show your love!

Responses to "How to bind a GridView control programmatically to data from a stored procedure"
Author: Shalu Hijas    11 Oct 2012Member Level: Bronze   Points : 0
thanks for sharing this code

really helpful

please contribute more in c#



Author: Abedualrhman    11 Nov 2012Member Level: Bronze   Points : 0
Thank you


Feedbacks      

Post Comment:




  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:   Sign In to fill automatically.
    Email: (Will not be published, but required to validate comment)



    Type the numbers and letters shown on the left.


    Submit Article     Return to Article Index

    Subscribe to Subscribers
    Active Members
    TodayLast 7 Daysmore...

    Awards & Gifts
    Talk to Webmaster Tony John

    Online Members

    jeevan
    More...
    Copyright © SpiderWorks Technologies Pvt Ltd., Kochi, India