The concept behind connecting to a database has always been confusing for novice application developers. This post focuses on the different methods available for connecting to databases in C#.
The software support provided by a database vendor to access its own product is known as a ‘Native driver’. This provides the simplest method of connectivity as the data access takes place without any intervention by other software layers. But, as the database changes, the data access mechanism also needs to be changed accordingly in this approach.
So as to standardize data access, Microsoft released a set of universal access drivers called OleDb, which stands for Object Linking and Embedding DataBase. It is said to support compound technology, which means that it can either connect to a database using Native drivers or seek the help of other Data Access drivers like ODBC (Open DataBase Connectivity) to access the data store.
Later on, a set of APIs were made to communicate effectively with data in the data store. The common APIs available were
- DAO – Data Access Object
- RDO – Remote Data Object
- ADO – ActiveX Data Object
- ADO.net – ActiveX Data Object for .net
The two distinct type of data access models defined are:
- Connected Approach ( Connection Oriented)
- ii) Disconnected Approach
- In Connected approach, whenever a query is run, the results are stored in an ActiveX DataSet in the server. A pointer to this ActiveX dataset is returned to the client. This pointer is known as Cursor. Therefore, to access the results, a connection must be consistently open between the server and the client.
In the Disconnected approach, the results of a query and sent back to the client, and stored in a client side cursor. This in turn means that continuous connectivity need not exist between the server and the client.
In .net, connected approach is accomplished using the following objects.
- Connection
- Command
- DataReader
The Connection is used to send a Command across to the server, which is executed and the result is stored in the server side cursor, which in turn is read using the DataReader.
Disconnected approach makes use of the following objects
- Connection
- Command
- DataAdapter
- DataSet
A DataAdapter acts as a bridge between a data source and a data class which is disconnected from the data source. It can provide a set of methods to carry out operations like Select, Insert, Update and Delete. A DataSet is the equivalent for client side cursor, which holds a portion of the database after execution of a query. It can be defined as a collection of DataRows and DataColumns. More popularly known as an in-memory database.
There are four methods of executing a query in C#.
- ExecuteReader() – which will return the address of the cursor.
- ExecuteNonQuery() – which will return the number of rows affected.
- ExecuteScalar() – which will return the first [row][column] of the result.
- ExecuteXmlReader() – which will return the instance of an Xml Parser.
We shall look into a few examples in the next post. Till then, have a nice time and happy programming.
