Thursday, May 16, 2013

How to Bind Datalist in Asp.net


Introduction: In this post I try to explain how to bind Datalist control in Asp.net using Store procedure.

Description:
I have created a table name STUDENT_DETAIL and insert data into table. STUDENT_ID is primary key.
STUDENT_ID
int
STUDENT_NAME
varchar(50)
STUDENT_ADDRESS
varchar(50)
STUDENT_CLASS
varchar(50)

Create a Store Procedure:
CREATE PROCEDURE DISPLAY_DATA
     
AS
BEGIN
     
      SET NOCOUNT ON;
SELECT * FROM dbo.STUDENT_DETAIL
 
END
GO


Now open the Visual Studio>Go to File>New>Website. Add the Connectionstring in web.config file of website.
<configuration>
       <connectionStrings>
    <add name="connection" connectionString="Data Source=SYS-1F78031ED0A;Initial Catalog=TestBlog;Integrated Security=True"/>
       </connectionStrings>
       <system.web>
        <compilation debug="true" targetFramework="4.0" />
    </system.web>
</configuration>

After that add new web form to website, drag and drop the Datalist data control from Toolbox.
<asp:DataList ID="dlstudent" runat="server" DataKeyField="STUDENT_ID">
            <HeaderStyle Font-Bold="True" BorderColor="Black" />
            <HeaderTemplate>
             <table border="1"><tr style="background-color:Blue;color:White;">
             <td><b>Student Name</b> </td>
             <td>Student Address</td>
             <td>Student Class</td>
             </tr>
            </HeaderTemplate>
            <ItemTemplate>
            <tr style="font-style:italic;">
              <td align="center"><asp:Label ID="lblname" runat="server"
                    Text='<%# Eval("STUDENT_NAME") %>'></asp:Label></td>
                <td align="center"><asp:Label ID="lbladdress" runat="server"
                    Text='<%# Eval("STUDENT_ADDRESS") %>'></asp:Label></td>
               <td align="center"><asp:Label ID="lblclass" runat="server"
                    Text='<%# Eval("STUDENT_CLASS") %>'></asp:Label></td>
                  </tr>
            </ItemTemplate>
          
                       </asp:DataList>
                        <table border="1">
             <tr style="color:Red;"><td>
                 <asp:Label ID="lblmessage" runat="server" Text=""></asp:Label></td></tr></table>

Now go to .aspx.cs page.
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["connection"].ToString());
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            bindDatalist();
        }
    }
    private void bindDatalist()
    {
        try
        {
            SqlCommand cmd = new SqlCommand("DISPLAY_DATA", con);
            con.Open();
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter adp = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            adp.Fill(dt);
            if (dt.Rows.Count > 0)
            {
                dlstudent.DataSource = dt;
                dlstudent.DataBind();
                con.Close();
            }
            else
            {
                dlstudent.DataSource = null;
                dlstudent.DataBind();
                lblmessage.Text = "No Data Found";
            }
        }
        catch (Exception ex)
        {
        }

    }

In VB
Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("connection").ToString())
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If con.State = ConnectionState.Closed Then
            con.Open()
        End If
        If Not IsPostBack Then
            BindDatalist()
        End If
    End Sub
    Private Sub BindDatalist()
        Try
            Dim cmd As New SqlCommand("DISPLAY_DATA", con)
            Dim adp As New SqlDataAdapter(cmd)
            cmd.CommandType = CommandType.StoredProcedure
            Dim dt As New DataTable()
            adp.Fill(dt)
            If dt.Rows.Count > 0 Then
                dlstudent.DataSource = dt
                dlstudent.DataBind()
            Else
                dlstudent.DataSource = Nothing
                dlstudent.DataBind()
                lblmessage.Text = "No Data Found"
            End If
        Catch ex As Exception

        End Try
    End Sub

Run the project and check the result.
Related Articles on Datalist

Ø  How to Bind,Edit, Delete and Update in Datalist in Asp.net?

Ø  How to Bind Datalist control using Sqldataadapter, Datatableand Query in Asp.net?

Is it helpful?

If yes post your comment to admire my work. You can like me on Facebook, Google+, Linkedin and Twitter via hit on Follow us Button and also can get update follow by Email.

No comments:

Post a Comment