Thursday, 5 September 2013

How to repeater using SqlDataAdapter, DataTable and Stored procedure in Sql server using ASP.NET,VB.NET/C#

Hi friends,in this article I will explain about How to bind repeater using SqlDataAdapter, DataTable and Stored procedure in Sql server using ASP.NET,VB.NET/C#.
Create a Database e.g. "Aspdotnet-kishore" and a table under that DataBase in Sql Server and name it "EMP" as shown in figure:-

And create the stored procedure as display_emp_data  to display the emp data as shown below.
CREATE PROCEDURE display_EMP_DATA                      
AS
BEGIN              
   SELECT * FROM EMP           
END
And open the .aspx page and drag and drop the Gridview.
In we.config file write the following code to create the connection string in the <connectionStrings>
Tag.
<connectionStrings>
    <add name="con" connectionString="database=Aspdotnet-kishore;uid=sa;password=123;" />
</connectionStrings>

And imports below namespaces
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

Add a GridView control in design page of your asp.net website under <BODY> tag
       <asp:Repeater ID="reptremp" runat="server">
        <HeaderTemplate>
   
        <table style=" border:1px solid #c1650f; width:300px" cellpadding="0">
<tr style="background-color:#c1650f; color:White">
<td colspan="2">
<b><center>Employee Details</center></b>
</td>
</tr>
</HeaderTemplate>
<ItemTemplate>
<tr style="background-color:#EBEFF0">
<td>
<table style="background-color:#EBEFF0;border-top:1px dotted #c1650f; width:300px" >
<tr>
<td>
<b>Employee ID:</b>
<asp:Label ID="lblBookName" runat="server" Text='<%#Eval("Emp_id") %>'/>
</td>
</tr>
<tr>
<td>
<b>Employee Name:</b>
<asp:Label ID="lblAuthor" runat="server" Text='<%#Eval("empname") %>'/>
</td>
</tr>
<tr>
<td>
<b>Employee Salary:</b>
<asp:Label ID="lblPublisher" runat="server" Text='<%#Eval("salary") %>'/>
</td>
</tr>
</table>
</td>
</tr>
</ItemTemplate>
<FooterTemplate>
</table>
</FooterTemplate>
        </asp:Repeater>

And write the following code in code behind.
In VB.NET:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

Partial Class _Default
    Inherits System.Web.UI.Page
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            Binddata()
        End If
    End Sub
    Private Sub Binddata()
   Dim dt As New DataTable()
        Try
            Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("Con").ConnectionString)

            Dim adp As New SqlDataAdapter("display_EMP_DATA", con)
            adp.SelectCommand.CommandType = CommandType.StoredProcedure
            adp.Fill(dt)

            If dt.Rows.Count > 0 Then
                Reptremp.DataSource = dt
                Reptremp.DataBind()
            Else
                Reptremp.DataSource = Nothing
                Reptremp.DataBind()
            End If
        Catch ex As Exception
            Response.Write("Error Occured: " & ex.ToString())
        Finally
            dt.Clear()
            dt.Dispose()
        End Try
    End Sub
End Class
In C#.NET:
using Microsoft.VisualBasic;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Data.SqlClient;
using System.Configuration;

partial class _Default : System.Web.UI.Page
{
       protected void Page_Load(object sender, System.EventArgs e)
       {
              if (!IsPostBack) {
                     Binddata();
              }
       }
       private void Binddata()
       {
              DataTable dt = new DataTable();
              try {
                     SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings("Con").ConnectionString);

                     SqlDataAdapter adp = new SqlDataAdapter("display_EMP_DATA", con);
                     adp.SelectCommand.CommandType = CommandType.StoredProcedure;
                     adp.Fill(dt);

                     if (dt.Rows.Count > 0) {
                           Reptremp.DataSource = dt;
                           Reptremp.DataBind();
                     } else {
                           Reptremp.DataSource = null;
                            Reptremp.DataBind();
                     }
              } catch (Exception ex) {
                     Response.Write("Error Occured: " + ex.ToString());
              } finally {
                     dt.Clear();
                     dt.Dispose();
              }
       }
    Public _Default()
       {
              Load += Page_Load;
       }
}



Save and run the page it will like as shown in the below figure.

No comments:

Post a Comment

© 2012-2014 Aspdotnet-Kishore.blogspot.com. All Rights Reserved.
The content is copyrighted to Kishore and may not be reproduced on other websites without permission from the owner.