Lecture 6: ADO.NET Part II

brickborderΑσφάλεια

3 Νοε 2013 (πριν από 3 χρόνια και 9 μήνες)

91 εμφανίσεις


1

ISDV 165



Lecture #
6
: ADO.NET
-

Part
I
I



Working with
DataSource controls:


DataSource controls encapsulate data access and can be configured
declaratively or programmatically. We have the following standard data source
controls in ASP.NET:



SQLDataSource:

provides data access using SQL syntax



ObjectDataSource: provide data access through business objects



XmlDataSource: provide data access through XML



We have al
r
eady worked with SqlDataSource and XMLDataSource.

In the next
section we will discuss Object
DataSource.


Working with ObjectDataSource control


Using the SQLDataSource control we can manipulate database data without
writing a single line of code.


The SqlDataSource is not appropriate for multi
-
tiers applications since ti mixes
the user interface

layer with the business logic layer. To work around this
limitation, we can use ObjectDataSource control.

This control enable
s

us to bind data controls to a middle
-
tire business objects.
When selecting a specific business object the control enables us to

set the data
method: select, insert, update and delete. We can also provide parameters to
any of these method
s
. This control supports the following kinds of parameters:



ControlParameter:
represents

the value of a property of a control



CookieParameter: rep
resents the value of a cookie send by a browser



FormParameter: represents tha value of a form field



profilePrarameter: represents the value of a profile property



SessionParameter: represents the value of a session field



QueryStringParameter: represents th
e value of a query string field (from a
form submitted using get method)






Assume that we have the following business logic code:


using

System;

using

System.Data;


2

using

System.Configuration;

using

System.Web;

using

System.Web.Security;

using

System.W
eb.UI;

using

System.Web.UI.WebControls;

using

System.Web.UI.WebControls.WebParts;

using

System.Web.UI.HtmlControls;

using

System.Data.SqlClient;

///

<summary>

///

Summary description for DB

///

</summary>

public

class

DB

{


public

DB()


{



//



// TODO: A
dd constructor logic here



//


}


public

SqlDataReader

GetRegions()

{

System.Data.SqlClient.
SqlConnection

sqlConnection1 =
new

System.Data.SqlClient.
SqlConnection
();



// to use Windows authentication method, uncomment the
following line and co
mment using the SQL authentication method


// sqlConnection1.ConnectionString = "Data
Source=HUMBERCOMP;Initial Catalog=Northwind;Integrated Security=True";




// using SQL authentication method












sqlConnectio
n1.ConnectionString =
"Data
Source=HUMBERCOMP;Initial Catalog=Northwind;User ID=sa;Password=humber
"
;



sqlConnection1.Open();


System.Data.SqlClient.
SqlCommand

cmd =
new

System.Data.SqlClient.
SqlCommand
();


cmd = sqlConnection1.Create
Command();


cmd.CommandType =
CommandType
.Text;


cmd.CommandText =
"Select * from region"
;


System.Data.SqlClient.
SqlDataReader

dr =
cmd.ExecuteReader(
CommandBehavior
.CloseConnection);


return

dr;

}


}



We can use the ObjectDat
aSource to encapsulate the functionality provided by
the above class and bind a G
r
idView to the DataSourceObject


<%
@

Page

Language
="C#"

AutoEventWireup
="true"

CodeFile
="usingDataSourceObject.aspx.cs"

Inherits
="_Default"

%>



3

<!
DOCTYPE

html

PUBLIC

"
-
//W3C/
/DTD XHTML 1.0 Transitional//EN"

"http://www.w3.org/TR/xhtml1/DTD/xhtml1
-
transitional.dtd">


<
html

xmlns
="http://www.w3.org/1999/xhtml"

>

<
head

runat
="server">


<
title
>
Using DataSourceObject
</
title
>

</
head
>

<
body
>


<
form

id
="form1"

runat
="server">



<
div
>


<
asp
:
ObjectDataSource

ID
="ObjectDataSource1"

runat
="server"

SelectMethod
="GetRegions"


TypeName
="DB"></
asp
:
ObjectDataSource
>




</
div
>


<
asp
:
GridView

ID
="GridView1"

runat
="server"

DataSourceID
="ObjectDataSource1">



</
asp
:
GridView
>


</
form
>

</
body
>

</
html
>




The ObjectDat
a
Source let you set the methods for
s
elect,

u
pdate delete and
insert. We can also use parameters with the ObjectDataSource. Assume that the
DB class defined above has been modified as follow
s:



using

System;

using

System.Data;

using

System.Configuration;

using

System.Web;

using

System.Web.Security;

using

System.Web.UI;

using

System.Web.UI.WebControls;

using

System.Web.UI.WebControls.WebParts;

using

System.Web.UI.HtmlControls;

using

System.Da
ta.SqlClient;

///

<summary>

///

Summary description for DB

///

</summary>

public

class

DB

{


public

DB()


{



//



// TODO: Add constructor logic here



//


}


public

SqlDataReader

GetRegions(
string

criteria)


{


System.Data.SqlClient.
SqlConne
ction

sqlConnection1 =
new

System.Data.SqlClient.
SqlConnection
();



4


// to use Windows authentication method, uncomment the
following line and comment using the SQL authentication method


// sqlConnection1.ConnectionString = "Data
Source=H
UMBERCOMP;Initial Catalog=Northwind;Integrated Security=True";



// using SQL authentication method












sqlConnection1.ConnectionString =
"Data
Source=HUMBERCOMP;Initial Catalog=Northwind;User ID=sa;Password=humber
"
;



sqlConne
ction1.Open();


System.Data.SqlClient.
SqlCommand

cmd =
new

System.Data.SqlClient.
SqlCommand
();


cmd = sqlConnection1.CreateCommand();


cmd.CommandType =
CommandType
.Text;


cmd.CommandText =
"Select * from region where
RegionDesc
ription=@description"
;


cmd.Parameters.AddWithValue(
"@description"
,criteria);




System.Data.SqlClient.
SqlDataReader

dr =
cmd.ExecuteReader(
CommandBehavior
.CloseConnection);


return

dr;


}


}



Then we can use the ObjectDataSource a
nd the GridView as shown below:



<%
@

Page

Language
="C#"

AutoEventWireup
="true"

CodeFile
="usingDataSourceObject.aspx.cs"

Inherits
="_Default"

%>


<!
DOCTYPE

html

PUBLIC

"
-
//W3C//DTD XHTML 1.0 Transitional//EN"

"http://www.w3.org/TR/xhtml1/DTD/xhtml1
-
transit
ional.dtd">


<
html

xmlns
="http://www.w3.org/1999/xhtml"

>

<
head

runat
="server">


<
title
>
Using DataSourceObject
</
title
>

</
head
>

<
body
>


<
form

id
="form1"

runat
="server">


<
div
>


<
asp
:
ObjectDataSource

ID
="ObjectDataSource1"

runat
="server"

Sele
ctMethod
="GetRegions"


TypeName
="DB">


<
SelectParameters
>


<
asp
:
SessionParameter

Name
="criteria"

SessionField
="criteria"

Type
="String"

/>


</
SelectParameters
>


</
asp
:
ObjectDataSource
>




</
div
>


5



<
asp
:
GridView

ID
="GridView1"

runat
="server"

DataSourceID
="ObjectDataSource1">


</
asp
:
GridView
>


</
form
>

</
body
>

</
html
>




Since we have specified that the parameter source is a session, we need to add
the value to the session in the page
load as shown below:


using

System;

using

System.Data;

using

System.Configuration;

using

System.Web;

using

System.Web.Security;

using

System.Web.UI;

using

System.Web.UI.WebControls;

using

System.Web.UI.WebControls.WebParts;

using

System.Web.UI.HtmlControls
;


public

partial

class

_Default

: System.Web.UI.
Page


{


protected

void

Page_Load(
object

sender,
EventArgs

e)


{


Session.Add(
"criteria"
,
"Eastern"
);


}

}





Ex: Editable Grid View bound to an ObjectDataSource


This example shows how to
insert, update, delete records from a GridView. We
will use a DetailsView to provide single record navigation mechanism and also to
insert new records.
Following is the
code for the
business object:


using

System;

using

System.Data;

using

System.Configurat
ion;

using

System.Web;

using

System.Web.Security;

using

System.Web.UI;

using

System.Web.UI.WebControls;

using

System.Web.UI.WebControls.WebParts;

using

System.Web.UI.HtmlControls;

using

System.Data.SqlClient;

///

<summary>

///

Summary description for DB

//
/

</summary>

public

class

DB


6

{


public

DB()


{



//



// TODO: Add constructor logic here



//


}


public

SqlDataReader

GetRegions()

{

System.Data.SqlClient.
SqlConnection

sqlConnection1 =
new

System.Data.SqlClient.
SqlConnection
();



// to use Wi
ndows authentication method, uncomment the
following line and comment using the SQL authentication method


// sqlConnection1.ConnectionString = "Data
Source=HUMBERCOMP;Initial Catalog=Northwind;Integrated Security=True";




/
/ using SQL authentication method












sqlConnection1.ConnectionString =
"Data
Source=HUMBERCOMP;Initial Catalog=Northwind;User ID=sa;Password=humber
"
;



sqlConnection1.Open();


System.Data.SqlClient.
SqlCommand

cmd =
new

System.
Data.SqlClient.
SqlCommand
();


cmd = sqlConnection1.CreateCommand();


cmd.CommandType =
CommandType
.Text;


cmd.CommandText =
"Select * from region"
;


System.Data.SqlClient.
SqlDataReader

dr =
cmd.ExecuteReader(
CommandBehavior
.Clos
eConnection);


return

dr;

}


public

SqlDataReader

GetRegions(
string

criteria)


{


System.Data.SqlClient.
SqlConnection

sqlConnection1 =
new

System.Data.SqlClient.
SqlConnection
();



// to use Windows authentication method, uncomme
nt the
following line and comment using the SQL authentication method


// sqlConnection1.ConnectionString = "Data
Source=HUMBERCOMP;Initial Catalog=Northwind;Integrated Security=True";



// using SQL authentication method













sqlConnection1.ConnectionString =
"Data
Source=HUMBERCOMP;Initial Catalog=Northwind;User ID=sa;Password=humber
"
;



sqlConnection1.Open();


System.Data.SqlClient.
SqlCommand

cmd =
new

System.Data.SqlClient.
SqlCommand
();


cmd = sqlConne
ction1.CreateCommand();


cmd.CommandType =
CommandType
.Text;


cmd.CommandText =
"Select * from region where
RegionDescription=@description"
;


cmd.Parameters.AddWithValue(
"@description"
,criteria);


7




System.Data.SqlClient.
SqlData
Reader

dr =
cmd.ExecuteReader(
CommandBehavior
.CloseConnection);


return

dr;


}



public

SqlDataReader

GetRegions(
int

RegionId)


{


System.Data.SqlClient.
SqlConnection

sqlConnection1 =
new

System.Data.SqlClient.
SqlConnection
();




// to use Windows authentication method, uncomment the
following line and comment using the SQL authentication method


// sqlConnection1.ConnectionString = "Data
Source=HUMBERCOMP;Initial Catalog=Northwind;Integrated Security=True";



// using SQL authentication method












sqlConnection1.ConnectionString =
"Data
Source=HUMBERCOMP;Initial Catalog=Northwind;User ID=sa;Password=humber
"
;



sqlConnection1.Open();


System.Data.SqlClient.
SqlCommand

cmd =
new

System
.Data.SqlClient.
SqlCommand
();


cmd = sqlConnection1.CreateCommand();


cmd.CommandType =
CommandType
.Text;


cmd.CommandText =
"Select * from region where RegionId=@id"
;


cmd.Parameters.AddWithValue(
"@id"
, RegionId);




Sys
tem.Data.SqlClient.
SqlDataReader

dr =
cmd.ExecuteReader(
CommandBehavior
.CloseConnection);


return

dr;


}




public

void

UpdateRegion(
string

RegionDescription,
int

RegionId)


{


System.Data.SqlClient.
SqlConnection

sqlConnection1 =
new

System.Data.SqlClient.
SqlConnection
();



// to use Windows authentication method, uncomment the
following line and comment using the SQL authentication method


// sqlConnection1.ConnectionString = "Data
Source=HUMBERCOMP;Initial Catalog=N
orthwind;Integrated Security=True";



// using SQL authentication method












sqlConnection1.ConnectionString =
"Data
Source=HUMBERCOMP;Initial Catalog=Northwind;User ID=sa;Password=humber
"
;



sqlConnection1.Open();


Syst
em.Data.SqlClient.
SqlCommand

cmd =
new

System.Data.SqlClient.
SqlCommand
();


8


cmd = sqlConnection1.CreateCommand();


cmd.CommandType =
CommandType
.Text;


cmd.CommandText =
"update region set
RegionDescription=@description where RegionID=
@id"
;




cmd.Parameters.AddWithValue(
"@description"
, RegionDescription);


cmd.Parameters.AddWithValue(
"@id"
, RegionId);




cmd.ExecuteNonQuery();


sqlConnection1.Close();


}



public

void

DeleteRegion(
int

RegionId)


{


System.Data.SqlClient.
SqlConnection

sqlConnection1 =
new

System.Data.SqlClient.
SqlConnection
();



// to use Windows authentication method, uncomment the
following line and comment using the SQL authentication method


// sqlC
onnection1.ConnectionString = "Data
Source=HUMBERCOMP;Initial Catalog=Northwind;Integrated Security=True";



// using SQL authentication method












sqlConnection1.ConnectionString =
"Data
Source=HUMBERCOMP;Initial Catalog=Northwind;Use
r ID=sa;Password=humber
"
;



sqlConnection1.Open();


System.Data.SqlClient.
SqlCommand

cmd =
new

System.Data.SqlClient.
SqlCommand
();


cmd = sqlConnection1.CreateCommand();


cmd.CommandType =
CommandType
.Text;


cmd.CommandT
ext =
"delete from region where RegionID=@id"
;



cmd.Parameters.AddWithValue(
"@id"
, RegionId);




cmd.ExecuteNonQuery();


sqlConnection1.Close();


}



public

void

AddRegion(
int

RegionId,
string

RegionDescription)


{


S
ystem.Data.SqlClient.
SqlConnection

sqlConnection1 =
new

System.Data.SqlClient.
SqlConnection
();



// to use Windows authentication method, uncomment the
following line and comment using the SQL authentication method


// sqlConnection1.Conn
ectionString = "Data
Source=HUMBERCOMP;Initial Catalog=Northwind;Integrated Security=True";



// using SQL authentication method












9


sqlConnection1.ConnectionString =
"Data
Source=HUMBERCOMP;Initial Catalog=Northwind;User ID=sa;Passwor
d=humber
"
;



sqlConnection1.Open();


System.Data.SqlClient.
SqlCommand

cmd =
new

System.Data.SqlClient.
SqlCommand
();


cmd = sqlConnection1.CreateCommand();


cmd.CommandType =
CommandType
.Text;


cmd.CommandText =
"insert i
nto region values
(@id,@description)"
;



cmd.Parameters.AddWithValue(
"@id"
, RegionId);


cmd.Parameters.AddWithValue(
"@description"
, RegionDescription);




cmd.ExecuteNonQuery();


sqlConnection1.Close();


}





}




Below is t
he user interface
:




<%
@

Page

Language
="C#"

AutoEventWireup
="true"

CodeFile
="EditableGridView.aspx.cs"

Inherits
="EditableGridView"

%>


<!
DOCTYPE

html

PUBLIC

"
-
//W3C//DTD XHTML 1.0 Transitional//EN"

"http://www.w3.org/TR/xhtml1/DTD/xhtml1
-
transitional.dtd"
>


<
html

xmlns
="http://www.w3.org/1999/xhtml"

>

<
head

runat
="server">


<
title
>
Editable GridView
</
title
>

</
head
>

<
body
>


<
form

id
="form1"

runat
="server">


<
div
>


<
asp
:
ObjectDataSource

ID
="ObjectDataSource1"

runat
="server"


Select
Method
="GetRegions"

TypeName
="DB">






</
asp
:
ObjectDataSource
>




</
div
>


<
asp
:
GridView

ID
="GridView1"

runat
="server"

DataSourceID
="ObjectDataSource1"

AutoGenerateColumns
="false"

AutoGenerateDeleteButton
="True"

AutoG
enerateEditButton
="True"

DataKeyNames
="RegionId"

AutoGenerateSelectButton
="True">


10


<
Columns
>


<
asp
:
BoundField

DataField
="RegionDescription"

HeaderText
="Region Desoncription"

SortExpression
="RegionDescription"

/>



</
Columns
>


<
SelectedRowStyle

ForeColor
="#0000C0"

/>



</
asp
:
GridView
>


<
asp
:
DetailsView

ID
="DetailsView1"

runat
="server"

DataSourceID
="ObjectDataSource2"


Height
="50px"

Width
="125px"

style
="left: 327px; position:
absol
ute; top: 40px">


<
Fields
>


<
asp
:
CommandField

ShowInsertButton
="True"

/>


</
Fields
>


</
asp
:
DetailsView
>


<
asp
:
ObjectDataSource

ID
="ObjectDataSource2"

runat
="server"

InsertMethod
="AddRegion"


Sel
ectMethod
="GetRegions"

TypeName
="DB">


<
SelectParameters
>


<
asp
:
ControlParameter

ControlID
="GridView1"

DefaultValue
="1"

Name
="RegionId"

PropertyName
="SelectedValue"


Type
="Int32"

/>


</
SelectParameter
s
>


<
InsertParameters
>


<
asp
:
Parameter

Name
="RegionId"

Type
="Int32"

/>


<
asp
:
Parameter

Name
="RegionDescription"

Type
="String"

/>


</
InsertParameters
>


</
asp
:
ObjectDataSource
>


&nbsp;&nbsp;





</
form
>

</
body
>

</
html
>