ADO.NET摘要1

DataSet vs. DataReader

To determine whether to use the DataSet or the DataReader when you design your application, consider the level of functionality that is needed in the application.

Use the DataSet in order to do the following with your application:

• Navigate between multiple discrete tables of results.
• Manipulate data from multiple sources (for example, a mixture of data from more than one database, from an XML file, and from a spreadsheet).
• Exchange data between tiers or using an XML Web service. Unlike the DataReader, the DataSet can be passed to a remote client.
• Reuse the same set of rows to achieve a performance gain by caching them (such as for sorting, searching, or filtering the data).
• Perform a large amount of processing per row. Extended processing on each row returned using a DataReader ties up the connection serving theDataReader longer than necessary, impacting performance.
• Manipulate data using XML operations such as Extensible Stylesheet Language Transformations (XSLT transformations) or XPath queries.

Use the DataReader in your application if you:

• Do not need to cache the data.
• Are processing a set of results too large to fit into memory.
• Need to quickly access data once, in a forward-only and read-only manner.

Note   The DataAdapter uses the DataReader when filling a DataSet. Therefore, the performance gained by using the DataReader instead of the DataSet is that you save on the memory that the DataSet would consume and the cycles it takes to populate the DataSet. This performance gain is, for the most part, nominal so you should base your design decisions on the functionality required.   The DataAdapter uses the DataReader when filling a DataSet. Therefore, the performance gained by using the DataReader instead of the DataSet is that you save on the memory that the DataSet would consume and the cycles it takes to populate the DataSet

Using the DataReader

The following are some tips for best performance using a DataReader, as well as, answers to common questions regarding the use of the DataReader.

• The DataReader must be closed before accessing any output parameters for the associated Command.
• Always close the DataReader when you are finished reading the data. If the Connection you are using is only used to return the DataReader, close it immediately after closing the DataReader.

  An alternative to explicitly closing the Connection is to pass CommandBehavior.CloseConnection to the ExecuteReader method to ensure that the associated connection is closed when the DataReader is closed. This is especially useful if you are returning a DataReader from a method and do not have control over the closing of the DataReader or associated connection.

• The DataReader cannot be remoted between tiers. The DataReader is designed for connected data access.
• When accessing column data use the typed accessors like GetString, GetInt32, and so on. This saves you the processing required to cast the Objectreturned from GetValue as a particular type.
• Only one DataReader can be open at a time, off of a single connection. In ADO, if you opened a single connection and requested two recordsets that used a forward-only, read-only cursor, ADO implicitly opens a second, unpooled connection to the data store for the life of that cursor, and then implicitly closes it. With ADO.NET, little is done for you "under-the-covers". If you want two DataReaders open at the same time, off the same data store, you have to explicitly create two connections, one for each DataReader. This is one way that ADO.NET gives you more control over the use of pooled connections.
• By default, the DataReader loads an entire row into memory with each Read. This allows for random access of columns within the current row. If this random access is not necessary, for increased performance, pass CommandBehavior.SequentialAccess to the call to ExecuteReader. This changes the default behavior of the DataReader to only load data into memory when it is requested. Note that, CommandBehavior.SequentialAccessrequires you to access returned columns in order. That is, once you have read past a returned column, you can no longer read its value.
• If you are finished reading the data from a DataReader, but still have a large number of unread results pending, call Cancel on the Command prior to calling Close on the DataReader. Calling Close on the DataReader causes it to retrieve pending results and empty the stream prior to closing the cursor. Calling Cancel on the Command discards results on the server so that the DataReader does not have to read though them when it is closed. If you are returning output parameters from your Command, calling Cancel discards them as well. If you need to read any output parameters, do not call Cancel on the Command; just call Close on the DataReader.

Using Commands

ExecuteScalar and ExecuteNonQuery

If you want to return a single value such as the result of Count(*), Sum(Price), or Avg(Quantity), you can use Command.ExecuteScalar. ExecuteScalarreturns the value of the first column of the first row, returning result set as a scalar value. ExecuteScalar both simplifies your code and improves performance by accomplishing in a single step, what would have been a two-step process using a DataReader (that is, ExecuteReader + Get the value).

When using SQL statements that do not return rows, like those that modify data (such as INSERT, UPDATE, or DELETE) or return only output parameters or return values, use ExecuteNonQuery. This removes any unnecessary processing to create an empty DataReader.

For more information, see Executing a Command.

Testing for Null

If a column in a table (in your database) allows nulls, you cannot test for a parameter value of "equal to" null. Instead, you need to write a WHERE clause to test whether both the column is null and the parameter is null. The following SQL statement returns rows where the LastName column equals the value assigned to the @LastName parameter, or whether both the LastName column and the @LastName parameter are null.

SELECT * FROM Customers
WHERE ((LastName = @LastName) OR (LastName IS NULL AND @LastName IS NULL))

Passing Null as a Parameter Value

When sending a null value as a Parameter value in a command to the database, you cannot use null (Nothing in Visual Basic® .NET). Instead you need to use DBNull.Value. For example:

'Visual Basic
Dim param As SqlParameter = New SqlParameter("@Name", SqlDbType.NVarChar, 20)
param.Value = DBNull.Value

//C#
SqlParameter param = new SqlParameter("@Name", SqlDbType.NVarChar, 20);
param.Value = DBNull.Value;

Performing Transactions

The transaction model has changed for ADO.NET. In ADO, when StartTransaction was called, any update following the call is considered part of the transaction. However, in ADO.NET, when Connection.BeginTransaction is called, a Transaction object is returned that needs to be associated with theTransaction property of a Command. This design enables you to perform multiple root transactions off of a single connection. If theCommand.Transaction property is not set to a Transaction that has been started for the associated Connection, the Command fails and an exception is thrown.

Upcoming releases of the .NET Framework will enable you to manually enlist in an existing distributed transaction. This is ideal for an object pooling scenario where a connection is opened once for a pooled object, but the object is involved in multiple separate transactions. This capability is not available in the .NET Framework 1.0 release.

SqlCommand.Parameters Property

.NET Framework 4

Other Versions

4 out of 10 rated this helpful - Rate this topic

Gets the SqlParameterCollection.

Namespace:   System.Data.SqlClient
Assembly:   System.Data (in System.Data.dll)

Syntax

---

C#

C++

F#

VB

public SqlParameterCollection Parameters { get; }

Property Value

Type:  System.Data.SqlClient.SqlParameterCollection
The parameters of the Transact-SQL statement or stored procedure. The default is an empty collection.

Remarks

---

The Microsoft .NET Framework Data Provider for SQL Server does not support the question mark (?) placeholder for passing parameters to a SQL Statement or a stored procedure called by a command of CommandType.Text. In this case, named parameters must be used. For example:

SELECT * FROM Customers WHERE CustomerID = @CustomerID

Note
If the parameters in the collection do not match the requirements of the query to be executed, an error may result.

For more information, see Configuring Parameters and Parameter Data Types (ADO.NET).

Examples

---

The following example demonstrates how to create a SqlCommand and add parameters to the SqlParameterCollection.

C#

VB

private static void UpdateDemographics(Int32 customerID,
    string demoXml, string connectionString)
{
    // Update the demographics for a store, which is stored 
    // in an xml column. 
    string commandText = "UPDATE Sales.Store SET Demographics = @demographics "
        + "WHERE CustomerID = @ID;";

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        SqlCommand command = new SqlCommand(commandText, connection);
        command.Parameters.Add("@ID", SqlDbType.Int);
        command.Parameters["@ID"].Value = customerID;

        // Use AddWithValue to assign Demographics.
        // SQL Server will implicitly convert strings into XML.
        command.Parameters.AddWithValue("@demographics", demoXml);

        try
        {
            connection.Open();
            Int32 rowsAffected = command.ExecuteNonQuery();
            Console.WriteLine("RowsAffected: {0}", rowsAffected);
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
    }
}

SqlCommand.ExecuteScalar Method

Executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored.

Return Value

Type:  System.Object
The first column of the first row in the result set, or a null reference (Nothing in Visual Basic) if the result set is empty. Returns a maximum of 2033 characters.

SqlCommand Example

private static void ReadOrderData(string connectionString)
{
    string queryString = 
        "SELECT OrderID, CustomerID FROM dbo.Orders;";
    using (SqlConnection connection = new SqlConnection(
               connectionString))
    {
        SqlCommand command = new SqlCommand(
            queryString, connection);
        connection.Open();
        SqlDataReader reader = command.ExecuteReader();
        try
        {
            while (reader.Read())
            {
                Console.WriteLine(String.Format("{0}, {1}",
                    reader[0], reader[1]));
            }
        }
        finally
        {
            // Always call Close when done reading.
            reader.Close();
        }
    }
}

SqlTransaction  Example

private static void ExecuteSqlTransaction(string connectionString)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();

        SqlCommand command = connection.CreateCommand();
        SqlTransaction transaction;

        // Start a local transaction.
        transaction = connection.BeginTransaction("SampleTransaction");

        // Must assign both transaction object and connection
        // to Command object for a pending local transaction
        command.Connection = connection;
        command.Transaction = transaction;

        try
        {
            command.CommandText =
                "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')";
            command.ExecuteNonQuery();
            command.CommandText =
                "Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description')";
            command.ExecuteNonQuery();

            // Attempt to commit the transaction.
            transaction.Commit();
            Console.WriteLine("Both records are written to database.");
        }
        catch (Exception ex)
        {
            Console.WriteLine("Commit Exception Type: {0}", ex.GetType());
            Console.WriteLine("  Message: {0}", ex.Message);

            // Attempt to roll back the transaction.
            try
            {
                transaction.Rollback();
            }
            catch (Exception ex2)
            {
                // This catch block will handle any errors that may have occurred
                // on the server that would cause the rollback to fail, such as
                // a closed connection.
                Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
                Console.WriteLine("  Message: {0}", ex2.Message);
            }
        }
    }
}