Transcript VB .NET Revisit - Vocational Training Council

ADO .NET
1
.NET Framework Data Namespaces
• System.Data
– Base set of classes and interfaces for ADO .NET
• System.Data.Common
– Classes shared by the .NET Data Providers
• System.Data.OleDb
– Classes that make up the .NET Data Provider for OLEDB
• System.Data.SqlClient
– Classes that make up the .NET Data Provider for SQL Server
• System.Data.SqlTypes
– Classes that represent the SQL data types
• System.XML
– Classes for working with XML data
2
.NET Data Providers (1)
• A Data Provider supports data access through its own
specific implementation that supports a common set of
interfaces
• Two standard .NET Data Providers
• SQL provider – for MS SQL Server 7.0 or later
• OLE DB provider – for OLE DB support, e.g. Oracle,
OLE DB .NET Data Provider as a wrapper around an
OLE DB provider
• Each .NET Data Provider is implemented as a group of
types that reside in System.Data.SqlClient and
System.Data.OleDb namespaces
3
.NET Data Providers (2)
SQL
.NET Data Provider
Client
OLE DB
.NET Data Provider
SQL
Server
OLE DB
Provider
ODBC
Provider
Other
DBMS
Other
DBMS
Managed Code
4
.NET Data Providers (3)
• Some fundamental classes supported by any .NET Data
Provider
• Connection – allows establishing and releasing
connections, and to begin transactions
• Command – allows storing and executing a command
(SQL query, stored procedure)
• DataReader – provides direct, sequential (forward-only),
read-only access to data in a database
• DataAdapter – built on DataReader, this class creates
and populates instances of the class DataSet. DataSets
allow more flexible access to data than using just
DataReader
5
.NET Data Providers (4)
• Clients can access data through a DataReader (straightforward onerow-at-a-time) or by using a DataSet (more complex requirements
such as ordering, filtering, sending results across a network etc.)
.NET Data Provider
Client
Connection
Command
Rows
DataReader
DBMS
DataSet
DataAdapter
6
Accessing Data with Reader
1.
Create a Connection object (of class SqlConnection or
OleDbConnection)
•
2.
Create a Command object (of class SqlCommand and
OleDbCommand) by Connection object’s CreateCommand
method
•
3.
4.
5.
Set CommandText property
Call the Open() method of the Connection object
Declare a DataReader (if need)
Executing the query by calling methods of the Command object
•
•
•
6.
7.
8.
set ConnectionString property
ExecuteReader(): returns a DataReader, can be accessed one row at a
time
ExecuteScalar(): returns a single value, e.g. result of SUM function
ExecuteNonQuery(): returns the number of rows affected
Process the result
Close the DataReader (if using ExecuteReader)
Close the Connection with the Close() method
7
AdoExample1
Sub Main()
Dim query As String = "SELECT StudentID, Name, Sex FROM Students"
Dim connStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=school.mdb"
Dim conn As OleDbConnection = New OleDbConnection(connStr)
Dim command As OleDbCommand = New OleDbCommand(query, conn)
Dim reader As OleDbDataReader
Try
conn.Open()
reader = command.ExecuteReader()
While reader.Read()
Console.WriteLine("ID: {0}, Name: {1}",
End While
Catch ex As OleDbException
Console.Out.WriteLine(ex.Message)
Finally
reader.Close()
conn.Close()
End Try
End Sub
' open connection
' execute the command
‘ process records
reader.GetString(0), reader.GetString(1))
8
Accessing Data with DataSets (1)
• A DataSet is an in-memory cache for data
• Disconnected: manipulate data without connecting the
database, allow you to move data across a network
• DataSets allow much more flexible access to data, can
examine data in an arbitrary way, scrolling back and forth
• Datasets are used in conjunction with DataAdapters.
DataAdapters populate DataSets with data from data
stores.
• Useful for combining data from different data sources,
and for data transfer across a network (as DataSets are
serializable)
9
Accessing Data with DataSets (2)
•Each DataSet can contain zero or
more DataTable objects.
•Each DataTable can contain the
result of some query
•A DataSet can also maintain
relationships among DataTables
using DataRelation objects
•Each DataSet has a schema,
describing the tables, columns,
data types
DataRelation
DataTable
DataTable
10
Creating and Using DataSets
1.
Create a Connection object
•
2.
Create a Command object
•
3.
7.
8.
Set CommandText property
Create a DataAdapter object (SqlDataAdapter,
OleDbDataAdapter)
•
4.
5.
6.
set ConnectionString property
Set the SelectCommand, InsertCommand, UpdateCommand,
DeleteComand property to the Command object created in last
step
Create a DataSet object
Call the Open() method of the Connection object
Use the Fill() method of the DataAdapter object to fill
the DataSet
Close the Connection with the Close() method
You may then update/delete rows in DataSet and use
the Update() method of the DataAdapter to update the
11
database
Sub Main()
Dim query As String = "SELECT StudentID, Name, Sex FROM Students"
Dim connStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=school.mdb"
Dim conn As OleDbConnection = New OleDbConnection(connStr)
Dim command As OleDbCommand = New OleDbCommand(query, conn)
Dim da As OleDbDataAdapter = New OleDbDataAdapter(command)
SelectCommand as well
Dim ds As DataSet = New DataSet
Dim table As DataTable
Dim row As DataRow
Dim rowIndex As Integer
Try
conn.Open()
da.Fill(ds, “Students”)
conn.Close()
‘ set the
AdoExample3
' open connection
‘ fill the dataset
‘ connection can be closed (disconnected)
table = ds.Tables.Item(“Students”) ‘ select the DataTable
For rowIndex = 0 To table.Rows.Count - 1
row = table.Rows.Item(rowIndex)
Console.Out.WriteLine("ID: {0}, Name: {1}", _
row("StudentID"), row("Name"))
Next
Catch ex As OleDbException
Console.Out.WriteLine(ex.Message)
End Try
End Sub
12
DataAdapter Properties
• SelectCommand
– contains a Command object that can be used to populate a
DataTable within DataSet
– Command object typically references a SQL SELECT statement
• InsertCommand
– to insert rows added to a DataTable into an underlying database
– Command object typically references a SQL INSERT statement
• UpdateCommand
– to update a database based on changes made to a DataTable
– Command object typically references a SQL UPDATE statement
• DeleteCommand
– to delete rows in a database based on deletions made to a
DataTable
– Command object typically references a SQL DELETE statement
13
DataAdapter Methods
• Fill
– Used to execute a query (in the
SelectCommand) and store the result in a
DataSet
– Da.Fill(Ds, “TableName”)
– Note that the connection can be closed once
the Fill method is done
• Update
– Used to modify data in the database based on
changes made to the DataTables
14
DataSet Contents
• Contents of a DataSet are grouped into
collections
• DataSet has a Tables property
– a collection of DataTable
– table = ds.Tables.Item(“Students”)
– table = ds.Tables.Item(0)
• DataTable has a Rows property
– a collection of DataRow
– row = table.Rows.Item(rowIndex)
– Column values can be obtained by row("StudentID") or row(0)
• Relations collection
15
Basic Objects
Dim query As String = "SELECT * FROM Students"
Dim connStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=school.mdb"
Dim conn As OleDbConnection = New OleDbConnection(connStr)
Dim command As OleDbCommand = New OleDbCommand(query, conn)
Dim da As OleDbDataAdapter = New OleDbDataAdapter(command)
' commandBuilder is used create the Commands automatically! Must be
for UPDATE
Dim autogen As New OleDbCommandBuilder(da)
16
Adding Data Using a DataSet
Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
Handles btnAdd.Click
Dim ds As DataSet = New DataSet
Dim table As DataTable
Dim row As DataRow
Try
da.Fill(ds, "Students") ' fill the dataset
table = ds.Tables.Item("Students") ' select the DataTable
row = table.NewRow()
row("StudentID") = "A90001"
row("Name") = "Chan Chan"
row("Sex") = "M"
row("DateOfBirth") = #10/31/2005#
row("PhoneNo") = "12345678"
row("Class") = "41111"
table.Rows.Add(row)
da.Update(ds, "Students")
Catch ex As OleDbException
Console.Out.WriteLine(ex.Message)
End Try
End Sub
17
Updating Data Using a DataSet
Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
Handles btnUpdate.Click
Dim ds As DataSet = New DataSet
Dim table As DataTable
Dim row As DataRow
Dim rowIndex As Integer
Try
da.Fill(ds, "Students") ' fill the dataset
table = ds.Tables.Item("Students") ' select the DataTable
For rowIndex = 0 To table.Rows.Count - 1
row = table.Rows.Item(rowIndex)
If row("StudentID") = "A90001" Then
row("Sex") = "F"
End If
Next
Catch ex As OleDbException
Console.Out.WriteLine(ex.Message)
End Try
da.Update(ds, "Students")
End Sub
18
Deleting Data Using a DataSet
Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
Handles btnDelete.Click
Dim ds As DataSet = New DataSet
Dim table As DataTable
Dim row As DataRow
Dim rowIndex As Integer
Try
da.Fill(ds, "Students") ' fill the dataset
table = ds.Tables.Item("Students") ' select the DataTable
For rowIndex = 0 To table.Rows.Count - 1
row = table.Rows.Item(rowIndex)
If row("StudentID") = "A90001" Then
row.Delete()
End If
Next
Catch ex As OleDbException
Console.Out.WriteLine(ex.Message)
End Try
da.Update(ds, "Students")
End Sub
19
AdoExample4
• Add/Update/Delete
20
Binding a DataSet to a Control
• Setup the connection and dataadapter
• Select the DataAdapter, generate the
DataSet
• Set the DataSource property of the
control
– e.g. datagridview1.DataSource =
myDataTable
• AdoExample5
21
Using SQL to Query Data (1)
‘ Assume connection object is setup already
Dim sql As String = “SELECT * FROM STUDENTS”
Try
conn.Open()
Dim da As New OleDbDataAdapter(sql, conn)
Dim ds As New DataSet
da.Fill(ds, "table")
DataGrid1.DataSource = ds
DataGrid1.DataMember = "table"
Catch e As Exception
MsgBox("Error in executing the following SQL statement:" & vbCrLf & vbCrLf & sql &
vbCrLf & vbCrLf & e.Message)
Finally
conn.Close()
End Try
22
Using SQL to Query Data (2)
Dim sql As String = "SELECT StudentID, Name, Sex FROM Students“
Dim connStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=school.mdb"
Dim conn As OleDbConnection = New OleDbConnection(connStr)
Dim command As OleDbCommand = New OleDbCommand(query, conn)
Dim reader As OleDbDataReader
Try
conn.Open()
' open connection
reader = command.ExecuteReader()
' execute the command
While reader.Read()
‘ process records
Console.WriteLine("ID: {0}, Name: {1}", reader.GetString(0),
reader.GetString(1))
End While
Catch ex As OleDbException
Console.Out.WriteLine(ex.Message)
Finally
reader.Close()
conn.Close()
End Try
23
Using SQL to Update Data
‘ Assume connection object is setup already
Dim sql As String = “UPDATE STUDENTS SET Sex = “F”
Dim command As OleDbCommand = New OleDbCommand(sql, conn)
Dim rowAffected As Integer
Try
conn.Open()
rowAffected = command.ExecuteNonQuery()
MsgBox("Query executed, " & rowAffected & " rows affected")
Catch e As Exception
MsgBox("Error in executing the following SQL statement:" & vbCrLf &
vbCrLf & sql & vbCrLf & vbCrLf & e.Message)
Finally
conn.Close()
End Try
24
Using SQL Aggregate Functions
Dim sql As String = "SELECT COUNT(*) FROM Students“
Dim connStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=school.mdb"
Dim conn As OleDbConnection = New OleDbConnection(connStr)
Dim command As OleDbCommand = New OleDbCommand(query, conn)
Dim result As Integer
Try
conn.Open()
' open connection
result = command.ExecuteScalar()
' execute the command
Console.WriteLine(“The count is {0} ", result)
Catch ex As OleDbException
Console.Out.WriteLine(ex.Message)
Finally
conn.Close()
End Try
25
DataReader Vs DataSet
• DataReader
–
–
–
–
Less memory consumed, fast access
Simple
One row at a time (forward sequential access)
Connection cannot be closed before finishing access
• DataAdapter + DataSet
– More flexible, can examine data in an arbitrary way,
scrolling back and forth
– Connection can be closed and accessing the DataSet
afterwards (i.e. DataSet can be de-linked with the
connection)
26