57084_08.qxp 30/01/2004 8:02 PM Page 247 8 Reading from Databases So far, 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.
57084_08.qxp 30/01/2004 8:02 PM Page 248 Chapter 8 plain text. Spreadsheets, on the other hand, are complex files containing not only the entered text and numbers, but also details about the data, such as what the columns contain, how they are formatted, and so on. Databases also fall into the category of complex files. When using Microsoft Access, you have an MDB file – this is a database file, but you can't tell anything about the data from the file itself.
57084_08.qxp 30/01/2004 8:02 PM Page 249 Reading from Databases Customer Address Order Date Order Item Quantity Item Cost Dave 3 Middle Lane Oxborough England UK 01/09/2003 Thingamajig 1 8.50 This is the sort of thing you'd see in a spreadsheet, but there are a couple of big problems with this. For a start, we have repeated information. John, for example, has his address shown three times. What happens if he moves house? You'd have to change the address everywhere it occurs.
57084_08.qxp 30/01/2004 8:02 PM Page 250 Chapter 8 OrderID CustomerID OrderDate 1 1 01/07/2003 2 1 01/08/2003 3 2 01/08/2003 4 3 01/09/2003 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.
7084_08.qxp 30/01/2004 8:02 PM Page 251 Reading from Databases foreign key is simply a column that is the primary key in another table. Because the values of the primary key and the foreign key will be the same, we can use them to link the tables together. This linking of the tables is done in Structured Query Language (SQL), usually as a query or a stored procedure. SQL and Stored Procedures Queries are the way in which we deal with data in a database, either to extract data or to manipulate it.
57084_08.qxp 30/01/2004 8:02 PM Page 252 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 (use a central location for the database, so that we can reuse it later in the book): C:\BegASPNET11\data\Northwind.mdb 4. Press OK to connect to the database. This is the Northwind database, one of the sample databases that ships with Microsoft Access. 5.
57084_08.qxp 30/01/2004 8:03 PM Page 253 Reading from Databases 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.
57084_08.qxp 30/01/2004 8:03 PM Page 254 Chapter 8 3. Save the page and run it as shown in Figure 8-5: 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 The workings rely on two controls – the AccessDataSourceControl that provides the connection to the database, and an MxDataGrid, which is a Web Matrix control (also covered in Chapter 10) that displays the data.
57084_08.qxp 30/01/2004 8:03 PM Page 255 Reading from Databases some database specific features. You don't need to know about these specifically (they are fully documented in the .NET help files); just copy them if you ever need to use them again. At this stage, you have enough details to connect to a database and fetch data, but don't have any way to display it.
57084_08.qxp 30/01/2004 8:03 PM Page 256 Chapter 8 ❑ PagerStyle: 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. ❑ FooterStyle: Defines the style of the footer row. In our grid, we aren't showing a footer, but the style is set so that the footer will look correct if it is shown. ❑ SelectedItemStyle: Defines the style of items when they are selected.
57084_08.qxp 30/01/2004 8:03 PM Page 257 Reading from Databases Try It Out Creating a Data Page 1. Create a new page using the Data Pages templates. Pick the Data Report with Paging and Sorting, and call it SortPage.aspx. 2. In the design window, select the All tab and change this line: <%@ import Namespace="System.Data.SqlClient" %> To: <%@ import Namespace ="System.Data.OleDb" %> If this is not done, errors will be encountered while loading the page. 3.
57084_08.qxp 30/01/2004 8:03 PM Page 258 Chapter 8 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. How It Works The first thing to look at is the namespace change: <%@ import Namespace="System.
57084_08.qxp 30/01/2004 8:03 PM Page 259 Reading from Databases ❑ AllowSorting: Allows the grid to sort the rows on the basis of column selections. Setting this to true enables links on the column headings. ❑ OnSortCommand: Defines the event procedure to call when a column heading is clicked. Now let's look at the code that uses this grid, starting with the Page_Load() event: void Page_Load(object sender, EventArgs e) { if (!Page.
57084_08.qxp 30/01/2004 8:03 PM Page 260 Chapter 8 one attached to. Well, since it hasn't got a named object, ASP.NET takes this as being a property of the current Page. By default, a Page doesn't have a SortField property, so we define one: protected String SortField { get { object o = ViewState["SortField"]; return (o == null) ? String.Empty : (String)o; } set { ViewState["SortField"] = value; } } The interesting point is that we haven't defined a class. Because we are coding within an ASP.
57084_08.qxp 30/01/2004 8:03 PM Page 261 Reading from Databases if (SortField == String.Empty) CommandText = "select * from Suppliers order by CompanyName"; else CommandText = "select * from Suppliers order by " + SortField; These commands use SQL statements, but we could equally have used stored queries or stored procedures. In practice, you should use stored queries, but using SQL directly here means we don't have to create the stored query – since we're concentrating on ASP.
57084_08.qxp 30/01/2004 8:03 PM Page 262 Chapter 8 Displaying Data Using the Code Wizards There are times where both the drag and drop from the Data Explorer and the template pages cannot provide you with exactly what you need. Perhaps you'd like to customize the query, or just add a routine to fetch data to an already existing page. The code wizards allow you to add code routines to a page, giving you a finer control of the data being fetched or updated. Let's give this a go.
57084_08.qxp 30/01/2004 8:03 PM Page 263 Reading from Databases 4. The drop-down list shows configured data sources (from the Data Explorer) as well as an option to create a new connection. Pick the existing connection and press Next to go to the screen shown in Figure 8-9: Figure 8-9 Now you can select the columns you wish to show. You can pick multiple columns (the * means all columns from the table) from multiple tables. You simply select them individually.
57084_08.qxp 30/01/2004 8:03 PM Page 264 Chapter 8 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.
57084_08.qxp 30/01/2004 8:03 PM Page 265 Reading from Databases You can see just the required columns in 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.
57084_08.qxp 30/01/2004 8:03 PM Page 266 Chapter 8 This is where (pun intended) we add the WHERE part of the SQL statement, and this is what filters the rows and joins tables together. We've selected the Join option allowing us to specify the primary key (CategoryID in the Categories table) and the foreign key (CategoryID in the Products table). The WHERE clause becomes: WHERE [Categories].[CategoryID] = [Products].
57084_08.qxp 30/01/2004 8:03 PM Page 267 Reading from Databases 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 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): System.Data.
57084_08.qxp 30/01/2004 8:03 PM Page 268 Chapter 8 Next, we have the connection string that simply points to our existing database: string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; " + "Ole DB Services=-4; Data Source=C:\\BegASPNET11\\" + "data\\Northwind.mdb"; Notice that this uses two backward slash characters to avoid the problem of the single slash character being an escape sequence. In our earlier example we used the @ symbol. Now we have the connection object: System.Data.
57084_08.qxp 30/01/2004 8:03 PM Page 269 Reading from Databases We mentioned earlier 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.
57084_08.qxp 30/01/2004 8:03 PM Page 270 Chapter 8 ❑ System.Data.OleDb: Contains the objects used to communicate to databases via OLEDB. OLEDB provides a common set of features to connect to multiple databases, such as Access, DBase, and so on. ❑ System.Data.SqlClient: 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.
57084_08.qxp 30/01/2004 8:03 PM Page 271 Reading from Databases On the left we have the database and the connection, in the middle we have four Command objects, and on the right a DataAdapter and a DataSet.
57084_08.qxp 30/01/2004 8:03 PM Page 272 Chapter 8 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.
57084_08.qxp 30/01/2004 8:03 PM Page 273 Reading from Databases of products, with a drop-down list showing the product categories. The user could select a category so that only those categories are shown. The Parameters collection contains a Parameter object for each parameter in the query. Thus, a command with three parameters would have objects looking like in Figure 8-19: Figure 8-19 Let's look at an example to see how this works. Try It Out Using Parameters 1. 2. 3. 4. 5. Create a new blank ASP.
57084_08.qxp 30/01/2004 8:03 PM Page 274 Chapter 8 6. Double-click the Fetch button to switch to the Click event procedure. Add the following code: void Button1_Click(object sender, EventArgs e) { DataGrid1.DataSource = GetProducts(Convert.ToInt32(lstCategory.SelectedValue)); DataGrid1.DataBind(); } 7. Underneath that procedure, add the following code: void Page_Load(Object Sender, EventArgs e) { if (!Page.IsPostBack) { lstCategory.DataSource = GetCategories(); lstCategory.
57084_08.qxp 30/01/2004 8:03 PM Page 275 Reading from Databases 13. 14. Save the file and run it. Select a category and then click Fetch to see only the products for that category shown in Figure 8-22: Figure 8-22 What you've achieved here is two things. First, you've used two controls that are bound to data – the list of categories and the grid of products. Second, you only fetched the products for a selected category – you've filtered the list. Let's see how this works.
57084_08.qxp 30/01/2004 8:03 PM Page 276 Chapter 8 lstCategory.DataValueField = "CategoryID"; lstCategory.DataTextField = "CategoryName"; lstCategory.DataBind(); } } 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.
57084_08.qxp 30/01/2004 8:03 PM Page 277 Reading from Databases Once the query string is set, we define our command to run the query, as follows: System.Data.IDbCommand dbCommand = new System.Data.OleDb.OleDbCommand(); dbCommand.CommandText = queryString; dbCommand.Connection = dbConnection; 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: System.Data.
57084_08.qxp 30/01/2004 8:03 PM Page 278 Chapter 8 string queryString = "SELECT [Products].[ProductName], " "[Products].[QuantityPerUnit], [Products].[UnitPrice]," + "[Products].[UnitsInStock] FROM [Products] " + "WHERE ([Products].[CategoryID] = " + CategoryID + ")"; This simply appends the CategoryID value (from the function argument) into the SQL string. Why is this bad when it achieves the same objectives while using lesser code? The answer has to do with hacking.
57084_08.qxp 30/01/2004 8:03 PM Page 279 Reading from Databases Many times, when fetching data we simply want to display it as it is, perhaps by binding it to a grid. The DataSet provides a local store of the data, which is often more than we need, so we can use an OleDbDataReader to stream the data directly from the database into the grid. Let's give this a go. Try It Out Using a DataReader 1. Create a new blank ASP.NET page called DataReader.aspx. 2.
57084_08.qxp 30/01/2004 8:03 PM Page 280 Chapter 8 "[Products].[UnitsInStock] FROM [Products]"; System.Data.IDbCommand dbCommand = new System.Data.OleDb.OleDbCommand(); dbCommand.CommandText = queryString; dbCommand.Connection = dbConnection; Once the command details are set, we can 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.
57084_08.qxp 30/01/2004 8:03 PM Page 281 Reading from Databases System.Data.OleDbDataReader dataReader = new _ dbCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection); if (!dataReader.HasRows) Response.Write("No rows found"); else while (dataReader.Read()) Response.Write(dataReader("ProductName") + "
"; 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.
57084_08.
