none
How to do BCP from VB.NET? RRS feed

  • Question

  •  

    Hi All,

     

    I am converting a VB4 application to VB.NET 2005. In this application, for data access the good old Db-Library is used. Now this feature is no more supported in SQL Server 2005 (DB is also upgraded as part of our project), I would like to replace DB-Library with ADO.NET.

     

    This application does two things by using BCP.

     

    1. Transfers data from one text file to a SQL Server table.

    2. Transfers data from a SQL Server table to a text file.

     

    At first it seems simple. By doing some research, I found out that SQLBulCopy class can be used to do BCP operations in ADO.NET.

     

    The first task, I can do by reading data from the tex file, set the DestinationTable property of SqlBulCopy object and then calling WriteServer. But what should I do for the second task?

     

    For copying data from a table to text file, what should I set for DestinationTable property? What are the steps to be done? I did some googling, but couldn't find any help.

     

    I found one sample code in MSDN where it shows how to copy from one SQL Server Database to another. No sample code can be found for copying between DB Table and Text File. Is there any sample code for this on Internet?

     

    Could any one of you answer my questions? (I have put them bold). Any comment is highly appreciated.

     

    Thanks,

     

    Kishore

     

    Wednesday, January 9, 2008 12:35 PM

Answers

  • The SQLBulkCopy class is for loading data into a SQL Server table from another data source. It only operates in that direction.

     

    If you want to export to another data source from SQL Server there are a couple of ways to do this. I've found the best method is to just use SQL and OLEDB/ODBC:

     

    Code Block

    Dim TextConnection As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _

    "Data Source=" & "E:\My Documents\TextFiles" & ";" & _

    "Extended Properties=""Text;HDR=NO;""")

     

    TextConnection.Open()

     

    Dim TextCommand As New System.Data.OleDb.OleDbCommand("SELECT * INTO [Orders#txt] FROM [Orders] IN '' [ODBC;Driver={SQL Server};Server=(local);Database=Northwind;Trusted_Connection=yes];", TextConnection)

     

    TextCommand.ExecuteNonQuery()

     

    TextConnection.Close()

     

     

     

    Wednesday, January 9, 2008 1:23 PM

All replies

  • The SQLBulkCopy class is for loading data into a SQL Server table from another data source. It only operates in that direction.

     

    If you want to export to another data source from SQL Server there are a couple of ways to do this. I've found the best method is to just use SQL and OLEDB/ODBC:

     

    Code Block

    Dim TextConnection As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _

    "Data Source=" & "E:\My Documents\TextFiles" & ";" & _

    "Extended Properties=""Text;HDR=NO;""")

     

    TextConnection.Open()

     

    Dim TextCommand As New System.Data.OleDb.OleDbCommand("SELECT * INTO [Orders#txt] FROM [Orders] IN '' [ODBC;Driver={SQL Server};Server=(local);Database=Northwind;Trusted_Connection=yes];", TextConnection)

     

    TextCommand.ExecuteNonQuery()

     

    TextConnection.Close()

     

     

     

    Wednesday, January 9, 2008 1:23 PM
  •  

    Thank you very much Paul.

     

    But the underlying bcp.exe supports both loading data to SQL Server table and loading to a text file from SQL SErver table. As I need transferring data both ways, I would call bcp.exe from .NET code.

     

    Thanks once again for the info.

     

    Kishore.

    Friday, January 11, 2008 3:51 PM