473,320 Members | 1,884 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Running External Programs through ASP.NET

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.PostedFile;
nContentLength = filePosted.ContentLength;
byte[] buffer = new byte[nBufferLen];
int nBytesRead;

String strVirDirPath = Server.MapPath(Request.ApplicationPath);

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

nBytesRead = filePosted.InputStream.Read(buffer,0, nBufferLen);
do
{
newFile.Write(buffer, 0, nBytesRead);
nBytesRead = filePosted.InputStream.Read(buffer,0, nBufferLen);
}while(nBytesRead != 0);

newFile.Close();

retval = UploadData(strPath); // code below

}
}

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

String strTemp = String.Empty;
String strSqlLoaderPath = String.Empty;
String strUserID = String.Empty;
String strPassword = String.Empty;
String strDatabase = String.Empty;
String strControlFile = String.Empty;
String strConnectionString = String.Empty;
String strBadFile = String.Empty;

bool blnSqlLoaderExists = false;

objRegKeyOracle = Registry.LocalMachine.OpenSubKey(@"SOFTWARE\ORACLE ");

if(objRegKeyOracle != null)
{
strSqlLoaderPath = (String)objRegKeyOracle.GetValue("ORACLE_HOME") +
@"\bin\sqlldr.exe";
blnSqlLoaderExists = File.Exists(strSqlLoaderPath);
}

objRegKeyOracle.Close();

if(!blnSqlLoaderExists)
{
throw new exception
}

strTemp = DBConnection.GetConnectionString(m_objCurrentUser. Region);

strUserID = GetValue(strTemp, "USER ID");
strPassword = GetValue(strTemp, "PASSWORD");
strDatabase = GetValue(strTemp, "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(strBadFile);
}

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

String strShellCmd = "sqlldr " + strConnectionString;

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

System.Diagnostics.ProcessStartInfo objPSI = new
System.Diagnostics.ProcessStartInfo("cmd.exe");
objPSI.UseShellExecute = false;
objPSI.RedirectStandardOutput = true;
objPSI.RedirectStandardInput = true;
objPSI.RedirectStandardError = true;

objPSI.WorkingDirectory = strWorkingDir;

System.Diagnostics.Process proc = System.Diagnostics.Process.Start(objPSI);

// Attach the output for reading
System.IO.StreamReader sOut = proc.StandardOutput;

// Attach the in for writing
System.IO.StreamWriter sIn = proc.StandardInput;

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

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

proc.WaitForExit();

// 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.
Nov 19 '05 #1
2 2163
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**@discussions.microsoft.com> 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.PostedFile;
nContentLength = filePosted.ContentLength;
byte[] buffer = new byte[nBufferLen];
int nBytesRead;

String strVirDirPath = Server.MapPath(Request.ApplicationPath);

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

nBytesRead = filePosted.InputStream.Read(buffer,0, nBufferLen);
do
{
newFile.Write(buffer, 0, nBytesRead);
nBytesRead = filePosted.InputStream.Read(buffer,0, nBufferLen);
}while(nBytesRead != 0);

newFile.Close();

retval = UploadData(strPath); // code below

}
}

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

String strTemp = String.Empty;
String strSqlLoaderPath = String.Empty;
String strUserID = String.Empty;
String strPassword = String.Empty;
String strDatabase = String.Empty;
String strControlFile = String.Empty;
String strConnectionString = String.Empty;
String strBadFile = String.Empty;

bool blnSqlLoaderExists = false;

objRegKeyOracle = Registry.LocalMachine.OpenSubKey(@"SOFTWARE\ORACLE ");

if(objRegKeyOracle != null)
{
strSqlLoaderPath = (String)objRegKeyOracle.GetValue("ORACLE_HOME") +
@"\bin\sqlldr.exe";
blnSqlLoaderExists = File.Exists(strSqlLoaderPath);
}

objRegKeyOracle.Close();

if(!blnSqlLoaderExists)
{
throw new exception
}

strTemp = DBConnection.GetConnectionString(m_objCurrentUser. Region);

strUserID = GetValue(strTemp, "USER ID");
strPassword = GetValue(strTemp, "PASSWORD");
strDatabase = GetValue(strTemp, "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(strBadFile);
}

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

String strShellCmd = "sqlldr " + strConnectionString;

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

System.Diagnostics.ProcessStartInfo objPSI = new
System.Diagnostics.ProcessStartInfo("cmd.exe");
objPSI.UseShellExecute = false;
objPSI.RedirectStandardOutput = true;
objPSI.RedirectStandardInput = true;
objPSI.RedirectStandardError = true;

objPSI.WorkingDirectory = strWorkingDir;

System.Diagnostics.Process proc = System.Diagnostics.Process.Start(objPSI);

// Attach the output for reading
System.IO.StreamReader sOut = proc.StandardOutput;

// Attach the in for writing
System.IO.StreamWriter sIn = proc.StandardInput;

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

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

proc.WaitForExit();

// 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;
}


Nov 19 '05 #2
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**@discussions.microsoft.com> 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.PostedFile;
nContentLength = filePosted.ContentLength;
byte[] buffer = new byte[nBufferLen];
int nBytesRead;

String strVirDirPath = Server.MapPath(Request.ApplicationPath);

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

nBytesRead = filePosted.InputStream.Read(buffer,0, nBufferLen);
do
{
newFile.Write(buffer, 0, nBytesRead);
nBytesRead = filePosted.InputStream.Read(buffer,0, nBufferLen);
}while(nBytesRead != 0);

newFile.Close();

retval = UploadData(strPath); // code below

}
}

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

String strTemp = String.Empty;
String strSqlLoaderPath = String.Empty;
String strUserID = String.Empty;
String strPassword = String.Empty;
String strDatabase = String.Empty;
String strControlFile = String.Empty;
String strConnectionString = String.Empty;
String strBadFile = String.Empty;

bool blnSqlLoaderExists = false;

objRegKeyOracle = Registry.LocalMachine.OpenSubKey(@"SOFTWARE\ORACLE ");

if(objRegKeyOracle != null)
{
strSqlLoaderPath = (String)objRegKeyOracle.GetValue("ORACLE_HOME") +
@"\bin\sqlldr.exe";
blnSqlLoaderExists = File.Exists(strSqlLoaderPath);
}

objRegKeyOracle.Close();

if(!blnSqlLoaderExists)
{
throw new exception
}

strTemp = DBConnection.GetConnectionString(m_objCurrentUser. Region);

strUserID = GetValue(strTemp, "USER ID");
strPassword = GetValue(strTemp, "PASSWORD");
strDatabase = GetValue(strTemp, "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(strBadFile);
}

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

String strShellCmd = "sqlldr " + strConnectionString;

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

System.Diagnostics.ProcessStartInfo objPSI = new
System.Diagnostics.ProcessStartInfo("cmd.exe");
objPSI.UseShellExecute = false;
objPSI.RedirectStandardOutput = true;
objPSI.RedirectStandardInput = true;
objPSI.RedirectStandardError = true;

objPSI.WorkingDirectory = strWorkingDir;

System.Diagnostics.Process proc = System.Diagnostics.Process.Start(objPSI);

// Attach the output for reading
System.IO.StreamReader sOut = proc.StandardOutput;

// Attach the in for writing
System.IO.StreamWriter sIn = proc.StandardInput;

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

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

proc.WaitForExit();

// 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;
}


Nov 19 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: James Li | last post by:
I have a windows form application (C#) that launches multiple external batch files to do various installation tasks. I want to show some status on the win form, say simply update the text on a...
16
by: TB | last post by:
Hi all: If you think that the following comments are absolute amateurish, then please bear with me, or simply skip this thread. A couple of months back I made the decision to initiate a...
1
by: sir_alex | last post by:
If i have to execute an external program (for example, in the frontend that i created for convert, from ImageMagick) i have 2 possibilities: i can call one function between the exec* family, or i...
0
by: jfigueiras | last post by:
>I have a problem with the module subprocess! As many other programs... I'm not sure what you mean by "non-standard file descriptors". The other program is free to open, read, write, etc any...
2
by: Heikki Toivonen | last post by:
We have successfully used a script to run external programs for several years. Now we upgraded our Python to 2.5, and are hitting a mysterious error. The expected output from the sample script...
2
by: mats edvinsson | last post by:
i have been up to creating an shortcut application but i dont know how to run external programs on a double click of an image. i have put the image out and added a double click action so all i...
51
by: Ojas | last post by:
Hi!, I just out of curiosity want to know how top detect the client side application under which the script is getting run. I mean to ask the how to know whether the script is running under...
3
by: pompeyoc | last post by:
Hi. I was wondering if anyone out here knows if there is a limit to the size of an external stored procedure? I am currently using VB programs in Windows to call small COBOL stored procs residing...
9
by: Jimmy | last post by:
Well, i know it may be a little non-python thing, however, I can think of no place better to post this question :) can anyone tell me, in python, how to obtain some information of a running...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.