Transcript Introduction to ADO.NET

Introduction to ADO.NET
 The objectives of this lecture are:








Learn what ADO.NET is.
Understand what a data provider is.
Understand what a connection object is.
Understand what a command object is.
Understand what a DataReader object is.
Understand what a DataSet object is.
Understand what a DataAdapter object is.
Create simple programs which use the above objects.
ADO
 Microsoft's ActiveX Data Objects (ADO) is a set of
Component Object Model (COM) objects for accessing data
sources.
 It provides a middleware layer between programming languages
and OLE DB (a means of accessing data stores, whether they be
databases or otherwise, in a uniform manner).
 ADO allows a developer to write programs that access data without
knowing how the database is implemented.
 He must be aware of the database for connection only.
 No knowledge of SQL is required to access a database when using
ADO, although one can use ADO to directly execute SQL
commands.
ADO.NET
 ADO.NET is a set of computer software components that
programmers can use to access data and data services.
 It is a part of the base class library that is included with the
Microsoft .NET Framework.
 It is commonly used by programmers to access and modify data
stored in relational database systems, though it can also access
data in non-relational sources.
 Commonly, the data source is a data base, but it could also be a text file,
an Excel spread sheet, or an XML file.
 ADO.NET is sometimes considered an evolution of ActiveX
Data Objects (ADO) technology, but was changed so
extensively that it can be considered an entirely new product.
 A way to interact with a data base.
 There are many different types of data bases available.
 For example, Microsoft SQL Server, Microsoft Access, Oracle,
Borland Interbase, IBM DB2, etc.
 Data Source can be defined as a piece of software that
represents information stored in physical media such as
Database.
 Data Binding associates underlying data to the User Interface
(UI) elements. The data holds the information and UI elements
render the information in a user perceivable form.
 Data binding can be read only - information from the Data Source
flows only to the UI elements.
 Alternatively, Data Binding can be read and write.
 Data Provider is a simplified Data Access architecture for Data
Binding, offered with better maintainability in mind. Data
provider is an abstraction over Data Source to provide a flexible
and uniform data binding mechanism.
 You can use ADO.NET to access data sources using the new
.NET Framework data providers. These data providers include:
 .NET Framework Data Provider for SQL Server.
 .NET Framework Data Provider for OLE DB.
 .NET Framework Data Provider for ODBC.
 .NET Framework Data Provider for Oracle.
 UI elements access the data from these Data Sources through
specific Data Provider API with out the need to understand the
Data Source specific data access format.

ADO.NET data access options
Command object
Select * from Actors
Sql Connection

Data Access layer

.Net Managed Provider
.Net Data provider

ODBC
OLE DB
ODP.Net
MySql
MS Sql
Server
Oracle
Ms
Access
Data Providers
 ADO.NET allows us to interact with different types of data
sources and different types of data bases. However, there isn't a
single set of classes that allow you to accomplish this
universally. Since different data sources expose different
protocols, we need a way to communicate with the right data
source using the right protocol.
 Some older data sources use the ODBC protocol, many newer
data sources use the OleDb protocol, and there are more data
sources every day that allow you to communicate with them
directly through .NET ADO.NET class libraries.
 ADO.NET provides a relatively common way to interact
with data sources, but comes in different sets of libraries for
each way you can talk to a data source.
 These libraries are called Data Providers and are usually
named for the protocol or data source type they allow you to
interact with.
Provider
Name
ODBC Data
Provider
API Prefix
OleDb Data
Provider
OleDb
Data Sources that
expose an OleDb
interface, i.e. Access or
Excel.
Oracle Data
Provider
Oracle
For Oracle Data Bases.
Odbc
Data Source
Description
Data Sources with an
ODBC
interface. Normally older
data bases.
SQL Data
Provider
Sql
Borland Data Bdp
Provider
For interacting with Microsoft
SQL Server.
Generic access to many data
bases such as Interbase, SQL
Server, IBM DB2, and Oracle.
ADO.NET Architecture
 1. the first layer directly talks to the data base and handles connection
as well as request forwarding. These components are collectively called
the DataProvider
 SqlConnection connects to the Server (address, user name, password
etc)
 SqlCommand is used to send requests (SELECT, CREATE, INSERT
etc)
 SqlDataReader is used to fetch the result of SELECT requests. The
rows received from the Server can be handled by our code (for
computation or display purposes)
 2. an intermediate component transfers the data between the
DataProvider and the components which store the data in memory: it is
the DataAdapter.
 It contains:
 four SqlCommand components, one for each possible SQL request:
SELECT, INSERT, DELETE, UPDATE
 a TableMappings component, allowing to replace the syntactic table
names (Table1, Table2, ...) with semantic names (Invoice, Orders,
...)
 the Table rows can be stored and handled in memory. Those rows
are provided:
 by the DataAdapter
 by reading a disc file (XML for example)
 by C# code

 3. The memory storage components are called DataSet, and include:
 a collection of DataTables
 constraints (NOT NULL etc)
 relations (foreign keys, cascading rules etc)
 views, used for filtering rows, projecting columns, sorting, aggregate computation,
searching etc
 4. finally, to display the rows and let the user modify them, we have
 controls, such as TextBox, ListBox or DataGrid
 DataBindings components which synchronize the modifications between the visual
controls and in memory data
 visual controls can also display data coming from other
sources than some DataSet, for example an ARRAY or a
tList:
 the DataAdapter plays the role of a middle man:
 it loads the data form the Server into the DataSet. This is performed by calling:
 DataAdapter.Fill(DataSet)


Data adapter saves the modifications coming from the visual controls. This is done
when we call:
DataAdapter.Update()
ADO.NET Objects
 The Connection Object
 To interact with a data base, you must have a connection to
it. The connection helps identify the data base server, the
data base name, user name, password, and other parameters
that are required for connecting to the data base. A
connection object is used by command objects so they will
know which data base to execute the command on.
 The Command Object
 The process of interacting with a data base means that you must
specify the actions you want to occur. This is done with a
command object.
 You use a command object to send SQL statements to the data
base.
 A command object uses a connection object to figure out which
data base to communicate with.
 You can use a command object alone, to execute a command
directly, or assign a reference to a command object to an
SqlDataAdapter, which holds a set of commands that work on a
group of data
 The DataReader Object
 Many data operations require that you only get a stream of data
for reading. The data reader object allows you to obtain the
results of a SELECT statement from a command object.
 For performance reasons, the data returned from a data reader is
a fast forward-only stream of data. This means that you can only
pull the data from the stream in a sequential manner. This is
good for speed, but if you need to manipulate data, then a
DataSet is a better object to work with.
 The DataSet Object
 DataSet objects are in-memory representations of data. They
contain multiple DataTable objects, which contain columns and
rows, just like normal data base tables. You can even define
relations between tables to create parent-child relationships.
 The DataSet is specifically designed to help manage data in
memory and to support disconnected operations on data, when
such a scenario make sense.
 The DataSet is an object that is used by all of the Data Providers,
which is why it does not have a Data Provider specific prefix.
 The DataAdapter Object
 Sometimes the data you work with is primarily read-only and
you rarely need to make changes to the underlying data source.
 Some situations also call for caching data in memory to
minimize the number of data base calls for data that does not
change.
 The data adapter makes it easy for you to accomplish these things
by helping to manage data in a disconnected mode.
 The data adapter fills a DataSet object when reading the data and
writes in a single batch when persisting changes back to the data
base.
 A data adapter contains a reference to the connection object and
opens and closes the connection automatically when reading
from or writing to the data base.
 Additionally, the data adapter contains command object
references for SELECT, INSERT, UPDATE, and DELETE
operations on the data
 . You will have a data adapter defined for each table in a DataSet
and it will take care of all communication with the data base for
you.
 All you need to do is tell the data adapter when to load from or
write to the data base.
The application can connect to a database using either a DataSet
and a DataAdapter or using a DataReader.
Create table
 connect to the Database using a SqlConnection component
 use an SqlCommand component, connect it to the SqlConnection, fill in the CommandText
property with our request and call the SqlConnection. ExecuteNonQuery() method
 For coding purposes, SQL requests are divided in two groups: the requests which
modify in some way the data on the SQL Server: CREATE TABLE, DROP TABLE,
ADD INDEX, INSERT rows, ALTER TABLE parameters and so on. We simply send
the text of the request to the Server, and do not expect any data in return (or possibly a
success / error notification code) . Use .ExecuteNonQuery()
 the request to retrieve some data (usually full rows, but also aggregates like COUNT,
AVERAGE, SUM etc). For those requests, the Client must first allocate a reception
buffer. Use ExecuteReader().

 con = new OleDbConnection("Provider=MSDAORA;Data
Source=amrita;Password=amma;User ID=mata");








cmd = new OleDbCommand();
cmd.CommandText = "Select * from employee";
cmd.Connection = con;
DataTable dt = new DataTable();
OleDbDataAdapter ada = new OleDbDataAdapter(cmd);
ada.Fill(dt);
dataGridView1.DataSource = dt;
 private void UpdateButton_Click(object sender, EventArgs e)

{
cmd.CommandText = "update student set ST_ID=100 where ST_ID =1";
con.Open();
try
{
int c = cmd.ExecuteNonQuery();
MessageBox.Show("Successfule update");







}
catch(Exception ex)
{
MessageBox.Show(ex.ToString());
}
con.Close();







}