[Solved] How to upload any type of and any size of file in table of Oracle using C#? [closed]

by


//Here First you need to upload file on application server then convert into File Stream.

string auditReportUploadDocLocation = ConfigurationManager.AppSettings["AuditReportUploadDocLocation"].ToString();
        string filelocation = Path.Combine(Server.MapPath(auditReportUploadDocLocation), fuUploadDoc.FileName);
        fuUploadDoc.PostedFile.SaveAs(filelocation);
    fileName = Path.GetFileName(fuUploadDoc.PostedFile.FileName);
    using (FileStream fs = new FileStream(filelocation, FileMode.Open, FileAccess.Read))
        {
               objAudAuditReportMaster = objAudAuditReportBAL.UploadAuditReportDoc(fileName, fs, Session["AudLoginID"].ToString(), audProcessName);
        }

//Delete the file from application server.

if (File.Exists(filelocation))
    File.Delete(filelocation);

//After that Call this function.

public AudAuditReportMaster UploadAuditReportDoc(string fileName, FileStream fs, string modifyBy, string processName)
       {

           AudAuditReportMaster objAudAuditReportMaster = new AudAuditReportMaster();

            BinaryReader brFileStream = new BinaryReader(fs);

            OpenConnection();

            int streamLength = (int)fs.Length;
            OracleTransaction transaction = objConn.BeginTransaction();
            objCmd.Transaction = transaction;
            objCmd.CommandText = "declare xx blob; begin dbms_lob.createtemporary(xx, false, 0); :tempblob := xx; end;";
            objCmd.Parameters.Add(new OracleParameter("tempblob", OracleType.Blob)).Direction = ParameterDirection.Output;
            objCmd.ExecuteNonQuery();
            OracleLob tempLob = (OracleLob)objCmd.Parameters[0].Value;

            byte[] tempbuff = new byte[streamLength];
            tempLob.BeginBatch(OracleLobOpenMode.ReadWrite);
            tempLob.Write(brFileStream.ReadBytes(streamLength), 0, streamLength);
            tempLob.EndBatch();
            objCmd.Parameters.Clear();

            objCmd.CommandType = CommandType.StoredProcedure;
            objCmd.CommandText = AudDB_Constants.Aud_Audit_Pkg.SP_Aud_Upload_Doc;
            objCmd.Parameters.Clear();

            OracleParameter param1 = objCmd.Parameters.Add("p_doc_name", OracleType.VarChar);
            param1.Value = fileName;

            objCmd.Parameters.Add(new OracleParameter("p_process_name", OracleType.VarChar)).Value = processName;

            objCmd.Parameters.Add(new OracleParameter("p_process_key_name", OracleType.VarChar)).Value = DBNull.Value;
            objCmd.Parameters.Add(new OracleParameter("p_process_key_id", OracleType.Int32)).Value = DBNull.Value;

            OracleParameter param2 = objCmd.Parameters.Add("p_document", OracleType.Blob);
            param2.Value = tempLob;

            OracleParameter param3 = objCmd.Parameters.Add("p_last_updated_by", OracleType.VarChar);
            param3.Value = modifyBy;

            objCmd.Parameters.Add(new OracleParameter("p_attribute1", OracleType.VarChar)).Value = DBNull.Value;
            objCmd.Parameters.Add(new OracleParameter("p_attribute2", OracleType.VarChar)).Value = DBNull.Value;
            objCmd.Parameters.Add(new OracleParameter("p_attribute3", OracleType.VarChar)).Value = DBNull.Value;
            objCmd.Parameters.Add(new OracleParameter("p_attribute4", OracleType.VarChar)).Value = DBNull.Value;
            objCmd.Parameters.Add(new OracleParameter("p_attribute5", OracleType.VarChar)).Value = DBNull.Value;

            OracleParameter param4 = objCmd.Parameters.Add("p_doc_id", OracleType.Int32);
            param4.Direction = ParameterDirection.Output;

            OracleParameter param5 = objCmd.Parameters.Add("p_status", OracleType.VarChar, 100);
            param5.Direction = ParameterDirection.Output;

            OracleParameter param6 = objCmd.Parameters.Add("p_status_dtl", OracleType.VarChar, 1000);
            param6.Direction = ParameterDirection.Output;

            objAudAuditReportMaster.Status = Convert.ToString(objCmd.ExecuteNonQuery());
            objAudAuditReportMaster.DocID = Convert.ToInt32(param4.Value.ToString());
            objAudAuditReportMaster.Status = param5.Value.ToString();
            objAudAuditReportMaster.StatusDetail = param6.Value.ToString();
            transaction.Commit();
            CloseConnection();
            DisposeConnection();

            return objAudAuditReportMaster;
        }

//At the database end call this procedure

procedure aud_upload_doc(p_doc_name                 in  varchar2,
                         p_process_name             in  varchar2,
                         p_process_key_name         in  varchar2,
                         p_process_key_id           in  number,
                         p_document                 in  blob,    
                         p_last_updated_by          in  varchar2, 
                         p_doc_id                   out number,
begin
      p_doc_id := AUD_UNIQUE_VAL_SEQ.nextval; 

         insert into AUD_AUDIT_UPLOAD_DOC(doc_id,   doc_name,   process_name,   process_key_name,   process_key_id,   doc,        created_by,        creation_date,  last_updated_by,   last_update_date )
                                  values (p_doc_id, p_doc_name, p_process_name, p_process_key_name, p_process_key_id, p_document, p_last_updated_by, sysdate,        p_last_updated_by, sysdate        );

end;

solved How to upload any type of and any size of file in table of Oracle using C#? [closed]