VistaDB 6
VistaDB / Developer's Guide / ADO.NET Overview / Connected ADO.NET Objects
In This Topic
    Connected ADO.NET Objects
    In This Topic
    Connected classes in ADO.NET are designed to communicate directly with the data source. Most of these classes map closely to basic data access concepts such as the Connection to the database, a Query, and QueryResults.

    ProviderFactory Class

    New in ADO.NET 2 this is an object factory that allows all .Net applications to generically load a provider without knowing much about it. Each Provider Factory includes a way to create Connections, ConnectionStringBuilders, etc.

    It is this ProviderFactory object that allows VistaDB to be swapped with SQL Server at runtime through your app.config or web.config files. It is not a perfect match for communicating directly against the strongly typed classes, but it is very close.

    Connection Class

    A Connection is a representation of the actual physical connection to the database. This may be through a server, or locally. You use this class to connect and disconnect from the actual database.

    A connection also acts as the handle to the database for other objects like the DataAdapter and Command objects. They do not communicate directly to the database, they must go through a Connection.

    ConnectionStringBuilder Class

    This is another new class in ADO.NET 2 that simplified the process of building connection strings and remembering the options for a specific provider, it is a great utility class to save you time. Once you have built up this object you can assign the Connection class ConnectionString property to this object.


    The DataAdapter is a new concept, there are no matching concepts in ADO or DAO. The DataAdapter is the bridge between your database and the disconnected ADO.NET objects. The Fill method provides an efficient mechanism to fetch the results of a query into DataSet or DataTable so you can work with the data offline (disconnected). You also use the DataAdapter to submit pending changes from the disconnected objects back to the database.

    This is a complex class with lots of internal jobs to track. We will cover it in more detail in another blog post.

    Command Class

    This class represents a question or query against a database, a call to a stored procedure, or a direct request to return the contents of a specific table. This is probably the hardest class to port your usage between database vendors as most of the calls at this level are vendor specific syntax.

    Using a Command object with a database is pretty easy. You set the Connection property to an existing opened Connection object, and then specify the command you want to execute in the CommandText. You can supply SQL commands, or just the name of a stored procedure or table (although you have to then also change the CommandType property to what you wish to accomplish).

    Commands have many ways to execute them, but all of them basically call ExecuteQuery under the hood. If you don't care about the results, or only want the first row and column there are short cuts you can take (ExecuteNonQuery and ExecuteScalar).

    Note that VistaDB does not support the ExecuteXML syntax at this time.

    Parameter Class

    To simplify the process of putting parameters into a Command object the Parameter class allows you to quickly put parameters into a query without string concatenation or worry about SQL Injection attacks. Most developers rely heavily on parameterized queries because of the added benefit of SQL Injection protection; strings are automatically quoted to the specs of the underlying database.

    There are many ways to create Parameters for Command objects, but they are mostly just different ways of doing the same thing. Some people prefer one syntax over another, but they all end up as Parameter objects on a Command object at execution in the database.

    DataReader Class

    The DataReader is built as a way to retrieve and examine the rows returned in response to your query as quickly as possible. Not all of the rows have to be ready before you can start working with the data. Only a single row is exposed at a time, so the database engine can get you the first record quickly and then continue to work in the background to find the rest of the answers over time.

    The data returned by a DataReader is always read only. This class was built to be a lightweight forward only, read only, way to run through data quickly (this was called a firehose cursor in ADO).

    Transaction Class

    Transactions are used to ensure that multiple changes to database rows occur as a single unit of work. The Connection class has a BeginTransaction method that can be used to create a Transaction.

    A definite best practice is to ensure that Transactions are placed in Using statements for rapid cleanup if they are not committed. Otherwise the objects (and any internal locks that may be needed) will remain active until the GC gets around to cleaning it up.

    See Also