/// <summary> /// Gets the Oracle datatype definition as string /// </summary> /// <param name="column">The column.</param> /// <returns></returns> public static string OracleDataType(DatabaseColumn column) { if (column == null) { return(string.Empty); } if (string.IsNullOrEmpty(column.DbDataType)) { return(string.Empty); } var dataType = column.DbDataTypeStandard(); int providerType = GetProviderType(column); var precision = column.Precision; var scale = column.Scale; var length = column.Length; dataType = PostgreSqlToSqlServerConversion(dataType); //oracle to sql server translation dataType = SqlServerToOracleConversion(dataType, providerType, length); if (dataType == "NUMERIC" || dataType == "DECIMAL") { var writeScale = ((scale != null) && (scale > 0) ? "," + scale : ""); dataType = dataType + " (" + precision + writeScale + ")"; } return(dataType); }
public string WriteDataType(DatabaseColumn column) { //sqlite is not strongly typed, and the type affinities (http://www.sqlite.org/datatype3.html) are very limited // (text, integer, real, blob) //the ado provider uses the column types for richer support //ado mapping http://sqlite.phxsoftware.com/forums/t/31.aspx if (column == null) { return(string.Empty); } var dt = column.DataType; if (dt != null) { if (dt.IsString) { return("TEXT"); } if (dt.IsInt) { return("INTEGER"); } if (dt.IsNumeric) { return("NUMERIC"); //Integer or Real } if (dt.IsFloat) { return("REAL"); } } if (string.IsNullOrEmpty(column.DbDataType)) { return(string.Empty); } var dataType = column.DbDataTypeStandard(); if (dataType == "IMAGE" || dataType.IndexOf("BINARY", StringComparison.OrdinalIgnoreCase) != -1) { return("BLOB"); } if (dataType == "BIT") { return("INTEGER"); // boolean = 0 or 1 } if (dataType == "DATE" || dataType == "DATETIME") { //a hint to the ado provider return("DATETIME"); } return(dataType); }
public static string OracleDataTypeForParameter(DatabaseColumn column) { if (column == null) { return(string.Empty); } if (string.IsNullOrEmpty(column.DbDataType)) { return(string.Empty); } var dataType = column.DbDataTypeStandard(); int providerType = GetProviderType(column); var length = column.Length; //oracle to sql server translation dataType = SqlServerToOracleConversion(dataType, providerType, length); return(dataType); }
public string WriteDataType(DatabaseColumn column) { if (column == null) { return(string.Empty); } if (string.IsNullOrEmpty(column.DbDataType)) { return(string.Empty); } var dataType = column.DbDataTypeStandard(); dataType = OtherDatabaseTypesToDb2(dataType, column); if ((dataType.StartsWith("TIMESTAMP", StringComparison.OrdinalIgnoreCase) || dataType == "TIME") && column.DateTimePrecision > 0) { dataType = dataType + " (" + column.DateTimePrecision + ")"; } //write out datatype definition if (IsString(column, dataType) && column.Length > 0) { dataType = dataType + " (" + column.Length + ")"; } if (dataType == "NUMERIC" || dataType == "DECIMAL") { var scale = column.Scale; var precision = column.Precision; var writeScale = ((scale != null) && (scale > 0) ? "," + scale : ""); if (precision > 0) { dataType = dataType + " (" + precision + writeScale + ")"; } } return(dataType); }
public string WriteDataType(DatabaseColumn column) { if (column == null) { return(string.Empty); } if (string.IsNullOrEmpty(column.DbDataType)) { return(string.Empty); } var sql = string.Empty; var dataType = column.DbDataTypeStandard(); var precision = column.Precision; var scale = column.Scale; var length = column.Length; if (dataType == "BOOLEAN") { dataType = "NUMBER"; precision = 1; scale = 0; } //sql server to oracle translation dataType = SqlServerToOracleConversion(dataType, GetProviderType(column), length); if (dataType == "UNIQUEIDENTIFIER") { dataType = "RAW"; length = 16; } if (dataType == "NUMERIC") { dataType = "NUMBER"; } if (dataType == "INT") { dataType = "NUMBER"; precision = 9; scale = 0; } if (dataType == "BIGINT") { dataType = "NUMBER"; precision = 19; scale = 0; } if (dataType == "SMALLINT") { dataType = "NUMBER"; precision = 5; scale = 0; } if (dataType == "BIT") { dataType = "NUMBER"; precision = 1; scale = 0; } if (dataType == "DECIMAL") { dataType = "NUMBER"; precision = 18; scale = 0; } if (dataType == "MONEY") { dataType = "NUMBER"; precision = 15; scale = 4; } string defaultValue = FixDefaultValue(column); //write out Oracle datatype definition if (dataType == "NVARCHAR2") { if (length == -1) { dataType = "CLOB"; } else { //don't specify "CHAR" for NVARCHAR2 sql = dataType + " (" + length + ")"; if (!string.IsNullOrEmpty(defaultValue)) { sql += " DEFAULT " + AddQuotedDefault(defaultValue); } } } if (dataType == "VARCHAR2") { //assume it's CHAR rather than bytes sql = dataType + " (" + length + " CHAR)"; if (!string.IsNullOrEmpty(defaultValue)) { sql += " DEFAULT " + AddQuotedDefault(defaultValue); } } if (dataType == "CHAR" || dataType == "NCHAR") { sql = dataType + " (" + length + ")"; if (!string.IsNullOrEmpty(defaultValue)) { sql += " DEFAULT " + AddQuotedDefault(defaultValue); } } if (dataType == "NUMBER") { if (!precision.HasValue) { sql = "NUMBER"; } else { var writeScale = ((scale != null) && (scale > 0) ? "," + scale : ""); sql = "NUMBER (" + precision + writeScale + ")"; } if (!column.Nullable && (string.IsNullOrEmpty(defaultValue) || column.IdentityDefinition != null)) { sql += " NOT NULL"; } if (column.IdentityDefinition != null) { if (column.DatabaseSchema == null || column.DatabaseSchema.Provider.IndexOf("Oracle", StringComparison.OrdinalIgnoreCase) == -1) { //this doesn't look like oracle, so we're converting. return(sql); } //Oracle 12c- this can be set. //For Oracle 11, IsAutoNumber can be set (by recognizing a sequence), but the definition won't be set. //these must be NUMBER of some sort or it's invalid sql += " GENERATED "; if (column.IdentityDefinition.IdentityByDefault) { sql += "BY DEFAULT "; } sql += "AS IDENTITY"; if (column.IdentityDefinition.IsNonTrivialIdentity()) { sql += string.Format(CultureInfo.InvariantCulture, " (START WITH {0} INCREMENT BY {1})", column.IdentityDefinition.IdentitySeed, column.IdentityDefinition.IdentityIncrement); } //no other options should be done return(sql); } if (!string.IsNullOrEmpty(defaultValue)) { sql += " DEFAULT " + defaultValue; if (!column.Nullable) { sql += " NOT NULL"; } } return(sql); } if (dataType == "REAL") { sql = "REAL"; if (!string.IsNullOrEmpty(defaultValue)) { sql += " DEFAULT " + defaultValue; } } if (dataType == "RAW") { sql = "RAW(" + length + ")"; } if (dataType == "XMLTYPE") { sql = dataType; } if (dataType == "DATE") { sql = "DATE"; if (!string.IsNullOrEmpty(defaultValue)) { sql += " DEFAULT DATE '" + defaultValue + "'"; } } if (dataType == "TIMESTAMP") { sql = "TIMESTAMP" + (precision.HasValue ? " (" + precision + ")" : " (6)"); if (!string.IsNullOrEmpty(defaultValue)) { sql += " DEFAULT " + defaultValue; } } if (dataType == "TIMESTAMP WITH TIME ZONE") { sql = string.Format("TIMESTAMP ({0}) WITH TIME ZONE", precision.HasValue ? precision : 6); if (!string.IsNullOrEmpty(defaultValue)) { sql += " DEFAULT " + defaultValue; } } if (dataType == "CLOB" || dataType == "NCLOB") { sql = dataType; if (!string.IsNullOrEmpty(defaultValue)) { sql += " DEFAULT " + AddQuotedDefault(defaultValue); } } if (dataType == "BLOB") { sql = dataType; if (!string.IsNullOrEmpty(defaultValue)) { sql += " DEFAULT " + defaultValue; //not quoted } } if (column.IsComputed) { sql = "GENERATED ALWAYS AS (" + column.ComputedDefinition + ") VIRTUAL"; } if (string.IsNullOrEmpty(sql)) { sql = column.DbDataType; if (!string.IsNullOrEmpty(defaultValue)) { sql += " DEFAULT " + AddQuotedDefault(defaultValue); } } return(sql.TrimEnd() + (!column.Nullable ? " NOT NULL" : string.Empty)); }
/// <summary> /// Gets the SQLServer datatype definition as string /// </summary> /// <param name="column">The column.</param> /// <returns></returns> public virtual string WriteDataType(DatabaseColumn column) { if (column == null) { return(string.Empty); } if (string.IsNullOrEmpty(column.DbDataType)) { return(string.Empty); } var dataType = column.DbDataTypeStandard(); int providerType = -1; if (column.DataType != null) { providerType = column.DataType.ProviderDbType; } var precision = column.Precision; var scale = column.Scale; var length = column.Length; //oracle to sql server translation if (dataType == "BLOB") { dataType = "VARBINARY"; length = -1; } if (dataType == "CLOB") { dataType = "NVARCHAR"; length = -1; } //In SqlServer, the maximum length allowed for any data type is 8000. //Ergo, TEXTs and NTEXTs that are larger (int.MaxValue or int.MaxValue/2) must be SqlServer types if (dataType == "NTEXT" && length > 8000) { return("NTEXT"); } if (dataType == "TEXT" && length > 8000) { return("TEXT"); } if (_originSqlType == SqlType.SqlServer || _originSqlType == SqlType.SqlServerCe) { if (dataType == "BINARY") { //should not be varbinary return(WriteDataTypeWithLength(dataType, length)); } } else { dataType = ConvertOtherPlatformTypes(dataType, providerType, length, precision, scale); } if ((dataType == "DATETIME2" || dataType == "TIME") && column.DateTimePrecision.HasValue) { dataType = dataType + "(" + column.DateTimePrecision + ")"; } //write out SqlServer datatype definition if (dataType == "NVARCHAR" || dataType == "VARCHAR" || dataType == "CHAR" || dataType == "NCHAR" || dataType == "BINARY" || dataType == "VARBINARY") { dataType = WriteDataTypeWithLength(dataType, length); } if (dataType == "NUMERIC" || dataType == "DECIMAL") { if (precision != null) { var writeScale = ((scale != null) && (scale > 0) ? "," + scale : ""); dataType = dataType + " (" + precision + writeScale + ")"; } } return(dataType); }