Home>

This article describes and uses tableadapter to add a new column to the datatable.Just re-run the tableadapter setup wizard at any time,All customizations made by the user must be overridden,To avoid this,We recommend modifying the stored procedure directly.

preface:

When a tableadapter is added to a typed dataset, the structure of the corresponding datatable is already defined by the tableadapter's main query.For example, if the main query returns the three fields a, b, and c, then the datatable will have the corresponding three columns a, b, and c. In addition to the main query,tableadapter can also contain other queries,May be returning data based on some parameters.For example, the main query of productstableadapter returns information about all products,In addition, productstableadapter contains methods such as getproductsbycategoryid (categoryid) and getproductbyproductid (productid), which return specific product information based on the assigned parameters.

If the columns returned by the tableadapter method are covered in the main query,No problem working.But if the returned columns are not covered in the main query,Then we need to extend the structure of the datatable.Chapter 35Implement master/slave report with repeater and datalist single pageIn this case, we add methods to the categoriestableadapter to return the categoryid, categoryname, description, and numberofproducts columns. The first 3 columns are covered in the main query.The numberofproducts column is not defined in the main query.It returns the number of related products per category.We can manually add a column to the categoriesdatatable,In order to count the value of the numberofproducts column returned from the new method.

Chapter 52Upload files using fileupload"We discussed,Attention must be paid to tableadapters built with ad-hoc sql statements whose methods return columns outside the scope of the main query.Because once you re-run the setup wizard,It will update the tableadapter method,Make the columns it returns match the main query.This is not the case if stored procedures are used.

In this article we will look at how to extend the datatable's architecture to include additional columns.We all know that the tableadapter using the ad-hoc sql statements framework is unstable. In this article, we will use stored procedures to structure.You can refer to Chapter 65Create new stored procedures in tableadaptersAnd Chapter 66Use existing stored procedures in tableadapters》 To get more information on setting up a tableadapter to use stored procedures.

Step 1:Add a pricequartile column to productsdatatable

In Chapter 67 we created a typed dataset called northwindwithsprocs.The data set currently contains 2 datatables:productsdatatable and employeesdatatable. Which productstableadapter contains 3 methods:

.getproducts-the main query, returns all records in 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

The main query and the other 2 methods return the same data columns,Which is all the columns of the products table,It does not return relevant data for categories and suppliers table.

In this article, we will add a method called productsproductwithpricequartile to productstableadapter, which returns all products.In addition to the standard data columns,It also returns the pricequartile column, which uses the quartile to measure how much the product price has fallen.If the price of the product rises by 25%, its value is 1, and if it drops by 25%, its 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 "column".

Figure 1:Adding a new column to productsdatatable

This will add a new column to the datatable,The name is "column1" and the type is 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. In the productsdatatable, select our new ColumnSet 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 an auto-incrementing column,Whether its value is allowed to be null, etc. But we use its default value here.

Step 2:Create a getproductswithpricequartile method

Now that we have updated the productsdatatable to include the pricequartile column, we are going to create a getproductswithpricequartile method. Right-click on the tableadapter,Then select "add query". This will launch the tableadapter query setup wizard,It first asks if we want to use ad-hoc sql statements or use an existing stored procedure or create a new one.We choose "create new stored procedure" and click next.

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

Next, as shown in Figure 4, the wizard asks what type of query we are adding,Since the getproductswithpricequartile method will return all records and all columns of the products table,We select the "select which returns rows" item and click next.

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

Next, we type the following query into the wizard:

select productid, productname, supplierid, categoryid, quantityperunit, unitprice, unitsinstock, unitsonorder, reorderlevel, discontinued, ntile (4) over (order by unitprice desc) as pricequartile
from products

The above query uses the new ntile function added by SQL Server 2005, which divides the results into 4 groups and groups the unitprice values ​​in descending order.

Unfortunately,The query builder cannot resolve the keyword over and throws an error message.So, type the above code directly into the text box of the wizard,Instead of using the query builder.

Note:For more information about ntile and other functions of SQL Server 2005,You can refer to the articles "returning ranked results with microsoft sql server 2005" () and the "ranking functions section" section of sql server 2005 books online ()

When finished, click next, the wizard will ask us to rename the new stored procedure,We named it products_selectwithpricequartile and click next.

Figure 5:Name the new stored procedure products_selectwithpricequartile

Finally we need to name the tableadapter method,Check "fill a datatable" and "return a datatable" and rename them fillwithpricequartile and getproductswithpricequartile.

Figure 6:Name the tableadapter method and click finish

When a select query is specified and the stored procedure and tableadapter methods are named,Click finish to complete the wizard.Then you will see 1 or 2 warning messages,Say "the over sql construct or statement is not supported." Don't bother about it.

After completing the wizard,The tableadapter will contain fillwithpricequartile and getproductswithpricequartile methods, and the database will contain a stored procedure called products_selectwithpricequartile.Take a moment to verify,Check the database,If you don't see the stored procedure we just added,Right-click on the stored procedure folder,Select "Refresh".

Figure 7:Verify that the new method is added to the tableadapter

Figure 8:Ensure the database contains the products_selectwithpricequartile stored procedure

Note:One of the benefits of using stored procedures to replace ad-hoc sql statements is that re-running the tableadapter setup wizard will not change the columns returned by the stored procedure.We can make a verification,Right click on tableadapter,Select the "configure" option to start the wizard,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 are using ad-hoc sql statements, re-running the wizard will make the columns returned by the query match the columns of the main query,So it will delete the ntile statement in the query used in the getproductswithpricequartile method.

When the getproductswithpricequartile method of the data access layer is called, the tableadapter will execute the products_selectwithpricequartile stored procedure,Add a corresponding row to the productsdatatable for each record returned. The data fields returned by the stored procedure will be mapped to the columns of the productsdatatable. Because the stored procedure returns a pricequartile data field, its value will be assigned to the productsdatatable Pricequartile column.

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 i want to specify a different value,Just change the defaultvalue attribute,But it must be a valid value (for example, the value of the pricequartile column must be a value of type system.int32).

Now we have completed the necessary steps to add additional columns to the datatable,Next we need to create an asp.net page to display the name, price, and price quartile of each product. But we need to update the business logic layer first,To include a method to call the getproductswithpricequartile method of the data access layer. We will update the business logic layer in step 3.Create an asp.net page in step 4.

Step 3:Update the business logic layer

Before we call the newly added getproductswithpricequartile method in the presentation layer,Corresponding methods must be added at 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 other methods,getproductswithpricequartile simply calls the getproductswithpricequartile method of the data access layer and returns its result.

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 for each product. Open the addingcolumns.aspx page in the advanceddal folder, drag a gridview control from the toolbox 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 the getproductswithpricequartile method of the productsbllwithsprocs class, and select "(none ) ".

Figure 9:Setting the objectdatasource to call the productsbllwithsprocs class

Figure 10:Invoking the getproductswithpricequartile method to obtain product information

After completing the setup wizard, Visual Studio will add boundfield or checkboxfield columns to gridview, including pricequartile columns. Remove all columns except productname, unitprice, pricequartile,Format the unitprice column as currency.And put the unitprice and pricequartile columns on the right,Centered. Finally, set the headertext properties of these three columns to "product", "price", and "price quartile". At the same time, the sort function of the gridview control is enabled.

After making the above modifications, The declaration code for the gridview and objectdatasource controls looks similar to the following:

<asp:gridview 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" />
 </asp:boundfield>
 </columns>
</asp:gridview>
<asp:objectdatasource runat="server"
 oldvaluesparameterformatstring="original_ {0}"
 selectmethod="getproductswithpricequartile"
 typename="productsbllwithsprocs">
</asp:objectdatasource>

As shown in Figure 11 when you log in to this page in the browser,We have noticed,The initial products are listed in descending order of price.At the same time, each product has a corresponding pricequartile value. Of course, these data can also be sorted according to other criteria.As shown in Figure 12.

Figure 11:Products are sorted by prices

Figure 12:Products are sorted by name.

Note:only a little code is needed,We can display different colors according to the pricequartile value of each row,For example, a line with a value of 1 is displayed in light green,Lines with a value of 2 are displayed in light yellow,And so on.You can take a moment to implement this feature,If necessary,You can refer to Chapter 11Data-based custom formatting》

Another way-create another tableadapter

As we see in this article,When the column returned by the method added to tableadapter is outside the scope of the main query,We can add the corresponding columns to the datatable.For a tableadapter, if it contains fewer methods that return "extra columns" and not many "extra columns",This way works properly.

In addition to adding columns to the datatable,We can also add another tableadapter to the 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 the dataset, which uses the products_selectwithpricequartile stored procedure as its main query,For asp.net pages that want to get price quartile information,Just call productswithpricequartiletableadapter;pages that don't need to get price quartile information just need to call productstableadapter.

This newly added tableadapters may cause some functionalities and tasks to be duplicated. For example, if those pages displaying pricequartile columns also have insert, update, delete functions enabled,Then it is necessary to set the insertcommand, updatecommand, deletecommand properties of productswithpricequartiletableadapter appropriately.And we have already set these 3 properties of productstableadapter,At this point, there are 2 ways to add, update, and delete products in the database-use the productstableadapter class or productswithpricequartiletableadapter class.

in In the code for download in this article,The northwindwithsprocs dataset contains the productswithpricequartiletableadapter class, which demonstrates these two methods.

to sum up:

In most instances,The data columns returned by all methods of tableadapter are the same,But there are very few methods that return "extra columns" that are not included in the main query. For example, in Chapter 35, "Implement master/slave report with repeater and datalist single page》, We added a method to categoriestableadapter,In addition to returning the columns in the main query, this methodAlso returned a numberofproducts column. And in this article,We looked at adding a method to productstableadapter to return a pricequartile column that was not included in the main query. For this "extra column" returned, we need to add a corresponding column to the datatable.

If you plan to add columns to the datatable manually, we recommend using a stored procedure.If you use ad-hoc sql statements, just re-run the tableadapter setup wizard at any time,All customizations made by the user must be overridden.This is not the case with stored procedures.

Happy programming!

About the Author

  • Previous 64 Manipulating Data in ASPNET 20: GridView Adds Data in Batches
  • Next Analysis of join usage of CI framework database query