21/02 Establishing a connection with DB using ADO.NET

Date 21-2-11 Monday

-          Every operation we perform on a data source involves in three types:

1.      Establishing a connection

2.      Sending request as a statement

3.      Capturing the result given by Data Source

1.      Establishing a connection: - In this process we open a channel for communication with the data source present on local or remote machine to perform the operation.

To open the channel for command we use connection Class.

Constructors:

Connection() Connection( String ConnectionString)

 

-          Connection string is the collection of attributes that are used for connecting with a Data Source - Those are:

§  Provide

§  Data Source

§  User ID and Password

§  Database (or) Initial Catalog

§  Trusted_Connection =  true

(or)

§  Integrated security = SSPI

§  DSN

-          Provide :- As we are aware a provider is required for communicating with data sources, we use a different provider for each Data Source:

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

-          Data Source: - It is the name of target machine to which we want to connect with, doesn’t required to be specify if data is on local machine only.

-          User ID and Password: - As DB’s are secured places for storing data, to connect with them we required a valid username and password.

Oracle	Scott / tiger
Sql server	Sa / < pwd >

-          Database (or) Initial Catalog and Trusted_Connection (or) Integrated security: - are used for connecting with Sql server database.

-          DSN: - attribute is used while working with Odbc classes.

Connection String for Oracle: -

“Provider = msdaord; UserId = Scott; Password=tiger (; Data Source = < server >)”

Connection String for Sql Server: -

“Provider =SqlOledb; UserId = sa; Password=<pwd>;Database = <db name > (; Data Source = < server >)”

-          

      Methods and Providers of connection class:

1.      

      Open():  - open a connection with data source

2.      

     Close(): - close a connection which is open

3.    

          State: - gets the status of connection

4.  

         ConnectionString: - gets or sets a ConnectionString 

 The object of class connection can be created in any of the following ways

Connection con = new connection(); con.ConnectionString = “< con str> ”; (OR) Connection con = new Connection( “< con str> ”);

Open a new windows project naming it as DbOperations and create the form as following: -

using System.Data.OleDb;

Under Button1_Click:             OleDbConnection Oracan = new OleDbConnection("Provider = Msdaora;User Id = Scott;Passwod = tiger;");             Oracan.Open();             MessageBox.Show(Oracan.State.ToString());             Oracan.Close();             MessageBox.Show(Oracan.State.ToString());

Under Button2_Click:             OleDbConnection sqlcon = new OleDbConnection();             sqlcon.ConnectionString = "Provider=SqlOledb;User Id =sa; Password = prabodh; Database=Master";             sqlcon.Open();             MessageBox.Show(sqlcon.State.ToString());             sqlcon.Close();             MessageBox.Show(sqlcon.State.ToString());

2.      Sending request as a statement: - In this process we send a request to data source specifying the type of action we want to perform using an Sql statement,

Like: Select; Insert; Update and Delete

We use command class for executing of statement.

Command()

Command(string sqlstmt, Connection con)

Properties of command:

-          Connection:-  sets or gets the connection object associated with command.

-          ConnectionText: - Sets or Gets the statement associate with command.

Object of a class can be created in any of the following ways: -

Command cmd = new Command() cmd.Connection = <con >; cmd.CommandText = “ < Sql stmts >”; (Or) Command cmd = new Command(“ <Sql stmts> “, con);

Method of command class: -

ExecuteReader            ExecuteScalar              ExecuteNonQuery

à DataReader à Object à int

Note – After creating the object of command class well need to call any of these three methods to execute the stmte.

-          Use ExecuteReader method when we want to execute a “select” statement. That returns data as rows and columns. The method return a object of class DataReader, which holds the data as rows and columns.

-          Use ExecuteScalar method when we want to create a select stmte that returns a single value result. Return type of method is “Object”, which gives a value in generic type.

-          Use ExecuteNonQuery method when we want to execute Non Query stmts (DML statement)

Like: Insert, Update, Delete etc.

            In this case we need to find out the no of Rows affected by the stmte and the return type of the method is “Int”.

Note – Above process of calling an appropriate method in the appropriate case is our third step i.e. Capturing the result given by Data Source.