ADO.NET is a group of libraries used to create powerful databases using various sources that include Microsoft Access, Microsoft Access, Oracle, XML, etc. ADO.NET relies on the .NET Framework's various classes to process requests and perform the transition between a database system and the user. The operations are typically handled through the DataSet class.
While ADO.NET is the concept of creating and managing database systems, the DataSet class serves as an intermediary between the database engine and the user interface.
Active X Data Objects(ADO) is simply a thin layer ,which hits on the top of OLE DB,and allows programs written in high level languages such as Visual Basic to access OLE DB Data.
The System.OleDb namespace provides objects that enable us to connect to OLEDB
providers. OLE-DB is an open specification for data providers that allow for
flexible access to many Microsoft and third-party data sources.This provides us
with one data access technology to connect to and manipulate data in several
database products, without having to change libraries.The System.Data.OleDb
namespace has been tested by Microsoft to work with Microsoft Access,
Microsoft SQL Server, and Oracle. In theory, any data provider that has an OLEDB
interface can be used in ADO.NET.
ODBC or, Open Database Connectivity, is part of the OLE-DB specification,
but Microsoft did not include it with the Beta 2 release.
Some common classes in the System.Data.SqlClient namespace are as follows:
•
OleDbConnection
•
OleDbCommand
•
OleDbDataAdapter
•
OleDbDataReader
Database Connection Class:
To support a connection to a database server, the .NET Framework provides the OleDbConnection class that is defined in the System.Data.OleDb namespace. Before using this class, you can first include this namespace in your file:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.OleDb;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
}
To connect to a database, you can first declare a variable of type OleDbConnection using one of its two constructors. Besides the default constructor, the second constructor takes as argument a string value. Its syntax is:
public OleDbConnection(string connectionString);
You can create the necessary (but appropriate) string in this constructor when declaring the variable. This would be done as follows:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.OleDb;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
OleDbConnection connection = new OleDbConnection("Something");
}
}
If you want, you can first create the string that would be used to handle the connection, then pass that string to this construction.
To support the connection as an object, the OleDbConnection class is equipped with the ConnectionString property. If you use the default constructor, you can first define a string value, then assign it to this property.
The Attributes of a Connection String
To use a OleDbConnection object, you must provide various pieces of information joined into a string but are separated from each other with a semi-colon ";". Each piece appears as a Key=Value:
Key1=Value1;Key2=Value2;Key_n=Value_n
It can be passed as follows:
OleDbConnection connection = new OleDbConnection("Key1=Value1;Key2=Value2;Key_n=Value_n");
or assigned as a string to the OleDbConnection.ConnectionString property:
string strConnection = "Key1=Value1;Key2=Value2;Key_n=Value_n";
OleDbConnection connection = new OleDbConnection();
connection.ConnectionString = strConnection;
The Database Provider
To use the database, you must indicate its source. To do this, add an attribute named Data Source and assign the file name to it. To help you locate the database file, the Server class of the IIS is equipped with a static method named MapMath. Pass the name of the database or the path to it to this method and assign the whole expression to the Data Source attribute. Here is an
Example:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.OleDb;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
OleDbConnection connection =
new OleDbConnection("Provider=Microsoft.Jet.OLE.4.0;" +
"Data Source=" + Server.MapPath("App_Data/exercise.mdb"));
}
}
Transaction class
A transaction is a group of operations combined into a logical unit of work that is either guaranteed to be executed as a whole or rolled back. Transactions help the database in satisfying all the ACID (Atomic, Consistent, Isolated, and Durable). Transaction processing is an indispensible part of ADO.NET. It guarantees that a block of statements will either be executed in its entirety or rolled back,( i.e., none of the statements will be executed). Transaction processing has improved a lot in ADO.NET 2.0. This article discusses how we can work with transactions in both ADO.NET 1.1 and 2.0.
Implementing Transactions in ADO.NET
In ADO.NET, the transactions are started by calling the BeginTransaction method of the connection class. This method returns an object of type SqlTransaction.
Other ADO.NET connection classes like OleDbConnection, OracleConnection also have similar methods. Once you are done executing the necessary statements within the transaction unit/block, make a call to the Commit method of the given SqlTransaction object, or you can roll back the transaction using the Rollback method, depending on your requirements (if any error occurs when the transaction unit/block was executed).
To work with transactions in ADO.NET, you require an open connection instance and a transaction instance. Then you need to invoke the necessary methods as stated later in this article. Transactions are supported in ADO.NET by the SqlTransaction class that belongs to the System.Data.SqlClient namespace.
The two main properties of this class are as follows:
•
Connection: This indicates the SqlConnection instance that the transaction instance is associated with
•
IsolationLevel: This specifies the IsolationLevel of the transaction
The following are the methods of this class that are noteworthy:
Commit() : This method is called to commit the transaction
Rollback() : This method can be invoked to roll back a transaction. Note that a transaction can only be rolled back after it has been committed.
Save() : This method creates a save point in the transaction. This save point can be used to rollback a portion of the transaction at a later point in time. The following are the steps to implement transaction processing in ADO.NET.
•
Connect to the database
•
Create a SqlCommand instance with the necessary parameters
•
Open the database connection using the connection instance
•
Call the BeginTransaction method of the Connection object to mark the beginning of the transaction
•
Execute the sql statements using the command instance
•
Call the Commit method of the Transaction object to complete the
transaction, or the Rollback method to cancel or abort the transaction
•
Close the connection to the database
Data Set Class
Datasets store a copy of data from the database tables. However, Datasets can not directly retrieve data from Databases. DataAdapters are used to link Databases with DataSets. If we see diagrammatically,
DataSets < ----- DataAdapters < ----- DataProviders < ----- Databases
DataSets and DataAdapters are used to display and manipulate data from databases.
Reading Data into a Dataset
To read data into Dataset, you need to:
•
Create a database connection and then a dataset object.
•
Create a DataAdapter object and refer it to the DB connection already created. Note that every DataAdapter has to refer to a connection object. For example, SqlDataAdapter refers to SqlDataConnection.
•
The Fill method of DataAdapter has to be called to populate the Dataset object.
We elaborate the above mentioned steps by giving examples of how each step can be performed:
1) As we said, our first task is to create a connection to database. We would explore later that there is no need of opening and closing database connection explicitly while you deal with DataAdapter objects. All you have to do is, create a connection to database using the code like this:
SqlConnection con = new SqlConnection ("data source=localhost; uid= sa; pwd= abc; database=Northwind");
We would use Northwind database by using OleDbConnection. The Code would
Look like:
OleDbConnection con= new OleDbConnection ("Provider =Microsoft.JET.OLEDB.4.0;" + "Data Source=C:\\Program Files\\Microsoft Office\\Office\\Samples\\Northwind.mdb");
2) Now, create a Dataset object which would be used for storing and manipulating data. You would be writing something like
DataSet myDataSet = new DataSet ("Northwind");
Since the name of source database is Northwind, we have passed the same name in the constructor.
3) The DataSet has been created but as we said before, this DataSet object can not directly interact with Database. We need to create a DataAdapter object which would refer to the connection already created.
Data Adapter Class
OleDbAdapter myDataAdapter = new OleDbAdapter (CommandObject, con);
The above line demonstrates one of many constructors of OleDbAdapter class. This constructor takes a command object and a database connection object. The purpose of command object is to retrieve suitable data needed for populating DataSet. As we know SQL commands directly interacting with database tables, a similar command can be assigned to CommandObject.
OleDbCommand CommandObject = new OleDbCommand ("Select * from employee");
Whatever data you need for your Dataset should be retrieved by using suitable command here. The second argument of OleDbAdapter constructor is connection object con.
Alternative approach for initializing DataAdapter object:
Place a null instead of CommandObject while you initialize the OleDbAdapter object:
OleDbAdapter myDataAdapter = new OleDbAdapter (null, con);
Then you assign your query to the CommandObject and write:
myDataAdapter.SelectCommand = CommandObject;
4) Now, the bridge between the DataSet and Database has been created. You can populate dataset by using the Fill command:
myDataAdapter.Fill (myDataSet, "EmployeeData");
The first argument to Fill function is the DataSet name which we want to populate. The second argument is the name of DataTable. The results of SQL queries go into DataTable. In this example, we have created a DataTable named EmployeeData and the values in this table would be the results of SQL query: "Select * from employee". In this way, we can use a dataset for storing data from many database tables.
5) DataTables within a Dataset can be accessed using Tables. To access EmployeeData, we need to write:
myDataSet.Tables["EmployeeData"].
To access rows in each Data Table, you need to write:
myDataSet.Tables["EmployeeData].Rows
1. <%@ Page Language= "C#" %>
2. <%@ Import Namespace= "System.Data" %>
3. <%@ Import Namespace= "System.Data.OleDb" %>
4. <html>
5. <body>
6.
7. <table border=2>
8. <tr>
9. <td><b> Employee ID </b></td>
10. <td><b> Employee Name </b></td>
11. </tr>
12.
13. <% OleDbConnection con= new OleDbConnection ("Provider
14. =Microsoft.JET.OLEDB.4.0;" + "Data Source=C:\\Program Files\\Microsoft
15. Office\\Office\\Samples\\Northwind.mdb");
16.
17. <%
18. DataSet myDataSet = new DataSet();
19. OleDbCommand CommandObject = new OleDbCommand ("Select * from
20. employee");
21.
22. OleDbAdapter myDataAdapter = new OleDbAdapter (CommandObject, con);
23.
24. myDataAdapter.Fill (myDataSet, "EmployeeData");
25.
26. foreach (DataRow dr in myDataSet.Tables["EmployeeData"].Rows)
27. {
28. Response.write ("<tr>");
29. for (int j = 0 ; j <2 ; j++)
30. {
31. Response.write ( "<td>" + dr[j].ToString() + "</td"> );
32. }
33. Response.write ("</tr>");
34.
35. %>
36. </table>
37. </body>
38. </html>
The Code above would iterate in all rows of Employee table and display ID and name of every employee. To Display all columns of Employee Table, Line # 29 would be replaced by:
for (int j = 0 ; j < dr.Table.Columns.Count ; j++)
