Transcript Delegates and Events

Chapter-5
ADO.Net & Object Oriented
Concepts Using C#
ADO.Net & Object Oriented
Concepts Using C#








5.1 Introduction to Ado. NET, Understanding the
Connection Object
5.2 Building the Connection String.
5.3 Understanding the Command Object.
5.4 Understanding Data Readers.
5.5 Understanding DataSets and DataAddapters
5.6 Data Table, Data Column, Data Row
5.7 Working with System Data OleDB. System
data
5.8 Data Grid
Introduction to Ado. NET







ADO is a Microsoft technology, and stands for ActiveX Data Objects.
Microsoft's ActiveX Data Objects (ADO) is a set of Component Object Model (COM) objects
for accessing data sources.
A part of MDAC, 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; developers 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 execute SQL commands directly.
The disadvantage of the latter is that it introduces a dependency upon the type of database
used.
ADO.NET is a natural evolution of ADO, built around n-tier development and architecture, with
XML at its core.

ADO.NET is the new set of classes that exposes data access services to the .NET programmer.

It is an integral part of the class Framework, which contains the entire library of classes that
Microsoft provides with .NET, including the base classes for the primitive system types, I/O,
network, data, and XML.
Introduction to Ado. NET

ADO.NET is an object-oriented set of libraries that allows
you to interact with data sources. Commonly, the data
source is a database, but it could also be a text file, an Excel
spreadsheet, or an XML file.

ADO.NET is the data access classes for the .Net
framework
ADO.NET is designed for highly efficient data access
ADO.NET is Support for XML and disconnected record
sets


Introduction to Ado. NET


ADO .NET is a collection of classes, interfaces,
structures, and enumerated types that manage
data access from relational data stores within
the .NET Framework.
ADO .NET is an evolution from ADO.

Does not share the same object model, but shares
many of the same paradigms and functionality!
Comparison
Ur disconnected recordsets can’t bypass a
That’s
butis Iinevitable
measily
not
But
comparison
firewall;
myimpressive,
XML
data stream
OK. Let’s
start…
listening
any
more
as I mthe
checking
as
we
represent
same
bypasses
a
firewall
Thank
you
Mr.
ADO
for
the
Imemory,
don’t
like
Inmy
I an
represent
data
think
I m better
IU
can
transmit
data
between
cell
fortechnology.
SMS
II just
received.
would
be
surprised
that
I
u
have
provided
comparisons
Aservices
Recordset
–
ha..ha..haa
Itoo
support
working
than
u
in
ofthat
different
applns
using
Cform
U
Later.
BYE!!
I
support
working
too
support
feature,
for
Microsoft
overthe
years.
Itdisconnected
is just
like a with
single
table.
disconnected
data
Recordset
recordsets.
disconnected
with
the
data
but
in
a
different
way!!
If
uaccomplish
wanna
use more
than
source.
To
this,
I
use
source.
Ione,
use
XML
data
stream
for
Microsoft
has
decided
to
u
have
to
use
JOIN.
I
communicate
to
the
COM
Marshalling.
But,
I communicate
toand
the
transmitting
data
between
terminate
ur
services
Instead,
Igr8
prefer
to
database
using
Adapters
Isn’t
this
a
feature.
database
using
calls
to
applications.
we
would
like
a
resignation
represent
data
in the
formur
that
allows
greater
control
Now
I
think
u
should
shut
OLEDB
providers
COM
marshalling
provides
Mr. ADO
Mr. ADO.NET
letter
from
you.
ofover
DataSet
which
is
a
optimization,
mouth
and
stoptypes;
a
limited
set
of
data
collection
of oneetc
or more
validation,
comparing…OK!
there
isrelationships
no restrictionb/n
on
- Bill Gates
tables,
data types
tablesin XML
ADO / ADO.Net Comparisons
Feature
In memory
data storage
ADO
Recordset object
Mimics single table
ADO.Net
Dataset object
Contains DataTables
Data Reads
Sequential
Sequential or nonsequential
Data Sources
OLE/DB via the
Connection object
Managed provider calls
the SQL APIs
ADO / ADO.Net Comparisons
Feature
Disconnected
data
ADO
Limited support,
suitable for R/O
ADO.Net
Strong support, with
updating
Passing datasets
COM marshalling
DataSet support for
XML passing
Scalability
Limited
Disconnected access
provides scalability
The Architecture
ADO.NET COMPONENTS
.NET Framework Data Provider
Connection
DataAdapter
SelectCommand
Transaction
Data Set
DataTableCollection
DataTable
DataRowColln
InsertCommand
Command
DataColColln
Parameters
UpdateCommand
DeleteCommand
DataReader
Database
ConstraintColln
DataRelationColln
XML
.NET
Data Providers
SQL .NET
Data Provider
SQL SERVER
Client
OLE DB .NET
Data Provider
OLE DB
Provider
ODBC .NET
Data Provider
ODBC
Driver
Other DB
Other DB
ADO.Net object model
Fill
DataAdapter
DataSet
DeleteCommand
UpdateCommand
InsertCommand
SelectCommand
Update
Errors Collection
Command
Connection
Data Source
Parameters
Understanding the Connection Object



To interact with a database, you must have a
connection to it.
The connection helps identify the database
server, the database 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 database to execute the
command.
Understanding the Connection Object
Creating Connection Object:
 A SqlConnection is an object, just like any other
C# object. Most of the time, you just declare and
instantiate the SqlConnection all at the same
time, as shown below:
SqlConnection conn = new SqlConnection ( );

Understanding the Connection Object
Creating ConnectionString:
 The SqlConnection object instantiated above uses
a constructor with a single argument of type
string. This argument is called a connection
string.
 SqlConnection conn = new SqlConnection
(“Data Source= (local); Initial
Catalog=Northwind; Integrated
Security=SSPI");
Parameter





Data Source :Identifies the server. Could be local
machine, machine domain name, or IP Address.
Initial Catalog :Database name.
Integrated Security :Set to SSPI to make connection
with user's Windows login
User ID :Name of user configured in SQL Server.
Password :Password matching SQL Server User ID.
Connecting to a database

The Namespace


For specific data providers, use specific
namespace




System.Data
MS Access  System.Data.OleDb
Sql Server  System.Data.SqlClient
Step 1: Include appropriate namespace
Step 2: Define the connection string

For MS Access:
Provider=Microsoft.Jet.Oledb.4.0; Data
Source=dbData.mdb
Connecting to a database(contd..)


Step 3: Create a connection object


TypeConnection cn = new
TypeConnection(connectionString);
Step 4: Call the Open( ) method


For Sql Server:
server=(serverName); integrated security=SSPI;
database=databaseName;
server=(serverName); uid=sa; pwd=pa;
database=databaseName;
cn.Open( )
Step 5: When done, close the connection
Properties of the Connection
Object:

Property of connection object
Understanding the Command
Object


The process of interacting with a database 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 database.
The Command object is represented by two
corresponding classes: SqlCommand and
OleDbCommand. Command objects are used to
execute commands to a database across a data
connection.
Understanding the Command
Object

Example of command object
Command object has the
following important methods.

ExecuteNonQuery (): Used to execute an SQL
statement that doesn’t return any value like insert,
update and delete. Return type of this method is int
and it returns the no. of rows affected by the given
statement.
Command object has the
following important methods.

ExecuteScalar (): Used to execute an SQL statement
and return a single value. When the select statement
executed by ExecuteScalar () method returns a row
and multiple rows, then the method will return the
value of first column of first row returned by the query.
Return type of this method is object. It is very useful to
use with aggregate functions like Count (*) or Sum ()
etc.
Command object has the
following important methods.


ExecuteReader (): Used to execute a select a statement
and return the rows returned by the select statement as
a DataReader. Return type of this method is
DataReader.
ExecuteXMLReader (): Return an XmlReader,
Available for a SqlCommand object only.
Properties of the Command
Object:


Connection: used to specify the connection to be used by
the command object.
CommandType: Used to specify the type of SQL
command you want to execute. To assign a value to this
property, use the enumeration CommandType that has
the members Text, StoredProcedure and TableDirect.
Text is the default and is set when you want to execute
ant SQL command with command object.
StoredProcedure is set when you want to call a stored
procedure or function and TableDirect is set when you
want to retrieve data from the table directly by specifying
the table name without writing a select statement.
Properties of the Command
Object:


CommandText: Used to specify the SQL statement
you want to execute.
Transaction: Used to associate a transaction object to
the command object so that the changes made to the
database with command object can be committed or
rollback.
Using Namespaces


VB.Net
Imports System.Data
Imports System.Data.SqlClient
Dim sqlAdp as SqlDataAdapter
C#
using System.Data;
using System.Data.SqlClient;
SqlDataAdapter sqlAdp= new
SqlDataAdapter();
The DataReader object


DataReader objects are highly
optimised for fast, forward only
enumeration of data from a data
command
A DataReader is not disconnected
The DataReader object




Access to data is on a per record basis.
Forward only
Read only
Does support multiple recordsets
Creating a data reader
SqlDataReader sqlReader;
sqlReader = sqlCommand.ExecuteReader();
while (sqlReader.Read())
{
// process, sqlReader("field")
}
sqlReader.Dispose();
Other Methods





GetString(), GetInt() etc.
GetSqlString(), GetSqlInt32() etc.
GetValues()
IsDBNull()
GetSchemaTable()
DataSets



In-memory representation of data
contained in a database/XML
Operations are performed on the
DataSet, not the data source
Can be created programmatically, using
a DataAdapter or XML schema and
document (or any mixture)
Creating DataSets




Setup SqlConnection
Setup a SqlDataAdapter
Create a DataSet
Call the .Fill() method on the DA
DataAdapters




Pipeline between DataSets and data
sources
Geared towards functionality rather
than speed
Disconnected by design
Supports select, insert, delete, update
commands and methods
DataAdapters


Must always specify a select command
All other commands can be generated
or specified
Using the DataAdapter
SQLDataAdapter sqlDA =
new SqlDataAdapter();
sqlDA.SelectCommand =
new SqlCommand ("select * from
authors“, sqlConnection);
DataSet sqlDS = new
DataSet("authorsTable");
sqlDA.Fill(sqlDS, "authorsTable");
DataAdapters


For speed and efficiency you should set
your own InsertCommand,
UpdateCommand and DeleteCommand
Call GetChanges to seperates the
updates, adds and deletes since the
last sync. Then sync each type.
Sets, Tables and Rows
DataSet
DataTable
DataTable
DataRow
DataRow
Using DataTables
With a DataTable we can
 Insert, modify and update
 Search
 Apply views
 Compare
 Clear
 Clone and Copy
DataRelations



New to ADO.Net
Tables within a DataSet can now have
relationships, with integrity.
Supports cascading updates and
deletes.
DataViews



Like a SQL view
Single, or multiple tables
Normally used with GUI applications via
Data Binding.