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

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 t开发者_高级运维o create the data file in native BCP format.

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

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!

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.

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.

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

assuming you only have fixed with column types in the destination table, nothing nullable, no unicode strings, and are willing to handle endian-ness, then the native file format is just the bytes of the types.

i recently bulk imported data from a c# script by writing a staging file byte-by-byte and using BCP

bcp destTable in model.raw -T -S _serverName -n

model.raw iscreated byte-wise by:

fileBytes = new byte[theLength * 4]; // * 4 bytes per element for int and float
var offset =0;

    foreach (var element in outputDimensions)
    {
        // fastCopy is a faster and "Unsafe" equivelent of BlockCopy , faster because it doesn't create an intermediate byte array.
        //Buffer.BlockCopy(BitConverter.GetBytes(profileid), 0, fileBytes, offset, 4);
        Utilities.fastCopy(profileid, fileBytes, offset);
        offset += 4;
        Utilities.fastCopy(element.index, fileBytes, offset);
        offset += 4;
        for (var i = 0; i < TimeSlices; i++, offset += 4)
        {
            float target = GetDataForTime(i,...);
            Utilities.fastCopy(target, fileBytes, offset); 
        }
    }

FileStream dataWriter.Write(fileBytes , 0, byteArray.Length);