none
bcp utility using c# ?? RRS feed

  • Question

  • Hi,

     

    I want to use bcp utility using c#.

    presently I am using like this. Here I am using Bulkcopy class .

    How will I use BCP utility so that I can get the full feature of BCP

     

    using (SqlBulkCopy BulkCopy = new SqlBulkCopy(_ConnectionString))
                    {
                        BulkCopy.BatchSize = BatchSize;
                        BulkCopy.DestinationTableName = TargetTableName;
                        BulkCopy.WriteToServer(dsTarget.Tables[0]);
                        _iRowsCopied = 0;
                        _iRowsCopied = dsTarget.Tables[0].Rows.Count;
                    }

     

    Thanks

    --Prakash



     

    Friday, May 23, 2008 11:51 AM

Answers

  • There is difference between BCP and Bulk Insert

    I got the answer


    string sCommandText;

    sCommandText = "exec xp_cmdShell 'bcp.exe'"+Database + ".." + TableName + " in "  +
                     FileName + " -c -q -U " + UserId + " -P " + Password +  "-t  ";
                
               SqlConnection conn = new SqlConnection(ConnectionString);
               conn.Open(); 

               SqlCommand cmd = new SqlCommand();
               cmd.Connection = conn;
               cmd.CommandType = CommandType.Text;
               cmd.CommandText = sCommandText;

               return cmd.ExecuteNonQuery();

    Tuesday, May 27, 2008 10:42 AM

All replies

  • You could execute BCP using BULK INSERT Transact-SQL statement and you could do it using ExecuteNonQuery method of SqlCommand.

    Saturday, May 24, 2008 2:20 AM
    Moderator
  • You can also use SMO or SSIS to create a package that will perform the bulk operations as well.

     

    Saturday, May 24, 2008 7:10 PM
    Moderator
  •  

    yes.. I can use SMO or SSIS... but I am using BCP. now the question is how to execute BCP through c# code.

    If we will use SMO Or SSIS how to execute it through C# code.

     

    e.g

    If I have a querry Select col1,col2 from Table1 where col1=5. I want to execute it through c# code then we have to use Ado.Net and execute the code. step involves are.

     

    step1 : create connection , open connection

    step2 : create command , assign command text

    step3 : using execute scalar or nonquerry execute command

     

    there are some different ways also as use stored proc etc..

     

    now my question is how we will execute the BCP command line utility from c# code?

     

     

    This is the bcp utility. I want to execute this through C# code

     

    Source MSDN http://msdn.microsoft.com/en-us/library/ms162802.aspx

     

    bcp Utility

    The bcp utility bulk copies data between an instance of Microsoft SQL Server 2005 and a data file in a user-specified format. The bcp utility can be used to import large numbers of new rows into SQL Server tables or to export data out of tables into data files. Except when used with the queryout option, the utility requires no knowledge of Transact-SQL. To import data into a table, you must either use a format file created for that table or understand the structure of the table and the types of data that are valid for its columns.

     For the bcp syntax conventions, see Command Prompt Utilities.

    bcp {[[database_name.][owner].]{table_name | view_name} | "query"}
        {in | out | queryout | format} data_file
        [-mmax_errors] [-fformat_file] [-x] [-eerr_file]
        [-Ffirst_row] [-Llast_row] [-bbatch_size]
        [-n] [-c] [-N] [-w] [-V (60 | 65 | 70 | 80)] [-6] 
        [-q] [-C { ACP | OEM | RAW | code_page } ] [-tfield_term] 
        [-rrow_term] [-iinput_file] [-ooutput_file] [-apacket_size]
        [-Sserver_name[\instance_name]] [-Ulogin_id] [-Ppassword]
        [-T] [-v] [-R] [-k] [-E] [-h"hint [,...n]"]

     

    Tuesday, May 27, 2008 6:06 AM
  • If you want to launch BCP as an executable that you could use Start method of Process class. It allows to start separate propcess from existing executable files. But I would suggest to use BULK INSERT SQL statement, since it is same BCP, but you will have more control in your code.

     

    Tuesday, May 27, 2008 9:46 AM
    Moderator
  • There is difference between BCP and Bulk Insert

    I got the answer


    string sCommandText;

    sCommandText = "exec xp_cmdShell 'bcp.exe'"+Database + ".." + TableName + " in "  +
                     FileName + " -c -q -U " + UserId + " -P " + Password +  "-t  ";
                
               SqlConnection conn = new SqlConnection(ConnectionString);
               conn.Open(); 

               SqlCommand cmd = new SqlCommand();
               cmd.Connection = conn;
               cmd.CommandType = CommandType.Text;
               cmd.CommandText = sCommandText;

               return cmd.ExecuteNonQuery();

    Tuesday, May 27, 2008 10:42 AM
  •  Prakash4 wrote:

    There is difference between BCP and Bulk Insert

    I got the answer


    string sCommandText;

    sCommandText = "exec xp_cmdShell 'bcp.exe'"+Database + ".." + TableName + " in "  +
                     FileName + " -c -q -U " + UserId + " -P " + Password +  "-t  ";
                
               SqlConnection conn = new SqlConnection(ConnectionString);
               conn.Open(); 

               SqlCommand cmd = new SqlCommand();
               cmd.Connection = conn;
               cmd.CommandType = CommandType.Text;
               cmd.CommandText = sCommandText;

               return cmd.ExecuteNonQuery();

     

    Thank you Prakash.  I found this post when I had the exact same question.

    Friday, June 13, 2008 7:30 PM
  • Hi Prakash4,

    Sorry, but could I run this with SQL Azure? Is there any physical path for output file?

    Nam Trung

    Thursday, January 2, 2014 9:36 AM