Is it possible to create a SQL Server native file from c# (like BCP native format)

Question

We are upgrading a 15 year old code-base, there is a requirement to create some native BCP formatted data files.

In the new system, we would ideally like to utilize data in a C# DataTable object to create the data file in native BCP format.

Can this be done and if so, what would be the best approach?

Answer 1

Are you able to put the data in the datatable into a Staging area on a SQL Server? If so, you can spawn off a BCP process.

BCP: http://msdn.microsoft.com/en-us/library/aa174646%28SQL.80%29.aspx

For example, I use:

BCP Database.Schema.TableName OUT FileName.Ext -S ServerName -T -n

Switches:

• -S is for server
• -T is for trusted connection
• -n is for Native format

EDIT + New Idea:

If you have access to the queries that fill up the datatable, you could use those with BCP or SQLDMO to export out a native format file. The following queries use the QUERYOUT switch instead of the OUT switch because they contain inline queries

Export specific columns from a view:

BCP "SELECT Column1, Column2 FROM MyViewName" QUERYOUT FileName.Ext -S ServerName -T -n

Export specific columns from a JOIN:

BCP "SELECT Table1.Column1, Table2.Column2 FROM Table1 INNER JOIN Table2 on Table1.Column33 = Table2.Column33" QUERYOUT FileName.Ext -S ServerName -T -n

Answer 2

If it is that old, then BCP piece may look very much like a Sybase BCP piece. In Sybase I'd start looking at client libaries and shipped examples of code using BCP API. CTLib and/or Java jars for corresponding API. For Microsoft there might be a similar portions of native C or Basic API involving BCP. You may not need the communication part of API, just record-by-record preparing and reads/writes to file.

If there is no such thing, then I'd consider non-native format with hand made FMT files and text-like data files, produced/consumed by original BCP program.

Answer 3

There is a similar way to do this, but you have to reference SQLDMO and it will create an equivalent format just like BCP. By using the BulkCopy object of the SQLDMO, you can do what you are looking for. Here's a link to the routine done in vbscript which uses the SQLDMO library here.

Hope this helps, Best regards, Tom.

Answer 4

No, it can't be done, the only way we've discovered to create native BCP files is by using SQL Server. If anyone develops or finds another way please post it here!