none
SqlBulkCopy class with .mdb file RRS feed

  • Question

  •  

    Hi,

     

    Can anyone please provide an example how to use the Sqlbulkcopy class to copy data from .mdb file to sql server ?

     

     

    Tuesday, March 4, 2008 11:42 AM

Answers

  • Below is an example which uses an Access database for the source table:

    Code Snippet

            Dim SQLConnectionString As String = "Data Source=(local);" & _
                                "Initial Catalog=Northwind;" & _
                                "Integrated Security=SSPI"

            ' Open a connection to the Northwinds database.
            Using SourceConnection As SqlConnection = _
               New SqlConnection(SQLConnectionString)
                SourceConnection.Open()

                ' Perform an initial count on the destination table.
                Dim CommandRowCount As New SqlCommand( _
                "SELECT COUNT(*) FROM dbo.Orders2;", _
                    SourceConnection)
                Dim CountStart As Long = _
                   System.Convert.ToInt32(CommandRowCount.ExecuteScalar())
                Console.WriteLine("Starting row count = {0}", CountStart)

                ' Get data from the source table as a AccessDataReader.
                Dim AccessConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                                            "Data Source=C:\Test Files\db1 XP.mdb")

                Dim AccessCommand As New OleDbCommand("SELECT * FROM Orders2", AccessConnection)
                AccessConnection.Open()
                Dim AccessDataReader As OleDbDataReader = AccessCommand.ExecuteReader(CommandBehavior.SequentialAccess)

                ' Open the destination connection.
                Using DestinationConnection As SqlConnection = _
                    New SqlConnection(SQLConnectionString)
                    DestinationConnection.Open()

                    ' Set up the bulk copy object.
                    ' The column positions in the source data reader
                    ' match the column positions in the destination table,
                    ' so there is no need to map columns.
                    Using BulkCopy As SqlBulkCopy = _
                      New SqlBulkCopy(DestinationConnection)
                        BulkCopy.DestinationTableName = _
                        "dbo.Orders2"

                        Try
                            ' Write from the source to the destination.
                            BulkCopy.WriteToServer(AccessDataReader)

                        Catch ex As Exception
                            Console.WriteLine(ex.Message)

                        Finally
                            ' Close the AccessDataReader. The SqlBulkCopy
                            ' object is automatically closed at the end
                            ' of the Using block.
                            AccessDataReader.Close()
                        End Try
                    End Using

                    ' Perform a final count on the destination table
                    ' to see how many rows were added.
                    Dim CountEnd As Long = _
                        System.Convert.ToInt32(CommandRowCount.ExecuteScalar())
                    Console.WriteLine("Ending row count = {0}", CountEnd)
                    Console.WriteLine("{0} rows were added.", CountEnd - CountStart)
                End Using
            End Using


    Friday, March 14, 2008 8:05 PM

All replies

  • I need more info MDB file is Access database file while SQL Server file is MDF so tell me what you want to do and I will try to help you.

     

    Friday, March 14, 2008 2:33 PM
  • Below is an example which uses an Access database for the source table:

    Code Snippet

            Dim SQLConnectionString As String = "Data Source=(local);" & _
                                "Initial Catalog=Northwind;" & _
                                "Integrated Security=SSPI"

            ' Open a connection to the Northwinds database.
            Using SourceConnection As SqlConnection = _
               New SqlConnection(SQLConnectionString)
                SourceConnection.Open()

                ' Perform an initial count on the destination table.
                Dim CommandRowCount As New SqlCommand( _
                "SELECT COUNT(*) FROM dbo.Orders2;", _
                    SourceConnection)
                Dim CountStart As Long = _
                   System.Convert.ToInt32(CommandRowCount.ExecuteScalar())
                Console.WriteLine("Starting row count = {0}", CountStart)

                ' Get data from the source table as a AccessDataReader.
                Dim AccessConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                                            "Data Source=C:\Test Files\db1 XP.mdb")

                Dim AccessCommand As New OleDbCommand("SELECT * FROM Orders2", AccessConnection)
                AccessConnection.Open()
                Dim AccessDataReader As OleDbDataReader = AccessCommand.ExecuteReader(CommandBehavior.SequentialAccess)

                ' Open the destination connection.
                Using DestinationConnection As SqlConnection = _
                    New SqlConnection(SQLConnectionString)
                    DestinationConnection.Open()

                    ' Set up the bulk copy object.
                    ' The column positions in the source data reader
                    ' match the column positions in the destination table,
                    ' so there is no need to map columns.
                    Using BulkCopy As SqlBulkCopy = _
                      New SqlBulkCopy(DestinationConnection)
                        BulkCopy.DestinationTableName = _
                        "dbo.Orders2"

                        Try
                            ' Write from the source to the destination.
                            BulkCopy.WriteToServer(AccessDataReader)

                        Catch ex As Exception
                            Console.WriteLine(ex.Message)

                        Finally
                            ' Close the AccessDataReader. The SqlBulkCopy
                            ' object is automatically closed at the end
                            ' of the Using block.
                            AccessDataReader.Close()
                        End Try
                    End Using

                    ' Perform a final count on the destination table
                    ' to see how many rows were added.
                    Dim CountEnd As Long = _
                        System.Convert.ToInt32(CommandRowCount.ExecuteScalar())
                    Console.WriteLine("Ending row count = {0}", CountEnd)
                    Console.WriteLine("{0} rows were added.", CountEnd - CountStart)
                End Using
            End Using


    Friday, March 14, 2008 8:05 PM