ADO.NET

ADO.NET Stands On ActiveX Data Object.

               Pretty much every application deals with data in some manner, whether that data comes from memory, databases, XML files, text files, or something else. The location where we store the data can be called as a Data Source or Data Store where a Data Source can be a file, database, address books or indexing server etc.

Programming Languages cannot communicate with Data Sources directly because each Data Source adopts a different Protocol (set of rules) for communication, so to overcome this problem long back Microsoft has introduced intermediate technologies like Odbc and Oledb which works like bridge between the Applications and Data Sources to communicate with each other.
                             
ODBC (Open Database Connectivity) is a standard C programming language middleware API for accessing database management systems (DBMS). ODBC accomplishes DBMS independence by using an ODBC driver as a translation layer between the application and the DBMS. The application uses ODBC functions through an ODBC driver manager with which it is linked, and the driver passes the query to the DBMS. An ODBC driver will be providing a standard set of functions for the application to use, and implementing DBMS-specific functionality. An application that can use ODBC is referred to as "ODBC-Compliant". Any ODBC-Compliant application can access any DBMS for which a driver is installed. Drivers exist for all major DBMS’s as well as for many other data sources like Microsoft Excel, and even for Text or CSV files. ODBC was originally developed by Microsoft during the early 90s.
              
OLE DB (Object Linking and Embedding, Database, sometimes written as OLEDB or OLE-DB), an API designed by Microsoft, allows accessing data from a variety of data sources in a uniform manner. The API provides a set of interfaces implemented using the Component Object Model (COM) and SQL. Microsoft originally intended OLE DB as a higher-level replacement for, and successor to, ODBC, extending its feature set to support a wider variety of non-relational databases, such as object databases and spreadsheets that do not necessarily implement SQL. OLE DB is conceptually divided into consumers and providers. The consumers are the applications that need access to the data, and the providers are the software components that implement the interface and thereby provide the data to the consumer. An OLE DB provider is a software component enabling an OLE DB consumer to interact with a data source. OLE DB providers are alike to ODBC drivers. OLE DB providers can be created to access such simple data stores as a text file and spreadsheet, through to such complex databases as Oracle, Microsoft SQL Server, and many others. It can also provide access to hierarchical data stores.

RDO’s and ADO’s in Visual Basic Language:

               Visual Basic Language used RDO’s and ADO’s for data source communication without having to deal with the comparatively complex ODBC or OLEDB API.

.NET Framework Providers:


The .NET Framework Data Provider for SQL Server uses its own protocol to communicate with SQL Server. It is lightweight and performs well because it is optimized to access a SQL Server directly without adding an OLE DB or Open Database Connectivity (ODBC) layer. The .NET Framework Data Provider for Oracle (OracleClient) enables data access to Oracle data sources through Oracle client connectivity software. The data provider supports Oracle client software version 8.1.7 or a later.

ADO.NET:-

It is a set of types that expose data access services to the .NET programmer. ADO.NET provides functionality to developers writing managed code similar to the functionality provided to native COM developers by ADO. ADO.NET provides consistent access to data sources such as Microsoft SQL Server, as well as data sources exposed through OLE DB and XML. Data-sharing consumer applications can use ADO.NET to connect to these data sources and retrieve, manipulate, and update data. It is an integral part of the .NET Framework, providing access to relational data, XML, and application data. ADO.NET supports a variety of development needs, including the creation of front-end database clients and middle-tier business objects used by applications or Internet browsers.

ADO.Net provides libraries for Data Source communication under the following namespaces:
·        System.Data
·        System.Data.Odbc
·        System.Data.Oledb
·        System.Data.SqlClient
·        System.Data.OracleClient

Note: System.Data, System.Data.Odbc, System.Data.Oledb and System.Data.SqlClient namespaces are under the assembly System.Data.dll whereas System.Data.OracleClient is under System.Data.OracleClient.dll assembly.

System.Data: types of this namespace are used for holding and managing of data on client machines. This namespace contains following set of classes in it:  DataSet, DataTable, DataRow, DataColumn, DataView, DataRelation etc.
System.Data.Odbc: types of this namespace can communicate with any Relational Data Source using Un-Managed Odbc Drivers.
System.Data.Oledb: types of this namespace can communicate with any Data Source using Oledb Providers (Un-Managed COM Providers).
System.Data.SqlClient: types of this namespace can purely communicate with Sql Server database only using SqlClient Provider (Managed .Net Framework Provider).
System.Data.OracleClient: types of this namespace can purely communicate with Oracle database only using OracleClient Provider (Managed .Net Framework Provider).

All the above 4 namespaces contains same set of types as following: Connection, Command, DataReader, DataAdapter, Parameter and CommandBuilder etc, but here each class is referred by prefixing with their namespace before the class name to discriminate between each other as following:

OdbcConnection
OdbcCommand
OdbcDataReader
OdbcDataAdapter
OdbcCommandBuilder
OdbcParameter
OledbConnection
OledbCommand
OledbDataReader
OledbDataAdapter
OledbCommandBuilder
OledbParameter
SqlConnection
SqlCommand
SqlDataReader
SqlDataAdapter
SqlCommandBuilder
SqlParameter
OracleConnection
OracleCommand
OracleDataReader
OracleDataAdapter
OracleCommandBuilder
OracleParameter

Performing Operations on a DataSource: Each and every operation we perform on a Data Source involves in 3 steps, like:
  • Establishing a connection with the data source.
  • Sending a request to the data source in the form of an SQL Statement.
  • Capturing the results given by the data source.

Establishing a Connection with Data Source:
It's a process of opening a channel for communication between Application and Data Source that is present on a local or remote machine to perform any operations. To open the channel for communication we use the Connection class.

Constructors of the Class:                             
Connection()                                      Connection(string ConnectionString)
Note: ConnectionString is a collection of attributes that are required for connecting with a DataSource, those are:
  • DSN
  • Provider
  • Data Source
  • User Id and Password
  • Integrated Security
  • Database or Initial Catalog

DSN: 
              This is the only attribute that is required if we want to connect with a data source by using Odbc Drivers and by using this we need to specify the DSN Name.
Provider: this attribute is required when we want to connect to the data source by using Oledb Providers. So by using this attribute we need to specify the provider name based on the data source we want to connect with.
               Oracle:                                 Msdaora                                             Sql Server:                           SqlOledb
MS-Access or MS-Excel:    Microsoft.Jet.Oledb.4.0                    MS-Indexing Server:          Msidxs
Data Source: this attribute is required to specify the server name if the data source is a database or else if the data source is a file we need to specify path of the file, in case of provider communication only.
User Id and Password: This attribute is required to specify the credentials for connection with a database, in case of provider communication only.
               Oracle: Scott/tiger                                                                         Sql Server: Sa/<pwd>
Integrated Security: this attribute is used while connecting with Sql Server Database only to specify that we want to connect with the Server using Windows Authentication and in this case we should not again use User Id and Password attributes, in case of provider communication only.
Database or Initial Catalog: these attributes are used while connecting with Sql Server Database to specify the name of database we want to connect with, in case of provider communication only.

Connection String for SqlServer to connect by using different options:

OdbcConnection con = new OdbcConnection("Dsn=<Dsn Name>");
OledbConnection con = new OledbConnection("Provider=SqlOledb;Data Source=<Server Name>;
Database=<DB Name>;User Id=<UID>;Password=<PWD>");
SqlConnection con = new SqlConnection("Data Source=<Server Name>;Database=<DB Name>;User Id=<UID>;
               Password=<PWD>");
Note: in case of Windows Authentication in place of User Id and Password attributes we need to use Integrated Security = SSPI (Security Support Provider Interface).
Connection String for Oracle to connect by using different options:
OdbcConnection con = new OdbcConnection("Dsn=<Dsn Name>");
OledbConnection con = new OledbConnection("Provider=Msdaora;Data Source=<Server Name>;
               User Id=<UID>;Password=<PWD>");
OracleConnection con = new OracleConnection("Data Source=<Server Name>;User Id=<UID>;Password=<PWD>");
Members of Connection class:
  1. Open(): a method which opens a connection with data source.
  2. Close(): a method which closes the connection that is open.
  3. State: an enumerated property which is used to get the status of connection.
  4. ConnectionString: a property which is used to get or set a connection string that is associated with the connection object.

The Object of class Connection can be created in any of the following ways:
               Connnection con = new Connection(); con.ConnectionString = "<connection string>";
                                                            or
Connection con = new Connection("<connection string>");
Testing the process of establishing a connection: open a new project of type Windows Forms Application, name it as DBOperations and design the form as following:

Creating DSN for working with ODBC Drivers:
To work with Odbc Drivers first we need to configure the drivers installed on our machine with corresponding Databases by creating a DSN (Data Source Name) and to do that go to Control Panel => Administrative Tools => double click on Odbc DataSources to open ODBC Data Source Administrator window, click on Add button, select a driver for Sql Server and click Finish button, which opens a window, in that enter the following details, Name: SqlDsn, Description: Connects with Sql Server Database, Server: <Server Name>, click on Next button, select the RadioButton “Using Sql Server Authentication”, enter the Login ID: <User Name>, Password: <Pwd>, click on Next button, select the CheckBox “Change the default database to”, and select the Database to which we want to configure with, click on Next button and Click on Finish button which displays a window showing the connection details, click on Ok button which adds the DSN under ODBC Data Source Administrator window.

Again click on Add button, select a driver for Oracle and click Finish button, which opens a window, in it enter the following details, Data Source Name: OraDsn, Description: Connects with Oracle Database, TNS Service Name: <Server Name>, User ID: Scott/tiger, click on Ok button which adds the DSN under ODBC Data Source Administrator window.
              
Now open the Solution Explorer, right click on References node under project and select “Add Reference” which opens ‘’Reference Manager” dialog box, in that on the LHS under Assemblies option select Framework, now on the RHS select System.Data.OracleClient assembly, click Ok and then write the following code:

using System.Data.Odbc; using System.Data.OleDb; using System.Data.SqlClient; using System.Data.OracleClient;
Under Connect with Oracle using Odbc Driver Button:
OdbcConnection con = new OdbcConnection("Dsn=OraDsn");
con.Open(); MessageBox.Show(con.State.ToString()); con.Close(); MessageBox.Show(con.State.ToString());
Under Connect with Oracle using OLEDB Provider Button:
ocon = new OleDbConnection(“Provider=Msdaora;User Id=Scott;Password=tiger;Data Source=<server name>");
ocon.Open(); MessageBox.Show(ocon.State.ToString()); ocon.Close(); MessageBox.Show(ocon.State.ToString());
Under Connect with Oracle using .Net Provider Button:
OracleConnection con = new OracleConnection("Data Source=Server;User Id=Scott;Password=tiger");
con.Open(); MessageBox.Show(con.State.ToString()); con.Close(); MessageBox.Show(con.State.ToString());
Under Connect with Sql Server using Odbc Driver Button:
OdbcConnection con = new OdbcConnection("Dsn=SqlDsn");
con.Open(); MessageBox.Show(con.State.ToString()); con.Close(); MessageBox.Show(con.State.ToString());
Under Connect with Sql Server using Oledb Provider Button:
scon = new OleDbConnection(); scon.ConnectionString =
"Provider=SqlOledb;User Id=Sa;Password=<pwd>;Database=Master;Data Source=<server name>";
scon.Open(); MessageBox.Show(scon.State.ToString()); scon.Close(); MessageBox.Show(scon.State.ToString());
Under Connect with Sql Server using .Net Provider Button:
SqlConnection con = new SqlConnection(
"Data Source=<Server Name>;Database=Master;User Id=Sa;Password=123");
con.Open(); MessageBox.Show(con.State.ToString()); con.Close(); MessageBox.Show(con.State.ToString());
Sending request to Data Source as a Sql Statement: In this process we send a request to Data Source by specifying the type of action we want to perform using a Sql Statement like Select, Insert, Update, and Delete or by calling a Stored Procedure. To send and execute those statements on data source we use the class Command.

Constructors of the class:                               Command()                         Command(string CommandText, Connection con)
Note: CommandText means it can be any Sql Stmt like Select or Insert or Update or Delete Stmt’s or Stored Procedure Name, whereas connection refers to instance of Connection class created in 1st step.

Properties of Command Class:
  1. Connection: sets or gets the connection object associated with command object.
  2. CommandText: sets or gets the Sql statement or SP name associated with command object.
  3. CommandType: sets of gets whether Command is configured to execute a SQL Statement [default] or S.P.

The object of class Command can be created in any of the following ways:
Command cmd = new Command(); cmd.Connection = <con>; cmd.CommandText = "<Sql Stmt or SP Name>";
or
Command cmd = new Command("<Sql Stmt or SP Name>", <con>);

Methods of Command class:
·        ExecuteReader()                 ->            DataReader
·        ExecuteScalar()                   ->            object
·        ExecuteNonQuery()            ->            int
Note: after creating object of Command class we need to call any of the execute methods to execute the stmt’s.