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