Access Data Object (cont.)

(1)

ADO.NET

(2)

DataSet

DataTable DataTable DataTable

What is a Dataset?

1

SQL Server 2000

Physical storage

OleDb Database

SqlDataAdapter SqlConnection

Web server memory

OleDbDataAdapter

(3)

Data Adapters

SelectCommand UpdateCommand InsertCommand DeleteCommand

DataAdapter

DataSet

DataReader

sp_SELECT

Command Command Command Command

Connection

sp_UPDATE sp_INSERT sp_DELETE Database

DataReader DataReader

(4)

• Store the query in a DataAdapter

SelectCommand Properties

DataAdapter

da.SelectCommand.CommandText da.SelectCommand.Connection

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

3

Definir os comandos InsertCommand, UpdateCommand, and

DeleteCommand, se necessário

da.SelectCommand.Connection

da.Insertcommand da.UpdateCommand

(5)

Creating a DataSet

Create a DataSet

Fill

– executa o

SelectCommand

Access a DataTable

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"]; }

(6)

DataAdapder / DataSet

string strconn="Provider=Microsoft.Jet.OLEDB.4.0;

….

String strsql=

"Select * from Produtos where IdCat=“ + id;

conn=new OleDbConnection();

•

DataSource de um Controlo

5

conn=new OleDbConnection();

conn.ConnectionString=strconn;

OleDbDataAdapter da;

da = new OleDbDataAdapter (strsql,conn);

DataSet ds= new DataSet();

da.Fill(ds,”Produtos”); //

Populating DataSet

gvProdutos.DataSource=ds;

gvProdutos.DataBind();

(7)

DataAdapder / DataSet

Aceder às tabelas no DataSet

Usar

Tables

para aceder à tabela

Produtos

Objecto DataTable

DataTable dt =

dataset.Tables[“

Produtos

"]

Aceder às linhas da tabela

Usar Rows

Objecto DataRow

DataRow dr= dataset.Tables["Produtos”].Rows[0];

foreach (DataRow dr in dataset.Tables[“Produtos"].Rows) …

(8)

Data-Bound Web Server Controls

ASP.NET Data-Bound Web Server Controls

GridView

displays data as a table and provides the capability to sort columns, page through data, and edit or delete a single record

DetailsView

renders a single record at a time as a table and provides the capability to page through multiple records, as well as to insert, update, and delete records.

update, and delete records.

FormView

renders a single record at a time from a data source and provides the capability to page through multiple records, as well as to insert, update, and delete records. FormView control does not specify a built-in layout.

(9)

Data-Bound Web Server Controls

Repeater

renders a read-only list from a set of records returned from a data source. Repeater control does not specify a built-in layout.

DataList

renders data as table and enables you to display data records in different layouts, such as ordering them in columns or rows

ASP.NET Data-Bound Web Server Controls Overview

(10)

Data-Bound Web Server Controls

Edit, Update, Delete e Insert num Data-Bound

Web Server Controls

Exemplificado com um DetailView

As propriedades e eventos são semelhantes

para os outros controlos

Todos os métodos não estão protegidos para

salientar o código ADO e programação dos

eventos

Cada método deveria:

Tratar excepções

Validar parâmetros de entrada

(11)

DetailsView

Populate DetailsView

void

bindDetailView

()

{

DataAccess dal = new DataAccess();

DataSet ds = dal.

getDatasetProdutos

();

DetailsView1.DataSource = ds;

DetailsView1.DataBind();

} }

(12)

DetailsView Configuration

(13)

DetailsView

Events:

DataBinding

Occurs when the server control binds to a

data source. (inherited from

Control

)

ItemCreated

Occurs when a record is created in a

DetailsView control.

ItemDeleted

Occurs when a Delete button within a

DetailsView control is clicked, but after the delete operation.

ItemDeleting

Occurs when a Delete button within a

ItemDeleting

Occurs when a Delete button within a

DetailsView control is clicked, but before the delete

operation.

ItemInserting

Occurs when an Insert button within a

DetailsView control is clicked, but before the insert

operation.

ItemUpdated

Occurs when an Update button within a

DetailsView control is clicked, but after the update

operation.

(14)

Events:

ItemUpdating

Occurs when an Update button within a

DetailsView control is clicked, but before the update operation.

Load

Occurs when the server control is loaded into the

Page

object. (inherited from

Control

)

ModeChanging

Occurs when a DetailsView control attempts to

change between edit, insert, and read-only mode, but before

the CurrentMode

property is updated.

PageIndexChanged

Occurs when the value of the

PageIndex

PageIndexChanged

Occurs when the value of the

PageIndex

property changes after a paging operation.

PageIndexChanging

Occurs when the value of the PageIndex

property changes before a paging operation.

…

http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.detailsview_events.aspx

(15)

DetailsView

Paging

Property: AllowPaging=true; Event : PageIndexChanging

Property: PageIndex

protected void DetailsView1_PageIndexChanging(object sender, DetailsViewPageEventArgs e) {

DetailsView1.PageIndex = e.NewPageIndex; bindDetailView();

(16)

DetailsView

Edit

Property: AutoGenerateEditButton=true;

Event:

ModeChanging

Property:

DetailsViewMode (Edit, Insert,

ReadOnly )

protected void DetailsView1_

ModeChanging

(object sender,

15

ModeChanging

(object sender, DetailsViewModeEventArgs e) { if ( e.NewMode == DetailsViewMode.Edit) { DetailsView1.ChangeMode(DetailsViewMode.Edit); }

else if (DetailsView1.CurrentMode == DetailsViewMode.Edit && e.CancelingEdit) {

DetailsView1.ChangeMode(DetailsViewMode.ReadOnly); }

(17)

DetailsView

DetailsView Structure (

GridView, FormView, etc)

Contains a collection of Rows

Row contains a collection of Cells

Cell contains a collection of Controls

Rows[0]

Cells[1]

Controls[0] –> (TextBox)

nome=((TextBox)DetailsView1.Rows[2].Cells[1].Controls[0]).Text;

(18)

DetailsView Fields Values

protected void getDetailsView_Values() { idcat = Convert.ToInt32(((TextBox)DetailsView1.Rows[1].Cells[1]. Controls[0]).Text); nome = ((TextBox)DetailsView1.Rows[2].Cells[1].Controls[0]).Text; preco = Convert.ToDouble(((TextBox)DetailsView1.Rows[3].Cells[1] . Controls[0]).Text); stock = Convert.ToInt32(((TextBox)DetailsView1.Rows[4].Cells[1]. Controls[0]).Text); } 17

(19)

DetailsView

Update

Event:

ItemUpdating

(object sender, DetailsViewUpdateEventArgs e)

{

int idprod =

Convert.ToInt32(DetailsView1.DataKey.Value); DataAccess dal = new DataAccess();

getDetailsViewValues();

dal.

updateProduct

(idprod,idcat,nome,preco,stock); DetailsView1.ChangeMode(DetailsViewMode.ReadOnly); bindDetailView();

(20)

DetailsView

Insert ( New)

Property:

AutoGenerateInsertButton

Event:

ItemInserting

(object sender, DetailsViewInsertEventArgs e) sender, DetailsViewInsertEventArgs e) {

DataAccess dal = new DataAccess(); getDetailsViewValues();

dal.

insertProduct

(idcat, nome, preco, stock);

DetailsView1.ChangeMode(DetailsViewMode.ReadOnly); bindDetailView();

}

(21)

DetailsView

Delete

Property: AutoGenerateDeleteButton=true;

Event:

ItemDeleting

(object

sender, DetailsViewDeleteEventArgs e) {

{

DataAccess dal = new DataAccess(); int idprod =

Convert.ToInt32(DetailsView1.DataKey.Value); dal.

deleteProduct

(idprod);

bindDetailView(); }

(22)

Métodos na Class Data Access Layer

getConnection

private OleDbConnection

getConnection

() {

string strcon = DEFAULT_CONN + DEFAULT_DBPATH; OleDbConnection conn = new OleDbConnection(); conn.ConnectionString = strcon; conn.Open(); conn.Open(); return conn; } 21

(23)

Métodos na Classe Data Access Layer

getDatasetProduts

public DataSet getDatasetProdutos() {

OleDbConnection conn = getConnection(); OleDbCommand cmd = new OleDbCommand(); cmd.Connection = conn;

string strsql = "select * from Produtos "; string strsql = "select * from Produtos "; cmd.CommandText = strsql;

OleDbDataAdapter da = new OleDbDataAdapter(); da.SelectCommand = cmd;

DataSet ds = new DataSet(); da.Fill(ds);

return ds; }

(24)

Métodos na Class Data Access Layer

updateProduct

public int

updateProduct

(int idprod, int idcat,string nomeprod,double preco, int stock) { string strsql = "update Produtos SET IdCat=? ,

NomeProd=? , Preco=? , Stock=? where IdProd=?"; OleDbConnection conn = getConnection();

OleDbCommand cmd = new OleDbCommand(); cmd.Connection = conn; cmd.CommandText = strsql; cmd.Parameters.AddWithValue("idcat", idcat); … cmd.Parameters.AddWithValue("stock", stock); cmd.Parameters.AddWithValue("idprod", idprod); int result=cmd.ExecuteNonQuery(); conn.Close(); return result; } 23

(25)

Class Data Access Layer

insertProduc

t

public int

insertProduct

( int idcat, string nomeprod, double preco, int stock){

string strsql = "insert into Produtos (IdCat, NomeProd , Preco , Stock ) values(?,?,?,?)"; OleDbConnection conn = getConnection();

OleDbCommand cmd = new OleDbCommand(); cmd.Connection = conn; cmd.Connection = conn; cmd.CommandText = strsql; cmd.Parameters.AddWithValue("idcat", idcat); cmd.Parameters.AddWithValue("nomeprod", nomeprod); cmd.Parameters.AddWithValue("preco", preco); cmd.Parameters.AddWithValue("stock", stock); int idprod = cmd.ExecuteNonQuery();

conn.Close(); return idprod; }

(26)

Class Data Access Layer

deleteProduct

public int deleteProduct(int idprod) {

string strsql = "Delete from Produtos where IdProd=?"; OleDbConnection conn = getConnection();

OleDbCommand cmd = new OleDbCommand(); cmd.Connection = conn;

cmd.CommandText = strsql;

cmd.Parameters.AddWithValue("idprod", idprod); int res = cmd.ExecuteNonQuery();

conn.Close(); return res; }