private void AppendMarkByKeyDataLayerCode(DatabaseTable dbTable, DatabaseTableColumn dbtColumn, StringBuilder sb)
{
sb.AppendLine();
sb.AppendLine(" public void " + this._options.MarkPrefix + dbTable.CsEntityName + "By" + dbtColumn.CSPropertyName + "(" + dbTable.CsEntityName + " par" + dbTable.CsEntityName + ", bool " + this._options.IsDeletedColumn + ", bool " + this._options.IsDisabledColumn + ")");
sb.AppendLine(" {");
sb.AppendLine(" DbParamCollection insDbParamCollection = new DbParamCollection();");
sb.AppendLine(" insDbParamCollection.Add(\"" + dbtColumn.SqlParamName + "\", par" + dbTable.CsEntityName + "." + dbtColumn.CSPropertyName + ");");
sb.AppendLine(" insDbParamCollection.Add(\"@" + this._options.IsDeletedColumn + "\", " + this._options.IsDeletedColumn + ");");
sb.AppendLine(" insDbParamCollection.Add(\"@" + this._options.IsDisabledColumn + "\", " + this._options.IsDisabledColumn + ");");
sb.AppendLine(" gloDbConnector.ExecuteNonQuery(\"" + this._options.AllGeneratedSpPrefix + this._options.MarkPrefix + dbTable.CsEntityName + "By" + dbtColumn.CSPropertyName + "\", insDbParamCollection);");
sb.AppendLine(" }");
sb.AppendLine();
sb.AppendLine(" public void " + this._options.MarkPrefix + this._options.DeletedPhrase + dbTable.CsEntityName + "By" + dbtColumn.CSPropertyName + "(" + dbTable.CsEntityName + " par" + dbTable.CsEntityName + ")");
sb.AppendLine(" {");
sb.AppendLine(" DbParamCollection insDbParamCollection = new DbParamCollection();");
sb.AppendLine(" insDbParamCollection.Add(\"" + dbtColumn.SqlParamName + "\", par" + dbTable.CsEntityName + "." + dbtColumn.CSPropertyName + ");");
sb.AppendLine(" insDbParamCollection.Add(\"@" + this._options.IsDeletedColumn + "\", true);");
sb.AppendLine(" insDbParamCollection.Add(\"@" + this._options.IsDisabledColumn + "\", true);");
sb.AppendLine(" gloDbConnector.ExecuteNonQuery(\"" + this._options.AllGeneratedSpPrefix + this._options.MarkPrefix + dbTable.CsEntityName + "By" + dbtColumn.CSPropertyName + "\", insDbParamCollection);");
sb.AppendLine(" }");
sb.AppendLine();
sb.AppendLine(" public void " + this._options.MarkPrefix + this._options.DisabledPhrase + dbTable.CsEntityName + "By" + dbtColumn.CSPropertyName + "(" + dbTable.CsEntityName + " par" + dbTable.CsEntityName + ")");
sb.AppendLine(" {");
sb.AppendLine(" DbParamCollection insDbParamCollection = new DbParamCollection();");
sb.AppendLine(" insDbParamCollection.Add(\"" + dbtColumn.SqlParamName + "\", par" + dbTable.CsEntityName + "." + dbtColumn.CSPropertyName + ");");
sb.AppendLine(" insDbParamCollection.Add(\"@" + this._options.IsDeletedColumn + "\", false);");
sb.AppendLine(" insDbParamCollection.Add(\"@" + this._options.IsDisabledColumn + "\", true);");
sb.AppendLine(" gloDbConnector.ExecuteNonQuery(\"" + this._options.AllGeneratedSpPrefix + this._options.MarkPrefix + dbTable.CsEntityName + "By" + dbtColumn.CSPropertyName + "\", insDbParamCollection);");
sb.AppendLine(" }");
sb.AppendLine();
}
/// <summary>
/// Gets a unique identifier name for the column, which doesn't conflict with the POCO class itself or with previous identifiers for this POCO.
/// </summary>
/// <param name="table"></param>
/// <param name="column"></param>
/// <param name="previouslyUsedIdentifiers"></param>
/// <returns></returns>
string GetPropertyNameForDatabaseColumn(DatabaseTable table, DatabaseTableColumn column, List <string> previouslyUsedIdentifiers)
{
string name = column.ColumnName;
// Replace forbidden characters
name = rxCleanUp.Replace(name, "_");
// Split multiple words
var parts = splitUpperCase.Split(name).Where(part => part != "_" && part != "-").ToList();
// we'll put first word into TitleCase except if it's a single-char in lowercase (like vNameOfTable) which we assume is a prefix (like v for views) and should be preserved as is
// if first world is a single-char in lowercase (like vNameOfTable) which we assume is a prefix (like v for views) and should be preserved as is
// Recapitalize (to TitleCase) all words
for (int i = 0; i < parts.Count; i++)
{
// if first world is a single-char in lowercase (like vNameOfTable), we assume it's a prefix (like v for views) and should be preserved as is
if (i == 0 && parts[i].Length == 1 && parts[i].ToLower() != parts[i])
{
continue;
}
switch (parts[i])
{
//case "ID": // don't convert "ID" for "Id"
// break;
default:
parts[i] = System.Globalization.CultureInfo.CurrentCulture.TextInfo.ToTitleCase(parts[i].ToLower());
break;
}
}
name = string.Join("", parts);
// can't start with digit
if (char.IsDigit(name[0]))
{
name = "_" + name;
}
// can't be a reserved keyword
if (cs_keywords.Contains(name))
{
name = "@" + name;
}
string attemptName = name;
// check for name clashes
int n = 0;
while (previouslyUsedIdentifiers.Contains((attemptName)) && n < 100)
{
n++;
attemptName = name + n.ToString();
}
previouslyUsedIdentifiers.Add(attemptName);
return(attemptName);
}
private void AppendSelectByKeyDataLayerCode(DatabaseTable dbTable, DatabaseTableColumn dbtColumn, StringBuilder sb)
{
sb.AppendLine();
sb.AppendLine(" public DataTable " + this._options.SelectPrefix + dbTable.CsEntityName + "By" + dbtColumn.CSPropertyName + "(" + dbTable.CsEntityName + " par" + dbTable.CsEntityName + ")");
sb.AppendLine(" {");
sb.AppendLine(" DbParamCollection insDbParamCollection = new DbParamCollection();");
sb.AppendLine(" insDbParamCollection.Add(\"" + dbtColumn.SqlParamName + "\", par" + dbTable.CsEntityName + "." + dbtColumn.CSPropertyName + ");");
sb.AppendLine(" return gloDbConnector.ExecuteDataTable(\"" + this._options.AllGeneratedSpPrefix + this._options.SelectPrefix + dbTable.CsEntityName + "By" + dbtColumn.CSPropertyName + "\", insDbParamCollection);");
sb.AppendLine(" }");
if (!dbTable.HasDeletedDisabled)
{
return;
}
sb.AppendLine();
sb.AppendLine(" public DataTable " + this._options.SelectPrefix + this._options.NotDeletedPhrase + dbTable.CsEntityName + "By" + dbtColumn.CSPropertyName + "(" + dbTable.CsEntityName + " par" + dbTable.CsEntityName + ")");
sb.AppendLine(" {");
sb.AppendLine(" DbParamCollection insDbParamCollection = new DbParamCollection();");
sb.AppendLine(" insDbParamCollection.Add(\"" + dbtColumn.SqlParamName + "\", par" + dbTable.CsEntityName + "." + dbtColumn.CSPropertyName + ");");
sb.AppendLine(" insDbParamCollection.Add(\"@" + this._options.IsDeletedColumn + "\", false);");
sb.AppendLine(" insDbParamCollection.Add(\"@" + this._options.IsDisabledColumn + "\", null);");
sb.AppendLine(" return gloDbConnector.ExecuteDataTable(\"" + this._options.AllGeneratedSpPrefix + this._options.SelectPrefix + dbTable.CsEntityName + "By" + dbtColumn.CSPropertyName + this._options.WithMarkPostfix + "\", insDbParamCollection);");
sb.AppendLine(" }");
sb.AppendLine();
sb.AppendLine(" public DataTable " + this._options.SelectPrefix + this._options.NotDisabledPhrase + dbTable.CsEntityName + "By" + dbtColumn.CSPropertyName + "(" + dbTable.CsEntityName + " par" + dbTable.CsEntityName + ")");
sb.AppendLine(" {");
sb.AppendLine(" DbParamCollection insDbParamCollection = new DbParamCollection();");
sb.AppendLine(" insDbParamCollection.Add(\"" + dbtColumn.SqlParamName + "\", par" + dbTable.CsEntityName + "." + dbtColumn.CSPropertyName + ");");
sb.AppendLine(" insDbParamCollection.Add(\"@" + this._options.IsDeletedColumn + "\", false);");
sb.AppendLine(" insDbParamCollection.Add(\"@" + this._options.IsDisabledColumn + "\", false);");
sb.AppendLine(" return gloDbConnector.ExecuteDataTable(\"" + this._options.AllGeneratedSpPrefix + this._options.SelectPrefix + dbTable.CsEntityName + "By" + dbtColumn.CSPropertyName + this._options.WithMarkPostfix + "\", insDbParamCollection);");
sb.AppendLine(" }");
sb.AppendLine();
}
private void AppendDeleteByKeySP(DatabaseTable dbTable, DatabaseTableColumn dbtColumn, StringBuilder sb, DateTime datetimeTimestamp)
{
sb.AppendLine();
sb.AppendLine("-- Date Of Creation : " + (object)datetimeTimestamp);
sb.AppendLine("CREATE PROC " + this.options.AllGeneratedSpPrefix + this.options.DeletePrefix + dbTable.CsEntityName + "By" + dbtColumn.CSPropertyName);
sb.AppendLine(dbtColumn.SqlParamNameWithCondensedType);
sb.AppendLine("AS");
sb.AppendLine(" DELETE FROM " + dbTable.SqlTableName + " WHERE " + dbtColumn.SqlParamAssignToColumnText);
sb.AppendLine("GO");
sb.AppendLine();
}
private void AppendSelectByKeyBusinessLayerCode(DatabaseTable dbTable, DatabaseTableColumn dtc, StringBuilder sb)
{
sb.AppendLine();
sb.AppendLine(" public DataTable " + this._options.SelectPrefix + dbTable.CsEntityName + "By" + dtc.CSPropertyName + "(" + dbTable.CsEntityName + " par" + dbTable.CsEntityName + ")");
sb.AppendLine(" {");
sb.AppendLine(" " + this._currentDataLayerClassName + " " + this._currentDataLayerInstanceName + " = null;");
sb.AppendLine(" try");
sb.AppendLine(" {");
sb.AppendLine(" " + this._currentDataLayerInstanceName + " = new " + this._currentDataLayerClassName + "(this.TranGuid);");
sb.AppendLine(" return " + this._currentDataLayerInstanceName + "." + this._options.SelectPrefix + dbTable.CsEntityName + "By" + dtc.CSPropertyName + "(par" + dbTable.CsEntityName + ");");
sb.AppendLine(" }");
sb.AppendLine(" catch(DbConnectorException dce)");
sb.AppendLine(" {");
sb.AppendLine(" this.ThrowIfNecessary(dce, " + this._currentDataLayerInstanceName + ");");
sb.AppendLine(" }");
sb.AppendLine(" return null;");
sb.AppendLine(" }");
sb.AppendLine();
if (!dbTable.HasDeletedDisabled)
{
return;
}
sb.AppendLine(" public DataTable " + this._options.SelectPrefix + this._options.NotDeletedPhrase + dbTable.CsEntityName + "By" + dtc.CSPropertyName + "(" + dbTable.CsEntityName + " par" + dbTable.CsEntityName + ")");
sb.AppendLine(" {");
sb.AppendLine(" " + this._currentDataLayerClassName + " " + this._currentDataLayerInstanceName + " = null;");
sb.AppendLine(" try");
sb.AppendLine(" {");
sb.AppendLine(" " + this._currentDataLayerInstanceName + " = new " + this._currentDataLayerClassName + "(this.TranGuid);");
sb.AppendLine(" return " + this._currentDataLayerInstanceName + "." + this._options.SelectPrefix + this._options.NotDeletedPhrase + dbTable.CsEntityName + "By" + dtc.CSPropertyName + "(par" + dbTable.CsEntityName + ");");
sb.AppendLine(" }");
sb.AppendLine(" catch(DbConnectorException dce)");
sb.AppendLine(" {");
sb.AppendLine(" this.ThrowIfNecessary(dce, " + this._currentDataLayerInstanceName + ");");
sb.AppendLine(" }");
sb.AppendLine(" return null;");
sb.AppendLine(" }");
sb.AppendLine();
sb.AppendLine(" public DataTable " + this._options.SelectPrefix + this._options.NotDisabledPhrase + dbTable.CsEntityName + "By" + dtc.CSPropertyName + "(" + dbTable.CsEntityName + " par" + dbTable.CsEntityName + ")");
sb.AppendLine(" {");
sb.AppendLine(" " + this._currentDataLayerClassName + " " + this._currentDataLayerInstanceName + " = null;");
sb.AppendLine(" try");
sb.AppendLine(" {");
sb.AppendLine(" " + this._currentDataLayerInstanceName + " = new " + this._currentDataLayerClassName + "(this.TranGuid);");
sb.AppendLine(" return " + this._currentDataLayerInstanceName + "." + this._options.SelectPrefix + this._options.NotDisabledPhrase + dbTable.CsEntityName + "By" + dtc.CSPropertyName + "(par" + dbTable.CsEntityName + ");");
sb.AppendLine(" }");
sb.AppendLine(" catch(DbConnectorException dce)");
sb.AppendLine(" {");
sb.AppendLine(" this.ThrowIfNecessary(dce, " + this._currentDataLayerInstanceName + ");");
sb.AppendLine(" }");
sb.AppendLine(" return null;");
sb.AppendLine(" }");
sb.AppendLine();
}
private void AppendMarkByKeySP(DatabaseTable dbTable, DatabaseTableColumn dbtColumn, StringBuilder sb, DateTime datetimeTimestamp)
{
sb.AppendLine();
sb.AppendLine("-- Date Of Creation : " + (object)datetimeTimestamp);
sb.AppendLine("CREATE PROC " + this.options.AllGeneratedSpPrefix + this.options.MarkPrefix + dbTable.CsEntityName + "By" + dbtColumn.CSPropertyName);
sb.AppendLine(dbtColumn.SqlParamNameWithCondensedType + ",");
sb.AppendLine("@" + this.options.IsDeletedColumn + " bit, @" + this.options.IsDisabledColumn + " bit");
sb.AppendLine("AS");
sb.AppendLine(" UPDATE " + dbTable.SqlTableName + " SET ");
sb.AppendLine(" " + this.options.IsDeletedColumn + " = @" + this.options.IsDeletedColumn + ", " + this.options.IsDisabledColumn + " = @" + this.options.IsDisabledColumn);
sb.AppendLine(" WHERE " + dbtColumn.SqlParamAssignToColumnText);
sb.AppendLine("GO");
}
bool ShouldProcessColumn(DatabaseTable table, DatabaseTableColumn column)
{
string sqlDataType = column.SqlDataType;
switch (sqlDataType)
{
case "hierarchyid":
case "geography":
return(false);
default:
break;
}
return(true);
}
private void AppendMarkByKeyBusinessLayerCode(DatabaseTable dbTable, DatabaseTableColumn dtc, StringBuilder sb)
{
sb.AppendLine();
sb.AppendLine(" public void " + this._options.MarkPrefix + dbTable.CsEntityName + "By" + dtc.CSPropertyName + "(" + dbTable.CsEntityName + " par" + dbTable.CsEntityName + ", bool " + this._options.IsDeletedColumn + ", bool " + this._options.IsDisabledColumn + ")");
sb.AppendLine(" {");
sb.AppendLine(" " + this._currentDataLayerClassName + " " + this._currentDataLayerInstanceName + " = null;");
sb.AppendLine(" try");
sb.AppendLine(" {");
sb.AppendLine(" " + this._currentDataLayerInstanceName + " = new " + this._currentDataLayerClassName + "(this.TranGuid);");
sb.AppendLine(" " + this._currentDataLayerInstanceName + "." + this._options.MarkPrefix + dbTable.CsEntityName + "By" + dtc.CSPropertyName + "(par" + dbTable.CsEntityName + ", " + this._options.IsDeletedColumn + ", " + this._options.IsDisabledColumn + ");");
sb.AppendLine(" }");
sb.AppendLine(" catch(DbConnectorException dce)");
sb.AppendLine(" {");
sb.AppendLine(" this.ThrowIfNecessary(dce, " + this._currentDataLayerInstanceName + ");");
sb.AppendLine(" }");
sb.AppendLine(" }");
sb.AppendLine();
sb.AppendLine(" public void " + this._options.MarkPrefix + this._options.DeletedPhrase + dbTable.CsEntityName + "By" + dtc.CSPropertyName + "(" + dbTable.CsEntityName + " par" + dbTable.CsEntityName + ")");
sb.AppendLine(" {");
sb.AppendLine(" " + this._currentDataLayerClassName + " " + this._currentDataLayerInstanceName + " = null;");
sb.AppendLine(" try");
sb.AppendLine(" {");
sb.AppendLine(" " + this._currentDataLayerInstanceName + " = new " + this._currentDataLayerClassName + "(this.TranGuid);");
sb.AppendLine(" " + this._currentDataLayerInstanceName + "." + this._options.MarkPrefix + this._options.DeletedPhrase + dbTable.CsEntityName + "By" + dtc.CSPropertyName + "(par" + dbTable.CsEntityName + ");");
sb.AppendLine(" }");
sb.AppendLine(" catch(DbConnectorException dce)");
sb.AppendLine(" {");
sb.AppendLine(" this.ThrowIfNecessary(dce, " + this._currentDataLayerInstanceName + ");");
sb.AppendLine(" }");
sb.AppendLine(" }");
sb.AppendLine();
sb.AppendLine(" public void " + this._options.MarkPrefix + this._options.DisabledPhrase + dbTable.CsEntityName + "By" + dtc.CSPropertyName + "(" + dbTable.CsEntityName + " par" + dbTable.CsEntityName + ")");
sb.AppendLine(" {");
sb.AppendLine(" " + this._currentDataLayerClassName + " " + this._currentDataLayerInstanceName + " = null;");
sb.AppendLine(" try");
sb.AppendLine(" {");
sb.AppendLine(" " + this._currentDataLayerInstanceName + " = new " + this._currentDataLayerClassName + "(this.TranGuid);");
sb.AppendLine(" " + this._currentDataLayerInstanceName + "." + this._options.MarkPrefix + this._options.DisabledPhrase + dbTable.CsEntityName + "By" + dtc.CSPropertyName + "(par" + dbTable.CsEntityName + ");");
sb.AppendLine(" }");
sb.AppendLine(" catch(DbConnectorException dce)");
sb.AppendLine(" {");
sb.AppendLine(" this.ThrowIfNecessary(dce, " + this._currentDataLayerInstanceName + ");");
sb.AppendLine(" }");
sb.AppendLine(" }");
sb.AppendLine();
}
private void AppendSelectByKeySP(DatabaseTable dbTable, DatabaseTableColumn dbtColumn, StringBuilder sb, DateTime datetimeTimestamp)
{
sb.AppendLine();
sb.AppendLine("-- Date Of Creation : " + (object)datetimeTimestamp);
sb.AppendLine("CREATE PROC " + this.options.AllGeneratedSpPrefix + this.options.SelectPrefix + dbTable.CsEntityName + "By" + dbtColumn.CSPropertyName);
sb.AppendLine(dbtColumn.SqlParamNameWithCondensedType);
sb.AppendLine("AS");
sb.AppendLine(" SELECT * FROM " + dbTable.SqlTableName + "(NOLOCK) WHERE " + dbtColumn.SqlParamAssignToColumnText);
sb.AppendLine("GO");
sb.AppendLine();
if (dbTable.PrimaryKeyColumn.SqlColumnName == dbtColumn.SqlColumnName || !dbTable.HasDeletedDisabled)
{
return;
}
sb.AppendLine("-- Date Of Creation : " + (object)datetimeTimestamp);
sb.AppendLine("CREATE PROC " + this.options.AllGeneratedSpPrefix + this.options.SelectPrefix + dbTable.CsEntityName + "By" + dbtColumn.CSPropertyName + this.options.WithMarkPostfix);
sb.AppendLine(dbtColumn.SqlParamNameWithCondensedType + ",");
sb.AppendLine("@" + this.options.IsDeletedColumn + " bit = NULL, @" + this.options.IsDisabledColumn + " bit = NULL");
sb.AppendLine("AS");
sb.AppendLine(" SELECT * FROM " + dbTable.SqlTableName + "(NOLOCK) WHERE " + dbtColumn.SqlParamAssignToColumnText + " AND (" + this.options.IsDeletedColumn + " = @" + this.options.IsDeletedColumn + " OR @" + this.options.IsDeletedColumn + " IS NULL) AND (" + this.options.IsDisabledColumn + " = @" + this.options.IsDisabledColumn + " OR @" + this.options.IsDisabledColumn + " IS NULL)");
sb.AppendLine("GO");
}
string GetTypeDefinitionForDatabaseColumn(DatabaseTable table, DatabaseTableColumn column)
{
System.Type type;
try
{
type = Type.GetType(column.ClrType);
}
catch (Exception ex)
{
return("?!");
}
string typeName = type.Name;
// Everyone prefers int instead of Int32, long instead of Int64, string instead of String, etc. - right?
if (_typeAlias.TryGetValue(type, out string alias))
{
typeName = alias;
}
bool isNullable = column.IsNullable;
// Many developers use POCO instances with null Primary Key to represent a new (in-memory) object, so we can force PKs as nullable
//if (column.IsPrimaryKeyMember)
// isNullable = true;
// reference types (basically only strings?) are nullable by default are nullable, no need to make it explicit
if (!type.IsValueType)
{
isNullable = false;
}
if (!isNullable)
{
return(typeName);
}
return($"{typeName}?"); // some might prefer $"System.Nullable<{typeName}>"
}
protected override IEnumerable <DatabaseTableColumn> GetDatabaseTableColumns(string databaseName, string tableName)
{
DataTable selectedDatabaseTableColumns = null;
DataTable selectedDatabaseTablePrimaryColumns = null;
DataTable selectedDatabaseTableForeignKeyColumns = null;
try
{
using var db = new SqlConnection($"{_connectionStringBuilder};Initial Catalog={databaseName};");
db.Open();
var columnRestrictions = new string[3];
columnRestrictions[0] = databaseName;
columnRestrictions[2] = tableName;
selectedDatabaseTableColumns = db.GetSchema(SqlClientMetaDataCollectionNames.Columns, columnRestrictions);
selectedDatabaseTablePrimaryColumns = db.GetSchema(SqlClientMetaDataCollectionNames.IndexColumns, columnRestrictions);
selectedDatabaseTableForeignKeyColumns = db.GetSchema(SqlClientMetaDataCollectionNames.ForeignKeys, columnRestrictions);
db.Close();
}
catch (Exception exc)
{
Console.Error.WriteLine(exc.Message, exc);
}
if (selectedDatabaseTableColumns == null)
{
yield break;
}
foreach (DataRow columnRow in selectedDatabaseTableColumns.Rows)
{
var columnName = columnRow.ItemArray[3].ToString();
var isNullable = columnRow.ItemArray[6].ToString() == "YES";
var dataType = columnRow.ItemArray[7].ToString();
var type = GetClrType(dataType, isNullable);
var maxLengthStr = columnRow.ItemArray[8].ToString();
int.TryParse(maxLengthStr, out var maxLength);
var column = new DatabaseTableColumn
{
ConnectionType = DbConnectionTypes.MsSql,
DatabaseName = databaseName,
TableName = tableName,
ColumnName = columnName,
DataType = dataType,
Type = type,
TypeNamespace = type.Namespace,
MaxLength = maxLength
};
if (selectedDatabaseTablePrimaryColumns != null)
{
foreach (DataRow indexColumnRow in selectedDatabaseTablePrimaryColumns.Rows)
{
var indexId = indexColumnRow[2].ToString();
var indexColumnName = indexColumnRow[6].ToString();
if (indexColumnName != columnName)
{
continue;
}
if (indexId.IndexOf("PK_", StringComparison.Ordinal) != -1)
{
column.PrimaryKeys.Add(indexId);
}
else
{
column.UniqueKeys.Add(indexId);
}
}
}
yield return(column);
}
}
protected override IEnumerable <DatabaseTableColumn> GetDatabaseTableColumns(string databaseName, string tableName)
{
var columns = new List <DatabaseTableColumn>();
try
{
using var db = new OracleConnection($"{_connectionStringBuilder}");
const string oracleColumnsQuery = @"
SELECT
COLUMN_NAME,
DATA_TYPE,
DATA_LENGTH,
NULLABLE
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME = :tableName";
var oracleColumns = db.Query <OracleColumn>(oracleColumnsQuery, new { tableName });
foreach (var oracleColumn in oracleColumns)
{
var nullable = oracleColumn.Nullable == "Y";
var dataType = GetClrType(oracleColumn.DataType, nullable);
int.TryParse(oracleColumn.DataLength, out var dataLength);
var column = new DatabaseTableColumn
{
ConnectionType = DbConnectionTypes.Oracle,
DatabaseName = databaseName,
TableName = tableName,
ColumnName = oracleColumn.ColumnName,
DataType = oracleColumn.DataType,
Type = dataType,
TypeNamespace = dataType.Namespace,
MaxLength = dataLength
};
columns.Add(column);
}
const string columnConstraintsQuery = @"
SELECT
COLUMN_NAME,
CONSTRAINT_NAME,
CONSTRAINT_TYPE
FROM ALL_CONSTRAINTS
NATURAL JOIN ALL_CONS_COLUMNS
WHERE TABLE_NAME = :tableName";
var columnConstraints = db.Query <OracleConstraint>(columnConstraintsQuery, new { tableName });
foreach (var columnConstraint in columnConstraints)
{
var columnName = columnConstraint.ColumnName;
var column = columns.SingleOrDefault(c => c.TableName == tableName && c.ColumnName == columnName);
if (column != null)
{
var constraintName = columnConstraint.ConstraintName;
var constraintType = columnConstraint.ConstraintType;
switch (constraintType)
{
case "P": // Primary Key
column.PrimaryKeys.Add(constraintName);
break;
case "R": // Foreign Key
column.ForeignKeys.Add(constraintName);
break;
case "U": // Unique Key
column.UniqueKeys.Add(constraintName);
break;
case "C": // Check on a Table
case "O": // Read Only on a View
case "V": // Check Option on a View
default:
// Do nothing
break;
}
}
}
}
catch (Exception exc)
{
Console.Error.WriteLine(exc.Message, exc);
}
return(columns);
}
protected override IEnumerable <DatabaseTableColumn> GetDatabaseTableColumns(string databaseName, string tableName)
{
DataTable selectedDatabaseTableColumns = null;
DataTable selectedDatabaseTablePrimaryColumns = null;
try
{
using (var db = new NpgsqlConnection($"{connectionStringBuilder};Database={databaseName};"))
{
db.Open();
var columnRestrictions = new string[3];
columnRestrictions[0] = databaseName;
columnRestrictions[2] = tableName;
selectedDatabaseTableColumns = db.GetSchema(SqlClientMetaDataCollectionNames.Columns, columnRestrictions);
selectedDatabaseTablePrimaryColumns = db.GetSchema(SqlClientMetaDataCollectionNames.IndexColumns, columnRestrictions);
db.Close();
}
}
catch (Exception exc)
{
Console.Error.WriteLine(exc.Message, exc);
}
if (selectedDatabaseTableColumns != null)
{
foreach (DataRow columnRow in selectedDatabaseTableColumns.Rows)
{
var columnName = columnRow.ItemArray[3].ToString();
var isNullable = columnRow.ItemArray[6].ToString() == "YES";
var dataType = columnRow.ItemArray[7].ToString();
var type = GetClrType(dataType, isNullable);
var maxLengthStr = columnRow.ItemArray[8].ToString();
int.TryParse(maxLengthStr, out var maxLength);
var column = new DatabaseTableColumn
{
ConnectionType = DbConnectionTypes.Postgres,
DatabaseName = databaseName,
TableName = tableName,
ColumnName = columnName,
DataType = dataType,
Type = type,
TypeNamespace = type.Namespace,
MaxLength = maxLength
};
if (selectedDatabaseTablePrimaryColumns != null)
{
foreach (DataRow indexColumnRow in selectedDatabaseTablePrimaryColumns.Rows)
{
var indexId = indexColumnRow[3].ToString();
var indexColumnName = indexColumnRow[4].ToString();
if (indexColumnName == columnName)
{
if (indexId.StartsWith("pk_", StringComparison.InvariantCultureIgnoreCase) ||
indexId.EndsWith("_pk", StringComparison.InvariantCultureIgnoreCase) ||
indexId.EndsWith("_pkey", StringComparison.InvariantCultureIgnoreCase))
{
column.PrimaryKeys.Add(indexId);
}
else
{
column.UniqueKeys.Add(indexId);
}
}
}
}
yield return(column);
}
}
}
public override string GetDataTypeField(DatabaseTableColumn tableColumn)
{
switch (tableColumn.DataType)
{
case DataType.Bit:
return("BIT");
case DataType.TinyInt:
return("TINYINT");
case DataType.Year:
case DataType.SmallInt:
return("SMALLINT");
case DataType.Int:
return("INT");
case DataType.BigInt:
return("BIGINT");
case DataType.Decimal:
return(GetDataTypeValues(tableColumn, "DECIMAL"));
case DataType.Numeric:
return(GetDataTypeValues(tableColumn, "NUMERIC"));
case DataType.Float:
return(GetDataTypeValues(tableColumn, "FLOAT"));
case DataType.Real:
return("REAL");
case DataType.Date:
return("DATE");
case DataType.Time:
return(GetDataTypeValues(tableColumn, "TIME"));
case DataType.DateTime:
return("DATETIME2");
case DataType.TimeStamp:
return("SMALLDATETIME");
case DataType.Char:
return(GetDataTypeValues(tableColumn, "CHAR"));
case DataType.VarChar:
return(GetDataTypeValues(tableColumn, "VARCHAR"));
case DataType.Text:
return("TEXT");
case DataType.NChar:
return(GetDataTypeValues(tableColumn, "NCHAR"));
case DataType.NVarChar:
return(GetDataTypeValues(tableColumn, "NVARCHAR"));
case DataType.NText:
return("NTEXT");
case DataType.Binary:
return(GetDataTypeValues(tableColumn, "BINARY"));
default:
case DataType.VarBinary:
return(GetDataTypeValues(tableColumn, "VARBINARY"));
}
}
private void AppendDropMarkByKeySP(DatabaseTable dbTable, DatabaseTableColumn dbColumn, StringBuilder sb)
{
StoredProcedureGenerator.AppendDropProcedure(this.options.AllGeneratedSpPrefix + this.options.MarkPrefix + dbTable.CsEntityName + "By" + dbColumn.CSPropertyName, sb);
StoredProcedureGenerator.AppendDropProcedure(this.options.AllGeneratedSpPrefix + this.options.MarkPrefix + dbTable.CsEntityName + "By" + dbColumn.CSPropertyName + this.options.WithMarkPostfix, sb);
}
public static string GetColumnNameAsParameter(this DatabaseTableColumn tableColumn)
{
var asd = tableColumn.ColumnName.FirstCharToLower();
return(tableColumn.ColumnName.FirstCharToLower());
}
public static string GetColumnNameAsParameter(this DatabaseTableColumn tableColumn)
{
return($"{char.ToLowerInvariant(tableColumn.ColumnName[0])}{tableColumn.ColumnName.Substring(1)}");
}
