How to bind a GridView control programmatically to data from a stored procedure
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:
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.
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:
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.
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.
thanks for sharing this code
really helpful
please contribute more in c#