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. | |
