private OracleParameter[] Dp2Op(string owner, string storedProcedureName, Dictionary <string, object> commandParameters, out Dictionary <string, string> tmpIpMappingOp)
{
tmpIpMappingOp = new Dictionary <string, string>();
if (commandParameters == null)
{
return(null);
}
string package = string.Empty;
string procName = string.Empty;
string[] tmpArr = storedProcedureName.Split('.');
if (tmpArr.Length == 1)
{
procName = tmpArr[0];
}
else if (tmpArr.Length == 2)
{
package = tmpArr[0];
procName = tmpArr[1];
}
if (!string.IsNullOrEmpty(procName))
{
string cacheKey = string.Format("{0}.{1}", owner, storedProcedureName);
object cacheObj = paramCache[cacheKey];
List <ProcedureInfo> procedureInfoList;
if (cacheObj != null)
{
procedureInfoList = (List <ProcedureInfo>)cacheObj;
}
else
{
procedureInfoList = new List <ProcedureInfo>();
string sql = @"SELECT t.object_name, t.argument_name, t.data_type, t.in_out, t.data_length, t.data_precision, t.data_scale FROM dba_arguments t WHERE t.owner=:owner AND t.package_name = :i_package_name AND t.object_name = :i_object_name ORDER BY t.position";
OracleParameter[] sqlParams = new OracleParameter[] {
new OracleParameter(":owner", OracleDbType.Varchar2, 50, owner.ToUpper(), ParameterDirection.Input),
new OracleParameter(":i_package_name", OracleDbType.Varchar2, 50, package.ToUpper(), ParameterDirection.Input),
new OracleParameter(":i_object_name", OracleDbType.Varchar2, 50, procName.ToUpper(), ParameterDirection.Input)
};
OracleDataReader odr = OracleHelper.ExecuteReader(connectionString, CommandType.Text, sql, sqlParams);
while (odr.Read())
{
int dataLength = 0;//dataPrecision = 0, dataScale = 0;
int.TryParse(odr["DATA_LENGTH"].ToString(), out dataLength);
ProcedureInfo pi = new ProcedureInfo();
pi.ArgumentName = odr["ARGUMENT_NAME"].ToString();
pi.DataType = odr["DATA_TYPE"].ToString().Replace(" ", "");
pi.DataLength = dataLength;
pi.InOut = odr["IN_OUT"].ToString();
procedureInfoList.Add(pi);
}
odr.Close();
paramCache.Add(cacheKey, procedureInfoList);
}
List <OracleParameter> buildSqlParams = new List <OracleParameter>();
foreach (var item in commandParameters)
{
tmpIpMappingOp.Add(item.Key.ToUpper(), item.Key);
}
foreach (ProcedureInfo pi in procedureInfoList)
{
string argument_name = pi.ArgumentName;
object paramValue = null;
if (tmpIpMappingOp.ContainsKey(argument_name))
{
paramValue = commandParameters[tmpIpMappingOp[argument_name]];
}
string data_type = pi.DataType;
OracleType otTataType = (OracleType)Enum.Parse(typeof(OracleType), data_type);
int enumIndex = (int)otTataType;
string in_out = pi.InOut;
int dataLength = pi.DataLength;
//int.TryParse(odr["DATA_PRECISION"].ToString(), out dataPrecision);
//int.TryParse(odr["DATA_SCALE"].ToString(), out dataScale);
if (in_out.Equals("IN"))
{
switch (otTataType)
{
case OracleType.VARCHAR2:
case OracleType.NVARCHAR2:
case OracleType.CHAR:
case OracleType.NCHAR:
buildSqlParams.Add(new OracleParameter(argument_name, (OracleDbType)enumIndex, dataLength, paramValue, ParameterDirection.Input));
break;
default:
buildSqlParams.Add(new OracleParameter(argument_name, (OracleDbType)enumIndex, dataLength, paramValue, ParameterDirection.Input));
break;
}
}
else if (in_out.Equals("OUT"))
{
switch (otTataType)
{
case OracleType.VARCHAR2:
case OracleType.NVARCHAR2:
case OracleType.CHAR:
case OracleType.NCHAR:
buildSqlParams.Add(new OracleParameter(argument_name, (OracleDbType)enumIndex, 4000, string.Empty, ParameterDirection.Output));
break;
default:
buildSqlParams.Add(new OracleParameter(argument_name, (OracleDbType)enumIndex, ParameterDirection.Output));
break;
}
}
}
return(buildSqlParams.ToArray());
}
else
{
return(null);
}
}
