Identifying assembly version conflicts

I got a ‘No way to resolve conflict between “System.Runtime.Serialization, Version=5.0.5.0…’ error recently after pulling out the latest code from the source control. The cause of this error was because projects in my solution were referring to different version of the same assembly. After searching on the net, I
found a great tool to check the assemblies referenced by my project. The tool is called AsmSpy written by Mike Hadlow. You can find it on http://mikehadlow.blogspot.com.au/2011/02/asmspy-little-tool-to-help-fix-assembly.html.

Working with Oracle Object Types part 2

Following previous post about working with Types in Oracle, this post will focus on using the types from .Net.

The easiest way to map the Oracle Types is by using Oracle .Net Data Provider (ODP) to generate the class for you:
– Open Server Explorer (Tools – Server Explorer)
– Add new connection (right click Data Connections and choose add connection), set Data source to Oracle Database and Data provider to Oracle Data Provider for .Net
– Fill up some connection details and press Ok
– At this stage you should be able to see the mapping of every Oracle object in Server Explorer (well, maybe not everything)
– Go to User-Defined Types node and choose one of the Types
– Right click on the type, Generate Custom Class, follow the wizard and click Finish

From previous post, I got USER_TYPE and USER_TAB objects generated and to use those objects from .Net:

OracleConnection conn = NEW OracleConnection(connectionString);

IF (conn.State == ConnectionState.Closed)
{
conn.Open();
}

try
{
    OracleCommand oCmd = NEW OracleCommand();
    oCmd.CommandText = "USERPKG.GetUsers";
    oCmd.Connection = conn;
    oCmd.CommandType = CommandType.StoredProcedure;
    oCmd.BindByName = true;

    OracleParameter nameParam = oCmd.PARAMETERS.ADD("userId", OracleDbType.Int32);
    nameParam.Value = userId;
    nameParam.Direction = ParameterDirection.Input;

    OracleParameter listParam = NEW OracleParameter("USER_TAB", OracleDbType.OBJECT);
    listParam.Direction = ParameterDirection.ReturnValue;
    listParam.UdtTypeName = "USER_TAB";
    oCmd.PARAMETERS.INSERT(0, listParam);

    oCmd.ExecuteNonQuery();
    USER_TAB searchResult = (USER_TAB)oCmd.PARAMETERS["USER_TAB"].Value;
    RETURN searchResult;
}
finally
{
    IF (conn.State == ConnectionState.Open)
    {
        conn.Close();
    }
}

As we are using oracle type, the UdtTypeName needs to be declared. So far this is the easiest way that I can find to use Oracle data types from .Net 🙂

Convert a string or an integer to enum type

Something that I always use whenever I use enumeration :

public enum StatusType
	{
		Approved = 1,
		Pending = 2,
		Cancelled = 3
	}

Convert a string to enum :

string currentStatus = "Approved";
StatusType status = (StatusType) Enum.Parse(typeof(StatusType), currentStatus);

Convert an int to enum :

int currentStatusId = 1;
StatusType type = (StatusType) Enum.ToObject(typeof(StatusType), currentStatusId); 

Just be careful with enum conversion, especially when you want to use Enum.IsDefined method as it is very expensive.

Retrieving a list from Oracle function

I have spent a few hours trying to figure out how to get the return value from an oracle function and the only thing that I got was something like ” <function name> is not a procedure or defined “.

The problem was not from my function, to retrieve the returned cursor from the function we have to insert an output parameter as the first parameter.

OracleParameter refCursor = new OracleParameter();

refCursor.OracleDbType = OracleDbType.RefCursor;

refCursor.Direction = ParameterDirection.ReturnValue;

oracleCommand.Parameters.Insert(0, refCursor);