Consuming and Manipulating Data

The best way to create a set of scripts to rebuild data structures on another database is to use Server Explorer. In that utility, click the server that you want to generate the scripts, and then click Generate Create Script.

The best way to store information from an XML Web Service is to store the information in a DataSet and then place it in the cache.

If you want to translate a legacy ADO recordset to a .NET DataSet, you need to do the following:

1. Use the Type Library Importer to create a .NET compatible assembly (interop).

2. Use an OleDbDataAdapter to fill the DataSet with rows from the recordset.

By setting the SmartNavigation attribute of the @Page directive to true, you allow the following behavior to take place:

• Retain control focus after a postback.

• Retain scroll position after a postback.

If you merge two data sets with the following command:

dsFormer.Merge(dsCurrent)

You will find all the data from dsCurrent placed in dsFormer.

It you have two data sets defined as:

and you merge them with this command:

DS1.Merge(DS2, false, MissingSchemaAction.Ignore);

This results in the following two behaviors:

• The value of SystemName in the match row will be the value stored in DS2.

• DS1 will not contain the SystemType column.

If you have two DataSets defined as this:

where a row in DS1 and DS2 have the same value for SystemID, and you merge the two with this code:

DS1.Merge(DS2, true, MissingSchemaAction.Add);

You get the following results:

• DS1 will contain two rows.

• DS1 will add the SystemType column.

The following code will populate a new DataSet with rows from another DataSet that have been modified:

dsUpdates = dsMyData.GetChanges()

If you wish to change the column name in a DataSet to a name different from the one is stored in the database, you need to do the following:

DataTableMappings MapName = MyDataAdapter.TableMappings.Add(
    "MyMap", "ProductsTable");
MapName.ColumnMappings.Add(("pr_ID", "ID");
MapName.ColumnMappings.Add(("pr_Name", "Name");

The SCOPE_IDENTITY function of a T-SQL statement can be used to retrieve the value of an identity column.

The following code will retrieve the key of the last row in a table:

lastID= MyOleCmd.ExecuteScaler()

The following code will read a column of data in a DataSet where you have mapped column names:

foreach(DataRow dr in ds.Tables["ProductTable"].Rows)
{
        string MyString = dr.["Name"].ToString();
}

The valid methods of the SqlTransaction class are Save, Commit, and Terminate.

The following code is the proper way to execute transactions:

SqlTransaction TransMain = SqlCommand.BeginTransaction();
SqlCommand cmdMain = new SqlCommand()
cmdMain.Transaction = TransMain;
cmdMain.CommandText = stringFirstCommand;
cmdMain.ExecuteNonQuery();
cmdMain.CommandText = stringSecondCommand;
cmdMain.ExecuteNonQuery();
TransMain.Commit();
ConnMain.Close();

If you want to use a transaction to update a database, and if errors occur, you want to free all the resources as well as roll back any changes, you should do the following:

try
{
        TransMain.Commit();
}
catch(Exception e)
{
        TransMain.Rollback();
}
finally
{
        ConnMain.Close();
}

If you want to see the errors that may exist in any of the rows of a DataSet, you should use the following code:

DataRow DataErrors;
If(MyDataSet.Tables["Table1"].HasErrors)
{
    DataErrors = MyDataSet.Tables["Table1"].GetErrors();
    For (int Index = 0 ; Index < DataErrors.Length; Index++)
    {
        Console.WriteLine(DataErrors.RowError);
    }
}

If you need to write a query to collect data from the following two tables that results in one table that contains OrderID, CustomerID, and Quantity, you should do this:

Orders OrderID CustomerID Date
OrdersDetails OrderID DetailID ProductID Quantity Amount
SELECT CustomerID ProductID Quantity
FROM Orders
INNER JOIN OrderDetails
On Orders.OrderID = OrderDetails.OrderID

A DataView object can be used to sort and filter data. Use the DataView.RowFilter property to filter data.

If you want to sort the contents of a DataGrid, you should do the following:

1. Set the Sort property of the DataView to the column you wish to sort by.

2. Bind the DataGrid to the DataView.

The initial catalog parameter of a connection string specifies the database name.

The following statement is used to call stored procedures:

SqlCommand.ExecuteScaler

The following code can be used to call a stored procedure that returns a value and store that value in a local variable:

SqlCommand MyCommand = new SqlCommand("MyStoredProcedureName", myConn);
SqlDataReader MyReader = MyCommand.ExecuteReader();
MyReader.Close();
Long MyVal = MyReader.Parameters["@ReturnVal"].Value;
.Value;
.Parameters["@ReturnVal"]d.ExecuteScaler();

The following connection string can be used to specify that a server has no more than 30 seconds to respond and cannot exceed 20 connections in its connection pool:

"user id=sa;password=12345;initial catalog =
  MyDB;source = MyServer;Connection Timeout=30;Max PoolSize=20"

The following code snippet shows how to delete data from a row in a table and any rows that match in a table that is joined by foreign keys:

DataColumn column1, column2;
DataRelation MyRelation;
Column1 = MyDataSet.Tables["Widgets"].Columns["WidgetID"];
Column2 = MyDataSet.Tables["WidgetsData"].Columns["WidgetID"];
MyRelation = new DataRelation("Widgets with components", Column1, Column2);
MyDataSet.Relations.Add(MyRelation);

The following shows another way to make sure that rows related via a foreign key are deleted when a row in the main table is deleted:

DataColumn Column1, Column2;
ForeignKeyConstraint MyConstraint;
Column1 = MyDataSet.Tables["Widgets"].Columns["WidgetID"];
Column2 = MyDataSet.Tables["WidgetsData"].Columns["WidgetID"];
MyConstraint = new ForeignKeyConstraint(("Widgets with components", Column1, Column2);
MyConstraint.UpdateRule = Rule.Cascade;
MyConstraint.DeleteRule = Rule.Cascade;
MyDataSet.Tables["Widgets"].Constraints.Add(MyConstraint);
MyDataSet.EnforceConstraints = true;

You can improve performance without affecting security by allowing all users to access a SQL database using one account, and then using .NET security to control access to features of the Web site.