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 🙂

How to Store and Retrieve an Object from Cookie in ASP.NET C#


Hi,

The name of the article suggests a typical problem which the developers come around whenever they are designing a login Form or want to store some useful object in the Cookie.

Unfortunately, cookies are unsecured and they can store string value only. So, to come out of this problem one need to understand that all data can not be / must not be saved in the Cookie. Some basic information like Login Name, user name, email can be saved but not password. Also, the developer need to make sure that no one can read the information except the website for which it is designed.

Solution?

1. First you need to prepare the object: How? – This can be done by serializing the object as a string. XML, JSON, base-64 of arbitrary binary etc.

2. Now, once your object is prepared, you need to add some security. In other word you need to encrypt the string such a way that the content can be readable by you only.

3. Last step: very easy, store the encrypted content as a string in the Cookie.

How to retrieve?

Just do the opposite.

1. Get the content of the cookie as a string

2. Decrypt the string

3. Deserialize it

4. Use the object as you like.

Now, lets see an example code.

here is the class whose object I want to store in my Cookie:

public class clsAdmin
{
private string _name;

public string Name
{
get { return _name; }

set { _name = value; }
}

private int _id;

public int AdminId
{
get { return _id; }
set { _id = value; }
}
}

Now, to serialize the object we can use the following code:

clsAdmin admin = new clsAdmin();

admin.Name = “UBK”;

admin.AdminId = 1;

//  Serialize it

String _serAdmin = SerializeAnObject(admin);

So, you have your serialized string. Now, you need to encrypt it. For this you can refer to this article or use your own code.

String _encVal = EncDec.Encrypt(_serAdmin, “YOUR PASSWORD”);

This time your data is ready to be saved in a cookie.

try
{
HttpCookie myCookie = new HttpCookie(“admin_cookie”);
DateTime now = DateTime.Now;

// Set the cookie value.
myCookie.Value = _encVal;

// Set the cookie expiration date.
myCookie.Expires = now.AddDays(14);

// Add the cookie.
Response.Cookies.Add(myCookie);
}
catch (Exception _e)
{
//Failed to add Cookie
throw _e;
}

Once your Cookie is added, this is time to use it in our code.

try
{
HttpCookie myCookie = new HttpCookie(“admin_cookie”);
myCookie = Request.Cookies[“admin_cookie”];

// Read the cookie information and display it.
if (myCookie != null)
{
string _admStr = EncDec.Decrypt(myCookie.Value, “YOUR PASSWORD”);
clsAdmin _adm = DeSerializeAnObject(_admStr, typeof(clsAdmin)) as clsAdmin;

if (null != _adm && _adm.Login.Length > 0)
{
//DO Whatever you want with the object
}
}
else
Response.Write(“not found”);
}
catch (Exception _e)
{
//Failed to read cookie?
throw _e;
}

For XML Serializer I have used the wonderful code from http://weblogs.asp.net/stevewellens/archive/2009/07/02/serializing-and-deserializing-objects-to-and-from-xml.aspx

/// ---- SerializeAnObject -----------------------------
/// <summary>
/// Serializes an object to an XML string
/// </summary>
///AnObject">The Object to serialize
/// <returns>XML string</returns>

public static string SerializeAnObject(object AnObject)
{
    XmlSerializer Xml_Serializer = new XmlSerializer(AnObject.GetType());
    StringWriter Writer = new StringWriter();      

    Xml_Serializer.Serialize(Writer, AnObject);
    return Writer.ToString();
}
 

/// ---- DeSerializeAnObject ------------------------------
/// <summary>
/// DeSerialize an object
/// </summary>
///XmlOfAnObject">The XML string
///ObjectType">The type of object
/// A deserialized object...must be cast to correct type

public static Object DeSerializeAnObject(string XmlOfAnObject, Type ObjectType)
{       
    StringReader StrReader = new StringReader(XmlOfAnObject);
    XmlSerializer Xml_Serializer = new XmlSerializer(ObjectType);
    XmlTextReader XmlReader = new XmlTextReader(StrReader);
    try
    {
        Object AnObject = Xml_Serializer.Deserialize(XmlReader);
        return AnObject;
    }
    finally
    {
        XmlReader.Close();
        StrReader.Close();
    }
}

Hope this will help someones quest :)