The SqlCommand Class

The SqlCommand Class

You use an object of the SqlCommand class to execute a command against a SQL Server database, an object of the OleDbCommand class to execute a command against any database that supports OLE DB, such as Oracle or Access, and an object of the OdbcCommand class to execute a command against any database that supports ODBC. Table 8.1 shows some of the SqlCommand properties. Table 8.2 shows some of the SqlCommand methods. You'll learn how to use some of these properties and methods in this chapter.

Table 8.1: SqlCommand PROPERTIES

PROPERTY

TYPE

DESCRIPTION

CommandText

string

Gets or sets the SQL statement, stored procedure call, or table to retrieve from.

CommandTimeout

int

Gets or sets the number of seconds to wait before ending an attempt to execute the command. The default is 30 seconds.

CommandType

CommandType

Gets or sets a value that indicates how the CommandText property is to be interpreted. Valid values are CommandType.Text, CommandType .StoredProcedure, and CommandType .TableDirect. Text indicates the command is a SQL statement. StoredProcedure indicates the command is a stored procedure call. TableDirect indicates the name of a table, for which all rows and columns are to be retrieved. The default is Text.

Connection

string

Gets the name of the database connection.

DesignTimeVisible

bool

Gets or sets a Boolean value that indicates whether the Command object is visible in a Windows Forms Designer control. The default is false.

Parameters

SqlParameterCollection

Gets the parameters (if any) to supply to the command. When using a SqlConnection, the parameters are stored in a SqlParameterCollection object.

Transaction

SqlTransaction

Gets or sets the database transaction for the command.

UpdatedRowSource

UpdateRowSource

Gets or sets how the command results are to be applied to a DataRow object when the Update() method of a DataAdapter object is called.

Table 8.2: SqlCommand METHODS

METHOD

RETURN TYPE

DESCRIPTION

Cancel()

void

Cancels the execution of the command.

CreateParameter()

SqlParameter

Creates a new parameter for the command.

ExecuteNonQuery()

int

Used to execute SQL statements that don't return a result set. These statements include INSERT, UPDATE, and DELETE statements, Data Definition Language statements, or stored procedure calls that don't return a result set. The int value returned is the number of database rows affected by the command, if any.

ExecuteReader()

SqlDataReader

Used to execute SQL SELECT statements, TableDirect commands, or stored procedures that return a result set. Returns the result set in a DataReader object.

ExecuteScalar()

object

Used to execute SQL SELECT statements that return a single value (any other values are ignored). Returns the result of the command as an object.

ExecuteXmlReader()

XmlReader

Used to execute SQL SELECT statements that return XML data. Returns the result set in an XmlReader object. Applies only to the SqlCommand class.

Prepare()

void

Creates a prepared version of the command. Sometimes results in faster execution of the command.

ResetCommandTimeout()

void

Resets the CommandTimeout property to its default value.

Note 

Although the SqlCommand class is specific to SQL Server, many of the properties and methods in this class are the same as those for the OleDbCommand and OdbcCommand classes. If a property or method is specific to SqlCommand, it says so in the Description column of the tables shown in this section.

Tip 

You're actually better off using the T-SQL EXECUTE command rather than CommandType.StoredProcedure to execute a stored procedure. This is because you can read values that are returned from a stored procedure through a RETURN statement, which you can't do when setting the CommandType to StoredProcedure. See the section "Executing SQL Server Stored Procedures" later in this chapter.




Mastering C# Database Programming
Mastering the SAP Business Information Warehouse: Leveraging the Business Intelligence Capabilities of SAP NetWeaver
ISBN: 0764596373
EAN: 2147483647
Year: 2003
Pages: 181

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net