Shulou(Shulou.com)06/03 Report--

This article mainly introduces how to add additional columns for DataTable in ASP.NET 2.0. it has a certain reference value, and interested friends can refer to it. I hope you can learn a lot after reading this article.

Step 1: add a PriceQuartile column to ProductsDataTable

In Chapter 67, we created a typed dataset called NorthwindWithSprocs. The dataset currently contains 2 DataTables:ProductsDataTable and EmployeesDataTable. ProductsTableAdapter contains three methods:

.GetProducts-the main query that returns all records of the Products table

.GetProductsByCategoryID (categoryID)-returns all products based on the specified SecretyID value

.GetProductByProductid (productID)-returns all products based on the specified productID value

The main query and the other two methods return the same data columns, that is, all the columns of the Products table, but do not return data related to the Categories and Suppliers tables.

In this article, we will add a method called GetProductsWithPriceQuartile to ProductsTableAdapter, which returns all products. In addition to the standard data column, it returns the PriceQuartile column, which measures the decline in product prices in the quartile. If the product price rises by 25%, the value is 1, and if it falls to 25%, the value is 4. 5%. 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 ProductsDataTable, select Add, and then select Column.

Figure 1: add a new column to ProductsDataTable

This will add a new column to DataTable, named "Column1" and of type System.String. We need to change the name of the column to PriceQuartile and the type to System.Int32 because its value is between 1 and 4. Select our newly added column in ProductsDataTable and set its Name property to "PriceQuartile" and its DataType property to System.Int32 in the properties window.

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 the column is self-incrementing, whether its value is allowed to be NULL, and so on. However, we use its default value here.

Step 2: create a GetProductsWithPriceQuartile method

Now that we have updated ProductsDataTable to include the PriceQuartile column, we are 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 us whether to use ad-hoc SQL statements or use an existing stored procedure or create a new one. Let's choose "Create new stored procedure" and then click Next.

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

Next, as shown in figure 4, the wizard asked us what type of query we added, and since the GetProductsWithPriceQuartile method will return all records and all columns of the Products table, we select the "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 PriceQuartileFROM Products

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

Unfortunately, the query constructor (Query Builder) cannot parse the keyword OVER and throw an error message. Therefore, type the above code directly into the text box of the wizard instead of using the query constructor.

Note: for more information about NTILE and other functions of SQL Server 2005, you can see the article "Returning Ranked Results with Microsoft SQL Server 2005" (http://www.4guysfromrolla.com/webtech/010406-1.shtml) and the "Ranking Functions section" section of SQL Server 2005 Books Online (http://msdn2.microsoft.com/en-us/library/ms189798.aspx))

When you are done, click Next, and the wizard will ask us to rename the new stored procedure. We will name it Products_SelectWithPriceQuartile and click Next.

Figure 5: name the new stored procedure Products_SelectWithPriceQuartile

Finally, we want to name the method of TableAdapter, select "Fill a DataTable" and "Return a DataTable", and rename it to FillWithPriceQuartile and GetProductsWithPriceQuartile.

Figure 6: name the method of TableAdapter and click Finish

When the SELECT query is specified and the stored procedure and the method of TableAdapter are named, click Finish to complete the wizard. You will see one or two warning messages saying "The OVER SQL construct or statement is not supported." Don't worry about it.

After completing the wizard, the TableAdapter will contain the FillWithPriceQuartile and GetProductsWithPriceQuartile methods, and the database will contain a stored procedure called Products_SelectWithPriceQuartile. Take some time to verify, check the database, if you don't see the stored procedure we just added, right-click on the stored procedure folder and select Refresh.

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

Figure 8: make sure the database contains Products_SelectWithPriceQuartile stored procedures

Note: one of the benefits of replacing ad-hoc SQL statements with a stored procedure is that rerunning the TableAdapter setup wizard does not change the columns returned by the stored procedure. We can do a verification, right-click on TableAdapter, select the "Configure" item 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 notice that the column it returns has not changed. If we use ad-hoc SQL statements, rerunning the wizard will match the columns returned by the query to those of the main query, so it will delete the NTILE statement from the query used in the GetProductsWithPriceQuartile method.

When the GetProductsWithPriceQuartile method of the data access layer is called, TableAdapter will hold the Products_SelectWithPriceQuartile stored procedure and add a corresponding row. Exe to ProductsDataTable for each record returned. The data field (data fields) returned by the stored procedure is mapped to the column of 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 the PriceQuartile data field, 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 value of type System.Int32).

Now that we have completed the necessary steps to add additional columns to DataTable, we will create an ASP.NET page showing name, price, and price quartile for each product. However, we need to update the business logic layer to include a method to call the GetProductsWithPriceQuartile method of the data access layer. We will update the business logic layer in step 3 and create an ASP.NET page in step 4.

Step 3: update the business logic layer

Before calling the newly added GetProductsWithPriceQuartile method in the presentation layer, we must add the corresponding method in the business logic layer, open the ProductsBLLWithSprocs class class file, and add the following code:

[System.ComponentModel.DataObjectMethodAttribute (System.ComponentModel.DataObjectMethodType.Select, false)] public NorthwindWithSprocs.ProductsDataTable GetProductsWithPriceQuartile () {return Adapter.GetProductsWithPriceQuartile ();}

Like other methods, GetProductsWithPriceQuartile simply calls the GetProductsWithPriceQuartile method corresponding to the data access layer and returns its results.

Step 4: display Price Quartile information on an ASP.NET page

After modifying the business logic layer, we will create an ASP.NET page to display the price quartile information of each product. Open the AddingColumns.aspx page in the AdvancedDAL folder, drag a GridView control from the toolbox to the page, and set its ID to Products. Bind it to a new ObjectDataSource control named ProductsDataSource in its smart tag, set the control to call the GetProductsWithPriceQuartile method of the ProductsBLLWithSprocs class class, and select "(None)" in the UPDATE, INSERT, and DELETE tags.

Figure 9: setting up ObjectDataSource to call the ProductsBLLWithSprocs class

Figure 10: calling the GetProductsWithPriceQuartile method to get product information

After completing the setup wizard, Visual Studio adds a BoundField or CheckBoxField column for GridView, including the PriceQuartile column. Delete all columns except ProductName and UnitPrice,PriceQuartile, and set UnitPrice as currency format. And put the UnitPrice and PriceQuartile columns on the right, centered. Finally, the HeaderText properties of these three columns are set to "Product", "Price", and "Price Quartile" respectively. At the same time, enable the sorting function of the GridView control.

With the above changes, the declaration code for the GridView and ObjectDataSource controls looks similar to the following:

As shown in figure 11, when you log on to the page in a browser, we notice that the initial products are arranged in descending order of price, and each product has a corresponding PriceQuartile value, which can also be sorted by other criteria, as shown in figure 12.

Figure 11: products are sorted by Prices

Figure 12: products are sorted by name.

Note: with very little code, we can display different colors according to the PriceQuartile value of each line, such as light green for rows with a value of 1, light yellow for rows with a value of 2, and so on. You can take some time to implement this feature, and if necessary, you can see Chapter 11, "Custom formatting based on data."

Another way-- create another TableAdapter

As we have seen in this article, when the column returned by the method added to TableAdapter is out of the scope of the main query, we can add the corresponding column to DataTable. For TableAdapter, this approach works only when it contains fewer methods to return "extra columns" and not many "extra columns".

In addition to adding columns to DataTable, we can also add another TableAdapter to DataSet, which contains methods that need to return "extra columns." As far as this question is concerned, we can add another TableAdapter named ProductsWithPriceQuartileTableAdapter to DataSet, which takes the Products_SelectWithPriceQuartile stored procedure as its main query, and for ASP.NET pages that want to get price quartile information, only need to call ProductsWithPriceQuartileTableAdapter; pages that do not need to get price quartile information only need to call ProductsTableAdapter.

This additional addition of TableAdapters may lead to duplication of some functionality and task. For example, if the insert and update,delete functions are also enabled for pages that display PriceQuartile columns, then the InsertCommand and UpdateCommand,DeleteCommand properties of ProductsWithPriceQuartileTableAdapter should be set appropriately. Now that we have set these three properties of ProductsTableAdapter, there are two ways to add, update and delete products in the database-using the ProductsTableAdapter class or the ProductsWithPriceQuartileTableAdapter class.

Thank you for reading this article carefully. I hope the article "how to add extra columns for DataTable in ASP.NET 2.0" shared by the editor will be helpful to you. At the same time, I also hope you will support us and follow the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.