System development with Java
Download
Report
Transcript System development with Java
ASP.NET
Rina Zviel-Girshin
Lecture 5
Overview
•
•
•
•
DataBase connection overview
DataSet
DataAdapter
DataReader vs. DataSet
Rina Zviel-Girshin @Paralex
2
ADO.NET classes
• ADO.NET classes are separated into 2 major
categories:
– datasource-specific and non-datasource-specific.
• Datasource-specific classes work with a specific
data provider:
• System.Data.SqlClient – Microsoft SQL Server 7.0 or higher
• System.Data.OleDb – OLE DB provider, such as Microsoft
Access.
• Non-datasource-specific (DataSet) work without
knowledge of data provider:
• XML file or result of a query.
– In memory representation of the data.
Rina Zviel-Girshin @Paralex
3
System.Data.SqlClient
• System.Data.SqlClient
namespace includes 3
classes to perform data
connection and
manipulation:
– SqlConnection
– SqlCommand
– SqlDataReader
Rina Zviel-Girshin @Paralex
Database
SqlConnection
SqlCommand
SqlDataReader
4
SqlQueryBox.aspx Example
Write a query
Query result is
presented to the
TextBox
Rina Zviel-Girshin @Paralex
5
A more complex example
SqlQueryBox.aspx
<%@ Page Language="C#"%>
<%@ Import Namespace="System.Data.SqlClient" %>
<script runat=“server”>
void Button_Click(Object sender , EventArgs e)
{
int intField;
txtResults.Text = "";
SqlConnection con = new SqlConnection( "Server=localhost;uid=sa;
database=pubs" );
con.Open();
SqlCommand cmdSelect = new SqlCommand( txtQuery.Text, con );
Rina Zviel-Girshin @Paralex
6
SqlQueryBox.aspx (cont)
SqlDataReader dr = cmdSelect.ExecuteReader();
while (dr.Read())
{
for ( intField = 0 ; intField < dr.FieldCount; intField++)
txtResults.Text += dr[ intField ].ToString().PadRight( 15 );
txtResults.Text += "\n";
}
dr.Close();
con.Close();
}
</script>
No data binding –
writing directly to
the textbox
Rina Zviel-Girshin @Paralex
7
SqlQueryBox.aspx (cont)
<html><head><title>SqlQueryBox.aspx</title></head>
<body>
<form Runat="Server">
<asp:TextBox ID="txtQuery" TextMode="MultiLine" Columns="80"
Rows="4" Runat="Server" /><br>
<asp:Button Text="Execute Query" OnClick="Button_Click"
Runat="Server" />
<p>
<asp:TextBox ID="txtResults“ TextMode="MultiLine" Columns="80"
Rows="15" Wrap="False" ReadOnly="True" Runat="Server" />
</form>
</body></html>
Rina Zviel-Girshin @Paralex
8
SqlQueryBox.aspx Output
Rina Zviel-Girshin @Paralex
9
DataReader
• All DataReaders (SqlDataReader,
OleDbDataReader) are connected to the
database.
– Reads only one record at a time.
– Does not remember previous record. Forwardonly stream reading.
– Can’t perform sorting, filtering or counting of
the resulting set.
– A minimal server memory usage.
Rina Zviel-Girshin @Paralex
10
Query’s Result Storage
• After opening a connection and execution of the
command a result set or so-called record set is
returned in the form of data stream.
• This stream
– can be read by a DataReader object or
– can be pushed into a DataSet object
– DataReader – reads
– DataSet – holds.
Rina Zviel-Girshin @Paralex
11
DataSet class
• The DataSet class of .NET presents a set of data
separated and distinct from any data stores.
• You can think of the DataSet as an always
disconnected recordset that knows nothing about the
source or destination of the data it contains - nondatasource-specific entity.
• DataSet works with all models of data storage:
– flat, relational, and hierarchical (since it does not have any
'knowledge' of the source of its data).
• To use DataSets the System.Data namespace should
be imported:
<%@ Import Namespace="System.Data" %>
Rina Zviel-Girshin @Paralex
12
DataAdapter
• A DataAdapter is the object that connects to the database or file
to fill the DataSet.
• Therefore it is a provider specific:
– SqlDataAdapter,OleDbDataAdapter.
• DataAdapter object works as a bridge between the DataSet and
the source of data
– You can think of the DataAdapter as a bridge between the connected and
disconnected objects
• DataAdapter is a link between the DataSet and the Database
– Used to fill DataSets and update the database.
DataAdapter
DataSet
Data
Source
Rina Zviel-Girshin @Paralex
13
SqlDataAdapter
• Represents a set of data commands and a database
connection that are used
– to fill the DataSet and
– update a SQL Server database.
• SqlDataAdapter Fill method adds or refreshes
rows in the DataSet to match those in the data
source
– using the DataSet name and creates a DataTable named
"Table“ .
Rina Zviel-Girshin @Paralex
14
Example
<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<script runat=server>
void Page_Load(Object sender , EventArgs e)
{
DataSet ds = new DataSet();
SqlConnection con = new SqlConnection( "Server=localhost;uid=sa;
database=pubs" );
SqlDataAdapter da = new SqlDataAdapter( "Select au_lname From
authors where au_lname like '%D%'", con);
da.Fill( ds, “DlastName" ); //name of DataMember
// Bind dataset to the DataGrid
dgrdProducts.DataSource = ds;
dgrdProducts.DataBind();
}</script>
Rina Zviel-Girshin @Paralex
15
Example
<html>
<head><title>DataSetwithDataGrid.aspx</title></head>
<body>
<asp:DataGrid ID="dgrdProducts" Runat="Server" />
</body></html>
Rina Zviel-Girshin @Paralex
16
Fill() method
• Fill() method of DataAdapter is used to populate a
DataSet object with the results from the query:
da.Fill( ds, “DlastName" );
• The Fill method implicitly opens the Connection that
the DataAdapter is connected to and closes it after
the execution.
SqlDataAdapter da = new SqlDataAdapter( SQLQuery, con);
• Fill method adds or refreshes rows in the DataSet by
using the DataSet name (ds) and creates or updates a
DataTable named (DlastName) with the result.
Rina Zviel-Girshin @Paralex
17
DataSet Structure
• DataSet object is very complex.
• It represents an in-memory cache of data with a database-like
structure (disconnected from DB).
• Inside a DataSet object there are
– tables, columns, constraints, relationships, views and more.
• A specific DataSet object contains
– all of the tables (DataTable objects) drawn from the database
– a DataRows collection (of DataRow objects) and a DataColumns
collection (of DataColumn objects) is provided for each table for
manipulation with table data
– also DataViews collection (of DataView objects) is provided and
gives various ways to arrange the table information for display.
Rina Zviel-Girshin @Paralex
18
The general structure of a DataSet is shown below:
Rina Zviel-Girshin @Paralex
19
DataAdapter and DataSet
System.Data.SqlClient
System.Data
SqlConnection
DataSet
SqlDataAdapter
SqlCommand
SqlDataReader
SqlParameters
SqlParameter
Rina Zviel-Girshin @Paralex
20
The DataAdapter Commands
• To perform data retrieval or storage from/to DB
– You can use DataAdapter constructor with a command
and connection object.
• SqlDataAdapter da = new SqlDataAdapter( "Select au_lname From authors
where au_lname like '%D%'", con);
– Or you can use DataAdapter commands
• Contains four command objects
–
–
–
–
SelectCommand
InsertCommand
UpdateCommand
DeleteCommand
Rina Zviel-Girshin @Paralex
21
Example
• Example:
SqlConnection myConnection = new SqlConnection(connection);
SqlDataAdapter myAdapter = new SqlDataAdapter();
myAdapter.InsertCommand = new SqlCommand("Insert INTO Table
(UserID, PSW) VALUES (‘rina’,’1234’)", myConnection);
Or
SqlConnection conn = new SqlConnection(connection);
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = new SqlCommand("Select au_lname From
authors where au_lname like '%D%'", con, conn);
adapter.Fill(dataset);
Rina Zviel-Girshin @Paralex
22
DataAdapter connection to DB
• The DataAdapter connects back to the database to
update the data there if any data manipulations
were performed on DataSet object that held the
data.
• The DataAdapter provides a bridge not only to
retrieve data but also to save data between a
DataSet and its source data store.
• It accomplishes this by means of requests to the
appropriate SQL commands made against the data
store.
Rina Zviel-Girshin @Paralex
23
Data Updates
• The DataAdapter object uses commands to
update the data source after changes have been
made to the DataSet.
• Using the Fill method of the DataAdapter calls the
SELECT command.
• Using the Update method calls the INSERT,
UPDATE or DELETE command for each changed
row.
• Only changed data is sent back to DB.
Rina Zviel-Girshin @Paralex
24
Filling and Updating
•
The cycle:
– DataAdapter.Fill(DataSet)
– user modifies the data in DataSet
– DataAdapter.Update(DataSet)
•
When you update, by calling an Update method , the
DataAdapter takes modified rows from the DataSet
one by one:
– If added, calls configured INSERT command
– If modified, calls configured UPDATE command
– If deleted, calls configured DELETE command
Rina Zviel-Girshin @Paralex
25
DataSet
• DataSet object does not interact directly with the source of
data.
– This allows the developer to work with a programming model that
is always consistent, regardless of where the source data resides.
• As changes are made to the DataSet they can be tracked
and verified before updating the source data.
• The GetChanges method of the DataSet object actually
creates a second DatSet that contains only the changes to
the data. This DataSet is then used by a DataAdapter (or
other objects) to update the original data source.
• One DataSet can be used/bound by several controls.
Rina Zviel-Girshin @Paralex
26
DataReader vs. DataSet
• DataReader is connected to data source
– No caching, just retrieve and display, record
after record.
• DataSet is disconnected from data source
– held in server memory, holds the whole data,
one set for several controls.
Rina Zviel-Girshin @Paralex
27
DataReader vs. DataSet Examples
• The DataSet's disconnected nature allows it to be transformed
into XML and sent over the wire via HTTP if appropriate.
• A connected DataReader cannot be serialized and thus cannot
be passed between physical-tier boundaries where only string
(XML) data can go.
• A connected DataReader is associated with a specific data
source therefore cannot be created, filled or traversed without
a connection to the data source.
• A DataSet can be created manually without a connection to
the source.
– In a situation such as an online shopping cart in which a custom data
store is required, a DataSet could be created manually and its rows
added.
Rina Zviel-Girshin @Paralex
28
DataReader vs. DataSet Examples
• Another good use of the DataSet is the situation in which data
must be retrieved and a complex action performed on each
row.
– A DataSet could be used to store the rowset and some code could loop
through the DataSet and perform a complex action.
• DataReaders are a good choice when an application has to be
sensitive to changes in the underlying database (not cached).
• Binding a DataReader to a DropDownList or even a read-only
DataGrid in ASP.NET works well as the data can be retrieved
and displayed in the list but does not need to be persisted for
editing. DataSets are ideal if data needs to be edited, sorted,
filtered or searched.
Rina Zviel-Girshin @Paralex
29
DataSet Memory Usage
• A DataSet object is stored in the server memory.
• Using viewstate an additional copy is in the
memory.
– If some DataSet holds 100.000 records and we have
100 clients then 100X100.000 records in stored in the
server memory.
A lot of
memory
Rina Zviel-Girshin @Paralex
30
Any Questions?
Rina Zviel-Girshin
31