Social Icons

Monday, 5 August 2013

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.

Communicating with Ado.Net Data Objects

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:

Connection String for Sql Server:

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

 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.

No comments:

Post a comment

Please Give your Valuable Suggestions,Questions and Comments to improve my blog.