| Let's go back and implement some functionality for your business object that you created in Listing 15.1. Since your object represents a database, the first thing you need is a connection string property. You'll create this property inside your Database class, as shown in Listing 15.4. Listing 15.4 The Connection String Property 1: Imports System 2: Imports System.Data 3: Imports System.Data.OleDb 4: 5: Namespace TYASPNET 6: 7: Public Class Database 8: public ConnectionString as String 9: private objConn as OleDbConnection 10: private objCmd as OleDbCommand 11: ... Any page that uses this business object will be able to set and retrieve the connection string for the current database at any time. You also added two private variables that represent database objects. Because these two variables are private, they will be accessible to the methods in this class, but not to outside methods. In other words, the ASP.NET page that uses this business object won't be able to access these variables, but it will be able to access the public ConnectionString variable. Tip Think of a class as a car engine. Everything that's private is inside and cannot be seen by anything on the outside. You may know that the pistons are inside the engine, but you can't see or access them (unless you take apart the engine, which is another story). Public items, however, reveal themselves to those on the outside. These include things such as the dipstick, spark plugs, and air filter.
Next, you need a method to retrieve information from the database. You'll create a function that will execute a specified SQL statement and return the data as an OleDbDataReader. You also need a function that performs database operations but doesn't return any data for Insert and Update statements, for example. Listing 15.5 shows two methods for accessing data: one that returns information, and one that doesn't. Listing 15.5 Executing a Select Query 12: public function SelectSQL(strSelect as string) as _ 13: OleDbDataReader 14: try 15: objConn = new OleDbConnection(ConnectionString) 16: objCmd = new OleDbCommand(strSelect, objConn) 17: objCmd.Connection.Open 18: return objCmd.ExecuteReader 19: objCmd.Connection.Close() 20: catch ex as OleDbException 21: return nothing 22: end try 23: end function 24: 25: public function ExecuteNonQuery(strQuery as string) _ 26: as Boolean 27: try 28: objConn = new OleDbConnection(ConnectionString) 29: objCmd = new OleDbCommand(strQuery, objConn) 30: objCmd.Connection.Open() 31: objCmd.ExecuteNonQuery 32: objCmd.Connection.Close() 33: return true 34: catch ex as OleDbException 35: return false 36: end try 37: end function 38: 39: End Class 40: 41: End Namespace The SelectSQL function is standard. It opens a connection with an OleDbCommand object, executes the query, and returns a DataReader, as shown on line 18. You then close your connection on line 19. If anything goes wrong, the try block will catch the error and return nothing. The ExecuteNonQuery method is similar, but it doesn't return a DataReader. Instead, it returns true or false depending on whether the command executed successfully. Recompile this object with the same command that you used previously: vbc /t:library /out:..\bin\TYASPNET21Days.dll /r:System.dll /r:System.Data.dll Database.vb Now let's modify your ASP.NET page. Listing 15.6 shows a modified version of Listing 15.3. It sets properties of your new database object and displays data in a DataGrid. Listing 15.6 Using Your Object's Methods 1: <%@ Page Language="VB" %> 2: <%@ Import Namespace="System.Data" %> 3: <%@ Import Namespace="System.Data.OleDb" %> 4: 5: <script runat="server"> 6: sub Page_Load(Sender as Object, e as EventArgs) 7: dim objDatabase as new TYASPNET.Database 8: 9: objDatabase.ConnectionString = "Provider=" & _ 10: "Microsoft.Jet.OLEDB.4.0;" & _ 11: "Data Source=c:\ASPNET\data\banking.mdb" 12: 13: dim objReader as OleDbDataReader 14: objReader = objDatabase.SelectSQL _ 15: ("Select * from tblUsers") 16: 17: if not objReader is nothing then 18: DataGrid1.DataSource = objReader 19: DataGrid1.DataBind() 20: objReader.Close 21: end if 22: end sub 23: 24: </script> 25: 26: <html><body> 27: <asp:Label runat="server" /> 28: 29: <asp:DataGrid 30: runat="server" BorderColor="black" 31: GridLines="Vertical" cellpadding="4" 32: cellspacing="0" width="100%" 33: Font-Name="Arial" Font-Size="8pt" 34: HeaderStyle-BackColor="#cccc99" 35: ItemStyle-BackColor="#ffffff" 36: AlternatingItemStyle-Backcolor="#cccccc" /> 37: </body></html>  | You declare your database object on line 7 and set the ConnectionString property on line 9. On line 14, you execute the SelectSQL statement that you built in Listing 15.5. You then grab the results and place them in an OleDbDataReader. |
Remember that if an error occurs, the SelectSQL function returns nothing. Before you do anything with the data reader, on line 17 you verify that you actually have results. If you do, you bind the data to a DataGrid on line 19 and close your reader on line 20. This listing, when viewed through a browser, generates the output shown in Figure 15.3. Figure 15.3. Your business object returns data that you can use in an ASP.NET page.
Note The Username and Password columns were added during "Week 2 in Review." Check out the bonus project in that section to see how and why it was done.
Using your custom Database object is the same as using any other object you've used up to this point. You create an instance of the object, set some properties, and execute some methods. You may be wondering why you did all that work just to save a few lines of code in your ASP.NET page. For a simple example such as this, it wasn't necessary to build a business object for your database query. However, when your applications start getting significantly larger, your business object can become much more complex. In those cases, this method can save you a lot of time writing ASP.NET pages. Also, you can now use this object in any ASP.NET page and get results. You can change the connection string to connect to another database and specify a different SQL statement to return different data. Although you saved yourself just 10 15 lines in this ASP.NET page, imagine how many lines you'll save when you have 10 or 20 ASP.NET pages that can all use this one object. Another question you may be asking is "Why build a database business object when I can simply use the OleDb data classes directly from the ASP.NET pages? Why not build something more useful?" Your business object is very useful, actually. Not only does it allow the user to retrieve data with a single line of code, but it hides all the complexity of working with the data objects. The user (in this case, another programmer) need not worry about OleDbCommands or connections. He doesn't have to worry about wrapping things in a try block or catching errors. Your class handles all that for the user of your object. Figure 15.4 illustrates the difference between what the user sees and what he doesn't. Figure 15.4. The developer sees only what you let him. He doesn't see any implementation.
| 
