need to execute bcp command through C#.net - URGENT

Question

Post reply

need to execute bcp command through C#.net - URGENT

mamidimahesh-127681

SSC Eights!

Points: 868
More actions
December 17, 2004 at 5:26 am

#162869

Hi All,
I am developing a browser application using ASP.NET and code behind is C#.net. On clicking of a button it should get the data from database (remote Server) and create a tab delimited file in my local folder. For this I want to use bcp command. Can anyone help me in this

Thanks in Advance,
Mahesh

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

mamidimahesh-127681 SSC Eights! Points: 868 More actions · Answer 1

Hi

If anybody is there from .net development, please help me.......

Mahesh

David Burrows SSC Guru Points: 65138 More actions · Answer 2

By 'Local Drive' I am assuming to mean the client and not the server

If so, then you have to run bcp via scripting in the html not in the code behind file.

e.g.

Set wshShell = CreateObject ("WSCript.shell")

wshshell.run """c:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp.exe"" ""select * from sysobjects"" queryout c:\temp\outputfile /c /t ""\t"" /r ""\n"" /T /S servername", 4, True

set wshshell = nothing

</script>

Far away is close at hand in the images of elsewhere.
Anon.

mamidimahesh-127681 SSC Eights! Points: 868 More actions · Answer 3

As you said, it should execute at client side only. But at the same time we can not except someone to have this bcp.exe at their local system, right ?

But I have to use C# as front-end

Mahesh

David Burrows SSC Guru Points: 65138 More actions · Answer 4

Yes, for that to work the end user will have to have bcp.exe. What you could do is create a random filename file on the server, put the data into that file using ADO (or BCP if you prefer) and make the button refer to that file to allow the end user to download it.

Far away is close at hand in the images of elsewhere.
Anon.

ramesh.tce SSC Rookie Points: 47 More actions · Answer 5

I also have same issue.Can anyone help me to export data from sql sever to the temp file in sever using bcp? in c#.net

Thanks

G-472796 SSC Enthusiast Points: 185 More actions · Answer 6

We have several ways of accomplishing the same tasks, for smaller datasets we use Client-side tables and write the data there. For very large applications, some with special requirements, we do use bcp. When we use bcp, we write from a staging table to temporary storage on the server where a file watcher creates a .ZIP package of all the required outputs; moves the ZIP file to our ftp site and notifies the recipient via e-mail that the file is ready for pickup.

As far as the SQL goes, the user executes a stored procedure to create the dataset he is interested in. The end of that procedure executes the bcp command.

Here us one version:

DECLARE @sSQL nvarchar(800)

DECLARE @DatabaseName nvarchar(20)

DECLARE @MeasurementCode nvarchar(20)

DECLARE @OrderID int

DECLARE @txtReplicate nvarchar(20)

SET @sql = 'bcp "exec [' + @DatabaseName + '].dbo.spPushOutput Queryout D:\TemporaryStorageArea\' + cast(@OrderID as varchar(10)) + '_' + @MeasurementCode + '_' + ltrim(@txtReplicate) + '_Output.csv -c -t, -T'

Exec master..xp_cmdshell @sql

You will need to enable the xp_cbdshell from the surface area configrateion.

You could replace this clause: "exec [' + @DatabaseName + '].dbo.spPushOutput

with a select statement.

Hope that helps.

David Burrows SSC Guru Points: 65138 More actions · Answer 7

ramesh.tce (9/8/2008)
I also have same issue.Can anyone help me to export data from sql sever to the temp file in sever using bcp? in c#.netThanks

If you are using c# in a code behind file then don't use BCP but use sql data reader instead, e.g.

SqlConnection Conn = new SqlConnection("connection string here");

SqlCommand Comm = new SqlCommand();

Conn.Open();

Comm.Connection = Conn;

Comm.CommandType = CommandType.StoredProcedure;

Comm.Parameters.Clear();

Comm.CommandText = "procedure name here";

SqlDataReader dr = Comm.ExecuteReader;

string strFilename = System.IO.Path.GetTempFileName;

System.IO.StreamWriter sw = System.IO.File.CreateText(strFilename);

while (dr.Read()) {

// use sw.WriteLine to output each item with required delimiter

}

sw.Close();

sw.Dispose();

sw = null;

dr.Close();

dr = null;

Conn.Close();

Conn.Dispose();

Conn = null;

Then use strFilename in the url for download

Far away is close at hand in the images of elsewhere.
Anon.