Manipulating data in ASP.net 2.0 69: Process Computed Columns list _ self-study

Introduction:

There is a computed in Microsoft SQL Server Columns columns. The value of this column is computed by an expression that refers to the value of the other columns of the same table. For example, there is a servicelog table containing serviceperformed, EmployeeID, Rate, Duration and other columns. Although we can calculate the cost per service (that is, the ratio rate times the time period duration) on a Web page or some other interface, we can also manually add one to the Servicelog table AmountDue column to reflect this information. We can create this column as a normal column, but you need to update the AmountDue column's value whenever the value of the rate or duration column changes. A better way is to create amountdue as a computed column, which uses an expression of Rate * Duration. This allows SQL Server to automatically compute the value of the AmountDue column when the column is referenced in a query.

Because the values of the computed column columns are determined by an expression, the column is read-only and cannot be assigned in the INSERT or UPDATE statements. However, for using Ad-hoc SQL Statements's TableAdapter, if the computed column columns are referenced in the main query, the automatically generated insert and UPDATE statements are also automatically referenced computed Column columns. Therefore, we must update the TableAdapter INSERT and update queries, as well as the InsertCommand and UpdateCommand properties, to remove references to any computed column columns.

One of the challenges we face when using computed columns in TableAdapter using AD-HOC SQL statements is that whenever the Tableadapte Setup Wizard is completed, the TableAdapter The INSERT and update queries are automatically generated, and the computed column columns are automatically referenced again. However, if the TableAdapters uses stored procedures, this problem will not occur.

In this article, we will add a computed column to the Suppliers table of the Northwind database, and then create a TableAdapter to process the table and the computed Column columns. We will use stored procedures in TableAdapter instead of Ad-hoc SQL statements.

First step: Add a computed Column to the Suppliers table

In this article, we'll add a computed column named Fullcontactname to the Suppliers table, which takes "ContactName (ContactTitle, CompanyName)" The format returns the name of the contact, title, and the company in which it is located.

Open Server Explorer, right-click on the Suppliers table, and select Open table Definition, which will show the columns that the table contains, as well as the properties of the columns, such as data type, whether to allow null values, and so on. To add a computed column , simply type the name of the table in the table definition, and then enter the expression (shown in Figure 1) in the (Formula) text box in the computed column specification section of the Column Property window. Name the computed column as Fullcontactname and use the following expression:

ContactName + ' (' + case when ContactTitle ' isn't NULL THEN
 contacttitle + ', ' ELSE ' ' end + CompanyName + ') '

Note that in SQL you can use the operator "+" to concatenate strings. Case declarations are similar to conditional statements in traditional programming languages. The case declaration in the code above can be interpreted as follows: If ContactTitle is not NULL, then the output ContactTitle value, followed by a comma, if NULL, there is no action. For more information on case declarations see the article "The Power of SQL Case Statements" (http://www.4guysfromrolla.com/webtech/102704-1.shtml)

  Note: In addition to the case statement, we can also use IsNull (ContactTitle, ""). Syntax IsNull (checkexpression, Replacementvalue) returns works this way, and returns if the checkexpression is not null , or null to return Replacementvalue. Although this article can be used in all 2 of these syntaxes, in some slightly more complex situations, the use of IsNull is a bit more. After adding the computed column, your screen should look like Figure 1:

Figure 1: Add a computed column named Fullcontactname to the Suppliers table

Add the Save icon on the Back Point toolbar, or press the Ctrl+s key, or select Save Suppliers on the File menu. The save operation automatically refreshes Server Explorer. Display the columns that you just added in the Suppliers table. In addition, expressions typed into the (Formula) text box are automatically adjusted to eliminate unnecessary whitespace, enclose column names in [], and use parentheses () To display the Order of operations:

((([contactname]+ ') +case when [ContactTitle] isn't NULL
 then [contacttitle]+ ', ' Else ' ' End ') +[companyname]) ' )

For more information about computed columns columns in Microsoft SQL Server, please refer to the article "Technical Documentation" (http://msdn2.microsoft.com/en-us/ library/ms191250.aspx) at the same time, you can also refer to the article "How To:specify computed Columns" (http://msdn2.microsoft.com/en-us/library/ ms188300.aspx), see How to create Computed columns columns step by step.

  Note: By default, the database table does not contain the computed columns column of "real" (physically), but instead evaluates its value each time it is referenced in a query. However, we can choose the IS Persisted option to let SQL The server actually creates computed columns columns in the database table. So we can create an index for the computed column columns, when using computed in a query's WHERE clause Column columns, you can increase execution efficiency. For more information, see the article "Creating Indexes on Computed Columns" (http://msdn2.microsoft.com/en-us/library/ms189292.aspx)

Step two: View the values of the computed column columns

Before working on the data access layer, let's take a moment to view the value of the Fullcontactname column. In Server Explorer, right-click on the Suppliers table, select New Query, This starts a query window that prompts us which table to include in the query. Add the Suppliers table, and then click Close. Then choose CompanyName, ContactName, ContactTitle from the Suppliers list, and Fullcontactname columns. Finally, click on the red exclamation point icon on the toolbar to execute the query and see the results. As shown in Figure 2, the result contains the Fullcontactname column, which is ContactName (ContactTitle, CompanyName) "Format uses CompanyName, contactname,contacttitle these 3 columns.

Figure 2:fullcontactname Column in the format "ContactName (ContactTitle, CompanyName)"

Step three: Add a supplierstableadapter to the data access layer

In order to process supplier information in our applications, we first need to create a TableAdapter and DataTable at the DAL layer. We can do this by using the previous tutorial approach to create a slightly different we are going to be with computed Deal with the columns column.

If you use Ad-hoc SQL statements to construct a TableAdapter, you can simply refer to TableAdapter column columns in the computed main query through the TableAdapter Setup Wizard, so that The computed column columns are referenced in the auto-generated insert and UPDATE statements. If you execute these 2 methods, you will throw a SqlException: "The column ' ColumnName ' cannot be modified because it is either a computed column or I s the result of a UNION operator. Although we can manually change the INSERT and UPDATE statement in the InsertCommand and UpdateCommand properties, But once the TableAdapter Setup Wizard is rerun, the user customizations we make will be lost.

Because of this instability using Ad-hoc SQL statements TableAdapters, we tend to use stored procedures to process computed columns columns. If you are using an existing stored procedure, you can refer to the 66th chapter, " Configure TableAdapter using the existing stored procedures in TableAdapters. If you use the TableAdapter Setup Wizard to create a stored procedure, it is important to start by not quoting computed in the main query. Columns column, if you have referenced computed columns column in the main query, you have just finished setting up, the wizard will prompt you not to create the corresponding stored procedure. In short, when you set up TableAdapter, don't refer to the main query at the beginning computed column columns, and then make changes to the corresponding stored procedures and TableAdapter SelectCommand properties to reference the computed column columns. This way we use joins in the 67th chapter of the TableAdapters has been discussed.

In this article we will add a new TableAdapter and automatically create the stored procedure. Of course we want to ignore this computed column named Fullcontactname in the main query. Open ~/app_code/ The Northwindwithsprocs DataSet dataset in the Dal folder, right-click in the designer, and select "Add a new TableAdapter", which will open the TableAdapter Setup Wizard to specify database connection information ( Also web.config the northwndconnectionstring in the file), click Next. Select the "Create New stored Procedures" item, and then click Next.

Figure 3: Select the "Create New stored Procedures" Item

Next we want to specify the main query, type the following query, which returns the SupplierID, CompanyName, ContactName, ContactTitle columns for each supplier. Note that we intentionally ignored the computed column columns (that is, the Fullcontactname column). However, we will update the stored procedure in step fourth to refer to the column:

SELECT SupplierID, CompanyName, ContactName, ContactTitle from
suppliers

After entering the main query, click Next, the wizard wants us to name the 4 stored procedures that will be created, named Suppliers_select, Suppliers_insert, Suppliers_update, and Suppliers_delete respectively. As shown in Figure 4:

Figure 4: Naming automatically generated stored procedures

Next, we'll name the TableAdapter method and specify the schema for accessing and updating the data. We all select these 3 items, but rename the GetData method to getsuppliers. Click Finish to complete the configuration.

Figure 5: Renaming the GetData method to Getsuppliers

When you are done, the wizard creates the 4 stored procedures and adds ableadapter and corresponding DataTable to the typed DataSet.

Step Fourth: Reference computed column columns in TableAdapter's main query

Next we will update the TableAdapter and DataTable created in step three to refer to the Fullcontactname column, which takes 2 steps:

1. Update the stored procedure named Suppliers_select to return the Fullcontactname column

2. Update the DataTable to include the corresponding Fullcontactname column

First open the Stored Procedures folder in Server Explorer, open the Suppliers_select stored procedure, and update its select query to refer to the Fullcontactname column:

SELECT SupplierID, CompanyName, ContactName, ContactTitle, fullcontactname from
suppliers

Save your changes. Next go back to DataSet Designer, right-click on Supplierstableadapter, and select Configure. We can see the data in Suppliers_select. The Fullcontactname column is already included in the columns set.

Figure 6: The Set Wizard that returns to TableAdapter updates the columns of the DataTable

Click Finish to complete the setting, which will automatically add the corresponding column for suppliersdatatable. TableAdapter found that fullcontactname column is a column of computed columns, and is read-only. This will set the ReadOnly property of the column to true. We can verify that the column is selected in Suppliersdatatable, and the Properties window is opened (Figure 7), and we notice that the datatype of the Fullcontactname column and The MaxLength property is set accordingly.

Figure 7:fullcontactname Column is labeled Read-only

Step Fifth: Add a Getsupplierbysupplierid method to the TableAdapter

In this article we will present suppliers information in a ASP.net page with updated functionality. In the previous article, we took the specified record from the DAL and returned it as a strongly typed DataTable to the BLL for updates, then passed the updated DataTable back to the Dal, making the database The corresponding changes. To do so, the first step-returning the record to be updated from the DAL-we need to add a method named Getsupplierbysupplierid (SupplierID) to the DAL layer.

Right-click Supplierstableadapter in the DataSet design designer, select Add Query, and then select Create New Stored procedure (refer to the 3 figure). Choose "Select which returns rows" and then click Next.

Figure 8: Selecting the "SELECT which returns rows" Item

Next, specify the query for the method, and type the following code, which retrieves the column returned by a specific supplier as the main query.

SELECT SupplierID, CompanyName, ContactName, ContactTitle, fullcontactname from
suppliers
WHERE SupplierID = @ SupplierID

Next, we'll name the stored procedure Suppliers_selectbysupplierid and click Next.

Figure 9: Naming the stored procedure as Suppliers_selectbysupplierid

In the next interface, all 2 items in the diagram are selected and the Fillby and Getdataby methods are named Fillbysupplierid and Getsupplierbysupplierid respectively.

Figure 10: Naming the TableAdapter method as Fillbysupplierid and Getsupplierbysupplierid

Dot Finish Wizard

Step Sixth: Create a business logic layer business Logic Layer

Before creating a asp.net page, we first add the appropriate method to the BLL. We will create the page in step 7th, which allows us to view and edit the suppliers. So we have to include at least 2 methods in BLL, one to get all the suppliers, one to update a specific supplier .

Create a new class named Suppliersbllwithsprocs in the ~/app_code/bll folder, adding the following code:

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using NorthwindWithSprocsTableAdapters;

[System.ComponentModel.DataObject]
public class SuppliersBLLWithSprocs
{
 private SuppliersTableAdapter _suppliersAdapter = null;
 protected SuppliersTableAdapter Adapter
 {
 get
 {
  if (_suppliersAdapter == null)
  _suppliersAdapter = new SuppliersTableAdapter();

  return _suppliersAdapter;
 }
 }

 [System.ComponentModel.DataObjectMethodAttribute
 (System.ComponentModel.DataObjectMethodType.Select, true)]
 public NorthwindWithSprocs.SuppliersDataTable GetSuppliers()
 {
 return Adapter.GetSuppliers();
 }

 [System.ComponentModel.DataObjectMethodAttribute
 (System.ComponentModel.DataObjectMethodType.Update, true)]
 public bool UpdateSupplier(string companyName, string contactName,
 string contactTitle, int supplierID)
 {
 NorthwindWithSprocs.SuppliersDataTable suppliers =
  Adapter.GetSupplierBySupplierID(supplierID);
 if (suppliers.Count == 0)
  // no matching record found, return false
  return false;

 NorthwindWithSprocs.SuppliersRow supplier = suppliers[0];

 supplier.CompanyName = companyName;
 if (contactName == null)
  supplier.SetContactNameNull();
 else
  supplier.ContactName = contactName;
 if (contactTitle == null)
  supplier.SetContactTitleNull();
 else
  supplier.ContactTitle = contactTitle;

 // Update the product record
 int rowsAffected = Adapter.Update(supplier);

 // Return true if precisely one row was updated, otherwise false
 return rowsAffected == 1;
 }
}

Like other BLL class classes, Suppliersbllwithsprocs has a protected adapter property, 2 public methods: Getsuppliers and Updatesupplier. Where the Getsuppliers method calls the corresponding Getsupplier method of the data Access layer layer, which returns suppliersdatatable to the BLL layer While the Updatesupplier method obtains information about a specific supplier of someone by invoking the Getsupplierbysupplierid (SupplierID) method of the DAL layer, and then updates its categoryname. Contactname,contacttitle property, and then passes the modified Suppliersrow object to the Update method of the data Access layer layer to update the database accordingly.

  Note: All columns in the Suppliers table, except SupplierID and CompanyName, are allowed to be null, so if the ContactName or ContactTitle parameters passed are null, We will call the Setcontactnamenull and Setcontacttitlenull methods individually to set the ContactName and ContactTitle properties to null.

Step seventh: Process computed column columns in the presentation layer

Having done all the necessary work, we will create a asp.net page to process the Fullcontactname column, open the Computedcolumns.aspx page in the Advanceddal folder, drag a GridView control to the page, Set its ID to suppliers and bind it to a ObjectDataSource control named Suppliersdatasource in its smart tag, setting its call to the Suppliersbllwithsprocs class, click Next.

Figure 11: Setting the ObjectDataSource call Suppliersbllwithsprocs class

There are only 2 methods Getsuppliers and Updatesupplier in the Suppliersbllwithsprocs class. Make sure that the 2 methods are selected separately in the Select and Update tabs. Finish Setup. When the settings are complete, Visual studio adds the corresponding BoundField, removes the SupplierID columns, and CompanyName, ContactName, ContactTitle, The HeaderText properties for the Fullcontactname column are set to "Company", "Contact name", "Title", "Full contact name", and then the GridView editing function is enabled.

Visual Studio Sets the OldValuesParameterFormatString property of the ObjectDataSource control to "original_{0}". We want to change it to the default value ' {0} '. So, the declaration code for the GridView and ObjectDataSource controls looks similar to the following:

<asp:GridView ID="Suppliers" runat="server" AutoGenerateColumns="False"
 DataKeyNames="SupplierID" DataSourceID="SuppliersDataSource">
 <Columns>
 <asp:CommandField ShowEditButton="True" />
 <asp:BoundField DataField="CompanyName"
  HeaderText="Company"
  SortExpression="CompanyName" />
 <asp:BoundField DataField="ContactName"
  HeaderText="Contact Name"
  SortExpression="ContactName" />
 <asp:BoundField DataField="ContactTitle"
  HeaderText="Title"
  SortExpression="ContactTitle" />
 <asp:BoundField DataField="FullContactName"
  HeaderText="Full Contact Name"
  SortExpression="FullContactName"
  ReadOnly="True" />
 </Columns>
</asp:GridView>

<asp:ObjectDataSource ID="SuppliersDataSource" runat="server"
 SelectMethod="GetSuppliers" TypeName="SuppliersBLLWithSprocs"
 UpdateMethod="UpdateSupplier">
 <UpdateParameters>
 <asp:Parameter Name="companyName" Type="String" />
 <asp:Parameter Name="contactName" Type="String" />
 <asp:Parameter Name="contactTitle" Type="String" />
 <asp:Parameter Name="supplierID" Type="Int32" />
 </UpdateParameters>
</asp:ObjectDataSource>

	
  

		
   

	
  

Next we log in to the page in the browser, as shown in Figure 12. Each row has a fullcontactname column, formatted as "ContactName (ContactTitle, CompanyName)".

Figure 12: show one supplier per line

Clicking on a row's edit button causes the page to return, and the row is displayed as an editing interface (Figure 13), and the first 3 rows are rendered as the default editing interface-a TextBox control with the Text property of the data field Value. However, the Fullcontactname column is still rendered as a text box. When the Data Source Setup Wizard finishes and adds the corresponding boundfields to the GridView control, Fullcontactname The BoundField ReadOnly property is true. As we noted in step fourth, the ReadOnly property of the Fullcontactname column is true because TableAdapter is aware that the column is a computed column.

Figure 13:fullcontactname Column read-only

Let's change the value of at least one column in these 3 columns and click the Update button. We found that the value of the Fullcontactname column changed accordingly.

  Note: Because the GridView is currently using BoundFields, The default interface is used when editing. Also because the CompanyName column is required, we should convert it to a TemplateField to contain a RequiredFieldValidator control. I leave this as an exercise for the reader, you can refer to the 19th Chapter "Add validation control to edit and add interface", see how to convert BoundField to TemplateField step by step, then add a confirmation control.

Conclusion:

When you create a table, Microsoft SQL Server allows us to create a computed columns column. These computed columns columns refer to the other columns of the record, It is then assigned a value by an expression. Because the value originates from an expression, the column is read-only and cannot be assigned by an INSERT or UPDATE statement. It is so that when you refer to the computed in a TableAdapter main query column, to automatically generate the corresponding insert, UPDATE, and DELETE statements a bit troublesome.

In this article, we explored the challenges of using the computed columns column. Specifically, because of the inherent instability of TableAdapters itself using the Ad-hoc SQL statements, We explored the use of stored procedures. When using the TableAdapter Wizard to create a new stored procedure, it is important to start by not referencing any computed columns in the main query. Otherwise, the corresponding stored procedure cannot be generated automatically. After completing the wizard, we will manually modify the SelectCommand property to refer to the computed columns column.

I wish you a happy programming!

Introduction of the author

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.