＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
＊ 電腦環境：Windows 7 64bit ＊
＊ SQL Server Management Studio版本：2008 R2 ＊
＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
Microsoft所提供的下載點：http://www.microsoft.com/en-us/download/details.aspx?id=23654
點一下「Download」就可以下載了，得到SQL2000SampleDb.msi
點兩下「SQL2000SampleDb.msi」安裝，完成後產出資料夾內有多個檔案
instnwnd.sql
instpubs.sql
NORTHWND.LDF
NORTHWND.MDF
PUBS.MDF
PUBS_LOG.LDF
ReadMe_SQL2000SampleDbScripts.htm
開啟SQL Server Management Studio並登入
準備掛上資料庫.mdf檔
滑鼠右鍵點擊「資料庫」>「附加」
「加入」
找到要附加的NORTHWND.MDF檔
NORTHWND.MDF：資料
NORTHWND.LDF：紀錄檔
倘若今天只剩下.mdf檔，丟失了.LDF檔
就把找不到的這個.LDF按「移除」>然後「確定」即可
否則會出現錯誤訊息
標題: Microsoft SQL Server Management Studio
------------------------------
伺服器 'LISASST-PC\LISasst' 的 附加資料庫 失敗。 (Microsoft.SqlServer.Smo)
如需說明，請按一下: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1617.0+((KJ_RTM_GDR).110422-1901+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=附加資料庫+Server&LinkId=20476
------------------------------
其他資訊:
執行 Transact-SQL 陳述式或批次時發生例外狀況。 (Microsoft.SqlServer.ConnectionInfo)
------------------------------
檔案 "D:\MSSQL\DATA\Northwind_log.LDF" 的目錄查閱失敗，有作業系統錯誤 3(系統找不到指定的路徑。)。 (Microsoft SQL Server, 錯誤: 5133)
如需說明，請按一下: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1617&EvtSrc=MSSQLServer&EvtID=5133&LinkId=20476
------------------------------
按鈕:
確定
------------------------------
在工具列空白處按滑鼠右鍵，出現如下選單，要ˇSQL 編輯器
按一下「新增查詢」然後輸入
CREATE DATABASE 資料庫名稱 ON
(FILENAME = N'MDF檔所在路徑')
FOR ATTACH_REBUILD_LOG
展開資料庫>Northwind>資料表，可以看到表單如下
dbo.Categories
dbo.CustomerCustomerDemo
dbo.CustomerDemographics
dbo.Customers
dbo.Employees
dbo.EmployeeTerritories
dbo.Order Details
dbo.Orders
dbo.Products
dbo.Region
dbo.Shippers
dbo.Suppliers
dbo.Territories
記住我們要連的
server是：(local)\abc
databse是：Northwind
＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
＊ 電腦環境：Windows 7 64bit ＊
＊ Microsoft Visual Studio版本：2010 Professional ＊
＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
開啟Microsoft Visual Studio並建立新專案
建立Button*3
ListBox*2
回到程式面撰寫如下
Imports System.Data.SqlClient
Public Class Form1
Private Sub button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles button1.Click
'先將listBox清空
listBox1.Items.Clear()
Dim cs As String = "server=(local)\abc;database=Northwind;integrated security=SSPI;"
Dim qs As String = "select LastName,TitleOfCourtesy, Region, ReportsTo from employees"
'1.引用SqlConnection物件連接資料庫
Using cn As New SqlConnection(cs)
'2.開啟資料庫
cn.Open()
'3.引用SqlCommand物件
Using command As New SqlCommand(qs, cn)
'4.搭配SqlCommand物件使用SqlDataReader
Using dr As SqlDataReader = command.ExecuteReader()
While (dr.Read())
'5.判斷資料列是否為空
If Not dr(0).Equals(DBNull.Value) Then
listBox1.Items.Add((((dr(0).ToString() & vbTab) + dr(1).ToString() & vbTab) + dr(2).ToString() & vbTab) + dr(3).ToString())
End If
End While
End Using
End Using
End Using
End Sub
Private Sub button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles button2.Click
'先將listBox清空
listBox1.Items.Clear()
Dim cs As String = "server=(local)\abc;database=Northwind;integrated security=SSPI;"
Dim qs As String = "select * from employees"
'1.引用SqlConnection物件連接資料庫
Using cn As New SqlConnection(cs)
'2.開啟資料庫
cn.Open()
'3.引用SqlCommand物件
Using command As New SqlCommand(qs, cn)
'4.搭配SqlCommand物件使用SqlDataReader
Using dr As SqlDataReader = command.ExecuteReader()
While (dr.Read())
Dim intID As Integer = dr.GetOrdinal("employeeid")
Dim intDesc As Integer = dr.GetOrdinal("FirstName")
listBox1.Items.Add((dr(intID).ToString() & " – ") + dr(intDesc).ToString())
End While
End Using
End Using
End Using
End Sub
Private Sub button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles button3.Click
'先將listBox清空
listBox1.Items.Clear()
listBox2.Items.Clear()
Dim cs As String = "server=(local)\abc;database=Northwind;integrated security=SSPI;"
Dim qs As String = "select * from employees;select * from products"
'1.引用SqlConnection物件連接資料庫
Using cn As New SqlConnection(cs)
'2.開啟資料庫
cn.Open()
'3.引用SqlCommand物件
Using command As New SqlCommand(qs, cn)
'4.搭配SqlCommand物件使用SqlDataReader
Using dr As SqlDataReader = command.ExecuteReader()
While (dr.Read())
'5.判斷資料列是否為空
If Not dr(0).Equals(DBNull.Value) Then
listBox1.Items.Add((((dr(0).ToString() & vbTab) + dr(1).ToString() & vbTab) + dr(2).ToString() & vbTab) + dr(3).ToString())
End If
End While
'6.下一個查詢結果
dr.NextResult()
While (dr.Read())
If Not dr(0).Equals(DBNull.Value) Then
listBox2.Items.Add((((dr(0).ToString() & vbTab) + dr(1).ToString() & vbTab) + dr(2).ToString() & vbTab) + dr(3).ToString())
End If
End While
End Using
End Using
End Using
End Sub
End Class
按F5執行畫面如下
點一下button1
在程式中我們界定了"select LastName,TitleOfCourtesy, Region, ReportsTo from employees"
表示從資料表employees抓出欄位LastName’TitleOfCourtesy、Region、ReportsTo的值
並用dr儲存：Using dr As SqlDataReader = command.ExecuteReader()
(dr(0).ToString() & vbTab)表示抓第一位置的值
(dr(1).ToString() & vbTab)表示抓第二位置的值
(dr(2).ToString() & vbTab)表示抓第三位置的值
(dr(3).ToString() & vbTab)表示抓第四位置的值
點一下button2
在程式中我們界定了"select * from employees"
表示從資料表employees抓出所有欄位的值
但是
Dim intID As Integer = dr.GetOrdinal("employeeid")
Dim intDesc As Integer = dr.GetOrdinal("FirstName")
表示我們只要抓欄位employeeid和FirstName
並以(dr(intID).ToString() & " – ") + dr(intDesc).ToString()的形式呈現
點一下button3
在程式中我們界定了"select * from employees;select * from products"
一次抓2張table的所有欄位值呢
Using command As New SqlCommand(qs, cn)
Using dr As SqlDataReader = command.ExecuteReader()
While (dr.Read())
'第1張table的資料
End While
dr.NextResult()
While (dr.Read())
'第2張table的資料
End While
End Using
End Using
終於寫完
沒有留言:
張貼留言