Managing Databases with Ado.net

ado| Data | database

Using Ado.net to manage relationships in a database (relation) is simple. Instead of returning a single rowset (rowset) to the Datastore (data store), you can return multiple rowset merges and associate them in a dataset. There are many benefits to using the associated data table (DataTable) in ado.net, including the ability to store data as a hierarchy, easier to update data, the ability to use expressions in columns, and so on.

This article describes the columns (column-based) expressions and computations in ado.net. I will demonstrate the use of aggregate functions on data columns (DataColumn), perform other types of calculations across the entire dataset, and concatenate data columns between data tables. Several examples are also given in the paper.

The total (summing) and average (averaging) in a SQL query is a cliché for you, thanks to the ANSI SQL for aggregate functions similar to sum and Avg. SQL allows columns to be computed, for example, by multiplying the product unit price by quantity to produce an expanded price. Now Ado.net provides a way to extend features outside of these data sources and into the middle or upper layers of n-tier structure applications. With column expressions in Ado.net, you can build your own columns with computed values in the dataset, compute the values of other columns in the same row, and even get values from the parent datasheet or subdatasheet through a data relationship (DataRelation). New techniques for managing data are formed by using column-based expressions and computations in ado.net.

Of course, using expression-based columns, collections, and computations in Ado.net has its advantages and disadvantages. An expression can be used for a single datasheet of a dataset or two data table objects that intersect through a data relationship. This article explains the difference between ado.net and SQL based on expression columns, and what you can get from them. This article discusses a number of operations, including the use of an expression roll up (roll up) and roll down (roll down), which relies on the relationship of the DataRelation object. I'll show you how to set up expressions in a data column object, how to use Datasets and SQL to create aggregate functions, how to roll and Roll fields in a dataset, and how to perform column calculations in a dataset.

An expression in SQL

SQL expressions are available in several formats for different purposes, including string formatting, user-defined functions, and mathematical calculations. An SQL statement contains an expression if the SQL statement connects the first and last names together, calculates the expanded price in order entry order, or contains a user-defined function in SQL Server 2000 to retrieve an order date.

Expressions provide a lot of flexibility for program developers to return values from the collection of rows derived from other fields in the database. Relational and standard databases do not save the expanded price of an order entry, which stores only the unit price and quantity. The extended price can be computed from these values, thus avoiding the data being out of sync. For example, if you store quantity, unit price, and extended price on a single line, you may have a number of 100, a unit price of 7, and an expansion price of 100. This should not happen, but it can happen if you store redundant data. A common guideline for transactional databases is to not store information that can be derived, such as expanding prices.

So there's the stage for the SQL expression show. The extended price can be derived by multiplying the unit value by the number by creating a computed column in the SQL statement. Expand the price can also calculate the account discount rate and other information. The following SQL code demonstrates how to connect a string in an SQL expression. The example connects the last name with the name and returns the full name of the upper case, which is in the front:

Use pubs
Go
SELECT au_fname as FirstName,
au_lname as LastName,
au_lname + ', ' + au_fname as FullName1,
(UPPER (au_fname) + ' + UPPER (au_lname)) As FullName2
From authors
ORDER BY
au_lname,
au_fname

SQL expressions can format strings and perform calculations in the rowset, but to do this, you need to be aware of some problems: if you fill a dataset with the above SQL statement, and the number of rows in the first row is modified, the expression column does not change. For example, if the quantity is 10 and the price is 7, now the quantity becomes 5,extendedprice (10x7) and the data is not synchronized. The main reason for this is that the expression is not passed from an SQL statement to the Ado.net dataset.

Data-Column expressions

An expression can also be defined by ado.net a data column object. As an alternative to calculating the expanded price through SQL statements, data columns can be defined to represent the expanded price. The difference between using an expression in an SQL statement and a data column is that if a field in the expression is modified, the data column automatically updates the field defined by the expression, but the SQL expression does not update the data column.

The following code shows how to populate a dataset's datasheet with an SQL statement and create a new data column that describes the evaluation expression, which uses the other fields in the dataset's unique data table. The default view of the datasheet is then bound to a DataGrid control called Grdorderdetail in asp.net.

This code uses order details to create and populate a dataset. A column is then added to the data table of the dataset, which is used to represent the expression. The column is called ExtendedPrice, the data type is floating-point, and its expression is the product unit price and quantity column. The expression can involve any data column in the data table and get the value from the current data row. For example, if the first line is 10 and the unit price is 7, the expanded Price column will use a value of 70.

An expression in a calculation can include literal values from other fields in the datasheet. For example, an expression that defines an extended price can be changed slightly to consider the discount rate:

ods.tables["OrderDetail"]. Columns.Add ("ExtendedPrice",
typeof (Decimal), "(UnitPrice * Quantity) * (1-discount)");

Try changing the values of UnitPrice, discount, or Quantity data columns, and the result is that unlike columns derived from SQL expressions, ExtendedPrice data columns are also updated. This feature is important to the application, especially if the user can modify the shopping cart, confirm the change, and then view the updated total price.

An expression can also be used to represent other data types, such as logical and character values. The following code demonstrates adding a field to show whether an author has a discount:

ods.tables["OrderDetail"]. Columns.Add ("Getsdiscount", typeof (BOOL),
"Discount > 0");

You can use the and, or or not conditions to create a mixed expression to consider multiple conditions, which reinforces the previous expression. There are also some operators, including like and in, that can be used in an expression.

An expression can also represent a string value, such as getting a last name and name from a datasheet and connecting to one. The following code snippet connects ProductName with ProductID:

ods.tables["OrderDetail"]. Columns.Add ("Stringfield", typeof (String),
"ProductID + '-' + ProductName");

Function

If you want the column to contain an expression with more complex logic, you can embed some functions in the column. An expression can include functions such as Len, Iif, IsNull, Convert, trim, and substring. These functions provide greater flexibility in building an expression. Len function calculates the length of a string:

ods.tables["OrderDetail"]. Columns.Add ("Lengthofproductname",
typeof (int), "Len (ProductName)");

The IIf function is an if statement of an iteration, like visual Basic. NET is the same as the IIF. It has three parameters and calculates the true and false of the first argument. If the first argument evaluates to True, the second argument is returned from the IIf function, or the third argument is returned. The following is basically a condensed if ... Then ... Else statement, which can be simply written in an embedded expression:

ods.tables["OrderDetail"]. Columns.Add ("Inventory", typeof (String),
"Iif (Quantity <, ' A few left ', ' plenty in the stock ')");

The IsNull function computes the first argument to see if it is equal to the system.dbnull. If the calculated result is false, the function returns the first parameter value, and the second parameter value if True. This is used when null values are not allowed and you want to replace them with an empty string or placeholder:

ods.tables["OrderDetail"]. Columns.Add ("Discountstring",
typeof (String), "IsNull (Discount, ' [null value] ')");

The TRIM function deletes the trailing space of the string value. The CONVERT function converts the data type in an expression to the data type that the second parameter of the function refers to. The SUBSTRING function returns a portion of a string that can be used to cut short long strings and display only fixed-length strings, which can return any part of the string and be used in combination with other functions as needed:

ods.tables["OrderDetail"]. Columns.Add ("Shortproduct",
typeof (String), "Substring (ProductName, 1, 10)");

Collections and relationships

An embedded aggregate function in an expression can help you create an expression that expresses more complex logic. What if you want to set up a column to compute the values across multiple rows? It is best to add an aggregate function.

Assuming that there is a relational structure in a data set that resembles orders to order details in a Northwind database similar to SQL Server, it is straightforward to create a column with an expression that contains an aggregate function. The following code shows how to create a dataset containing a structure in which the order details are located in the parent data table and the order detail is in the subdatasheet. These data table objects are associated with each other through a data relationship called Orders2orderdetails.

Notice how the code creates an expression column and adds it to the order datasheet. The first expression creates a column that displays a summary of the details of each order. In particular, the OrderTotal data column has an expression that summarizes the expression-based ExtendedPrice data columns of the OrderDetail data table. You can see that you can use aggregate functions across data relationships and use them on another expression-based column.

Ado. NET also has other aggregate functions, including sum, AVG, Max, Min, StDev, Var, and Count. The following code shows how to use the AVG function to get the average number of order details. The key is to connect data through a data relationship using parent and child keywords:

ods.tables["Order"]. Columns.Add ("Avgquantity", typeof (Decimal),
AVG (Order2orderdetail). Quantity) ");

The child function accepts the data relation name to get the rowset. This parameter is optional and is only required if there are more than one child relationship in the source data table. Therefore, if the datasheet has only one child table, the syntax can be simplified:

ods.tables["Order"]. Columns.Add ("Avgquantity", typeof (Decimal),
AVG (child.quantity) ");

Scroll down and calculate

The parent function works like the child function, except that it reaches the parent data table along the chain of relationships. These two functions in the Ado.net code help to establish a false group by function.

Another action for these keywords is to roll up or roll down a value that is unchanged from one data table to another. People often ask me how to connect a parent data table and subdatasheet into a single datasheet so that it can be displayed in a DataGrid control. You can use a parent keyword to roll down a field to a subdatasheet, and then only bind the subdatasheet to the DataGrid control. For example, you need to display the order date for each row in the OrderDetail datasheet, and you can add a data column that uses the parent keyword:

ods.tables["OrderDetail"]. Columns.Add ("OrderDate", typeof (String),
"Parent.orderdate");

This feature allows the fields to be rolled up and down without any calculation required. Use the Child keyword to scroll fields from the parent table and bind to the DataGrid control. After you do this, you get a two-bit decimal line set, as you get from the SQL statement. Keep in mind that if you plan to use data from a single rowset, it's a good idea to return a single rowset to a datasheet. However, if you want to use a relational structure in a dataset, the parent keyword gives you the flexibility to display the data.

Another feature that is worth looking at is the compute function of the datasheet, which is computed using the aggregate function on the current datasheet given the filter condition. For example, you can add a column to the datasheet to calculate the total quantity of orders that are larger than 999 for the order price.

In the first parameter of the COMPUTE function, shown in the following example, an aggregate function is executed to compute all the ordertotal values that meet the criteria:

Show order quantity with order price greater than 999
int icnt = (int) ods.tables["order"]. Compute ("Count (OrderTotal)",
"OrderTotal >= 1000");
Lbltest.text = icnt.tostring () + "orders are at least $1000";

The filter is specified in the second parameter, and the aggregate function is qualified to contain only rows that meet the criteria. The code calculates a line that is greater than or equal to 1000 ordertotal. This is a powerful tool for quickly executing computations in a datasheet, especially if you can use it. For example, you can easily find out the number of customers who order X goods and the number of customers who order Y, without having to iterate through the database.

Note data Column objects that are bound to an expression cannot be updated manually. Columns that are bound to an expression are not overwritten unless an expression is deleted. Of course, this data will not correspond to columns in the database or XML file as data sources. Therefore, if you want to save the data to the database, remember that the expression cannot be saved to the database.

The expressions in SQL and ado.net are discussed above, demonstrating the wide range of function features provided by expressions that you can use to generate and add data in your application.