Microsoft Visual C#.NET 2003 Kick Start

2017-07-07 02:10:07

Each of these objects come in four types, each in their own namespaces, depending on the data provider you're using:

For OLE DB data providers, you use OleDbConnection , OleDbCommand , and OleDbDataAdapter objects from the System.Data.OleDb namespace.

For SQL Server, you use SqlConnection , SqlCommand , and SqlDataAdapter objects from the System.Data.SqlClient namespace.

For ODBC data providers, you use OdbcConnection , OdbcCommand , and OdbcDataAdapter objects from the System.Data.Odbc namespace.

For Oracle, you use OracleConnection , OracleCommand , and OracleDataAdapter objects from the System.Data.OracleClient namespace.

We'll see how this works by taking a look at the various data objects we needconnection objects, command objects, and data adapter objectsin detail, starting with connection objects.

Using Connection Objects in Code

To gain access to the data in a data provider, you need a connection object, as we saw in Chapter 9, "Using ADO.NET and Databases." In ADO.NET, you can create and work with connections to data providers with these connection objects:

OleDbConnection manages a connection to any data source accessible with the OLE DB protocol.

SqlConnection manages a connection to a SQL Server, version 7.0 or later. (Optimized for use with SQL Server 7.0 or later.)

OdbcConnection manages a connection to a data source created by using an ODBC connection string or ODBC data source name (DSN).

OracleConnection manages a connection to Oracle databases.

We'll take a look at these various objects next .

Working with the OleDbConnection Class

You use OleDbConnection objects to support connections to OLE DB data providers. As with other generic data objects, you should note that not all properties, methods , and events of OleDbConnection objects are supported by all data providers.

To create a connection to a data provider, you need to create a connection string of the kind we saw in Chapter 9. Connection strings are made up of attribute/value pairs separated by semicolons like this: Provider=SQLOLEDB.1; . You can assign a connection string to the connection's ConnectionString property, or pass that string to the connection object's constructor this way:

string connectionString = "Provider=SQLOLEDB.1;Integrated " + "Security=SSPI;Persist Security Info=False;Initial " + "Catalog=pubs;Packet Size=4096;Workstation ID=STEVE;" + "Use Encryption for Data=False"; OleDbConnection Connection1 = new OleDbConnection(connectionString);

As you can see, a connection string like this one is pretty obscure. If you don't know what's needed in a connection string for a specific data provider, how can you create one from scratch? As recommended in the previous chapter, you can use the visual data tools in the C# IDE to create connection strings for you. Just drag a data adapter onto a form, use it to connect to your favorite data provider, and take a look at the created connection object's ConnectionString property in the properties window, which will give you a template you can modify as needed. (Although we'll hard-code our connection strings in our code in this chapter, you can also store them outside your code, as in isolated storage which was discussed in Chapter 5. Doing so is a good idea if your connection string is going to change.)

When you've created a connection object and set its connection string, you open the connection with its Open method, and assign the opened connection object to the Connection property of a command object. After specifying the SQL you want in the command object, you can use that command object with a data adapter. For example, if you assign the command object to a data adapter's SelectCommand property (named for the SQL SELECT statement), that command object will be used when you call the data adapter's Fill method to fill a dataset. When you're through with a connection, you can call its Close method to close the connection.

You can find the significant public properties of OleDbConnection objects in Table 10.1, their significant methods in Table 10.2, and their significant events in Table 10.3.

Table 10.1. Significant Public Properties of OleDbConnection Objects

PROPERTY	PURPOSE
`ConnectionString`	Returns or sets the connection string used to connect to a database.
`ConnectionTimeout`	The maximum time to try to make a connection, in seconds.
`Database`	The name of the database to open.
`DataSource`	The data source (typically the location and filename to open).
`Provider`	The OLE DB provider's name.
`ServerVersion`	The version of the data server.
`State`	The connection's current state.

Table 10.2. Significant Public Methods of OleDbConnection Objects

METHOD	PURPOSE
`Close`	Closes the connection to the data provider.
`CreateCommand`	Creates an `OleDbCommand` object for this connection.
`GetOleDbSchemaTable`	Returns the current schema table.
`Open`	Opens a database connection.

Table 10.3. Significant Public Events of OleDbConnection Objects

EVENT	MEANING
`InfoMessage`	Happens when the provider sends a message.
`StateChange`	Happens when a connection's state changes.

Working with the SqlConnection Class

As you can gather from their name, SqlConnection objects connect to SQL Server (you can also use OLE DB connections with the SQL Server, but SqlConnection objects are tuned for the best performance; SQL connections to the Microsoft SQL Server are up to 70% faster than OLE DB connections). Working with SqlConnection objects is much the same as working with OleDbConnection objects in code, although the connection string you use will be different. Here's what a sample connection string might look like for a SQL connection:

string connectionString = "workstation id=STEVE;packet size=4096;" + "integrated security=SSPI;initial catalog=pubs;" + "persist security info=False"; SqlConnection connection1 = new SqlConnection(connectionString);

Because the programming interface is so similar, the significant public properties, methods, and events of the SqlConnection class are nearly the same as for the OleDbConnection class, except that the SqlConnection class doesn't support the GetOleDbSchema method, but it does support the additional methods you see in Table 10.4.

Table 10.4. Additional Significant Public Properties of SqlConnection Objects

PROPERTY	PURPOSE
`PacketSize`	Returns the size of communication packets to use, in bytes.
`WorkstationId`	Returns the database client ID.

Working with the OdbcConnection Class

You use the OdbcConnection class to connect to Open Database Connectivity (ODBC) data providers. You can create ODBC connections to nearly all data providers, including MS Access. You create and manage ODBC data sources with the ODBC Data Source Administrator, which you open from the control panel. You can see the ODBC Data Source Administrator in Figure 10.1.

Figure 10.1. The ODBC Data Source Administrator.

In the ODBC Data Source Administrator, you give a data source a data source name, or DSN. The connection strings you use with OdbcConnection connection objects specify the DSN name. For example, if you create a DSN named books for an MS Access database named books.mdb , your connection string might look something like this:

string connectionString = "MaxBufferSize=2048;FIL=MSAccess;" + "DSN=books;PageTimeout=5;UID=admin;DBQ=C:\books.mdb;DriverId=25"; odbcConnection1 = new System.Data.Odbc.OdbcConnection(connectionString);

The significant public properties, methods, and events of the OdbcConnection class are the same as for the OleDbConnection class, except that it doesn't support the GetOleDbSchema method, and it also supports the additional property you see in Table 10.5.

Table 10.5. Additional Significant Public Properties of OdbcConnection Objects

PROPERTY	PURPOSE
`Driver`	Returns the ODBC driver name for the connection.

Working with the OracleConnection Class

You can connect to Oracle data sources using OleDbConnection objects or OracleConnection objects; OracleConnection objects are optimized for use with the Oracle data provider. Here's how you might create an OracleConnection object and assign a connection string to its ConnectionString property:

string connectionString = "Data Source=Oracle8i;Integrated Security=yes;" + "persist security info=False"; OracleConnection connection1 = new OracleConnection(connectionString);

As with other connection objects, the significant public properties, methods, and events of OracleConnection class are the same as for the OleDbConnection class, except that the OracleConnection class doesn't support the GetOleDbSchema method.

In code, you create a connection object, open that connection, and then use command objects, because command objects hold the actual SQL you'll use to extract data from a database. We'll take a look at command objects next. We'll also see how the entire process works in code in a few pages when we connect to a data provider and read data from it.

Using Command Objects

Command objects hold SQL. To use them, you create a connection object, and then assign that connection to a command object's Connection property. Then you store the SQL you want in the command object. Finally, you assign the command object to one of a data adapter object's four command properties: SelectCommand , UpdateCommand , InsertCommand , or DeleteCommand . The SelectCommand command object is used when the data adapter's Fill method is called, the UpdateCommand command object when the data adapter's Update method is called, and so on.

How do you store SQL in a command object? You assign SQL text to its CommandText property, or you can pass that SQL to the command object's constructor like this, where we're selecting all records in the pubs database's authors table:

OleDbCommand command1 = new OleDbCommand("SELECT * FROM authors");

You must also set the type of the command, which, for SQL statements, is CommandType.Text (this is also the default), and assign an open connection to the command's Connection property to make this Command object active. Here's an example:

OleDbCommand command1 = new OleDbCommand("SELECT * FROM authors"); command1.CommandType = CommandType.Text; connection1.Open(); command1.Connection = connection1;

Now if you assign this command object to a data adapter's SelectCommand property, its SQL will be executed when you call the data adapter's Fill method. Note that you can also use these command object methods to execute commands in a database, no data adapter needed:

ExecuteNonQuery executes SQL statements that do not return data rows (such as SQL INSERT , DELELE , UPDATE , and SET statements).

ExecuteReader executes SQL commands that return rows, creating a data reader. More on data readers in this chapter.

ExecuteScalar calculates and returns a single value, such as a sum over various records, from a database.

We'll take a look at the various command classes available next.

Working with the OleDbCommand Class

You use OleDbCommand objects to hold SQL statements executed in an OLE DB data provider. You can find the significant public properties of OleDbCommand objects in Table 10.6, and their significant methods in Table 10.7.

Table 10.6. Significant Public Properties of OleDbCommand Objects

PROPERTY	PURPOSE
`CommandText`	Returns or sets the SQL statement or stored procedure for this command to execute.
`CommandTimeout`	The amount of time to try a command, in seconds.
`CommandType`	Returns or sets the data type of the `CommandText` property (typically set to `CommandType.Text` , the default, for SQL).
`Connection`	Returns or sets the connection object to use.
`Parameters`	SQL command parameters.
`UpdatedRowSource`	Returns or sets how results are used in a data row when you use the `Update` method.

Table 10.7. Significant Public Methods of OleDbCommand Objects

METHOD	PURPOSE
`Cancel`	Cancels a command.
`CreateParameter`	Creates a new SQL parameter.
`ExecuteNonQuery`	Executes a non-row returning SQL statement, returning the number of affected rows.
`ExecuteReader`	Creates a data reader using the command.
`ExecuteScalar`	Executes the command and returns the value in the first column in the first row of the result.
`Prepare`	Creates a compiled version of the command.
`ResetCommandTimeout`	Resets the time-out value to the default.

Working with the SqlCommand Class

As you can guess, you use SqlCommand objects with SQL Server. These objects are nearly the same as OleDbCommand objects, except they're designed to be used with SQL connections. The significant public properties and methods of SqlCommand objects are the same as for OleDbCommand objects.

Working with the OdbcCommand Class

OdbcCommand objects are also similar to OleDbCommand objects, but you use them with ODBC connections. As with SqlCommand objects, the significant public properties, methods, and events of OdbcCommand objects are the same as OleDbCommand objects.

Working with the OracleCommand Class

In programming terms, OracleCommand objects are just like the other command objects, except, obviously, you use them with OracleConnection objects. The significant properties, methods, and events of the OracleCommand class are the same as for OleDbCommand objects, except there is no CommandTimeout property, and the OracleCommand class supports the additional methods you see in Table 10.8.

Table 10.8. Additional Significant Public Methods of OracleCommand Objects

METHOD	PURPOSE
`ExecuteOracleNonQuery`	Executes a SQL statement and returns the number of rows affected.
`ExecuteOracleScalar`	Executes the query and returns the first column of the first row in the result returned by the query as an Oracle-specific data type.

You use connection objects to connect to a data provider and assign them to command objects. Then you assign command objects to data adapters, coming up next.

Using Data Adapters in Code

When you create a connection object and use it in a command object, you can assign that command object to one of the command properties of the data adapter SelectCommand , InsertCommand , DeleteCommand , and UpdateCommand . These commands are used as needed by the data adapter; if you plan to retrieve data only from the data source, you only need to assign a command object to the SelectCommand . Data adapters are based on the DataAdapter class, and we'll start our survey of data adapters with this class.

Working with the DataAdapter Class

The DataAdapter class is the base class for data adapters, which represent a bridge between a dataset and a database in a data provider. You can find the significant public properties of the DataAdapter class in Table 10.9, and their significant methods in Table 10.10 (this class has no non-inherited events).

Table 10.9. Significant Public Properties of DataAdapter Objects

PROPERTY	PURPOSE
`AcceptChangesDuringFill`	Returns or sets whether a data row's `AcceptChanges` method is called when rows are added to a table.
`TableMappings`	Returns the mapping between source tables and data tables. Table mappings let you use different names for tables in a dataset than their original names in the data source.

Table 10.10. Significant Public Methods of DataAdapter Objects

METHOD	PURPOSE
`Fill`	Addsor updates rows in a dataset to match those in the data source. Creates a table named " `Table` " by default.
`FillSchema`	Adds a table named " `Table` " to the specified `DataSet` object, making the table's schema match that in the data source.
`GetFillParameters`	Returns the parameters to use when executing a `SELECT` statement in SQL.
`Update`	Updates the data source by calling the appropriate `INSERT` , `UPDATE` , or `DELETE` statements for each inserted, updated, or deleted row in a dataset.

The DataAdapter class, in turn , is the base class for the DbDataAdapter class, coming up next.

Working with the `DbDataAdapter` Class

The DbDataAdapter class serves as the base class for the OleDbDataAdapter and SqlDataAdapter classes. You can find the significant public methods of DbDataAdapter objects in Table 10.11, and their significant events in Table 10.12.

Table 10.11. Significant Public Methods of DbDataAdapter Objects

PROPERTY	PURPOSE
`Fill`	Adds or updates rows in a dataset to match those in the data source.
`GetFillParameters`	Returns the parameters to use when executing a SQL `SELECT` statement.
`Update`	Updates the data source by calling the `INSERT` , `UPDATE` , or `DELETE` statements for each inserted, updated, or deleted row in the dataset.

Table 10.12. Significant Public Event of DbDataAdapter Objects

EVENT	MEANING
`FillError`	Happens when an error happens while performing a fill operation.

That's it for the DataAdapter and DbDataAdapter classes; now we'll take a look at the data adapter classes you actually use in code, starting with the OleDbDataAdapter class.

Working with the OleDbDataAdapter Class

The OleDbDataAdapter class represents a bridge between a dataset and an OLE DB database. You can find the significant public properties of OleDbDataAdapter objects in Table 10.13, their significant methods in Table 10.14, and their significant events in Table 10.15.

Table 10.13. Significant Public Properties of OleDbDataAdapter Objects

PROPERTY	PURPOSE
`DeleteCommand`	Returns or sets the SQL for deleting records.
`InsertCommand`	Returns or sets the SQL for inserting new records.
`SelectCommand`	Returns or sets the SQL for selecting records.
`UpdateCommand`	Returns or sets the SQL for updating records.

Table 10.14. Significant Public Methods of OleDbDataAdapter Objects

METHOD	PURPOSE
`Fill`	Adds or refreshes rows to a dataset to make them match the rows in a data store.

Table 10.15. Significant Public Events of OleDbDataAdapter Objects

EVENT	MEANING
`RowUpdated`	Happens when a row is updated.
`RowUpdating`	Happens when a row is being updated.

Working with the SqlDataAdapter Class

The SqlDataAdapter class is the data adapter class targeted to SQL Server. Like the other data adapter classes, the SqlDataAdapter class includes the SelectCommand , InsertCommand , DeleteCommand , and UpdateCommand properties you use for loading and updating data. The significant properties, methods, and events of the SqlDataAdapter class are the same as for the OleDbDataAdapter class.

Working with the OdbcDataAdapter Class

You use the OdbcDataAdapter class with ODBC connections and command objects. The significant properties, methods, and events of the SqlDataAdapter class are the same as for the OdbcDataAdapter class.

Working with the OracleDataAdapter Class

You use the OracleDataAdapter class with the Oracle data provider. Like the other data adapters, the significant properties, methods, and events of the OracleDataAdapter class are the same as for the OdbcDataAdapter class.

After you create a data adapter, you need some place to put the data you read using that adapter. You'll typically use datasets for that.