BCP for bulk insert from falt file to the database in C#

[srilatk]

Hi,
I have the followuing code that bulks inserts data in the .fmt files in a folder to the database tables. But, when I run it, the data from the files is not copied to database properly. It copies only data from one file and leaves the rest.

Is it becuase of sleep()? Pls respond. I am struggling with this problem a lot.

foreach (string file in fileList) //this has .fmt files to be bulk copied to database
{
System.Diagnostics.Process proc = null;
bool rVal = false;
try
{
Logger.WriteMessage(string.Format("Populate data in DET_H tables {0}", file), logEventCategory, (int)LogPriority.LogAll);
strSourceFileName = strparam;
strparam = "prd_pos_ej.dbo.ej_tran_det_h in ";
strparam += file;
strparam += " -f ";
strparam += m_strSourceFilePath;
strparam += "\\ejDet_h.fmt";

string sDataSource = GetValueFromConnectionString("Data Source", connectionstr);
string sUserid = GetValueFromConnectionString("User ID", connectionstr);
string sPassword = GetValueFromConnectionString("Password", connectionstr);

strparam += " -S";
strparam += sDataSource;
strparam += " -U";
strparam += sUserid;
strparam += " -P";
strparam += sPassword;

Logger.WriteMessage(string.Format("BCP command{0}", strparam.ToString()), logEventCategory, (int)LogPriority.LogReads);
proc = new System.Diagnostics.Process();
proc.EnableRaisingEvents = false;
proc.StartInfo.UseShellExecute = false;
//proc.StartInfo.WorkingDirectory = archiveFolder;
proc.StartInfo.RedirectStandardOutput = true;
proc.StartInfo.Arguments = strparam.ToString();
proc.StartInfo.FileName = "bcp";
proc.Start();

Logger.WriteMessage(string.Format("BCP standard output:{0}", proc.StandardOutput.ReadToEnd()), logEventCategory, (int)LogPriority.LogStatus);
int ticks = Environment.TickCount;
if ((proc.HasExited == false) && (ticks + 600000 > Environment.TickCount))
{
System.Threading.Thread.Sleep(2000); //2 seconds
}
rVal = true;
}
catch (Exception ex)
{
Logger.WriteMessage(string.Format("BCP command failed {0}", ex.ToString()), logEventCategory, (int)LogPriority.LogErrors);
Logger.WriteMessage(string.Format("BCP command failed {0}", ex.ToString()), logCategory, (int)LogPriority.LogErrors);
rVal = false;
}
finally
{
if (proc != null)
{
proc.Close();
}
}
return rVal;
}

PS: works fine if only 1 file is in the folder.

[kenobewan]

What does return rVal do? Return in c# breaks loops and it is always set to false at the start anyway...

[srilatk]

What does return rVal do? Return in c# breaks loops and it is always set to false at the start anyway...

Hi,
I removed the return stmt and checked. But does not make any difference .
Note: It works fine (writes all data to the database) if I keep a break point and debug the code!!
My hunch is that the below code is blowing it off...some thing to do with making the thread sleep?
proc.Start();

Logger.WriteMessage(string.Format("BCP standard output:{0}", proc.StandardOutput.ReadToEnd()), logEventCategory, (int)LogPriority.LogStatus);
int ticks = Environment.TickCount;
if ((proc.HasExited == false) && (ticks + 600000 > Environment.TickCount))
{
System.Threading.Thread.Sleep(2000); //2 seconds
}

Thanks,

[onjayuk]

you need the WaitForExit();

ps.Start();
string _output = ps.StandardOutput.ReadToEnd();
ps.WaitForExit();