ADO.NET のサンプル

Imports System.Data.OleDb

Public Class Form1

Private m_sConn1 As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\ExcelData1.xls;" & _
"Extended Properties=""Excel 8.0;HDR=YES"""
Private m_sConn2 As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\ExcelData2.xls;" & _
"Extended Properties=""Excel 8.0;HDR=YES"""
Private m_sNorthwind = _
"C:\Program Files\Microsoft Office\Office10\Samples\Northwind.mdb"
Private m_sAction As String
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
RadioButton1.Text = "Create_Workbook"
RadioButton2.Text = "Retrieve_Records"
RadioButton3.Text = "Add_Records"
RadioButton4.Text = "Update_Records"
RadioButton5.Text = "Update_Individual_Cells"
RadioButton6.Text = "Use_External_Source"
Button1.Text = "Go!"
End Sub
Private Sub RadioButtons_Click(ByVal sender As Object, ByVal e As System.EventArgs) _
Handles RadioButton1.Click, RadioButton2.Click, RadioButton3.Click, _
RadioButton4.Click, RadioButton5.Click, RadioButton6.Click
m_sAction = sender.Text 'Store the text for the selected radio button
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Try
' Call the associated routine to add/update/modify the workbook.
Select Case m_sAction
Case "Create_Workbook" : Create_Workbook()
Case "Retrieve_Records" : Retrieve_Records()
Case "Add_Records" : Add_Records()
Case "Update_Records" : Update_Records()
Case "Update_Individual_Cells" : Update_Individual_Cells()
Case "Use_External_Source" : Use_External_Source()
End Select
Catch ex As OleDbException
Dim er As OleDbError
For Each er In ex.Errors
MsgBox(er.Message)
Next
Catch ex2 As System.InvalidOperationException
MsgBox(ex2.Message)
End Try
End Sub
Public Sub Create_Workbook()
' If the workbooks already exist, prompt to delete.
Dim answer As MsgBoxResult
If Dir("C:\ExcelData1.xls") <> "" Or Dir("C:\ExcelData2.xls") <> "" Then
answer = MsgBox("Delete existing workbooks (C:\ExcelData1.xls and " & _
"C:\ExcelData2.xls)?", MsgBoxStyle.YesNo)
If answer = MsgBoxResult.Yes Then
If Dir("C:\ExcelData1.xls") <> "" Then Kill("C:\ExcelData1.xls")
If Dir("C:\ExcelData2.xls") <> "" Then Kill("C:\ExcelData2.xls")
Else
Exit Sub
End If
End If
'==========================================================================
' Create a workbook with a table named EmployeeData. The table has 3
' fields: ID (char 255), Name (char 255) and Birthdate (date).
'==========================================================================
Dim conn As New OleDbConnection()
conn.ConnectionString = m_sConn1
conn.Open()
Dim cmd1 As New OleDbCommand()
cmd1.Connection = conn
cmd1.CommandText = "CREATE TABLE EmployeeData (Id char(255), Name char(255), BirthDate date)"
cmd1.ExecuteNonQuery()
cmd1.CommandText = "INSERT INTO EmployeeData (Id, Name, BirthDate) values ('AAA', 'Andrew', '12/4/1955')"
cmd1.ExecuteNonQuery()
conn.Close()
'==========================================================================
' Create a workbook with a table named InventoryData. The table has 3
' fields: Product (char 255), Qty (float) and Price (currency).
'==========================================================================
conn.ConnectionString = m_sConn2
conn.Open()
Dim cmd2 As New OleDbCommand()
cmd2.Connection = conn
cmd2.CommandText = "CREATE TABLE InventoryData (Product char(255), Qty float, Price currency)"
cmd2.ExecuteNonQuery()
cmd2.CommandText = "INSERT INTO InventoryData (Product, Qty, Price) values ('Cola', 200, 1.35)"
cmd2.ExecuteNonQuery()
cmd2.CommandText = "INSERT INTO InventoryData (Product, Qty, Price) values ('Chips', 550, 0.89)"
cmd2.ExecuteNonQuery()
conn.Close()
' NOTE: You can ALTER and DROP tables in a similar fashion.
End Sub
Public Sub Retrieve_Records()
'==========================================================
'Use a DataReader to read data from the EmployeeData table.
'==========================================================
Dim conn1 As New System.Data.OleDb.OleDbConnection(m_sConn1)
conn1.Open()
Dim cmd1 As New System.Data.OleDb.OleDbCommand("Select * From [EmployeeData$]", conn1)
Dim rdr As OleDbDataReader = cmd1.ExecuteReader
Debug.WriteLine(vbCrLf & "EmployeeData:" & vbCrLf & "=============")
Do While rdr.Read()
Debug.WriteLine(System.String.Format("{0,-10}{1, -15}{2}", _
rdr.GetString(0), rdr.GetString(1), _
rdr.GetDateTime(2).ToString("d")))
Loop
rdr.Close()
conn1.Close()
'========================================================
'Use a DataSet to read data from the InventoryData table.
'========================================================
Dim conn2 As New OleDbConnection(m_sConn2)
Dim da As New OleDbDataAdapter("Select * From [InventoryData$]", conn2)
Dim ds As DataSet = New DataSet()
da.Fill(ds)
Debug.WriteLine(vbCrLf & "InventoryData:" & vbCrLf & "==============")
Dim dr As DataRow
For Each dr In ds.Tables(0).Rows 'Show results in output window
Debug.WriteLine(System.String.Format("{0,-15}{1, -6}{2}", _
dr("Product"), dr("Qty"), dr("Price")))
Next
conn2.Close()
End Sub
Public Sub Add_Records()
'==========================================================================
' Run an INSERT INTO command to add new records to the workbook.
'==========================================================================
Dim conn1 As New System.Data.OleDb.OleDbConnection(m_sConn1)
conn1.Open()
Dim cmd As New System.Data.OleDb.OleDbCommand()
cmd.Connection = conn1
cmd.CommandText = "INSERT INTO [EmployeeData$] (ID, Name, BirthDate) values ('CCC', 'Charlie', '10/14/48')"
cmd.ExecuteNonQuery()
cmd.CommandText = "INSERT INTO [EmployeeData$] (ID, Name, BirthDate) values ('DDD', 'Deloris', '7/19/98')"
cmd.ExecuteNonQuery()
conn1.Close()
'====================================================================
'Use the InsertCommand object to add new records to the InventoryData
'table.
'====================================================================
Dim conn2 As New OleDbConnection(m_sConn2)
Dim da As New OleDbDataAdapter("Select * From [InventoryData$]", conn2)
Dim ds As DataSet = New DataSet()
da.Fill(ds, "MyExcelTable")
' Generate the InsertCommand and add the parameters for the command.
da.InsertCommand = New OleDbCommand( _
"INSERT INTO [InventoryData$] (Product, Qty, Price) VALUES (?, ?, ?)", conn2)
da.InsertCommand.Parameters.Add("@Product", OleDbType.VarChar, 255, "Product")
da.InsertCommand.Parameters.Add("@Qty", OleDbType.Double).SourceColumn = "Qty"
da.InsertCommand.Parameters.Add("@Price", OleDbType.Currency).SourceColumn = "Price"
' Add two new records to the dataset.
Dim dr As DataRow
dr = ds.Tables(0).NewRow
dr("Product") = "Bread" : dr("Qty") = 390 : dr("Price") = 1.89 : ds.Tables(0).Rows.Add(dr)
dr = ds.Tables(0).NewRow
dr("Product") = "Milk" : dr("Qty") = 99 : dr("Price") = 2.59 : ds.Tables(0).Rows.Add(dr)
' Apply the dataset changes to the actual data source (the workbook).
da.Update(ds, "MyExcelTable")
conn2.Close()
End Sub
Public Sub Update_Records()
'==========================================================================
' Run an UPDATE command to change a record in the EmployeeData
' table.
'==========================================================================
Dim conn1 As New System.Data.OleDb.OleDbConnection(m_sConn1)
conn1.Open()
Dim cmd As New System.Data.OleDb.OleDbCommand()
cmd.Connection = conn1
cmd.CommandText = "UPDATE [EmployeeData$] " & _
"SET NAME = 'Aaron', BirthDate = '5/4/1975' WHERE ID = 'AAA'"
cmd.ExecuteNonQuery()
conn1.Close()
'====================================================================
' Use the UpdateCommand object to modify records in the InventoryData
' table.
'====================================================================
Dim conn2 As New OleDbConnection(m_sConn2)
Dim da As New OleDbDataAdapter("Select * From [InventoryData$]", conn2)
Dim ds As DataSet = New DataSet()
da.Fill(ds, "MyInventoryTable")
' Generate the UpdateCommand and add the parameters for the command.
da.UpdateCommand = New OleDbCommand( _
"UPDATE [InventoryData$] SET Qty = ?, Price=? WHERE Product = ?", conn2)
da.UpdateCommand.Parameters.Add("@Qty", OleDbType.Numeric).SourceColumn = "Qty"
da.UpdateCommand.Parameters.Add("@Price", OleDbType.Currency).SourceColumn = "Price"
da.UpdateCommand.Parameters.Add("@Product", OleDbType.VarChar, 255, "Product")
' Update the first two records.
ds.Tables(0).Rows(0)("Qty") = 1000
ds.Tables(0).Rows(0)("Price") = 10.1
ds.Tables(0).Rows(1)("Qty") = 2000
ds.Tables(0).Rows(1)("Price") = 20.2
' Apply the dataset changes to the actual data source (the workbook).
da.Update(ds, "MyInventoryTable")
conn2.Close()
End Sub
Public Sub Update_Individual_Cells()
'==========================================================================
' Update individual cells on the EmployeeData worksheet;
' specifically, cells F3, G3, and I4 are modified.
'==========================================================================
' NOTE: The connection string indicates that the table does *NOT*
' have a header row.
Dim conn As New System.Data.OleDb.OleDbConnection(m_sConn1.Replace("HDR=YES", "HDR=NO"))
conn.Open()
Dim cmd As New System.Data.OleDb.OleDbCommand()
cmd.Connection = conn
cmd.CommandText = "UPDATE [EmployeeData$F3:G3] SET F1 = 'Cell F3', F2 = 'Cell G3'"
cmd.ExecuteNonQuery()
cmd.CommandText = "UPDATE [EmployeeData$I4:I4] SET F1 = 'Cell I4'"
cmd.ExecuteNonQuery()
conn.Close()
End Sub
Public Sub Use_External_Source()
' Open a connection to the sample Northwind Access database.
Dim conn As New System.Data.OleDb.OleDbConnection( _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & m_sNorthwind & ";")
conn.Open()
Dim cmd As New System.Data.OleDb.OleDbCommand()
cmd.Connection = conn
'=======================================================================
' Run an INSERT..INTO command on the Northwind database to append
' the records from a table/query to an existing table in the Excel
' workbook.
'=======================================================================
cmd.CommandText = "INSERT INTO [EmployeeData$] IN 'C:\ExcelData1.xls' 'Excel 8.0;'" & _
"SELECT 社員コード AS ID, 氏名 AS Name, 誕生日 AS BirthDate FROM 社員"
cmd.ExecuteNonQuery()
'==========================================================================
' Run a SELECT..INTO command on the Northwind database to insert
' all the records from a table/query into a new sheet in the Excel
' workbook.
'==========================================================================
cmd.CommandText = "SELECT * INTO [Excel 8.0;Database=C:\ExcelData2.xls].[ProductSales]" & _
"FROM [1997年 売上高]"
cmd.ExecuteNonQuery()
conn.Close()
End Sub

'Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

'End Sub
End Class