none
Execute BCP Out from C# RRS feed

  • Question

  • Hi Experts,

    Could you pleases share a simple example . I have a table like this in SQL Server. I need to execute these query and output its data using bcp. withformat output_Getdate(). Please share how to execute the below in C# on a SQL Server instance.

    SQL_Query SQL_Instance
    SELECT Columns FROM Table1 LocalHost
    SELECT Columns FROM Table2 LocalHost
    SELECT Columns FROM Table3 LocalHost

    bcp "Select Query from above table" queryout C:\Data\output_Getdate().txt -c -t, -S localhost -T 

    Wednesday, November 27, 2019 2:57 PM

Answers

  • Hi Priya,

    Thanks for your feedback.

    When you have finished querying the database, you should get a datatable.

    Use the loop to execute the above code, and then stitch the different SQL statements into the bcp command.

    And what do you mean “datetime myfile_datetime format”? Do you mean stitch the time into the file name?

    If so, please refer to the following code.

    static void Main(string[] args)
            {
                DataTable table = GetData();
                foreach (DataRow item in table.Rows)
                {
                    Process p = new Process();
                    p.StartInfo.FileName = "cmd.exe";
    
                    p.StartInfo.UseShellExecute = false;
                    p.StartInfo.RedirectStandardInput = true;
                    p.StartInfo.RedirectStandardOutput = true;
                    p.StartInfo.RedirectStandardError = true;
                    p.StartInfo.CreateNoWindow = true;
                    long dataTime = DateTime.Now.Ticks;
                    string arg = "/c " + " bcp \"" + item["SQL_Query"] + "\" queryout d:\\test\\testBCP_" + dataTime + ".txt -T -c -S " + item["SQL_Instance"];
                    p.StartInfo.Arguments = arg;
                    p.Start();
                }
            }
    
            static DataTable GetData()
            {
                DataTable table = new DataTable();
                table.Columns.Add("SQL_Query", typeof(string));
                table.Columns.Add("SQL_Instance", typeof(string));
    
                table.Rows.Add("select * from Student.dbo.Stu", "localhost");
                table.Rows.Add("select * from Student.dbo.Emp", "localhost");
                table.Rows.Add("select * from Student.dbo.Address", "localhost");
    
                return table;
            }

    The default format of DateTime cannot be used as part of the file name, so some processing is required.

    Hope this could be helpful.

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Thursday, November 28, 2019 6:24 AM

All replies

  • Hi Priya,

    Thank you for posting here.

    For your question, you want to You want to execute bcp command in C#.

    You can use Process to call cmd and then execute this command.

    Here is a code example:

           static void Main(string[] args)
            {
                Process p = new Process();
    
                p.StartInfo.FileName = "cmd.exe";          
                p.StartInfo.Arguments = "/c " + " bcp \"select * from Student.dbo.stu\" queryout d:\\test\\testBCP.txt -T -c -S localhost";    
                p.StartInfo.UseShellExecute = false;        
                p.StartInfo.RedirectStandardInput = true;   
                p.StartInfo.RedirectStandardOutput = true;  
                p.StartInfo.RedirectStandardError = true;   
                p.StartInfo.CreateNoWindow = true;
    
                p.Start(); 
    
            }

    I didn't write the steps to query the database tables because I believe you can do it.

    Hope this could be helpful.

    Best Regards, 

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, November 28, 2019 3:11 AM
  • Hi Priya,

    Thank you for posting here.

    For your question, you want to You want to execute bcp command in C#.

    You can use Process to call cmd and then execute this command.

    Here is a code example:

           static void Main(string[] args)
            {
                Process p = new Process();
    
                p.StartInfo.FileName = "cmd.exe";          
                p.StartInfo.Arguments = "/c " + " bcp \"select * from Student.dbo.stu\" queryout d:\\test\\testBCP.txt -T -c -S localhost";    
                p.StartInfo.UseShellExecute = false;        
                p.StartInfo.RedirectStandardInput = true;   
                p.StartInfo.RedirectStandardOutput = true;  
                p.StartInfo.RedirectStandardError = true;   
                p.StartInfo.CreateNoWindow = true;
    
                p.Start(); 
    
            }

    I didn't write the steps to query the database tables because I believe you can do it.

    Hope this could be helpful.

    Best Regards, 

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Hello Timon,

    Thank you so much for the support. Could you please help with other listed support requirement.

    1. I need to pass the queries SQL_Query column 1 by 1 and execute them as BCP.

    2. The BCP file generated should have a datetime myfile_datetime format.

    Am very thankful for the quick support. If you could help with the above also, it will help me to finish an important task.

    Thanks again

    Priya

    Thursday, November 28, 2019 3:39 AM
  • Hi Priya,

    Thanks for your feedback.

    When you have finished querying the database, you should get a datatable.

    Use the loop to execute the above code, and then stitch the different SQL statements into the bcp command.

    And what do you mean “datetime myfile_datetime format”? Do you mean stitch the time into the file name?

    If so, please refer to the following code.

    static void Main(string[] args)
            {
                DataTable table = GetData();
                foreach (DataRow item in table.Rows)
                {
                    Process p = new Process();
                    p.StartInfo.FileName = "cmd.exe";
    
                    p.StartInfo.UseShellExecute = false;
                    p.StartInfo.RedirectStandardInput = true;
                    p.StartInfo.RedirectStandardOutput = true;
                    p.StartInfo.RedirectStandardError = true;
                    p.StartInfo.CreateNoWindow = true;
                    long dataTime = DateTime.Now.Ticks;
                    string arg = "/c " + " bcp \"" + item["SQL_Query"] + "\" queryout d:\\test\\testBCP_" + dataTime + ".txt -T -c -S " + item["SQL_Instance"];
                    p.StartInfo.Arguments = arg;
                    p.Start();
                }
            }
    
            static DataTable GetData()
            {
                DataTable table = new DataTable();
                table.Columns.Add("SQL_Query", typeof(string));
                table.Columns.Add("SQL_Instance", typeof(string));
    
                table.Rows.Add("select * from Student.dbo.Stu", "localhost");
                table.Rows.Add("select * from Student.dbo.Emp", "localhost");
                table.Rows.Add("select * from Student.dbo.Address", "localhost");
    
                return table;
            }

    The default format of DateTime cannot be used as part of the file name, so some processing is required.

    Hope this could be helpful.

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Thursday, November 28, 2019 6:24 AM
  • Hi Priya,

    Thanks for your feedback.

    When you have finished querying the database, you should get a datatable.

    Use the loop to execute the above code, and then stitch the different SQL statements into the bcp command.

    And what do you mean “datetime myfile_datetime format”? Do you mean stitch the time into the file name?

    If so, please refer to the following code.

    static void Main(string[] args)
            {
                DataTable table = GetData();
                foreach (DataRow item in table.Rows)
                {
                    Process p = new Process();
                    p.StartInfo.FileName = "cmd.exe";
    
                    p.StartInfo.UseShellExecute = false;
                    p.StartInfo.RedirectStandardInput = true;
                    p.StartInfo.RedirectStandardOutput = true;
                    p.StartInfo.RedirectStandardError = true;
                    p.StartInfo.CreateNoWindow = true;
                    long dataTime = DateTime.Now.Ticks;
                    string arg = "/c " + " bcp \"" + item["SQL_Query"] + "\" queryout d:\\test\\testBCP_" + dataTime + ".txt -T -c -S " + item["SQL_Instance"];
                    p.StartInfo.Arguments = arg;
                    p.Start();
                }
            }
    
            static DataTable GetData()
            {
                DataTable table = new DataTable();
                table.Columns.Add("SQL_Query", typeof(string));
                table.Columns.Add("SQL_Instance", typeof(string));
    
                table.Rows.Add("select * from Student.dbo.Stu", "localhost");
                table.Rows.Add("select * from Student.dbo.Emp", "localhost");
                table.Rows.Add("select * from Student.dbo.Address", "localhost");
    
                return table;
            }

    The default format of DateTime cannot be used as part of the file name, so some processing is required.

    Hope this could be helpful.

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Thank you so much ! for all the support
    Thursday, November 28, 2019 7:55 AM