coding ado net dataset objects
Download
Skip this Video
Download Presentation
Coding ADO.Net DataSet Objects

Loading in 2 Seconds...

play fullscreen
1 / 19

Coding ADO.Net DataSet Objects - PowerPoint PPT Presentation


  • 77 Views
  • Uploaded on

Coding ADO.Net DataSet Objects. DataSet Object. A DataSet object can hold several tables and relationships between tables.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' Coding ADO.Net DataSet Objects' - drake


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
dataset object
DataSet Object
  • A DataSet object can hold several tables and relationships between tables.
  • A DataSet is a set of disconnedted data. Data is extracted from the database and stored in the DataSet object. Updates to the DataSet must copy back to the database to make the changes permanent.
dataset and related objects
DataSet and Related Objects
  • DataSet: Can contain multiple tables and relationships.
  • DataTable object: Represents a table in the dataset.
  • DataAdapter: This the object used to pass data between the database and the dataset. The Fill method copies the data into the dataset, and the Update method copies the updates back into the database.
  • DataView: This represents a specific view of the DataTables held in the dataset.
dataset and related objects1
DataSet and Related Objects

DataSet

Tables

DataView

DataAdapter

Commands

Connection

DataSource

structure of a dataset
Structure of a Dataset

Dataset

Tables

Data table

Rows

Data Row

Columns

Data Column

Constraints

Relations

Constraint

Data Relation

reading data into a table
Reading Data into a Table
  • dim strConn as string ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source = c:\salesDB.mdb"
  • dim objConn as new OledbConnection(strConn)
  • dim strSQLSelect as string = "select * from customer;"
  • dim objDataSet as new Dataset()
  • dim objAdapter as new OledbDataAdapter(strSQLSelect, objConn)
  • objAdapter.Fill(objDataSet, "Cust")
binding datagrid view with code
Binding DataGridView with Code

Imports System.Data.OleDb

Public Class Form4

Inherits System.Windows.Forms.Form

Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = c:\salesDB.mdb"

Dim objConn As New OleDbConnection(strConn)

Dim objDataSet As New DataSet()

Private Sub Form4_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Dim strSQL As String = "select * from customer;"

Dim objAdapter As New OleDbDataAdapter(strSQL, objConn)

objAdapter.Fill(objDataSet, "Cust")

DataGridView1.DataSource = objDataSet

DataGridView1.DataMember = "Cust"

End Sub

Note: Adapter’s Fill method is able to open the connection.

binding a listbox with code
Binding a ListBox with Code

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = c:\salesDB.mdb"

Dim objConn As New OleDbConnection(strConn)

Dim strSQL As String = "select * from customer;"

Dim objDataSet As New DataSet()

Dim objAdapter As New OleDbDataAdapter(strSQL, objConn)

objAdapter.Fill(objDataSet, "Customer")

ListBox1.DataSource = objDataSet.Tables("Customer")

ListBox1.DisplayMember = "CID"

End Sub

Private Sub ListBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged

TextBox1.Text = ListBox1.SelectedItem.item("cname")

End Sub

datatable s rows property
DataTable’s Rows Property
  • This is a collection of all the records in a table, a collection of DataRow objects.
  • DataRow object’s properties and methods.
accessing a record with index
Accessing a Record with Index

objAdapter.Fill(objDataSet, "Customer")

TextBox1.Text = objDataSet.Tables("Customer").Rows(rowIndex).Item(0)

TextBox2.Text = objDataSet.Tables("Customer").Rows(rowIndex).Item(1)

access rows in a datarow collection
Access Rows in a DataRow Collection
  • dim objTable as DataTable = objDataset.Tables("Customer")
  • dim objRow as DataRow
  • For each objRow in objTable.Rows
  • strResult=strResult+“ " & objRow("cid") & " " & objRow("cname") & vbCrLf
  • Next
dataview object convenient for binding and display records meeting criteria
DataView ObjectConvenient for Binding and Display Records Meeting Criteria
  • The DataView object exposes a complete table or a subset of the records from a table.
  • Table’s DefaultView property:
    • Dim ObjDataView As New DataView()
    • ObjDataView.Table = objDataSet.Tables("Cust")
    • Or: ObjDataView = objDataSet.Tables("Cust").DefaultView
  • DataView can be used as a DataSource in data binding.
  • Other useful methods:
    • Find, Sort
data binding with dataview object
Data Binding with DataView Object

Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = c:\salesDB.mdb"

Dim objConn As New OleDbConnection(strConn)

Dim objDataSet As New DataSet()

Dim strSQL As String = "select * from customer;"

Dim objAdapter As New OleDbDataAdapter(strSQL, objConn)

objAdapter.Fill(objDataSet, "Cust")

Dim ObjDataView As New DataView()

ObjDataView = objDataSet.Tables("Cust").DefaultView

DataGridView1.DataSource = ObjDataView

selecting a subset of records with dataview s rowfilter property
Selecting a Subset of Records with DataView’s RowFilter Property
  • objDataView.RowFilter = criteria
  • Note: The criteria can be a simple or complex condition.
dataview example
DataView Example

Dim ObjDataView As New DataView()

Dim newRating As String

If RadioButton1.Checked = True Then

newRating = "A"

ElseIf RadioButton2.Checked Then

newRating = "B"

Else

newRating = "C"

End If

ObjDataView.Table = objDataSet.Tables("Cust")

ObjDataView.RowFilter = "rating=\'" & newRating & "\'"

DataGridView1.Visible = True

DataGridView1.DataSource = ObjDataView

creating multiple tables one adapter one table
Creating multiple tables: One Adapter, One Table

Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = c:\salesDB.mdb"

Dim objConn As New OleDbConnection(strConn)

Dim strSQLCustomer As String = "select * from customer;"

Dim strSQLOrders As String = "select * from orders"

Dim objAdapterCust As New OleDbDataAdapter(strSQLCustomer, objConn)

Dim objAdapterOrd As New OleDbDataAdapter(strSQLOrders, objConn)

Dim objDataset As New DataSet

objAdapterCust.Fill(objDataset, "Customer")

objAdapterOrd.Fill(objDataset, "orders")

creating multiple tables one adapter many tables
Creating Multiple Tables: One Adapter, Many Tables
  • Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = c:\salesDB.mdb"
  • Dim objConn As New OleDbConnection(strConn)
  • Dim strSQLCustomer As String = "select * from customer;"
  • Dim strSQLOrders As String = "select * from orders"
  • Dim objAdapter As New OleDbDataAdapter(strSQLCustomer, objConn)
  • Dim objDataset As New DataSet
  • objAdapter.Fill(objDataset, "Customer")
  • objAdapter.SelectCommand.CommandText = strSQLOrders
  • objAdapter.Fill(objDataset, "orders")
  • DataGrid1.DataSource = objDataset
  • DataGridView1.DataMember = "customer"
parent child form without a relation
Parent/Child Form without a Relation
  • This form lets users to select a CID from a listbox, then displays parent information in textboxes and child records in a datagrid by defining a dataview on Orders table based on selected CID.
    • Use View’s Filter

Dim ObjDataView As New DataView()

ObjDataView.Table = objDataSet.Tables(“Orders")

ObjDataView.RowFilter = “cid=\'" & ListBox1.SelectedItem.item("cid") & "\'"

DataGrid1.DataSource = ObjDataView

slide19

Private Sub MainSubForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = c:\salesDB.mdb"

Dim objConn As New OleDbConnection(strConn)

Dim strSQLCustomer As String = "select * from customer;"

Dim strSQLOrders As String = "select * from orders"

Dim objAdapter As New OleDbDataAdapter(strSQLCustomer, objConn)

objAdapter.Fill(objDataSet, "Customer")

objAdapter.SelectCommand.CommandText = strSQLOrders

objAdapter.Fill(objDataSet, "orders")

lstCID.DataSource = objDataSet.Tables("customer")

lstCID.DisplayMember = "CID"

End Sub

Private Sub lstCID_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lstCID.SelectedIndexChanged

Dim ObjDataView As New DataView

ObjDataView.Table = objDataSet.Tables("Orders")

ObjDataView.RowFilter = "cid=\'" & lstCID.SelectedItem.item("cid") & "\'"

txtCname.Text = lstCID.SelectedItem.item("Cname")

txtCity.Text = lstCID.SelectedItem.item("City")

txtRating.Text = lstCID.SelectedItem.item("rating")

txtOrdCount.Text = ObjDataView.Count.ToString

dgOrders.DataSource = ObjDataView

End Sub

ad