Wednesday, 18 July 2012

ADO.Net in Detail

What is ADO.NET?

ADO.NET is the new database technology of the .NET (Dot Net) platform, and it builds on Microsoft ActiveX® Data Objects (ADO).
ADO is a language-neutral object model that is the keystone of Microsoft's Universal Data Access strategy.
ADO.NET is an integral part of the .NET Compact Framework, providing access to relational data, XML documents, and application data. ADO.NET supports a variety of development needs. You can create database-client applications and middle-tier business objects used by applications, tools, languages or Internet browsers.
ADO.NET defines DataSet and DataTable objects which are optimized for moving disconnected sets of data across intranets and Internets, including through firewalls. It also includes the traditional Connection and Commandobjects, as well as an object called a DataReader that resembles a forward-only, read-only ADO recordset. If you create a new application, your application requires some form of data access most of the time.
ADO.NET provides data access services in the Microsoft .NET platform.
You can use ADO.NET to access data by using the new .NET Framework data providers which are:
  • Data Provider for SQL Server (System.Data.SqlClient).
  • Data Provider for OLEDB (System.Data.OleDb).
  • Data Provider for ODBC (System.Data.Odbc).
  • Data Provider for Oracle (System.Data.OracleClient).
ADO.NET is a set of classes that expose data access services to the .NET developer. The ADO.NET classes are found in System.Data.dll and are integrated with the XML classes in System.Xml.dll.
There are two central components of ADO.NET classes: the DataSet, and the .NET Framework Data Provider.
Data Provider is a set of components including:
  • the Connection object (SqlConnectionOleDbConnectionOdbcConnectionOracleConnection)
  • the Command object (SqlCommandOleDbCommandOdbcCommandOracleCommand)
  • the DataReader object (SqlDataReaderOleDbDataReaderOdbcDataReaderOracleDataReader)
  • and the DataAdapter object (SqlDataAdapterOleDbDataAdapterOdbcDataAdapter,OracleDataAdapter).
DataSet object represents a disconnected cache of data which is made up of DataTables and DataRelations that represent the result of the command.

The ADO.NET Object Model

2. Connection to an ADO.NET database

Before working with a database, you have to add (here) the OleDb .NET Data Provider namespace, by placing the following at the start of your code module:
using System.Data.OleDb;
Similarly for the SqlClient .NET Data Provider namespace:
using System.Data.SqlClient;
The using statement should be positioned first in your code.
Now, we have to declare a connection string pointing to a MS Access database "PersonDatabase.mdb".
public string 
  conString=@"Provider=Microsoft.Jet.OLEDB.4.0;" + 
            @" DataSource=..\\..\\PersonDatabase.mdb";
The database should be in the specified path, otherwise you should change the path accordingly.
The next step is to create an OleDbConnection object. We pass then the connection string to thisOleDbConnection object. You can code now to create a new ADO.NET Connection object in order to connect to an OLE DB provider database.
OleDbConnection con = new OleDbConnection(conString);
You can also explicitly reference declared objects if you don’t mind typing a lot.
System.Data.OleDb.OleDbConnection con = 
  new System.Data.OleDb.OleDbConnection(conString);
Here is the code snippet for connection to a database:
//using declaration for OLE DB
using System.Data.OleDb; 
//specify the ConnectionString property
public string conString= 
  @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=..\\..\\PersonDatabase.mdb";
//Initializes a new instance of the OleDbConnection
OleDbConnection con = new OleDbConnection(conString);
// open the database connection with the property settings
// specified by the ConnectionString "conString"
In many earlier applications, the tendency was to open a connection when you start the application and not close the connection until the application terminates. It is an expensive and time-consuming operation to open and close a database connection. Most databases have a limit on the number of concurrent connections that they allow.
For example: each connection consumes a certain amount of resources on the database server and these resources are not infinite. Most modern OLE DB providers (including SQL Server provider) implement connection pooling. If you create database connections, they are held in a pool. When you want a connection for an application, the OLE DB provider extracts the next available connection from the pool. When your application closes the connection, it returns to the pool and makes itself available for the next application that wants a connection.
This means that opening and closing a database connection is no longer an expensive operation. If you close a connection, it does not mean you disconnect from the database. It just returns the connection to the pool. If you open a connection, it means it's simply a matter of obtaining an already open connection from the pool. It's recommended in many ADO.NET books not to keep the connections longer than you need to. Therefore, you should:
  • Open a connection when you need it, and
  • Close it as soon as you have finished with it.
For example: here is another way to get a connection to a database:
// setup the global SqlConnection object and constr in your class
  private SqlConnection con = null;
  private string constr ="Integrated Security=SSPI;" +
  "Initial Catalog=Northwind;" +
  "Data Source=SONY\\MYSQLSERVER;";
  private void fnGetConnection()
       // setup the database connection
       con = new SqlConnection(constr);
     }catch (Exception ex) {
        MessageBox.Show("Error in connection : "+ex.Message);
     }finally {
        // dispose of open objects
        if (con != null)
     } //finally
For example: you want to open the connection, fill the DataSet, and close the connection. If the connection fails, you want to get the error message.
} catch (Exception ex) {
   MessageBox.Show("Error in retrieving data: " + ex.Message);
For example: if you want to save the data you changed, then you just open the connection, update the data, and close the connection and accept the changes. If it fails, display an error message, reject the changes, and close the connection.
  DataSet changes = dataset.GetChanges();
}catch (Exception ex) {
  MessageBox.Show("ErrorR: " + ex.Message);


No comments:

Post a Comment