public ProcedureProperty SaveOrUpdateMerge(ProcedureProperty property)
{
object mergedObj = Session.Merge(property);
HibernateTemplate.SaveOrUpdate(mergedObj);
return((ProcedureProperty)mergedObj);
}
public static ProcedureProperty CreateProcedureProperty(PLSQLProcedureParameter param)
{
ProcedureProperty storedProcParam = new ProcedureProperty();
// Set parameter name
storedProcParam.Name = param.Name;
// Length, precision is set to null since it can't be fetched here.
storedProcParam.Length = null;
storedProcParam.Precision = null;
storedProcParam.Scale = null;
// Set direction of parameter
switch (param.Direction)
{
case ParameterDirection.In:
storedProcParam.PropertyType = DbPropertyType.In;
break;
case ParameterDirection.InOut:
storedProcParam.PropertyType = DbPropertyType.InOut;
break;
case ParameterDirection.Out:
storedProcParam.PropertyType = DbPropertyType.Out;
break;
default:
storedProcParam.PropertyType = DbPropertyType.In;
break;
}
// Not supported.
storedProcParam.Text = string.Empty;
storedProcParam.DbDatatype = string.Empty;
storedProcParam.OriginalTable = string.Empty;
storedProcParam.OriginalColumn = string.Empty;
if (param.ParameterType == ParameterType.DataType)
{
storedProcParam.DbDatatype = PLSQLProcedureParameter.GetParameterDBTypeAsString(param.ParameterTypeDataType_DataType);
}
else if (param.ParameterType == ParameterType.TableColumn)
{
storedProcParam.OriginalTable = param.ParameterTypeTableColumn_Table;
storedProcParam.OriginalColumn = param.ParameterTypeTableColumn_Column;
}
// Not supported with defaultvalues
storedProcParam.DefaultValue = string.Empty;
// Set mandatory flag
storedProcParam.IsMandatory = param.IsMandatory;
return(storedProcParam);
}
public ProcedureProperty Save(ProcedureProperty property)
{
if (property.Id == Guid.Empty)
{
property.Id = Guid.NewGuid();
}
HibernateTemplate.Save(property);
return(property);
}
private bool SyncProperties(StoredProcedure dbStoredProc, StoredProcedure newProc)
{
using (new SessionScope(MetaManagerServices.GetSessionFactory(), MetaManagerServices.GetDomainInterceptor(), true, FlushMode.Never, true))
{
procedurePropertiesToDelete.Clear();
List <ProcedureProperty> addList = new List <ProcedureProperty>();
List <ProcedureProperty> changeList = new List <ProcedureProperty>();
List <ProcedureProperty> deleteList = new List <ProcedureProperty>(dbStoredProc.Properties);
// Compare new query properties to old ones
// determine cause of action, i.e. Add new Properties, Delete removed Properties
// or update data type
foreach (ProcedureProperty newPp in newProc.Properties)
{
ProcedureProperty original = FindPropertyByName(newPp.Name, newPp.PropertyType, deleteList);
if (original != null)
{
if (!ProcedureProperty.IsEqual(newPp, original))
{
// Datatype changed
changeList.Add(newPp);
}
else
{
// Set sequence
original.Sequence = newPp.Sequence;
}
// Mark as treated
deleteList.Remove(original);
}
else
{
// Totally new
addList.Add(newPp);
}
}
// What is left in deleteList should be removed
if (deleteList.Count > 0)
{
Dictionary <string, object> searchKey = new Dictionary <string, object>();
searchKey.Add("ProcedureProperty.Id", deleteList[0].Id);
IList <MappedProperty> mappedProperties = modelService.GetAllDomainObjectsByPropertyValues <MappedProperty>(searchKey);
foreach (ProcedureProperty deleteProp in deleteList)
{
procedurePropertiesToDelete.Add(deleteProp);
dbStoredProc.Properties.Remove(deleteProp);
}
}
if (addList.Count > 0)
{
foreach (ProcedureProperty addProp in addList)
{
addProp.StoredProcedure = dbStoredProc;
dbStoredProc.Properties.Add(addProp);
}
}
if (changeList.Count > 0)
{
foreach (ProcedureProperty changeProp in changeList)
{
ProcedureProperty dbPp = FindPropertyByName(changeProp.Name, changeProp.PropertyType, dbStoredProc.Properties);
if (dbPp != null)
{
dbPp.DbDatatype = changeProp.DbDatatype;
dbPp.Length = changeProp.Length;
dbPp.Precision = changeProp.Precision;
dbPp.Scale = changeProp.Scale;
dbPp.OriginalColumn = changeProp.OriginalColumn;
dbPp.OriginalTable = changeProp.OriginalTable;
dbPp.Text = changeProp.Text;
dbPp.Sequence = changeProp.Sequence;
}
}
}
return(true);
}
}
public void Delete(ProcedureProperty property)
{
HibernateTemplate.Delete(property);
}
public ProcedureProperty SaveOrUpdate(ProcedureProperty property)
{
HibernateTemplate.SaveOrUpdate(property);
return(property);
}
public static StoredProcedure CreateStoredProcedure(PLSQLProcedure plsqlProcedure)
{
StoredProcedure storedProc = null;
if (plsqlProcedure != null &&
plsqlProcedure.Status == ProcedureStatus.Valid)
{
// Add procedures found in package specification that was parsed
storedProc = new StoredProcedure();
// Set the name of the procedure
storedProc.ProcedureName = plsqlProcedure.Name;
// If returning a ref cursor
storedProc.IsReturningRefCursor = false;
storedProc.RefCursorParameterName = string.Empty;
// Counter to set sequence for parameters
// First parameter has sequence = 1
int paramSequenceCounter = 1;
foreach (PLSQLProcedureParameter param in plsqlProcedure.Parameters)
{
ProcedureProperty storedProcParam = ProcedurePropertyHelper.CreateProcedureProperty(param);
// Sequence of the parameters
storedProcParam.Sequence = paramSequenceCounter;
// Set the parent stored procedure for the parameter
storedProcParam.StoredProcedure = storedProc;
// Add the property to the stored procedure
storedProc.Properties.Add(storedProcParam);
// Add one to sequence counter
paramSequenceCounter++;
}
// Check if function.
if (plsqlProcedure.IsFunction)
{
// Add the return type as a property to the stored procedure.
ProcedureProperty storedProcParam = new ProcedureProperty();
// Add a name for the parameter
storedProcParam.Name = "Result";
// Length, precision is set to null since it can't be fetched here.
storedProcParam.Length = null;
storedProcParam.Precision = null;
storedProcParam.Scale = null;
// Set parameter of type function result
storedProcParam.PropertyType = DbPropertyType.Result;
// Sequence of the parameters
storedProcParam.Sequence = paramSequenceCounter;
// Set the parent stored procedure for the parameter
storedProcParam.StoredProcedure = storedProc;
// Not supported.
storedProcParam.Text = string.Empty;
storedProcParam.DbDatatype = string.Empty;
storedProcParam.OriginalTable = string.Empty;
storedProcParam.OriginalColumn = string.Empty;
if (plsqlProcedure.FunctionReturnType == FunctionReturnType.DataType)
{
storedProcParam.DbDatatype = PLSQLProcedure.GetFunctionReturnDBTypeAsString(plsqlProcedure.FunctionReturnTypeDataType_DataType);
}
else if (plsqlProcedure.FunctionReturnType == FunctionReturnType.TableColumn)
{
storedProcParam.OriginalTable = plsqlProcedure.FunctionReturnTypeTableColumn_Table;
storedProcParam.OriginalColumn = plsqlProcedure.FunctionReturnTypeTableColumn_Column;
}
// Not supported with defaultvalues
storedProcParam.DefaultValue = string.Empty;
// Set mandatory flag
storedProcParam.IsMandatory = false;
// Add the property to the stored procedure
storedProc.Properties.Add(storedProcParam);
}
}
return(storedProc);
}
public static StoredProcedure CreateStoredProcedure(PLSQLProcedure plsqlProcedure, RefCurStoredProcedure refCurStoredProcedure)
{
StoredProcedure storedProc = null;
if (plsqlProcedure != null &&
plsqlProcedure.Status == ProcedureStatus.Valid &&
plsqlProcedure.Parameters.Count > 0 &&
refCurStoredProcedure != null &&
refCurStoredProcedure.Status == RefCurStoredProcedureStatus.Valid &&
refCurStoredProcedure.ParameterList.Count > 0)
{
// Add procedures found in package specification that was parsed
storedProc = new StoredProcedure();
// Set the name of the procedure
storedProc.ProcedureName = refCurStoredProcedure.Name;
// If returning a ref cursor
storedProc.IsReturningRefCursor = true;
// Get the name of the column that is the Ref Cursor parameter
storedProc.RefCursorParameterName = refCurStoredProcedure.InitialParameterList.Where(p => p.ProviderType == Oracle.DataAccess.Client.OracleDbType.RefCursor).ToList()[0].ColumnName;
// Counter to set sequence for parameters
// First parameter has sequence = 1
int paramSequenceCounter = 1;
foreach (PLSQLProcedureParameter param in plsqlProcedure.Parameters)
{
ProcedureProperty storedProcParam = null;
if (param.ParameterType == ParameterType.DataType &&
param.ParameterTypeDataType_DataType == ParameterDBDataType.Ref_Cur)
{
foreach (OracleDataTypeInfo refCurParam in refCurStoredProcedure.ParameterList)
{
ProcedureProperty newProcParam = new ProcedureProperty();
// Set parameter name
newProcParam.Name = refCurParam.ColumnName;
newProcParam.Length = refCurParam.ColumnSize;
newProcParam.Precision = refCurParam.Precision;
newProcParam.Scale = refCurParam.Scale;
// Set direction of parameter
switch (refCurParam.Direction)
{
case System.Data.ParameterDirection.Input:
newProcParam.PropertyType = DbPropertyType.In;
break;
case System.Data.ParameterDirection.InputOutput:
newProcParam.PropertyType = DbPropertyType.InOut;
break;
case System.Data.ParameterDirection.Output:
newProcParam.PropertyType = DbPropertyType.Out;
break;
default:
newProcParam.PropertyType = DbPropertyType.In;
break;
}
// Sequence of the parameters
newProcParam.Sequence = paramSequenceCounter;
// Set the parent stored procedure for the parameter
newProcParam.StoredProcedure = storedProc;
// Not supported.
newProcParam.Text = string.Empty;
newProcParam.OriginalTable = string.Empty;
newProcParam.OriginalColumn = string.Empty;
newProcParam.DbDatatype = refCurParam.DataType;
// Not supported with defaultvalues
newProcParam.DefaultValue = string.Empty;
// Set mandatory flag
newProcParam.IsMandatory = false;
// Add the property to the stored procedure
storedProc.Properties.Add(newProcParam);
// Add one to sequence counter
paramSequenceCounter++;
}
}
else
{
storedProcParam = ProcedurePropertyHelper.CreateProcedureProperty(param);
// Sequence of the parameters
storedProcParam.Sequence = paramSequenceCounter;
// Set the parent stored procedure for the parameter
storedProcParam.StoredProcedure = storedProc;
// Add the property to the stored procedure
storedProc.Properties.Add(storedProcParam);
// Add one to sequence counter
paramSequenceCounter++;
}
}
}
return(storedProc);
}
