ASP.NET

1. ASP.NET Database Connection

2. ASP.NET Framework • The ASP.NET framework includes the ADO.NET data access technology for working with databases and other OLE DB data sources • ASP.NET pages that connect to databases must gain access to the system classes that provide data access functionality. For working with Microsoft Access and other databases that use OLE DB providers, the page must import the System.Data.OleDb namespace

3. Database Namespace Directive • System.Data.OleDb namespace can be imported by including the following directive at the top of the page: <%@ Import Namespace="System.Data.OleDb" %> • This namespace includes the three basic classes needed to work with Access databases: • OleDbConnection - for connecting to a database • OleDbCommand - for issuing SQL queries against database tables • OleDbDataReader - for access to recordsets retrieved through SQL queries

4. Product Table Field Name Data Type Field Size Example Data ItemNumber Text 6 OS1111 ItemType Text 20 Operating System ItemSupplier Text 20 Microsoft ItemName Text 50 Windows XP ItemDescription Memo  Windows XP is ItemPrice Currency  $149.95 ItemQuantity Number Long Integer 20

5. Opening a DB Connection • A connection to a database is made by creating an OleDbConnection object that can be used to access the database. • After the connection is established then, this connection's Open() method is used to open the database.

6. Opening a DB Connection • DimDBConnectionAs OleDbConnectionDBConnection = NewOleDbConnection(ConnectionString)DBConnection.Open()     orDimDBConnection = NewOleDbConnection(ConnectionString)DBConnection.Open()

7. Opening a DB Connection • DBConnection is a programmer-supplied reference; the ConnectionString specifies the OLE DB Provider (the database type) and the Data Source (the physical path to the database on the server). • For Access databases, the Provider is: "Microsoft.Jet.OLEDB.4.0" • the Data Source is in the format: "drive:\folder\folder\...\database.mdb". • The two clauses are separated by a semicolor and compose a single string.

8. Opening a DB Connection – Ex. • <%@ Import Namespace="System.Data.OleDb" %><SCRIPT runat="server"> Dim DBConnection As OleDbConnectionSub Page_Load  '-- Open a database connection  DBConnection = New OleDbConnection( _"Provider=Microsoft.Jet.OLEDB.4.0;" & _"DataSource=d:\Databases\eCommerce.mdb")  DBConnection.Open()End Sub</SCRIPT>

9. Selecting Records • Selecting records from a database table to display or to edit is made through the OleDbCommand object. • This selection is normally an SQL command issued through the OleDbCommand object against the database.

10. Selecting Records • DimDBCommandAs OleDbCommandDBCommand = NewOleDbCommand(CommandString, DBConnection)     orDimDBCommand = NewOleDbCommand(CommandString, DBConnection) • DBCommand is a programmer-supplied reference. The CommandString is an SQL statement to access a set of records from the database; the DBConnection is a reference to the database connection opened previously.

11. Selecting Records – Ex. • <%@ Import Namespace="System.Data.OleDb" %><SCRIPT runat="server">Dim DBConnection As OleDbConnectionDim DBCommand As OleDbCommandDim SQLString As StringSub Page_Load  '-- Open a database connection  DBConnection = New OleDbConnection( _"Provider=Microsoft.Jet.OLEDB.4.0;" & _"Data Source=d:\Databases\eCommerce.mdb")  DBConnection.Open()  '-- Create and issue SQL command through the database connection  SQLString = "SELECT * FROM Products"  DBCommand = New OleDbCommand(SQLString, DBConnection)End Sub</SCRIPT>

12. Creating a DataReader • Any of the SQL statement types (SELECT, INSERT, UPDATE, DELETE, and so forth) can be issued through the OleDbCommand object. When issuing a SELECT statement, a set of records (a recordset) is returned from the database and made available to the script. In this case a mechanism is needed for iterating through the recordset and specifying fields of data to be displayed or otherwise processed. • An OleDbDataReader object represents a stream of database records returned from a SELECT statement issued through the OleDbCommand object. A data reader is created by using the ExecuteReader() method of the OleDbCommand object

13. Creating a DataReader • DimDBReaderAs OleDbDataReaderDBReader = DBCommand.ExecuteReader()     orDimDBReader = DBCommand.ExecuteReader() • DBReader is a programmer-supplied reference; DBCommand is a reference to the OleDbCommand object previously created for issuing the SQL statement

14. Creating a DataReader – Ex. • <%@ Import Namespace="System.Data.OleDb" %><SCRIPT runat="server">Dim DBConnection As OleDbConnectionDim DBCommand As OleDbCommandDim DBReader As OleDbDataReaderDim SQLString As StringSub Page_Load  '-- Open a database connection  DBConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" _&"Data Source=d:\Databases\eCommerce.mdb")  DBConnection.Open()  '-- Create and issue an SQL command through the database connection  SQLString = "SELECT * FROM Products"  DBCommand = New OleDbCommand(SQLString, DBConnection)  '-- Create a recordset of selected records from the database  DBReader = DBCommand.ExecuteReader()End Sub</SCRIPT>

15. Accessing through DataReader • An OleDbDataReader represents a stream of database records that are made available to a script one record at a time. It is a forward-only recordset (it cannot be read backwards), and individual records are made available with its Read() method. • When the Read() method is called, two events occur: • First, it returns True if a next record is available in the recordset, or it returns False if no additional records are available. • Second, it advances to the next record if one is available. These pair of events make it very easy to iterate through the records in the OleDbDataReader.

16. Accessing through DataReader • While DBReader.Read()...process database recordEnd While • An OleDbDataReader supplies a complete data record (table row) one at a time. Normally, the interest is in working with individual data fields within the record. In order to specify a data field, the following format is used: DataReader("FieldName") • DataReader is a reference to a previously created OleDbDataReader object. FieldName is the name of a table column in the database

17. DataReader - Ex. • <%@ Import Namespace="System.Data.OleDb" %><SCRIPT runat="server">Dim DBConnection As OleDbConnectionDim DBCommand As OleDbCommandDim DBReader As OleDbDataReaderDim SQLString As StringSub Page_Load  '-- Open a database connection  DBConnection = New OleDbConnection(  "Provider=Microsoft.Jet.OLEDB.4.0;" & _    "Data Source=d:\Databases\eCommerce.mdb")  DBConnection.Open()  '-- Create and issue an SQL command through the database connection  SQLString = "SELECT * FROM Products"  DBCommand = New OleDbCommand(SQLString, DBConnection)  '-- Create a recordset of selected records from the database  DBReader = DBCommand.ExecuteReader()  '-- Read through the recordset one record at a timeWhile DBReader.Read()    ...processDBReader("ItemNumber")    ...processDBReader("ItemName")    ...processDBReader("ItemPrice")End WhileEnd Sub

18. Closing DB Connection • When access to a database is no longer required both the data reader and database connection should be closed. • Each of these task is accomplished with their respective Close() methods, as added below to the continuing script. • '-- Close the reader and database connections  DBReader.Close()  DBConnection.Close()

19. Binding DataReader to a Control • Under ASP.NET a typical method of working with a data reader is to bind it to one of the listing controls: asp:Repeater, asp:DataList, or asp:DataGrid. In this case it is not necessary to iterate through the records in the data reader with a While...End While loop. Instead, the data source is bound to the control. • The following script binds the data reader to an asp:DataGrid control which has the id value of MyDataGrid: '-- Bind the recordset to a controlMyDataGrid.DataSource = DBReaderMyDataGrid.DataBind() <asp:DataGrid id="MyDataGrid" runat="server"/>

20. Binding DataReader to a Control • For purpose of retrieving and displaying records in the table, the records can be iterated within a While...End While loop, giving access to each of the individual records and their separate data fields. • For purpose of retrieving and displaying records in the table, the data reader can be bound to one of the display controls where they are automatically iterated and bound to the control to produce a complete listing of the recordset. • The method chosen -- recordset iteration or control binding -- depends mostly on programmer preferences and characteristics of the database application.

21. Accessing Single Table Value • For certain applications it may not be necessary to extract a complete set of records from a database table. For instance, you may wish simply to get a count of the number of records in the table using a SELECT statement with, say, a Count function: SELECT Count(*) FROM Products • In this case a data reader is not required since no records are return by the query. All that is returned is a numeric value representing the number of records. • Extracting single values from a table is accomplished with the OleDbCommand object's ExecuteScalar() method (rather than its ExecuteReader() method). The returned value can be assigned to a variable.

22. Accessing Single Table Value • <%@ Import Namespace="System.Data.OleDb" %><SCRIPT runat="server">Dim DBConnection As OleDbConnectionDim DBCommand As OleDbCommandDim SQLString As StringDim TheCount As IntegerSub Page_Load  DBConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" _ & "Data Source=d:\Databases\eCommerce.mdb")  DBConnection.Open()SQLString = "SELECT Count(*) FROM Products"  DBCommand = New OleDbCommand(SQLString, DBConnection)TheCount = DBCommand.ExecuteScalar()  DBConnection.Close()End Sub</SCRIPT> • The ExecuteScalar() method is used with other SQL functions that return single values such as MIN, MAX, AVG, and others.

23. Updating a Table • The SQL INSERT, UPDATE, and DELETE statements are used to edit records in a database table, adding new records or changing or deleting existing records. When these statements are issued no recordset is returned; the affected record is updated in place within the database. • There is no requirement for a data reader. Instead, these statements are issued through the command object's ExecuteNonQuery() method.

24. Updating a Table • '-- Create and issue an SQL UPDATE '-- command through the database connectionSQLString = "UPDATE Products _ SET ItemQuantity=0 _ WHERE ItemNumber='BU1111'"DBCommand = New OleDbCommand(SQLString, DBConnection)DBCommand.ExecuteNonQuery()

25. Contingency Binding • Occasionally, SQL SELECT statements do not return a recordset that can be bound to an output control. The recordset is empty because the SQL statement was in error or because no existing records matched the selection criteria. This situation may not cause a processing error, but you may not wish to display a partial or empty control where a recordset would otherwise display. • Fortunately, controls that are bound to a recordset are displayed only when data are bound to them. A common way of ensuring that a control is displayed only when it has records to display is by first getting a count of the records matching the search criteria, then binding to the control only if the count is greater than 0.

26. Contingency Binding SQLString = "SELECT Count(*) FROM Products WHERE ItemType = 'Business'"  DBCommand = New OleDbCommand(SQLString, DBConnection) If DBCommand.ExecuteScalar() <> 0 Then    SQLString = "SELECT * FROM Products WHERE ItemType = 'Business'"    DBCommand = New OleDbCommand(SQLString, DBConnection)    DBReader = DBCommand.ExecuteReader()    MyRepeater.DataSource = DBReader    MyRepeater.DataBind()    DBReader.Close() End If  DBConnection.Close()<asp:Repeater id="MyRepeater" runat="server"></asp:Repeater> • In the above example a test is first made of the number of records retrieved by an SQL statement issued to retrieve records that meet a particular search criteria. If this count is not 0, then a second SQL statement is issued to retrieve the matching recordset. • The record count is not assigned to a variable as a way to capture its value. The direct result of executing the DBCommand.ExecuteScalar() statement is tested.

27. Contingency Binding • Even though an asp:Repeater is coded on the page it does not display unless data are bound to it. • If the DataBind() method is issued (when the record count <> 0), then the Repeater is displayed. • If the DataBind() method is not issued (when the record count = 0), then the Repeater is not displayed. The control is displayed only when it has data to display -- when it is bound to a recordset. • This is the case for all bound controls, and it relieves the programmer from having to script the visibility of a control depending on the number of records retrieved.

28. Display Table Values <%@ Import Namespace="System.Data.OleDb" %> <SCRIPT runat="server"> Dim DBConnection As OleDbConnection Dim DBCommand As OleDbCommand Dim DBReader As OleDbDataReader Dim SQLString As String Sub Page_Load '-- Display table header MyTable.Text = "<table border=""1"" style=""border-collapse:collapse"">" MyTable.Text &= "<tr style=""background-color:#F0F0F0"">" MyTable.Text &= "<th>No</th>" MyTable.Text &= "<th>Type</th>" MyTable.Text &= "<th>Supplier</th>" MyTable.Text &= "<th>Name</th>" MyTable.Text &= "<th>Price</th>" MyTable.Text &= "<th>Qty</th>" MyTable.Text &= "</tr>"

29. Display Table Values DBConnection = New OleDbConnection ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\inetpub\wwwroot\eCommerce.mdb") DBConnection.Open() SQLString = "SELECT * FROM Products ORDER BY ItemNumber" DBCommand = New OleDbCommand(SQLString, DBConnection) DBReader = DBCommand.ExecuteReader() While DBReader.Read() '-- Display table rows MyTable.Text &= "<tr>" MyTable.Text &= "<td>" & DBReader("ItemNumber") & "</td>" MyTable.Text &= "<td>" & DBReader("ItemType") & "</td>" MyTable.Text &= "<td>" & DBReader("ItemSupplier") & "</td>" MyTable.Text &= "<td>" & DBReader("ItemName") & "</td>" MyTable.Text &= "<td align=""right"">" & DBReader("ItemPrice") & "</td>" MyTable.Text &= "<td align=""right"">" & DBReader("ItemQuantity") & "</td>" MyTable.Text &= "</tr>" End While DBReader.Close() DBConnection.Close()

30. Display Table Values '-- Display table footer MyTable.Text &= "</table>" End Sub </SCRIPT> <html> <body> <form runat="server"> <asp:Label id="MyTable" runat="server"/> </form> </body> </html> DisplayTable.aspx

31. Display Table Values • When the possibility exists that an SQL query will not return a set of records, it is always a good idea to anticipate and deal with the possibility that column headings may display but no rows of data appear. • As was suggested previously, the script is modified to check for a returned recordset and provide explanation if none were retrieved. SQLString = "SELECT Count(*) FROM Products WHERE ItemType='Business'"DBCommand = New OleDbCommand(SQLString, DBConnection)If DBCommand.ExecuteScalar() <> 0 Then...Else    MyTable.Text &= "<tr><td colspan=""6"" style=""color:#FF0000"">"    MyTable.Text &= "No matching records"    MyTable.Text &= "</td></tr>"End If DisplayTableCheck.aspx

32. CalculatingTable Values • Since field values from a database table are available during iteration of the table, additional processing can be performed to generate new information based on those values. • In the following example the ItemPrice and ItemQuantity fields are multiplied to derive the inventory value for each product. These values are accumulated across all records and reported in a total line appended to the output table.

33. CalculatingTable Values • Sub Page_LoadDim Amount As Decimal  Dim Total As Decimal = 0  '-- Display table header  MyTable.Text = "<table border=""1"" style=""border- _ collapse:collapse"">"  MyTable.Text &= "<tr style=""background-color:#F0F0F0"">"  MyTable.Text &= "<th>No</th>"  MyTable.Text &= "<th>Type</th>"  MyTable.Text &= "<th>Supplier</th>"  MyTable.Text &= "<th>Name</th>"  MyTable.Text &= "<th>Price</th>"  MyTable.Text &= "<th>Qty</th>"MyTable.Text &= "<th>Amount</th>"  MyTable.Text &= "</tr>"

34. CalculatingTable Values • DBConnection = New OleDbConnection _    ("Provider=Microsoft.Jet.OLEDB.4.0; & _    Data Source=d:\Databases\eCommerce.mdb")DBConnection.Open() SQLString = "SELECT * FROM Products _ ORDER BY ItemNumber"DBCommand = New OleDbCommand _ (SQLString, DBConnection)DBReader = DBCommand.ExecuteReader()

35. CalculatingTable Values While DBReader.Read() '-- Calculate item amount Amount = DBReader("ItemPrice") * DBreader("ItemQuantity") Total += Amount '-- Display table rows MyTable.Text &= "<tr>“ MyTable.Text &= "<td>" & DBReader("ItemNumber") & "</td>“ MyTable.Text &= "<td>" & DBReader("ItemType") & "</td>“ MyTable.Text &= "<td>" & DBReader("ItemSupplier") & "</td>“ MyTable.Text &= "<td>" & DBReader("ItemName") & "</td>“ MyTable.Text &= "<td align=""right"">" & DBReader("ItemPrice") & "</td>“ MyTable.Text &= "<td align=""right"">" & DBReader("ItemQuantity") & "</td>“ MyTable.Text &= "<td align=""right"">" & _ FormatNumber(Amount) & "</td>“ MyTable.Text &= "</tr>“ End While

36. CalculatingTable Values •  '-- Display table footerMyTable.Text &= "<tr align=""right"" style=""background-color:#F0F0F0"">"  MyTable.Text &= "<td colspan=""6""><b>Total </b></td>"  MyTable.Text &= "<td>" & FormatCurrency(Total) & "</td>"  MyTable.Text &= "</tr>"  MyTable.Text &= "</table>"End Sub</SCRIPT><html><body><form runat="server><asp:Label id="MyTable" runat="server"/></form></body></html> • Two variables are declared at the beginning of the script: Amount holds the calculation of ItemPrice times ItemQuantity for each product; Total is the accumulator for all the Amounts and is initialized to 0. • Within the processing loop Amount is calculated as DBReader("ItemPrice") * DBReader("ItemQuantity") for this product. This calculated Amount is added to the Total. Within a new table column this Amount is displayed with FormatNumber() formatting. • At the end of the processing loop variable Total, having accumulated all the individual Amounts, is displayed in an added table row. It is formatted as a dollar amount. DisplayTableCalc.aspx

37. Binding to Data Display Controls • The preferred ASP.NET method to display database records is to bind the recordset to a list control such as the asp:Repeater, asp:DataList, or asp:DataGrid control. • For an asp:Repeater control, templates are provided to describe output formatting. A table can be used to display rows and columns of records, with individual data items bound to the table cells. Also, alternating row formatting can be specified. A column can be provided for displaying a calculated amount for each item, and a row can be added to the bottom of the table for display of the inventory total.

38. Binding to a Repeater • <%@ Import _ Namespace="System.Data.OleDb" %><SCRIPT runat="server">Dim DBConnection As OleDbConnectionDim DBCommand As OleDbCommandDim DBReader As OleDbDataReaderDim SQLString As StringDim Amount As DecimalDim Total As Decimal = 0

39. Binding to a Repeater • Sub Page_Load  If Not Page.IsPostBack Then    DBConnection = New OleDbConnection _      ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\Databases\eCommerce.mdb")    DBConnection.Open()    SQLString = "SELECT * FROM Products ORDER BY ItemNumber"    DBCommand = New OleDbCommand(SQLString, DBConnection)    DBReader = DBCommand.ExecuteReader()    RepeaterOutput.DataSource = DBReader    RepeaterOutput.DataBind()    DBReader.Close()    DBConnection.Close()  End IfEnd Sub

40. Binding to a Repeater • Function GetAmount(Price As Decimal, Quantity As Decimal)  Amount = Price * Quantity  Total += Amount  Return AmountEnd FunctionFunction GetTotal()  Return TotalEnd Function</SCRIPT>

41. Binding to a Repeater • <html><body><form runat="server"><asp:Repeater id="RepeaterOutput" runat="server">  <HeaderTemplate>    <table border="1" style="border-collapse:collapse">      <tr style="background-color:#A0A0A0; color:#FFFFFF">        <th>No</th>        <th>Type</th>        <th>Supplier</th>        <th>Name</th>        <th>Price</th>        <th>Qty</th>        <th>Amount</th>      </tr>  </HeaderTemplate>

42. Binding to a Repeater •   <ItemTemplate>    <tr>      <td><%# Container.DataItem("ItemNumber") %></td>      <td><%# Container.DataItem("ItemType") %></td>      <td><%# Container.DataItem("ItemSupplier") %></td>      <td><%# Container.DataItem("ItemName") %></td>      <td align="right"><%# Container.DataItem("ItemPrice")%> </td>      <td align="right"><%# Container.DataItem("ItemQuantity")%> </td>      <td align="right"> <%# FormatNumber(GetAmount(Container.DataItem("ItemPrice"), _ Container.DataItem("ItemQuantity"))) %></td>    </tr>  </ItemTemplate>

43. Binding to a Repeater •   <AlternatingItemTemplate>    <tr style="background-color:#F0F0F0">      <td><%# Container.DataItem("ItemNumber") %></td>      <td><%# Container.DataItem("ItemType") %></td>      <td><%# Container.DataItem("ItemSupplier") %></td>      <td><%# Container.DataItem("ItemName") %></td>      <td align="right"><%# Container.DataItem("ItemPrice") %> </td>      <td align="right"><%# Container.DataItem("ItemQuantity") %> </td>      <td align="right"> <%# FormatNumber(GetAmount(Container.DataItem("ItemPrice"), _ Container.DataItem("ItemQuantity"))) %></td>    </tr>  </AlternatingItemTemplate>

44. Binding to a Repeater • <FooterTemplate>    <tr align="right">      <th colspan="6" style="background-color:#A0A0A0; _ color:#FFFFFF">Total</th>      <td><%# FormatCurrency(GetTotal()) %></td>    </tr>    </table>  </FooterTemplate></asp:Repeater></form></body></html> Repeater.aspx

45. Binding to a Repeater • The script links to the database, extracts a recordset, and binds the associated data reader to the Repeater control. • Scripting is placed inside the If Not Page.IsPostBack condition because the control only needs to be populated the first time the page loads. • Although it does not occur in this example, the control would retain its data through the page's View State if a page postback were made.

46. Binding to a Repeater • Data values extracted from the Products table are bound to the table cells with a simple binding expression in the format <%# Container.DataItem("FieldName") %>. A calculated amount for each item is given by a function call to GetAmount() which passes the ItemPrice and ItemQuantity from the associated record: <%# FormatNumber(GetAmount(Container.DataItem("ItemPrice") _ Container.DataItem("ItemQuantity"))) %> • The function receives these values as arguments Price and Quantity, and multiplies them to derive the item Amount. At the same time, this Amount is added to variable Total to accumulate the total value of inventory. Variables Amount and Total have been declared as global variables for access by the Repeater and by the function. The function returns the calculated Amount, which is formatted as a number with the built-in FormatNumber() function.

47. Binding to a Repeater • Incidentally, were it not for the fact that the inventory Total is calculated by accumulating item Amounts, the function call to GetAmount() would not be needed. • If only the item Amount is calculated, it could be done by including the calculation inside the Repeater cell: <%# FormatNumber(Container.DataItem _ ("ItemPrice") * Container.DataItem _ ("ItemQuantity")) %>

48. Binding to a Repeater • Still, there is coding consistency in always using function calls for calculated values. This consistency is maintained by displaying the inventory Total at the bottom of the Repeater table by a function call to GetTotal(): <%# FormatCurrency(GetTotal()) %> • The function simply returns the value of variable Total. This value could have been displayed without a function call by embedding the variable itself inside the binding expression: <%# FormatCurrency(Total) %>

49. Binding to a DataGrid • An asp:DataGrid control provides both the easiest and the most elaborate methods for displaying database output. On one hand, the control can automatically generate columns of output data to match the columns of input data with only minimal specifications. On the other hand, the control can be altered in numerous ways to produce specialized output. • In its minimal state the asp:DataGrid control requires only a single line of code: <asp:DataGrid id="DataGridOutput" runat="server"/>

50. Binding to a DataGrid DBConnection = New OleDbConnection _ ("Provider=Microsoft.Jet.OLEDB.4.0; _ Data Source=d:\Databases\eCommerce.mdb") DBConnection.Open() SQLString = "SELECT ItemNumber,ItemType,ItemSupplier, _ ItemName,ItemPrice, ItemQuantity FROM Products _ ORDER BY ItemNumber" DBCommand = New OleDbCommand(SQLString, DBConnection) DBReader = DBCommand.ExecuteReader() DataGridOutput.DataSource = DBReader DataGridOutput.DataBind() DBReader.Close() DBConnection.Close() Datagrid.aspx