How to Bind Data in List Control Using SqlDataReader in Asp.Net

SqlDataReader is nothing but we can read data from Database and bind in DataTable and read in Controls. SqlDataReader read data from SqlCommand and read based on condition what we pass in code through. Here we will create ListBox and pass data using SqlDataReader control. So let’s Start how to achieve this functionality.

Step 1: Create table in Sql Server.

CREATE TABLE [dbo].[publishers](

                [pub_id] [int] IDENTITY(1,1) NOT NULL,

                [pub_name] [varchar](50) NOT NULL,

                [state] [varchar](50) NOT NULL,

                [country] [varchar](50) NOT NULL,

                [Amounts] [bigint] NULL,

 CONSTRAINT [PK_publishers] PRIMARY KEY CLUSTERED

(

                [pub_id] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

Step 2: Now Drag and Drop ListBox and Button Control.

<div>

<asp:Button ID="Button1"runat="server"OnClick="Button1_Click"Text="Click"Width="146px" />

     <br /><br />

<asp:Label ID="Label1" runat="server"></asp:Label>

     <br /><br />

<asp:ListBox ID="ListBox1" runat="server" Height="132px" Width="153px"></asp:ListBox>

</div>

For Connection String we have to Read Connection from Database server we can create Connection String in Web.Config File and create connection in Webpage.

Web.Config

<connectionStrings>

                <add connectionString="Data Source=YourServerName;Initial Catalog=DatabaseName;Integrated Security=True" providerName="System.Data.SqlClient" name="DBCS"/>

</connectionStrings>

Now use this connection string in webpage using ConfigurationManager like.

string connectionString = ConfigurationManager.ConnectionStrings["DBCS"].ToString();

Step 3: Now Generate Button Click event and code for read data from Database and bind in ListBox.

protected void Button1_Click(object sender, System.EventArgs e)

        {

            SqlConnection connection = new SqlConnection(connectionString);

            string sql = "select pub_id,pub_name from publishers";

            try

            {

                connection.Open();

                SqlCommand cmd = new SqlCommand(sql, connection);

                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())

                {

                    ListBox1.Items.Add(reader.GetValue(0) + " – " + reader.GetValue(1));

                }

                reader.Close();

                connection.Close();

            }

            catch (Exception ex)

            {

                Label1.Text = "Error in SqlDataReader " + ex.ToString();

            }

        }

Step 4: Now check in your Browser and Click on Button to see output.

Author : Umesh Patel

Related Posts