public dynamic GetOpenPositionsDetails(int companyId)
{
try
{
StoredProcedureParameter OpenPositionsParam = new StoredProcedureParameter("@OpenPositions", 0, DbType.Int32, ParameterDirection.Output);
StoredProcedureParameter OffersMadeParam = new StoredProcedureParameter("@OffersMade", 0, DbType.Int32, ParameterDirection.Output);
StoredProcedureParameter OnBoardParam = new StoredProcedureParameter("@OnBoard", 0, DbType.Int32, ParameterDirection.Output);
StoredProcedureParameter RejectedDeniedParam = new StoredProcedureParameter("@RejectedDenied", 0, DbType.Int32, ParameterDirection.Output);
StoredProcedureParameter InProcessParam = new StoredProcedureParameter("@InProcess", 0, DbType.Int32, ParameterDirection.Output);
StoredProcedureHelper helper = new StoredProcedureHelper();
List <StoredProcedureParameter> parameters = new List <StoredProcedureParameter>()
{
new StoredProcedureParameter("@CompanyID", companyId, DbType.Int32, ParameterDirection.Input),
OpenPositionsParam,
OffersMadeParam,
OnBoardParam,
RejectedDeniedParam,
InProcessParam
};
var results = helper.MultiResultStoredProcedure(new StoredProcedureSettings(StoredProcedureNames.OpenPositions, parameters));
dynamic d = new ExpandoObject();
d.OpenPositions = OpenPositionsParam.Value;
d.OffersMade = OffersMadeParam.Value;
d.OnBoard = OnBoardParam.Value;
d.RejectedDenied = RejectedDeniedParam.Value;
d.InProcess = InProcessParam.Value;
return(d);
}
catch
{
throw;
}
}
internal static string GetParameterCustomLabel(Parameter parameter)
{
string label = parameter.Name;
string defaultString = SR.SchemaHierarchy_SubroutineParameterNoDefaultLabel;
string inputOutputString = SR.SchemaHierarchy_SubroutineParameterInputLabel;
string typeName = parameter.DataType.ToString();
if (parameter.DefaultValue != null &&
!string.IsNullOrEmpty(parameter.DefaultValue))
{
defaultString = SR.SchemaHierarchy_SubroutineParameterDefaultLabel;
}
StoredProcedureParameter stordProcedureParameter = parameter as StoredProcedureParameter;
if (stordProcedureParameter != null && stordProcedureParameter.IsOutputParameter)
{
inputOutputString = SR.SchemaHierarchy_SubroutineParameterInputOutputLabel;
if (parameter.IsReadOnly)
{
inputOutputString = SR.SchemaHierarchy_SubroutineParameterInputOutputReadOnlyLabel;
}
}
else if (parameter.IsReadOnly)
{
inputOutputString = SR.SchemaHierarchy_SubroutineParameterInputReadOnlyLabel;
}
return(string.Format(CultureInfo.InvariantCulture,
SR.SchemaHierarchy_SubroutineParameterLabelFormatString,
label,
typeName,
inputOutputString,
defaultString));
}
public dynamic GetIndentTrackerInfo(string indentNumber)
{
StoredProcedureParameter NoOfPositionsParam = new StoredProcedureParameter("@NoOfPositions", 0, DbType.Int32, ParameterDirection.Output);
StoredProcedureParameter OffersMadeParam = new StoredProcedureParameter("@OffersMade", 0, DbType.Int32, ParameterDirection.Output);
StoredProcedureParameter OnBoardParam = new StoredProcedureParameter("@OnBoard", 0, DbType.Int32, ParameterDirection.Output);
StoredProcedureParameter RejectedParam = new StoredProcedureParameter("@Rejected", 0, DbType.Int32, ParameterDirection.Output);
StoredProcedureParameter OfferDeniedParam = new StoredProcedureParameter("@OfferDenied", 0, DbType.Int32, ParameterDirection.Output);
StoredProcedureParameter InProcessParam = new StoredProcedureParameter("@InProcess", 0, DbType.Int32, ParameterDirection.Output);
StoredProcedureHelper helper = new StoredProcedureHelper();
List <StoredProcedureParameter> parameters = new List <StoredProcedureParameter>()
{
new StoredProcedureParameter("@IndentNumber", indentNumber, DbType.String, ParameterDirection.Input),
NoOfPositionsParam,
OffersMadeParam,
OnBoardParam,
RejectedParam,
OfferDeniedParam,
InProcessParam
};
var results = helper.MultiResultStoredProcedure(new StoredProcedureSettings(StoredProcedureNames.IndentTrackerInfo, parameters));
dynamic d = new ExpandoObject();
d.NoOfPositions = NoOfPositionsParam.Value;
d.OfferedMade = OffersMadeParam.Value;
d.OnBoard = OnBoardParam.Value;
d.Rejected = RejectedParam.Value;
d.OfferDenied = OfferDeniedParam.Value;
d.InProcess = InProcessParam.Value;
return(d);
}
public DataTable SaveSelfAudit(int auditGroupID, int staffID_Responsible, string auditName, int userID)
{
DataTable result = null;
var storedProcedure = new StoredProcedure {
StoredProcedureName = _schema + ".[Audit.Self.Save]"
};
storedProcedure.CommandTimeOut = 3600;
var paramAuditGroupID =
new StoredProcedureParameter("@AuditGroupID", ParameterType.DBInteger, auditGroupID);
var paramStaffID_Responsible =
new StoredProcedureParameter("@StaffID_Responsible", ParameterType.DBInteger, staffID_Responsible);
var paramAuditName =
new StoredProcedureParameter("@AuditName", ParameterType.DBString, auditName);
var paramUserID =
new StoredProcedureParameter("@UserID", ParameterType.DBInteger, userID);
storedProcedure.Parameters.Add(paramAuditGroupID);
storedProcedure.Parameters.Add(paramStaffID_Responsible);
storedProcedure.Parameters.Add(paramAuditName);
storedProcedure.Parameters.Add(paramUserID);
result = storedProcedure.ExecuteDataSet();
return(result);
}
public DataTable DoorInfoByBadgeID_LocationID_ReaderID(int badgeID, int locationID, int readerID, string accessType = "%")
{
DataTable result = null;
var storedProcedure = new StoredProcedure()
{
StoredProcedureName = _Schema + ".[SAAU.ProblemBadge.DoorInfoByBadgeID_LocationID_ReaderID]"
};
StoredProcedureParameter paramBadgeID = new StoredProcedureParameter("@BadgeID", ParameterType.DBInteger, badgeID);
storedProcedure.Parameters.Add(paramBadgeID);
StoredProcedureParameter paramLocationID = new StoredProcedureParameter("@LocationID", ParameterType.DBInteger, locationID);
storedProcedure.Parameters.Add(paramLocationID);
StoredProcedureParameter paramReaderID = new StoredProcedureParameter("@ReaderID", ParameterType.DBInteger, readerID);
storedProcedure.Parameters.Add(paramReaderID);
StoredProcedureParameter paramAccessType = new StoredProcedureParameter("@AccessType", ParameterType.DBString, accessType);
storedProcedure.Parameters.Add(paramAccessType);
result = storedProcedure.ExecuteDataSet();
return(result);
}
public int AuditManagerGroupSave(int auditGroupID, string facilityCode, string groupName, DateTime auditDate, string _Action, int userID)
{
var storedProcedure = new StoredProcedure {
StoredProcedureName = _schema + ".[Audit.Manager.Group.Save]"
};
var paramAuditGroupID = new StoredProcedureParameter("@AuditGroupID", ParameterType.DBInteger, auditGroupID);
var paramFacilityCode = new StoredProcedureParameter("@FacilityCode", ParameterType.DBString, facilityCode);
var paramGroupName = new StoredProcedureParameter("@GroupName", ParameterType.DBString, groupName);
var paramAuditDate = new StoredProcedureParameter("@AuditDate", ParameterType.DBDateTime, auditDate);
var param_Action = new StoredProcedureParameter("@_Action", ParameterType.DBString, _Action);
var paramUserID = new StoredProcedureParameter("@UserID", ParameterType.DBInteger, userID);
storedProcedure.Parameters.Add(paramAuditGroupID);
storedProcedure.Parameters.Add(paramFacilityCode);
storedProcedure.Parameters.Add(paramGroupName);
storedProcedure.Parameters.Add(paramAuditDate);
storedProcedure.Parameters.Add(param_Action);
storedProcedure.Parameters.Add(paramUserID);
DataTable dt = storedProcedure.ExecuteDataSet();
int newIdentity = int.Parse(dt.Rows[0][0].ToString());
return(newIdentity);
}
public DataTable PopulateNameListReport(int UserID, DataTable NameListEntryIDs)
{
DataTable result = null;
var storedProcedure = new StoredProcedure()
{
StoredProcedureName = _Schema + ".[NameList.NameListOnDemandMatchReport]"
};
var paramUserID = new StoredProcedureParameter()
{
Name = "@UserID",
DBValueType = ParameterType.DBInteger,
Value = UserID
};
storedProcedure.Parameters.Add(paramUserID);
var paramNameList = new StoredProcedureParameter
{
Name = "@NameListMatches",
DBValueType = ParameterType.Structured,
Value = NameListEntryIDs
};
storedProcedure.Parameters.Add(paramNameList);
result = storedProcedure.ExecuteDataSet();
return(result);
}
public DataTable AuditPoliceInspectionsSave(int auditInspectionID, string facilityCode, DateTime auditDate, int watchNumber, string location, int officerBadgeID, int?officerBadgeID2, int?officerBadgeID3, string _Action, int userID)
{
DataTable result = null;
var storedProcedure = new StoredProcedure {
StoredProcedureName = _schema + ".[Audit.PoliceInspections.Save]"
};
var paramAuditInspectionID = new StoredProcedureParameter("@AuditInspectionID", ParameterType.DBInteger, auditInspectionID);
var paramFacilityCode = new StoredProcedureParameter("@FacilityCode", ParameterType.DBString, facilityCode);
var paramAuditDate = new StoredProcedureParameter("@AuditDate", ParameterType.DBDateTime, auditDate);
var paramwWatchNumber = new StoredProcedureParameter("@WatchNumber", ParameterType.DBInteger, watchNumber);
var paramLocation = new StoredProcedureParameter("@Location", ParameterType.DBString, location);
var paramOfficerBadgeID = new StoredProcedureParameter("@OfficerBadgeID", ParameterType.DBInteger, officerBadgeID);
var paramOfficerBadgeID2 = new StoredProcedureParameter("@OfficerBadgeID2", ParameterType.DBInteger, officerBadgeID2 != null ? (object)officerBadgeID2.Value : DBNull.Value);
var paramOfficerBadgeID3 = new StoredProcedureParameter("@OfficerBadgeID3", ParameterType.DBInteger, officerBadgeID3 != null ? (object)officerBadgeID3.Value : DBNull.Value);
var param_Action = new StoredProcedureParameter("@_Action", ParameterType.DBInteger, _Action);
var paramUserID = new StoredProcedureParameter("@UserID", ParameterType.DBInteger, userID);
storedProcedure.Parameters.Add(paramAuditInspectionID);
storedProcedure.Parameters.Add(paramFacilityCode);
storedProcedure.Parameters.Add(paramAuditDate);
storedProcedure.Parameters.Add(paramwWatchNumber);
storedProcedure.Parameters.Add(paramLocation);
storedProcedure.Parameters.Add(paramOfficerBadgeID);
storedProcedure.Parameters.Add(paramOfficerBadgeID2);
storedProcedure.Parameters.Add(paramOfficerBadgeID3);
storedProcedure.Parameters.Add(param_Action);
storedProcedure.Parameters.Add(paramUserID);
result = storedProcedure.ExecuteDataSet();
return(result);
}
public Model.Models.QueryBrowser.Query GetById(int id)
{
var storedProcedure = new StoredProcedure()
{
StoredProcedureName = "[App.Sbo].[QueryBrowser.GetQueryById]"
};
StoredProcedureParameter dbParam = new StoredProcedureParameter("@queryId", ParameterType.DBInteger, id);
storedProcedure.Parameters.Add(dbParam);
var result = storedProcedure.ExecuteMultipleDataSet();
if (result == null || result.Tables.Count == 0 || result.Tables[0].Rows.Count == 0)
{
return(null);
}
var json = result.Tables[0].Rows[0]["QueryJson"].ToString();
var userQuery = (new JavaScriptSerializer()).Deserialize <Model.Models.QueryBrowser.Query>(json);
userQuery.Id = id; //set the correct id
return(userQuery);
}
public DataTable AuditEditorWorkItem(int userID, int?workID, int?auditSpecificationID)
{
if (workID == null && auditSpecificationID == null)
{
throw new Exception("When calling AuditEditorWorkItem workID or auditSpecificationID must have a valid (int) value.");
}
if (workID == null)
{
workID = -1;
}
if (auditSpecificationID == null)
{
auditSpecificationID = -1;
}
DataTable result = null;
var storedProcedure = new StoredProcedure {
StoredProcedureName = _schema + ".[Audit.Editor.WorkItem]"
};
var paramUserID = new StoredProcedureParameter("@UserID", ParameterType.DBInteger, userID);
var paramWorkID = new StoredProcedureParameter("@WorkID", ParameterType.DBInteger, workID);
var paramAuditSpecificationID = new StoredProcedureParameter("@AuditSpecificationID", ParameterType.DBInteger, auditSpecificationID);
storedProcedure.Parameters.Add(paramUserID);
storedProcedure.Parameters.Add(paramWorkID);
storedProcedure.Parameters.Add(paramAuditSpecificationID);
result = storedProcedure.ExecuteDataSet();
return(result);
}
private StoredProcedure GetDependencyStoredProcedure(string procedureName,
Database db,
string procedureBody)
{
StoredProcedure sp = new StoredProcedure(db, procedureName);
StoredProcedureParameter sprocName = new StoredProcedureParameter(sp,
Sproc.Mapping.FilterParameter.SPROC_NAME_PARAM, DataType.NVarChar(255));
List <StoredProcedureParameter> parameters = new List <StoredProcedureParameter>();
parameters.Add(sprocName);
sp.TextMode = false;
sp.AnsiNullsStatus = false;
sp.QuotedIdentifierStatus = false;
sp.TextBody = procedureBody;
foreach (StoredProcedureParameter parameter in parameters)
{
//tableName.DefaultValue = "null";
sp.Parameters.Add(parameter);
}
return(sp);
}
public DataTable SaveSelfAuditOld(int auditGroupID, int divisionID, int staffID_Responsible, int percentage, string auditName, int userID)
{
DataTable result = null;
var storedProcedure = new StoredProcedure {
StoredProcedureName = _schema + ".[Audit.Self.Save.Old]"
};
var paramAuditGroupID =
new StoredProcedureParameter("@AuditGroupID", ParameterType.DBInteger, auditGroupID);
var paramDivisionID =
new StoredProcedureParameter("@DivisionId", ParameterType.DBInteger, divisionID);
var paramStaffID_Responsible =
new StoredProcedureParameter("@StaffID_Responsible", ParameterType.DBInteger, staffID_Responsible);
var paramPercentage =
new StoredProcedureParameter("@Percentage", ParameterType.DBInteger, percentage);
var paramAuditName =
new StoredProcedureParameter("@AuditName", ParameterType.DBString, auditName);
var paramUserID =
new StoredProcedureParameter("@UserID", ParameterType.DBInteger, userID);
storedProcedure.Parameters.Add(paramAuditGroupID);
storedProcedure.Parameters.Add(paramDivisionID);
storedProcedure.Parameters.Add(paramStaffID_Responsible);
storedProcedure.Parameters.Add(paramPercentage);
storedProcedure.Parameters.Add(paramAuditName);
storedProcedure.Parameters.Add(paramUserID);
result = storedProcedure.ExecuteDataSet();
return(result);
}
public async Task <IActionResult> Generate([FromQuery] string dataSourceType, [FromQuery] string dataSourceConnStr,
[FromQuery] string parametersFile, [FromQuery] string templateFile,
[FromQuery] int worksheet, [FromQuery] int row, [FromQuery] int column,
[FromBody] ParameterInfoModel[] parameters)
{
ReportsAutoDiscoveryConfigModel pathSearchConfig = GetAutoDiscoveryConfig();
KeyValuePair <DbEngine, string> dataSourceDbEngine = _availableDataSources.First(item => string.Equals(item.Value.Trim().ToLower(), dataSourceType.Trim().ToLower()));
IReportGeneratorManager manager = new ExcelReportGeneratorManager(_loggerFactory, dataSourceDbEngine.Key, dataSourceConnStr);
string reportFile = GetExcelFilePath("Report", Guid.NewGuid());
ExecutionConfig config = ExecutionConfigManager.Read(Path.Combine(pathSearchConfig.ParametersFilesDirectory, parametersFile));
if (parameters != null && parameters.Length > 0)
{
if (config.DataSource == ReportDataSource.StoredProcedure)
{
foreach (ParameterInfoModel parameter in parameters)
{
StoredProcedureParameter existingStoreProcParam = config.StoredProcedureParameters.FirstOrDefault(p => string.Equals(p.ParameterName.ToLower(), parameter.Key.ToLower()));
if (existingStoreProcParam != null)
{
existingStoreProcParam.ParameterValue = parameter.Value;
}
}
}
else
{
foreach (ParameterInfoModel parameter in parameters)
{
DbQueryParameter sqlStatementPrameter = null;
if (parameter.Type == ParameterType.Where)
{
sqlStatementPrameter = config.ViewParameters.WhereParameters.FirstOrDefault(p => string.Equals(p.ParameterName.ToLower(), parameter.Key.ToLower()));
}
if (parameter.Type == ParameterType.Order)
{
sqlStatementPrameter = config.ViewParameters.OrderByParameters.FirstOrDefault(p => string.Equals(p.ParameterName.ToLower(), parameter.Key.ToLower()));
}
if (parameter.Type == ParameterType.Group)
{
sqlStatementPrameter = config.ViewParameters.GroupByParameters.FirstOrDefault(p => string.Equals(p.ParameterName.ToLower(), parameter.Key.ToLower()));
}
if (sqlStatementPrameter != null)
{
sqlStatementPrameter.ParameterValue = parameter.Value.ToString();
}
}
}
}
bool result = await manager.GenerateAsync(Path.Combine(pathSearchConfig.TemplatesFilesDirectory, templateFile), config, reportFile,
ExcelReportGeneratorHelper.CreateParameters(worksheet, row, column));
if (result)
{
byte[] bytes = System.IO.File.ReadAllBytes(reportFile);
return(File(bytes, _expectedMimeTypes[MsExcelExtension], "Report.xlsx"));
}
return(null);
}
public bool AuditFormSave(DataSet ds, int userID)
{
const int tblDivisions = 0;
const int tblBadges = 1;
StoredProcedure storedProcedure;
StoredProcedureParameter paramAuditBadgeTableType;
var paramAuditDivisionTable = new StoredProcedureParameter();
var paramUserID = new StoredProcedureParameter();
bool result;
try
{
storedProcedure = new StoredProcedure {
StoredProcedureName = _schema + ".[Audit.Editor.Save]"
};
paramAuditDivisionTable = new StoredProcedureParameter
{
Name = "@AuditDivisionTable",
DBValueType = ParameterType.Structured,
Value = ds.Tables[tblDivisions]
};
//copy and drop the note date column which is not a part of the user defined table type
DataTable table = ds.Tables[tblBadges].Copy();
//if (table.Columns.Contains("NoteDate"))
// table.Columns.Remove("NoteDate");
paramAuditBadgeTableType = new StoredProcedureParameter
{
Name = "@AuditBadgeTable",
DBValueType = ParameterType.Structured,
Value = table
};
paramUserID = new StoredProcedureParameter
{
Name = "@UserID",
DBValueType = ParameterType.DBString,
Value = userID
};
storedProcedure.Parameters.Add(paramAuditDivisionTable);
storedProcedure.Parameters.Add(paramAuditBadgeTableType);
storedProcedure.Parameters.Add(paramUserID);
storedProcedure.ExecuteDataSet();
result = true;
}
catch (Exception ex)
{
throw ex;
}
return(result);
}
public bool FindSqlParameterByUserData(StoredProcedureParameter parameter)
{
bool found = false;
if (parameter.UserData == StoredProcedureParameterUserDataHolder)
{
found = true;
}
return(found);
}
/// <summary>
/// This event is fired AFTER the storedProcedureParameter is parsed.
/// </summary>
/// <param name="xmlNode"></param>
/// <param name="storedProcedureParameter"></param>
/// <returns>True if cancelled else false if not.</returns>
public bool Parsed(XmlNode xmlNode, ref StoredProcedureParameter storedProcedureParameter)
{
// initial value
bool cancel = false;
// Add any post processing code here. Set cancel to true to abort adding this object.
// return value
return(cancel);
}
// <Summary>
// This method is used to export a StoredProcedureParameter object to xml.
// </Summary>
public string ExportStoredProcedureParameter(StoredProcedureParameter storedProcedureParameter, int indent = 0)
{
// initial value
string storedProcedureParameterXml = "";
// locals
string indentString = TextHelper.Indent(indent);
string indentString2 = TextHelper.Indent(indent + 2);
// If the storedProcedureParameter object exists
if (NullHelper.Exists(storedProcedureParameter))
{
// Create a StringBuilder
StringBuilder sb = new StringBuilder();
// Append the indentString
sb.Append(indentString);
// Write the open storedProcedureParameter node
sb.Append("<StoredProcedureParameter>" + Environment.NewLine);
// Write out each property
// Write out the value for DataType
sb.Append(indentString2);
sb.Append("<DataType>" + storedProcedureParameter.DataType + "</DataType>" + Environment.NewLine);
// Write out the value for Length
sb.Append(indentString2);
sb.Append("<Length>" + storedProcedureParameter.Length + "</Length>" + Environment.NewLine);
// Write out the value for ParameterName
sb.Append(indentString2);
sb.Append("<ParameterName>" + storedProcedureParameter.ParameterName + "</ParameterName>" + Environment.NewLine);
// Write out the value for ParameterValue
sb.Append(indentString2);
sb.Append("<ParameterValue>" + storedProcedureParameter.ParameterValue + "</ParameterValue>" + Environment.NewLine);
// Append the indentString
sb.Append(indentString);
// Write out the close storedProcedureParameter node
sb.Append("</StoredProcedureParameter>" + Environment.NewLine);
// set the return value
storedProcedureParameterXml = sb.ToString();
}
// return value
return(storedProcedureParameterXml);
}
public async Task <bool> ApproveData(SafeAccessTokenHandle accessToken, int batchNumber)
{
var batchNumberParameter = new StoredProcedureParameter
{
Name = "@BatchNumber",
Type = SqlDbType.Int,
Value = batchNumber
};
return(await ExecuteStoredProcedure(accessToken, "pApproveData", batchNumberParameter));
}
private static string MakeParameter(StoredProcedureParameter par, out bool hasDefaultValue)
{
if (!string.IsNullOrEmpty(par.DefaultValue))
{
hasDefaultValue = true;
return(par.Name + " = " + par.DefaultValue + " -- " + par.DataType.Name + " ,default " + par.DefaultValue);
}
hasDefaultValue = false;
return(MakeParameterWithValue(par.Name, par.DataType, false));
}
private void _dataGridView_CellValueChanged(object sender, DataGridViewCellEventArgs e)
{
if (e.RowIndex < 0 || e.RowIndex >= _dataGridView.Rows.Count)
{
return;
}
if (_dataGridView.Columns[e.ColumnIndex].Name == "Desc")
{
StoredProcedureParameter p = _sp.Parameters[_dataGridView.Rows[e.RowIndex].Cells[0].Value as string];
string memo = _dataGridView.Rows[e.RowIndex].Cells[e.ColumnIndex].Value as string;
Utils.SetDescription(p, memo);
}
}
public DataTable GetAuditsByAuditName(string auditName)
{
DataTable result = null;
var storedProcedure = new StoredProcedure {
StoredProcedureName = _schema + ".[Audit.AuditsByName]"
};
var paramAuditName = new StoredProcedureParameter("@AuditName", ParameterType.DBString, auditName);
storedProcedure.Parameters.Add(paramAuditName);
result = storedProcedure.ExecuteDataSet();
return(result);
}
public DataTable GetAuditsByDivision(int divisionID)
{
DataTable result = null;
var storedProcedure = new StoredProcedure {
StoredProcedureName = _schema + ".[Audit.ByDivision]"
};
var paramDivisionID = new StoredProcedureParameter("@DivisionId", ParameterType.DBInteger, divisionID);
storedProcedure.Parameters.Add(paramDivisionID);
result = storedProcedure.ExecuteDataSet();
return(result);
}
public int AuditProposalSave(DataSet ds, int userID)
{
const int tblAuditSpecifications = 0;
const int tblAuditDivisions = 1;
int result = -1;
StoredProcedure storedProcedure;
StoredProcedureParameter sppAuditSpecificationsTable;
StoredProcedureParameter sppAuditDivisionsTable;
StoredProcedureParameter sppUserID;
//TODO: use names for tables and columns
try
{
sppAuditSpecificationsTable = new StoredProcedureParameter
{
Name = "@AuditSpecificationsTable",
DBValueType = ParameterType.Structured,
Value = ds.Tables[tblAuditSpecifications]
};
sppAuditDivisionsTable = new StoredProcedureParameter
{
Name = "@AuditDivisionsTable",
DBValueType = ParameterType.Structured,
Value = ds.Tables[tblAuditDivisions]
};
sppUserID = new StoredProcedureParameter
{
Name = "@UserID",
DBValueType = ParameterType.DBInteger,
Value = userID
};
storedProcedure = new StoredProcedure {
StoredProcedureName = _schema + ".[Audit.Proposal.Save]"
};
storedProcedure.Parameters.Add(sppAuditSpecificationsTable);
storedProcedure.Parameters.Add(sppAuditDivisionsTable);
storedProcedure.Parameters.Add(sppUserID);
storedProcedure.ExecuteDataSetWithIDOutputParam(out result);
return(result);
}
catch (Exception ex)
{
throw ex;
}
}
private questStatus create(MasterPricingEntities dbContext, List <Quest.Functional.MasterPricing.StoredProcedureParameter> storedProcedureParameterList, out List <Quest.Functional.MasterPricing.StoredProcedureParameter> storedProcedureParameterIdList)
{
// Initialize
questStatus status = null;
storedProcedureParameterIdList = null;
// Perform create
try
{
List <Quest.Services.Dbio.MasterPricing.StoredProcedureParameters> _storedProcedureParameterList = new List <Quest.Services.Dbio.MasterPricing.StoredProcedureParameters>();
foreach (Quest.Functional.MasterPricing.StoredProcedureParameter storedProcedureParameter in storedProcedureParameterList)
{
Quest.Services.Dbio.MasterPricing.StoredProcedureParameters _storedProcedureParameter = new Quest.Services.Dbio.MasterPricing.StoredProcedureParameters();
BufferMgr.TransferBuffer(storedProcedureParameter, _storedProcedureParameter);
_storedProcedureParameterList.Add(_storedProcedureParameter);
}
dbContext.StoredProcedureParameters.AddRange(_storedProcedureParameterList);
dbContext.SaveChanges();
storedProcedureParameterIdList = new List <StoredProcedureParameter>();
foreach (Quest.Services.Dbio.MasterPricing.StoredProcedureParameters _storedProcedureParameter in _storedProcedureParameterList)
{
Quest.Functional.MasterPricing.StoredProcedureParameter storedProcedureParameter = new StoredProcedureParameter();
storedProcedureParameter.Id = _storedProcedureParameter.Id;
storedProcedureParameterIdList.Add(storedProcedureParameter);
}
}
catch (DbEntityValidationException ex)
{
var errorMessages = ex.EntityValidationErrors.SelectMany(x => x.ValidationErrors).Select(x => x.ErrorMessage);
String fullErrorMessage = string.Join("; ", errorMessages);
String exceptionMessage = string.Concat(ex.Message, fullErrorMessage);
status = new questStatus(Severity.Fatal, String.Format("EXCEPTION: {0}.{1}: {2}",
this.GetType().Name, MethodBase.GetCurrentMethod().Name,
exceptionMessage));
LogException(ex, status);
return(status);
}
catch (System.Exception ex)
{
status = new questStatus(Severity.Fatal, String.Format("EXCEPTION: {0}.{1}: {2}",
this.GetType().Name, MethodBase.GetCurrentMethod().Name,
ex.InnerException != null ? ex.InnerException.Message : ex.Message));
LogException(ex, status);
return(status);
}
return(new questStatus(Severity.Success));
}
private List <StoredProcedureParameter> GetStoredProcedureParameters(SqlConnection connection, string schema, string name)
{
var dtResult = new DataTable();
var result = new List <StoredProcedureParameter>();
// Validate this - based on https://stackoverflow.com/questions/20115881/how-to-get-stored-procedure-parameters-details/41330791
var sql = $@"
SELECT
'Parameter' = name,
'Type' = type_name(system_type_id),
'Length' = CAST(max_length AS INT),
'Precision' = CAST(case when type_name(system_type_id) = 'uniqueidentifier'
then precision
else OdbcPrec(system_type_id, max_length, precision) end AS INT),
'Scale' = CAST(OdbcScale(system_type_id, scale) AS INT),
'Order' = CAST(parameter_id AS INT),
'Collation' = convert(sysname,
case when system_type_id in (35, 99, 167, 175, 231, 239)
then ServerProperty('collation') end),
is_output AS output,
is_nullable AS nullable
from sys.parameters where object_id = object_id('{schema}.{name}')
ORDER BY parameter_id;";
var adapter = new SqlDataAdapter
{
SelectCommand = new SqlCommand(sql, connection)
};
adapter.Fill(dtResult);
foreach (DataRow par in dtResult.Rows)
{
var parameter = new StoredProcedureParameter()
{
Name = par["Parameter"].ToString().Replace("@", ""),
StoreType = par["Type"].ToString(),
Length = par["Length"].GetType() == typeof(DBNull) ? (int?)null : int.Parse(par["Length"].ToString()),
Precision = par["Precision"].GetType() == typeof(DBNull) ? (byte?)null : byte.Parse(par["Precision"].ToString()),
Scale = par["Scale"].GetType() == typeof(DBNull) ? (byte?)null : byte.Parse(par["Scale"].ToString()),
Ordinal = int.Parse(par["Order"].ToString()),
Output = (bool)par["output"],
Nullable = (bool)par["nullable"],
};
result.Add(parameter);
}
return(result);
}
/// <summary>
/// This method is used to parse StoredProcedureParameter objects.
/// </summary>
public StoredProcedureParameter ParseStoredProcedureParameter(ref StoredProcedureParameter storedProcedureParameter, XmlNode xmlNode)
{
// if the storedProcedureParameter object exists and the xmlNode exists
if ((storedProcedureParameter != null) && (xmlNode != null))
{
// get the full name of this node
string fullName = xmlNode.GetFullName();
// Check the name of this node to see if it is mapped to a property
switch (fullName)
{
case "Database.StoredProcedures.StoredProcedure.Parameters.StoredProcedureParameter.DataType":
// Set the value for storedProcedureParameter.DataType
storedProcedureParameter.DataType = EnumHelper.GetEnumValue <DataManager.DataTypeEnum>(xmlNode.FormattedNodeValue, DataManager.DataTypeEnum.NotSupported);
// required
break;
case "Database.StoredProcedures.StoredProcedure.Parameters.StoredProcedureParameter.Length":
// Set the value for storedProcedureParameter.Length
storedProcedureParameter.Length = NumericHelper.ParseInteger(xmlNode.FormattedNodeValue, 0, -1);
// required
break;
case "Database.StoredProcedures.StoredProcedure.Parameters.StoredProcedureParameter.ParameterName":
// Set the value for storedProcedureParameter.ParameterName
storedProcedureParameter.ParameterName = xmlNode.FormattedNodeValue;
// required
break;
}
// if there are ChildNodes
if (xmlNode.HasChildNodes)
{
// iterate the child nodes
foreach (XmlNode childNode in xmlNode.ChildNodes)
{
// append to this StoredProcedureParameter
storedProcedureParameter = ParseStoredProcedureParameter(ref storedProcedureParameter, childNode);
}
}
}
// return value
return(storedProcedureParameter);
}
public questStatus Update(DbMgrTransaction trans, StoredProcedureParameter storedProcedureParameter)
{
// Initialize
questStatus status = null;
// Update storedProcedureParameter
status = _dbStoredProcedureParametersMgr.Update(trans, storedProcedureParameter);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
return(new questStatus(Severity.Success));
}
public DataTable AuditManagerLoadAudits(int auditSpecificationID)
{
DataTable result = null;
var storedProcedure = new StoredProcedure {
StoredProcedureName = _schema + ".[Audit.Manager.Load.Audits]"
};
var paramAuditSpecificationID = new StoredProcedureParameter("@AuditSpecificationID", ParameterType.DBInteger, auditSpecificationID);
storedProcedure.Parameters.Add(paramAuditSpecificationID);
result = storedProcedure.ExecuteDataSet();
return(result);
}
public DataSet GetSpecificationInfo(int auditSpecificationID)
{
DataSet result = null;
var storedProcedure = new StoredProcedure {
StoredProcedureName = _schema + ".[Audit.Info.Inspections]"
};
var paramAuditSpecificationID = new StoredProcedureParameter("@AuditSpecificationID", ParameterType.DBInteger, auditSpecificationID);
storedProcedure.Parameters.Add(paramAuditSpecificationID);
result = storedProcedure.ExecuteMultipleDataSet();
return(result);
}
public DataTable AuditManagerLoad(string facilityCode)
{
DataTable result = null;
var storedProcedure = new StoredProcedure {
StoredProcedureName = _schema + ".[Audit.Manager.Load]"
};
var paramFacilityCode = new StoredProcedureParameter("@FacilityCode", ParameterType.DBString, facilityCode);
storedProcedure.Parameters.Add(paramFacilityCode);
result = storedProcedure.ExecuteDataSet();
return(result);
}
protected virtual StoredProcedureParameter ReadParameter(IDataRecord dataRecord)
{
var parameter = new StoredProcedureParameter();
int field = 0;
parameter.ProcedureName = dataRecord.GetAsString(field++);
parameter.Name = dataRecord.GetAsString(field++);
parameter.Schema = dataRecord.GetAsString(field++);
parameter.Type = new DataType();
parameter.Type.SqlType = dataRecord.GetAsString(field++);
parameter.Type.Length = dataRecord.GetAsNullableNumeric<long>(field++);
parameter.Type.Precision = dataRecord.GetAsNullableNumeric<int>(field++);
parameter.Type.Scale = dataRecord.GetAsNullableNumeric<int>(field++);
string inOut = dataRecord.GetAsString(field++).ToLower();
parameter.In = inOut.Contains("in");
parameter.Out = inOut.Contains("out");
return parameter;
}
public static SprocParameterInfo ScanParameter(StoredProcedureParameter param)
{
var sprocParameterInfo = new SprocParameterInfo();
sprocParameterInfo.name = param.Name;
if (param.ExtendedProperties.Contains("MS_Description") &&
param.ExtendedProperties["MS_Description"].Value != null)
sprocParameterInfo.description = param.ExtendedProperties["MS_Description"].Value.ToString();
sprocParameterInfo.nativeType = param.DataType.SqlDataType.ToString();
sprocParameterInfo.maximumLength = param.DataType.MaximumLength;
sprocParameterInfo.numericPrecision = param.DataType.NumericPrecision;
sprocParameterInfo.numericScale = param.DataType.NumericScale;
sprocParameterInfo.defaultValue = param.DefaultValue;
sprocParameterInfo.isOutputParameter = param.IsOutputParameter;
return sprocParameterInfo;
}
/// <summary>
/// Creates the parameter.
/// </summary>
/// <param name="parameter">The parameter.</param>
/// <returns>The <see cref="SqlParameter"/>.</returns>
private SqlParameter CreateParameter(StoredProcedureParameter parameter)
{
var sqlParameter = new SqlParameter
{
ParameterName = parameter.Name,
DbType = DbTypeConverter.ConvertToDbType(parameter.DataType),
Value = Convert(parameter.Value, parameter.DataType)
};
return sqlParameter;
}
private ParameterModel ToModel(DatabaseConfiguration configuration, StoredProcedureParameter parameter)
{
return new ParameterModel
{
Scale = GetNumericScale(parameter.DataType),
Precision = GetNumericPrecision(parameter.DataType),
Size = GetSize(parameter.DataType),
IsOutput = parameter.IsOutputParameter,
//todo
//SqlDataType = p.DataType.SqlDataType,
SqlDbType = _typeConverter.ToSqlDbDataType(parameter.DataType),
Column = new ColumnModel
{
DatabaseName = parameter.Name,
ClrType = _typeConverter.ToClrType(parameter, configuration.TableTypeNamespaceFromStoredProcedure)
}
};
}
private static void LoadFromDisk(IEnumerable<StoredProcedure> list, nHydrateModel model, string rootFolder, Microsoft.VisualStudio.Modeling.Store store)
{
var folder = Path.Combine(rootFolder, FOLDER_SP);
if (!Directory.Exists(folder)) return;
#region Load other parameter/field information
var fList = Directory.GetFiles(folder, "*.configuration.xml");
foreach (var f in fList)
{
var document = new XmlDocument();
try
{
document.Load(f);
}
catch (Exception ex)
{
//Do Nothing
MessageBox.Show("The file '" + f + "' is not valid and could not be loaded!", "Load Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
var fi = new FileInfo(f);
var name = fi.Name.Substring(0, fi.Name.Length - ".configuration.xml".Length).ToLower();
var itemID = XmlHelper.GetAttributeValue(document.DocumentElement, "id", Guid.Empty);
var item = list.FirstOrDefault(x => x.Id == itemID);
if (item == null)
{
item = new StoredProcedure(model.Partition, new PropertyAssignment[] { new PropertyAssignment(ElementFactory.IdPropertyAssignment, XmlHelper.GetAttributeValue(document.DocumentElement, "id", Guid.NewGuid())) });
model.StoredProcedures.Add(item);
}
System.Windows.Forms.Application.DoEvents();
item.PropertyChanged += new System.ComponentModel.PropertyChangedEventHandler(FieldParameter_PropertyChanged);
item.Name = XmlHelper.GetAttributeValue(document.DocumentElement, "name", item.Name);
item.PrecedenceOrder = XmlHelper.GetAttributeValue(document.DocumentElement, "precedenceorder", item.PrecedenceOrder);
item.IsGenerated = XmlHelper.GetAttributeValue(document.DocumentElement, "isgenerated", item.IsGenerated);
item.CodeFacade = XmlHelper.GetAttributeValue(document.DocumentElement, "codefacade", item.CodeFacade);
item.Schema = XmlHelper.GetAttributeValue(document.DocumentElement, "schema", item.Schema);
item.DatabaseObjectName = XmlHelper.GetAttributeValue(document.DocumentElement, "databaseobjectname", item.DatabaseObjectName);
item.IsExisting = XmlHelper.GetAttributeValue(document.DocumentElement, "isexisting", item.IsExisting);
item.GeneratesDoubleDerived = XmlHelper.GetAttributeValue(document.DocumentElement, "generatesdoublederived", item.GeneratesDoubleDerived);
item.Summary = XmlHelper.GetNodeValue(document.DocumentElement, "summary", item.Summary);
item.PropertyChanged -= new System.ComponentModel.PropertyChangedEventHandler(FieldParameter_PropertyChanged);
//Fields
var fieldsNodes = document.DocumentElement.SelectSingleNode("//fieldset");
if (fieldsNodes != null)
{
var nameList = new List<string>();
foreach (XmlNode n in fieldsNodes.ChildNodes)
{
var subItemID = XmlHelper.GetAttributeValue(n, "id", Guid.Empty);
var field = item.Fields.FirstOrDefault(x => x.Id == subItemID);
if (field == null)
{
field = new StoredProcedureField(item.Partition, new PropertyAssignment[] { new PropertyAssignment(ElementFactory.IdPropertyAssignment, XmlHelper.GetAttributeValue(n, "id", Guid.NewGuid())) });
item.Fields.Add(field);
}
field.PropertyChanged += new System.ComponentModel.PropertyChangedEventHandler(FieldParameter_PropertyChanged);
field.Name = XmlHelper.GetAttributeValue(n, "name", field.Name);
field.CodeFacade = XmlHelper.GetAttributeValue(n, "codefacade", field.CodeFacade);
nameList.Add(field.Name.ToLower());
field.Nullable = XmlHelper.GetAttributeValue(n, "nullable", field.Nullable);
var dtv = XmlHelper.GetAttributeValue(n, "datatype", field.DataType.ToString());
DataTypeConstants dt;
if (Enum.TryParse<DataTypeConstants>(dtv, true, out dt))
field.DataType = dt;
field.Default = XmlHelper.GetAttributeValue(n, "default", field.Default);
field.IsGenerated = XmlHelper.GetAttributeValue(n, "isgenerated", field.IsGenerated);
field.Length = XmlHelper.GetAttributeValue(n, "length", field.Length);
field.Scale = XmlHelper.GetAttributeValue(n, "scale", field.Scale);
field.Summary = XmlHelper.GetNodeValue(n, "summary", field.Summary);
field.PropertyChanged -= new System.ComponentModel.PropertyChangedEventHandler(FieldParameter_PropertyChanged);
}
if (item.Fields.Remove(x => !nameList.Contains(x.Name.ToLower())) > 0)
item.nHydrateModel.IsDirty = true;
}
//Parameters
var parametersNodes = document.DocumentElement.SelectSingleNode("//parameterset");
if (parametersNodes != null)
{
var nameList = new List<string>();
foreach (XmlNode n in parametersNodes.ChildNodes)
{
var subItemID = XmlHelper.GetAttributeValue(n, "id", Guid.Empty);
var parameter = item.Parameters.FirstOrDefault(x => x.Id == subItemID);
if (parameter == null)
{
parameter = new StoredProcedureParameter(item.Partition, new PropertyAssignment[] { new PropertyAssignment(ElementFactory.IdPropertyAssignment, XmlHelper.GetAttributeValue(n, "id", Guid.NewGuid())) });
item.Parameters.Add(parameter);
}
parameter.PropertyChanged += new System.ComponentModel.PropertyChangedEventHandler(FieldParameter_PropertyChanged);
parameter.Name = XmlHelper.GetAttributeValue(n, "name", parameter.Name);
parameter.CodeFacade = XmlHelper.GetAttributeValue(n, "codefacade", parameter.CodeFacade);
parameter.IsOutputParameter = XmlHelper.GetAttributeValue(n, "isoutput", parameter.IsOutputParameter);
nameList.Add(parameter.Name.ToLower());
parameter.Nullable = XmlHelper.GetAttributeValue(n, "nullable", parameter.Nullable);
var dtv = XmlHelper.GetAttributeValue(n, "datatype", parameter.DataType.ToString());
DataTypeConstants dt;
if (Enum.TryParse<DataTypeConstants>(dtv, true, out dt))
parameter.DataType = dt;
parameter.Default = XmlHelper.GetAttributeValue(n, "default", parameter.Default);
parameter.IsGenerated = XmlHelper.GetAttributeValue(n, "isgenerated", parameter.IsGenerated);
parameter.Length = XmlHelper.GetAttributeValue(n, "length", parameter.Length);
parameter.Scale = XmlHelper.GetAttributeValue(n, "scale", parameter.Scale);
parameter.Summary = XmlHelper.GetNodeValue(n, "summary", parameter.Summary);
parameter.PropertyChanged -= new System.ComponentModel.PropertyChangedEventHandler(FieldParameter_PropertyChanged);
}
if (item.Parameters.Remove(x => !nameList.Contains(x.Name.ToLower())) > 0)
item.nHydrateModel.IsDirty = true;
}
LoadModules(folder, item);
}
#endregion
#region Load SQL
fList = Directory.GetFiles(folder, "*.sql");
foreach (var f in fList)
{
var fi = new FileInfo(f);
if (fi.Name.ToLower().EndsWith(".sql"))
{
var name = fi.Name.Substring(0, fi.Name.Length - ".sql".Length).ToLower();
var item = list.FirstOrDefault(x => x.Name.ToLower() == name);
if (item != null)
{
item.SQL = File.ReadAllText(f);
System.Windows.Forms.Application.DoEvents();
}
}
}
#endregion
}
public void generate(string path, ProcedureGenerationType type, bool justGenerateScriptFiles)
{
#region [ Validate object state for generation. ]
if (_database == null || _table == null)
{
return;
}
#endregion
#region [ Set the procedure's name. ]
string procedureName = string.Format("cp_{0}", _table.Name);
#endregion
#region [ Create the procedure object. ]
StoredProcedure procedure = new StoredProcedure(_database, procedureName);
procedure.Schema = _table.Schema;
#endregion
#region [ Set the procedure's parameters. ]
procedure.TextMode = false;
StoredProcedureParameter operationParameter = new StoredProcedureParameter(procedure, "@Operation");
operationParameter.DataType = DataType.Int;
operationParameter.DefaultValue = "0";
procedure.Parameters.Add(operationParameter);
StoredProcedureParameter userParameter = new StoredProcedureParameter(procedure, "@User");
userParameter.DataType = DataType.NVarChar(128);
userParameter.DefaultValue = "NULL";
procedure.Parameters.Add(userParameter);
IDictionary<Column, StoredProcedureParameter> columnsToParameters = new SortedDictionary<Column, StoredProcedureParameter>(new ColumnComparer());
foreach (Column column in _table.Columns)
{
StoredProcedureParameter parameter = new StoredProcedureParameter(procedure, string.Format("@{0}", column.Name));
parameter.DataType = column.DataType;
parameter.DefaultValue = string.IsNullOrEmpty(column.Default) ? "NULL" : column.Default;
columnsToParameters.Add(column, parameter);
procedure.Parameters.Add(parameter);
}
#endregion
#region [ Set the procedure's header. ]
string textHeader = procedure.ScriptHeader(true);
#region [ Sort and format the parameters for readability ]
// Split the procedure's parameters by line
string[] parameters = textHeader.Split(new string[] { Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries);
// Create a line to hold each parameter's: name, data type,
// and default value.
string[][] parameterParts = new string[parameters.Length - 2][];
for (int l = 0; l < parameterParts.Length; l++)
{
parameterParts[l] = new string[3];
}
// Create a regular expression to split the parameter into
// its parts.
Regex regex = new Regex("(\\s)*(@[^\\s]+)\\s+(\\[.*\\](\\s*\\([^\\(]*\\)){0,1})\\s*([^,]*)(,)*(.*)");
// Determine the maximum length of parameter name and data
// type for formatting.
int parameterNameSize = 0;
int parameterTypeSize = 0;
for (int i = 1; i < parameters.Length - 1; i++)
{
string parameter = parameters[i];
Match match = regex.Match(parameter);
if (match.Groups[2].Value.Length > parameterNameSize)
{
parameterNameSize = match.Groups[2].Value.Length;
}
if (match.Groups[3].Value.Length > parameterTypeSize)
{
parameterTypeSize = match.Groups[3].Value.Length;
}
}
// Sort and format (align) the parameters.
SortedDictionary<string, string[]> sortedParameters = new SortedDictionary<string, string[]>();
for (int j = 1; j < parameters.Length - 1; j++)
{
string parameter = parameters[j];
Match match = regex.Match(parameter);
parameterParts[j - 1][0] =
match.Groups[1].Value +
(j == 1 ? " " : ",") +
match.Groups[2].Value
.PadRight(parameterNameSize + 1);
parameterParts[j - 1][1] = match.Groups[3].Value
.Replace("(", "( ")
.Replace(")", " )")
.PadRight(parameterTypeSize + 3)
.ToUpper();
parameterParts[j - 1][2] = match.Groups[5].Value;
sortedParameters.Add(parameterParts[j - 1][0], parameterParts[j - 1]);
}
// Rebuild the text of the procedure's header with the
// processed parameters.
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.AppendLine(parameters[0]);
foreach (string[] parameterPart in sortedParameters.Values)
{
stringBuilder.AppendFormat("{0}\t{1}\t{2}{3}",
parameterPart[0],
parameterPart[1],
parameterPart[2],
Environment.NewLine);
}
stringBuilder.AppendLine(parameters[parameters.Length - 1]);
textHeader = stringBuilder.ToString();
#endregion
if (type == ProcedureGenerationType.Create)
{
textHeader = textHeader.Replace("ALTER PROCEDURE", "CREATE PROCEDURE");
}
textHeader = string.Format("{0}\r\n{1}", readResource(TEXTHEADER), textHeader);
textHeader = string.Format(textHeader,
procedure.Name,
GetType().FullName,
DateTime.Now,
string.Format("Provides CRUD access to [{0}].[{1}].[{2}]", _database.Name, _table.Schema, _table.Name));
procedure.TextMode = true;
procedure.TextHeader = textHeader;
#endregion
#region [ Set the procedure's body. ]
// Remove the header comment from the template that explains
// the meaning of the replacement tokens.
string textBody = readResource(TEXTBODY);
string textBodyCommentEnd = "}}}}}\r\n";
textBody = textBody.Remove(0, textBody.LastIndexOf(textBodyCommentEnd) + textBodyCommentEnd.Length);
// Create SELECT Column list.
string selectColumns = createStringList(
from Column c
in columnsToParameters.Keys
select string.Format("[{0}]", c.Name), 5);
string selectPrimaryKeyColumns = createStringList(
from c
in columnsToParameters.Keys
where c.InPrimaryKey
select string.Format("[{0}]", c.Name), 5);
// Create PRIMARY KEY constraint.
string primaryKeyConstraint = string.Format(
"WHERE\t{0}",
createColumnParameterAssignments(
from kv
in columnsToParameters
where kv.Key.InPrimaryKey
select kv,
string.Empty,
"AND\t\t",
3));
if (primaryKeyConstraint.Length == 6)
{
StringCollection lines = new StringCollection();
lines.Add(string.Format("Error creating CrudProcedure for [{0}].[{1}].[{2}]: No primary key.",
_database.Name,
_table.Schema,
_table.Name));
writeScriptFile(path, string.Format("{0}.txt", procedure.Name), lines);
return;
}
// Create UPDATE Assignments.
string updateAssignments = createColumnParameterAssignments(
from kv
in columnsToParameters
where !(kv.Key.InPrimaryKey || kv.Key.Identity || kv.Key.Computed)
select kv,
" ",
",",
5);
// Create INSERT Column list.
string insertColumns = createStringList(
from Column c
in columnsToParameters.Keys
where !(c.Identity || c.Computed)
select string.Format("[{0}]", c.Name), 6);
// Create INSERT Parameter list.
string insertParameters = createStringList(
from KeyValuePair<Column, StoredProcedureParameter> kv
in columnsToParameters
where !(kv.Key.Identity || kv.Key.Computed)
select kv.Value.Name, 6);
// Replace tokens in text body.
textBody = string.Format(
textBody,
string.Format("[{0}].[{1}].[{2}]", _database.Name, _table.Schema, _table.Name),
selectColumns,
primaryKeyConstraint,
updateAssignments,
insertColumns,
insertParameters,
selectPrimaryKeyColumns);
procedure.TextBody = textBody;
#endregion
#region [ Write procedure script to file. ]
writeScriptFile(path, string.Format("{0}.sql", procedure.Name), procedure.Script());
#endregion
}
public StoredProcedureParameter GetSqlParameter()
{
StoredProcedureParameter spPar = new StoredProcedureParameter();
spPar.Name = this.SqlColumn.Name;
spPar.DataType = this.SqlColumn.DataType;
if (this.SqlColumn.Nullable)
{
if (this.SqlColumn.Default.Length > 0)
{
spPar.DefaultValue = this.SqlColumn.Default;
}
else
{
spPar.DefaultValue = "NULL";
}
}
return spPar;
}
List<StoredProcedureParameter> GetSPParams(string spName)
{
var result = new List<StoredProcedureParameter>();
string[] restrictions = new string[4] { mDatabase, null, spName, null };
using (SqlConnection conn = new SqlConnection(mConnectionString))
{
conn.Open();
var sprocs = conn.GetSchema("ProcedureParameters", restrictions);
conn.Close();
foreach (DataRow row in sprocs.Select("", "ORDINAL_POSITION"))
{
StoredProcedureParameter p = new StoredProcedureParameter();
p.SysType = GetSysType(row["DATA_TYPE"].ToString());
p.DbType = GetDbType(row["DATA_TYPE"].ToString()).ToString();
p.Name = row["PARAMETER_NAME"].ToString().Replace("@", "");
p.IsOutput = (row["PARAMETER_MODE"].ToString() == "INOUT") || (row["PARAMETER_MODE"].ToString() == "OUT");
p.IsInput = (row["PARAMETER_MODE"].ToString() == "INOUT") || (row["PARAMETER_MODE"].ToString() == "IN");
result.Add(p);
}
}
return result;
}
private void CreateSelectProcedure(Schema spSchema, Table tbl)
{
String procName;
StringBuilder sbSQL = new StringBuilder();
StringBuilder sbSelect = new StringBuilder();
StringBuilder sbWhere = new StringBuilder();
StoredProcedure sp;
StoredProcedureParameter parm;
try
{
// Create stored procedure name from user entry and table name
procName = PrefixTextBox.Text + tbl.Name + @"Select";
if (DropOnlyCheckBox.CheckState == CheckState.Checked)
{
DropStoredProcedure(procName, spSchema);
}
else
{
DropStoredProcedure(procName, spSchema);
ScriptTextBox.AppendText(string.Format(
System.Globalization.CultureInfo.InvariantCulture,
Properties.Resources.CreatingStoredProcedure,
spSchema.ToString(), BracketObjectName(procName))
+ Environment.NewLine);
ScrollToBottom();
// Create the new stored procedure object
sp = new StoredProcedure(tbl.Parent, procName, spSchema.Name);
sp.TextMode = false;
foreach (Column col in tbl.Columns)
{
// Select columns
if (sbSelect.Length > 0)
{
sbSelect.Append(", " + Environment.NewLine);
}
// Note: this does not fix object names with embedded brackets
sbSelect.Append("\t[");
sbSelect.Append(col.Name);
sbSelect.Append(@"]");
// Create parameters and where clause from indexed fields
if (col.InPrimaryKey == true)
{
// Parameter columns
parm = new StoredProcedureParameter(sp, "@"
+ col.Name);
parm.DataType = col.DataType;
parm.DataType.MaximumLength
= col.DataType.MaximumLength;
sp.Parameters.Add(parm);
// Where columns
if (sbWhere.Length > 0)
{
sbWhere.Append(" " + Environment.NewLine + "\tAND ");
}
// Note: this does not fix object names with embedded brackets
sbWhere.Append(@"[");
sbWhere.Append(col.Name);
sbWhere.Append(@"] = @");
sbWhere.Append(col.Name);
}
}
// Put where clause into string
if (sbWhere.Length > 0)
{
sbWhere.Insert(0, @"WHERE ");
}
sbrStatus.Text = string.Format(System.Globalization.CultureInfo.InvariantCulture,
Properties.Resources.Creating, procName);
sbSQL.Append("SELECT ");
sbSQL.Append(sbSelect);
sbSQL.Append(" " + Environment.NewLine + "FROM ");
sbSQL.Append(tbl.ToString());
sbSQL.Append(" " + Environment.NewLine);
sbSQL.Append(sbWhere);
sp.TextBody = sbSQL.ToString();
sp.Create();
}
}
catch (SmoException ex)
{
ExceptionMessageBox emb = new ExceptionMessageBox(ex);
emb.Show(this);
}
finally
{
// Clean up.
sbSQL = null;
sbSelect = null;
sbWhere = null;
sp = null;
parm = null;
}
}
