Database connectivity:

Interrelated and integrated collection of data is called database. Databases can be classified into 2 types

1.local database

Ex:foxpro

2.remote database

Ex:oracle, sqlserver

 

->sql(structured query language) is required to perform different operations with database

->sql is providing set of commands in the form of sub languages

1)data definition language(DDL)

   Create, alter and drop commands

2.data manipulation language(DML)

  Insert,delete & update commands

3.data retrieval language

    Select command

 

ADO.NET[active data objects for .NET]

->ADO.NET is a technology provided with in .net to internet with database are xml

->.Net to providing set of libraries to work with ADO.NET

    System.Data.dll

    System.Data.oracleclient.dll

->ADO.NET 3.5 is provided with LINQ

->ADO.NET 4.0 is provided with ado.net entity frame work

->ADO.NET provides 2 components

1..net data provider[connected model]

2.dataset[disconnected model]

 

.NET data provider

->.NET data provider is a collection of components [classes] to handle database connectivity issues, It is called as connected model

->.NET is providing following .net data providers

1.oledb.net data provider

     This can be used to connect with any database using oledb provider

2.sqlserver .net data provider

    This can be used to connect with sqlserver 7.0 (or) later using native protocol programming[TDS]

3.oracle.net data provider

    This can be used to connect with oracle8i (or) later using native protocol programming[TSN]

4.odbc.net data provider

    This can be used to connect with any database using odbc driver programming

 

Note:

->oracle.net is deprecated with in .net 4.0[it may be removed in future versions]

->different companies are providing .net data providers, this are called third party .NET data providers

1.oledb.netdata provider: this is given by oracle corporation to connect with oracle9i (or) later

2.my sql direct.net data provider

        This is given by corelab software to connect with mysql database

->each database will have IIS own standards[rules] for communication, This is called native protocol of database

->TDS[tabular data stream] is a native protocol of sqlserver database

->TNS[tabular network substream] is a native protocol of oracle database

->oledb provider/odbc driver is a com dll [windows operating system specific dll, not a .NET dll] to implement protocol standards of database different oledb providers are available to different databases

->.net data provider working based native protocol standards  of database is called “managed .net data provider”

Ex:sqlserver.net

->.Net provider working based on oledb provider / odbc driver is called “unmanaged .net data provider”

Ex:oledb.net and odbc.net

->managed .net data provider provides better performance over unmanaged .net data provider

 

Oledb.net data provider:

->oledb.net data provider can be used when there is no managed .net data provider to the required database

Ex:access

     Sqlserver

    Oracle8i

->oledb.net provider classes are provided with in system.data.oledb namespace

  Oledb connection class

 Oledb command class

 Oledb datareader class

 

Oledb connection class:

->Connection class can be used to establish bridge between .net application and database

->syntax for creating connection object:

Oledbconnection con=new oledbconnection(“provider=oledbprovidername;userid=…;password..;datasource=….”)

->provider requires oledbprovider name for connecting to required database

Ex:

oledb providername                   database

msdaora.1                                    oracle

sqloledb.1                                     sqlserver

Microsoft.ace.oled                       access 2007

->userid & password requires database login parameters

  [login parameters are not required for access]

->data source requires system name in which database is available. This is mandatory if database is present in remote system

[in case of access data source requires path of database file->accdb(mdb file path)]

 

Methods:

1.open()

This method will establish connection to database based on connectionstring

2.close()

This method will close connection

3.dispose()

This method will close connection by removing connectionstring

Note:closed connection can be opened, disposed connection can not be opened

 

4.connectionstring[property]

It can be used to specify connectionstring programmatically

 

Oledb command class

1)this class can be used to excute sql command with database

2)sql command can be ddl statement, dml statement drl statement and stored procedure

Syntax:

Oledbcommand cmd=new oledbcommand(“sql command”,connection)

 

Properties & methods

1.commandtext-specify sql statement to command object programmatically

2.connection-specify connection object to command object programmatically

3.executescalar()

    This method can be used to execute select statement returning only one value

EX:select count (*) from login where uname=”raju” and password=”123”

4.execute nonquery()

  This method can be used to execute dml statement[insert, delete & update] and ddl statement[create, alter and drop]

   This method can be used to execute and sql statements other than select statements

5.executereader()

This method can be to execute select statement returning more than one value. It returns datareader object to read all the values into application

 

Note:

Using execute scalar method with select statements returning more than one value will not be an error, it returns 1^st record 1^st coloum data