Need to bind a web control to an OleDb data source in your C# web application, here’s the magic. In this case I am binding a ListBox control with an ID of ListBox1 to the ProductName field in the Products table of our old friend the Northwind database.

These are the using statements that you need to apply including the ConfigurationManager to get your web.config connection string conveniently

[codesyntax lang="csharp"]

using System.Data;
using System.Data.OleDb;
using System.Configuration;

[/codesyntax]

Here is our class that does all the work

[codesyntax lang="csharp"]

protected void DataSetFromOleDbDemo()
    {
      string strSQL = "SELECT * FROM Products WHERE CategoryID = 1";

      var conString = ConfigurationManager.ConnectionStrings["Northwind"];
      string Conn = conString.ConnectionString;

      try
      {
        using (OleDbDataAdapter adapter = new OleDbDataAdapter(strSQL, Conn))
        {
          DataSet ds = new DataSet();
          adapter.Fill(ds, "ProductInfo");

          foreach (DataRow dr in ds.Tables["ProductInfo"].Rows)
          {
            ListBox1.Items.Add(dr["ProductName"].ToString());
          }
        }
      }
      catch (Exception)
      {

        throw;
      }
    }

[/codesyntax]

And your OleDb connection string to put in the connectionStrings section of your web.config is as follows

[codesyntax lang="xml"]

<connectionStrings>
    <add name="Northwind" connectionString="Provider=SQLOLEDB;server=YOURSERVER;database=Northwind;uid=USERNAME;password=PASSWORD;" />
</connectionStrings>

[/codesyntax]

 

 

These are the minimum using directives to add at the top of the code behind for building an HTML table from a SQL Server to a SqlDataReader concisely.

[codesyntax lang="csharp"]

using System.Web.UI.HtmlControls;
using System.Data;
using System.Data.SqlClient;

[/codesyntax]

Your table in the aspx file can be as simple as

[codesyntax lang="html4strict"]

<table id="Products" runat="server"></table>

[/codesyntax]

And the code behind that does the work,  taking the value from a dropdown list control called Category, and retrieving data from Northwind is

[codesyntax lang="csharp"]

using(SqlConnection cnn = new SqlConnection("Data Source=YOURSERVER; Database=Northwind; UID=YOURDBUSERID; PWd=YOURDBPASSWORD"))
      {
        SqlCommand cmd;
        SqlDataReader rdr;

          HtmlTableRow row = new HtmlTableRow();

          HtmlTableCell cell = new HtmlTableCell("th");
          cell.InnerText = "Product ID";
          row.Cells.Add(cell);

          cell = new HtmlTableCell("th");
          cell.InnerText = "Product Name";
          row.Cells.Add(cell);

          cell = new HtmlTableCell("th");
          cell.InnerText = "Units in stock";
          row.Cells.Add(cell);

          Products.Rows.Add(row);

          cmd = new SqlCommand("SELECT ProductID, ProductName, UnitsInStock FROM Products WHERE CategoryID = "
            + Category.SelectedValue, cnn);
          cnn.Open();
          rdr = cmd.ExecuteReader();

          while (rdr.Read())
          {
            row = new HtmlTableRow();

            cell = new HtmlTableCell();
            cell.InnerText = rdr["ProductID"].ToString();
            row.Cells.Add(cell);

            cell = new HtmlTableCell();
            cell.InnerText = rdr["ProductName"].ToString();
            row.Cells.Add(cell);

            cell = new HtmlTableCell();
            cell.InnerText = rdr["UnitsInStock"].ToString();
            row.Cells.Add(cell);

            Products.Rows.Add(row);

          }
        }

[/codesyntax]

Remember to add the usual using directives at the start of the codebehind

[codesyntax lang="csharp"]

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

[/codesyntax]

For a DropDownList on your ASP.NET page with an ID of Category such a

[codesyntax lang="html4strict"]

<asp:DropDownList ID="Category" runat="server">
</asp:DropDownList>

[/codesyntax]

The following in your code behind will bind data from an example Northwind database on SQL Server to the text and values of the DropDownList

[codesyntax lang="csharp"]

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;

namespace AppDevStuff
{
  public partial class _Default : System.Web.UI.Page
  {
    protected void Page_Load(object sender, EventArgs e)
    {
      using(SqlConnection cnn = new SqlConnection("Data Source=YOURSERVERNAME; Database=Northwind; UID=YOURUSERNAME; PWd=YOURPASSWORD"))
      {
        SqlCommand cmd;
        SqlDataReader rdr;
        if (!Page.IsPostBack)
        {
          cmd = new SqlCommand("SELECT CategoryID, CategoryName FROM Categories ORDER BY CategoryName;", cnn);
          cnn.Open();
          rdr = cmd.ExecuteReader();

          Category.DataSource = rdr;
          Category.DataTextField = "CategoryName";
          Category.DataValueField = "CategoryID";
          Category.DataBind();
        }
      }
    }
  }
}

[/codesyntax]