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); }