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

Delegates

Enumerations

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

Delegates

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