B.Sc.
(CS) TY Unit VI – Database Connectivity
Unit VI
Database Connectivity
* Introduction ADO.Net –
ADO stands for ActiveX Data Objects. ADO.NET is a database technology of .NET
Framework used to connect application system and database server. ADO.NET is a part of the
.NET Framework. ADO.NET consists of a set of classes used to handle data access.
The following figure shows the ADO.NET objects at a glance:
Figure – ADO.Net Architecture
System.Data namespace is the core of ADO.NET and it contains classes used by all data
providers.
1) Data Providers –
A key component of an ADO.NET is Data Provider. The Data Provider classes are meant
to work with different kinds of data sources. They are used to perform all data-management
operations on specific databases.
The .Net Framework includes mainly three Data Providers for ADO.NET. They are the
Microsoft SQL Server Data Provider, OLEDB Data Provider and ODBC Data Provider. SQL
Server uses the SqlConnection object, OLEDB uses the OleDbConnection Object and ODBC uses
OdbcConnection Object respectively.
C#.Net [email protected] 1
B.Sc.(CS) TY Unit VI – Database Connectivity
2) Connection –
The Connection Object provides physical connection to the Data Source. Connection object
needs the necessary information to recognize the data source and to log on to it properly, this
information is provided through a connection string.
3) Command –
The Command Object uses to perform SQL statement or stored procedure to be executed
at the Data Source. The command object provides a number of Execute methods that can be used
to perform the SQL queries in a variety of fashions.
Different execute methods of ADO.NET command object are ExecuteScalar ( ),
ExecuteReader ( ), ExecuteNonQuery ( ). ExecuteScalar ( ) fetches only a single object.
ExecuteReader ( ) fetches result set with multiple rows and loads to DataReader.
ExecuteNonquery ( ) executes SQL statements for insert, update and delete.
4) DataReader –
The DataReader Object is a stream-based, forward-only, read-only retrieval of query results
from the Data Source, which do not update the data. DataReader requires a live connection with
the database.
5) DataAdapter –
DataAdapter Object populates a Dataset Object with results from a Data Source. It is a
special class whose purpose is to bridge the gap between the disconnected Dataset objects and the
physical data source.
6) DataSet –
DataSet class provides mechanisms for managing data when it is disconnected from the
data source. It is completely independent from the Data Source. DataSet provides much greater
flexibility when dealing with related Result Sets.
* Advantages of ADO.Net –
ADO.Net offers several advantages; some of them are listed below,
1) Scalability –
ADO.NET provides full support for disconnected data access. In this type of data access,
most of the time the data we are working with is not connected to a data source.
C#.Net [email protected] 2
B.Sc.(CS) TY Unit VI – Database Connectivity
2) Data Source Independence –
The fundamental object that holds data in ADO.NET is an object of the class DataSet. This
class is present in the namespace.System.Data. DataSet is a completely independent of data source
and no way is DataSet controlled by the data source as it happens in case of RecordSet.
3) Interoperability –
As ADO.NET transmits the data using the format of XML which is not dependent on
ADO.NET or windows platform.
4) Performance –
Less processing time is needed for data conversion in the case of ADO.NET, as data
transmission occurs via XML. This results in better performance of the business application that
is developed with the help of ADO.NET.
5) Firewall –
As in ADO.NET transmission is via XML format, therefore it can pass through firewalls.
6) XML –
ADO.NET programs can take advantage of the flexibility and broad acceptance of
Extensible Markup Language (XML). XML is the format for transmitting datasets across the
network, so any component that can read the XML format can process data.
* Developing a Simple ADO.NET Based Application –
Consider college.mdb database is created in MS-Access with Student table,
Field Data Type
RN Auto Number
Name Text
Fees Number
RN Name Fees
1 Amol 1000
2 Balaji 2000
3 Chetan 3000
Table – Student (Design and Data Sheet View)
C#.Net
[email protected] 3
B.Sc.(CS) TY Unit VI – Database Connectivity
Program – Develop an application that will remove record from Student table depending upon
roll number.
Property Table –
Control Property
Name Text
Label1 - RN
textBox1 txt_rn -
button1 btn_delete Delete
button2 btn_exit Exit
using System.Data.OleDb;
namespace simpleADO
{
public partial class Form1 : Form
{
public Form1( )
{
InitializeComponent( );
}
private void btn_delete_Click(object sender, EventArgs e)
{
C#.Net
[email protected] 4
B.Sc.(CS) TY Unit VI – Database Connectivity
OleDbConnection con = new OleDbConnection ("Provider = Microsoft.Jet.oledb.4.0;
Data Source = d:\\college.mdb"); con.Open();
string query = "delete from student where rn = " + txt_rn.Text ;
OleDbCommand cmd = new OleDbCommand(query, con);
cmd.ExecuteNonQuery( );
MessageBox.Show("Record is deleted");
con.Close( );
}
private void btn_exit_Click(object sender, EventArgs e)
{
Application.Exit( );
}
}
}
* Retrieving & Updating Data from Tables –
a) Retrieving Data from Table –
The SQL SELECT statement returns a result set of records from one or more tables.
Program – Write a program for demonstration of accessing or retrieving data from database.
C#.Net [email protected] 5
B.Sc.(CS) TY Unit VI – Database Connectivity
Property Table –
Control Property
Name Text
Label1 - RN
Label1 - Name
Label1 - Fees
textBox1 txt_rn -
textBox2 txt_name -
textBox3 txt_fees -
button1 btn_search Search
using System.Data.OleDb;
namespace databaseDemo
{
public partial class Form1 : Form
{
public Form1( )
{
InitializeComponent( );
}
private void btn_search_Click(object sender, EventArgs e)
{
OleDbConnection con = new OleDbConnection ("Provider = Microsoft.Jet.oledb.4.0;
Data Source = d:\\college.mdb"); con.Open();
string query = "select * from student where rn =" + txt_rn.Text;
OleDbCommand cmd = new OleDbCommand(query, con);
OleDbDataReader dr = cmd.ExecuteReader( );
if (dr.Read( ))
{
txt_name.Text = dr["name"].ToString( );
txt_fees.Text = dr["fees"].ToString( );
}
C#.Net
[email protected] 6
B.Sc.(CS) TY Unit VI – Database Connectivity
else
{
MessageBox.Show("Record not found");
txt_clear( );
}
con.Close( );
}
}
}
b) Table Updating –
An SQL UPDATE statement changes the data of one or more records in a table.
Program – Write a program for demonstration of modifying or updating data from database.
Property Table –
Control Property
Name Text
Label1 - RN
Label2 - Name
C#.Net [email protected] 7
B.Sc.(CS) TY Unit VI – Database Connectivity
Label3 - Fees
textBox1 txt_rn -
textBox2 txt_name -
textBox3 txt_fees -
button1 btn_update Update
button2 btn_exit Exit
using System.Data.OleDb;
namespace updateData
{
public partial class Form1 : Form
{
public Form1( )
{
InitializeComponent( );
}
private void btn_update_Click(object sender, EventArgs e)
{
OleDbConnection con = new OleDbConnection("Provider = Microsoft.Jet.oledb.4.0;
Data Source = d:\\college.mdb");
con.Open( );
string query = "update student set name = ' " + txt_name.Text + " ',
fees= ' " + txt_fees.Text + " ' where rn = " + txt_rn.Text + " ";
OleDbCommand cmd = new OleDbCommand(query, con);
cmd.ExecuteNonQuery( );
MessageBox.Show("Record is updated");
con.Close( );
}
private void btn_exit_Click(object sender, EventArgs e)
{
Application.Exit( )
}
C#.Net [email protected] 8
B.Sc.(CS) TY Unit VI – Database Connectivity
}
}
* Disconnected Data Access through Dataset Objects –
The ADO.NET Framework supports two models of Data Access Architecture, connected
and disconnected modes.
A connected mode of operation in ADO.Net is one in which the connection to the
underlying database is alive throughout the lifetime of the operation. Meanwhile, a disconnected
mode of operation is one in which ADO.Net retrieves data from the underlying database, stores
the data retrieved temporarily in the memory, and then closes the connection to the database.
ADO.Net provides a new solution by introduce a new component called Dataset. The
DataSet is the central component in the ADO.NET Disconnected Data Access Architecture. It can
hold multiple tables at the same time. DataSets only hold data and do not interact with a Data
Source.
In Connection Oriented Data Access, when you read data from a database by using a
DataReader object, an open connection must be maintained between your application and the Data
Source. Unlike the DataReader, the DataSet is not connected directly to a Data Source through a
Connection object when you populate it.
It is the DataAdapter that manages connections between Data Source and Dataset by fill
the data from Data Source to the Dataset and giving a disconnected behavior to the Dataset. The
DataAdapter acts as a bridge between the DataSet and Data Source.
Program – Write a program for demonstration of disconnected data access through
dataset object.
C#.Net [email protected] 9
B.Sc.(CS) TY Unit VI – Database Connectivity
using System.Data.OleDb;
namespace disconnectedAccess
{
public partial class Form1 : Form
{
public Form1( )
{
InitializeComponent( );
}
private void Form1_Load(object sender, EventArgs e)
{
OleDbConnection con = new OleDbConnection("Provider = Microsoft.Jet.oledb.4.0;
Data Source = d:\\college.mdb");
con.Open( );
string query = "select * from student";
OleDbDataAdapter da = new OleDbDataAdapter(query, con);
DataSet ds = new DataSet( ); da.Fill(ds,
"student"); dataGridView1.DataSource =
ds.Tables["student"]; con.Close( );
}
}
}
C#.Net [email protected] 10
B.Sc.(CS) TY Unit VI – Database Connectivity
C#.Net [email protected] 11
