Apply Your Knowledge

Exercises

6.1. Preselecting Data by Using Parameterized Stored Procedures

One of the biggest issues in working with server-side data such as SQL Server data is to minimize the amount of data that you load into an application. That's because communication with such servers is typically comparatively slow, and the servers themselves have enough processing power to quickly locate the exact data that you want. In this exercise, you'll see how you can minimize the amount of data retrieved by using a series of stored procedures with parameters.

Estimated time: 30 minutes

1.
Create a new Visual C# .NET project to use for the exercises in this chapter.

2.
Add a new form to the project.

3.
Place a ComboBox control (cboCustomers), a Button control (btnLoad), and a DataGrid control (dgMain) on the form.

4.
Switch to the code view and add the following using directives to make the ADO.NET objects available:

using System.Data;
using System.Data.SqlClient;

5.
Using a tool such as the SQL Query Analyzer or the Visual Studio .NET IDE, create this stored procedure:

CREATE PROC procCustomerList
AS
SELECT CustomerID, CompanyName
FROM Customers
ORDER BY CompanyName

6.
Using a tool such as the SQL Query Analyzer or the Visual Studio .NET IDE, create this stored procedure:

CREATE PROC procCustomerDetails
  @CustomerID char(5)
AS
SELECT * FROM Customers
WHERE CustomerID = @CustomerID

7.
Using a tool such as the SQL Query Analyzer or the Visual Studio .NET IDE, create this stored procedure:

CREATE PROC procOrdersForCustomer
  @CustomerID char(5)
AS
SELECT * FROM Orders
WHERE CustomerID = @CustomerID

8.
To minimize load time, the form starts by loading only the customer list into the ComboBox control. Enter this code to load the customer list in the form's Load event handler:

SqlConnection cnn = new SqlConnection(
   "Data Source=(local);" +
   "Initial Catalog=Northwind;" +
 "Integrated Security=SSPI");

private void Exercise6_1_Load(
object sender,
    System.EventArgs e)
{
    // Load the customer list
    SqlCommand cmdCustomers =
       cnn.CreateCommand();
    cmdCustomers.CommandType =
       CommandType.StoredProcedure;
    cmdCustomers.CommandText =
       "procCustomerList";
    cnn.Open();
    DataSet ds = new DataSet();
    SqlDataAdapter da = new SqlDataAdapter();
    da.SelectCommand = cmdCustomers;
    da.Fill(ds, "Customers");
    cboCustomers.DataSource =
       ds.Tables["Customers"];
    cboCustomers.DisplayMember =
       "CompanyName";
    cboCustomers.ValueMember = "CustomerID";
    cnn.Close();
}

9.
When the user clicks the Load button, the other stored procedures should load only the data of interest. Enter this code to build the DataSet object and bind it to the DataGrid control in the btnLoad Click event handler:

private void btnLoad_Click(object sender,
    System.EventArgs e)
{
    // Create a new DataSet
    DataSet ds = new DataSet();
    // Load only the customer of interest
    SqlCommand cmdCustomer =
       cnn.CreateCommand();
    cmdCustomer.CommandType =
       CommandType.StoredProcedure;
    cmdCustomer.CommandText =
       "procCustomerDetails";
    cmdCustomer.Parameters.Add(
       new SqlParameter(
        "@CustomerID", SqlDbType.Text, 5));
    cmdCustomer.Parameters["@CustomerID"].
       Value =
        cboCustomers.SelectedValue;
    SqlDataAdapter daCustomer =
       new SqlDataAdapter();
    daCustomer.SelectCommand = cmdCustomer;
    daCustomer.Fill(ds, "Customers");
    // Load the orders for this customer
    SqlCommand cmdOrders =
       cnn.CreateCommand();
    cmdOrders.CommandType =
       CommandType.StoredProcedure;
    cmdOrders.CommandText =
       "procOrdersForCustomer";
    cmdOrders.Parameters.Add(
       new SqlParameter(
        "@CustomerID", SqlDbType.Text, 5));
    cmdOrders.Parameters["@CustomerID"].
       Value =
        cboCustomers.SelectedValue;
    SqlDataAdapter daOrders =
       new SqlDataAdapter();
    daOrders.SelectCommand = cmdOrders;
    daOrders.Fill(ds, "Orders");
    // Relate the two DataTables
    DataRelation relCustOrder =
      ds.Relations.Add(
      "CustOrder",
      ds.Tables["Customers"].
      Columns["CustomerID"],
      ds.Tables["Orders"].
      Columns["CustomerID"]);
    // Bind the data to the user interface
    dgMain.DataSource = ds;
    dgMain.DataMember = "Customers";
}

10.
Insert the Main() method to launch the form. Set the form as the startup form for the project.

11.
Run the project. Select a customer from the list in the combo box and then press the Load button. The form displays only the information for that customer. Click on the + sign next to the customer to see the order information, as shown in Figure 6.29.

Figure 6.29. You can use parameterized stored procedures to minimize the amount of data that you need to load into an application.

[View full size image]

6.2. Transferring Database Data to a Disk File

The DataSet object has facilities for saving data as XML. But sometimes file size is more important than the readability that XML brings to the table. In this exercise, you'll retrieve data from a database and then use the BinaryWriter class to write the data out as a compact disc file.

Estimated time: 20 minutes

1.
Add a new form to your Visual C# .NET project.

2.
Place a ComboBox control (cboProducts) and a Button control (btnWrite) on the form.

3.
Switch to the code view and add the following using directives:

using System.Data;
using System.Data.SqlClient;
using System.IO;

4.
The form initializes the list of products at load time. Enter this code to load the list of products in the form's Load event handler:

SqlConnection cnn = new SqlConnection(
   "Data Source=(local);" +
   "Initial Catalog=Northwind;" +
   "Integrated Security=SSPI");

private void Exercise6_2_Load(
    object sender, System.EventArgs e)
{
    // Load the customer list
    SqlCommand cmd = cnn.CreateCommand();
    cmd.CommandType = CommandType.Text;
    cmd.CommandText =
      "SELECT ProductName, ProductID " +
       "FROM Products ORDER BY ProductName";
    cnn.Open();
    DataSet ds = new DataSet();
    SqlDataAdapter da = new SqlDataAdapter();
    da.SelectCommand = cmd;
    da.Fill(ds, "Products");
    cboProducts.DataSource =
       ds.Tables["Products"];
    cboProducts.DisplayMember =
       "ProductName";
    cboProducts.ValueMember = "ProductID";
    cnn.Close();
}

5.
Double-click the Button control and enter this code to handle the button's Click event:

private void btnWrite_Click(object sender,
   System.EventArgs e)
{
    // Get a file name to use
    // when saving the file
    SaveFileDialog sfd =
      new SaveFileDialog();
    sfd.Title = "Choose save file";
    if (sfd.ShowDialog() == DialogResult.OK)
    {
        // Get the full data on
        // the selected product
        SqlCommand cmdProduct =
           cnn.CreateCommand();
        cmdProduct.CommandType =
           CommandType.Text;
        cmdProduct.CommandText =
           "SELECT * FROM Products" +
           " WHERE ProductID = @ProductID";
        cmdProduct.Parameters.Add(
           new SqlParameter(
            "@ProductID", SqlDbType.Int));
        cmdProduct.Parameters["@ProductID"].Value =
            cboProducts.SelectedValue;
        // Open a BinaryWriter
        FileStream fsOut = new FileStream(
            sfd.FileName, FileMode.Create);
        BinaryWriter bw =
           new BinaryWriter(fsOut);
        // Get the data into a DataRow
        SqlDataAdapter da =
           new SqlDataAdapter();
        da.SelectCommand = cmdProduct;
        DataSet ds = new DataSet();
        da.Fill(ds, "Products");
        DataRow dr =
           ds.Tables["Products"].Rows[0];
        // And write the data
        for (int intI=0; intI <= 9; intI++)
        {
            bw.Write(dr[intI].ToString());
        }
        bw.Flush();
        bw.Close();
        MessageBox.Show("Data written");
    }
}

This code retrieves the DataRow object of interest and then uses a BinaryWriter object to move it, field-by-field, to a disk file. The code uses the SaveFileDialog class to prompt for a filename.

6.
Insert the Main() method to launch the form. Set the form as the startup form for the project.

7.
Run the project. Select a customer from the list in the combo box and click the Write button to save the corresponding DataRow object through a BinaryWriter class.

6.3. Updating Multiple Tables

You've seen how to load data from more than one table into the same DataSet object and how to update a database with changes that were made to a DataSet object. In this exercise, you'll combine the two and write code to update multiple tables that are contained in a single DataSet object.

Estimated time: 25 minutes

1.
Add a new form to your Visual C# .NET project.

2.
Place a DataGrid control (dgMain) and a Button control (btnUpdate) on the form.

3.
Switch to the code view and add the following using directives to make the ADO.NET objects available:

using System.Data;
using System.Data.SqlClient;

4.
Double-click the form and enter this code to load data when the form is opened:

private void Exercise6_3_Load(
    object sender, System.EventArgs e)
{
   // Create a SqlCommand  object
   // to select Customer data
   SqlCommand cmdSelect =
       cnn.CreateCommand();
   cmdSelect.CommandType =
       CommandType.Text;
   cmdSelect.CommandText =
      "SELECT CustomerID, CompanyName, " +
      "ContactName FROM Customers";
   // Create a SqlCommand object
   // to update Customer data
   SqlCommand cmdUpdate =
       cnn.CreateCommand();
   cmdUpdate.CommandType =
       CommandType.Text;
   cmdUpdate.CommandText =
   "UPDATE Customers SET " +
   "CompanyName = @CompanyName, " +
   "ContactName = @ContactName " +
   "WHERE CustomerID = @CustomerID";
   cmdUpdate.Parameters.Add("@ContactName",
     SqlDbType.NVarChar,30, "ContactName");
   cmdUpdate.Parameters.Add("@CompanyName",
     SqlDbType.NVarChar,40, "CompanyName");
   cmdUpdate.Parameters.Add("@CustomerID",
     SqlDbType.NChar, 5, "CustomerID");
   cmdUpdate.Parameters["@CustomerID"].
     SourceVersion =
     DataRowVersion.Original;
   // Create a SqlCommand object
   // to insert Customer data
   SqlCommand cmdInsert =
       cnn.CreateCommand();
   cmdInsert.CommandType =
       CommandType.Text;
   cmdInsert.CommandText =
       "INSERT INTO Customers (" +
       "CustomerID, CompanyName, " +
       "ContactName) VALUES(@CustomerID," +
       " @CompanyName, @ContactName)";
    cmdInsert.Parameters.Add("@CustomerID",
       SqlDbType.NChar, 5, "CustomerID");
   cmdInsert.Parameters.Add("@CompanyName",
    SqlDbType.NVarChar, 40, "CompanyName");
   cmdInsert.Parameters.Add("@ContactName",
    SqlDbType.NVarChar, 30, "ContactName");
   cmdInsert.Parameters["@CustomerID"].
   SourceVersion = DataRowVersion.Original;
    // Create a SqlCommand object
    // to delete Customer data
    SqlCommand cmdDelete =
        cnn.CreateCommand();
    cmdDelete.CommandType =
        CommandType.Text;
    cmdDelete.CommandText =
      "DELETE FROM Customers " +
      "WHERE CustomerID = @CustomerID";
    cmdDelete.Parameters.Add("@CustomerID",
        SqlDbType.NChar, 5, "CustomerID");
    cmdDelete.Parameters["@CustomerID"].
   SourceVersion = DataRowVersion.Original;
    // Set up the DataAdapter object
    // and fill the DataSet
    daCust.SelectCommand = cmdSelect;
    daCust.UpdateCommand = cmdUpdate;
    daCust.InsertCommand = cmdInsert;
    daCust.DeleteCommand = cmdDelete;
    daCust.Fill(ds, "Customers");
    // Now set up the commands for Orders
    SqlCommand cmdSelectOrders =
        cnn.CreateCommand();
    cmdSelectOrders.CommandType =
        CommandType.Text;
    cmdSelectOrders.CommandText =
       "SELECT OrderID, CustomerID," +
       " OrderDate FROM Orders";
    // Create a SqlCommand object
    // to update Order data
    SqlCommand cmdUpdateOrders =
        cnn.CreateCommand();
    cmdUpdateOrders.CommandType =
        CommandType.Text;
    cmdUpdateOrders.CommandText =
        "UPDATE Orders SET " +
        "CustomerID = @CustomerID, " +
        "OrderDate = @OrderDate " +
        "WHERE OrderID = @OrderID";
    cmdUpdateOrders.Parameters.Add(
       "@CustomerID",
       SqlDbType.NChar, 5, "CustomerID");
    cmdUpdateOrders.Parameters.Add(
       "@OrderDate", SqlDbType.DateTime);
    cmdUpdateOrders.Parameters[
      "@OrderDate"].SourceColumn =
      "OrderDate";
    cmdUpdateOrders.Parameters.Add(
     "@OrderID", SqlDbType.Int);
    cmdUpdateOrders.Parameters[
      "@OrderID"].SourceColumn =
      "OrderID";
    cmdUpdateOrders.Parameters[
       "@CustomerID"].SourceVersion =
       DataRowVersion.Original;
    // Create a SqlCommand object
    // to insert Order data
    SqlCommand cmdInsertOrders =
        cnn.CreateCommand();
    cmdInsertOrders.CommandType =
        CommandType.Text;
    cmdInsertOrders.CommandText =
       "INSERT INTO Orders (" +
       "CustomerID, OrderDate) " +
       "VALUES(@CustomerID, @OrderDate)";
    cmdInsertOrders.Parameters.Add(
       "@CustomerID", SqlDbType.NChar,
       5, "CustomerID");
    cmdInsertOrders.Parameters.Add(
       "@OrderDate", SqlDbType.DateTime);
    cmdInsertOrders.Parameters[
       "@OrderDate"].SourceColumn =
       "OrderDate";
    // Create a SqlCommand object
    // to delete Order data
    SqlCommand cmdDeleteOrders =
        cnn.CreateCommand();
    cmdDeleteOrders.CommandType =
        CommandType.Text;
    cmdDeleteOrders.CommandText =
       "DELETE FROM Orders WHERE " +
       "OrderOD = @OrderID";
    cmdDeleteOrders.Parameters.Add(
         "@OrderID", SqlDbType.Int);
    cmdDeleteOrders.Parameters["@OrderID"].
      SourceColumn = "OrderID";
    cmdDeleteOrders.Parameters["@OrderID"].
       SourceVersion =
       DataRowVersion.Original;
    // Add orders to the DataSet
    daOrders.SelectCommand =
        cmdSelectOrders;
    daOrders.UpdateCommand =
        cmdUpdateOrders;
    daOrders.InsertCommand =
        cmdInsertOrders;
    daOrders.DeleteCommand =
        cmdDeleteOrders;
    daOrders.Fill(ds, "Orders");
    // Relate the two tables
    DataRelation relCustOrder =
       ds.Relations.Add(
       "CustOrder",
       ds.Tables["Customers"].
       Columns["CustomerID"],
       ds.Tables["Orders"].
       Columns["CustomerID"]);
    // And bind the data to the DataGrid
    dgMain.DataSource = ds;
    dgMain.DataMember = "Customers";
}

5.
Double-click the Button control and enter this code to handle the button's Click event. This code calls the Update property of each of the DataTable objects in the DataSet object:

private void btnUpdate_Click(object sender,
   System.EventArgs e)
{
    // Update both datatables
    daCust.Update(ds, "Customers");
    daOrders.Update(ds, "Orders");
}

6.
Insert the Main() method to launch the form. Set the form as the startup form for the project.

7.
Run the project. Make as many updates as you like and then click OK to make those updates a permanent part of the database.

6.4. Using Transactions with ADO.NET

Popular RDBMS software uses transactions to maintain database integrity. A transaction is a single unit of work. If a transaction is successful, all the data modifications made during the transaction are committed and become permanent parts of the database.

If a transaction encounters errors or is invalid, then it must be canceled or rolled back; in that case, all the data modifications in that transaction are erased.

In this exercise, I use the SqlTransaction class, which represents a T-SQL transaction on a SQL Server database. A transaction is explicitly started by using the SqlConnection.BeginTransaction() method. The newly created SqlTransaction object is then assigned to the Transaction property of the current command object. If the changes need to be committed to the database, you use SqlTransaction.Commit() method, whereas if the changes to the database need to be rolled back, you use the SqlTransaction.RollBack() method. Both the Commit() and RollBack() methods end the current transaction.

EXAM TIP

Using Transactions with ADO.NET You can create a SqlTransaction object by calling the BeginTransaction() method on the SqlConnection object. After you have the SqlTransaction object available, all subsequent operations associated with the transaction, such as Commit() and RollBack(), are performed on the SqlTransaction object instead of the SqlConnection object.


Estimated time: 25 minutes

1.
Add a new form to your Visual C# .NET project.

2.
Place five Label controls, four TextBox controls (txtCustomerID1, txtCompanyName1, txtCustomerID2, and txtCompanyName2), and a Button control (btnAdd) on the form.

3.
Switch to the code view and add the following using directives to make the ADO.NET objects available:

using System.Data;
using System.Data.SqlClient;

4.
Double-click the Button control and enter this code in the Click event handler:

private void btnAdd_Click(object sender,
   System.EventArgs e)
{
    // Create a SqlConnection object
    SqlConnection cnn = new SqlConnection(
        "Data Source=(local);" +
        "Initial Catalog=Northwind;" +
        "Integrated Security=SSPI");
    // Open the connection
    cnn.Open();
    // Create a SqlTransaction object
    SqlTransaction trans =
       cnn.BeginTransaction();
    // Create a SqlCommand object for
    // the SqlConnection object
    SqlCommand cmd = cnn.CreateCommand();
    // Assign the transaction object to the
    // SqlCommand object's
    // Transaction property
    cmd.Transaction = trans;
    try
    {
        // Set the CommandType and
        // CommandText property
        // of the SqlCommand object
        // Execute the Query
        cmd.CommandType = CommandType.Text;
        cmd.CommandText =
            "INSERT INTO Customers " +
            "(CustomerID, CompanyName)" +
            " VALUES ('" +
            txtCustomerID1.Text + "', " +
            "'" + txtCompanyName1.Text +
            "')";
        cmd.ExecuteNonQuery();

        // Set the
        // CommandText property
        // of the SqlCommand object
        // Execute the Query
        cmd.CommandType = CommandType.Text;
        cmd.CommandText =
           "INSERT INTO Customers " +
            "(CustomerID, CompanyName)" +
            " VALUES ('" +
            txtCustomerID2.Text + "', " +
            "'" + txtCompanyName2.Text +
            "')";
        cmd.ExecuteNonQuery();

        // Commit the transaction
        trans.Commit();
        MessageBox.Show(
          "Customers added successfully",
            "Commit");
    }
    catch(Exception ex)
    {
        MessageBox.Show(ex.Message,
          "Rollback");
        // Rollback the transaction
        trans.Rollback();
    }
    finally
    {
        // Close the SqlConnection object
        cnn.Close();
    }
}

5.
Insert the Main() method to launch the form. Set the form as the startup form for the project.

6.
Run the project. Add information about two customers in the text boxes and then click the Add the Customers button. If no error occurs while the database is being updated, the transaction is committed. In case of any error, the transaction is rolled back and the database remains unaffected.

Review Questions

1:Describe the difference between an ad hoc query and a stored procedure.
2:List and describe the four basic T-SQL statements.
3:Name four ways that you can execute SQL statements.
4:In a T-SQL SELECT statement, what is the difference between the WHERE clause and the HAVING clause?
5:What is the purpose of the @@IDENTITY variable?
6:What is a stream? What is a backing store?
7:How should you decide between using a StreamReader object and using a BinaryReader object?
8:Describe the difference between the data provider objects and the DataSet objects.
9:Which ADO.NET object do you use to execute a stored procedure?
10:Which ADO.NET object transfers data between the database and the data model?
11:What are the advantages of strongly typed DataSet objects?
12:Which XML object can you synchronize with a DataSet object?
13:Name and describe the two main types of concurrency control that you can implement in .NET.

Exam Questions

1:Your SQL Server database contains a table, Sales, with these columns:

SalesID (int, identity)

StoreNumber (int)

Sales (int)

You want to see a list of the stores, together with their total sales. The list should be filtered to include only stores whose total sales are more than 10. Which SQL statement should you use?

  1. SELECT StoreNumber, Sales
FROM Sales
WHERE Sales > 10


  2. SELECT StoreNumber, SUM(Sales)
FROM Sales
WHERE Sales > 10
GROUP BY StoreNumber


  3. SELECT StoreNumber, SUM(Sales)
FROM Sales
GROUP BY StoreNumber
HAVING SUM(Sales) > 10


  4. SELECT StoreNumber, SUM(Sales)
FROM Sales
WHERE Sales > 10
GROUP BY StoreNumber
HAVING SUM(Sales) > 10
2:Your SQL Server database contains a table, Sales, with these columns:

SalesID (int, identity)

StoreNumber (int)

Sales (int)

You want to see a list of the stores, together with their total sales. The list should be filtered to include only rows from the table where the sales are more than 10. Which SQL statement should you use?

  1. SELECT StoreNumber, Sales
FROM Sales
WHERE Sales > 10


  2. SELECT StoreNumber, SUM(Sales)
FROM Sales
WHERE Sales > 10
GROUP BY StoreNumber


  3. SELECT StoreNumber, SUM(Sales)
FROM Sales
GROUP BY StoreNumber
HAVING SUM(Sales) > 10


  4. SELECT StoreNumber, SUM(Sales)
FROM Sales
WHERE Sales > 10
GROUP BY StoreNumber
HAVING SUM(Sales) > 10
3:Your SQL Server database contains a table, Experiments, with the following columns:

ExperimentID (int, identity)

ExperimentType (char(1))

ExperimentDate (datetime)

You want to delete all rows from the table where the ExperimentType value is either A or C. You do not want to delete any other rows. Which SQL statement should you use?

  1. DELETE FROM Experiments
WHERE ExperimentType LIKE '[AC]'


  2. DELETE FROM Experiments
WHERE ExperimentType LIKE '[A-C]'


  3. DELETE FROM Experiments
WHERE ExperimentType LIKE 'A' OR 'C'


  4. DELETE * FROM Experiments
WHERE ExperimentType IN ('A', 'C')
4:Your SQL Server database contains a table, Sales, with these columns:

SalesID (int, identity)

StoreNumber (int)

Sales (int)

You want to create a stored procedure that accepts as inputs the store number and sales, inserts a new row in the table with this information, and returns the new identity value. Which SQL statement should you use?

  1. CREATE PROCEDURE procInsertSales
  @StoreNumber int,
  @Sales int,
  @SalesID int
AS
  INSERT INTO Sales (StoreNumber, Sales)
  VALUES (@StoreNumber, @Sales)
  SELECT @SalesID = @@IDENTITY


  2. CREATE PROCEDURE procInsertSales
  @StoreNumber int,
  @Sales int,
  @SalesID int OUTPUT
AS
  INSERT INTO Sales (
     SalesID, StoreNumber, Sales)
  VALUES (@SalesID, @StoreNumber, @Sales)


  3. CREATE PROCEDURE procInsertSales
  @StoreNumber int,
  @Sales int,
  @SalesID int OUTPUT
AS
  INSERT INTO Sales (
     SalesID, StoreNumber, Sales)
  VALUES (0, @StoreNumber, @Sales)
  SELECT @SalesID = @@IDENTITY


  4. CREATE PROCEDURE procInsertSales
  @StoreNumber int,
  @Sales int,
  @SalesID int OUTPUT
AS
  INSERT INTO Sales (StoreNumber, Sales)
  VALUES (@StoreNumber, @Sales)
  SELECT @SalesID = @@IDENTITY
5:Your application has two FileStream objects. The fsIn object is open for reading, and the fsOut object is open for writing. Which code snippet would copy the contents of fsIn to fsOut using a 2KB buffer? 

  1. Int32[] buf = new  Int32[2048];
Int32 intBytesRead;
while((intBytesRead =
   fsIn.Read(buf, 0, 2048)) > 0)
{
    fsOut.Write(buf, 0, intBytesRead);
}
//Clean up
fsOut.Flush();
fsOut.Close();
fsIn.Close();


  2. Int32[] buf = new  Int32[2048];
Int32 intBytesRead;
while((intBytesRead =
   fsIn.Read(buf, 0, 2048)) > 1)
{
    fsOut.Write(buf, 0, intBytesRead);
}
//Clean up
fsOut.Flush();
fsOut.Close();
fsIn.Close();


  3. Byte[] buf = new  Byte[2048];
Int32 intBytesRead;
while((intBytesRead =
   fsIn.Read(buf, 0, 2048)) > 0)
{
    fsOut.Write(buf, 0, intBytesRead);
}
//Clean up
fsOut.Flush();
fsOut.Close();
fsIn.Close();


  4. Byte[] buf = new  Byte[2048];
Int32 intBytesRead;
while((intBytesRead =
   fsIn.Read(buf, 0, 2048)) > 1)
{
    fsOut.Write(buf, 0, intBytesRead);
}
//Clean up
fsOut.Flush();
fsOut.Close();
fsIn.Close();
6:Your application includes 15 double-precision floating-point numbers that you want to write out to a disk file. You'd like to minimize the size of the disk file. Which object should you use to write the file?

  1. FileStream

  2. StreamWriter

  3. BinaryWriter

  4. XmlTextWriter

7:Your application needs to return the total number of customers in a database. What is the fastest way to do this?

  1. Write ad hoc SQL queries to return the total number of customers. Use the SqlCommand.ExecuteScalar() method to execute the SQL statement.

  2. Write ad hoc SQL queries to return the total number of customers. Use the SqlDataAdapter.Fill() method to execute the SQL statement.

  3. Create a stored procedure to return the total number of customers. Use the SqlCommand.ExecuteScalar() method to execute the stored procedure.

  4. Create a stored procedure to return the total number of customers. Use the SqlDataAdapter.Fill() method to execute the stored procedure.

8:Your application needs to retrieve a list of customer balances from a SQL Server database. The application should move through the list once, processing each balance in turn. The application does not need to write to the database. Which object should you use to hold the list in the data model?

  1. DataSet

  2. SqlDataReader

  3. DataTable

  4. DataView

9:Your SQL Server database contains customer and order information. The Orders table includes a foreign key that refers to the Customers table. You have loaded the Customers and Orders tables into a single DataSet object through two separate SqlDataAdapter objects. The DataSet object is bound to a DataGrid control on the application's user interface. When you run the application, only customer information appears in the DataGrid control. You have verified that there are orders in the database. What is the most likely cause of this problem?

  1. You must use a single SqlDataAdapter object to load both tables.

  2. You have neglected to create a DataRelation object in the DataSet object.

  3. There are no orders for the first customer displayed on the DataGrid control.

  4. The DataGrid control can display information from only a single table.

10:Your application uses a SqlDataReader object to retrieve information about customer balances. When you find a past-due balance, you want to write a new entry to a billing table by executing a stored procedure in the same database. You have used a SqlCommand object to represent the stored procedure. Calling the ExecuteNonQuery() method of the SqlCommand object is causing an error. What is the most likely cause of this error?

  1. You must use a SqlDataAdapter object to execute the stored procedure.

  2. You must use an ad hoc SQL statement rather than a stored procedure to insert new rows in a database.

  3. You are using the ExecuteNonQuery method of the SqlCommand object, and you should be using the ExecuteScalar method instead.

  4. You are using the same SqlConnection object for both the SqlDataReader object and the SqlCommand object, and the SqlDataReader object is still open when you try to execute the SqlCommand object.

11:Your application allows the user to edit product data on a DataGrid control. The DataGrid control is bound to a DataSet object. The DataSet object is filled through a SqlDataAdapter object. The InsertCommand, UpdateCommand, and DeleteCommand properties of the SqlDataAdapter object are set to SqlCommand objects, and you have tested the SQL in those SqlCommand objects.

When users exit the application, none of their changes are saved to the database, and they do not receive any errors. What could be the problem?

  1. You have neglected to call the SqlDataAdapter.Update() method in your code.

  2. The users do not have permission to write to the database.

  3. You have neglected to fill the DataSet object from the DataGrid control after the users finish editing the data.

  4. The DataSet object is a read-only object.

12:Your application includes a DataSet object that contains a DataTable object named Suppliers. This DataTable object contains all rows from the Suppliers table in your database. You want to bind an object to a DataGrid control on a form such that the DataGrid control displays only the suppliers from Pennsylvania. What should you do?

  1. Create a filtered array by calling the DataTable. Select() method on the Suppliers data table and bind the array to the DataGrid control.

  2. Create a new SqlCommand object to retrieve only suppliers from Pennsylvania. Use a new SqlDataAdapter object to fill a new DataSet object with these suppliers. Bind the new DataSet object to the DataGrid control.

  3. Use a foreach loop to move through the entire Suppliers data table. Each time you find a DataRow object that represents a supplier from Pennsylvania, bind that DataRow object to the DataGrid control.

  4. Create a filtered DataView object from the Suppliers data table and bind the DataView object to the DataGrid control.

13:You allow users to edit product information on a DataGrid control that is bound to a DataSet object. When the user clicks the Update button on the form, you call the SqlDataAdapter.Update() method to cause the changes from the DataSet object to persist to the underlying database.

Users report that new records and updated rows are saved properly but that deleted rows are reappearing the next time they run the application. What could be the problem?

  1. The users do not have permission to update the underlying table.

  2. The Update() method does not delete rows.

  3. Someone is restoring an old version of the database between the two executions of the program.

  4. The DeleteCommand property of the SqlDataAdapter object points to a SqlCommand object that does not properly delete rows.

14:Your application recursively calls the FirstChild() and NextChild() methods of XmlNode objects to visit every node in an XML file. When you find a node that includes customer name information, you store the information. The application is not returning all the customer names from the file. What could be the problem?

  1. The XML file is not well formed.

  2. The XML file has more than one root node.

  3. The customer name information is stored in XML attributes.

  4. The HasChildNodes property is not properly set on all nodes.

15:Your application reads an XML file from disk into an XmlDocument object, and then it modifies some of the nodes in the document. Which object should you use to write the modified XmlDocument object back to disk?

  1. XmlTextWriter

  2. FileStream

  3. StreamWriter

  4. BinaryWriter

  5. TextWriter

16:You have designed an application to use optimistic concurrency control. Alice and Bob each retrieve the Products table to the application at 8:00 a.m. The initial price of a widget is $3. At 8:05 a.m., Alice changes the price of a widget to $4 and saves her changes to the database. At 8:10 a.m., Bob changes the price of a widget to $5 and saves his changes to the database. What would be the price of a widget in the database at 8:11 a.m. if no one made any other changes?

  1. $3

  2. $4

  3. $5

  4. $9

Answers to Review Questions

A1: An ad hoc query consists of SQL statements that are sent to the server. A stored procedure consists of SQL statements that are permanently stored on the server.
A2: The SELECT statement retrieves data, the UPDATE statement updates existing data, the INSERT statement adds new data, and the DELETE statement deletes data.
A3: Using the Visual Studio .NET IDE, through osql, through the SQL Query Analyzer, or with your own home-grown solutions.
A4: The WHERE clause restricts the rows that are used as input to an aggregate. The HAVING clause restricts the rows that are output from an aggregate.
A5: The @@IDENTITY variable returns the last identity value to have been assigned to a table.
A6: A stream is a file viewed as a list of bytes. A backing store is a place where data can be stored.
A7: The StreamReader object is most useful when you're dealing with a line-oriented text file. The BinaryWriter object is most useful when you're working with a file in a particular format.
A8: There are multiple sets of platform- and product-specific data provider objects. A single set of DataSet objects holds abstract data that's not directly associated with any database.
A9: The SqlCommand object can be used to execute a stored procedure.
A10: The SqlDataAdapter object is the pipeline between the data model and the DataSet object.
A11: Strongly typed DataSet objects give you the benefit of IntelliSense at design time. They also provide earlier data binding than late data binding by ordinary DataSet objects.
A12: The XmlDataDocument object can be synchronized with a DataSet object.
A13: With optimistic concurrency control, an update to a row succeeds only if no one else has changed that row after it is loaded into the DataSet object. With “last one wins” concurrency control, an update to a row always succeeds, whether another user has edited the row or not (as long as the row still exists).

Answers to Exam Questions

A1: C. The GROUP BY clause is required to obtain aggregate numbers. The HAVING clause filters the results after the aggregation has been performed. The answers that contain the WHERE clause are incorrect because WHERE filters the input to the aggregations. For more information, see the section “The SELECT Statement” in this chapter.
A2: B. The GROUP BY clause is required to obtain aggregate numbers. The WHERE clause filters rows before aggregating them. The answers that contain the HAVING clause are incorrect because HAVING filters the results after aggregation.For more information, see the section “The SELECT Statement” in this chapter.
A3: A. Answer B would also delete rows with an ExperimentType value of B. Answer C would take the OR of A and C before evaluating the LIKE clause. DELETE * is not valid T-SQL syntax. For more information, see the section “The DELETE Statement” in this chapter.
A4: D. Answer A does not indicate that @SalesID is an output parameter. Answers B and C attempt to insert values into the identity column, rather than let SQL Server assign the new value. For more information, see the section “Using Stored Procedures” in this chapter.
A5: C. The Read() method returns the number of bytes read, so Answers B and D fail when there is 1 byte in the file. The Read() method reads to a byte array, so Answers A and B fail because the buffer has the wrong data type. For more information, see the section “Using the FileStream Class” in this chapter.
A6: C. The BinaryWriter object provides a compact format for data storage on disk, as long as you don't need the data to be human-readable. All the other objects store the data as ASCII text, which takes more space. For more information, see the section “Using the BinaryReader and BinaryWriter Classes” in this chapter.
A7: C. Stored procedures execute faster than the corresponding ad hoc SQL statements because stored procedures are stored in the database in compiled form. Using the ExecuteScalar() method is faster than filling a DataSet object for returning a single value. For more information, see the section “The SqlCommand and SqlParameter Objects” in this chapter.
A8: B. The SqlDataReader object gives you a fast, forward-only, read-only view of the data. It is ideal for processing all rows once, without extra overhead. For more information, see the section “The SqlDataReader Object” in this chapter.
A9: B. Even though the two tables are related in the database, you must still tell the DataSet object what the relationship is by creating a DataRelation object. For more information, see the section “Using DataSet Objects” in this chapter.
A10: D. While a SqlDataReader object is open, you cannot execute other commands on the SqlConnection object that the SqlDataReader object is using. For more information, see the section “The DataProviders Objects” in this chapter.
A11: A. If you do not call the SqlDataAdapter. Update() method, all changes to the data model are lost. Answer B would return an error to the users. Answer C is incorrect because a bound DataSet object automatically reflects changes to the DataGrid control. Answer D is incorrect because DataSet objects are designed to be edited. For more information, see the section “Using DataSet Objects” in this chapter.
A12: D. Answers A and C do not give you objects that can be bound to the DataGrid control. Answer B works, but retrieving the data from the database a second time is slower than filtering it from the existing DataTable object. For more information, see the section “Finding and Sorting Data in DataSet Objects” in this chapter.
A13: D. If Answers A or C were the case, none of the changes would be saved. Answer B is simply incorrect. For more information, see the section “Editing Data with ADO.NET” in this chapter.
A14: C. By default, XML attributes do not appear as part of the XmlNodes that is traversed by the FirstChild and NextSibling properties. If Answers A or B were the case, you would not be able to load the file into an XmlDocument object. Answer D is incorrect because HasChildNodes is automatically set by the .NET Framework. For more information, see the section “Using XML Data” in this chapter.
A15: A. The XmlTextWriter object is designed to write XML files, preserving the proper XML structure. For more information, see the section “Using XML Data” in this chapter.
A16: B. With optimistic concurrency control, Bob's change is not written to the database. For more information, see the section “Handling Multiuser Errors” in this chapter.
Suggested Readings and Resources

1. SQL Server Books Online, “Transact-SQL Reference”

2. Visual Studio .NET Combined Help Collection:

• “Overview of ADO.NET”

• “XML in Visual Studio”

3. .NET Framework SDK documentation:

• “Accessing Data with ADO.NET”

• “Basic File I/O”

4. Bob Beauchemin . Essential ADO.NET. Addison-Wesley, 2002.

5. David Sceppa . Microsoft ADO.NET (Core Reference). Microsoft Press, 2002.

6. Kalen Delaney . Inside SQL Server 2000. Microsoft Press, 2000.

7. .NET Data Access Architecture Guide. msdn.microsoft.com/library/en-us/dnbda/html/daag.asp.

8. Designing Data Tier Components and Passing Data Through Tiers. www.microsoft.com/downloads/release.asp?ReleaseID=44269.

9. Microsoft Support Webcast: XML Integration in ADO.NET. support.microsoft.com/servicedesks/webcasts/wc022202/wcblurb022202.asp.

10. Microsoft Support Webcast: Microsoft ADO.NET: Programming ADO.NET Datasets. support.microsoft.com/servicedesks/webcasts/wc041202/wcblurb041202.asp.

11. Microsoft Support Webcast: Programming XML in the Microsoft .NET Framework Part I. support.microsoft.com/servicedesks/webcasts/wc121801/wcblurb121801.asp.

12. Microsoft Support Webcast: Programming XML in the Microsoft .NET Framework Part II. support.microsoft.com/servicedesks/webcasts/wc010302/wcblurb010302.asp.

13. Microsoft Support WebCast: Accessing Data with Microsoft Visual C# Applications. support.microsoft.com/servicedesks/webcasts/wc092101/wcblurb092101.asp.

14. Microsoft Support WebCast: ADO.NET. support.microsoft.com/servicedesks/webcasts/wc081401/wcblurb081401.asp.

15. The .NET Show: ADO.NET. msdn.microsoft.com/theshow/Episode017.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.134.88.228