ADO .Net

1

• ADO .Net
•   
• Modelo para acesso a dados nas aplicação .Net
•  
• Componentes
• DataSet
• .Net Data providers
• Connection
• Command
• DataReader
• DataAdapter

 
2
Arquitectura .Net
3
Accessing Data with ADO.NET
Database

1. Client makes request

1

1. Create the SqlConnection and SqlDataAdapter
   objects

2

1. Fill the DataSet from the DataAdapter and close
   the connection

SqlConnection
3
Web server

1. Return the DataSet to the Client

4
SqlDataAdapter

1. Client manipulates the data

5

1. Update the DataSet

6

1. Use the SqlDataAdapter to open the SqlConnection,
   update the database, and close the connection

7
List-Bound Control
Client
4
.Net Data Providers

• SQL Server - System.Data.SqlClient
• OLE DB - System.Data.OleDb
• ODBC - System.Data.Odbc
• Oracle - System.Data.OracleCli
  ent.

Implementam os Objectos
Connection Estabelece a comunicação com a fonte de dados
Command Executa um comando na fonte de dados
DataReader Lê sequencialmente de uma fonte de dados só leitura
DataAdapter Preenche um DataSet
5
Connection
SqlConnection (SqlServer)
System.Data.SqlClient.SqlConnection
myConnectionStr "Initial CatalogNorthwindData
Sourcelocalhost Integrated SecuritySSPI" Sql
Connection myConnection new SqlConnection(myConn
ectionString) myConnection.Open() ... myConnecti
on.Close()
OleDbConnection (p.e. Access)
System.Data.OleDb.OleDbConnection
myConnection strpathServer.MapPath(loja.mdb")
myConnectionStr"ProviderMicrosoft.Jet.OLEDB.4.0
Data Source" strpath myConnectionnew
OleDbConnection(myConnectionStr) myConnection.Ope
n() ... myConnection.Close()
6
Data Commands

• É uma instância das classes OleDbCommand,
  SqlCommand, OdbcCommand, ou
  OracleCommand
• Contém uma referência para uma instrução SQL ou
  stored procedure
• Propriedades
• Connection
• CommandText
• Parameters
• Métodos
• ExecuteReader Devolve registos para um
  DataReader, usado em Select
• ExecuteNonQuery Usado em Insert, Update e
  Delete

7
Data Commands
private System.Data.OleDb.OleDbConnection
myConnection private System.Data.OleDb.OleDbComma
nd cmd String myConnectionstr"ProviderMicrosof
t.Jet.OLEDB.4.0 Data Source" strpath String
strsql"Insert into Produtos (IdCat,NomeProd,Preco
) Values ('1','" nomeprod "'," "'" preco
"')" myConnectionnew System.Data.OleDb.OleDbCo
nnection(myConnectionstr) cmd.Connection.Open()
cmdnew OleDbCommand (strsql,myConnection)
() cmd.ExecuteNonQuery() myConnection.Close()
() cmdnew System.Data.OleDb.OleDbCommand()
cmd.ConnectionmyConnection
cmd.CommandTextstrsql
8
DataReader
Web Form
DataReader

• Permite ler de uma fonte de dados forward-only
  e read-only
• Criado através do método executeReader de um
  objecto Command
• Pode ser associado ao DataSource de Server
  Controls
• string mySelectQuery "SELECT OrderID,
  CustomerID FROM Orders"
• OleDbConnection myConnection new
  OleDbConnection(myConnString)
• OleDbCommand myCommand new OleDbCommand(mySelec
  tQuery,myConnection)
• myConnection.Open()
• OleDbDataReader myReader
• myReader myCommand.ExecuteReader()
• DataGrid1.DataSourcedtreader
• DataGrid1.DataBind()

9
DataReader
Métodos Read devolve uma linha do resultado
do query if (myReader.HasRows) while
(myReader.Read()) ...
10
What is a Dataset?
DataSet
DataTable




DataTable




DataTable




SqlDataAdapter
Web server memory
Physical storage
SqlConnection
OleDbDataAdapter
OleDbConnection
SQL Server 2000
OleDb Database
11
Data Adapters
12
Creating a DataAdapter

• Store the query in a DataAdapter
• The DataAdapter constructor sets the
  SelectCommand property
• Set the InsertCommand, UpdateCommand, and
  DeleteCommand properties if needed

SqlDataAdapter da new SqlDataAdapter ("select
from Authors",conn)
da.SelectCommand.CommandText da.SelectCommand.Con
nection
13
Creating a DataSet

• Create and populate a DataSet with DataTables
• Fill method executes the SelectCommand
• Access a DataTable

DataSet ds new DataSet() da.Fill(ds,
"Authors")
ds.Tables"Authors".Rows.Count
string str"" foreach(DataRow r in
ds.Tables"Authors".Rows) str r2 str
r"au_lname"