How to Bind DataList using SqlDataAdapter and DataTable in asp.net (C#,VB.Net)

Introduction: In previous articles i explained Bind,Save,Edit,Update,Cancel,Delete,Paging example in GridView and Bind,save,edit,update,delete records from DataList and Bind, edit, update and delete data in Repeater and  How to bind gridview using SqlDataAdapter , DataSet and Stored procedure in Asp.net and How to bind gridview using SqlDataAdapter, DataSet and query and Pass parameter to stored procedure using SqlDataAdapter and check login and  How to bind gridview using SqlDataAdapter, SqlCommand, DataSet and Stored procedure .
In this example I am going to explain with example How to Bind DataList data control with data from Sql server database table using SqlDataAdapter and DataTable   in asp.net using both C# and VB.Net languages.

Bind DataList with Sql server database table in asp.net
Click on image to see the large version
Implementation: Let's create an asp.net sample website to see it in action.
  •  First of all create a Database e.g. "MyDataBase" and a table under that DataBase in Sql Server and name it "EMPLOYEE" as  shown in figure: 
    Binding DataList example in asp.net
    Click on image to enlarge

 Note: EMP_ID column is set to Primary key and Identity specification is set to yes with Identity increment and Identity seed equal to 1. Insert some data in this table that you  want to show in the DataList.
  • Now in web.config file add the connection string under <configuration> tag as : 
<connectionStrings>

    <add name="conStr" connectionString="Data Source=LALIT;Initial Catalog=MyDataBase;Integrated Security=True"/>

  </connectionStrings>

Note: Replace the Data Source and Initial Catalog(i.e. Database name) as per your application.

Source Code:
  • In the design page (.aspx) place a DataList control from the Data Category of Visual Studio toolbox and create it as:
<fieldset style="width:250px;">
            <legend>Bind DataList example in asp.net</legend>
            <asp:DataList ID="dlEmployee" runat="server" CellPadding="4" ForeColor="#333333" RepeatColumns="2">
                <AlternatingItemStyle BackColor="White" ForeColor="#284775" />
                <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                <ItemStyle BackColor="#F7F6F3" ForeColor="#333333" />
            <ItemTemplate>
                <table style="width:220px;" cellspacing="2" cellpadding="2">
                      <tr>
                        <td><b>Employee Name:</b> <%#DataBinder.Eval(Container.DataItem, "EMP_NAME")%></td>
                      </tr>
                      <tr>
                        <td><b>Department:</b> <%#DataBinder.Eval(Container.DataItem, "DEPT")%></td>
                      </tr>
                      <tr>
                        <td><b>Salary:</b> <%#DataBinder.Eval(Container.DataItem, "SALARY")%></td>
                      </tr>
                </table>
            </ItemTemplate>
                <SelectedItemStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
        </asp:DataList>
        </fieldset>

C#.Net Code to Bind DataList using SqlDataAdadpter and DataTable

First include the following required namespaces:

using System.Data;
using System.Data.SqlClient;
using System.Configuration;

then write the code on page load event as:

protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            BindDataList();
        }
    }
    protected void BindDataList()
    {
        DataTable dt = new DataTable();
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString);
        try
        {          
            SqlDataAdapter adp = new SqlDataAdapter("select * from EMPLOYEE", con);           
            adp.Fill(dt);
            dlEmployee.DataSource = dt;
            dlEmployee.DataBind();
        }
        catch (Exception ex)
        {
            Response.Write("Error occured: " + ex.Message.ToString());
        }
        finally
        {
            dt.Clear();
            dt.Dispose();
            con.Close();           
        }      
    }

VB.Net Code to Bind DataList using SqlDataAdadpter and DataTable

First import following 3 namespaces:

Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

Then write the code on page load event as:

  Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
        If Not Page.IsPostBack Then
            BindDataList()
        End If
    End Sub
    Protected Sub BindDataList()
        Dim dt As New DataTable()
        Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("conStr").ConnectionString)
        Try
            Dim adp As New SqlDataAdapter("select * from EMPLOYEE", con)
            adp.Fill(dt)
            dlEmployee.DataSource = dt
            dlEmployee.DataBind()
        Catch ex As Exception
            Response.Write("Error occured: " & ex.Message.ToString())
        Finally
            dt.Clear()
            dt.Dispose()
            con.Close()
        End Try
    End Sub


Now over to you:
"If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linked in and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates."
Previous
Next Post »

5 comments

Click here for comments
Anonymous
admin
September 11, 2013 ×

Thank You Sir.. It is very useful. Helped me a lot:-)

Reply
avatar
September 11, 2013 ×

i am glad you like my post..keep reading..

Reply
avatar
Anonymous
admin
October 10, 2013 ×

i want to pass a query string with in anchor tag,when anyone click on category name then show description and image show of that category with same page
i am using datalist control

Reply
avatar
Unknown
admin
October 19, 2013 ×

Thanks A lots sir . your Article superb for the beginner . one of the one issue of the project i did use data list along with state name and particular city name what can i do

Reply
avatar
October 19, 2013 ×

Hello Yogesh Chavan..what is your exact requirement? let me know i will help you..

Reply
avatar

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily..