24/02 Accessing DATA from a DataReader:

Date 24/2/11 Thursday

Accessing DATA from a DataReader: - DataReader is a class which hold Data in the form of Rows and Columns (Table Structure). To access the data from the DataReader, it provide us following methods.

1.      Read() à bool    (return type)

-          Moves the record pointer from current location to next Row and returns a status specifying, where the Row contains Data to which it has moved, that will be true if present or False if not present.

2.      GetValue(int index) à object

-          Used for retrieving field values from the row to which pointer was pointing by specific the column index position.

Note -  we can also access the row pointer by pointer in the form of single dimensional array also, either by specifying column index position or name, As following:

< DR > [index ]             à object < DR > [Colname]        à object

3.      GetName(int Index)   à String

-          Returns the name of column for given index position.

4.      NextResult()               à bool

-          Moved the record pointer from current table to next table if a table exists & returns true or else returns False.

Add a new form in the project:

Pics

-          Now write the following code:

using System.Data.OleDb;

Variable Declaration : OleDbConnection  con; OleDbcommand     cmd; OleDbDataReader   dr;

Under Form load : con = new OleDbConnection(“Provider=Msdaora; User ID = Scott; Password=tiger”); cmd = new OleDbcommand(“Select Deptno, Dname, Loc From Dept”, con); con.Open(); dr = cmd.ExecuteReader(); lable1.Text = dr.GetName(0); lable2.Text = dr.GetName(1); lable3.Text = dr.GetName(2); ShowData();

Define a method ShowData : Private void ShowData() {    If(dr.Read())    {       textBox1.Text = dr.GetValue(0).ToStrring();       textBox1.Text = dr[1].ToString();       textBox1.Text = dr[“Loc”].ToString();    } else    {          MessageBox.Show(“Last Record”    } }

Under next button : ShowData();

Under Close Button : If(con.state != ConnectionState.Closed) con.Class(); this.Close();

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.

19/02 ADO.NET :

Date 19-2-11 Saturday

ADO.NET

Data Source Communication: - Data source is a storage device under which we can store data. Every storage device like: File, Database or Indexing sever can be called as data sources

                

                - Programming language can not communicate with a data source directly because each data source adopts a different protocol for communication.

                - To facilitate the process of communication Microsoft has provide a solution in the form of Drivers and Providers, which acts as a mediator b/w a  language and Data Source.

Drivers: - Initially these are designed for targeting the databases, Drivers are of two types:

 1.       JET drivers

2.       ODBC drivers

Jet Drivers: - (Joint Engine Technology) these driver are designed for targeting Local Databases.

ODBC Drivers (Open Data-Base Connectivity) : -  These drivers are designed for targeting remote databases

Like: Oracle, SQL server, Sybase, Ingress etc.

Drawbacks of Drivers: -

1.       These are initially designed for targeting the database only.

2.       Derivers Reside on client machine, so each and every machine on which the application is present the Drivers has to be config explicitly.

OLEDB Providers: - (Object Linking and Embedding Database) these are designed to resolve the problems with drivers targeting Data Source communication and moreover providers reside on server but not on client.

Note – Both drivers and providers suffers from a common drawback i.e. they are OS dependent because they were developed using Native code Languages.

Classical Visual Basic language was not able to use Drivers and Providers directly because drivers and provide have native code support. So Visual Basic Language used few intermediate components for communication with Drivers and Providers as following.

Here DAO’s means: Data Access Objects

RDO’s: Remote Data Objects

ADO’s: Active Data Objects

Int Que. What is difference B/W ADO’s and ADO.Net?

ADO.NET

ADO.NET: - It is a collection of managed Providers that can be used for communication with data Sources. When .NET was introduced to communicate with Data Sources they have designed ADO.net, which is an extension to older ADO. I.e. collection of unmanaged providers.

- ADO.net provides various classes that can be used for data source communication under the following namespace.

-        System.Data

-        System.Data.Oledb

-        System.Data.SqlClient

-        System.Data.OracleClient

-        System.Data.Odbc

1.       System.Data à Set of types used for holding and manage data on the client machine classes under this are sets DataSets, DataTable, DataColumn, DataView, DataRelation etc.

2.       System.Data.Oledb à collection of type used for communicate with any data source Like: Files, Databases, indexing servers etc.

3.       System.Data.SqlClient à collection of types used for communication only with Sql server databases

4.       System.Data.OracleClient à collection of types used for communication only with oracle  databases

5.       System.Data.Odbc à collection of types used for communication with traditional odbc drivers, which will in turn communicate with data sources.

All the above 4 namespace contain same set of classes as following: Connection, Command, DataReader, DataAdopter, CommandBuilder, Parameter etc.

Note – Each class was referred by prefixing with the namespace before class name to discriminate b/w each other as following:

-        OledbConnection	-        OledbCommand
-        SqlConnection	-        SqlCommand
-        OracleConnection	-        OracleCommand
-        OdbcConnection	-        OdbcCommand