57076_Ch 8 SAN.qxd 01/12/2003 6:43 PM Page 249 8 Reading From Databases So far in this book you've learnt a lot about programming, and seen those techniques in use in a variety of Web pages. Now it's time to turn our attention to one of the most important topics of building Web sites – data. Whatever the type of site you aim to build, data plays an important part. From a personal site (perhaps a vacation diary or a photo album), to a corporate e-commerce site, data is key.
57076_Ch 8 SAN.qxd 01/12/2003 6:43 PM Page 250 Chapter 8 Databases also fall into the category of complex files. When using Microsoft Access, you have an MDB file – this is a database file, but from the file itself you can't tell anything about the data inside. You need some way to get to the data, either using something such as Microsoft Access itself, or as we are going to do, using the .NET data classes. Before you can access the data, you need to know how it is stored internally.
57076_Ch 8 SAN.qxd 01/12/2003 6:43 PM Page 251 Reading From Databases To get around these problems we use a process called Normalization. Normalization Normalization is the process of separating repeated information into separate tables. There are whole books dedicated to database design, but we only need to look at the simplest case. A good beginner book on database design is Database Design for Mere Mortals: A Hands On Guide to Relational Database Design, 2nd Edition (ISBN: 0-201-75284-0).
57076_Ch 8 SAN.qxd 01/12/2003 6:43 PM Page 252 Chapter 8 The OrderDetails table is as follows: OrderDetailsID OrderID Order Item Quantity Item Cost 1 1 Widget 10 3.50 2 1 Doodad 5 2.95 3 2 Thingy 1 15.98 4 3 Widget 1 3.50 5 4 Doodad 2 2.95 6 4 Thingamajig 1 8.50 We now have three tables that can be linked together by their ID fields as shown in Figure 8-1: Figure 8-1 We now have links between the tables.
7076_Ch 8 SAN.qxd 01/12/2003 6:43 PM Page 253 Reading From Databases If you remember, in Chapter 5 when we looked at functions, we had a function name encapsulating some code statements. Think of a stored procedure in a similar way – it wraps a set of SQL statements, allowing us to use the name of the stored procedure to run those SQL statements. We're not going to focus much on this topic as it's outside the scope of this book.
57076_Ch 8 SAN.qxd 01/12/2003 6:43 PM Page 254 Chapter 8 Figure 8-2 2. 3. Select Access Database from the window that appears and press OK. Enter the following into the Data File text area (we'll use a central location for the database, so that we can reuse it later in the book): C:\BegASPNET11\data\Northwind.mdb 254 4. Press OK to connect to the database. This is the Northwind database, one of the sample databases that ships with Microsoft Access. 5.
57076_Ch 8 SAN.qxd 01/12/2003 6:43 PM Page 255 Reading From Databases Figure 8-3 You can double-click on these to open the table, and see and change the data. One thing you might notice is that you don't see any queries – that's because Web Matrix doesn't support queries in Access. When connecting to SQL Server, you'll see the stored procedures – you can even create and edit them – but for Access, you are limited to tables only. How It Works There's nothing really to explain about how it works.
57076_Ch 8 SAN.qxd 01/12/2003 6:43 PM Page 256 Chapter 8 Displaying Data Using the Data Explorer You've already seen how easy connecting to a database is using the Data Explorer. Creating pages directly from this explorer is even easier – all you have to do is drag the table name and drop it onto a page. This will automatically create a connection on the page and a fully functional data grid. Let's give this a go. Try It Out Creating a Grid 1. Create a new ASP.NET page called Grid1.aspx. 2.
57076_Ch 8 SAN.qxd 01/12/2003 6:43 PM Page 257 Reading From Databases Figure 8-5 Amazing! A sortable grid full of data and you didn't have to write even a single line of code! How It Works Its workings rely on two controls: the AccessDataSourceControl, which provides the connection to the database, and an MxDataGrid, which is a Web Matrix control (also covered in Chapter 10) that displays the data. Looking at the HTML view for these controls gives you a good idea of what they do.
57076_Ch 8 SAN.qxd 01/12/2003 6:43 PM Page 258 Chapter 8 ❑ The SelectCommand defines the SQL that will return the required data – in this case, it's all rows and columns from the Suppliers table. This is the default since we dragged this table, but we can customize the SelectCommand to return only selected rows or columns. ❑ The ConnectionString defines the OLEDB connection string.
57076_Ch 8 SAN.qxd 01/12/2003 6:43 PM Page 259 Reading From Databases Attribute Description BorderWidth This is how wide the border of the grid is. Here it is 1 pixel (px stand for pixel), which is a thin border. BorderStyle This is the style of the border. As part of the grid we also have some style elements: ❑ PagerStyle, which defines the style of the pager section. In our grid this is the last row, showing the page numbers, but it appears before the footer if a footer row is being shown.
57076_Ch 8 SAN.qxd 01/12/2003 6:43 PM Page 260 Chapter 8 ❑ Editable Grid gives a grid allowing updates to the data. ❑ Simple Stored Procedure gives a grid that uses a stored procedure for its data source. All of these supplied templates connect to a SQL Server database, and need modification if they are to be used with a different database. However, they provide a quick way to get pages constructed allowing you to make a few simple changes to get what you need, rather than coding from scratch.
57076_Ch 8 SAN.qxd 01/12/2003 6:43 PM Page 261 Reading From Databases 4. Save the file and run it; you'll see something like Figure 8-6: Figure 8-6 This isn't much different from the drag and drop approach we used in the first example, but it uses the .NET data classes and a DataGrid control, rather than the Web Matrix controls (AccessDataSourceControl and MxDataGrid). It means this technique will work even if Web Matrix isn't installed on the server running the page. Let's see how it works.
57076_Ch 8 SAN.qxd 01/12/2003 6:43 PM Page 262 Chapter 8 ❑ PageSize, which defines the number of rows to show per page. ❑ OnPageIndexChanged, which defines the event procedure to call when the page number is changed. When a page number link is clicked, the procedure defined here is run. ❑ AllowSorting, which is true, allowing the grid to sort the rows on the basis of column selections. Setting this to true enables links on the column headings.
57076_Ch 8 SAN.qxd 01/12/2003 6:43 PM Page 263 Reading From Databases The first line sets the CurrentPageIndex of the grid to 0, having the effect of starting the grid at page 1. We do this because we are re-sorting. We then set SortField to the sorted field, and rebind the grid. Notice that SortField hasn't been declared as a variable – in fact it's a property. This might seem confusing because properties are always attached to objects, prompting the question what object is this one attached to.
57076_Ch 8 SAN.qxd 01/12/2003 6:43 PM Page 264 Chapter 8 Next we check the SortField property to see if we are sorting the data in the order selected by the user (that is, if the user has clicked one of the column headings). This is accessing the SortField property of the Page and therefore calls the Get part of the property. If the sort order hasn't been defined, the String.Empty is the value of SortField, so we set the command string to order by the CompanyName.
57076_Ch 8 SAN.qxd 01/12/2003 6:43 PM Page 265 Reading From Databases The data is now in our DataSet so we can use it as the DataSource for the grid, and bind the grid: DataGrid1.DataSource = ds DataGrid1.DataBind() End Sub This may look like a complex set of procedures, but it's actually a simple set of steps that is used many times when you need to fetch data. You'll be seeing this many times during this book, and we'll go over its theory later so you really understand what's happening.
57076_Ch 8 SAN.qxd 01/12/2003 6:43 PM Page 266 Chapter 8 3. Pick the SELECT Data Method and drag it from the Toolbox, dropping it into your code window. This starts the wizard, and the first screen as shown in Figure 8-8 is where you pick the database to connect: Figure 8-8 4. 266 The drop down list shows configured data sources (from the Data Explorer) as well as an option to create a new connection.
57076_Ch 8 SAN.qxd 01/12/2003 6:43 PM Page 267 Reading From Databases Figure 8-9 Now you can select the columns you wish to show. You can pick multiple columns (the * column means all columns from the table) from multiple tables. You simply select them individually. However, when picking columns from multiple tables, you must join the tables. Remember our discussion of linked tables and keys from the beginning of the chapter – you need the primary and foreign key to join the tables. 5.
57076_Ch 8 SAN.qxd 01/12/2003 6:43 PM Page 268 Chapter 8 7. Select your options the same as shown in Figure 8-11: Figure 8-11 8. Click OK and you'll see the WHERE clause part of the window is filled in as shown in Figure 8-12: Figure 8-12 9.
57076_Ch 8 SAN.qxd 01/12/2003 6:43 PM Page 269 Reading From Databases Figure 8-13 10. 11. Press Next. From the Name Method window, change the name textbox to GetProductsDataSet. Make sure the radio button at the bottom is set to DataSet and press Finish. We'll look at the DataReader later in the chapter. 12. Once the code has been added, you want a way to display it. You can do this by switching to Design view and dragging a DataGrid onto the page. 13.
57076_Ch 8 SAN.qxd 01/12/2003 6:43 PM Page 270 Chapter 8 Figure 8-14 You can see how we've now only got two columns and from two different tables. Let's see how this works. How It Works The key to this is the wizard that allows you to build up an SQL statement. This is great if you are a to SQL as you don't have to understand how the SQL language works. Perhaps the most important part of this wizard is the WHERE Clause Builder shown in Figure 8-11.
57076_Ch 8 SAN.qxd 01/12/2003 6:43 PM Page 271 Reading From Databases If we wanted to add a third table, perhaps Suppliers, we could use an AND clause.
57076_Ch 8 SAN.qxd 01/12/2003 6:43 PM Page 272 Chapter 8 Now when you look at the WHERE clause section you see two tables joined together as in Figure 8-17: Figure 8-17 The WHERE Clause Builder can also be used to filter data so that only selected rows are shown; we'll look at that later. For now though, let's move on to look at the code the wizard created for us (it may look slightly different in your page – we've wrapped it so it's easier to read): Function GetProductsDataSet() As System.Data.
57076_Ch 8 SAN.qxd 01/12/2003 6:43 PM Page 273 Reading From Databases Normally we use the <%@ import Namespace="…" %> page directive to indicate the namespaces being used in a page, and thus we don't have to specify the namespace when declaring variables. The wizard isn't sure what namespaces have been set at the top of the page, so it includes the full namespace just in case, ensuring that the code will compile under all situations.
57076_Ch 8 SAN.qxd 01/12/2003 6:43 PM Page 274 Chapter 8 Dim dataAdapter As System.Data.IDbDataAdapter = _ New System.Data.OleDb.OleDbDataAdapter We mentioned that the data adapter is the link between our page and the data. As part of this link, the adapter provides not only data fetching, but also data modification. It does so with different command objects, exposed as properties of the adapter. These allow the different commands to run depending upon the action being performed.
57076_Ch 8 SAN.qxd 01/12/2003 6:43 PM Page 275 Reading From Databases ❑ System.Data.SqlClient, which provides the objects used to communicate with SQL Server. For some of the objects there are two copies – one in the OleDb namespace, and one in the SqlClient namespace. For example, there are two Connection objects – OleDbConnection and SqlConnection. Having two objects means they can be optimized for particular databases.
57076_Ch 8 SAN.qxd 01/12/2003 6:43 PM Page 276 Chapter 8 Each of these Command objects has a Connection property to specify which database the command applies to, a CommandText property to specify the command text to run, and a CommandType property to indicate the type of command (straight SQL or a stored procedure).
57076_Ch 8 SAN.qxd 01/12/2003 6:43 PM Page 277 Reading From Databases Property Description CommandText Contains the SQL command or the name of a stored procedure. CommandType Indicates the type of command being run, and can be one of the CommandType enumeration values, which are: StoredProcedure, to indicate a stored procedure is being run. TableDirect, to indicate the entire contents of a table are being returned. In this case, the CommandText property should contain the table name.
57076_Ch 8 SAN.qxd 01/12/2003 6:43 PM Page 278 Chapter 8 Command Parameters Parameter Parameter Parameter Figure 8-19 Let's look at an example to see how this works. Try It Out Using Parameters 1. Create a new blank ASP.NET page called Parameters.aspx. 2. Add a Label and change the Text property to Category:. 3. Add a DropDownList next to the label, and change the ID property to lstCategory. 4. Add a Button next to the DropDownList and change the Text property to Fetch. 5.
57076_Ch 8 SAN.qxd 01/12/2003 6:43 PM Page 279 Reading From Databases DataGrid1.DataSource = GetProducts(lstCategory.SelectedValue) DataGrid1.DataBind() End Sub 7. Underneath that procedure, add the following code: Sub Page_Load(Sender As Object, E As EventArgs) If Not Page.IsPostback Then lstCategory.DataSource = GetCategories() lstCategory.DataValueField = "CategoryID" lstCategory.DataTextField = "CategoryName" lstCategory.DataBind() End If End Sub 8.
57076_Ch 8 SAN.qxd 01/12/2003 6:43 PM Page 280 Chapter 8 13. 14. Save the file and run it. Select a category and then click Fetch to see only products for that category shown in Figure 8-22: Figure 8-22 What you've achieved here is two things. First Yyou've first used two controls that are bound to data – the list of categories and the grid of products. Secondly you only fetched the products for a selected category – you've filtered the list. Let's see how this works.
57076_Ch 8 SAN.qxd 01/12/2003 6:43 PM Page 281 Reading From Databases lstCategory.DataValueField = "CategoryID" lstCategory.DataTextField = "CategoryName" lstCategory.DataBind() End If End Sub When the Fetch button is clicked, we need to get the value from the DropDownList. For this, we use the SelectedValue property, which is new to ASP.NET 1.1. This contains the ID of the selected category, and we pass this into the GetProducts routine, which will return a DataSet of the products.
57076_Ch 8 SAN.qxd 01/12/2003 6:43 PM Page 282 Chapter 8 Now we come to the definition of the parameter. Like many of the other examples, this uses a database-specific object – an OleDbParameter, which defines what is being passed into the query: Dim dbParam_categoryID As System.Data.IDataParameter = _ New System.Data.OleDb.OleDbParameter We then set the properties of the parameter. The ParameterName indicates the name of the parameter, and we set the value to be the same as the placeholder.
57076_Ch 8 SAN.qxd 01/12/2003 6:43 PM Page 283 Reading From Databases This simply appends the CategoryID value (from the function argument) into the SQL string. Why is this bad when it achieves the same things, plus uses less code? The answer has to do with hacking. This type of method potentially allows what are known as SQL Injection Attacks, which are a ‘very bad thing' (do a Web search for more details on SQL Injection).
57076_Ch 8 SAN.qxd 01/12/2003 6:43 PM Page 284 Chapter 8 Try It Out Using a DataReader 1. Create a new blank ASP.NET page called DataReader.aspx. 2. Drag a DataGrid control from the Toolbox onto the page. 3. Switch to Code view and start the code wizard by dragging the SELECT Data Method onto the code page. 4. 5. Select the existing database connection from the first screen and press Next. 6. 7. Click Next, and Next again, to go past the Query Preview screen. 8. 9.
57076_Ch 8 SAN.qxd 01/12/2003 6:43 PM Page 285 Reading From Databases Dim dbCommand As System.Data.IDbCommand = _ New System.Data.OleDb.OleDbCommand dbCommand.CommandText = queryString dbCommand.Connection = dbConnection Once the command details are set, we can then open the database connection: dbConnection.Open Even though the database connection has been opened for us when using a DataSet, we still have to open it manually because we are using an OleDbCommand and a data reader.
57076_Ch 8 SAN.qxd 01/12/2003 6:43 PM Page 286 Chapter 8 Dim dataReader As System.Data.OleDbDataReader = _ dbCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection) If Not dataReader.HasRows Then Response.Write("No rows found") Else While dataReader.Read() Response.Write(datareader("ProductName") & "
") End While End If dataReader.Close() This first uses the HasRows property to determine if there are any rows, and then uses the Read method to read a row.
