aspnet03-02.htm

tutorialssss.... for everybody!!!!!!!!!!!!!!!!!!!

    name="SqlBooksConnection" 
    connectionString="Provider=Microsoft.Jet.OLEDB.4.0; 
                      Data Source=c:\eCommerce\Databases\BooksDB.mdb" 
  />
  </connectionStrings>
</b>
&lt;/configuration&gt;
</pre>
<div class="listing"><b>Listing 3-10.</b> Coding <span class="code">connectionStrings</span> entries in the <span class="code">web.config</span> file.</div>
<p>A <span class="code">&lt;connectionStrings&gt;</span> specification is made 
through an <span class="code"><b>&lt;add/&gt;</b></span> entry. A 
programmer-supplied <span class="code"><b>name</b></span> is given for the entry, 
and a <span class="code"><b>connectionString</b></span> gives the connection 
information.</p>
<p>For an AccessDataSource's <span class="code">DataFile</span> attribute鈥攈ere 
named <span class="code"><b>AccessBooksConnection</b></span>鈥攖he only required 
information is the physical path to the database. For an SqlDataSource鈥攈ere 
named <span class="code"><b>SqlBooksConnection</b></span>鈥攖he connection string 
includes both a <span class="code">Provider</span> and <span class="code">Data 
Source</span> string, the latter giving the physical path to the database.</p>
<p class="head3">Using a Database Connection String</p>
<p>After a connection string entry is made in the <span class="code">web.config</span> file, it can be used in a data source control 
through reference to its <span class="code">name</span>. In the following listing, 
an AccessDataSource and an SqlDataSource point to the previous entries.</p><pre class="divcode">&lt;asp:AccessDataSource id="BookSource" Runat="Server"
  DataFile="<b>&lt;%$ connectionStrings: AccessBooksConnection %&gt;</b>"
  SelectCommand="SELECT BookID, BookType, BookTitle, BookPrice FROM Books
                 WHERE BookType = 'Database'
                 ORDER BY BookTitle"
/&gt;

&lt;asp:SqlDataSource id="BookSource" Runat="Server" 
  ProviderName="System.Data.OleDb"
  ConnectionString="<b>&lt;%$ connectionStrings: SqlBooksConnection %&gt;</b>"
  SelectCommand="SELECT BookID, BookType, BookTitle, BookPrice FROM Books
                 WHERE BookType = 'Database'
                 ORDER BY BookTitle"
/&gt;
</pre>
<div class="listing"><b>Listing 3-11. </b>Using connection strings to access 
databases.</div>
<p>Note the format of the references. They must be enclosed inside <span class="code">&lt;%$</span> and <span class="code">%&gt;</span> symbols. The <span class="code">connectionStrings:</span> parameter refers to the <span class="code">&lt;connectionStrings&gt;</span> section of the <span class="code">web.config</span> file; <span class="code">AccessBooksConnection</span> 
and <span class="code">SqlBooksConnection</span> refer to the <span class="code">names</span> associated with the connection strings coded there.</p>
<p>Now, any time a database changes location or has its name changed, this 
change needs to be recoded only one time in the <span class="code">&lt;connectionStrings&gt;</span> section of the <span class="code">web.config</span> file. Thereafter, the change is automatically 
reflected in all occurences of a data source control on all pages of a Web 
site.</p>
<p>For purposes of this tutorial, a <span class="code">&lt;connectionStrings&gt;</span> entry in the <span class="code">web.config</span> file is <i>not</i> assumed. All connection strings 
are coded as physical or relative paths in the data source controls. This 
practice simply reinforces code learning; in a production environment, 
centralizing connection-string coding is certainly preferred.</p>
<p class="head2">Binding to a Display Control</p>
<p>There are numerous ways to display information extracted from a database. The 
easiest way to display recordsets, however, is through several information 
display controls available through ASP.NET. One of the handiest and easy-to-use 
of these controls is the <span class="code">&lt;asp:GridView&gt; </span>control 
introduced earlier. The way in which it binds to a data source control is 
similar to most other display controls.</p>
<p>A GridView in its default configuration automatically displays the rows and 
columns of a recordset returned from a data source control. It even uses the 
field names in a database table as column headings for its table display. A 
GridView binds to a data source by giving the <span class="code">id</span> of the 
data source in its <span class="code">DataSourceID</span> property. To bind to the 
AccessDataSource described above (<span class="code">id="BookSource"</span>), 
GridView coding is as simple as that shown below.</p><pre class="divcode">&lt;asp:GridView <b>DataSourceID="BookSource"</b> Runat="Server"/&gt;
</pre>
<div class="listing"><b>Listing 3-12. </b>Coding for a GridView control bound to 
an AccessDataSource.</div>
<p>A display control binds to a data source control during the page-load 
process. Therefore, the display control is already populated with returned 
information when the page opens. The resulting display is shown below.</p>
<div class="page"><br>
<div>
<table id="BookGrid" style="border-collapse: collapse;" border="1" cellspacing="0" rules="all">
  <tbody>
  <tr>
    <th scope="col">BookID</th>
    <th scope="col">BookType</th>
    <th scope="col">BookTitle</th>
    <th scope="col">BookPrice</th></tr>
  <tr>
    <td>DB444</td>
    <td>Database</td>
    <td>Access Database Design</td>
    <td>34.95</td></tr>
  <tr>
    <td>DB333</td>
    <td>Database</td>
    <td>Database Processing</td>
    <td>136.65</td></tr>
  <tr>
    <td>DB222</td>
    <td>Database</td>
    <td>Databases in Depth</td>
    <td>29.95</td></tr>
  <tr>
    <td>DB111</td>
    <td>Database</td>
    <td>Oracle Database</td>
    <td>69.99</td></tr>
  <tr>
    <td>DB555</td>
    <td>Database</td>
    <td>SQL Server 2005</td>
    <td>29.99</td></tr></tbody></table></div><br></div>
<div class="figure"><b>Figure 3-11.</b> Page output produced by AccessDataSource 
and GridView controls.</div>
<p>You are likely to want more control over the aesthetics of the display than 
what is provided by the default GridView, although the default view is 
sufficient to test database connections. In later tutorials you learn the 
formatting tricks for the GridView.</p>
<p class="head2">Data Binding with Script</p>
<p>A Web site can be data driven in a second way in addition to reporting 
contents of external data sources. It can respond to user requests. Controls can 
be placed on the page to solicit user preferences about what information to 
display and how to display it. In order to demonstrate the ease with which users 
can become active in selecting page content, the following rewrite of the 
previous application provides six buttons for selecting different table 
displays, each of which is produced dynamically in response to the choice.</p>
<div class="page" style="height: 260px;">
<h3>Select a book type:</h3><input style="width: 80px;" value="Database" name="ctl01" type="submit"> <input style="width: 80px;" value="Graphics" name="ctl02" type="submit"> <input style="width: 80px;" value="Hardware" name="ctl03" type="submit"> <br><input style="width: 80px;" value="Systems" name="ctl04" type="submit"> <input style="width: 80px;" value="Software" name="ctl05" type="submit"> <input style="width: 80px;" value="Web" name="ctl06" type="submit"> <br><br>
<div></div></div>
<div class="figure"><b>Figure 3-12.</b> GridView output governed by user 
choices.</div>
<p>In this case, a script is needed to respond to user clicks on the buttons. As 
shown in the listing below, subprogram <span class="code">Display_Type</span> is 
called to dynamically create an appropriate SQL <span class="code">SELECT</span> 
statement to select all records of the chosen type. This <span class="code">SELECT</span> statement is dynamically assigned to the 
AccessDataSource to return these records for display in a GridView.</p><pre class="divcode"><span class="script">&lt;SCRIPT Runat="Server"&gt;

Sub Show_Type (Src As Object, Args As EventArgs)

  Dim SQLString As String
  SQLString = "SELECT BookID, BookType, BookTitle, BookPrice FROM Books " &amp; _
              "WHERE BookType = '" &amp; Src.Text &amp; "'"
  BookSource.SelectCommand = SQLString

End Sub

&lt;/SCRIPT&gt;
</span>
&lt;form Runat="Server"&gt;

&lt;h3&gt;Select a book type:&lt;/h3&gt;

&lt;asp:Button Text="Database" Width="80px" OnClick="Show_Type" Runat="Server"/&gt;
&lt;asp:Button Text="Graphics" Width="80px" OnClick="Show_Type" Runat="Server"/&gt;
&lt;asp:Button Text="Hardware" Width="80px" OnClick="Show_Type" Runat="Server"/&gt;
&lt;br/&gt;
&lt;asp:Button Text="Systems"  Width="80px" OnClick="Show_Type" Runat="Server"/&gt;
&lt;asp:Button Text="Software" Width="80px" OnClick="Show_Type" Runat="Server"/&gt;
&lt;asp:Button Text="Web"      Width="80px" OnClick="Show_Type" Runat="Server"/&gt;

&lt;asp:AccessDataSource id="BookSource" Runat="Server" 
  DataFile="../Databases/BooksDB.mdb"/&gt;

&lt;asp:GridView DataSourceID="BookSource" Runat="Server"/&gt;

&lt;/form&gt;
</pre>
<div class="listing"><b>Listing 3-13.</b> Code for GridView output governed by 
user choices.</div>
<p>The AccessDataSource includes the required <span class="code">DataFile</span> 
property to point to the database. However, it does <i>not</i> include a <span class="code">SelectCommand </span>to retrieve a set of records for display. In 
this case, records are chosen for display by clicking buttons, and the <span class="code">SelectCommand</span> is composed in the associated script.</p>
<p>User choices are effected by assigning the six <span class="code">BookType</span> field values in the database as the <span class="code">Text</span> properties of the buttons. The buttons' labels, then, 
match the book-type values found in the database. Recall that when a button 
calls a subprogram, it identifies itself through the first item in the argument 
list, through argument <span class="code">Src</span> in the example script. This 
button's <span class="code">Text</span> property is given by the reference <span class="code">Src.Text</span>, which translates as one of the <span class="code">BookType</span> values in the database. Therefore, this <span class="code">Src.Text</span> property can be plugged into a <span class="code">SELECT</span> statement to retrieve records of this type.</p>
<p>Assume, for instance, the button labeled "Graphics" is clicked. Therefore, 
the subprogram reference to <span class="code">Src.Text</span> produces the value 
<span class="code">"Graphics"</span>. Since a <span class="code">SELECT</span> 
statement is composed with the following declaration and assignment,</p><pre class="divscript">SQLString = "SELECT BookID, BookType, BookTitle, BookPrice FROM Books " &amp; _
            "WHERE BookType = '" &amp; <b>Src.Text</b> &amp; "'"
</pre>
<div class="listing"><b>Listing 3-14.</b> A scripted <span class="code">SELECT</span> statement which integrates a passed value.</div>
<p>when <span class="code">Src.Text</span> (<span class="code">"Graphics"</span>) is 
concatenated inside this string the following statement is produced:</p><pre class="divscript">SELECT BookID, BookType, BookTitle, BookPrice FROM Books 
WHERE BookType = '<b>Graphics</b>'
</pre>
<div class="listing"><b>Listing 3-15.</b> A <span class="code">SELECT</span> 
statement with a substituted passed value.</div>
<p>Now it is a matter of assigning this statement to the <span class="code">SelectCommand</span> property of the AccessDataSource. This 
assignment is done programmatically with the statement,</p><pre class="divscript">BookSource.<b>SelectCommand</b> = SQLString
</pre>
<div class="listing"><b>Listing 3-16.</b> Assigning an SQL command to a data 
source control.</div>
<p>The <span class="code">SelectCommand</span> property of the control with <span class="code">id="BookSource"</span> (the AccessDataSource) is assigned the <span class="code">SELECT</span> statement stored in variable <span class="code">SQLString</span>. Immediately upon this assignment, the newly 
composed SQL command is issued, and the AccessDataSource returns this set of 
records from the database. Its binding with the GridView automatically produces 
a new table display.</p>
<p>The above examples just scratch the surface of Web-based data access. 
Throughout these tutorials additional server controls demonstrate how to 
retrieve data sources and display their content to produce dynamic information 
for changing user needs. Working with databases is an exercise in using the SQL 
language to compose <span class="code">SELECT</span>, <span class="code">INSERT</span>, <span class="code">UPDATE</span>, <span class="code">DELETE</span>, and other SQL commands to carry out database 
processing. It is assumed you have basic facility with this language. If you 
need a review of SQL syntax, check the appendix to these 
tutorials.</p><br></div>
<div><input id="__EVENTVALIDATION" value="/wEWCALamtmOCgLbhM+CAgKiwImNCwKfwImNCwKgwImNCwKlwImNCwKmwImNCwKjwImNCxWReQOWR90P4IFxmA+ZYp0koypi" name="__EVENTVALIDATION" type="hidden"> </div></form></body></html>