A challenge related to data-centric applications is that multiple users need to access data simultaneously from the data source. As the number of users accessing an application increases the network becomes slow and might lead to network congestion. Therefore, some of the factors that decide the type of data access environment used for an application include the number of users who intend to use the application and the functionality of the application.
The ADO.NET environment can be categorized into connected and disconnected environments. A connected environment requires a constant connection to transfer data between the client application and the data source. However, a disconnected environment retrieves data and performs modification without a constant connection to the network. Here I am not discussing the advantages and limitations of these.
The following figure shows the classes related to connected and disconnected environments in ADO.NET:
![ado.gif]()
As can be seen from the preceding diagram, the ADO.NET class hierarchy can be categorized as disconnected and connected classes. Connected classes consist of DataAdapters, DataReaders, Connection and Command objects. Disconnected classes consist of DataSets and DataTables.
Working in a Connected Environment
In a connected environment, an application is continuously connected to a data source. In this, a DataReader object is used. A DataReader object, which is a component of a data provider, uses the Connection object to connect to the database. It then uses the Command object to retrieve data, and provides data to the application in a read-only and forward-only mode.
Working with Command Objects
A Command object is specific command that is used to manipulate data in a data source. It is an object representing a DML statement or a stored procedure that is used to insert, delete, or modify data in a data source. A data command is an instance of a SqlCommand class.
To retrieve data by using data commands, first a Connection object is created to connect to the database from where data is to be retrieved. Then, a Command object is created. To access a data source, a data command should provide information about the connection for communication with the data source.
The two types of operations performed by a command object to retrieve and modify data in a data source are :
- Synchronous operations
- Asynchronous operations
Performing Synchronous Operation Using Command Objects
During synchronous operation the command objects are linked to each other. Executing command objects synchronously result in a sequential execution, where each database command must complete before the next command is executed. For example, when one command object is deleted, the command object linked with the first command object is also deleted.
Synchronous operations are performed using the following command objects:
- DbCommand object
- DbParameters object
- DbDataReader object
DbCommand Object
The DbCommand object is used to execute a command in a data source. The DbCommand object can be a Data Manipulation Language (DML) command, which is used to retrieve, insert, update or delete data in the database.
To create a DbCommand object, you need to create a DbConnection object that will generate a valid open connection to the data source.
To execute the command, the CreateCommand () Method is called. The CreateCommand () method creates and returns a DbCommand object associated with the DbConnection object.
The CreateCommand () method allows the DbConnection object to automatically create the appropriate provider-specific DbCommand object. In addition the CommandText and Commandtype Properties of the DbCommand object are also specified.
Properties of DbCommand object
- CommandText
- CommandTimeout
- CommandType
- Connection
- Container
- DesignTimeVisible
- Parameters
- Site
- Transaction
- UpdatedRowSource
Methods of DbCommand object
- Cancel ()
- CreateObjRef ()
- CreateParameter ()
- Dispose ()
- Equals ()
- ExecuteNonQuery ()
- ExecuteReader ()
- GetHashCode ()
- GetLifeTimeService ()
- GetType ()
- InitializeLifeTiimeService ()
- Prepare ()
- ReferenceEquals ()
- ToString ()
Consider an example, where a Windows form needs to be created to display the product record data having a record value equal to 750. This data will be fetched, by sending a SQL command to the database with the help of the DbCommand object. The code for creating DbCommand object is as follows:
//Createing a connection to the AdventureWorks database
string connectionString = "Data Source=.\\Sqlexpress;Initial Catalog=AdventureWorks;Integrated Security=True";
SqlConnection cn = new SqlConnection();
cn.ConnectionString = connectionString;
cn.Open();
//Call the CreateCommand method
SqlCommand cmd = cn.CreateCommand();
cmd.CommandType = CommandType.Text;
//Execute teh SQL Query
cmd.CommandText = "Select * from Production.Product Where RecordPoint=750";
//Execute the SQL Query Against the connecion object
cmd.ExecuteNonQuery();
In the preceding code, the SqlCommand executes the SQL query. The CommandText property of the SqlCommand contains the SQL query. The CommandType property of the SqlCommand calls the SQL query.
You can find the full source code of this series of articles in the last article.