Slide 13.3: Database connection
Slide 13.5: Database connection (cont.)
Home

Database Connection (Cont.)

Create a Database Command
To specify the records to retrieve from the database, we will create a dbcmd variable as a new OleDbCommand class. The OleDbCommand class is for issuing SQL queries against database tables: 

<%@ 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 );
 }
</script>

Create a DataReader
The AddWithValue(String, Object) method adds a value to the end of the OleDbParameterCollection and the OleDbDataReader class is used to read a stream of records from a data source. A DataReader is created by calling the ExecuteReader method of the OleDbCommand object: 

<%@ 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+"%" );
  var dbread = dbcmd.ExecuteReader( );
 }
</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+"%" );
  var 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>