Home>

When SQL Server creates a table, it allows us to create computed columns. The value source and expression of such columns.And is read-only,So we cannot assign values ​​to it through insert and update,This article specifically explains the methods and steps for creating and calling displays.

preface:

Microsoft SQL Server has a computed columns column. The value of this column is calculated by an expression.The expression references the values ​​of other columns of the same table.For example,There is a servicelog table, which contains columns such as serviceperformed, employeeid, rate, duration, etc. Although we can calculate the cost of each service in a web page or any other interface (that is, the ratio rate times the duration), but we It is also possible to manually add an amountdue column to the servicelog table to reflect that information.We can create this column as a normal column,Just whenever the value of the rate or duration column changes, you need to update the value of the amountdue column. A better way is to create amountdue into a computed column column using the expression rate * duration. In this way, when a When the column is referenced in the query, SQL Server can automatically calculate the value of the amountdue column.

As the value of the calculated column is determined by the expression,So this column is read-only,It cannot be assigned in insert or update statements.However, for a tableadapter using ad-hoc sql statements, if the calculated query column is referenced in the main query, the automatically generated insert and update statements will also automatically reference the calculated column. Therefore, we must update the tableadapter's insert and update query, and insertcommand and updatecommand attributes to remove references to any computed column.

If you use computed columns in a tableadapter that uses ad-hoc sql statements, one of the challenges we will face is,Whenever the tableadapte setup wizard is completed,Tableadapter insert and update queries are automatically generated,Automatically reference the computed column again. But if tableadapters use stored procedures,This problem does not occur.

In this article, we will add a calculated column to the suppliers table in the northwind database, and then create a tableadapter to handle the table and the calculated column accordingly. We will use stored procedures in tableadapter instead of ad-hoc sql statements.

Step 1:Add a computed column to the suppliers table

In this article, we will add a computed column named fullcontactname to the suppliers table, which returns the name, title, and company of the contact in the format "contactname (contacttitle, companyname)".

Open Server Explorer,Right-click on the suppliers table,Select "open table definition", this will show the columns contained in the table and their attributes,Such as data type, whether to allow null values, etc. To add a computed column, simply type the name of the table in the table definition,Next, enter an expression in the formula text box in the calculated column specification section of the column property window (see Figure 1). Name the computed column fullcontactname and use the following expression:

contactname + "(" + case when contacttitle is not null then
 contacttitle + "," else "" end + companyname + ")"

Please note that you can use the operator "+" in sql to concatenate strings.A case statement is similar to a conditional statement in a traditional programming language.The case statement in the above code can be understood as follows:if contacttitle is not null, then the value of contacttitle is output, followed by a comma;If null, no operation.For more information about case statements, see the article "the power of sql case statements" ()

Note:In addition to the case statement, we can also use isnull (contacttitle, ""). The syntax isnull (checkexpression, replacementvalue) returns works like this,If checkexpression is not null, return it;If null, replacement value is returned. Although both syntaxes can be used in this article,But in some more complicated cases,Use isnull more often.After adding the calculated column, your screen should look similar to Figure 1:

Figure 1:Adding a computed column named fullcontactname to the suppliers table

After adding, click the save icon on the toolbar, or press ctrl + s, or select "Save suppliers" in the file menu. The "Save" operation will automatically refresh the server resource manager,The newly added column is displayed in the suppliers table. In addition, the expression typed into the (formula) text box will be automatically adjusted,Remove unnecessary whitespace,Enclose column names in [] and use parentheses () to show the order of operations:

(((([contactname] + "(") + case when [contacttitle] is not null
 then [contacttitle] + "," else "" end) + [companyname]) + ")")

Note:By default,The database table does not "physically" contain computed columns, but recalculates its value each time it is referenced in a query.However, we can select the "is persisted" option to let SQL Server create the computed columns column in the database table. In this way, we can create an index for the computed column column.When using the value of the calculated column in the where clause of a query, the execution efficiency can be improved.For more information, see the article "creating indexes on computed columns" ()

Step 2:View the value of the calculated column

deal with Before processing the data access layer,Let's take a moment to see the value of the fullcontactname column. In Server Explorer,Right click on the suppliers table,Select "new query", this will launch a query window prompting us which table to include in the query.Add the suppliers table and click "close". Next select the companyname, contactname, contacttitle, and fullcontactname columns from the suppliers table. Finally, click the red exclamation mark icon on the toolbar to execute the queryView Results.As shown in Figure 2, the result contains the fullcontactname column, which uses the companyname, contactname, contacttitle columns in the format contactname (contacttitle, companyname).

Figure 2:The format of the fullcontactname column is "contactname (contacttitle, companyname)"

Step 3:Add a supplierstableadapter to the data access layer

In order to process supplier information in our application, we first need to create a tableadapter and datatable in the dal layer. We can use the methods discussed in the previous tutorial to create,Slightly different is that we will be dealing with computed columns.

If you use ad-hoc sql statements to construct a tableadapter, you can simply refer to the calculated column in the tableadapter's main query through the tableadapter setup wizard. In this way, the automatically generated insert and update statements will reference the calculated column. . If you implement these 2 methods,This will throw a sqlexception:"the column" columnname "cannot be modified because it is either a computed column or is the result of a union operator". Although we can manually change the insert and update statement in the insertcommand and updatecommand attributes , But once you re-run the tableadapter setup wizard,The user customizations we make will be lost.

Due to this instability of tableadapters using ad-hoc sql statements,We tend to use stored procedures to handle computed columns. If you are using an existing stored procedure,You can read Chapter 66Use existing stored procedures in tableadapters"Configure the tableadapter like that. If you use the tableadapter setup wizard to create a stored procedure,It ’s important to start withYou should not reference the calculated columns column in the main query. If you reference the calculated columns column in the main query, you have just completed the setup.The wizard will prompt you that you cannot create the corresponding stored procedure.in short,When setting the tableadapter, do not reference the calculated column in the main query at first, and then change the corresponding stored procedure and tableadapter's selectcommand attribute to reference the calculated column. This method is described in Chapter 67.Using joins in tableadaptersDiscussed in ``.

In this article, we will add a new tableadapter and automatically create a stored procedure.Of course, we need to ignore this computed column named fullcontactname in the main query. Open the northwindwithsprocs dataset in the ~/app_code/dal folder, and right-click in the designer.Select "add a new tableadapter", this will launch the tableadapter setup wizard,Specify the database connection information (that is, the northwndconnection string in the web.config file), and click next. Select the "create new stored procedures" item and click next.

Figure 3:Select the "create new stored procedures" item

Next we want to specify the main query,Type the following query,It returns the supplierid, companyname, contactname, contacttitle columns for each supplier. Note that we deliberately ignored the calculated column (ie, the fullcontactname column). However, we will update the stored procedure to reference the column in step four:

select supplierid, companyname, contactname, contacttitle
from suppliers

After entering the main query, click next. The wizard asks us to name the 4 stored procedures that will be created.Named suppliers_select, suppliers_insert, suppliers_update, and suppliers_delete. As shown in Figure 4:

Figure 4:Naming automatically generated stored procedures

Next we want to name the tableadapter's method and specify the schema used to access and update the data.We selected all three, but renamed the getdata method to getsuppliers. Click finish to complete the configuration.

Figure 5:Rename the getdata method to getsuppliers

After completion, the wizard will create these 4 stored procedures,And add the availableadapter and the corresponding datatable to the typed dataset.

Step 4:Reference the calculated column in the main query of the tableadapter

Next we will update the tableadapter and datatable created in step 3 to reference the fullcontactname column, which goes through 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 procedure folder in Server Explorer,Open the suppliers_select stored procedure,Update its select query to reference the fullcontactname column:

select supplierid, companyname, contactname, contacttitle, fullcontactname
from suppliers

Save your changes.Next go back to the dataset designer, right click on supplierstableadapter,Select "configure". We can notice that the data contact set in suppliers_select already contains the fullcontactname column.

Figure 6:Return to tableadapter's setup wizard to update datatable columns

Click finish to complete the setup,This will automatically add corresponding columns to the suppliersdatatable.The tableadapter found that the fullcontactname column is a computed column column and is read-only.Therefore, the readonly property of the column will be set to true. We can verify:select the column in the suppliersdatatable,Open its properties window (as shown in Figure 7), we notice that the datatype and maxlength properties of the fullcontactname column are set accordingly.

Figure 7:The fullcontactname column is marked read-only

Step 5:Add a getsupplierbysupplierid method to the tableadapter

In this article we will display supplier information in an asp.net page with update function. In the previous article,We get the specified record from dal and return it to bll as a strongly typed datatable for update,Then pass the updated datatable to dal and make corresponding changes to the database.To do this, the first step-returning the record to be updated from dal-we need to add a method called getsupplierbysupplierid (supplierid) to the dal layer.

Right Right-click supplierstableadapter in the dataset design designer, select "add query", and then select "create new stored procedure" (refer to Figure 3). Then select "select which returns rows" and click next.

Figure 8:Select the "select which returns rows" item

Next specify the query for the method,Type the following code,It will retrieve the columns returned by a specific supplier like the main query.

select supplierid, companyname, contactname, contacttitle, fullcontactname
from suppliers
where [email protected]

Next we named the stored procedure supplier_selectbysupplierid, click next.

Figure 9:Name the stored procedure supplier_selectbysupplierid

In the next interface,Select all 2 items in the figure, and name the fillby and getdataby methods as fillbysupplierid and getsupplierbysupplierid, respectively.

Figure 10:Name the tableadapter methods fillbysupplierid and getsupplierbysupplierid

Click Finish to complete the wizard

Step 6:Create a business logic layer

create Before creating an asp.net page, we must first add the corresponding method in bll.We will create the page in step 7,It allows us to view and edit suppliers. Therefore, we must include at least 2 methods in bll, one to get all suppliers, and one to update a specific supplier.

Create a new class named suppliersbllwithsprocs in the ~/app_code/bll folder, add the code as follows:

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 and two public methods:getsuppliers and updatesupplier. Among them, the getsuppliers method calls the getsupplier method corresponding to the data access layer layer, which returns the suppliersdatatable to the bll layer;and the updatesupplier method Get the supplier's specific supplier information by calling the getsupplierbysupplierid (supplierid) method of the dal layer, then update its categoryname, contactname, contacttitle attributes, and then pass the modified suppliersrow object to the update method of the data access layer layer to do the database Update accordingly.

Note:Except for supplierid and companyname, all columns of the suppliers table are allowed to be null values, so if the passed contactname or contacttitle parameters are null, we will call the setcontactnamenull and setcontacttitlenull methods to set the contactname and contacttitle properties to null.

Step 7:Process the computed column in the presentation layer

After doing all the necessary work, We will create an asp.net page to handle the fullcontactname column, open the computedcolumns.aspx page in the advanceddal folder, and drag a gridview control to the page,Let its id be suppliers, and in its smart tag bind to an objectdatasource control named suppliersdatasource, set it to call suppliersbllwithsprocs class, click next.

Figure 11:Set objectdatasource to call suppliersbllwithsprocs class

only In the suppliersbllwithsprocs class, there are only two methods, getsuppliers and updatesupplier. Make sure that these two methods are selected in the select and update tabs respectively, and click finish to complete the setting.After setting up,Visual Studio will add the corresponding boundfield, remove the supplierid column, and set the headertext attributes of the companyname, contactname, contacttitle, and fullcontactname columns to "company", "contact name", "title", "full contact name", and then Enable gridview editing.

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 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 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 this page in the browser,As shown in Figure 12. Each row has a fullcontactname column in the format "contactname (contacttitle, companyname)".

Figure 12:Each supplier shows a supplier

Clicking the edit button on a row will cause the page to postAnd this line is displayed as an editing interface (as shown in Figure 13), the first three lines are presented as the default editing interface-a textbox control, and its text property is the value of the data field. A text box.When the data source setup wizard is complete and the corresponding boundfields are added to the gridview control, the readonly property of the fullcontactname boundfield is true. We noticed in step four,The readonly property of the fullcontactname column is true because tableadapter realizes that the column is a computed column.

Figure 13:The fullcontactname column is read-only

We change the value of at least one of these 3 columns,Click the update button. We find that the value of the fullcontactname column changes accordingly.

Note:Because gridview currently uses boundfields, the default interface is used when editing.Since the companyname column is required,We should turn it into a templatefield to contain a requiredfieldvalidator control. I leave this as an exercise for the reader,You can refer to Chapter 19Added validation controls for editing and adding interfaces》, See how to convert the boundfield to templatefield step by step, and then add a confirmation control.

Conclusion:

When creating 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,Assign it to another expression.Since its value comes from an expression,So this column is read-only,It cannot be assigned by insert or update statement.exactly,When referring to a computed column in a tableadapter's main query, it is a bit cumbersome to automatically generate the corresponding insert, update, and delete statements.

In this article, we explored the challenges of using computed columns.Specifically,Due to the inherent instability of tableadapters using ad-hoc sql statements,We explored the use of stored procedures.When using the tableadapter wizard to create a new stored procedure,The important point is thatAt first, do not reference any computed columns in the main query, otherwise you cannot automatically generate the corresponding stored procedure.After completing the wizard,We want to manually modify the selectcommand attribute to reference the calculated columns.

Happy programming!

About the Author

  • Previous 67 Manipulating Data in ASPNET 20: Using JOINs in TableAdapters
  • Next 64 Manipulating Data in ASPNET 20: GridView Adds Data in Batches