Overview of ADO.NET

Introduction:

In this article you can able to understand the basic concept of ADO.NET technologies and objects that we are using to manipulate with data source.

ActiveX Data Object (ADO.NET) is the new evolution of ADO technology. In ADO.NET, new objects are introduced like DataSet, DataReader, DataAdapter. In ADO, you need to connect to the database as long as you manipulate the data and then you will close the connection to the database, this will create problem when number of user increases and performance of the database will decrease. To overcome this issues ADO.NET technology is introduced in .NET, which is a disconnected data access model. Once the database is connected and fetches the information and disconnect it. We can able to manipulate the data using many objects provided in ADO.NET. The ADO.NET is designed for the more usage of XML's also. The ADO.NET mainly contains three entities. DataSource, Dataset, DataAdapter.

DataSource - DataSource is the provider of the data, which is database. Using ADO.NET you can able to connect to different data base like MS SQL Server, MYSQL server, Excel etc., MS SQL server will have a separate class library for manipulation of data so the access will be more faster. Where as other database are connected using ODBC and OLEDB class libraries. This class library has a lot of collection of members, using these members you will be able to access the database for various purposes like inserting, retrieving, deleting and more operations can be performed.

DataSet - In simple way we can call DataSet as a mini database. DataSet resides in the memory with cached data, all the manipulation can be done with in the DataSet, Connection to the database or data source can be disconnected after the retrieval of information. DataSet is the collection of data tables which can be interlinked or relate to each other using DataRelation object. You can set the constraints for these DataTable like Unique Key, PrimaryKey. You can retrieve the date from various DataSource like MS SQL Server, XML files etc and fill it in the DataSet. DataSet will have the collection of DataTable's and each DataTable has the number of column in it, where constraint will be defined for each column if necessary. Each row in the DataTable is called as DataRow. So we can say collection of DataRow will represents as DataTable.

DataAdapter - DataAdapter is the intermediater between DataSet and DataSource. Using DataAdapter we can able to fetch the information from DataSource and load it in DataSet. DataAdapter is a separate object in ADO.NET technologies. It has Fill() method that will help to fill the data from DataSource to DataSet. DataAdapter holds four very important properties.

SelectCommand - This property will execute the select query or command text and retrieve the result set in the form of table. Using Fill method you will be able to fill the data in DataSet or DataTable.

InsertCommand - Execute the insert query or command text and insert a row in the table.

DeleteCommand - Execute the insert query or command text and delete single row or multiple rows.

UpdateCommand - Execute the insert query or command text and update single row or multiple rows.

Now we will see few objects in ADO.NET so that it will be more useful while accessing the data from the DataSource

Connection Object - Connection object will help to create a connection between your application and the database. The connect object takes a Connection string as a parameter. You can see the example of connection string below.

server=local; DataBase=MyDataBase; UID=xxxxxxx; PWD=xxxxxxx;

Now we can see how to use this connection string and create a connection object. In this example we are using MS SQL as the database. System.Data.SqlClient makes sure this namespace is imported in the form.

Command Object - Command object will help to execute the command text or store procedures. For executing command object we need to pass the command text and connection string as the parameter. There is few overload method in this command object. The below example will show how to use command object.

Command object has four methods. Before executing the four methods we need to open the database connection and close it after execution.

• ExecuteReader, the command object will execute the query and the returned row is loaded in to the DataReader object. The below example shows how to use the ExecuteReader() method.
  
  Dim connString As String = "server=local; DataBase=MyDataBase; UID=xxxxxxx; PWD=xxxxxxx;"
  Dim myConn As New SqlConnection(connString)
  Dim strQuery As String = "select * from TestTable"
  Dim myCommand As New SqlCommand(strQuery, myConn)
  myConn.Open()
  Dim myReader As SqlDataReader = myCommand.ExecuteReader()
  myConn.Close()
  
  
• ExecuteNonQuery, this method will simply execute the command text or query. Here you can do Insert, Update, Delete in the database. The Example show how to use ExecuteNonQuery() method.
  
  Dim connString As String = "server=local; DataBase=MyDataBase; UID=xxxxxxx; PWD=xxxxxxx;"
  Dim myConn As New SqlConnection(connString)
  Dim strQuery As String = "select * from TestTable"
  Dim myCommand As New SqlCommand(strQuery, myConn)
  myConn.Open()
  myCommand.ExecuteNonQuery()
  myConn.Close()
  
  
• ExecuteScalar this will execute the command text or query and return single value from the database. The Example show how to use ExecuteScalar () method
  
  Dim connString As String = "server=local; DataBase=MyDataBase; UID=xxxxxxx; PWD=xxxxxxx;"
  Dim myConn As New SqlConnection(connString)
  Dim strQuery As String = "select * from TestTable"
  Dim myCommand As New SqlCommand(strQuery, myConn)
  myConn.Open()
  Dim strMyValue As String
  strMyValue = Convert.ToString(myCommand.ExecuteScalar())
  myConn.Close()
  
  
• ExecuteXmlReader, this is just like the ExecuteReader, the only difference is the result will be in the form of XmlReader
  
  Dim connString As String = "server=local; DataBase=MyDataBase; UID=xxxxxxx; PWD=xxxxxxx;"
  Dim myConn As New SqlConnection(connString)
  Dim strQuery As String = "select * from TestTable"
  Dim myCommand As New SqlCommand(strQuery, myConn)
  myConn.Open()
  Dim xmlReader As Xml.XmlReader = myCommand.ExecuteXmlReader()
  myConn.Close()

Summary:

These are the very basic concept of accessing the data sources.