Each of these objects come in four types, each in their own namespaces, depending on the data provider you're using:
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:
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
Table 10.2. Significant Public Methods of OleDbConnection Objects
Table 10.3. Significant Public Events of OleDbConnection Objects
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
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
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:
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
Table 10.7. Significant Public Methods of OleDbCommand Objects
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
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
Table 10.10. Significant Public Methods of DataAdapter Objects
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
Table 10.12. Significant Public Event of DbDataAdapter Objects
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
Table 10.14. Significant Public Methods of OleDbDataAdapter Objects
Table 10.15. Significant Public Events of OleDbDataAdapter Objects
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.