Real Life SQL and .NET :Using SQL with C#: Part VIII


Using SQL with C#

A .NET data provider describes a collection of classes used to access a data source, such as a database, in the managed space. Using the OleDbDataAdapter, you can fill a memory-resident DataSet, which you can use to query and update the datasource.

Classes

 

 

 


 

 

 

 



 

Class
Description.
OleDbCommand
Represents an SQL statement or stored procedure to execute against a data source.
OleDbCommandBuilder
Provides a means of automatically generating single-table commands used to reconcile changes made to a DataSet with the associated database. This class cannot be inherited.
OleDbConnection
 Represents an open connection to a data source.
OleDbDataAdapter

 Represents a set of data commands and a database connection that are used to fill the DataSet and update the data source.
OleDbDataReader
Provides a way of reading a forward-only stream of data rows from a data source. This class cannot be inherited.
OleDbError
Collects information relevant to a warning or error returned by the data source. This class cannot be inherited.
OleDbErrorCollection
Collects all errors generated by the OLE DB .NET Data Provider. This class cannot be inherited.
OleDbException
The exception that is thrown when the underlying provider returns a warning or error for an OLE DB data source. This class cannot be inherited.
OleDbInfoMessageEventArgs
Provides data for the InfoMessage event. This class cannot be inherited.
OleDbParameter
Represents a parameter to an OleDbCommand and optionally, its mapping to a DataSet column. This class cannot be inherited.
 
OleDbParameterCollection
Collects all parameters relevant to an OleDbCommand as well as their respective mappings to DataSet columns.
OleDbPermission
 Provides the capability for the OLE DB .NET Data Provider to ensure that a user has a security level adequate to access an OLE DB data source.
OleDbPermissionAttribute
Associates a security action with a custom security attribute.
OleDbRowUpdatedEventArgs
Provides data for the RowUpdated event.
OleDbRowUpdatingEventArgs
Provides data for the RowUpdating event.
OleDbSchemaGuid
Returns the type of schema table specified by the GetOleDbSchemaTable method.
OleDbTransaction
Represents an SQL transaction to be made at a data source. This class cannot be inherited

Delegates

Delegate
Description
OleDbInfoMessageEventHandler
Represents the method that will handle the InfoMessage event of an OleDbConnection.
OleDbRowUpdatedEventHandler
Represents the method that will handle the RowUpdated event of an OleDbDataAdapter.
OleDbRowUpdatingEventHandler
Represents the method that will handle the RowUpdating event of an OleDbDataAdapter.
 

Enumerations

Enumeration Description
OleDbLiteral
Returns information about literals used in text commands, data values, and database objects.
OleDbType
Specifies the data type of a field, a property, or an OleDbParameter.

OleDbDataAdapter Class

Represents a set of data commands and a database connection that are used to fill the DataSet and update the data source. For a list of all members of this type, see OleDbDataAdapter Members.

System.Object
            System.MarshalByRefObject
            System.ComponentModel.Component
            System.Data.Common.DataAdapter
            System.Data.Common.DbDataAdapter
            System.Data.OleDb.OleDbDataAdapter

And syntax in C#:

public sealed class OleDbDataAdapter: DbDataAdapter, IDbDataAdapter;

Any public static members of this type are safe for multithreaded operations. Any instance members are not guaranteed to be thread safe. The OleDbDataAdapter serves as a bridge between a DataSet and data source for retrieving and saving data. The OleDbDataAdapter provides this bridge by using Fill to load data from the data source into the DataSet, and using Update to send changes made in the DataSet back to the data source.

When the OleDbDataAdapter fills a DataSet, it will create the necessary tables and columns for the returned data if they do not already exist. However, primary key information will not be included in the implicitly created schema unless the MissingSchemaAction property is set to AddWithKey. You may also have the OleDbDataAdapter create the schema of the DataSet, including primary key information, before filling it with data using FillSchema. Note that some OLE DB providers, including the MSDataShape provider, do not return base table or primary key information. As a result, the OleDbDataAdapter cannot properly set the PrimaryKey property on any created DataTable. In such cases you should explicitly specify primary keys for tables in the DataSet. The OleDbDataAdapter also includes the SelectCommand, InsertCommand, DeleteCommand, UpdateCommand, and TableMappings properties to facilitate the loading and updating of data.

When you create an instance of OleDbDataAdapter, properties are set to their initial values. For a list of these values, see the OleDbDataAdapter constructor. The following example uses the OleDbCommand, OleDbDataAdapter, and OleDbConnection, to select records from an Access data source, and populate a DataSet with the selected rows. The filled DataSet is then returned. To accomplish this, the method is passed an initialized DataSet, a connection string, and a query string that is a SQL SELECT statement.

public DataSet SelectOleDbSrvRows(DataSet dataset,string connection,string query)
{
OleDbConnection conn =
new
OleDbConnection(connection);
OleDbDataAdapter adapter =
new
OleDbDataAdapter();
adapter.SelectCommand =
new
OleDbCommand(query, conn);
adapter.Fill(dataset);
return
dataset;
}

Execute an SQL SELECT command in C#

When SelectCommand is assigned to a previously created OleDbCommand, the OleDbCommand is not cloned. The SelectCommand maintains a reference to the previously created OleDbCommand object. If the SelectCommand does not return any rows, no tables are added to the DataSet, and no exception is raised.

public void CreateOleDbDataAdapter ()
{
OleDbDataAdapter myDataAdapter =
new
OleDbDataAdapter();
myDataAdapter.SelectCommand.CommandText = "SELECT * FROM Categories ORDER BY
ategoryID";
myDataAdapter.SelectCommand.Connection.ConnectionString =
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=NWIND_RW.MDB";
}

System.Data.SqlClient Namespace

The System.Data.SqlClient namespace is the SQL Server .NET Data Provider. A .NET data provider describes a collection of classes used to access a SQL Server database in the managed space. Using the SqlDataAdapter, you can fill a memory-resident DataSet, which you can use to query and update the datasource. For information on how this namespace can help you, see the SqlDataAdapter, the SqlCommand, and the SqlConnection classes.

Classes

Class
Description
SqlClientPermission
Provides the capability for the SQL Server .NET Data Provider to ensure that a user has a security level adequate to access a data source.
SqlClientPermissionAttribute
Associates a security action with a custom security attribute.
SqlCommand
Represents a Transact-SQL statement or stored procedure to execute against a SQL Server database. This class cannot be inherited.
SqlCommandBuilder
Provides a means of automatically generating single-table commands used to reconcile changes made to a DataSet with the associated SQL Server database. This class cannot be inherited.
 
SqlConnection Represents an open connection to a SQL Server database. This class cannot be inherited.
 
SqlDataAdapter
Represents a set of data commands and a database connection that are used to fill the DataSet and update a SQL Server database. This class cannot be inherited.
SqlDataReader
Provides a means of reading a forward-only stream of rows from a SQL Server database. This class cannot be inherited.
SqlError
Collects information relevant to a warning or error returned by SQL Server. This class cannot be inherited.
SqlErrorCollection
Collects all errors generated by the SQL .NET Data Provider. This class cannot be inherited.
SqlException
 The exception that is thrown when SQL Server returns a warning or error. This class cannot be inherited.
SqlParameter
Represents a parameter to a SqlCommand, and optionally, its mapping to DataSet columns. This class cannot be inherited.
SqlParameterCollection
Collects all parameters relevant to a SqlCommand as well as their respective mappings to DataSet columns. This class cannot be inherited.
SqlRowUpdatedEventArgs
Provides data for the RowUpdated event. This class cannot be inherited.
SqlRowUpdatingEventArgs
Provides data for the RowUpdating event. This class cannot be inherited.
SqlTransaction
Represents a Transact-SQL transaction to be made in a SQL Server database. This class cannot be inherited.
SqlInfoMessageEventArgs
Provides data for the InfoMessage event. This class cannot be inherited.

Delegates

Delegate Description
SqlInfoMessageEventHandler
Represents the method that will handle the InfoMessage event of a SqlConnection.
SqlRowUpdatedEventHandler
Represents the method that will handle the RowUpdated event of a SqlDataAdapter.
SqlRowUpdatingEventHandler
Represents the method that will handle the RowUpdating event of a SqlDataAdapter.
 

SqlDataAdapter Class

Represents a set of data commands and a database connection that are used to fill the DataSet and update a SQL Server database. This class cannot be inherited. For a list of all members of this type, see SqlDataAdapter Members.

System.Object
            System.MarshalByRefObject
            System.ComponentModel.Component
            System.Data.Common.DataAdapter
            System.Data.Common.DbDataAdapter
            System.Data.SqlClient.SqlDataAdapter

public sealed class SqlDataAdapter : DbDataAdapter, IDbDataAdapter ;

Any public static members of this type are safe for multithreaded operations. Any instance members are not guaranteed to be thread safe. The SqlDataAdapter serves as a bridge between a DataSet and SQL Server for retrieving and saving data. The SqlDataAdapter provides this bridge by mapping Fill, which changes the data in the DataSet to match the data in the data source, and Update, which changes the data in the data source to match the data in the DataSet, using the appropriate Transact-SQL statements against the data source.

SqlDataAdapter is used in conjunction with SqlConnection and SqlCommand to increase performance when connecting to a Microsoft SQL Server database. To access other data sources, use OleDbDataAdapter along with its associated OleDbCommand and OleDbConnection. The SqlDataAdapter also includes the SelectCommand, InsertCommand, DeleteCommand, UpdateCommand, and TableMappings properties to facilitate the loading and updating of data.

When an instance of SqlDataAdapter is created, the read/write properties are set to initial values. For a list of these values, see the SqlDataAdapter constructor. The following example uses the SqlCommand, SqlDataAdapter, and SqlConnection, to select records from a data source, and populate a DataSet with the selected rows. The filled DataSet is then returned. To accomplish this, the method is passed an initialized DataSet, a connection string, and a query string that is a Transact-SQL SELECT statement.

public static void CreateSqlDataAdapter()
{
SqlConnection nwindConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind");
SqlDataAdapter custDA = new SqlDataAdapter();
custDA.MissingSchemaAction = MissingSchemaAction.AddWithKey;
custDA.SelectCommand = new SqlCommand("SELECT CustomerID, CompanyName FROM CUSTOMERS", nwindConn);
custDA.InsertCommand = new SqlCommand("INSERT INTO Customers (CustomerID, CompanyName) " + "VALUES (@CustomerID, @CompanyName)", nwindConn);
custDA.UpdateCommand = new SqlCommand("UPDATE Customers SET CustomerID = @CustomerID, CompanyName = @CompanyName " + "WHERE CustomerID = @oldCustomerID", nwindConn);
custDA.DeleteCommand = new SqlCommand("DELETE FROM Customers WHERE CustomerID = @CustomerID", nwindConn);
custDA.InsertCommand.Parameters.Add("@CustomerID", SqlDbType.Char, 5, "CustomerID");
custDA.InsertCommand.Parameters.Add("@CompanyName", SqlDbType.VarChar, 40, "CompanyName");
custDA.UpdateCommand.Parameters.Add("@CustomerID", SqlDbType.Char, 5, "CustomerID");
custDA.UpdateCommand.Parameters.Add("@CompanyName", SqlDbType.VarChar, 40, "CompanyName");
custDA.UpdateCommand.Parameters.Add("@oldCustomerID", SqlDbType.Char, 5, "CustomerID").SourceVersion = DataRowVersion.Original;
custDA.DeleteCommand.Parameters.Add("@CustomerID", SqlDbType.Char, 5, "CustomerID").SourceVersion = DataRowVersion.Original;} 

SqlDataAdapter.SelectCommand Property

Gets or sets a Transact-SQL statement or stored procedure used to select records in the data source.

public new SqlCommand SelectCommand {get; set;}

A SqlCommand used during Fill to select records from the database for placement in the DataSet. When SelectCommand is assigned to a previously created SqlCommand, the SqlCommand is not cloned. The SelectCommand maintains a reference to the previously created SqlCommand object. If the SelectCommand does not return any rows, no tables are added to the DataSet, and no exception is raised. The following example creates a SqlDataAdapter and sets some of its properties.

public void CreateSqlDataAdapter()
{
SqlDataAdapter myDataAdapter = new SqlDataAdapter();
myDataAdapter.SelectCommand.CommandText = "SELECT * FROM Categories ORDER BY CategoryID";
myDataAdapter.SelectCommand.Connection = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind");
myDataAdapter.UpdateCommand.CommandText = "UPDATE Categories SET Description='Cheeses, Milk, Ice Cream' WHERE CategoryName='Dairy Products'";
myDataAdapter.UpdateCommand.Connection = (SqlConnection) myDataAdapter.SelectCommand.Connection;
}

continue article