Net-informations.com
SiteMap  | About    

GridView from Stored Procedure

In this article, you can see how to populate a gridview from stored procedure.

Stored Procedure

A stored procedure is a group of Transact-SQL statements compiled into a single execution plan. Stored procedures are more secure than SQL queries code and often get result faster.

Database

In this article I have used Microsoft's Pubs database for sample data. You can download it free from the following link.

Download

How to create a Stored Procedure ?

The following SQL statements will create a Store Procedure.

gridview from stored procedure

The above code create a store procedure named as 'SPPUBLISHER' and it execute SQL statement that select data of all publishers from publishers table from the PUB database.

Grid View Binding using Stored Procedure

You can retrieve data from database using this Store Procedure and display it in a GridView.

To call a stored procedure from C# application, set the CommandType of the Command object to StoredProcedure.

  

command.CommandType = CommandType.StoredProcedure;

Next step is to inform the your Store Procedure name to the Command Object.

  

command.CommandText = "SPPUBLISHER";

The rest of the things are same as SQl Query string.

gridview populate from stored procedure

Finally you can set GridView DataSource as your Dataset.

bind gridview

Default.aspx

  

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
	<title></title>
</head>
<body>
	<form id="form1" runat="server">
	<div>
		<asp:GridView ID="GridView1" runat="server">
		</asp:GridView>
	</div>
	</form>
</body>
</html>

C# Source Code

  

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;
public partial class _Default : System.Web.UI.Page
{
	protected void Page_Load(object sender, EventArgs e)
	{
		SqlCommand command = new SqlCommand();
		SqlDataAdapter adapter = new SqlDataAdapter();
		DataSet ds = new DataSet();
		int i = 0;
		string sql = null;
		string connetionString = "Data Source=.;Initial Catalog=pubs;User ID=sa;Password=*****";
		SqlConnection connection = new SqlConnection(connetionString);
		connection.Open();
		command.Connection = connection;
		command.CommandType = CommandType.StoredProcedure;
		command.CommandText = "SPPUBLISHER";
		adapter = new SqlDataAdapter(command);
		adapter.Fill(ds);
		connection.Close();
		GridView1.DataSource = ds.Tables[0];
		GridView1.DataBind();
	}
}

VB.Net Source Code

  

Imports System.Drawing
Imports System.Data.SqlClient
Imports System.Data
Partial Class _Default
	Inherits System.Web.UI.Page
	Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
		Dim command As New SqlCommand()
		Dim adapter As New SqlDataAdapter()
		Dim ds As New DataSet()
		Dim i As Integer = 0
		Dim sql As String = Nothing
		Dim connetionString As String = "Data Source=.;Initial Catalog=pubs;User ID=sa;Password=*****"
		Dim connection As New SqlConnection(connetionString)
		connection.Open()
		command.Connection = connection
		command.CommandType = CommandType.StoredProcedure
		command.CommandText = "SPPUBLISHER"
		adapter = New SqlDataAdapter(command)
		adapter.Fill(ds)
		connection.Close()
		GridView1.DataSource = ds.Tables(0)
		GridView1.DataBind()
	End Sub
End Class






net-informations.com (C) 2016    Founded by raps mk
All Rights Reserved. All other trademarks are property of their respective owners.