Save and retrieve files into Oracle database through C# asp.net


First create the table:

CREATE TABLE “TBL_FILES”
( “FILE_ID” NUMBER(10,0) NOT NULL PRIMARY KEY,
“FILE_NAME” VARCHAR2(255 BYTE) NOT NULL,
“FILE_TYPE” VARCHAR2(255 BYTE) NOT NULL,
“FILE_LENGTH” VARCHAR2(50 BYTE) NOT NULL,
“FL_CONTENT_TYPE” VARCHAR2(1000 BYTE) NOT NULL,
“FILE_DATA” BLOB
)

Now, code to load / save files into this table:

Assuming you know how to create File input types in your page and get into the Post Back method, following is how I used to save the files:

if (null != Request.Files && Request.Files.Count > 0)
{
string[] _uploadedFiles = new string[Request.Files.Count];

HttpFileCollection fileCollection = Request.Files;

for (int i = 0; i < fileCollection.Count; i++)
{
HttpPostedFile uploadfile = fileCollection[i];

string fileName = Path.GetFileName(uploadfile.FileName);

//Valiation on Content type

if (string.IsNullOrEmpty(uploadfile.FileName))
continue;

if (false == FileUtilities.isValidExtention(uploadfile.FileName.Substring(uploadfile.FileName.IndexOf(‘.’))))
{ ErrorMessage.Text = “Invalid extension”; return; }

string _mimetype = FileUtilities.getMimeFromFile(uploadfile);

if (false == FileUtilities.isValidMimeType(_mimetype))
{ ErrorMessage.Text = “Invalid File Type”; return; }

//Validation on Length

if (uploadfile.ContentLength > 1048576)
{
ErrorMessage.Text = String.Format(“File size {0} KB exceeds mamximum limit 1 MB (1024 KB)”,
uploadfile.ContentLength / 1024); return;
}

if (uploadfile.ContentLength > 0)
{
string _path = Server.MapPath(“~/UploadFiles/”) + fileName;
uploadfile.SaveAs(_path);

clsFile _objFile = new clsFile();

_objFile.FileName = fileName;
_objFile.FileType = uploadfile.FileName.Substring(uploadfile.FileName.IndexOf(‘.’) + 1);
_objFile.FileServerPath = _path;
_objFile.FileLength = Convert.ToString(uploadfile.ContentLength);
_objFile.FileMimeType = _mimetype;

SaveFile(_objFile);
}

uploadfile.InputStream.Close();
uploadfile = null;
}
}

Here is the clsFile class:

public class clsFile
{
public int FileId { get; set; }
public string FileName { get; set; }
public string FileType { get; set; }
public string FileLength { get; set; }
public string FileMimeType { get; set; }
public string FileServerPath { get; set; }
}

Notice that I am not reading the file content and adding into the clsFile object which was created.

Following are the methods from FileUtilities  class:

[DllImport(“urlmon.dll”, CharSet = CharSet.Unicode, ExactSpelling = true, SetLastError = false)]
static extern int FindMimeFromData(IntPtr pBC,
[MarshalAs(UnmanagedType.LPWStr)] string pwzUrl,
[MarshalAs(UnmanagedType.LPArray, ArraySubType = UnmanagedType.I1, SizeParamIndex = 3)] byte[] pBuffer,
int cbSize,
[MarshalAs(UnmanagedType.LPWStr)] string pwzMimeProposed,
int dwMimeFlags, out IntPtr ppwzMimeOut, int dwReserved);

public static string getMimeFromFile(HttpPostedFile file)
{
IntPtr mimeout;

int MaxContent = (int)file.ContentLength;
if (MaxContent > 4096) MaxContent = 4096;

byte[] buf = new byte[MaxContent];
file.InputStream.Read(buf, 0, MaxContent);
int result = FindMimeFromData(IntPtr.Zero, file.FileName, buf, MaxContent, null, 0, out mimeout, 0);

if (result != 0)
{
Marshal.FreeCoTaskMem(mimeout);
return “”;
}

string mime = Marshal.PtrToStringUni(mimeout);
Marshal.FreeCoTaskMem(mimeout);

return mime.ToLower();
}

//Method to check if the Extension of the uploaded file is valid based on my requirement

//We will do Mime Type checking as well to make it more secured.

public static bool isValidExtention(string _etnx)
{
string[] acceptedExtensions = new string[] { “.docx”, “.doc”, “.txt”, “rtf”, “.pdf”, “.msg” };
return (acceptedExtensions.Contains(_etnx.ToLower())) ? true : false; ;
}

//Method to check if the Mime types of the uploaded file is valid based on my requirement

public static bool isValidMimeType(string _mimetype)
{
string[] acceptedExtensions = new string[] { “text/rtf”,
“application/doc”,
“appl/text”,
“application/vnd.msword”,
“application/vnd.ms-word”,
“application/winword”,
“application/word”,
“application/msword”,
“application/x-msw6”,
“application/x-msword”,
“application/pdf”,
“application/x-pdf”,
“application/vnd.openxmlformats-officedocument.wordprocessingml.document”,
“application/vnd.openxmlformats-officedocument.wordprocessingml.template”,
“application/vnd.openxmlformats-officedocument.wordprocessingml.document”,
“application/octet-stream”,
“text/plain”,
“application/vnd.ms-outlook”};

return (acceptedExtensions.Contains(_mimetype.ToLower())) ? true : false;
}

//Method to read file content

public static byte[] ReadFile(string sPath)
{
//Initialize byte array with a null value initially.
byte[] data = null;

//Use FileInfo object to get file size.
FileInfo fInfo = new FileInfo(sPath);
long numBytes = fInfo.Length;

//Open FileStream to read file
FileStream fStream = new FileStream(sPath, FileMode.Open, FileAccess.Read);

//Use BinaryReader to read file stream into byte array.
BinaryReader br = new BinaryReader(fStream);

//When you use BinaryReader, you need to supply number of bytes to read from file.
//In this case we want to read entire file. So supplying total number of bytes.
data = br.ReadBytes((int)numBytes);

br.Close();
fStream.Close();

return data;
}

By this time all your files are uploaded in the server. It’s time to put them into database.

public void SaveFile(clsFile _attach)
{

try
{
FileInfo _fI = new FileInfo(_attach.FileServerPath);

if (_fI.Exists)
{

//Add details in Parameter collection
#region Param
OracleParameter _param;
ArrayList _paramList = new ArrayList();

_param = new OracleParameter(“PARAM_FILENAME”, OracleType.VarChar);
_param.Value = _attach.FileName;
_paramList.Add(_param);

_param = null;
_param = new OracleParameter(“PARAM_FILETYPE”, OracleType.VarChar);
_param.Value = _attach.FileType;
_paramList.Add(_param);

_param = null;
_param = new OracleParameter(“PARAM_FILELENGTH”, OracleType.VarChar);
_param.Value = Convert.ToString(_attach.FileLength);
_paramList.Add(_param);

_param = null;
_param = new OracleParameter(“PARAM_FL_CT_TYPE”, OracleType.VarChar);
_param.Value = _attach.FileMimeType;
_paramList.Add(_param);

byte[] blob = FileUtilities.ReadFile(_attach.FileServerPath);
_param = null;
_param = new OracleParameter(“PARAM_FILE_DATA”, OracleType.Blob);
_param.Value = blob;
_paramList.Add(_param);

_param = null;
_param = new OracleParameter(“v_result”, OracleType.Number);
_param.Value = DBNull.Value;
_param.Direction = ParameterDirection.Output;
_paramList.Add(_param);

_param = null;
_param = new OracleParameter(“v_error_details”, OracleType.VarChar);
_param.Value = DBNull.Value;
_param.Size = 1000;
_param.Direction = ParameterDirection.Output;
_paramList.Add(_param);

#endregion

//I hope you already have a Data handling class to communicate with Oracle database. 

//OracleDBUtil.CallStoredProcedureWithOutput is my procedure which takes 2 argument, 1st the procedure name

//and 2nd the parameter list

//It will return an array list of output parameters so that we can check the value later to determine successful save

object _retObj = OracleDBUtil.CallStoredProcedureWithOutput(“PROC_SAVE_FILE”, _paramList);

int _result = -1;
string _resultDetails = “”;

if (_retObj.GetType().Equals(typeof(ArrayList)))
{
foreach (object _obj in ((ArrayList)_retObj))
{
if (_obj.GetType().Equals(typeof(OracleParameter)))
{
OracleParameter _oraParam = (OracleParameter)_obj;

if (_oraParam.ParameterName.Equals(“v_result”))
{
_result = Convert.ToInt32(_oraParam.Value);

}
else if (_oraParam.ParameterName.Equals(“v_error_details”))
{
_resultDetails = Convert.ToString(_oraParam.Value);
}
}
}
}
else
{
throw new Exception(“Return list not proper type : ” + Convert.ToString(_retObj.GetType()));
}

if (_result != 1)
throw new Exception(“Exception while saving: ” + _resultDetails);
}
}
catch (Exception _exp)
{
throw _exp;
}

}

So we are all set in C# part. Following is my Oracle Stored procedure to do the saving of the given file in database table:

CREATE OR REPLACE PROCEDURE “PROC_SAVE_FILE”
(
PARAM_FILENAME VARCHAR2,
PARAM_FILETYPE VARCHAR2,
PARAM_FILELENGTH VARCHAR2,
PARAM_FL_CT_TYPE VARCHAR2,
PARAM_FILE_DATA BLOB,
v_result OUT NUMBER,
v_error_details OUT varchar2
) IS

BEGIN

INSERT INTO TBL_FILES
(
FILE_ID,
FILE_NAME,
FILE_TYPE,
FILE_LENGTH,
FL_CONTENT_TYPE,
FILE_DATA
)
VALUES
(
seq_file_id.nextval,
PARAM_FILENAME,
PARAM_FILETYPE,
PARAM_FILELENGTH,
PARAM_FL_CT_TYPE,
PARAM_FILE_DATA
);

v_result:=1;
EXCEPTION

WHEN OTHERS THEN
v_result:=-1;
v_error_details:= SQLERRM;

END PROC_SAVE_FILE;

/

Now, we have saved the files successfully. You might want to retrieve file from Oracle database also. For this please follow this:

public void GetFilesFromDatabase(int _id)
{
try
{

//Here I assume that you already have a method which returns a datatable after executing a query in the database.

//OracleDBUtil.GetDataTableFromQuery is such a method which I have in my project
DataTable _d = OracleDBUtil.GetDataTableFromQuery( String.Format(@”SELECT * FROM TBL_FILES WHERE FILE_ID = {0}”, _id));

if (null != _d && _d.Rows.Count == 1)
{
Response.Clear();
Response.Buffer = true;
string strFileToSave = Convert.ToString(_d.Rows[0][“FILE_NAME”]);//Convert.ToBase64String
byte[] FileData = (byte[])(_d.Rows[0][“FILE_DATA”]);

Response.ContentType = Convert.ToString(_d.Rows[0][“FL_CONTENT_TYPE”]);
Response.AddHeader(“content-disposition”,
String.Format(“attachment;filename={0}”, strFileToSave));

Response.Charset = “”;
Response.Cache.SetCacheability(HttpCacheability.NoCache);

Response.BinaryWrite(FileData);

Response.End();
}
else
{
Response.Write(“File Not found”);
}
}
catch (Exception ex)
{
Response.Write(ex.StackTrace);
}
}

I hope you’ll be now able to do it on your own. Let me know if any help is needed 🙂

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s