Slide 13.4: Database connection (cont.)
Slide 13.6: Server controls
Home

Database Connection (Cont.)

Bind to a Repeater Control
Then we bind the DataReader to a Repeater control:

view source
print?
01<%@ Import Namespace="System" %>
02<%@ Import Namespace="System.Data.OleDb" %>
03 
04<script language="C#" runat="server">
05 protected void Select_Click( object sender, EventArgs e ) {
06  OleDbConnection dbconn = new OleDbConnection(
07    @"Provider=Microsoft.ACE.OleDb.12.0;Data Source=" +
08    Server.MapPath( "Access\\Grocery.mdb" ) );
09  dbconn.Open( );
10  String sql;
11  sql  = "SELECT c.cid, p.name FROM coupons c, coupon_product cp,";
12  sql += " products p WHERE cp.pid = p.pid AND c.cid = cp.cid AND";
13  sql += " c.cid IN ( SELECT DISTINCT c1.cid FROM coupons c1,";
14  sql += " coupon_product cp1, products p1 WHERE p1.name LIKE @pName";
15  sql += " AND p1.pid = cp1.pid AND cp1.cid = c1.cid )";
16  sql += " ORDER BY c.cid";
17  OleDbCommand dbcmd = new OleDbCommand( sql, dbconn );
18  dbcmd.Parameters.AddWithValue( "@pName", "%"+pName.Text+"%" );
19  var dbread = dbcmd.ExecuteReader( );
20  coupons.DataSource = dbread;
21  coupons.DataBind( );
22 }
23</script>

Close the Database Connection
Always close both the DataReader and database connection after access to the database is no longer required:

view source
print?
01<%@ Import Namespace="System" %>
02<%@ Import Namespace="System.Data.OleDb" %>
03 
04<script language="C#" runat="server">
05 protected void Select_Click( object sender, EventArgs e ) {
06  OleDbConnection dbconn = new OleDbConnection(
07    @"Provider=Microsoft.ACE.OleDb.12.0;Data Source=" +
08    Server.MapPath( "Access\\Grocery.mdb" ) );
09  dbconn.Open( );
10  String sql;
11  sql  = "SELECT c.cid, p.name FROM coupons c, coupon_product cp,";
12  sql += " products p WHERE cp.pid = p.pid AND c.cid = cp.cid AND";
13  sql += " c.cid IN ( SELECT DISTINCT c1.cid FROM coupons c1,";
14  sql += " coupon_product cp1, products p1 WHERE p1.name LIKE @pName";
15  sql += " AND p1.pid = cp1.pid AND cp1.cid = c1.cid )";
16  sql += " ORDER BY c.cid";
17  OleDbCommand dbcmd = new OleDbCommand( sql, dbconn );
18  dbcmd.Parameters.AddWithValue( "@pName", "%"+pName.Text+"%" );
19  var dbread = dbcmd.ExecuteReader( );
20  coupons.DataSource = dbread;
21  coupons.DataBind( );
22  dbread.Close( )
23  dbcmd.Dispose( );
24  dbconn.Close( )
25 }
26</script>

†A related example can be found at W3Schools.


demo_dbconn.aspx
Web
view source
print?
01<%@ Page Language="C#" %>
02<%@ Import Namespace="System" %>
03<%@ Import Namespace="System.Data.OleDb" %>
04 
05<script language="C#" runat="server">
06 protected void Select_Click( object sender, EventArgs e ) {
07  OleDbConnection dbconn = new OleDbConnection(
08    @"Provider=Microsoft.ACE.OleDb.12.0;Data Source=" +
09    Server.MapPath( "Access\\Grocery.mdb" ) );
10  dbconn.Open( );
11 
12  String sql;
13  sql  = "SELECT c.cid, p.name FROM coupons c, coupon_product cp,";
14  sql += " products p WHERE cp.pid = p.pid AND c.cid = cp.cid AND";
15  sql += " c.cid IN ( SELECT DISTINCT c1.cid FROM coupons c1,";
16  sql += " coupon_product cp1, products p1 WHERE p1.name LIKE @pName";
17  sql += " AND p1.pid = cp1.pid AND cp1.cid = c1.cid )";
18  sql += " ORDER BY c.cid";
19 
20  OleDbCommand dbcmd = new OleDbCommand( sql, dbconn );
21  dbcmd.Parameters.AddWithValue( "@pName", "%"+pName.Text+"%" );
22  var dbread = dbcmd.ExecuteReader( );
23  coupons.DataSource = dbread;
24  coupons.DataBind( );
25  dbread.Close( );
26  dbcmd.Dispose( );
27  dbconn.Close( );
28 }
29</script>
30 
31<html><body>
32 <form runat="server">
33  <center><h3>Finding Coupons</h3></center><hr />
34   A product:
35   <asp:TextBox ID="pName" runat="server" />
36   <asp:Button ID="Select" runat="server" Text="Find coupons"
37     OnClick="Select_Click" />
38  <asp:Repeater id="coupons" runat="server">
39   <HeaderTemplate>
40    <table border="0">
41   </HeaderTemplate>
42   <ItemTemplate>
43    <tr>
44     <td>Coupon ID: </td>
45     <td><%#DataBinder.Eval(Container.DataItem,"cid")%></td>
46     <td>⇒ Product: </td>
47     <td><%#DataBinder.Eval(Container.DataItem,"name")%></td>
48    </tr>
49   </ItemTemplate>
50   <FooterTemplate>
51    </table>
52   </FooterTemplate>
53  </asp:Repeater>
54 </form>
55</body></html>