#副標=邁向 Microsoft .NET
#大標= ADO.NET - 資料存取的聖石
#眉標=Visual Basic .NET特企
#作者=曹祖聖



--------------------------box程式1---------------------------
Dim Conn As New SqlConnection
Conn.ConnectionString = _
　　"User ID=sa; Password=; Server=localhost;Database=Northwind"
Conn.Open()
　　:
　　:
Conn.Close()
--------------------------------------end----------------------------------


------------------------------box程式2----------------------------
Dim Conn As New OleDbConnection
Conn.ConnectionString = _
			"Provider=SQLOLEDB; User ID=sa; Password=; " & _
			"Data Source=localhost; Initial Catalog=Northwind"
Conn.Open()
　　:
　　:
Conn.Close()
-----------------------------------------end--------------------------------------


------------------------------------box程式3------------------------------
01 Dim Conn As New SqlConnection
02 Conn.ConnectionString = _
			"User ID=sa; Password=; Server=localhost;Database=Northwind"
03 Conn.Open()

04 Dim myCommand As SqlCommand = Conn.CreateCommand()
05 Dim myTrans As SqlTransaction

06 myTrans = Conn.BeginTransaction()
07 myCommand.Connection = Conn
08 myCommand.Transaction = myTrans

09 Try
10     myCommand.CommandText = _
			"INSERT INTO Region (RegionID, RegionDescription) " & _
			"VALUES (100, 'Description')"
11     myCommand.ExecuteNonQuery()
12     myCommand.CommandText = _
			"INSERT INTO Region (RegionID, RegionDescription) " & _
			"VALUES (101, 'Description')"
13     myCommand.ExecuteNonQuery()
14     myTrans.Commit()
15     MessageBox.Show("交易完成")
16 Catch err As Exception
17     Try
18          myTrans.Rollback()
19     Catch ex As SqlException
20 		If Not myTrans.Connection Is Nothing Then
21     		Console.WriteLine("Rollback 失敗")
22 		End If
23     End Try
24     MessageBox.Show("交易失敗")
25 Finally
26     Conn.Close()
27 End Try
------------------------------------end------------------------------


------------------------------box程式4-------------------
Dim Conn As New OleDbConnection
Conn.ConnectionString = _
			"Provider=SQLOLEDB; User ID=sa; Password=; " & _
			"Data Source=localhost; Initial Catalog=MyDatabase"
Dim sql As String = "select PName, Qty from MyOrders"
Dim cmd As SqlCommand = new SqlCommand(sql, Conn)

Conn.Open()
Dim dr As SqlDataReader = cmd.ExecuteReader()

Do While dr.Read()
	Dim pname As String = dr.GetString(0)
	Dim qty As Integer = dr.GetInt32(1) 
	Console.WriteLine("{0}, {1}", pname, qty)
Loop

dr.Close()
Conn.Close()
-------------------------------------end-------------------------------


--------------------------------box程式5---------------------
#單筆資料修改
Dim Conn As New OleDbConnection
Conn.ConnectionString = _
			"Provider=SQLOLEDB; User ID=sa; Password=; " & _
			"Data Source=localhost; Initial Catalog=Northwind"

Dim cmd As New SqlCommand("UPDATE … WHERE OrderID=@ID", Conn)
Dim param As New SqlParameter("@ID", TypeOf(Integer))
param.Value = 12
cmd.Parameters.Add(param) 

Conn.Open()
Dim rowEffects As Integer = cmd.ExecuteNonQuery()
Conn.Close()
-----------------------------------------end-------------------------------------


-------------------------box程式6----------------------
#單筆資料查詢
Dim Conn As New OleDbConnection
Conn.ConnectionString = _
			"Provider=SQLOLEDB; User ID=sa; Password=; " & _
			"Data Source=localhost; Initial Catalog=Northwind"

Dim sql As String = "SELECT cid FROM … WHERE OrderID=@ID" 
Dim cmd As New SqlCommand(sql, conn) 
Dim param As New SqlParameter("@ID", TypeOf(Integer))
param.Value = 12
cmd.Parameters.Add(param) 

Conn.Open()
Dim myCID As Integer = cmd.ExecuteScalar()
Conn.Close()
---------------------------------------end-----------------------------------


----------------------------box程式7------------------------
Dim Conn As New OleDbConnection
Conn.ConnectionString = _
			"Provider=SQLOLEDB; User ID=sa; Password=; " & _
			"Data Source=localhost; Initial Catalog=Northwind"

Dim cmd As New SqlCommand()
cmd.Connection = Conn
cmd.ComandText = "DeleteOrder"
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@ID", orderID)

Conn.Open()
Dim rowEffects As Integer = cmd.ExecuteNonQuery()
Conn.Close()
----------------------------end-----------------------------


----------------------------box程式7------------------------
Dim Conn As New OleDbConnection
Conn.ConnectionString = _
			"Provider=SQLOLEDB; User ID=sa; Password=; " & _
			"Data Source=localhost; Initial Catalog=Northwind"

Dim cmd As New SqlCommand()
cmd.Connection = Conn
cmd.ComandText = "DeleteOrder"
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@ID", orderID)

Conn.Open()
Dim rowEffects As Integer = cmd.ExecuteNonQuery()
Conn.Close()
----------------------------end-----------------------------


------------------------------------box程式8----------------------------
Dim Conn As New OleDbConnection
Conn.ConnectionString = _
			"Provider=SQLOLEDB; User ID=sa; Password=; " & _
			"Data Source=localhost; Initial Catalog=Northwind"

Dim cmd As New SqlCommand()
cmd.Connection = Conn
Conn.Open()

cmd.CommandText = "DECLARE mycursor SCROLLABLE CURSOR " & _
            	 "FOR SELECT * FROM Customers"
cmd.ExecuteNonQuery()
cmd.CommandText = "OPEN mycursor"
cmd.ExecuteNonQuery(
cmd.CommandText = "FETCH NEXT FROM mycursor"
SqlDataReader? dr =cmd.ExecuteReader()
‘    :
‘ 處理 dr 取回的資料
‘    :
dr.Close()
cmd.CommandText = "FETCH ABSOLUTE 5 FROM mycursor"
cmd.ExecuteNonQuery()
cmd.CommandText = "UPDATE Customers set ContactName = " & _
	             "‘Luca Bolognese’ WHERE CURRENT OF mycursor"
cmd.ExecuteNonQuery()
cmd.CommandText = "CLOSE mycursor; DEALLOCATE mycursor"
cmd.ExecuteNonQuery()

Conn.Close()
--------------------------end----------------------------


---------------------box程式9-------------------
Dim Conn As New OleDbConnection
Conn.ConnectionString = _
			"Provider=SQLOLEDB; User ID=sa; Password=; " & _
			"Data Source=localhost; Initial Catalog=Northwind"

Dim cmd As New SqlCommand()
cmd.Connection = Conn
cmd.ComandText = "SELECT * FROM Customers"

Dim da As New SqlDataAdapter()
da.SelectCommand = cmd

Dim ds As New DataSet("Customers")
da.Fill(ds)
---------------------------end-------------------------------


--------------------box程式10-------------------
Dim dt1, dt2 As DataTable
dt1 = ds.Tables(0)
dt2 = ds.Tables("Customers")
-----------------------end-------------------------------


------------------box程式11-----------
For I As Integer = 0 To dt1.Rows.Count - 1
	Dim row As DataRow = dt1.Rows(I)
Next
----------------end------------------


----------------box程式12----------------
For Each row As DataRow In dt1.Rows
	Dim sName As String = row(“Name”)
	Dim iAge As Integer = row(3)
Next
--------------------end----------------------


----------------box程式13-----------------
Dim bdText As New Binding("Text", ds, "Members.Name")
Dim bdWidth As New Binding("Width", ds, "Members.Size")
txtName.DataBindings.Add(bdText)
txtName.DataBindings.Add(bdWidth)
-----------------------end--------------------------


----------------box程式14-----------------
Dim cm As CurrencyManager
cm = BindingContext(ds, "Members")		‘ 取得表單的 CurrencyManager 物件
cm.Position = 0						‘ 第一筆
cm.Position = cm.Position + 1				‘ 下一筆
cm.Position = cm.Count – 1				‘ 移到最後一筆
cm.AddNew()							‘ 新增一筆
cm.EndCurrentEdit()					‘ 結束編輯
cm.RemoveAt( cm.Position )				‘ 刪除一筆
---------------------end----------------------------


----------------box程式15--------------
Dim dr As New DataRelation( _
	"CustomerToOrders" , _
	ds.Tables("Customers").Columns("CustomerID") , _
	ds.Tables("Orders").Columns("CustomerID ") )
ds.Relations.Add(dr)
------------------end-------------------


--------------------box程式16-----------------
Dim dr As DataRow = ds.Tables("Members").Rows(0)
Dim child() As DataRow = dr.GetChildRows("CustomerToOrders")
For I = 0 To child.Length - 1
	  Dim pid As Integer = child( I )( "ProductID" )
	  Dim qty As Integer = child( I )( "Quantity" )
Next
------------------------end------------------------