How to Execute an Oracle stored procedure that returns LOB parameter using Entity Framework Core in .Net Core 3.1 with C#

Problem:

How to Execute an Oracle stored procedure that returns LOB parameter using Entity Framework Core in .Net Core with C#.

Solution:

Executing a simple stored procedure without LOB value is not a headache in Entity Framework, but if you try to execute a procedure that returns an LOB parameter, it may be a headache for you, because using any other method like FromSQLRaw, etc, procedure is executed but the Oracle connection closes before fetching LOB value. Here is the sample code to execute a stored procedure, lets say 'MY_PROC' that returns a CLOB parameter.

 private void ExecuteProcedure(IDbConnection dbConnection, Document d, DocumentDetail di, bool IsApproved, long? AgentKey, out string Status,out string Error)

        {

            try

            {

                Error = "";

                OracleParameter p1 = new OracleParameter("LOT_TYPE", OracleDbType.Varchar2, (d.Category == LotCategory.IMPORT) ? "BL" : "CRN", System.Data.ParameterDirection.Input );

                OracleParameter p2 = new OracleParameter("LOT_ID", OracleDbType.Varchar2, (d.Category == LotCategory.IMPORT) ? d.BLNo : d.CRNNo, System.Data.ParameterDirection.Input);

                OracleParameter p3 = new OracleParameter("ERRR", OracleDbType.Clob, System.Data.ParameterDirection.Output);

                OracleParameter p4 = new OracleParameter("STATUS", OracleDbType.Varchar2, System.Data.ParameterDirection.Output);

                OracleParameter p5 = new OracleParameter("DOC_ID", OracleDbType.Varchar2, di.BillingDocId,System.Data.ParameterDirection.Input);

                OracleParameter p6 = new OracleParameter("pIS_APPROVED", OracleDbType.Int16, (IsApproved)? 1:0, System.Data.ParameterDirection.Input);

                OracleParameter p7 = new OracleParameter("pVALIDITY", OracleDbType.Date, di.Validity, System.Data.ParameterDirection.Input);

                OracleParameter p8 = new OracleParameter("pAGENT_KEY", OracleDbType.Int64, AgentKey, System.Data.ParameterDirection.Input);

                OracleParameter p9 = new OracleParameter("pCREATOR", OracleDbType.Varchar2, HttpContext.ConnectedUser(), System.Data.ParameterDirection.Input);

                p3.Size = 5000;

                p4.Size = 50;

                

                var CON = _context.Database.GetDbConnection();

                {

                    con.Open();

                    var CMD = new OracleCommand();

                    CMD.Connection = (OracleConnection)CON;

                    CMD.CommandText = "MY_PROC";

                    CMD.CommandType = CommandType.StoredProcedure;

                    CMD.Parameters.Add(p1);

                    CMD.Parameters.Add(p2);

                    CMD.Parameters.Add(p3);

                    CMD.Parameters.Add(p4);

                    CMD.Parameters.Add(p5);

                    CMD.Parameters.Add(p6);

                    CMD.Parameters.Add(p7);

                    CMD.Parameters.Add(p8);

                    CMD.Parameters.Add(p9);

                    

                    CMD.ExecuteNonQuery();

                    Status = ((Oracle.ManagedDataAccess.Types.OracleString)p4.Value).Value;

                    if (!((Oracle.ManagedDataAccess.Types.OracleClob)p3.Value).IsNull)

                    {

                        Error = ((Oracle.ManagedDataAccess.Types.OracleClob)p3.Value).Value;

                    }

                     con.Close();

                };

            

               

                                

            }

            catch (Exception x)

            {

                log.Error("Exception : " + x);

                Status = "Error";

                Error = x.Message;

            }

        }

Problem

Image not saving / updating / uploading properly in Oracle Database using Entity Framework / EF Core in BLOB / CLOB field using Oracle.EntityFrameworkCore provider.

Solution

Oracle BLOB / CLOB has by-default restriction of 2000 bytes in Oracle.EntitiyFrameworkCore provider. Due to this reason it trims the larger image bytes array to 2000 bytes and store in database without giving any error. As a result image not appear properly when retrieved.

To overcome this issue, an attribute is provided by Entity Framework which increases its capability of accepting large sized images. MaxSize attribute is available in Data Annotations assembly which helps to solve this problem.

Hope your problem is solved. Happy Coding!!!

Crystal Reports for VS 2010 and .Net 4 framework : "Load Report Failed" error

Problem:

If you have recently upgraded your project and started using Crystal Reports for VS 2010 and .Net 4 framework in your project, you might seen this error randomly or continuously after generating some reports. 

Cause:

Unlike previous versions of Crystal Reports, Crystal Report Engine is not releasing memory after closing reports. 

Solution:

After getting tired of this error, I started diagnosing this issue. I monitored the memory usage and found that memory usage is getting increased even after closing down the reports. So I added some code to restore memory after closing. Here is the addition to my code that solved my problem:

            rpt.Close()
            rpt.Dispose()
            rpt = Nothing

            GC.Collect()

Hope it will solve your problem. Enjoy Coding!

The DataColumnMapping.SourceColumn is required to be unique. 'ColumnName' already exists in the collection.

Problem:

While configuring Table Adapter query in ADO.Net Dataset, you may have received this error:

 The DataColumnMapping.SourceColumn is required to be unique. 'Column' already exists in the collection. 

Solution:

Open the Dataset's xsd file with XML text editor. Search for the column name showing in the error. Manually remove the mapping column and then save the xml file.

Hopefully you will have no error from now on.