Accessing Data with Code Builders


Web Matrix provides a set of four useful Wizards named code builders. These can create sections of code automatically, based on a few options that we specify in the Wizard dialogs. The resulting code is a method (a function) that we can use in our page.

The four code builders are:

  • SELECT Data Method – Creates a function that uses a SQL SELECT statement to access a database table and returns the data as either a DataReader or a DataSet.

  • INSERT Data Method – Creates a function that uses a SQL INSERT statement to insert new rows into a database table and returns the number of rows that were inserted (one or zero).

  • DELETE Data Method – Creates a function that uses a SQL DELETE statement to delete one or more rows from a database table and returns the number of rows that were deleted.

  • UPDATE Data Method – Creates a function that uses a SQL UPDATE statement to update one or more rows in a database table and returns the number of rows that were updated.

The code builders are available on the Toolbox when you have an ASP.NET page open in Code view in the Web Matrix editor window. To start a code builder, you simply drag it from the Toolbox onto the page.

Displaying Data in a DataGrid Control

We'll use the SELECT Data Method code builder in our next example to create a method that returns a DataReader, and then use this to populate an ASP.NET DataGrid control.

Try It Out —Using the SELECT Data Method to Populate a DataGrid

  1. Starting with a blank new ASP.NET page called SelectDataMethod.aspx, click the Code tab at the bottom of the editor window to switch to Code view. Find the SELECT Data Method in the Code Builders section of the Toolbox, click on it, and drag it onto the page:

    click to expand

  2. The SELECT Data Method Wizard starts up, and the first thing you see is the Connect to Database dialog. This is the same dialog as we used in the previous chapters to create a connection to a database in the Data window. Enter the name of your database server – probably (local)\NetSDK – and select the CAM database in the drop-down list at the bottom of the dialog:

    click to expand

  3. Next comes the Query Builder dialog. Here, you specify which table from the database to use, which columns should be returned from the method, and, optionally, any criteria that will select individual rows (this is used in the WHERE clause of the SQL statement that the Query Builder creates). We only have one table, named Discs, in our database, and this is selected by default. Select the asterisk (all columns) entry in the top list, and leave the WHERE clause section empty so that we get all the rows returned:

    click to expand

  4. Click the Next button, and the Query Builder now allows you to test the query that you have created. We haven't specified any WHERE clause, so when you click the Test Query button you'll get all the rows from the database table:

    click to expand

  5. Click Next again to go to the last page of the Wizard.

    click to expand

    Here, you specify the name of the method, and select the type of data access object you want it to return. Enter AllDiscs for the method name, and select the DataReader option, then click Finish:

  6. The Code window in Web Matrix now contains the complete data-access method. You can see that it is a Function named AllDiscs, and that it returns a DataReader object:

    click to expand

    Not all of the code is visible in this window. The code that Web Matrix creates (with line breaks added so that it fits on the page) is:

     Function AllDiscs() As System.Data.SqlClient.SqlDataReader  Dim connectionString As String = _  "server='(local)\netsdk'; trusted_connection=true; Database='CAM'"  Dim sqlConnection As System.Data.SqlClient.SqlConnection =  New System.Data.SqlClient.SqlConnection(connectionString)  Dim queryString As String = "SELECT [Discs].* FROM [Discs]"  Dim sqlCommand As System.Data.SqlClient.SqlCommand = _  New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)  sqlConnection.Open  Dim dataReader As System.Data.SqlClient.SqlDataReader = _  sqlCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)  Return dataReader End Function 

    This function, named AllDiscs(), accepts no parameters, and returns a DataReader that references all the rows in the Discs table from our example database. It does this by creating a Connection object, using this to create a Command object, and then calling the ExecuteReader() method of the Command object to get back a DataReader. Notice that Web Matrix references them using the full namespace name and class name each time, instead of Importing the namespaces that contain the data-access objects into the page.

  7. Now we can use this function to extract the data we want, and bind it to a list control that will display it. Switch to Design view in the main Web Matrix editor window, and find the DataGrid control in the Web Controls section of the Toolbox. Select it, and drag it onto the page.

  8. This adds the DataGrid to the page, with a default set of property values. You can see in the following screenshot that the ID is set to DataGrid1. Find the DataSource property in the Properties window, and type in the name of the function we just created, AllDiscs():

    click to expand

  9. Setting the DataSource property connects the AllDiscs() function to the DataGrid, but this is not enough to display the data in the page. Unlike the MxDataGrid we used in the previous chapters, we have to tell the generic ASP.NET list controls when they should carry out the data-binding process to display the data. We do this by calling their DataBind() method, and the usual place to call this method is during the Page_Load event. Go to the drop-down list right at the top of the Properties window, which displays the ID of the currently selected item, and select the Page object:

  10. Now click the Events icon just below it to display the events that are available for the Page itself:

  11. We want to handle the Load event for the Page object, so double-click on the entry for the Load event. This switches the editor window to Code view, and inserts an empty event handler for the Page_Load event. Add the following highlighted line of code to it, which calls the DataBind() method of our DataGrid control:

      Sub Page_Load(sender As Object, e As EventArgs)  DataGrid1.DataBind()   End Sub
  12. Click the Start icon in the main Web Matrix toolbar to run the page. You'll see the contents of the Discs table displayed in the page by the DataGrid control:

    click to expand

  13. The output doesn't look as attractive as that we got from our MxDataGrid control. This is because Web Matrix automatically sets some default values for the style and formatting of the MxDataGrid control when we dragged a table from the Data Explorer onto our page in the examples in the previous chapter. However, we can easily change the appearance of the DataGrid control using the built-in Auto Format Wizard that is provided with Web Matrix. With the DataGrid selected in Design view, click the Auto Format... link at the foot of the Properties window:

  14. The Auto Format dialog opens. In it, you can select from several pre-defined styles for the grid, and also preview each one. Here, we've selected the Colorful 1 style:

    click to expand

  15. Click OK in the Auto Format dialog, and you'll see the DataGrid control in the page updated to the new style. Then click the Start button to run the page again. The output from the grid control is presented in the selected style:

    click to expand

Selecting the Rows to Display

The previous example demonstrates how easy it is to use one of the "generic" list controls from the .NET Framework class library. We used a DataGrid control, but the principles are much the same for other list controls. We also took advantage of the WebMatrix SELECT Data Method code builder to create the data access code for us.

However, there are some more advanced topics that we need to look at, and we'll do this next. We'll use the same techniques as those you've just seen, but build a page with some important differences:

  • We'll allow the user to enter a criterion (search string) and display only the rows that contain this value in a specified column.

  • We'll use a DataSet as the source data object, rather than a DataReader as we did in the previous example.

  • We'll display only a few specific columns, rather than all of them as we've done before.

We'll use the example pubs database that comes with the .NET SDK in this example. The titles table in this database contains 18 rows, so there are plenty of different things in the title column of each row that we can search for, and each row has some columns that we do not want to display in our page.

Try It Out—Selecting Rows that Contain a Search String

  1. Start with a new blank ASP.NET page, call it SelectingRows.aspx, switch to Code view, and drag the SELECT Data Method code builder from the Code Builders section of the Toolbox onto the page. In the Connect to Database dialog, specify your database server and select the Pubs database.

  2. Click OK, and in the Query Builder dialog select the titles table from the list of tables in the pubs database. The columns in this table are shown in the Columns list. Instead of checking * and displaying all the columns as we did in the previous example, select just the title, price, notes, and pubdate columns:

    click to expand

  3. Now we want to add a WHERE clause that will limit the rows that are returned by our new data-access method to a specific subset. Click the WHERE button in the Query Builder dialog to open the WHERE Clause Builder dialog. Select the title column in the Column list, select like in the Operator drop-down list, and change the Filter to @search:

    click to expand

  4. Click OK to return to the Query Builder dialog. The combination of settings we specified creates a WHERE clause that will select only rows where the value in the titles column is the same as the value we provide for the @search parameter when we call the new data access method we're building. The LIKE operator in SQL is used to compare two String values. You can see the WHERE clause in the Query Builder dialog, together with the complete SQL statement that will be used in the method:

click to expand

  1. Click Next to go to the next page of the Query Builder, and click the Test Query button. Since there is a parameter required for the method, the Preview window opens (it didn't in the previous Try It Out because there was no parameter specified for the method). The Preview window displays the SQL statement, and prompts for the value to use for the @search parameter. Enter %computer%, which will select only rows where the title column contains the string computer (remember that the percent character is a wildcard in SQL):

    click to expand

  2. Click OK in the Preview dialog, and Query Builder displays the matching rows. You can see that it found five matches:

    click to expand

  3. Click Next in the Query Builder, and now we have to specify the name of the method and the kind of data-access object we want returned. Enter FindByTitle for the name, and select the DataSet option. Then, click Finish to create the new method in the page:

    click to expand

  4. Now you can see the code that the wizard created in the page:

    click to expand

How It Works

The code that has been created is similar to that created by the SELECT Data Method code builder in the previous Try It Out, but with some important differences. We've listed it below, adding line breaks to make it easier to see. This time, the function expects us to provide the value for a parameter named search, and it returns a DataSet rather than a DataReader:

 Function FindByTitle(ByVal search As String) As System.Data.DataSet  Dim connectionString As String = _  "server='(local)\netsdk'; trusted_connection=true; Database='pubs'"  Dim sqlConnection As System.Data.SqlClient.SqlConnection = _  New System.Data.SqlClient.SqlConnection(connectionString)  Dim queryString As String = "SELECT [titles].[title], [titles].[price]," _  & "[titles].[notes], [titles].[pubdate] " _  & "FROM [titles] WHERE ([titles].[title] like @search)"  Dim sqlCommand As System.Data.SqlClient.SqlCommand = _  New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)  sqlCommand.Parameters.Add( _  "@search", System.Data.SqlDbType.NVarChar).Value = search  Dim dataAdapter As System.Data.SqlClient.SqlDataAdapter = _  New System.Data.SqlClient.SqlDataAdapter(sqlCommand)  Dim dataSet As System.Data.DataSet = New System.Data.DataSet  dataAdapter.Fill(dataSet)  Return dataSet End Function 

Inside the function, you can see that a Connection object is created:

  Dim connectionString As String = _     "server='(local)\netsdk'; trusted_connection=true; Database='pubs'"   Dim sqlConnection As System.Data.SqlClient.SqlConnection = _     New System.Data.SqlClient.SqlConnection(connectionString)

Then, using this and the SQL statement that the Wizard generated, a Command object is created:

  Dim queryString As String = "SELECT [titles].[title], [titles].[price]," _     & "[titles].[notes], [titles].[pubdate] " _     & "FROM [titles] WHERE ([titles].[title] like @search)"   Dim sqlCommand As System.Data.SqlClient.SqlCommand = _     New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

Next, the code adds a parameter named @search to the Command object, using the value that is provided in the search parameter to the method:

  sqlCommand.Parameters.Add( _     "@search", System.Data.SqlDbType.NVarChar).Value = search

Then, a DataAdapter object is created using this Command object. When we create a DataAdapter in code, as in the FindByTitle() method shown above, the Command object specified in the constructor (the parameter sqlCommand in our case) is used to set the SelectCommand property of the DataAdapter.

  Dim dataAdapter As System.Data.SqlClient.SqlDataAdapter = _     New System.Data.SqlClient.SqlDataAdapter(sqlCommand) 

Finally, the code calls the Fill() method of the DataAdapter to pull the values from the database and push them into a new DataSet object, after which the DataSet is returned from the method.

  Dim dataSet As System.Data.DataSet = New System.Data.DataSet   dataAdapter.Fill(dataSet)

Notice that the method uses type-safe parameters. What this means is that, when the function is called, the data type of the values that are passed as parameters (in our case there is only one – the search parameter) must be correct. As long as they are correct, they will automatically match the data type of the column in the database table. This helps to prevent errors where you might call the method with the wrong types of values in the parameters.

We'll use this method in our next Try It Out, so leave the page open in Web Matrix for the moment.

Displaying Selected Rows in a DataGrid

OK, so we've got a method that will return a DataSet containing specific rows, depending on the value we provide for the parameter of the method. This parameter is a string value that equates to the value of the title column in the titles table in the pubs example database.

What do we do with it now? It's like being all dressed up with nowhere to go. So we had better get on and build a page that uses this method. We'll provide the user with a textbox where they can enter a value for the parameter, and a button to start the process. We'll then use our new method to select just the matching rows, and display them in a DataGrid control.

Try It Out—Using the FindByTitle() Method in an ASP.NET Page

  1. With the page we created in the previous Try It Out open, switch to Design view. Type Find: and a space at the top left of the page, then drag a TextBox control from the Toolbox onto the page. Type a space and then drag a Button control onto the page. Go to the Properties window and change the Text property of the Button control to Go. Then, back on the page, press Return to start a new "line" and drag a DataGrid control onto the page:

    click to expand

  2. Now we need to write some code that will run when the Go button is clicked. Double-click the button to switch the editor window to Code view, and the outline of the event handler for the Click event of the Button control is inserted:

     Sub Button1_Click(sender As Object, e As EventArgs) End Sub 
  3. When the button is clicked we want to set the DataSource property of the DataGrid control. In the previous Try It Out, we set this in the Properties window of the DataGrid control, but we can't do that here because we need to set the value of the search parameter. Our code extracts this value from the textbox, by accessing its Text property, and places it in a String variable named sTitle. Then, we can set the DataSource property of the DataGrid control to the result of executing the FindByTitle() method with this value as the parameter:

    Sub Button1_Click(sender As Object, e As EventArgs)  Dim sTitle As String = TextBox1.Text  DataGrid1.DataSource = FindByTitle(sTitle)  DataGrid1.DataBind() End Sub

    Notice that we also have to call the DataBind() method of the DataGrid to tell it to generate the output that creates the display in the page.

  4. Click the Start button on the main Web Matrix toolbar to run the page. When first opened, it only displays the textbox and the Go button. This is because the Click event for the Button control has not been raised, and so, the DataGrid has no DataSource property set, and the DataBind() method has not been called. Enter the value %computer% into the textbox and click the Go button. Now the page displays the matching rows:

    click to expand

  5. Well, that was easy enough, but the result isn't very pretty. We could just apply one of the Auto Format designs, like we did in the previous Try It Out, but instead, we'll use another of the tools provided by Web Matrix. In Design view, select the DataGrid control on the page and click the Property Builder... link at the bottom of the Properties dialog:

  6. In the DataGrid1 Properties dialog that opens, select the Format entry on the left hand side of the dialog. The Objects section in the center of the dialog displays a list of things that we can provide format details for. Select the Header entry, and set the values for some of the options in the right-hand section of the dialog. Here, we've specified the foreground and background colors of the text in the header row of the grid, and the font name and size. We've also specified a bold font:

    click to expand

  7. Now select the Normal Items entry within the Items entry, and set some values for this. We have just specified the font name, Arial Narrow, and size, X-Small, in this case. Then, select the Alternating Items entry and set any values you want for alternating rows. We changed the background color to Silver but left the others blank. Any values we don't provide are inherited from the Normal Items entry.

  8. Select the Borders entry in the left-hand side of the dialog and specify the values you want for the borders of the grid. Here, we've specified the cell padding (to separate the row and column contents a little), turned on grid lines, and specified the border color and width:

    click to expand

  9. Click OK to close the DataGrid1 Properties dialog, and the grid in the page shows the effects of the property settings you just applied:

  10. Click the Start icon to run the page and view the results. A lot neater!

    click to expand

Using the Other Code Builders

The other code builders are slightly different from the SELECT data method because, while SELECT is primarily concerned with returning information to the program, DELETE, INSERT, and UPDATE are mainly concerned with transferring data from the program to the database. Here's a quick rundown of how each one works. You've already seen how to use SELECT, and you should feel fairly at home with the others. Give them a try – they are fast, easy, and very useful!

INSERT Data Method

The wizard will ask you to connect to the database in the normal way. It will then ask you to choose which table you want to insert a record into, and whether any of the fields have default values. If the database itself defines default values, then the wizard will detect this… so adding an INSERT method for the CAM database will bring up the following dialog:

click to expand

As you can see, DiscID and ReleaseDate are already selected as having default values. You can add others if you wish. Once you click Next, you'll be asked for a name for the method.

The method you end up with will have arguments that let you specify values for all of the columns that don't have default values, so if we are building an INSERT method from the example above we will get the following method definition:

 Function MyInsertMethod(ByVal title As String, ByVal notes As String,  ByVal label As String, ByVal coverImageURL As String) As Integer 

The return value will be an integer that tells you how many rows were added. Usually this will be 1. Checking the value in the code that calls the function is a useful way to check nothing unexpected happened.

DELETE Data Method

Once again, you need to specify the database connection. Then you have a dialog that lets you choose a table. You also need to build a WHERE clause that specifies exactly what records you want to delete. Let's say we want to build a function that deletes any album made before the specified date. Once we've connected to the database, we'll see the following dialog:

click to expand

First of all we select the Discs table, and click the WHERE button. This will bring up the following:

click to expand

where we can select the Column that will act as the condition, and the filter we will use. Select ReleaseDate in the Column list, < in the Operator dropdown, and leave the Right Operand section as it is (filtering on a SQL variable called @DiscID). Click OK.

Important

It is very important to specify WHERE clauses when deleting or updating a database… of you don not, every single row it the table will be deleted or changed!

Now, if you click Next, you'll be given the chance to test the query by clicking the Test Query button and entering a date. After clicking Next again, you can name the function and finish.

You'll now have a function that takes a date as a parameter, deletes all albums released before that date, and then returns the number of albums deleted – a useful way of making sure that the correct number of records was removed. (If no records match the WHERE clause in a DELETE statement, no error is raised… so it's often useful to check that this value isn't a zero.)

UPDATE Data Method

This is more complicated than the previous code builders because you need to specify the data to place in the database, and a WHERE condition. But we've seen both these on their own, so it's really not too bad. Connect as usual, then specify the columns that you want to update. Let's say we want to add a note to every disc produced before 1990 that there will soon be a remastered CD version with bonus tracks that are even worse than the songs on the album itself.

First of all, we select the Notes column and click OK on the dialog that pops up:

Next we need to specify the WHERE clause, so click the WHERE button and choose ReleaseDate from the Columns list, < from the Operator list, leave the Right Operand filter criterion set to the default value of @ReleaseDate allowing us to specify a date in the program code).

Now click Next, skip the test, and click Finish. You'll get a function that takes a value for Notes as a parameter, and once again returns the number of affected rows. If you chose not to select a value for ReleaseDate, but left it variable, then you'll get a parameter to specify that too.

This is the most complex of the data method code builders. It's worth taking a look at the code and trying to understand broadly what's going on. Even though it uses some objects you aren't familiar with, the general ideas are the same as the code you've already seen – declaring objects, setting properties, and calling methods.

Let's take a quick look at it now (there are a lot of long lines which I've had to break).

 Function MyUpdateMethod(ByVal releaseDate As Date, _  ByVal notes As String) As Integer 

First of all there's the method declaration. Next we define a connectionString, and use it to create a SqlConnection object – which ASP.NET will use to connect to our database:

  Dim connectionString As String = "server='(local)\netsdk'; " & _  "trusted_connection=true; Database='CAM'"  Dim sqlConnection As System.Data.SqlClient.SqlConnection = _  New System.Data.SqlClient.SqlConnection(connectionString) 

Next up we Dim the SQL string itself. We talked about the UPDATE command earlier, so it should be fairly familiar:

  Dim queryString As String = "UPDATE [Discs] SET [Notes]=@Notes" & _  " WHERE ([Discs].[ReleaseDate] < @ReleaseDate)" 

We then use the queryString and the connection to declare a SqlCommand object – this is what we'll use to actually tell the database what we want it to do.

  Dim sqlCommand As System.Data.SqlClient.SqlCommand =  New System.Data.SqlClient.SqlCommand(queryString, sqlConnection) 

The next lines set up the name-value pairs of SQL variable names and the values we want to assign to them. Because they are variables we're passing to SQL, we call them parameters – just as we would if we were passing variables to a procedure or function:

  sqlCommand.Parameters.Add("@ReleaseDate",   System.Data.SqlDbType.DateTime).Value = releaseDate  sqlCommand.Parameters.Add("@Notes",  System.Data.SqlDbType.Text).Value = notes 

Next, the function declares an Integer to hold the number of rows affected by the operation:

  Dim rowsAffected As Integer = 0 

This is where the action happens. We open the connection, and run the query. We do this within a Try block so that, if something goes wrong, the Finally block still executes, and the connection is closed safely:

  sqlConnection.Open  Try   rowsAffected = sqlCommand.ExecuteNonQuery  Finally  sqlConnection.Close  End Try 

Finally we return the rowsAffected variable, and end the function:

  Return rowsAffected End Function 

If you didn't quite understand all that, it really doesn't matter. The reason the code builders exist is that you don't have to understand the details. They're worth getting to grips with, though, if you want to really understand how to get the most out of databases. There's a lot of theory behind all of these code builders that can get quite deep and complex, so for that reason, we recommend that you read Beginning ASP.NET Databases using VB.NET, Wrox Press, ISBN: 1-86100-619-5 for a more in-depth discussion of what's actually going on behind the scenes.




Beginning Dynamic Websites with ASP. NET Web Matrix
Beginning Dynamic Websites: with ASP.NET Web Matrix (Programmer to Programmer)
ISBN: 0764543741
EAN: 2147483647
Year: 2003
Pages: 141

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net