private static void InsertMainProcedures(TDataDefinitionStore AStore,
TTable ACurrentTable,
ProcessTemplate ATemplate,
ProcessTemplate ASnippet)
{
ASnippet.SetCodelet("TABLENAME", TTable.NiceTableName(ACurrentTable.strName));
ASnippet.SetCodeletComment("TABLE_DESCRIPTION", ACurrentTable.strDescription);
ASnippet.SetCodelet("SQLTABLENAME", ACurrentTable.strName);
ASnippet.SetCodelet("VIAOTHERTABLE", "");
ASnippet.SetCodelet("VIALINKTABLE", "");
string formalParametersPrimaryKey;
string actualParametersPrimaryKey;
string actualParametersPrimaryKeyToString;
int numberPrimaryKeyColumns;
PrepareCodeletsPrimaryKey(ACurrentTable,
out formalParametersPrimaryKey,
out actualParametersPrimaryKey,
out actualParametersPrimaryKeyToString,
out numberPrimaryKeyColumns);
ASnippet.SetCodelet("FORMALPARAMETERSPRIMARYKEY", formalParametersPrimaryKey);
ASnippet.SetCodelet("ACTUALPARAMETERSPRIMARYKEY", actualParametersPrimaryKey);
ASnippet.SetCodelet("ACTUALPARAMETERSPRIMARYKEYTOSTRING", actualParametersPrimaryKeyToString);
ASnippet.SetCodelet("PRIMARYKEYNUMBERCOLUMNS", numberPrimaryKeyColumns.ToString());
string formalParametersUniqueKey;
string actualParametersUniqueKey;
int numberUniqueKeyColumns;
PrepareCodeletsUniqueKey(ACurrentTable,
out formalParametersUniqueKey,
out actualParametersUniqueKey,
out numberUniqueKeyColumns);
ASnippet.SetCodelet("FORMALPARAMETERSUNIQUEKEY", formalParametersUniqueKey);
ASnippet.SetCodelet("ACTUALPARAMETERSUNIQUEKEY", actualParametersUniqueKey);
ASnippet.SetCodelet("UNIQUEKEYNUMBERCOLUMNS", numberUniqueKeyColumns.ToString());
ASnippet.SetCodelet("SEQUENCENAMEANDFIELD", "");
foreach (TTableField tablefield in ACurrentTable.grpTableField)
{
// is there a field filled by a sequence?
// yes: get the next value of that sequence and assign to row
if (tablefield.strSequence.Length > 0)
{
ASnippet.SetCodelet("SEQUENCENAMEANDFIELD", ", \"" + tablefield.strSequence + "\", \"" + tablefield.strName + "\"");
// assume only one sequence per table
break;
}
}
}
/// <summary>
/// write the definition for the code of a typed row
/// </summary>
/// <param name="Template"></param>
/// <param name="currentTable"></param>
/// <param name="origTable"></param>
/// <param name="WhereToInsert"></param>
public static void InsertRowDefinition(ProcessTemplate Template, TTable currentTable, TTable origTable, string WhereToInsert)
{
ProcessTemplate snippet = Template.GetSnippet("TYPEDROW");
if (origTable != null)
{
snippet.SetCodelet("BASECLASSROW", TTable.NiceTableName(currentTable.strName) + "Row");
snippet.SetCodelet("OVERRIDE", "override ");
}
else
{
snippet.SetCodelet("BASECLASSROW", "System.Data.DataRow");
snippet.SetCodelet("OVERRIDE", "virtual ");
}
snippet.SetCodeletComment("TABLE_DESCRIPTION", currentTable.strDescription);
snippet.SetCodelet("TABLENAME", currentTable.strDotNetName);
foreach (TTableField col in currentTable.grpTableField)
{
ProcessTemplate tempTemplate = null;
string columnOverwrite = "";
if ((origTable != null) && (origTable.GetField(col.strName, false) != null))
{
columnOverwrite = "new ";
}
if (columnOverwrite.Length == 0)
{
tempTemplate = Template.GetSnippet("ROWCOLUMNPROPERTY");
tempTemplate.SetCodelet("COLUMNDBNAME", col.strName);
tempTemplate.SetCodelet("COLUMNNAME", col.strNameDotNet);
tempTemplate.SetCodelet("COLUMNHELP", col.strDescription.Replace(Environment.NewLine, " "));
tempTemplate.SetCodelet("COLUMNLABEL", col.strLabel);
tempTemplate.SetCodelet("COLUMNLENGTH", col.iLength.ToString());
tempTemplate.SetCodelet("COLUMNDOTNETTYPE", col.GetDotNetType());
if (!col.bNotNull)
{
if (col.GetDotNetType().Contains("DateTime?"))
{
tempTemplate.SetCodelet("TESTFORNULL", "!value.HasValue");
}
else if (col.GetDotNetType().Contains("String"))
{
tempTemplate.SetCodelet("TESTFORNULL", "(value == null) || (value.Length == 0)");
}
}
if (col.GetDotNetType().Contains("DateTime?"))
{
tempTemplate.SetCodelet("ACTIONGETNULLVALUE", "return null;");
}
else if (col.GetDotNetType().Contains("DateTime"))
{
tempTemplate.SetCodelet("ACTIONGETNULLVALUE", "return DateTime.MinValue;");
}
else if (col.GetDotNetType().ToLower().Contains("string"))
{
tempTemplate.SetCodelet("ACTIONGETNULLVALUE", "return String.Empty;");
}
else
{
tempTemplate.SetCodelet("ACTIONGETNULLVALUE", "throw new System.Data.StrongTypingException(\"Error: DB null\", null);");
}
tempTemplate.SetCodeletComment("COLUMN_DESCRIPTION", col.strDescription);
snippet.InsertSnippet("ROWCOLUMNPROPERTIES", tempTemplate);
tempTemplate = Template.GetSnippet("FUNCTIONSFORNULLVALUES");
tempTemplate.SetCodelet("COLUMNNAME", TTable.NiceFieldName(col));
snippet.InsertSnippet("FUNCTIONSFORNULLVALUES", tempTemplate);
}
if ((col.strDefault.Length > 0) && (col.strDefault != "NULL"))
{
string defaultValue = col.strDefault;
if (defaultValue == "SYSDATE")
{
defaultValue = "DateTime.Today";
}
else if ((col.strType == "bit") || ((col.strTypeDotNet != null) && col.strTypeDotNet.ToLower().Contains("bool")))
{
defaultValue = (defaultValue == "1" || defaultValue.ToLower() == "true").ToString().ToLower();
}
else if ((col.strType == "varchar") || ((col.strTypeDotNet != null) && col.strTypeDotNet.ToLower().Contains("string")))
{
defaultValue = '"' + defaultValue + '"';
}
snippet.AddToCodelet("ROWSETNULLORDEFAULT", "this[this.myTable.Column" + TTable.NiceFieldName(
col) + ".Ordinal] = " + defaultValue + ";" + Environment.NewLine);
}
else
{
snippet.AddToCodelet("ROWSETNULLORDEFAULT", "this.SetNull(this.myTable.Column" + TTable.NiceFieldName(
col) + ");" + Environment.NewLine);
}
}
Template.InsertSnippet(WhereToInsert, snippet);
}
/// <summary>
/// create the code for the definition of a typed table
/// </summary>
/// <param name="Template"></param>
/// <param name="currentTable"></param>
/// <param name="origTable"></param>
/// <param name="WhereToInsert"></param>
/// <param name="CalledFromDataSet"></param>
public static void InsertTableDefinition(ProcessTemplate Template,
TTable currentTable,
TTable origTable,
string WhereToInsert,
Boolean CalledFromDataSet)
{
ProcessTemplate snippet = Template.GetSnippet("TYPEDTABLE");
string derivedTable = "";
if (origTable != null)
{
snippet.SetCodelet("BASECLASSTABLE", TTable.NiceTableName(currentTable.strName) + "Table");
derivedTable = "new ";
snippet.SetCodelet("TABLEID", origTable.iOrder.ToString());
}
else
{
snippet.SetCodelet("BASECLASSTABLE", "TTypedDataTable");
snippet.SetCodelet("TABLEID", currentTable.iOrder.ToString());
}
snippet.SetCodelet("NEW", derivedTable);
snippet.SetCodeletComment("TABLE_DESCRIPTION", currentTable.strDescription);
snippet.SetCodelet("TABLENAME", currentTable.strDotNetName);
if (CalledFromDataSet)
{
snippet.SetCodelet("TABLEINTDS", "TableInTDS");
}
else
{
snippet.SetCodelet("TABLEINTDS", "");
}
if (currentTable.AvailableForCustomReport)
{
snippet.SetCodelet("AVAILABLEFORCUSTOMREPORT", "true");
}
else
{
snippet.SetCodelet("AVAILABLEFORCUSTOMREPORT", "false");
}
snippet.SetCodelet("CUSTOMREPORTPERMISSION", currentTable.CustomReportPermission);
if (currentTable.strVariableNameInDataset != null)
{
snippet.SetCodelet("TABLEVARIABLENAME", currentTable.strVariableNameInDataset);
}
else
{
snippet.SetCodelet("TABLEVARIABLENAME", currentTable.strDotNetName);
}
snippet.SetCodelet("DBTABLENAME", currentTable.strName);
snippet.SetCodelet("DBTABLELABEL", currentTable.strLabel);
if (currentTable.HasPrimaryKey())
{
TConstraint primKey = currentTable.GetPrimaryKey();
bool first = true;
string primaryKeyColumns = "";
int prevIndex = -1;
// the fields in the primary key should be used in the same order as in the table.
// otherwise this is causing confusion. eg. a_processed_fee
foreach (TTableField column in currentTable.grpTableField)
{
int newIndex = -1;
if (primKey.strThisFields.Contains(column.strName))
{
newIndex = primKey.strThisFields.IndexOf(column.strName);
}
else if (primKey.strThisFields.Contains(TTable.NiceFieldName(column)))
{
newIndex = primKey.strThisFields.IndexOf(TTable.NiceFieldName(column));
}
if (newIndex != -1)
{
if (newIndex < prevIndex)
{
throw new Exception("Please fix the order of the fields in the primary key of table " + currentTable.strName);
}
prevIndex = newIndex;
}
}
// the fields in the primary key should be used in the same order as in the table.
// otherwise this is causing confusion. eg. a_processed_fee
foreach (TTableField column in currentTable.grpTableField)
{
if (primKey.strThisFields.Contains(column.strName) || primKey.strThisFields.Contains(TTable.NiceFieldName(column)))
{
string columnName = column.strName;
string toAdd = currentTable.grpTableField.IndexOf(currentTable.GetField(columnName)).ToString();
if (!first)
{
toAdd = ", " + toAdd;
primaryKeyColumns += ",";
}
first = false;
snippet.AddToCodelet("COLUMNPRIMARYKEYORDER", toAdd);
primaryKeyColumns += "Column" + TTable.NiceFieldName(currentTable.GetField(columnName));
}
}
if (primaryKeyColumns.Length > 0)
{
snippet.SetCodelet("PRIMARYKEYCOLUMNS", primaryKeyColumns);
snippet.SetCodelet("PRIMARYKEYCOLUMNSCOUNT", primKey.strThisFields.Count.ToString());
}
}
else
{
snippet.AddToCodelet("COLUMNPRIMARYKEYORDER", "");
}
if (currentTable.HasUniqueKey())
{
TConstraint primKey = currentTable.GetFirstUniqueKey();
bool first = true;
foreach (string columnName in primKey.strThisFields)
{
string toAdd = currentTable.grpTableField.IndexOf(currentTable.GetField(columnName)).ToString();
if (!first)
{
toAdd = ", " + toAdd;
}
first = false;
snippet.AddToCodelet("COLUMNUNIQUEKEYORDER", toAdd);
}
}
else
{
snippet.AddToCodelet("COLUMNUNIQUEKEYORDER", "");
}
int colOrder = 0;
Boolean CustomReportFieldAdded = false;
ProcessTemplate tempTemplate = null;
foreach (TTableField col in currentTable.grpTableField)
{
col.strTableName = currentTable.strName;
string columnOverwrite = "";
bool writeColumnProperties = true;
if ((origTable != null) && (origTable.GetField(col.strName, false) != null))
{
columnOverwrite = "new ";
if (origTable.GetField(col.strName).iOrder == colOrder)
{
// same order number, save some lines of code by not writing them
writeColumnProperties = false;
}
}
if (writeColumnProperties && (columnOverwrite.Length == 0))
{
tempTemplate = Template.GetSnippet("DATACOLUMN");
tempTemplate.SetCodeletComment("COLUMN_DESCRIPTION", col.strDescription);
tempTemplate.SetCodelet("COLUMNNAME", TTable.NiceFieldName(col));
snippet.InsertSnippet("DATACOLUMNS", tempTemplate);
}
if (writeColumnProperties)
{
tempTemplate = Template.GetSnippet("COLUMNIDS");
tempTemplate.SetCodelet("COLUMNNAME", TTable.NiceFieldName(col));
tempTemplate.SetCodelet("COLUMNORDERNUMBER", colOrder.ToString());
tempTemplate.SetCodelet("NEW", columnOverwrite);
snippet.InsertSnippet("COLUMNIDS", tempTemplate);
}
if (origTable == null)
{
tempTemplate = Template.GetSnippet("COLUMNINFO");
tempTemplate.SetCodelet("COLUMNORDERNUMBER", colOrder.ToString());
tempTemplate.SetCodelet("COLUMNNAME", TTable.NiceFieldName(col));
tempTemplate.SetCodelet("COLUMNDBNAME", col.strName);
tempTemplate.SetCodelet("COLUMNLABEL", col.strLabel);
tempTemplate.SetCodelet("COLUMNODBCTYPE", CodeGenerationPetra.ToOdbcTypeString(col));
tempTemplate.SetCodelet("COLUMNLENGTH", col.iLength.ToString());
tempTemplate.SetCodelet("COLUMNNOTNULL", col.bNotNull.ToString().ToLower());
tempTemplate.SetCodelet("COLUMNCOMMA", colOrder + 1 < currentTable.grpTableField.Count ? "," : "");
snippet.InsertSnippet("COLUMNINFO", tempTemplate);
}
tempTemplate = Template.GetSnippet("INITCLASSADDCOLUMN");
tempTemplate.SetCodelet("COLUMNDBNAME", col.strName);
tempTemplate.SetCodelet("COLUMNDOTNETTYPE", col.GetDotNetType());
tempTemplate.SetCodelet("COLUMNDOTNETTYPENOTNULLABLE", col.GetDotNetType().Replace("?", ""));
snippet.InsertSnippet("INITCLASSADDCOLUMN", tempTemplate);
tempTemplate = Template.GetSnippet("INITVARSCOLUMN");
tempTemplate.SetCodelet("COLUMNDBNAME", col.strName);
tempTemplate.SetCodelet("COLUMNNAME", TTable.NiceFieldName(col));
snippet.InsertSnippet("INITVARSCOLUMN", tempTemplate);
if (col.bAvailableForCustomReport)
{
tempTemplate = Template.GetSnippet("INITVARSCUSTOMREPORTFIELDLIST");
if (CustomReportFieldAdded)
{
tempTemplate.SetCodelet("LISTDELIMITER", ",");
}
else
{
tempTemplate.SetCodelet("LISTDELIMITER", "");
}
tempTemplate.SetCodelet("COLUMNDBNAME", col.strName);
snippet.InsertSnippet("INITVARSCUSTOMREPORTFIELDLIST", tempTemplate);
CustomReportFieldAdded = true;
}
if (writeColumnProperties)
{
tempTemplate = Template.GetSnippet("STATICCOLUMNPROPERTIES");
tempTemplate.SetCodelet("COLUMNDBNAME", col.strName);
tempTemplate.SetCodelet("COLUMNNAME", TTable.NiceFieldName(col));
tempTemplate.SetCodelet("COLUMNHELP", col.strHelp.Replace("\"", "\\\""));
tempTemplate.SetCodelet("COLUMNLENGTH", col.iLength.ToString());
tempTemplate.SetCodelet("NEW", columnOverwrite);
snippet.InsertSnippet("STATICCOLUMNPROPERTIES", tempTemplate);
}
colOrder++;
}
if (!CustomReportFieldAdded)
{
// fill snippet if nothing was added yet
tempTemplate = Template.GetSnippet("INITVARSCUSTOMREPORTFIELDLISTEMPTY");
tempTemplate.SetCodelet("EMPTY", "");
snippet.InsertSnippet("INITVARSCUSTOMREPORTFIELDLIST", tempTemplate);
}
Template.InsertSnippet(WhereToInsert, snippet);
}
/// <summary>
/// write the definition for the code of validation of a typed table
/// </summary>
/// <param name="Template"></param>
/// <param name="currentTable"></param>
/// <param name="origTable"></param>
/// <param name="WhereToInsert"></param>
public static void InsertTableValidation(ProcessTemplate Template, TTable currentTable, TTable origTable, string WhereToInsert)
{
ProcessTemplate snippet = Template.GetSnippet("TABLEVALIDATION");
string ReasonForAutomValidation;
bool CheckForEmptyDateGenerated;
snippet.SetCodeletComment("TABLE_DESCRIPTION", currentTable.strDescription);
snippet.SetCodelet("TABLENAME", currentTable.strDotNetName);
foreach (TTableField col in currentTable.grpTableField)
{
ProcessTemplate columnTemplate;
ProcessTemplate validateColumnTemplate;
CheckForEmptyDateGenerated = false;
// NOT NULL checks
if (TDataValidation.GenerateAutoValidationCodeForDBTableField(col, TDataValidation.TAutomDataValidationScope.advsNotNullChecks,
currentTable.grpConstraint, out ReasonForAutomValidation))
{
if (col.GetDotNetType().Contains("DateTime"))
{
// CHECKEMPTYDATE has NULL as invalid so we use this test with VALIDATECOLUMN2 (test not enclosed in 'if')
validateColumnTemplate = Template.GetSnippet("CHECKEMPTYDATE");
validateColumnTemplate.SetCodelet("COLUMNNAME", col.strNameDotNet);
columnTemplate = Template.GetSnippet("VALIDATECOLUMN2");
columnTemplate.InsertSnippet("COLUMNSPECIFICCHECK", validateColumnTemplate);
columnTemplate.SetCodelet("COLUMNNAME", col.strNameDotNet);
columnTemplate.SetCodelet("COLUMNSPECIFICCOMMENT", "'" + col.strNameDotNet + "' " + ReasonForAutomValidation);
snippet.InsertSnippet("VALIDATECOLUMNS", columnTemplate);
CheckForEmptyDateGenerated = true;
}
else
{
// Check all other types with a general NOT NULL check - again using VALIDATECOLUMN2 (test not enclosed in 'if')
validateColumnTemplate = Template.GetSnippet("CHECKGENERALNOTNULL");
validateColumnTemplate.SetCodelet("COLUMNNAME", col.strNameDotNet);
columnTemplate = Template.GetSnippet("VALIDATECOLUMN2");
columnTemplate.InsertSnippet("COLUMNSPECIFICCHECK", validateColumnTemplate);
columnTemplate.SetCodelet("COLUMNNAME", col.strNameDotNet);
columnTemplate.SetCodelet("COLUMNSPECIFICCOMMENT", "'" + col.strNameDotNet + "' " + ReasonForAutomValidation);
snippet.InsertSnippet("VALIDATECOLUMNS", columnTemplate);
}
// Additionally we do not allow empty string in primary keys or columns that are foreign keys
if (col.GetDotNetType().Contains("String") && ReasonForAutomValidation.Contains(" and "))
{
validateColumnTemplate = Template.GetSnippet("CHECKEMPTYSTRING");
validateColumnTemplate.SetCodelet("COLUMNNAME", col.strNameDotNet);
columnTemplate = Template.GetSnippet("VALIDATECOLUMN");
columnTemplate.InsertSnippet("COLUMNSPECIFICCHECK", validateColumnTemplate);
columnTemplate.SetCodelet("COLUMNNAME", col.strNameDotNet);
columnTemplate.SetCodelet("COLUMNSPECIFICCOMMENT", "'" + col.strNameDotNet + "' " + ReasonForAutomValidation);
snippet.InsertSnippet("VALIDATECOLUMNS", columnTemplate);
}
}
if (!CheckForEmptyDateGenerated)
{
// Date checks
// If a NULL date is not allowed we will have already tested for that above
if (TDataValidation.GenerateAutoValidationCodeForDBTableField(col, TDataValidation.TAutomDataValidationScope.advsDateChecks,
null, out ReasonForAutomValidation))
{
columnTemplate = Template.GetSnippet("VALIDATECOLUMN2");
columnTemplate.SetCodelet("COLUMNNAME", col.strNameDotNet);
// CHECKVALIDDATE allows NULL to be valid but ensures that otherwise the date is correctly formed
validateColumnTemplate = Template.GetSnippet("CHECKVALIDDATE");
validateColumnTemplate.SetCodelet("COLUMNNAME", col.strNameDotNet);
validateColumnTemplate.SetCodelet("COLUMNLENGTH", (col.iCharLength * 2).ToString());
columnTemplate.InsertSnippet("COLUMNSPECIFICCHECK", validateColumnTemplate);
columnTemplate.SetCodelet("COLUMNSPECIFICCOMMENT", "'" + col.strNameDotNet + "' " + ReasonForAutomValidation);
snippet.InsertSnippet("VALIDATECOLUMNS", columnTemplate);
}
}
// String Length checks
if (TDataValidation.GenerateAutoValidationCodeForDBTableField(col, TDataValidation.TAutomDataValidationScope.advsStringLengthChecks,
null, out ReasonForAutomValidation))
{
columnTemplate = Template.GetSnippet("VALIDATECOLUMN");
columnTemplate.SetCodelet("COLUMNNAME", col.strNameDotNet);
validateColumnTemplate = Template.GetSnippet("CHECKSTRINGLENGTH");
validateColumnTemplate.SetCodelet("COLUMNNAME", col.strNameDotNet);
validateColumnTemplate.SetCodelet("COLUMNLENGTH", (col.iCharLength * 2).ToString());
columnTemplate.InsertSnippet("COLUMNSPECIFICCHECK", validateColumnTemplate);
columnTemplate.SetCodelet("COLUMNSPECIFICCOMMENT", "'" + col.strNameDotNet + "' " + ReasonForAutomValidation);
snippet.InsertSnippet("VALIDATECOLUMNS", columnTemplate);
}
// Number Range checks
if (TDataValidation.GenerateAutoValidationCodeForDBTableField(col, TDataValidation.TAutomDataValidationScope.advsNumberRangeChecks,
null, out ReasonForAutomValidation))
{
columnTemplate = Template.GetSnippet("VALIDATECOLUMN");
columnTemplate.SetCodelet("COLUMNNAME", col.strNameDotNet);
validateColumnTemplate = Template.GetSnippet("CHECKNUMBERRANGE");
validateColumnTemplate.SetCodelet("COLUMNNAME", col.strNameDotNet);
validateColumnTemplate.SetCodelet("NUMBEROFDECIMALDIGITS", col.iLength.ToString());
validateColumnTemplate.SetCodelet("NUMBEROFFRACTIONALDIGITS", col.iDecimals > 0 ? col.iDecimals.ToString() : "0");
columnTemplate.InsertSnippet("COLUMNSPECIFICCHECK", validateColumnTemplate);
columnTemplate.SetCodelet("COLUMNSPECIFICCOMMENT", "'" + col.strNameDotNet + "' " + ReasonForAutomValidation);
snippet.InsertSnippet("VALIDATECOLUMNS", columnTemplate);
}
}
Template.InsertSnippet(WhereToInsert, snippet);
}
