'Namespaces in use.
Imports System.Data
Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports ADODB
Imports System.IO
Module Code
'An untyped dataset that is created and used only during the runtime.
'It's the 'container' for the retrieved data which is then added to
'the database.
Private dsXLData As New DataSet("XL")
Sub Main()
'Define the directory.
Dim Folder As New DirectoryInfo("c:\Data")
Dim File As FileInfo
'Iterate through the files in the directory.
'If it's an Excel file the filename is sent to the
'procedure to retrieve the wanted data from the file.
For Each File In Folder.GetFiles()
If File.FullName Like "*.xlsx" Then
Get_XL_Data(File.ToString)
End If
Next
'Here we call the procedure to update the database.
UpDate_SQL2005EE_DB()
End Sub
Private Sub Get_XL_Data(ByVal FileName As String)
'The SQL statements.
'The Provider does not support multiply SQL statements and
'therefore two separetly calls must be made.
Dim sqlData As String = "SELECT * FROM [Data$]"
Dim sqlProduction As String = "SELECT * FROM [Production$];"
'The connection string.
Dim xlCon As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=c:\Data\" & FileName & ";" & _
"Extended Properties=""Excel 12.0;HDR=YES"""
'Declare and instantiate the connection object.
Dim con As New OleDbConnection(xlCon)
'Declare and instantiate the command object.
Dim com As New OleDbCommand()
'Declare and instantiate a local untyped dataset.
Dim ds As New DataSet
'Declare and instantiate a DataAdapter which fill the dataset with data.
Dim da As New OleDbDataAdapter
'Add values to the command object's properties.
With com
.Connection = con
.CommandType = CommandType.Text
.CommandText = sqlData
End With
With da
'By setting it to False every record appears to be as new
'which make it possible to add the data from the Dataset.
.AcceptChangesDuringFill = False
.SelectCommand = com
'Add data to the dataset.
.Fill(ds, "Status")
com.CommandText = sqlProduction
'Add data to the dataset.
.Fill(ds, "Production")
End With
'Merge data from the local dataset to the general dataset.
dsXLData.Merge(ds)
'Cleaning up.
ds.Dispose()
da.Dispose()
con.Dispose()
End Sub
Private Sub UpDate_SQL2005EE_DB()
'The connection string
Const conSales As String = "Data Source=.\SQLEXPRESS;" & _
"Initial Catalog=Sales;" & _
"Integrated Security=True"
'Declare the variable and instantiate which open up the connection.
Dim sqlcon As New SqlConnection(conSales)
'Declare the variables and instantiate the commands.
Dim comStatus As New SqlCommand( _
"INSERT INTO Status (SalesPerson, Client, Status) VALUES (@SalesPerson, @Client, @Status)", sqlcon)
Dim comProduction As New SqlCommand( _
"INSERT INTO Production (Line, Output, Duration) VALUES (@Line, @Output, @Duration)", sqlcon)
'Declare the variable and instantiate a new instance of the SqlDataAdapter.
Dim dasql As New SqlDataAdapter
'Add the necessary parameters and their values.
With comStatus
.Parameters.Add("@SalesPerson", SqlDbType.NChar, 10, "SalesPerson")
.Parameters.Add("@Client", SqlDbType.SmallInt, 16, "Client")
.Parameters.Add("@Status", SqlDbType.SmallInt, 16, "Status")
'As the sample doesn't update any existing records the option None
'is used here. The UpdatedRowSource is necessary as the example
'use the UpdateBatchSize.
.UpdatedRowSource = UpdateRowSource.None
End With
With comProduction
.Parameters.Add("@Line", SqlDbType.SmallInt, 16, "Line")
.Parameters.Add("@Output", SqlDbType.Int, 8, "Output")
.Parameters.Add("@Duration", SqlDbType.SmallInt, 16, "Duration")
.UpdatedRowSource = UpdateRowSource.None
End With
'Add the data to the database.
With dasql
'By setting the size to 0 the procedure add them in a chunk.
.UpdateBatchSize = 0
.InsertCommand = comStatus
.Update(dsXLData, "Status")
'By setting the size to 0 the procedure add them in a chunk.
.UpdateBatchSize = 0
.InsertCommand = comProduction
.Update(dsXLData, "Production")
End With
'Remove all the data from the untyped Dataset.
With dsXLData
.Clear()
.Dispose()
End With
'Cleaning up.
dasql.Dispose()
comStatus.Dispose()
comProduction.Dispose()
sqlcon.Dispose()
End Sub
End Module
