private void lvFoundProcedures_ItemSelectionChanged(object sender, ListViewItemSelectionChangedEventArgs e)
{
if (lvFoundProcedures.SelectedItems.Count == 1)
{
int index = e.ItemIndex;
ListViewItem selectedItem = e.Item;
PLSQLProcedure procedure = ((ImportStoredProcedureWrapperClass)e.Item.Tag).StoredProcedure;
tbStoredProcText.Text = procedure.ToString();
tbStoredProcText.WordWrap = (procedure.Status != ProcedureStatus.Valid);
if (procedure.Status.Equals(ProcedureStatus.NotSupported) || procedure.IsFunction)
{
procedureMissingInFileAndNoValidSelectionMade = true;
}
else
{
procedureMissingInFileAndNoValidSelectionMade = false;
if (!procedure.Name.Equals(myStoredProcedure.ProcedureName))
{
myStoredProcedure.ProcedureName = procedure.Name;
}
}
ChangedSP(sender, e);
}
EnableDisableButtons();
}
public void UpdateExistingProcedure()
{
if (lvFoundProcedures.SelectedItems.Count == 1 && myStoredProcedure.Id != Guid.Empty)
{
// Get selected procedure
PLSQLProcedure selectedProcedure = ((ImportStoredProcedureWrapperClass)lvFoundProcedures.SelectedItems[0].Tag).StoredProcedure;
if (myStoredProcedure.ProcedureName == selectedProcedure.Name)
{
if (existingPackage == null)
{
GetExistingPackage(lastReadPLSQLSpec.PackageName);
}
if (UpdateStoredProcedure(selectedProcedure, existingPackage, myStoredProcedure.Id))
{
}
else
{
procedurePropertiesToDelete.Clear();
}
}
else
{
MessageBox.Show("You can only update the stored procedure currently connected to the Action");
}
}
}
private bool UpdateProcedureItem(ListViewItem item, PLSQLProcedure procedure, bool reloadExisting)
{
if (reloadExisting || existingPackage == null)
{
GetExistingPackage(lastReadPLSQLSpec.PackageName);
}
StoredProcedure storedProc = StoredProcedureHelper.CreateStoredProcedure(procedure);
// Check if procedure is imported already
bool isImported = (existingPackage != null &&
storedProc != null &&
existingPackage.Procedures.Where(p => p.Equals(storedProc)).Count() == 1);
bool packageHasProcedureNameImported = (existingPackage != null &&
storedProc != null &&
existingPackage.Procedures.Where(p => p.ProcedureName == storedProc.ProcedureName).Count() > 0);
ImportStoredProcedureWrapperClass wrapper = new ImportStoredProcedureWrapperClass();
wrapper.StoredProcedure = procedure;
wrapper.IsImported = isImported;
item.Text = procedure.Name;
item.SubItems[1].Text = procedure.Status.ToString();
if (isImported)
{
item.SubItems[2].Text = "Imported";
}
else
{
if (packageHasProcedureNameImported)
{
item.SubItems[2].Text = "Overloaded/Updated";
}
else
{
item.SubItems[2].Text = "Not Imported";
}
}
item.Tag = wrapper;
if (procedure.Status != ProcedureStatus.Valid)
{
item.ForeColor = Color.OrangeRed;
}
else if (isImported)
{
item.ForeColor = Color.Green;
}
return(true);
}
private bool NewProcedureItem(ref ListViewItem item, PLSQLProcedure procedure, bool reloadExisting)
{
if (item == null)
{
item = new ListViewItem();
item.SubItems.Add(string.Empty);
item.SubItems.Add(string.Empty);
}
return(UpdateProcedureItem(item, procedure, reloadExisting));
}
private StoredProcedure GetStoredProcedureFromPLSQLProcedure(PLSQLProcedure procedure, string packageName)
{
StoredProcedure storedProc = null;
if (procedure != null)
{
// Check if there are any ref cursors in the procedure parameters
if (procedure.IsContainingRefCursor && !procedure.IsFunction)
{
// Create ref cursor stored procedure
RefCurStoredProcedure proc = RefCurStoredProcedure.Create(procedure.Name, packageName, mySchema.ConnectionString);
// Check if procedure is valid
if (proc.Status == RefCurStoredProcedureStatus.Valid)
{
// Convert the RefCurStoredProcedure to a StoredProcedure
storedProc = StoredProcedureHelper.CreateStoredProcedure(procedure, proc);
}
else
{
string errorText = string.Format("Error when trying to import procedure {0} that has a Ref Cursor parameter!" + Environment.NewLine + Environment.NewLine, proc.Name) +
string.Format("Status: {0}", proc.Status.ToString()) + Environment.NewLine + Environment.NewLine +
proc.ErrorText;
MessageBox.Show(errorText, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
// Convert the PLSQLProcedure to a StoredProcedure
storedProc = StoredProcedureHelper.CreateStoredProcedure(procedure);
}
}
return(storedProc);
}
public bool UpdateStoredProcedure(PLSQLProcedure selectedProcedure, Package package, Guid spId)
{
// Check if there is more than one stored procedure which we can update and show them all
// together with the selected procedure to update with.
StoredProcedure procedureToUpdate = null;
IList <StoredProcedure> procList;
procList = (from StoredProcedure sp in package.Procedures
where sp.Id == spId
select sp).ToList();
if (procList != null && procList.Count == 1)
{
// Only one in list, then update that one.
procedureToUpdate = procList[0];
}
else
{
MessageBox.Show("The Stored Procedure could not be synchronized.", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
return(false);
}
if (procedureToUpdate != null)
{
StoredProcedure readProc = null;
readProc = GetStoredProcedureFromPLSQLProcedure(selectedProcedure, package.Name);
// Check that IsReturningRefCursor flag hasn't changed or we can't support this.
if ((procedureToUpdate.IsReturningRefCursor ?? false) != (readProc.IsReturningRefCursor ?? false))
{
if (procedureToUpdate.IsReturningRefCursor ?? false)
{
MessageBox.Show("It's not possible to update a procedure when it has changed from beeing\n" +
"a procedure with a ref cursor to not having a ref cursor as a parameter.\n\n" +
"You have to delete the initial stored procedure and import it again.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
MessageBox.Show("It's not possible to update a procedure when it has changed from not having\n" +
"a ref cursor to now having a ref cursor as a parameter.\n\n" +
"You have to delete the initial stored procedure and import it again.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
return(false);
}
procedureToUpdate.ProcedureName = selectedProcedure.Name;
// Update name of ref cursor parameter
procedureToUpdate.RefCursorParameterName = readProc.RefCursorParameterName;
// Syncronize the properties
if (!SyncProperties(procedureToUpdate, readProc))
{
MessageBox.Show("Failed to synchronize properties!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
}
if (myStoredProcedure.Id == procedureToUpdate.Id)
{
myStoredProcedure = procedureToUpdate;
}
return(true);
}
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);
}
