I have two .dat files that were generated through bcp (of two tables). I've been assigned with (within an EF seed or migration) taking this data and populating the database appropriately.
I've searched SO and several other sites regarding this task, and I don't seem to have any clear picture as to how to get the data out of the .dat file (although I've seen some bits about the old ADO.NET DataTable()s being used).
Essentially, my question is: how can I either
a) perform a BCP within C#, given a .dat file with some table data, or
b) grab an in-memory version of that .dat file with the data that I can 'manually' deal with?
The command-line BCP utility can generate files of different formats. In the case of character mode, you can parse the file in your C# code as you would any other text file. However, the file does not contain any meta-data so you will need to know the fields and format in order to parse and process.
BCP can also generate a file in native format. The native format is an undocumented binary format and intended to be consumed only by BCP. If the .dat file is a native file, your best option would be to create a staging table with the same columns as the source table and import using BCP native mode. You can shell out to BCP if you need to do that directly from your C# application. Below is an example of that technique.
executeBcp(@"C:\temp\data.dat in /n /S ServerName /T");
private static int executeBcp(string commandArguments)
{
var bcpProcess = new System.Diagnostics.Process();
bcpProcess.StartInfo = new System.Diagnostics.ProcessStartInfo("BCP.EXE", commandArguments);
bcpProcess.StartInfo.CreateNoWindow = true;
bcpProcess.StartInfo.UseShellExecute = false;
bcpProcess.StartInfo.RedirectStandardOutput = true;
bcpProcess.Start();
var result = bcpProcess.StandardOutput.ReadToEnd();
bcpProcess.WaitForExit();
return bcpProcess.ExitCode;
}