Running External Programs through ASP.NET

[Matt]

I have a requirement where a user upload a CSV file to the server through an
ASP.NET page. After the file is uploaded the ASP.NET page then has to upload
the contents of this file into an Oracle table. After the upload is complete
the ASP.NET page has to call an Oracle stored procedure to process the data.
All this has to be done in one go.

Here is a what I did.
1. Created a simple ASP.NET page which provides the user with a file upload
element to select the CSV file to uploaded. One the user selects the file he
clicks on the upload button.
2. Upon clicking the upload button, I upload the file file and save it into
a particular folder on the server. This folder also contains a Oracle SQL
Loader control file (*.ctl).
3. After the upload of the file, I launch a shell command to execute the
sqlldr.exe to bulk load the data to the oracle table.
4. Once the bulk load is complete, if there are no errors then I call a
stored procedure through the OracleClient.

All this work fine in the CSV file contains upto 4500 records. Each record
is made up of 2 columns, 1) a five digit code and 2) an numeric value between
0 and 10000000.

The problem occurs when the number of records exceed more than 5000, the
process hangs after loading abt 4700 records. It shows the SQLLDR process as
runing in the task manager on the server. The browers shows the progress bar
for about 3-5 mins before throwing a page not found error. I tried uploading
by directly calling sqlldr, the file with 5000 records took less than 5
seconds to complete load.

Splitting the file into multiple files is not an option. I have to do in one
go.

Here is code I have used to launch the external process.

Code for on click of upload button
----------------------------------
private void UploadFile()
{
int nBufferLen = 4096;

if(fileReserve. PostedFile != null)
{
filePosted = fileReserve.Pos tedFile;
nContentLength = filePosted.Cont entLength;
byte[] buffer = new byte[nBufferLen];
int nBytesRead;

String strVirDirPath = Server.MapPath( Request.Applica tionPath);

strPath = strVirDirPath + // folder where the file needs to uploaded to
if(File.Exists( strPath))
{
File.Delete(str Path);
}
FileStream newFile = new FileStream(strP ath, FileMode.Create );

nBytesRead = filePosted.Inpu tStream.Read(bu ffer,0, nBufferLen);
do
{
newFile.Write(b uffer, 0, nBytesRead);
nBytesRead = filePosted.Inpu tStream.Read(bu ffer,0, nBufferLen);
}while(nBytesRe ad != 0);

newFile.Close() ;

retval = UploadData(strP ath); // code below

}
}

private bool UploadData(Stri ng FileName)
{
RegistryKey objRegKeyOracle = null;

String strTemp = String.Empty;
String strSqlLoaderPat h = String.Empty;
String strUserID = String.Empty;
String strPassword = String.Empty;
String strDatabase = String.Empty;
String strControlFile = String.Empty;
String strConnectionSt ring = String.Empty;
String strBadFile = String.Empty;

bool blnSqlLoaderExi sts = false;

objRegKeyOracle = Registry.LocalM achine.OpenSubK ey(@"SOFTWARE\O RACLE");

if(objRegKeyOra cle != null)
{
strSqlLoaderPat h = (String)objRegK eyOracle.GetVal ue("ORACLE_HOME ") +
@"\bin\sqlldr.e xe";
blnSqlLoaderExi sts = File.Exists(str SqlLoaderPath);
}

objRegKeyOracle .Close();

if(!blnSqlLoade rExists)
{
throw new exception
}

strTemp = DBConnection.Ge tConnectionStri ng(m_objCurrent User.Region);

strUserID = GetValue(strTem p, "USER ID");
strPassword = GetValue(strTem p, "PASSWORD") ;
strDatabase = GetValue(strTem p, "DATA SOURCE");

strControlFile = Server.MapPath(//Virtual path to control file);
strBadFile = Server.MapPath(// virtual path to where the bad file has to be
created if SQLLDR fails);

if(File.Exists( strBadFile))
{
File.Delete(str BadFile);
}

strConnectionSt ring = "userid=" + strUserID + "@" + strDatabase + "/" +
strPassword + " control=" + strControlFile + " data=" + FileName;

String strShellCmd = "sqlldr " + strConnectionSt ring;

String strWorkingDir = strControlFile. Substring(0,
strControlFile. LastIndexOf("\\ "));

System.Diagnost ics.ProcessStar tInfo objPSI = new
System.Diagnost ics.ProcessStar tInfo("cmd.exe" );
objPSI.UseShell Execute = false;
objPSI.Redirect StandardOutput = true;
objPSI.Redirect StandardInput = true;
objPSI.Redirect StandardError = true;

objPSI.WorkingD irectory = strWorkingDir;

System.Diagnost ics.Process proc = System.Diagnost ics.Process.Sta rt(objPSI);

// Attach the output for reading
System.IO.Strea mReader sOut = proc.StandardOu tput;

// Attach the in for writing
System.IO.Strea mWriter sIn = proc.StandardIn put;

// Write command to standard input
sIn.WriteLine(s trShellCmd);

// Exit Command
sIn.WriteLine(" EXIT");

proc.WaitForExi t();

// Close the process
proc.Close();

// Read the sOut to a string.
string results = sOut.ReadToEnd( ).Trim();

// Close the io Streams;
sIn.Close();
sOut.Close();

return true;
}

--
Regards,
Matt.

[Scott Allen]

Matt:

Is there any chance the sqlldr.exe might be waiting for user input of
some sort?

Are then any switches you can pass the process to get more diagnostic
information or log to a file?

In short, nothing looks wrong with the code - you'll have to gather
some more info about what is happening.

--
Scott
http://www.OdeToCode.com/blogs/scott/

On Thu, 5 May 2005 13:46:04 -0700, "Matt"
<Ma**@discussio ns.microsoft.co m> wrote:

I have a requirement where a user upload a CSV file to the server through an
ASP.NET page. After the file is uploaded the ASP.NET page then has to upload
the contents of this file into an Oracle table. After the upload is complete
the ASP.NET page has to call an Oracle stored procedure to process the data.
All this has to be done in one go.

Here is a what I did.
1. Created a simple ASP.NET page whic h provides the user with a file uploadelement to select the CSV file to uploaded. One the user selects the file he
clicks on the upload button.
2. Upon clicking the upload button, I upload the file file and save it into
a particular folder on the server. This folder also contains a Oracle SQL
Loader control file (*.ctl).
3. After the upload of the file, I launch a shell command to execute the
sqlldr.exe to bulk load the data to the oracle table.
4. Once the bulk load is complete, if there are no errors then I call a
stored procedure through the OracleClient.

All this work fine in the CSV file contains upto 4500 records. Each record
is made up of 2 columns, 1) a five digit code and 2) an numeric value between
0 and 10000000.

The problem occurs when the number of records exceed more than 5000, the
process hangs after loading abt 4700 records. It shows the SQLLDR process as
runing in the task manager on the server. The browers shows the progress bar
for about 3-5 mins before throwing a page not found error. I tried uploading
by directly calling sqlldr, the file with 5000 records took less than 5
seconds to complete load.

Splitting the file into multiple files is not an option. I have to do in one
go.

Here is code I have used to launch the external process.

Code for on click of upload button
----------------------------------
private void UploadFile()
{
int nBufferLen = 4096;

if(fileReserve. PostedFile != null)
{
filePosted = fileReserve.Pos tedFile;
nContentLength = filePosted.Cont entLength;
byte[] buffer = new byte[nBufferLen];
int nBytesRead;

String strVirDirPath = Server.MapPath( Request.Applica tionPath);

strPath = strVirDirPath + // folder where the file needs to uploaded to
if(File.Exists( strPath))
{
File.Delete(str Path);
}
FileStream newFile = new FileStream(strP ath, FileMode.Create );

nBytesRead = filePosted.Inpu tStream.Read(bu ffer,0, nBufferLen);
do
{
newFile.Write(b uffer, 0, nBytesRead);
nBytesRead = filePosted.Inpu tStream.Read(bu ffer,0, nBufferLen);
}while(nBytesRe ad != 0);

newFile.Close() ;

retval = UploadData(strP ath); // code below

}
}

private bool UploadData(Stri ng FileName)
{
RegistryKey objRegKeyOracle = null;

String strTemp = String.Empty;
String strSqlLoaderPat h = String.Empty;
String strUserID = String.Empty;
String strPassword = String.Empty;
String strDatabase = String.Empty;
String strControlFile = String.Empty;
String strConnectionSt ring = String.Empty;
String strBadFile = String.Empty;

bool blnSqlLoaderExi sts = false;

objRegKeyOracle = Registry.LocalM achine.OpenSubK ey(@"SOFTWARE\O RACLE");

if(objRegKeyOra cle != null)
{
strSqlLoaderPat h = (String)objRegK eyOracle.GetVal ue("ORACLE_HOME ") +
@"\bin\sqlldr. exe";
blnSqlLoaderExi sts = File.Exists(str SqlLoaderPath);
}

objRegKeyOracle .Close();

if(!blnSqlLoade rExists)
{
throw new exception
}

strTemp = DBConnection.Ge tConnectionStri ng(m_objCurrent User.Region);

strUserID = GetValue(strTem p, "USER ID");
strPassword = GetValue(strTem p, "PASSWORD") ;
strDatabase = GetValue(strTem p, "DATA SOURCE");

strControlFile = Server.MapPath(//Virtual path to control file);
strBadFile = Server.MapPath(// virtual path to where the bad file has to be
created if SQLLDR fails);

if(File.Exists( strBadFile))
{
File.Delete(str BadFile);
}

strConnectionSt ring = "userid=" + strUserID + "@" + strDatabase + "/" +
strPassword + " control=" + strControlFile + " data=" + FileName;

String strShellCmd = "sqlldr " + strConnectionSt ring;

String strWorkingDir = strControlFile. Substring(0,
strControlFile .LastIndexOf("\ \"));

System.Diagnost ics.ProcessStar tInfo objPSI = new
System.Diagnos tics.ProcessSta rtInfo("cmd.exe ");
objPSI.UseShell Execute = false;
objPSI.Redirect StandardOutput = true;
objPSI.Redirect StandardInput = true;
objPSI.Redirect StandardError = true;

objPSI.WorkingD irectory = strWorkingDir;

System.Diagnost ics.Process proc = System.Diagnost ics.Process.Sta rt(objPSI);

// Attach the output for reading
System.IO.Strea mReader sOut = proc.StandardOu tput;

// Attach the in for writing
System.IO.Strea mWriter sIn = proc.StandardIn put;

// Write command to standard input
sIn.WriteLine(s trShellCmd);

// Exit Command
sIn.WriteLine(" EXIT");

proc.WaitForExi t();

// Close the process
proc.Close();

// Read the sOut to a string.
string results = sOut.ReadToEnd( ).Trim();

// Close the io Streams;
sIn.Close();
sOut.Close();

return true;
}

[Matt]

Sorry for the late response...

SQLLDR is not waiting for any user input... However I found another piece of
code which actually worked. I did not have too much time to research. It is
more or less the same as the code below, but instead of lauching a command
prompt window and then firing the sqlldr command, i called sqlldr directly.

Thank again.

--
Regards,
Matt.
"Scott Allen" wrote:

Matt:

Is there any chance the sqlldr.exe might be waiting for user input of
some sort?

Are then any switches you can pass the process to get more diagnostic
information or log to a file?

In short, nothing looks wrong with the code - you'll have to gather
some more info about what is happening.

--
Scott
http://www.OdeToCode.com/blogs/scott/

On Thu, 5 May 2005 13:46:04 -0700, "Matt"
<Ma**@discussio ns.microsoft.co m> wrote:

I have a requirement where a user upload a CSV file to the server through an
ASP.NET page. After the file is uploaded the ASP.NET page then has to upload
the contents of this file into an Oracle table. After the upload is complete
the ASP.NET page has to call an Oracle stored procedure to process the data.
All this has to be done in one go.

Here is a what I did.
1. Created a simple ASP.NET page whic

h provides the user with a file upload

element to select the CSV file to uploaded. One the user selects the file he
clicks on the upload button.
2. Upon clicking the upload button, I upload the file file and save it into
a particular folder on the server. This folder also contains a Oracle SQL
Loader control file (*.ctl).
3. After the upload of the file, I launch a shell command to execute the
sqlldr.exe to bulk load the data to the oracle table.
4. Once the bulk load is complete, if there are no errors then I call a
stored procedure through the OracleClient.

All this work fine in the CSV file contains upto 4500 records. Each record
is made up of 2 columns, 1) a five digit code and 2) an numeric value between
0 and 10000000.

The problem occurs when the number of records exceed more than 5000, the
process hangs after loading abt 4700 records. It shows the SQLLDR process as
runing in the task manager on the server. The browers shows the progress bar
for about 3-5 mins before throwing a page not found error. I tried uploading
by directly calling sqlldr, the file with 5000 records took less than 5
seconds to complete load.

Splitting the file into multiple files is not an option. I have to do in one
go.

Here is code I have used to launch the external process.

Code for on click of upload button
----------------------------------
private void UploadFile()
{
int nBufferLen = 4096;

if(fileReserve. PostedFile != null)
{
filePosted = fileReserve.Pos tedFile;
nContentLength = filePosted.Cont entLength;
byte[] buffer = new byte[nBufferLen];
int nBytesRead;

String strVirDirPath = Server.MapPath( Request.Applica tionPath);

strPath = strVirDirPath + // folder where the file needs to uploaded to
if(File.Exists( strPath))
{
File.Delete(str Path);
}
FileStream newFile = new FileStream(strP ath, FileMode.Create );

nBytesRead = filePosted.Inpu tStream.Read(bu ffer,0, nBufferLen);
do
{
newFile.Write(b uffer, 0, nBytesRead);
nBytesRead = filePosted.Inpu tStream.Read(bu ffer,0, nBufferLen);
}while(nBytesRe ad != 0);

newFile.Close() ;

retval = UploadData(strP ath); // code below

}
}

private bool UploadData(Stri ng FileName)
{
RegistryKey objRegKeyOracle = null;

String strTemp = String.Empty;
String strSqlLoaderPat h = String.Empty;
String strUserID = String.Empty;
String strPassword = String.Empty;
String strDatabase = String.Empty;
String strControlFile = String.Empty;
String strConnectionSt ring = String.Empty;
String strBadFile = String.Empty;

bool blnSqlLoaderExi sts = false;

objRegKeyOracle = Registry.LocalM achine.OpenSubK ey(@"SOFTWARE\O RACLE");

if(objRegKeyOra cle != null)
{
strSqlLoaderPat h = (String)objRegK eyOracle.GetVal ue("ORACLE_HOME ") +
@"\bin\sqlldr. exe";
blnSqlLoaderExi sts = File.Exists(str SqlLoaderPath);
}

objRegKeyOracle .Close();

if(!blnSqlLoade rExists)
{
throw new exception
}

strTemp = DBConnection.Ge tConnectionStri ng(m_objCurrent User.Region);

strUserID = GetValue(strTem p, "USER ID");
strPassword = GetValue(strTem p, "PASSWORD") ;
strDatabase = GetValue(strTem p, "DATA SOURCE");

strControlFile = Server.MapPath(//Virtual path to control file);
strBadFile = Server.MapPath(// virtual path to where the bad file has to be
created if SQLLDR fails);

if(File.Exists( strBadFile))
{
File.Delete(str BadFile);
}

strConnectionSt ring = "userid=" + strUserID + "@" + strDatabase + "/" +
strPassword + " control=" + strControlFile + " data=" + FileName;

String strShellCmd = "sqlldr " + strConnectionSt ring;

String strWorkingDir = strControlFile. Substring(0,
strControlFile .LastIndexOf("\ \"));

System.Diagnost ics.ProcessStar tInfo objPSI = new
System.Diagnos tics.ProcessSta rtInfo("cmd.exe ");
objPSI.UseShell Execute = false;
objPSI.Redirect StandardOutput = true;
objPSI.Redirect StandardInput = true;
objPSI.Redirect StandardError = true;

objPSI.WorkingD irectory = strWorkingDir;

System.Diagnost ics.Process proc = System.Diagnost ics.Process.Sta rt(objPSI);

// Attach the output for reading
System.IO.Strea mReader sOut = proc.StandardOu tput;

// Attach the in for writing
System.IO.Strea mWriter sIn = proc.StandardIn put;

// Write command to standard input
sIn.WriteLine(s trShellCmd);

// Exit Command
sIn.WriteLine(" EXIT");

proc.WaitForExi t();

// Close the process
proc.Close();

// Read the sOut to a string.
string results = sOut.ReadToEnd( ).Trim();

// Close the io Streams;
sIn.Close();
sOut.Close();

return true;
}