Manipulating data 68 in asp.net 2.0: Adding extra columns to the DataTable self-study process

Introduction:

When you add a TableAdapter to a typed dataset (Typed DataSet), the schema of the corresponding DataTable is already defined by the TableAdapter main query. For example, if the main query returns a, b,c these 3 domains, then The DataTable will have corresponding 3 columns A, B, and C. In addition to the main query, TableAdapter can contain other queries, possibly returning data based on some parameters. For example, ProductsTableAdapter's main query returns information for all products, and ProductsTableAdapter also includes such things as Getproductsbycategoryid (CategoryID) and Getproductbyproductid (ProductID) method that returns specific product information based on the assigned parameters.

If the TableAdapter method returns columns that are covered in the main query, there is no problem working. However, if the returned column is not covered in the main query, then we need to expand the schema of the DataTable. In the 35th chapter, "Using Repeater and DataList single page to implement master/report", We add methods to Categoriestableadapter to return CategoryID, CategoryName, Description, and numberofproducts columns. The first 3 columns are covered in the main query, and the Numberofproducts column is not defined in the main query. It returns the number of related products for each category. We can add a column to categoriesdatatable to make it possible to count the values of the Numberofproducts columns returned by the new method.

In the 52nd chapter, "Uploading Files using FileUpload", we explored the use of Ad-hoc SQL Statements built and its method returns columns that exceed the scope of the main query TableAdapters must pay more attention to it. Because once the Setup wizard is rerun, it updates the TableAdapter method so that the columns it returns are matched to the main query. But that doesn't happen if you use stored procedures .

In this article we will examine how to extend the schema of the DataTable to include additional columns. We all know that using AD-HOC SQL statements Architecture TableAdapter instability, this article we will use stored procedures to construct. You can refer to the 65th Chapter, "Creating New stored procedures in TableAdapters" and chapter 66th " Use existing stored procedures in TableAdapters to get more information about setting up TableAdapter using Stored procedures.

First step: Add a pricequartile column to the productsdatatable

In the 67th chapter we created a typed dataset named Northwindwithsprocs. The dataset currently contains 2 datatables:productsdatatable and employeesdatatable. Where ProductsTableAdapter contains 3 methods:

. getproducts--the main query to return all records of the Products table
. Getproductsbycategoryid (CategoryID)--Returns all products based on the specified CategoryID value
. Getproductbyproductid (ProductID)--Returns all products based on the specified ProductID value

Both the main query and the other 2 methods return the same data column, all the columns of the Products table, and do not return the relevant data for categories and suppliers tables.

In this article, we'll add a method named Getproductswithpricequartile to ProductsTableAdapter, It returns all the products. In addition to the standard data columns, it returns the Pricequartile column, which measures the price of the product down by four decimal places. If the price of a product rises by 25%, the value is 1, and if it falls to 25%, The value is 4. Before we create a stored procedure to return this information, we first need to update the productsdatatable and add a new column to contain the Pricequartile value returned by the Getproductswithpricequartile method.

Open the Northwindwithsprocs DataSet, right-click on the Productsdatatable, select Add, and then select Column.

Figure 1: Adding a new column to the Productsdatatable

This will add a new column to the DataTable named "Column1" and the type is System.String. We need to change the name of the column to "Pricequartile", The type is changed to System.Int32 because its value is between 1 and 4. In Productsdatatable, select our newly added column and set its Name property to "Pricequartile" in the Properties window. The DataType property is System.Int32.

Figure 2: Setting the name and datatype properties of the new column

As shown in Figure 2, we can also set other properties. For example, whether the value of the column must be unique, if it is a self added column, whether the value is allowed to be null, and so on. But we use its default values here.

Step Two: Create the Getproductswithpricequartile method

Now that we've updated productsdatatable to include the Pricequartile column, we're going to create a getproductswithpricequartile method. Right-click on the TableAdapter and select Add Query. This opens the TableAdapter Query Setup Wizard, which first asks whether we are using Ad-hoc SQL statements or using an existing stored procedure or creating a new stored procedure. We choose Create new stored Procedure ", and then click Next.

Figure 3: Creating a new stored procedure in the TableAdapter wizard

Next, as shown in Figure 4, the wizard asks us what kind of query we are adding, because the Getproductswithpricequartile method returns all the records of the Products table and all the columns, we choose Select which returns rows Item, and then click Next.

Figure 4: The query will be a select Statement that returns multiple rows

Next, we type the following query in the wizard:

SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder,
 ReorderLevel, discontinued,
 Ntile (4) over (order by UnitPrice DESC) as Pricequartile from

The above query uses the new Ntile function function of SQL Server 2005, which divides the results into 4 groups and groups the UnitPrice values in descending order.

Unfortunately, the Query Builder (Builder) cannot parse the keyword over and throw an error message. Instead of using the Query Builder, type the above code directly in the text box of the wizard.

　　Note: For more information about ntile and other functions of SQL Server 2005, you can refer to the article "returning ranked Results with Microsoft SQL Server 2005" ( http://www.4guysfromrolla.com/webtech/010406-1.shtml and the ranking functions section of SQL Server Books Online (http://msdn2.microsoft.com/en-us/library/ Ms189798.aspx)

When you're done, click Next, and the wizard will rename us for the new stored procedure, and we'll name Products_selectwithpricequartile and then click Next.

Figure 5: Naming the new stored procedure as Products_selectwithpricequartile

Finally, we'll name the TableAdapter method, select "Fill a DataTable" and "Return a DataTable" and rename it to Fillwithpricequartile and Getproductswithpricequartile.

Figure 6: Naming the TableAdapter method and Dot finish

When a select query is specified and the stored procedure and TableAdapter methods are named, Finish the wizard. At this point you will see 1 to 2 warning messages saying "the over SQL construct or statement are not supported." Don't bother about it.

When you complete the wizard, the TableAdapter will contain the Fillwithpricequartile and Getproductswithpricequartile methods, and the database will contain a name Products_ A selectwithpricequartile stored procedure. Take a moment to verify, check the database, and if you don't see the stored procedure we just added, right click on the stored Procedure folder and select Refresh.

Figure 7: Verifying that the new method is added to the TableAdapter

Figure 8: Ensuring that the database contains products_selectwithpricequartile stored procedures

　　Note: One of the benefits of using stored procedures to replace AD-HOC SQL statements is that running the TableAdapter Setup Wizard does not change the columns returned by the stored procedure. We can do a validation, right-click on the TableAdapter, select "Configure" to start the wizard, and then click Finish to complete the wizard. Next, we look at the products_selectwithpricequartile stored procedure in the database. We noticed that the columns it returned did not change. If we're using ad-hoc SQL statements, Rerun the wizard will cause the columns returned by the query to match the columns of the main query, so it will remove the Ntile statement in the query used in the Getproductswithpricequartile method.

When the Getproductswithpricequartile method of the data access layer is invoked, TableAdapter holds the products_selectwithpricequartile stored procedure. and add a row to the productsdatatable for each returned record. Data field returned by the stored procedure (Information fields) The column to be mapped to productsdatatable. Because the stored procedure returns a Pricequartile data field, its value is assigned to the Pricequartile column of productsdatatable.

For methods that do not return pricequartile data fields, the value of the Pricequartile column is specified by its DefaultValue property. As shown in Figure 2, the default value is DBNull. If you want to specify a different value, just change the DefaultValue property, but it must be a valid value (for example, the value of the Pricequartile column must be a System.Int32 type value).

Now that we have completed the necessary steps to add additional columns to the DataTable, we will then create a asp.net page to show the name, Price, and the Quartile. However, we need to update the business logic layer first to include a method to invoke the Getproductswithpricequartile method of the data access layer. We will update the business logic layer in step 3rd and create a asp.net page in step 4th.

Step three: Update the business logic layer

Before we invoke the newly added Getproductswithpricequartile method at the presentation layer, we must add the appropriate method to the business logic layer, open the Productsbllwithsprocs class file, and add the following code:

[System.ComponentModel.DataObjectMethodAttribute
 (System.ComponentModel.DataObjectMethodType.Select, false)]
Public northwindwithsprocs.productsdatatable getproductswithpricequartile ()
{
 return Adapter.getproductswithpricequartile ();
}

Just like any other method, Getproductswithpricequartile simply invokes the Getproductswithpricequartile method of the data access layer and returns its result.

Fourth step: Show price quartile information on a asp.net page

Once you have finished modifying the business logic layer, we will create a asp.net page to display the price quartile information for each product. Open the Addingcolumns.aspx page in the Advanceddal folder and drag one from the Toolbox GridView control to the page, Set its ID to products. Bind it to a new ObjectDataSource control named Productsdatasource in its smart tag, set the control to call Productsbllwithsprocs Class Getproductswithpricequartile method, select (None) in Update, INSERT, and delete tags.

Figure 9: Setting the ObjectDataSource call Productsbllwithsprocs class

Figure 10: Calling the Getproductswithpricequartile method to get product information

When you finish the Setup wizard, Visual Studio adds BoundField or CheckBoxField columns to the GridView, including Pricequartile columns. Remove ProductName, Unitprice,pricequartile, and set UnitPrice in currency format. and place the UnitPrice and Pricequartile columns on the right, centered. Finally, set the HeaderText property of these 3 columns to "Product", "Price" and "price quartile". Also enables the collation of the GridView control.

With these modifications, the declaration code for the GridView and ObjectDataSource controls looks similar to the following:

 <asp:gridview id= "Products" runat= "server" allowsorting= "True" autogeneratecolumns= "False" datakeynames= "ProductID" datasourceid= "Productsdatasource" > <Columns> <asp : BoundField datafield= "ProductName" headertext= "Product" sortexpression= "ProductName"/> <asp:boundfield Datafield= "UnitPrice" dataformatstring= "{0:c}" headertext= "Price" htmlencode= "False" sortexpression= "UnitPrice" > <itemstyle horizontalalign= "right"/> </asp:BoundField> <asp:boundfield datafield= "Pricequartile" "headertext=" Price quartile "sortexpression=" Pricequartile "> <itemstyle horizontalalign=" Center "/> </as p:boundfield> </Columns> </asp:GridView> <asp:objectdatasource id= "Productsdatasource" runat= " Server "oldvaluesparameterformatstring=" original_{0} "selectmethod=" Getproductswithpricequartile "TypeName=" Productsbllwithsprocs "> </asp:ObjectDataSource> 

As shown in Figure 11, when you log in to the page in a browser, we notice that the first products are sorted in descending order of price, and each product has a corresponding pricequartile value, which, of course, can be sorted by other criteria, as shown in Figure 12.

Figure 11: Products sorted by prices

Figure 12: The product is sorted by name.

　　Note: With very little code, we can display a different color depending on the pricequartile value of each row, such as a light green for a row with a value of 1, a light yellow for a row with a value of 2, and so on. You can take a moment to implement this feature, and if necessary, you can refer to chapter 11th, "Data-based custom formatting"

Another way--Create another TableAdapter

As we've seen in this article, we can add columns to the DataTable when the column that is added to TableAdapter is outside the scope of the main query. For TableAdapter, if it contains fewer methods for returning "extra columns" and "Extra columns" Not a lot of time, this way to work properly.

In addition to adding columns to the DataTable, we can add additional TableAdapter to the DataSet, which includes methods that need to return the "extra columns" . For the purpose of this question, we can add another TableAdapter named Productswithpricequartiletableadapter to the dataset, which will Products_ Selectwithpricequartile stored procedures as its main query, for the ASP.net page to get the price quartile information, simply call Productswithpricequartiletableadapter A page that does not need to get price quartile information needs to call ProductsTableAdapter only.

This additional newly added TableAdapters may cause some work (functionality), Job (Task) duplication. For example, if the page that shows Pricequartile column also has to enable the INSERT, Update,delete function, Then the InsertCommand of Productswithpricequartiletableadapter, UpdateCommand, The DeleteCommand property is set appropriately. And we have set the 3 properties of the ProductsTableAdapter, then there are 2 ways to add, update, delete the products in the database- Use the ProductsTableAdapter class or the Productswithpricequartiletableadapter class.

In the code that is downloaded, the Productswithpricequartiletableadapter class class is included in the Northwindwithsprocs dataset, demonstrating the 2 methods.

Summarize:

In most cases, all of the TableAdapter's methods return the same data columns, but there are a handful of methods that return "extra columns" that are not included in the main query. For example, in the 35th chapter, "Using Repeater and DataList single pages to achieve master/report", We added a method to Categoriestableadapter, which returns a numberofproducts column in addition to the columns in the main query. In this paper, we examine the class of the ProductsTableAdapter Add a method to return a pricequartile column that is not included in the main query. For this returned "extra column", we need to add a corresponding column to the DataTable.

If you plan to add columns to the DataTable manually, we recommend that you use a stored procedure. If you use Ad-hoc SQL statements, any time you want to run the TableAdapter Setup Wizard again, All customizations made by the user are overwritten. This is not the case with stored procedures.

I wish you a happy programming!

Author Introduction

Scott Mitchell, author of this series of tutorials, has six asp/asp. NET book, is the founder of 4GuysFromRolla.com, has been applying Microsoft Web technology since 1998. You can click to see all Tutorials "[translation]scott Mitchell asp.net 2.0 data tutorial," I hope to learn asp.net help.