Ado.Net Tutorials: Introduction to Ado.Net - Part1 Establishing Connection with DataBase

Introduction to ADO.NET:

Introduction:

If you are new to .Net Technologies, want to get job in .net then start learning basics oops concepts and then learn Ado.Net Tutorials.

       

                 This article will give you brief introduction to ADO.NET in .NET like

• What is Ado.Net, 
• What Ado.Net Provides you to communicate with data sources, 
• What are the providers provided by Ado.Net to Communicate with Data Sources like Flat Files(Text Files,XML FIles etc), Databases(Oracle, Sql Server, DB2, Teradata, MySql, Ms Access, etc), 
• Architecture of Ado.Net, Object Model of Ado.Net,
•  How to perform CRUD operations on Databases etc things.  

Description:

It is a set of libraries included in Mircrosoft .Net Framework for Data Source communication from .Net Applications. These libraries are in the form of Drivers and Data Providers. Ado.Net was designed as an extension for older ADO which is unmanaged.

The following are the Data Providers provided by Ado.Net

• ODBC Data Provider.
• Oledb Data Provider.
• Oracle Data Provider.
• Sql Data Provider. 

ODBC Data Provider:

This Data Provider used for communicating with data sources using ODBC interface.
These data sources are older one.

API Prefix is: Odbc

Namespace: System.Data.Odbc.

 Oledb Data Provider:

This Data Provider used for communicating with data sources like Files, Databases,Indexing servers etc.

API Prefix is: Oledb

Namespace: System.Data.Oledb.

Oracle Data Provider:

This Data Provider used for communicating with only Oracle Database using OracleClient Provider.

API Prefix: OracleClient.

Namespace: System.Data.OracleClient.

Sql Data Provider:

This Data Provider used for communicating with only Sql Server Database using SqlClient Provider.

API Prefix: SqlClient.

Namespace: System.Data.SqlClient.

System.Data,System.Data.Oledb, System.Data.SqlClient, System.Data.Odbc all these namespaces are defined under same assembly i.e System.Data.dll where as System.Data.OracleClient namespace defined in System.Data.OracleClient.dll Assembly.

All the above four namespaces contains same set of classes as follows : Connection, Command, DataReader, DataAdapter, CommandBuilder, Parameter etc.

To discriminate between each other these class names are prefixed by their API Prefixes Such as

              OdbcConnection                 OdbcCommand

              SqlConnection                     SqlCommand

              OledbConnection                OledbCommand

              OracleConnection                OracleCommand.

 

 Most of the .Net Applications Use Database as Sql Server So we will go deeply on How to Communicate with Sql Server using SqlClient Data Provider.

If you want to see how this Data Providers Communicate with Data Stores then look at the below diagram.

If you see the Ado.Net Object Communication System in the above image you find that it requires three steps to communicate with Data Stores.

1). Establishing a Connection with Database or Data Store Connection Object.

2). Sending Request i.e in the form of Sql Statement using Command ,Adapter objects.

3). Receiving result using Reader Object or DataSet Object.

Establishing Connection with DataStore:

 To Interact with Database we must have connection with it. We use Connection Object to Establish a channel between database and client for communication.

This Connection Object have following Constructors i.e 

                            Connection( )

                            Connection( string ConnectionString)

ConnectionString is a collection of attributes that are necessary for connecting with a DataSource, those are:
     -Provider
     -Data Source
     -User Id & Password
     -Database or Initial Catalog
     -Trusted_Connection or Integrated Security
     -DSN

Provider:

Provider is required for connecting with any Data Store. we have to use different provider for each Data Store.

Oracle                                     Msdaora
Sql Server                               SqlOledb
MS-Access or MS-Excel        Microsoft.Jet.Oledb.4.0
MS-Indexing Server                 Msidxs

Data Source:

It is the name of the machine on which the Data Source exist. If the Data Source exist in the local machine then it is optional. If Data Source exist in remote machine then we have to mention it in Connection String.

User Id and Password:

As Database's are Secured with Roles and Users, then it is necessary to mention the User Id and Password in the Connection String.

                   Sql Server         sa/

                   Oracle                 scott/tiger

Database or Initial Catalog:

As a single Data Source may contain multiple Databases then it is necessary to mention the Database name in Connection String. These attributes used only for connecting with Sql Server Database.

Trusted_Connection or Integrated Security:

These credentials are used for Security purposes while connecting with Sql Server Database in windows authentication mode.  If you use Windows Authentication Mode by specifying these credentials then no need to mention the User Id and Password in Connection String.

DSN:

These Attributes are required while connecting with data stores using ODBC Providers.

Connection String for Oralce:

"Provider=Msdaora;User Id=Scott;Password=tiger [;Data Source=<server>]"

Connection String for Sql Server:
"Provider=SqlOledb;User Id=Sa;Password=<pwd>; Database=<db name>[;Data Source=<server>]" In case Windows Authentication "Provider=SqlOledb;Trusted_Connection= true/ Integrated Security=SSPI; Database=<db name>[;Data Source=<server>]" SSPI: Security Support Provider Interface.

Members of Connection Class:

1. Open( ): Opens the Connection with Data Store or Database.

2. Close( ):  Closes the Connection that is Open.

3. State: Gets the Status of the Connection.

4. ConnectionString: Gets or Sets the Connection String Associated with the Connection Object.

We can create the connection Object as follows
Connnection ObjCon= new Connection(); ObjCon.ConnectionString = "<connection String>"; or Connection ObjCon = new Connection("<connection String>");

You can also learn What are the different parameters for Connection String and How to set Connection for SqlConnection Object to Connect to Sql Server database.

 Conclusion:

I hope You clear about things like what is Ado.Net, What are the Providers that Ado.Net Provides, How to communicate with data sources ,how to establish Connection with  DataSources.

         In this part we saw only Only How to create a Connection between Application and Database, So in the Second Part I will Cover remaining two things i.e how to send a request as Sql Statement, and How to receive Result.

Don't Forget to Comment and Share for our friends if you like this Post.