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.

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: 

  

  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."

• Tweet
• Share
• Share
• Share

Related Post