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