ADO .Net  

Modelo para acesso a dados nas aplicação .Net Componentes:

• DataSet

• .Net Data providers:

•Connection

•Command

•DataReader

•DataAdapter

 

Arquitectura .Net

Accessing Data with ADO.NET

DatabaseDatabase

4. Return the DataSet to the Client

5. Client manipulates the data

2. Create the SqlConnection and SqlDataAdapter objects

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

SqlDataAdapter

SqlConnection

List-Bound

Control

List-Bound

Control

1. Client makes request1111

2222

3333

4444

5555

6. Update the DataSet

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

6666

7777

ClientClient

Web serverWeb server

DataSet

.Net Data Providers

• SQL Server - System.Data.SqlClient

• OLE DB - System.Data.OleDb

• ODBC - System.Data.Odbc

• Oracle - System.Data.OracleClient.

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

Implementam os Objectos:

Connection

System.Data.SqlClient.SqlConnection

myConnectionStr = "Initial Catalog=Northwind;Data Source=localhost; Integrated Security=SSPI;";

SqlConnection myConnection = new SqlConnection(myConnectionString);

myConnection.Open();

...

myConnection.Close();

SqlConnection (SqlServer)

System.Data.OleDb.OleDbConnection myConnection;

strpath=Server.MapPath(“loja.mdb");

myConnectionStr="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + strpath;

myConnection=new OleDbConnection(myConnectionStr);

myConnection.Open();

...

myConnection.Close();

OleDbConnection (p.e. Access)

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

Data Commands

private System.Data.OleDb.OleDbConnection myConnection;

private System.Data.OleDb.OleDbCommand cmd;

String myConnectionstr="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + strpath;

String strsql="Insert into Produtos (IdCat,NomeProd,Preco) Values ('1','" + nomeprod + "'," + "'" + preco +"')" ;

myConnection=new System.Data.OleDb.OleDbConnection(myConnectionstr);

cmd.Connection.Open();

cmd=new OleDbCommand (strsql,myConnection); (*)

cmd.ExecuteNonQuery();

myConnection.Close();

(*) cmd=new System.Data.OleDb.OleDbCommand(); cmd.Connection=myConnection; cmd.CommandText=strsql;

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(mySelectQuery,myConnection);

myConnection.Open();

OleDbDataReader myReader;

myReader = myCommand.ExecuteReader();

DataGrid1.DataSource=dtreader;

DataGrid1.DataBind();

Web Form DataReader

DataReader

Métodos:

Read – devolve uma linha do resultado do query

if (myReader.HasRows) while (myReader.Read()) { ... }

SQL Server 2000

DataSet

DataTable

DataTable

Physical storage

OleDb Database

SqlDataAdapterSqlDataAdapter

SqlConnectionSqlConnection

DataTable

Web server memory

OleDbDataAdapterOleDbDataAdapter

OleDbConnection

OleDbConnection

What is a Dataset?

Data Adapters

sp_SELECT

CommandCommand

SelectCommand UpdateCommand InsertCommand DeleteCommandDataAdapter

CommandCommand CommandCommand CommandCommand

ConnectionConnection

sp_UPDATE sp_INSERT sp_DELETE

Database

DataSetDataSet

DataReaderDataReader

• Store the query in a DataAdapter

• The DataAdapter constructor sets the SelectCommand property

• Set the InsertCommand, UpdateCommand, and DeleteCommand properties if needed

Creating a DataAdapter

da.SelectCommand.CommandText da.SelectCommand.Connection

da.SelectCommand.CommandText da.SelectCommand.Connection

SqlDataAdapter da = new SqlDataAdapter("select * from Authors",conn);

SqlDataAdapter da = new SqlDataAdapter("select * from Authors",conn);

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");

DataSet ds = new DataSet();da.Fill(ds, "Authors");

ds.Tables["Authors"].Rows.Count;ds.Tables["Authors"].Rows.Count; string str="";

foreach(DataRow r in ds.Tables["Authors"].Rows){ str += r[2]; str += r["au_lname"];}

string str="";

foreach(DataRow r in ds.Tables["Authors"].Rows){ str += r[2]; str += r["au_lname"];}