GridView from Stored Procedure

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

Stored Procedure

Stored procedures provide a secure and efficient way to encapsulate Transact-SQL statements, offering improved performance, code reusability, and enhanced security. They are a valuable tool for database developers and administrators to enhance the functionality and manageability of database systems.

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.

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.

Finally you can set GridView DataSource as your Dataset.

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

Next > How to create Gridview Popup Form