Slide 13.2: Data binding syntax: <%#  %> (cont.)
Slide 13.4: Database connection (cont.)
Home

Database Connection

ADO.NET (ActiveX Data Objects) is part of the Microsoft .NET Framework. It is used to handle data access. With ADO.NET you can work with databases.

What Is ADO.NET? Create a Database Connection
The Grocery database will be use in this example:


First, import the following namespaces: 
   <%@ Import Namespace="System" %>
   <%@ Import Namespace="System.Data.OleDb" %>
We need these namespaces to work with Microsoft Access and other OLE DB (Object Linking and Embedding, Database, sometimes written as OLEDB or OLE-DB) database providers. A dbconn variable is created as a new OleDbConnection class with a connection string which identifies the OLE DB provider and the location of the database. Then we open the database connection: 

<%@ Import Namespace="System" %>
<%@ Import Namespace="System.Data.OleDb" %>

<script language="C#" runat="server">
 protected void Select_Click( object sender, EventArgs e ) {
  OleDbConnection dbconn = new OleDbConnection(
    @"Provider=Microsoft.ACE.OleDb.12.0;Data Source=" +
    Server.MapPath( "Access\\Grocery.mdb" ) );
  dbconn.Open( );
 }
</script>

†A related example can be found at W3Schools.


demo_dbconn.aspx
Web 
<%@ Page Language="C#" %>
<%@ Import Namespace="System" %>
<%@ Import Namespace="System.Data.OleDb" %>

<script language="C#" runat="server">
 protected void Select_Click( object sender, EventArgs e ) {
  OleDbConnection dbconn = new OleDbConnection(
    @"Provider=Microsoft.ACE.OleDb.12.0;Data Source=" +
    Server.MapPath( "Access\\Grocery.mdb" ) );
  dbconn.Open( );

  String sql;
  sql  = "SELECT c.cid, p.name FROM coupons c, coupon_product cp,";
  sql += " products p WHERE cp.pid = p.pid AND c.cid = cp.cid AND";
  sql += " c.cid IN ( SELECT DISTINCT c1.cid FROM coupons c1,";
  sql += " coupon_product cp1, products p1 WHERE p1.name LIKE @pName";
  sql += " AND p1.pid = cp1.pid AND cp1.cid = c1.cid )";
  sql += " ORDER BY c.cid";

  OleDbCommand dbcmd = new OleDbCommand( sql, dbconn );
  dbcmd.Parameters.AddWithValue( "@pName", "%"+pName.Text+"%" );
  OleDbDataReader dbread = dbcmd.ExecuteReader( );
  coupons.DataSource = dbread;
  coupons.DataBind( );
  dbread.Close( );
  dbcmd.Dispose( );
  dbconn.Close( );
 }
</script>

<html><body>
 <form runat="server">
  <center><h3>Finding Coupons</h3></center><hr />
   A product:
   <asp:TextBox ID="pName" runat="server" />
   <asp:Button ID="Select" runat="server" Text="Find coupons"
     OnClick="Select_Click" />
  <asp:Repeater id="coupons" runat="server">
   <HeaderTemplate>
    <table border="0">
   </HeaderTemplate>
   <ItemTemplate>
    <tr>
     <td>Coupon ID: </td>
     <td><%#DataBinder.Eval(Container.DataItem,"cid")%></td>
     <td>⇒ Product: </td>
     <td><%#DataBinder.Eval(Container.DataItem,"name")%></td>
    </tr>
   </ItemTemplate>
   <FooterTemplate>
    </table>
   </FooterTemplate>
  </asp:Repeater>
 </form>
</body></html>