ADO.NET -database connection

ADO.NET
database connection

@wong
Ref:: http://www.anekwong.com

ADO.NET
• ADO.NET
.NET
• ADO => ActiveX Data Object

.NET

ADO NET Architecture
• NET Framework data providers
• The DataSet

windows Data Sources
Windows Data sources
(data source)

-

-

IDE

,

(bind)

Data Provider
•

Query
•

Object

DataSet
•

VS2005
–
–
–
–

SQL Server Data Provider
OLEDB Data Provider
Oracle Data Provider
ODBC Data Provider

MS SQL Server
MS Access

V.7

Data Provider
• Data Provider
– Connection
– Command
Query
SQL
– DataAdapter
fill
DataSet
– DataReader
result set
read-only

forward-only /

NET Framework Data Providers
NET Framework
data provider

Description

NET Framework Data Provider for SQL Server

Provides data access for Microsoft SQL
Server version 7.0 or later Uses the
System.Data.SqlClient namespace

NET Framework Data Provider for OLE DB

For data sources exposed using OLE DB
Uses the System.Data.OleDb namespace

NET Framework Data Provider for ODBC

For data sources exposed using ODBC Uses
the System.Data.Odbc namespace

NET Framework Data Provider for Oracle

For Oracle data sources The NET Framework
Data Provider for Oracle supports Oracle
client software version 8.1.7 and later, and
uses the System.Data.OracleClient
namespace

Core Objects of NET Framework Data Providers

Object Description
Connection

Establishes a connection to a specific data source The base
class for all Connection objects is the DbConnection class

Command

Executes a command against a data source Exposes
Parameters and can execute within the scope of a
Transaction from a Connection. The base class for all
Command objects is the DbCommand class

DataReader

Reads a forward-only, read-only stream of data from a data
source The base class for all DataReader objects is the
DbDataReader class

DataAdapter

Populates a DataSet and resolves updates with the data
source The base class for all DataAdapter objects is the
DbDataAdapter class

- The NET Framework Data Provider for SQL Server

Imports Provider
Imports System Data SqlClient
- The .NET Framework Data Provider for OLE DB

Imports Provider

Imports System Data OleDb
Driver

Provider

SQLOLEDB

Microsoft OLE DB Provider for SQL Server

MSDAORA

Microsoft OLE DB Provider for Oracle

Microsoft Jet OLEDB 4.0

OLE DB Provider for Microsoft Jet

- The NET Framework Data Provider for ODBC

Imports Provider
Imports System Data Odbc
Driver
SQL Server
Microsoft ODBC for Oracle

Microsoft Access Driver

mdb

- The NET Framework Data Provider for Oracle

Imports Provider
Imports System.Data
Imports System.Data.OracleClient

Choosing a NET Framework Data Provider
Provider

Notes

NET Framework
Data Provider
for SQL Server

Recommended for middle-tier applications using Microsoft SQL Server
7.0 or later
Recommended for single-tier applications using Microsoft Database
Engine MSDE or SQL Server 7.0 or later
Recommended over use of the OLE DB Provider for SQL Server
SQLOLEDB with the NET Framework Data Provider for OLE DB
For SQL Server 6.5 and earlier, you must use the OLE DB Provider for
SQL Server with the NET Framework Data Provider for OLE DB

NET Framework
Data Provider
for OLE DB

Recommended for middle-tier applications using SQL Server 6.5 or
earlier
For SQL Server 7.0 or later, the NET Framework Data Provider for SQL
Server is recommended
Also recommended for single-tier applications using Microsoft Access
databases Use of an Access database for a middle-tier application is
not recommended

NET Framework
Data Provider
for ODBC

Recommended for middle and single-tier applications using ODBC data
sources

NET Framework
Data Provider
for Oracle

Recommended for middle and single-tier applications using Oracle data
sources

2. Data Set Designer
Vs
DataSet

.Net Framework
disconnected data access

(

Data Table)
)

DataSet (
DataSet

dataSet
Data Table

Customers
Orders

Table Adapter
SME
Table Adapter

Customer
Orders

DataSet
•
DataSet

Disconnected data access
•
– DataTable

dataSet

TableAdapter

TableAdapter
DataSet
DataSet

Tableadapter.Fill(datatable)

Tableadapter
Datatable

DataSet
DataTable TableAdapter
Data Table

Dataset.table(row).field
Dataset
Table
Row
count-1
Field

Dataset

count

Tableadapter.Fill(datatable)
Fill

GetData
GetData
DataTable

TableAdap
D
GetData

Data Binding
Data Binding

(bind)

DataBinding
code

Dat

Dat

ADO NET Sample Application
Option Explicit On
Option Strict On

Provider
SqlClient

Imports System
Imports System Data
Imports System Data SqlClient

Public Class Program
Public Shared Sub Main
Dim connectionString As String GetConnectionString
Dim queryString As String _
SELECT CategoryID, CategoryName FROM dbo Categories;
Using connection As New SqlConnection connectionString
Dim command As SqlCommand connection CreateCommand
command CommandText queryString

Try
connection Open
Dim dataReader As SqlDataReader _
command ExecuteReader
Do While dataReader Read
Console WriteLine vbTab & {0} & vbTab & {1} , _
dataReader 0 , dataReader 1
Loop
dataReader Close
Catch ex As Exception
Console WriteLine ex Message
End Try
End Using
End Sub
Private Shared Function GetConnectionString As String
' To avoid storing the connection string in your code,
' you can retrieve it from a configuration file
Return Data Source local ;Initial Catalog Northwind; _
& Integrated Security SSPI;
End Function
End Class

ADO NET Sample Application
Option Explicit On
Option Strict On

Provider

Imports System
Imports System Data
Imports System Data OleDb
Public Class Program
Public Shared Sub Main
Dim connectionString As String GetConnectionString
Dim queryString As String _
SELECT CategoryID, CategoryName FROM Categories;
Using connection As New OleDbConnection connectionString
Dim command As OleDbCommand connection CreateCommand
command CommandText queryString
Try

OleDb

Try
connection Open
Dim dataReader As OleDbDataReader _
command ExecuteReader
Do While dataReader Read
Console WriteLine vbTab & {0} & vbTab & {1} , _
dataReader 0 , dataReader 1
Loop
dataReader Close
Catch ex As Exception
Console WriteLine ex Message
End Try
End Using
End Sub
Private Shared Function GetConnectionString As String
' To avoid storing the connection string in your code,
' you can retrieve it from a configuration file
' Assumes Northwind mdb is located in c Data folder
Return Provider Microsoft Jet OLEDB 4.0;Data Source _
& c DataNorthwind mdb;User Id admin;Password ;

Just do it!
•
•

OleDB Provider

Northwind
Ms Access
Import
Provider

Imports System.Data.OleDB
Dim objConn As New System.Data.OleDbConnection()
objConn.ConnectionString = “Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=C:Northwind.mdb”

objConn.Open()

objConn.Close()

Command
Sql
Select, Update, Delete, Insert

Dim objComm As New OleDb.OleDbCommand()

Provider
CommandText
objComm.CommandText = “Select * from Employyes”

objComm.ExecuteNonQury
objComm.ExecuteScalar
objComm.ExecuteReader

objComm.ExecuteReader
Set

dataReader
Dim dr As OleDb.OleDbDataReader
dr = objComm.ExcuteReader

Item

DataReader

While dr.read()
Dim myObj As object = dr.Item(3)
Dim myOtherObj As object = dr.Item(“Customer”)
End While

Dim strSQL As String = “Select CustomerID,CompanyName F
Sub
Dim objConn As New OleDbConnection(StrConn)
Dim objComm As New OleDbCommand(strSQL,objConn)
Dim dr As OleDbDataReader
objConn.Open()
dr = objComm.ExecuteReader()

ListBox1.Items.Add(“
” & ControlChars.Tab & “
Do while dr.Read()
Dim strRecord As String = dr.Item(“CustomerID”).tostring(
Item(“CompanyName”).Tostring()
ListBox1.Items.Add(strRecord)
Loop

DataAdapter
DataAdapter => Dim da As New OleDb.OleDbDataAdapter
Method Fill

DataSet => dataAdapter.Fill(dataSet,tab

Dim objConn As New Data.OleDbConnection(“Provider=Microsoft.Jet.OL _
EDB.4.0;Data Source=C:Northwind.mdb”)
Dim strSQL As String = “Select * from Employees”
Dim da As New OleDb.OleDbDataAdapter
Da.SelectCommand = New OleDb.OleDbCommand(strSQL,objConn)
Dim ds As New DataSet
da.Fill(ds,”Employees”)

dataSet
dataSet.Tables(table).rows(row).(field)
textbox1.text = ds.Tables(“employees”).row(0).(“FirstName”)

ds.Tables(“employees”).row(0).(“FirstName”

DataAdapter
Method Update
=> dataAdapter.Update(dataSet,table)

DataSet
•

DataSet
Dim tb As DataTable = ds.Tables(“Employees”)
Dim row As DataRow = tb.NewRow()
row(“FirstName”) = “Chaimard”
row(“LastName”) = “Kama”
tb.Rows.Add(row)



DataSet
For Each row As DataRow In tb.Rows
if row(“FirstName”) = “Chaimard” Then
row.(“FirstName”) = “Superman”
end if
Next



DataSet
For Each row As DataRow In tb.Rows
if row(“FirstName”) = “Chaimard” Then
row.dalete()
end if
Next

Binding
Data
• Simple Binding Data
textbox, Label

• Complex Binding Data
ListBox, ComboBox



Simple Binding Data
:
object.DataBindings.Add(propertyName,dataSource,dataMember)
: textbox1.dataBindings.Add(“text”,ds.Tables(“emp”), “FirstName”)



Listbox1.DataSource = ds.Tables(“emp”)
Listbox1.DisplayMember = “FirstName”

:

Complex Binding Data

With cboStudent
ComboBox
.datasource = ds.Tables(“Faculty”)
.displaymember = “DescriptionThai”
.ValueMember = “FacultyCode”
.DataBindings.Add(“SelectValue”,objBS,“facultyCode”)
End with

2.

Student
Student


: me.bindingContext(ds.Tables.(“emp”)).position = 0
: me.bindingContext(ds.Tables.(“emp”)).position +=
1

: me.bindingContext(ds.Tables.(“emp”)).position =1
:
me.bindingContext(ds.Tables.(“emp”)).position =
me.bindingContext(ds.Tables.(“emp”)).count - 1


BindingSource
Dim objBS As New BindingSource
objBS.DataSource =
ds.Tables(“emp”)
(ds.Tables(“emp”)

emp )
: objBS.MoveFirst
: objBS.MoveNext

ADO.NET -database connection

More Related Content

What's hot

Viewers also liked

Similar to ADO.NET -database connection

More from Anekwong Yoddumnern

Recently uploaded

ADO.NET -database connection