ASP.NET & ADO.NET

acceptableseashoreSecurity

Nov 5, 2013 (3 years and 7 months ago)

169 views

AUC Technologies

AUC Technologies

Projects Consulting, Development, Mentoring, and Training Company

ASP.NET & ADO.NET

Presented By :

Muhammad Atif Hussain

Head of I.T. (Takaful Pakistan Limited)

Technologies Consultant (AUC Technologies)


MCS(KU)

MSCS(SZABIST)

MCP

MCAD

MCSD

MCTS (Windows, Web, Distributed Applications)

MCPD (Enterprise Applications)

MCT(Microsoft Certified Trainer)



AUC Technologies

AUC Technologies

Projects Consulting, Development, Mentoring, and Training Company


Introduce Microsoft
®

ADO.NET


Show the evolution of ADO to ADO.NET


Introduce the primary components of ADO.NET



Agenda

AUC Technologies

AUC Technologies

Projects Consulting, Development, Mentoring, and Training Company


Differences Between ADO and ADO.NET


Benefits of ADO.NET


ADO.NET Core Concepts and Architecture


The ADO.NET Object Model


The
DataSet

and Data Views


Managed Providers

Contents

AUC Technologies

AUC Technologies

Projects Consulting, Development, Mentoring, and Training Company

Microsoft .NET Framework

Common Language Runtime

Base Classes

Web Services

User Interface

Data and XML

ADO.NET

XML

...

...

ADO.NET and the .NET Framework

AUC Technologies

AUC Technologies

Projects Consulting, Development, Mentoring, and Training Company


ADO


Designed for connected access


Tied to the physical data model


The
RecordSet
is the central data container


RecordSet

is one (1) table that contains all the data


Retrieving data from > 1 table or source requires a database JOIN


Data is “flattened”: lose relationships; navigation is sequential


Data types are bound to COM/COM+ data types


Data sharing via COM marshalling


Problems marshalling through firewalls (DCOM, binary)

ADO v/s ADO.NET

AUC Technologies

AUC Technologies

Projects Consulting, Development, Mentoring, and Training Company


ADO.NET


Designed for disconnected access


Can model data logically!


The
DataSet

replaces the
RecordSet


DataSet

can contain multiple tables


Retrieving data from > 1 table or source does not require a JOIN


Relationships are preserved: navigation is relational


Data types are only bound to XML schema


No data type conversions required


XML, like HTML, is plaintext: “Firewall friendly”

ADO v/s ADO.NET

AUC Technologies

AUC Technologies

Projects Consulting, Development, Mentoring, and Training Company


Interoperability through use of XML (more later!)


Open standard for data that describes itself


Human readable and decipherable text


Used internally but accessible externally


Can use XML to read and write and move data


Scalability through the disconnected DataSet


Connections are not maintained for long periods


Database locking does not occur


Locking support with ServiceComponents


Optimistic locking otherwise


Works the way the Web works: “Hit and Run!”


Maintainability


Separation of data logic and user interface

Benefits of ADO.NET

AUC Technologies

AUC Technologies

Projects Consulting, Development, Mentoring, and Training Company


Typed programming

a programming style


Uses end
-
user words: Easier to read and write


Statement completion in Microsoft Visual Studio.NET


Safer: Provides compile
-
time checking


Examples:


Untyped:

Table("Customer")("Jones").Column(

Balance
µ
)


Typed:

myDataSet.Customer("Jones").Balance


Wizard support


Generates queries for you


Graphical way to select data you want to work with


XML Designer (for creating
DataSets
)

Visual Studio Enhancement

AUC Technologies

AUC Technologies

Projects Consulting, Development, Mentoring, and Training Company


The ADO.NET Object Model


Objects of System.Data


.NET data providers


ADO.NET namespace hierarchy


Organizes the object model


Includes:



System.Data


System.Data.OleDb


System.Data.Common


System.Data.SqlClient


System.Data.SqlTypes


System.Data.OracleClient

Core Concept and Architecture

AUC Technologies

AUC Technologies

Projects Consulting, Development, Mentoring, and Training Company

ADO.NET

System.Data

.OleDb

.SqlClient

.OracleClient

.Common

ADO.NET related Namespaces

AUC Technologies

AUC Technologies

Projects Consulting, Development, Mentoring, and Training Company


Contains the basis and bulk of ADO.NET


Data
-
centric namespace


Provides the means to work on and with your data!


Classes and methods to manipulate your data


Ability to create views of your data


Means to logically represent your data


Enables the use of XML to view, share, and store data

System.Data

Namespace

AUC Technologies

AUC Technologies

Projects Consulting, Development, Mentoring, and Training Company


Contains the “main” classes of ADO.NET


In
-
memory cache of data


In
-
memory cache of a database table


Used to manipulate a row in a
DataTable


Used to define the columns in a
DataTable


Used to relate 2
DataTable
s to each other


Used to create views on
DataSets

System.Data

DataTable

DataRow

DataRelation

DataColumn

DataViewManager

DataSet

Introducing the Objects

AUC Technologies

AUC Technologies

Projects Consulting, Development, Mentoring, and Training Company

DataSet

Tables

DataTable

Relations

DataRelation

DataRelation

DataRow(s)

DataColumn

Constraint(s)

DataTable

DataTable

DataView

DataViewManager

Putting the Object Together

AUC Technologies

AUC Technologies

Projects Consulting, Development, Mentoring, and Training Company


An in
-
memory cache of data from a data source


Common way to represent and manipulate data


Universal data container


Not just for use with databases


Logical
or

physical representation of data


Designed to be disconnected from the data source


Connect, execute query, disconnect


Can use XML


To read and write data


To read and write XMLSchema

Working Data
-

The Dataset

AUC Technologies

AUC Technologies

Projects Consulting, Development, Mentoring, and Training Company


Collections are used to add & remove tables & relations


Properties of Interest:


Tables:
Returns the collection of
DataTable

objects


Relations:
Returns the collection of
DataRelations


Namespace:
Gets or sets the namespace of the
DataSet


Using Properties Samples:


myDataSet.Tables.Add( myTable );

²
myDataTableCollection = myDataSet.Tables

Properties and Methods of Interest

AUC Technologies

AUC Technologies

Projects Consulting, Development, Mentoring, and Training Company

Universal Data Container


DataSet: It’s not just for Databases


Al About Data!

AUC Technologies

AUC Technologies

Projects Consulting, Development, Mentoring, and Training Company


May be mapped to a physical table in the data source


Can be related to one another through
DataRelation
s


Optimistic concurrency or locking
-

model


Properties of Interest:


Columns:
Returns ColumnsCollection of DataColumns


Rows:
Returns DataRow objects as a RowsCollection


ParentRelations:
Returns the RelationsCollection


Constraints:
Returns the table’s ConstraintsCollection


DataSet:
Returns the DataSet of the DataTable



PrimaryKey:
Gets the DataColumns that make up the table’s primary key

The
DataTable

AUC Technologies

AUC Technologies

Projects Consulting, Development, Mentoring, and Training Company


Create a
DataTable

and add it to a
DataSet

DataSet ds = new DataSet();



// Create DataTable object: “Customers”.

DataTable dt= new DataTable( “Customers” );



// Create and add columns to the table


// 1. Explicitly create and Add a DataColumn

DataColumn dc;

dc = new DataColumn( “CustID”, Type.GetType("System.Int16"));

dt.Columns.Add( dc );



// 2. Implicitly Create and Add columns (DataColumn).

dt.Columns.Add( “First_Name”,Type.GetType("System String”));

dt.Columns.Add( “Last_Name”, Type.GetType("System String”));



// Add the DataTable object to the DataSet

ds.Tables.Add( dt );

System.Data

--

Dataset and
DataTable

AUC Technologies

AUC Technologies

Projects Consulting, Development, Mentoring, and Training Company


Used to create logical relations between your data


Create relations between two (2)
DataTable
objects


Requires a
DataColumn

object from each
DataTable


The
DataType

of both
DataColumns

must be the same


Cannot relate a
Int32 DataColumn

and a
String DataColumn


The relation is named (by you!)


DataRelation dr=new DataRelation(

myRelation

,...)


Makes relational navigation possible


RelationsCollection

used to hold/group them


Accessed through the
DataSet
’s
Relations

property

Relating Data
-

The
DataRelation

AUC Technologies

AUC Technologies

Projects Consulting, Development, Mentoring, and Training Company


// Building on the DataTable example earlier...


// Get the DataTable DataColumns we want to relate...

DataColumn parentCol, childCol;

parentCol= DataSet.Tables["Customers"].Columns["CustID"];

childCol = DataSet.Tables["Orders“].Columns["CustID"];



// Create DataRelation with the name “CustomerOrders”...

DataRelation dr = new DataRelation("CustomersOrders",


parentCol,


childCol);



// Add the relation to the DataSet...

ds.Relations.Add( dr );

Creating Relations with
DataRelation

AUC Technologies

AUC Technologies

Projects Consulting, Development, Mentoring, and Training Company


DataSet

can read/write XML for its data and/or schema


You can create or modify data in a
DataSet

using XML


You can create or modify the
DataSet
s schema using XML


XML
-
related
DataSet

methods for reading:


ReadXml
: Reads an XML schema and data into the
DataSet


ReadXmlSchema
: Reads an XML schema into the
DataSet


And for writing:


WriteXml, WriteXmlSchema


GetXml, GetXmlSchema


Namespace
property: sets the namespace for serialization


Full support for SQL Server
-
style DiffGrams

XML and the Dataset

AUC Technologies

AUC Technologies

Projects Consulting, Development, Mentoring, and Training Company


// Code for creating the DataSet mds and loading the


// DataSet from a data source not shown.


String oFile = “C:
\
\
My_ADO.NET
\
\
myXmlOutput.xsd”;

String iFile = “C:
\
\
My_ADO.NET
\
\
myXmlInput.xsd”;




// Write the DataSet’s XMLSchema to an XML Document

mds.WriteXmlSchema( oFile );



// Read/Upload XML Data into the DataSet

mds.ReadXml( iFile);



// modify the data


// ...



// Write the existing Data to an XML Document

mds.WriteXml( "C:
\
\
My_ADO.NET
\
\
myXmlData.txt",


XmlWriteMode.DiffGram);


Methods of Reading and Writing XML

AUC Technologies

AUC Technologies

Projects Consulting, Development, Mentoring, and Training Company

DataViewManager

DataViewSettings

DataSet

Tables

DataTable

Relations

DataRelation

DataRelation

DataRow(s)

DataColumn

Constraint(s)

DataTable

DataTable

DataView

DataViewSetting

DataViewSetting

Dataset,
DataRelation
,
DataView


AUC Technologies

AUC Technologies

Projects Consulting, Development, Mentoring, and Training Company


Create multiple views on
DataTable

objects


Bindable to user interface controls


Properties of Interest:


Table
: Retrieves or sets the associated
DataTable


Sort
: Gets or sets the table’s sort columns and sort order


RowFilter
: Gets or sets the expression used to filter rows


RowStateFilter
: Gets or sets the row state filter


None
,
Unchanged
,
New
,
Deleted
,
ModifiedCurrent
, and others

Viewing Data
-

The
DataView

AUC Technologies

AUC Technologies

Projects Consulting, Development, Mentoring, and Training Company


// Code for myTable “Customers” with “Name” column not shown

DataView view1 = new DataView( myTable );

DataView view2 = new DataView( myTable );



// Creates Ascending view of Customers by “Name”

view1.Sort = “Name ASC”;



// Set the view to show only modified (original) rows

view2.RowStateFilter= DataViewRowState.ModifiedOriginal;



// Bind to UI element(s)...

DataGrid myGrid = new DataGrid();

myGrid.SetDataBinding( view1, “Customer”);



//...

Creating
DataView

by Example

AUC Technologies

AUC Technologies

Projects Consulting, Development, Mentoring, and Training Company


Similar to a
DataView

but
DataSet

oriented


Used to create multiple views on a
DataSet


Ability to automatically set filters on the tables


Properties of Interest:


DataViewSettings
: Gets the
DataView

for on each
DataTable


DataSet
: Gets or sets the
DataSet

to be viewed


CreateDataView
method


Creates a
DataView
on a
DataTable


Viewing More Data
-

DataViewManager

AUC Technologies

AUC Technologies

Projects Consulting, Development, Mentoring, and Training Company


// Create the DataViewManager & views...

DataViewManager dvMgr = new DataViewManager( myDS );

dvMgr.CreateDataView( ds.Tables[“Orders"] );

dvMgr.DataViewSettings[“Orders"].Sort = “CustID ASC";


dvMgr.CreateDataView( ds.Tables[“Customers"] );

dvMgr.DataViewSettings[“Customers"].Sort = “Name DESC";



// Bind to a UI elements/controls...

dataGrid1.DataSource = viewMgr;

dataGrid1.DataMember = "Table1";


dataGrid2.DataSource = viewMgr;

dataGrid2.DataMember = "Table2";



// Update the control with the data...

dataGrid1.Update();

dataGrid2.Update();

DataViewManager

By Example

AUC Technologies

AUC Technologies

Projects Consulting, Development, Mentoring, and Training Company


A collection of classes for accessing data sources:


Microsoft SQL Server


2000
-
2005, SQL Server 7, and MSDE


Any OLE Database (OLE DB) providers


Including: Oracle, JET, and SQL OLE DB Providers


Establish connection between
DataSet
s and data stores


Two .NET data providers:


ADO: via the
System.Data.OleDb

namespace


SQL Server: via the
System.Data.SqlClient

namespace


System.Data.OleDb

is
the
.NET data provider

The (ADO).NET Data Provides

AUC Technologies

AUC Technologies

Projects Consulting, Development, Mentoring, and Training Company

System.Data

.OleDb

.SqlClient

OleDbCommand

OleDbConnection

OleDbDataReader

OleDbDataAdapter

SqlCommand

SqlConnection

SqlDataReader

SqlDataAdapter

.Common


Contains classes
shared by both

.NET Data Providers Hierarchy

AUC Technologies

AUC Technologies

Projects Consulting, Development, Mentoring, and Training Company


Represent a unique session with a data source


Create, open, close a connection to a data source


Functionality and methods to perform transactions


OleDbConnection
example:

String conStr="Provider=Microsoft.Jet.OLEDB.4.0;" +


"Data Source=NWIND_RW.MDB";

OleDbConnection aConn = new OleDbConnection(conStr);

aConn.Open();


// Execute Queries using OleDbDataAdapter Class

aConn.Close();

OleDBConnection

and
SqlConnection

AUC Technologies

AUC Technologies

Projects Consulting, Development, Mentoring, and Training Company


Data
-
driven applications


Fetch data


Format data in a programmer
-
friendly way


Format data in a user
-
friendly way


Forward data to the browser



Submit data


Catch server
-
generated values


Refresh the view for the user

ADO.NET and ASP.NET

AUC Technologies

AUC Technologies

Projects Consulting, Development, Mentoring, and Training Company


Data
-
Binding


DataGrid and other controls


Sorting and Filtering


ADO.NET views


Aggregates


ADO.NET in
-
memory objects (DataColumn, DataTable, DataRelation)


User Interface


ASP.NET controls and state maintenance

Technical Aspects

AUC Technologies

AUC Technologies

Projects Consulting, Development, Mentoring, and Training Company


Automatic association between a data source and the user interface of a
control


Definition of a set of feasible sources


ADO.NET container objects can be associated with data
-
bound controls


Iterative and list
-
bound controls

Data
-
Bindings

AUC Technologies

AUC Technologies

Projects Consulting, Development, Mentoring, and Training Company


DataSource property


DataBind method


// data loading


DataTable __dataTable;


__dataTable = __dataHandler.Load();



// data binding


DDList.DataSource = __dataTable;


DDList.DataTextField = "Name";


DDList.DataValueField = "empID";


DDList.DataBind();

Building data to controls

AUC Technologies

AUC Technologies

Projects Consulting, Development, Mentoring, and Training Company


The DataGrid Control


Elements: header, footer, items, pager


Columns: bound, button, hyperlink, template, edit


Cell customization using templates and data
-
bound expressions


Paging and sorting


Store the value of the key field per each displayed row


Bindable

Grids of Data

AUC Technologies

AUC Technologies

Projects Consulting, Development, Mentoring, and Training Company


Can you afford caching?


The Cache object for data storage


Let the grid do the hard work


The output caching mechanism for pages


Do you need up
-
to
-
date information?


Minimize the bandwidth for SQL queries


Need sorting?


Do the hard work of pagination yourself


Let the grid do the annoying work

Pagination

AUC Technologies

AUC Technologies

Projects Consulting, Development, Mentoring, and Training Company

AUC Technologies

AUC Technologies

Projects Consulting, Development, Mentoring, and Training Company

Questions



?