System.Data.SqlClient Namespace

I l @ ve RuBoard

System.Data.SqlClient Namespace

The System.Data.SqlClient namespace is the .NET Data Provider for SQL Server. The classes in this namespace bypass OLE DB and communicate directly with SQL Server, therefore increasing performance tremendously when using SQL Server.

SqlCommand Object Members

The SqlCommand class represents a Transact -SQL query or stored procedure to execute at a SQL Server database. Tables B.43 through B.46 list the constructor, properties, methods , and events for the SqlCommand class.

Table B.43. SqlCommand Constructor
Name Parameters Description
SqlCommand none Initializes a new instance of the SqlCommand class.
SqlCommand cmdText (String) Initializes a new instance of the SqlCommand class with the specified command text.
SqlCommand

cmdText (String)

connection (SqlConnection)

Initializes a new instance of the SqlCommand class with the specified command text and SqlConnection .
SqlCommand

cmdText (String)

connection (SqlConnection)

Transaction (SqlTransaction)

Initializes a new instance of the SqlCommand class with the text of the query, a SqlConnection, and the Transaction.
Table B.44. SqlCommand Properties
Name Type Description
CommandText String Retrieves or sets the Transact-SQL or stored procedure to execute at the data source.
CommandTimeout Integer Retrieves or sets the wait time before terminating the attempt to connect.
CommandType CommandType Retrieves or sets how the CommandText property is interpreted.
Connection SqlConnection Retrieves or sets the SqlConnection used by this instance of SqlCommand .
Container IContainer Returns the IContainer that contains this component.
DesignTimeVisible Boolean Retrieves or sets a value indicating whether the command object should be visible in a Windows Form designer control.
Parameters SqlParameterCollection Retrieves the SqlParameterCollection .
Site ISite Retrieves or sets the site for the component.
Transaction SqlTransaction Retrieves or sets the transaction in which the SqlCommand executes.
UpdatedRowSource UpdateRowSource Retrieves or sets how command results are applied to the DataRow when used by the Update method of a DbDataAdapter .
Table B.45. SqlCommand Methods
Name Parameters Description
Cancel none Cancels the execution of the SqlCommand .
CreateObjRef requestedType (Type) Creates an object that contains all information necessary to generate a proxy used for communicating with a remote object.
CreateParameter none Creates an instance of SqlParameter .
Dispose none Releases all resources held by the component.
Dispose disposing (Boolean) Releases the unmanaged resources used by the MarshalByValueComponent and optionally releases the managed resources.
Equals obj (Object) Determines whether the specified Object is the same instance as the current Object .
Equals

objA (Object)

objB (Object)

Determines whether the specified object instances are equal.
ExecuteNonQuery none Executes a Transact-SQL statement against the data source and returns the number of rows affected.
ExecuteReader none Sends the CommandText to the Connection and builds a SqlDataReader .
ExecuteReader behavior (CommandBehavior) Sends the CommandText to the Connection and builds a SqlDataReader using one of the CammandBehavior values.
ExecuteScalar none Executes the query and returns the value in the first column of the first row of the result set.
ExecuteXmlReader none Sends the CommandText to the Connection and builds an XmlReader object.
GetHashCode none Serves as a hash function for a particular type.
GetLifetimeService none Retrieves a lifetime service object that controls the lifetime policy for this instance.
GetType none Retrieves the Type of the object.
InitializeLifetime Service none Allows object to provide its own lease and control its own lifetime.
Prepare none Creates a prepared version of the command on an instance of SQL Server.
ResetCommandTimeout none Resets the Command time out back to its default value.
ToString none Returns a String that represents the current object.
Table B.46. SqlCommand Events
Name Description
Disposed Adds an event handler to listen to the Disposed event of the component.

SqlConnection Object Members

The SqlConnection class represents an open connection to a Sql Server database. Tables B.47 through B.50 list the constructor, properties, methods, and events for the SqlCollection class.

Table B.47. SqlConnection Constructor
Name Parameters Description
SqlConnection none Initializes a new instance of the SqlConnection class.
SqlConnection connectionString (String) Initializes a new instance of the SqlConnection class with the specified connection string.
Table B.48. SqlConnection Properties
Name Type Description
ConnectionString String Retrieves or sets the string used to open a data store in SQL Server.
ConnectionTimeout Integer Retrieves or sets the time to wait while establishing a connection before terminating the attempt and generating an error.
Container IContainer Returns the IContainer that contains the component.
Database String Retrieves or sets the name of the current database or the database to be used after a connection is opened.
DataSource String Retrieves or sets the name of the database to connect to.
PacketSize Integer Gets the size (in bytes) of the network packets used for communicating with a SQL Server instance.
ServerVersion String Retrieves a string containing the version of the connected Sql Server.
Site ISite Retrieves or sets the site of the Component .
State DBObjectState Retrieves the current state of the connection.
WorkstationID String Retrieves a string that identifies the database client.
Table B.49. SqlConnection Methods
Name Parameters Description
BeginTransaction none Begins a database transaction.
BeginTransaction iso (IsolationLevel) Begins a database transaction with the specified isolation level.
BeginTransaction transactionName (String) Begins a database transaction with the specified transaction name.
BeginTransaction

iso (IsolationLevel)

transactionName (String)

Begins a database transaction with the specifed isolation level and transaction name.
ChangeDatabase database (String) Changes the current database for an open SqlConnection .
Close none Closes the connection to the database.
CreateCommand none Creates and returns a SqlCommand object associated with the SqlConnection.
CreateObjRef requestedType (Type) Creates an object that contains all information necessary to generate a proxy used for communicating with a remote object.
Dispose none Releases all resources held by the component.
Dispose disposing (Boolean) Releases the unmanaged resources used by the MarshalByValueComponent and optionally releases the managed resources.
Equals obj (Object) Determines whether the specified Object is the same instance as the current Object .
Equals

objA (Object)

objB (Object)

Determines whether the specified object instances are equal.
GetHashCode none Serves as a hash function for a particular type.
GetLifetimeService none Retrieves a lifetime service object that controls the lifetime policy for this instance.
GetType none Retrieves the Type of the Object .
InitializeLifetime Service none Allows object to provide its own lease and control its own lifetime.
Open none Opens a database connection with the current property settings.
ToString none Returns a String object that represents the current object.
Table B.50. SqlConnection Events
Name Description
Disposed Adds an event handler to listen to the Disposed event of the component.
InfoMessage Fires when the provider sends a warning or a message.
StateChange Fires when the state of the connection changes.

SqlDataReader Object Members

The SqlDataReader class represents a way of reading a forward-only stream of data records from a Sql Server. Tables B.51 and B.52 list the properties and methods for the SqlDataReader class.

Table B.51. SqlDataReader Properties
Name Type Description
Depth Integer Retrieves a value indicating the depth of the nesting for the current row.
FieldCount Integer Indicates the number of fields within the current record. This is a read-only property.
IsClosed Boolean Indicates whether the data reader is closed. This is a read-only property.
Item Object Indicates the value for a column.
RecordsAffected Integer Retrieves the number of rows changed, inserted, or deleted by execution of the Transact-SQL statement.
Table B.52. SqlDataReader Methods
Name Parameters Description
Close none Closes the data reader object.
CreateObjRef requestedType (Type) Creates an object that contains all information necessary to generate a proxy used for communicating with a remote object.
Equals obj (Object) Determines whether the specified Object is the same instance as the current Object .
Equals

objA (Object)

objB (Object)

Determines whether the specified object instances are equal.
GetBoolean ordinal (Integer) Retrieves the value of the specified column as a Boolean.
GetByte ordinal (Integer) Retrieves the value of the specified column as a byte.
GetBytes

ordinal (Integer)

(Long) buffer[] (Byte)

bufferIndex (Integer)

length (Integer)

Retrieves the dataIndex value of the specified column as a byte array.
GetChar ordinal (Integer) Retrieves the value of the specified column as a character.
GetChars

ordinal (Integer)

dataIndex (Long)

buffer[] (Char)

bufferIndex (Integer)

length (Integer)

Retrieves the value of the specified column as a character array.
GetData ordinal (Integer) Not currently supported.
GetDataTypeName ordinal (Integer) Retrieves the name of the back-end data type.
GetDateTime ordinal (Integer) Retrieves the value of the specified column as a DateTime object.
GetDecimal ordinal (Integer) Retrieves the value of the specified column as a Decimal object.
GetDouble ordinal (Integer) Retrieves the value of the specified column as a double-precision floating-point number.
GetFieldType ordinal (Integer) Retrieves the Type that is the data type of the object.
GetFloat ordinal (Integer) Retrieves the value of the specified column as a single-precision floating-point number.
GetGuid ordinal (Integer) Retrieves the value of the specified column as a globally unique identifier.
GetHashCode none Serves as a hash function for a particular type.
GetInt16 ordinal (Integer) Retrieves the value of the specified column as a 16-bit signed integer.
GetInt32 ordinal (Integer) Retrieves the value of the specified column as a 32-bit signed integer.
GetInt64 ordinal (Integer) Retrieves the value of the specified column as a 64-bit signed integer.
GetName ordinal (Integer) Retrieves the name of the specified column.
GetOrdinal name (String) Retrieves the column ordinal given the specified column name.
GetSchemaTable none Returns a DataTable that describes the column metadata of the SqlDataReader .
GetSqlBinary ordinal (Integer) Retrieves the value of the specified column as a SqlBinary object.
GetSqlBoolean ordinal (Integer) Retrieves the value of the specified column as a SqlBoolean object.
GetSqlByte ordinal (Integer) Retrieves the value of the specified column as a SqlByte object.
GetSqlDateTime ordinal (Integer) Retrieves the value of the specified column as a SqlDateTime object.
GetSqlDouble ordinal (Integer) Retrieves the value of the specified column as a SqlDouble object.
GetSqlGuid ordinal (Integer) Retrieves the value of the specified column as a SqlGuid object.
GetSqlInt16 ordinal (Integer) Retrieves the value of the specified column as a SqlInt16 object.
GetSqlInt32 ordinal (Integer) Retrieves the value of the specified column as a SqlInt32 object.
GetSqlInt64 ordinal (Integer) Retrieves the value of the specified column as a SqlInt64 object.
GetSqlMoney ordinal (Integer) Retrieves the value of the specified column as a SqlMoney object.
GetSqlSingle ordinal (Integer) Retrieves the value of the specified column as a SqlSingle object.
GetSqlString ordinal (Integer) Retrieves the value of the specified column as a SqlString object.
GetSqlValue ordinal (Integer) Retrieves an Object that is a representation of the underlying SqlDbType Variant .
GetSqlValues values[] (Object) Retrieves all the attribute fields in the collection for the current column.
GetString ordinal (Integer) Returns the value of the specified column as a string.
GetType none Retrieves the Type of the Object .
GetValue

ordinal (Integer)

value (Object)

Retrieves the value of the specified column.
GetValues values[] (Object) Retrieves all the attribute fields in the collection for the current record.
InitializeLifetime Service none Allows object to provide its own lease and control its own lifetime.
IsDBNull ordinal (Integer) Retrieves a value indicating a nonexistent value.
NextResult none Advances the data reader to the next result set.
Read none Advances the data reader to the next record.
ToString none Returns a String representing the current Object .

SqlDataAdapter Object Members

The SqlDataAdapter represents a set of data commands and a database connection that are used to fill a data set and update the data source. Tables B.53 through B.56 list the constructor, properties, methods, and events for the SqlDataAdapter class.

Table B.53. SqlDataAdapter Constructor
Name Parameters Description
SqlDataAdapter none Initializes a new instance of the SqlDataAdapter class.
SqlDataAdapter selectCommand (SqlCommand) Initializes a new instance of the SqlDataAdapter class with the specified select command.
SqlDataAdapter

selectCommandText (String)

selectConnection (SqlConnection)

Initializes a new sinstance of the SqlDataAdapter class with the specified select statement and SqlConnection object.
SqlDataAdapter

selectCommandText (String)

selectConnectionString (String)

Initializes a new instance of the SqlDataAdapter class with the specified select statement and connection string.
Table B.54. SqlDataAdapter Properties
Name Type Description
AcceptChangesDuringFill Boolean Retrieves or sets a value indicating whether AcceptChanges is called on a DataRow after it is added to a DataTable .
Container IContainer Returns the IContainer that contains the component.
DeleteCommand SqlCommand Retrieves or sets a command to delete records from the data set.
InsertCommand SqlCommand Retrieves or sets a command to insert records into the data source.
MissingMappingAction MissingMappingAction Retrieves or sets whether unmapped source tables or columns are to be passed with their source names to be filtered.
MissingSchemaAction MissingSchemaAction Retrieves or sets whether missing source tables, columns, and their relationships are to be added to the data set schema or ignored or whether to raise an error.
SelectCommand SqlCommand Retrieves or sets a command used to select records in the data source.
Site ISite Retrieves or sets the site of the Component .
TableMappings DataTableMappings Retrieves how a source table is to be mapped to a data set table.
UpdateCommnad SqlCommand Retrieves or sets a command used to update records in the data source.
Table B.55. SqlDataAdapter Methods
Name Parameters Description
CreateObjRef requestType (Type) Creates an object that contains all information necessary to generate a proxy used for communicating with a remote object.
Dispose none Releases all resources held by the component.
Dispose disposing (Boolean) Releases the unmanaged resources used by the MarshalByValueComponent and optionally releases the managed resources.
Equals obj (Object) Determines whether the specified Object is the same instance as the current Object .
Equals

objA (Object)

objB (Object)

Determines whether the specified object instances are equal.
Fill dataSet (DataSet) Fills the DataSet with records from the data source.
Fill dataTable (DataTable) Adds or refreshes rows in a DataTable to match those in the data source using the DataTable name.
Fill

dataSet (DataSet)

srcTable (String)

Fills the DataSet with records from the specified source table.
Fill

dataTable(DataTable)

dataReader (IDataReader)

Add or refreshes rows in a DataTable to match those in the datasource using the specified DataTable and IDataReader .
Fill

dataTable (DataTable)

Command (IDbCommand)

Behavior (CommandBehavior)

Adds or refreshes rows in a DataTable to match those in the data sourceusing the DataTable name, the specified SQL SELECT statement, and CommandBehavior .
Fill

dataSet (DataSet)

startRecord (Integer)

maxRecords (Integer)

srcTable (String)

Fills the DataSet with records located between the given bounds from the specified source table.
Fill

dataSet (DataSet)

srcTable (String)

dataReader (IDataReader)

startRecord (Integer)

maxRecords (Integer)

Adds or refreshes rows in a specified range in the DataSet tomatch those in the data source using the DataSet , DataTable , and IDataReader names.
Fill

dataSet (DataSet)

startRecord (Integer)

maxRecords (Integer)

srcTable (String)

command (IDbCommand)

behavior (CommandBehavior)

Adds or refreshes rows in a specified range in the DataSet to match those in the data source using the DataSet and source table names, command string, and command behavior.
FillSchema

dataSet (DataSet)

schemaType (SchemaType)

Adds a DataTable named "Table" to the specified DataSet and configuresthe schema to match that in the data source based on the specified SchemaType .
FillSchema

dataTable (DataTable)

schemaType (SchemaType)

Adds a DataTable and configures the schema of the table based on the specified SchemaType.
FillSchema

dataSet (DataSet)

schemaType (SchemaType)

srcTable (String)

Adds a DataTable to the specified DataSet and configures the schema to match that in the data source based on the specified SchemaType and DataTable .
FillSchema

dataSet (DataSet)

schemaType (SchemaType)

command (IDBCommand)

behavior (CommandBehavior)

Adds a DataTable to the specified DataSet and configures the schema to match that in the datasource based on the specified SchemaType.
GetFill Parameters none Returns all parameters used when performing the select command.
GetHashCode none Serves as a hash function for a particular type.
GetLifetime none Retrieves a lifetime service object that Service controls the lifetime policy for this instance.
GetType none Retrieves the Type of the Object .
Initialize LifetimeService none Allows object to provide its own lease and control its own lifetime.
ToString none Returns a String representing the current object.
Update dataRows[] (DataRow) Calls the respective insert , update , or delete commands for each inserted, updated, or deleted row in the given array of DataRow objects.
Update dataset (DataSet) Calls the respective insert , update , or delete commands for each inserted, updated, or deleted row in the specified data set.
Update dataTable (DataTable) Calls the respective insert , update , or delete statements for each inserted, updated, or deleted row in the specified DataTable .
Update

dataRows[] (DataRow)

tableMapping

(DataTableMapping)

Calls the respective insert , update , or delete statements for each inserted, updated, or deleted row in the specified array of DataRows .
Update

dataset (DataSet)

srcTable (String)

Calls the respective insert , update , or delete commands for each inserted, updated, or deleted row in the specified data set with the given source table name.
Table B.56. SqlDataAdapter Events
Name Description
Disposed Adds an event handler to listen to the Disposed event on a component.
FillError Occurs when an error occurs during a fill operation.
RowUpdated Fires during Update after a command is executed against the data source.
RowUpdating Fires during Update before a command is executed against the data source.

SqlError Object Members

The SqlError class collects information relevant to a warning or error returned by Sql Server. Tables B.57 and B.58 list the properties and methods for the SqlError class.

Table B.57. SqlError Properties
Name Type Description
Class Byte Retrieves the severity level of the error returned from the Sql Server adapter.
LineNumber Integer Retrieves the line number within the Sql command batch or stored procedure that had the error.
Message String Retrieves the text describing the error.
Number Integer Retrieves the number that identifies the type of error.
Procedure String Retrieves the name of the stored procedure that generated the error.
Server String Retrieves the name of the database server that generated the error.
Source String Retrieves the line of source code that generated the error.
State Byte Retrieves the number modifying the error to provide additional information.
Table B.58. SqlError Methods
Name Parameters Description
Equals obj (Object) Determines whether the specified Object is the same instance as the current Object .
Equals

objA (Object)

objB (Object)

Determines whether the specified object instances are equal.
GetHashCode none Serves as a hash function for a particular type.
GetType none Retrieves the Type of the Object .
ToString none Returns the entire text of the SqlError .

SqlParameter Object Members

The SqlParameter class represents a parameter to a SqlCommand object. Tables B.59 through B.61 list the constructor, properties, and methods for the SqlParameter class.

Table B.59. SqlParameter Constructor
Name Parameters Description
SqlParameter none Initializes a new instance of the SqlParameter class.
SqlParameter

name (String)

Value (Object)

Initializes a new instance of the SqlParameter class with the parameter name and an SqlParameter object.
SqlParameter

parameterName (String)

dbType(SqlDbType)

Initializes a new instance of the SqlParameter class with the specified name and SqlDbType .
SqlParameter

parameterName (String)

dbType (SqlDbType)

size (Integer)

Initializes a new instance of the SqlParameter class with the specified name, SqlDbType , and size.
SqlParameter

parameterName (String)

dbType (SqlDbType)

size (Integer)

sourceColumn (String)

Initializes a new instance of the SqlParameter class with the specified name, SqlDbType , size, and source column name.
SqlParameter

parameterName (String)

dbType (SqlDbType)

size (Integer)

direction (ParameterDirection)

isNullable (Boolean)

precision (Byte)

scale (Byte)

sourceColumn (String)

sourceVersion (DataRowVersion)

value (Object)

Initializes a new instance of the SqlParameter class with the specified name, direction, parameter precision, parameter scale, source column name, DataRowVersion , and the value of the parameter. SqlDbType , size, parameter.
Table B.60. SqlParameter Properties
Name Type Description
DbType DbType Retrieves or sets the native data type from the data source.
Direction ParameterDirection Retrieves or sets whether the parameter is input only, output only, bidirectional, or a return value parameter.
IsNullable Boolean Retrieves or sets whether the parameter accepts null values.
Offset Integer Retrieves or sets the offset to the value.
ParameterName String Retrieves or sets the name of the SqlParameter .
Precision Byte Retrieves or sets the maximum number of digits used to represent the value.
Scale Byte Retrieves or sets the number of decimal places to which Value is resolved.
Size Integer Retrieves or sets the maximum size, in bytes, of the data within the field.
SourceColumn String Retrieves or sets the name of the source column mapped to the DataSet .
SourceVersion DataRowVersion Retrieves or sets the DataRowVersion to use when loading a Value .
SqlDbType SqlDbType Retrieves or sets the SqlDbType for the parameter.
Value Object Retrieves or sets the value of the parameter.
Table B.61. SqlParameter Methods
Name Parameters Description
CreateObjRef requestedType (Type) Creates an object that contains all information necessary to generate a proxy used for communicating with a remote object.
Equals obj (Object) Determines whether the specified Object is the same instance as the current Object .
Equals

objA (Object)

objB (Object)

Determines whether the specified object instances are equal.
GetHashCode none Serves as a hash function for a particular type.
GetLifetimeService none Retrieves a lifetime service object that controls the lifetime policy for this instance.
GetType none Retrieves the Type of the Object .
InitializeLifetimeService none Allows object to provide its own lease and control its own lifetime.
ToString none Returns a String representing the current object.
I l @ ve RuBoard


Asp. Net. By Example
ASP.NET by Example
ISBN: 0789725622
EAN: 2147483647
Year: 2001
Pages: 154

Similar book on Amazon

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