Docstoc

VB .NET Revisit_property

Document Sample
VB .NET Revisit_property Powered By Docstoc
					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                              SQL
             .NET Data Provider              Server




Client       OLE DB               OLE DB
             .NET Data Provider   Provider   Other
                                             DBMS



                                  ODBC
                                  Provider   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 one-
  row-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)
       •   set ConnectionString property
2.   Create a Command object (of class SqlCommand and
     OleDbCommand) by Connection object‟s CreateCommand
     method
       •   Set CommandText property
3.   Call the Open() method of the Connection object
4.   Declare a DataReader (if need)
5.   Executing the query by calling methods of the Command object
       •   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
6.   Process the result
7.   Close the DataReader (if using ExecuteReader)
8.   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()                                  ' 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
End Sub                                                                                   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.                     DataRelation
•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,         DataTable
describing the tables, columns,                        DataTable
data types




                                                                   10
     Creating and Using DataSets
1.   Create a Connection object
       •   set ConnectionString property
2.   Create a Command object
       •   Set CommandText property
3.   Create a DataAdapter object (SqlDataAdapter,
     OleDbDataAdapter)
       •   Set the SelectCommand, InsertCommand, UpdateCommand,
           DeleteComand property to the Command object created in last
           step
4.   Create a DataSet object
5.   Call the Open() method of the Connection object
6.   Use the Fill() method of the DataAdapter object to fill
     the DataSet
7.   Close the Connection with the Close() method
8.   You may then update/delete rows in DataSet and use
     the Update() method of the DataAdapter to update the
     database                                                11
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)               „ set the
   SelectCommand as well
   Dim ds As DataSet = New DataSet

    Dim table As DataTable
    Dim row As DataRow
    Dim rowIndex As Integer
                                                      AdoExample3
    Try
      conn.Open()                          ' open connection
      da.Fill(ds, “Students”)              „ fill the dataset
      conn.Close()              „ 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
                                                                                        12
End Sub
       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                                                                             17
 End Sub
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

				
DOCUMENT INFO
Shared By:
Categories:
Stats:
views:3
posted:8/18/2010
language:English
pages:26