G
Guest
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;
}
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;
}