public ColumnSchema[] GetTableColumns(string connectionString, TableSchema table)
{
DataTable dt = GetSchemaData(connectionString,
Columns,
null /*restrictions[0] - catalog*/,
null /*restrictions[1] - unused*/,
table.Name /*restrictions[2] - table*/,
null /*restrictions[3] - column*/);
var extendedProperties = new List <ExtendedProperty>();
var columns = new ColumnSchema[dt.Rows.Count];
int columnIndex = 0;
foreach (DataRow dr in dt.Rows)
{
string name = (string)dr[ColumnsNameColumn];
string nativeType = dr.IsNull(ColumnsTypeColumn) ? "text" : (string)dr[ColumnsTypeColumn];
DbType dbType = DbTypeFromType(connectionString, nativeType);
bool allowDBNull = dr.IsNull(ColumnsNullableColumn) || (bool)dr[ColumnsNullableColumn];
int size = dr.IsNull(ColumnsSize) ? 0 : (int)dr[ColumnsSize];
int precision = dr.IsNull(ColumnsPrecision) ? 0 : (int)dr[ColumnsPrecision];
int scale = dr.IsNull(ColumnsScale) ? 0 : (int)dr[ColumnsScale];
int ordinalPosition = dr.IsNull("ORDINAL_POSITION") ? 0 : (int)dr["ORDINAL_POSITION"];
string edmType = dr.IsNull("EDM_TYPE") ? "String" : (string)dr["EDM_TYPE"];
bool isAutoIncrement = !dr.IsNull("AUTOINCREMENT") && (bool)dr["AUTOINCREMENT"];
bool isUnique = !dr.IsNull("UNIQUE") && (bool)dr["UNIQUE"];
bool hasDefault = !dr.IsNull("COLUMN_HASDEFAULT") && (bool)dr["COLUMN_HASDEFAULT"];
string columnDefault = dr.IsNull("COLUMN_DEFAULT") ? string.Empty : (string)dr["COLUMN_DEFAULT"];
string collation = dr.IsNull("COLLATION_NAME") ? string.Empty : (string)dr["COLLATION_NAME"];
extendedProperties.Clear();
extendedProperties.Add(ExtendedProperty.Readonly(ExtendedPropertyNames.Description, ""));
extendedProperties.Add(ExtendedProperty.Readonly(ExtendedPropertyNames.ObjectID, ordinalPosition));
extendedProperties.Add(ExtendedProperty.Readonly(ExtendedPropertyNames.IsIdentity, isAutoIncrement));
extendedProperties.Add(ExtendedProperty.Readonly("CS_IsUnique", isUnique)); // Added for backwards compatibility.
extendedProperties.Add(ExtendedProperty.Readonly("CS_HasDefault", hasDefault)); // Added for backwards compatibility.
extendedProperties.Add(ExtendedProperty.Readonly(ExtendedPropertyNames.DefaultValue, columnDefault));
extendedProperties.Add(ExtendedProperty.Readonly(ExtendedPropertyNames.Collation, collation));
extendedProperties.Add(ExtendedProperty.Readonly(ExtendedPropertyNames.SystemType, edmType));
extendedProperties.Add(ExtendedProperty.Readonly("CS_SQLiteType", edmType)); // Added for backwards compatibility.
var col = new ColumnSchema(table, name, dbType, nativeType, size, Convert.ToByte(precision), scale, allowDBNull, extendedProperties.ToArray());
columns[columnIndex++] = col;
}
return(columns);
}
/// <summary>
/// 获取列的指定长度的说明
/// </summary>
/// <param name="column"列></param>
/// <param name="length">最大长度，传入0则获取全部长度</param>
/// <returns></returns>
public static string GetShortDescription(this ColumnSchema column, int length = 4, bool descriptionEmpthShowColumnName = false)
{
if (length < 1)
{
return(column.Description);
}
if (column.Description != null && column.Description.Trim().Length > length)
{
return(column.Description.Substring(0, length));
}
if (descriptionEmpthShowColumnName && string.IsNullOrEmpty((column.Description ?? "").Trim()))
{
return(column.Name);
}
return(column.Description ?? "");
}
public static string GetTypeAndSize(SchemaExplorer.ColumnSchema column)
{
string ret = String.Empty;
ret += column.NativeType;
if (column.NativeType == "varbinary" ||
column.NativeType == "nvarchar" ||
column.NativeType == "varchar" ||
column.NativeType == "binary" ||
column.NativeType == "char" ||
column.NativeType == "nchar")
{
ret += GetSize(column.Size);
}
if (column.NativeType == "decimal")
{
ret += "(" + column.Precision + ", " + column.Scale + ")";
}
return(ret);
}
public ExtendedProperty[] GetExtendedProperties(string connectionString, SchemaObjectBase schemaObject)
{
List <ExtendedProperty> list = new List <ExtendedProperty>();
if (schemaObject is ColumnSchema)
{
ColumnSchema columnSchema = schemaObject as ColumnSchema;
string commandText = string.Format(
@"SELECT EXTRA, COLUMN_DEFAULT, COLUMN_TYPE,COLUMN_COMMENT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = '{0}' AND TABLE_NAME = '{1}' AND COLUMN_NAME = '{2}'", columnSchema.Table.Database.Name, columnSchema.Table.Name, columnSchema.Name);
using (DbConnection dbConnection = MySQLSchemaProvider.CreateConnection(connectionString))
{
dbConnection.Open();
DbCommand dbCommand = dbConnection.CreateCommand();
dbCommand.CommandText = commandText;
dbCommand.Connection = dbConnection;
using (IDataReader dataReader = dbCommand.ExecuteReader(CommandBehavior.CloseConnection))
{
while (dataReader.Read())
{
string text = dataReader.GetString(0).ToLower();
bool flag = dataReader.IsDBNull(1);
string text2 = "";
if (!flag)
{
text2 = dataReader.GetString(1).ToUpper();
}
string text3 = dataReader.GetString(2).ToUpper();
string textCOMMENT = dataReader.GetString(3).ToUpper();
bool flag2 = text.IndexOf("auto_increment") > -1;
list.Add(new ExtendedProperty("CS_IsIdentity", flag2, columnSchema.DataType));
if (flag2)
{
list.Add(new ExtendedProperty("CS_IdentitySeed", 1, columnSchema.DataType));
list.Add(new ExtendedProperty("CS_IdentityIncrement", 1, columnSchema.DataType));
}
list.Add(new ExtendedProperty("CS_ColumnDefaultIsNull", flag, DbType.Boolean));
list.Add(new ExtendedProperty("CS_Default", text2, DbType.String));
list.Add(new ExtendedProperty("CS_ColumnDefault", text2, DbType.String));
list.Add(new ExtendedProperty("CS_SystemType", text3, DbType.String));
list.Add(new ExtendedProperty("CS_ColumnType", text3, DbType.String));
list.Add(new ExtendedProperty("CS_Description", textCOMMENT, DbType.String));
list.Add(new ExtendedProperty("CS_ColumnExtra", text.ToUpper(), DbType.String));
}
if (!dataReader.IsClosed)
{
dataReader.Close();
}
}
if (dbConnection.State != ConnectionState.Closed)
{
dbConnection.Close();
}
}
}
if (schemaObject is TableSchema)
{
TableSchema tableSchema = schemaObject as TableSchema;
string commandText2 = string.Format("SHOW CREATE TABLE `{0}`.`{1}`", tableSchema.Database.Name, tableSchema.Name);
using (DbConnection dbConnection2 = MySQLSchemaProvider.CreateConnection(connectionString))
{
dbConnection2.Open();
DbCommand dbCommand2 = dbConnection2.CreateCommand();
dbCommand2.CommandText = commandText2;
dbCommand2.Connection = dbConnection2;
using (IDataReader dataReader2 = dbCommand2.ExecuteReader(CommandBehavior.CloseConnection))
{
while (dataReader2.Read())
{
string @string = dataReader2.GetString(1);
list.Add(new ExtendedProperty("CS_CreateTableScript", @string, DbType.String));
list.Add(new ExtendedProperty("CS_CreateTableScript", @string, DbType.String));
}
if (!dataReader2.IsClosed)
{
dataReader2.Close();
}
}
if (dbConnection2.State != ConnectionState.Closed)
{
dbConnection2.Close();
}
}
string commandTextTABLES = string.Format(
@"SELECT TABLE_COMMENT
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = '{0}' AND TABLE_NAME = '{1}'", tableSchema.Database.Name, tableSchema.Name);
using (DbConnection dbConnection3 = MySQLSchemaProvider.CreateConnection(connectionString))
{
dbConnection3.Open();
DbCommand dbCommand2 = dbConnection3.CreateCommand();
dbCommand2.CommandText = commandTextTABLES;
dbCommand2.Connection = dbConnection3;
using (IDataReader dataReader2 = dbCommand2.ExecuteReader(CommandBehavior.CloseConnection))
{
while (dataReader2.Read())
{
string textCOMMENT = dataReader2.GetString(0);
list.Add(new ExtendedProperty("CS_Description", textCOMMENT, DbType.String));
}
if (!dataReader2.IsClosed)
{
dataReader2.Close();
}
}
if (dbConnection3.State != ConnectionState.Closed)
{
dbConnection3.Close();
}
}
}
return(list.ToArray());
}
/// <summary>
/// Gets all columns for a given table.
/// </summary>
/// <param name="connectionString">The connection string used to connect to the target database.</param>
/// <param name="table"></param>
/// <returns></returns>
public ColumnSchema[] GetTableColumns(string connectionString, TableSchema table)
{
//Erik Ejlskov - exclude system columns
string getColumnSql = string.Format(@"SELECT
COLUMN_NAME as [Name],
COLUMN_DEFAULT as [Default],
IS_NULLABLE as [IsNullable],
DATA_TYPE as [DataType],
CHARACTER_MAXIMUM_LENGTH as [Length],
NUMERIC_PRECISION as [Precision],
NUMERIC_SCALE as [Scale],
AUTOINC_SEED,
AUTOINC_INCREMENT,
COLUMN_HASDEFAULT,
COLUMN_FLAGS
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME = '{0}' AND
COLUMN_FLAGS <> 98 AND
COLUMN_FLAGS <> 114
ORDER BY
ORDINAL_POSITION", table.Name);
var columns = new List <ColumnSchema>();
using (SqlCeCommand cmd = GetCeCommand(connectionString, getColumnSql))
{
using (SqlCeResultSet results = cmd.ExecuteResultSet(ResultSetOptions.None))
{
while (results.Read())
{
var extendedProperties = new List <ExtendedProperty>();
if (!results.IsDBNull(7))
{
extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.IsIdentity, true, DbType.Boolean));
extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.IdentitySeed, results["AUTOINC_SEED"].ToString(), DbType.String));
extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.IdentityIncrement, results["AUTOINC_INCREMENT"].ToString(), DbType.String));
}
else
{
extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.IsIdentity, false, DbType.Boolean));
extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.IdentitySeed, "0", DbType.String));
extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.IdentityIncrement, "0", DbType.String));
}
if (results["COLUMN_HASDEFAULT"] != null)
{
extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.DefaultValue, results["Default"].ToString(), DbType.String));
}
else
{
extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.DefaultValue, string.Empty, DbType.String));
}
var name = (string)results["Name"];
var nativeType = (string)results["DataType"];
//Erik Ejlskov - should be "timestamp" instead
if (nativeType == "rowversion")
{
nativeType = "timestamp";
}
DbType dataType = GetDbTypeFromString(nativeType);
if ((dataType == DbType.Guid && results.GetInt32(10) == 378) || (dataType == DbType.Guid && results.GetInt32(10) == 282))
{
extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.IsRowGuidColumn, true, DbType.Boolean));
}
else
{
extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.IsRowGuidColumn, false, DbType.Boolean));
}
int size;
int.TryParse(results["Length"].ToString(), out size);
byte precision;
byte.TryParse(results["Precision"].ToString(), out precision);
int scale;
int.TryParse(results["scale"].ToString(), out scale);
bool allowNull = GetBoolFromYesNo((string)results["IsNullable"]);
var s = new ColumnSchema(table, name, dataType, nativeType, size, precision, scale, allowNull, extendedProperties.ToArray());
columns.Add(s);
} // while(read)
} // using(results)
} // using(command)
return(columns.ToArray());
}
/// <summary>
/// Gets the extended properties for a given schema object.
/// </summary>
/// <param name="connectionString">The connection string used to connect to the target database.</param>
/// <param name="schemaObject"></param>
/// <returns></returns>
public ExtendedProperty[] GetExtendedProperties(string connectionString, SchemaObjectBase schemaObject)
{
List <ExtendedProperty> extendedProperties = new List <ExtendedProperty>();
if (schemaObject is ColumnSchema)
{
ColumnSchema columnSchema = schemaObject as ColumnSchema;
string commandText = string.Format(@"SELECT EXTRA, COLUMN_DEFAULT, COLUMN_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = '{0}' AND TABLE_NAME = '{1}' AND COLUMN_NAME = '{2}'",
columnSchema.Table.Database.Name, columnSchema.Table.Name, columnSchema.Name);
using (DbConnection connection = CreateConnection(connectionString))
{
connection.Open();
DbCommand command = connection.CreateCommand();
command.CommandText = commandText;
command.Connection = connection;
using (IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
{
while (reader.Read())
{
string extra = reader.GetString(0).ToLower();
bool columndefaultisnull = reader.IsDBNull(1);
string columndefault = "";
if (!columndefaultisnull)
{
columndefault = reader.GetString(1).ToUpper();
}
string columntype = reader.GetString(2).ToUpper();
bool isIdentity = (extra.IndexOf("auto_increment") > -1);
extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.IsIdentity, isIdentity, columnSchema.DataType));
if (isIdentity)
{
/*
* MySQL auto_increment doesn't work exactly like SQL Server's IDENTITY
* I believe that auto_increment is equivalent to IDENTITY(1, 1)
* However, auto_increment behaves differently from IDENTITY when used
* with multi-column primary keys. See the MySQL Reference Manual for details.
*/
extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.IdentitySeed, 1, columnSchema.DataType));
extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.IdentityIncrement, 1, columnSchema.DataType));
}
extendedProperties.Add(new ExtendedProperty("CS_ColumnDefaultIsNull", columndefaultisnull, DbType.Boolean)); // Added for Backwards Compatibility.
extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.DefaultValue, columndefault, DbType.String));
extendedProperties.Add(new ExtendedProperty("CS_ColumnDefault", columndefault, DbType.String)); // Added for Backwards Compatibility.
extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.SystemType, columntype, DbType.String));
extendedProperties.Add(new ExtendedProperty("CS_ColumnType", columntype, DbType.String)); // Added for Backwards Compatibility.
extendedProperties.Add(new ExtendedProperty("CS_ColumnExtra", extra.ToUpper(), DbType.String));
}
if (!reader.IsClosed)
{
reader.Close();
}
}
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
}
if (schemaObject is TableSchema)
{
TableSchema tableSchema = schemaObject as TableSchema;
string commandText = string.Format(@"SHOW CREATE TABLE `{0}`.`{1}`", tableSchema.Database.Name, tableSchema.Name);
using (DbConnection connection = CreateConnection(connectionString))
{
connection.Open();
DbCommand command = connection.CreateCommand();
command.CommandText = commandText;
command.Connection = connection;
using (IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
{
while (reader.Read())
{
string createtable = reader.GetString(1);
extendedProperties.Add(new ExtendedProperty("CS_CreateTableScript", createtable, DbType.String));
}
if (!reader.IsClosed)
{
reader.Close();
}
}
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
}
return(extendedProperties.ToArray());
}
/// <summary>
/// 是否是时间列如 Date，DateTime，DateTime2
/// </summary>
/// <param name="column"></param>
/// <returns></returns>
public static bool IsDateColumn(this ColumnSchema column)
{
return(column.DataType == DbType.Date || column.DataType == DbType.DateTime || column.DataType == DbType.DateTime2);
}
/// <summary>
/// 是否是数字列，如 int,double等
/// </summary>
/// <param name="column"></param>
/// <returns></returns>
public static bool IsNumberColumn(this ColumnSchema column)
{
return(column.DataType == DbType.Byte || column.DataType == DbType.Int16 || column.DataType == DbType.Int32 || column.DataType == DbType.Int64 || column.DataType == DbType.Double);
}
/// <summary>
/// 是否是字符串列
/// </summary>
/// <param name="column"></param>
/// <returns></returns>
public static bool IsStringColumn(this ColumnSchema column)
{
return(column.DataType == DbType.AnsiString || column.DataType == DbType.AnsiStringFixedLength || column.DataType == DbType.String || column.DataType == DbType.StringFixedLength);
}
/// <summary>
/// 获取Column的数据库数据类型 SqlDbType
/// </summary>
/// <param name="column"></param>
/// <returns></returns>
public static string GetNativeType(this ColumnSchema column)
{
switch (column.NativeType.ToLower())
{
case "text":
return("SqlDbType.Text");
case "int":
return("SqlDbType.Int");
case "bigint":
return("SqlDbType.BigInt");
case "decimal":
return("SqlDbType.Decimal");
case "smallint":
return("SqlDbType.SmallInt");
case "binary":
return("SqlDbType.Binary");
case "bit":
return("SqlDbType.Bit");
case "float":
return("SqlDbType.Float");
case "tinyint":
return("SqlDbType.TinyInt");
case "money":
return("SqlDbType.Money");
case "smallmoney":
return("SqlDbType.SmallMoney");
case "timestamp":
return("SqlDbType.Timestamp");
case "datetime":
return("SqlDbType.DateTime");
case "datetime2":
return("SqlDbType.DateTime2");
case "date":
return("SqlDbType.Date");
case "datetimeoffset":
return("SqlDbType.DateTimeOffset");
case "char":
return("SqlDbType.Char");
case "nvarchar":
return("SqlDbType.NVarChar");
default:
return("SqlDbType.VarChar");
}
}
/// <summary>
/// 获取与数据库字段类型对应的CSharp类型
/// </summary>
/// <param name="column"></param>
/// <returns></returns>
public static string GetJavaVariableType(this ColumnSchema column)
{
switch (column.DataType)
{
case DbType.String:
case DbType.StringFixedLength:
case DbType.AnsiString:
case DbType.AnsiStringFixedLength: return("String");
case DbType.Binary: return("Integer");
case DbType.Byte: return("byte");
case DbType.Guid: return("Guid");
case DbType.Object: return("object");
case DbType.Boolean: if (column.AllowDBNull)
{
return("Boolean");
}
else
{
return("boolean");
};
case DbType.Currency: if (column.AllowDBNull)
{
return("Float");
}
else
{
return("float");
};
case DbType.Decimal: if (column.AllowDBNull)
{
return("Float");
}
else
{
return("float");
};
case DbType.Double: if (column.AllowDBNull)
{
return("Double");
}
else
{
return("double");
};
case DbType.Int16: if (column.AllowDBNull)
{
return("Short");
}
else
{
return("short");
};
case DbType.UInt16: if (column.AllowDBNull)
{
return("Short");
}
else
{
return("short");
};
case DbType.Int32: if (column.AllowDBNull)
{
return("Integer");
}
else
{
return("int");
};
case DbType.UInt32: if (column.AllowDBNull)
{
return("Integer");
}
else
{
return("int");
};
case DbType.Int64: if (column.AllowDBNull)
{
return("Long");
}
else
{
return("long");
};
case DbType.UInt64: if (column.AllowDBNull)
{
return("Long");
}
else
{
return("long");
};
case DbType.SByte: if (column.AllowDBNull)
{
return("Byte");
}
else
{
return("byte");
};
case DbType.Single: if (column.AllowDBNull)
{
return("Float");
}
else
{
return("float");
};
case DbType.Date: if (column.AllowDBNull)
{
return("Date");
}
else
{
return("Date");
};
case DbType.DateTime: if (column.AllowDBNull)
{
return("Date");
}
else
{
return("Date");
};
case DbType.Time: if (column.AllowDBNull)
{
return("Date");
}
else
{
return("Date");
};
case DbType.VarNumeric: if (column.AllowDBNull)
{
return("float");
}
else
{
return("float");
};
default: return("string");
}
}
/// <summary>
/// 获取与数据库字段类型对应的CSharp类型
/// </summary>
/// <param name="column"></param>
/// <returns></returns>
public static string GetCSharpVariableType(this ColumnSchema column)
{
switch (column.DataType)
{
case DbType.String:
case DbType.StringFixedLength:
case DbType.AnsiString:
case DbType.AnsiStringFixedLength: return("string");
case DbType.Binary: return("Nullable<int>");
case DbType.Byte: return("int");
case DbType.Guid: return("Guid");
case DbType.Object: return("object");
case DbType.Boolean: if (column.AllowDBNull)
{
return("Nullable<bool>");
}
else
{
return("bool");
};
case DbType.Currency: if (column.AllowDBNull)
{
return("Nullable<decimal>");
}
else
{
return("decimal");
};
case DbType.Decimal: if (column.AllowDBNull)
{
return("Nullable<decimal>");
}
else
{
return("decimal");
};
case DbType.Double: if (column.AllowDBNull)
{
return("Nullable<double>");
}
else
{
return("double");
};
case DbType.Int16: if (column.AllowDBNull)
{
return("Nullable<short>");
}
else
{
return("short");
};
case DbType.UInt16: if (column.AllowDBNull)
{
return("Nullable<ushort>");
}
else
{
return("ushort");
};
case DbType.Int32: if (column.AllowDBNull)
{
return("Nullable<int>");
}
else
{
return("int");
};
case DbType.UInt32: if (column.AllowDBNull)
{
return("Nullable<uint>");
}
else
{
return("uint");
};
case DbType.Int64: if (column.AllowDBNull)
{
return("Nullable<long>");
}
else
{
return("long");
};
case DbType.UInt64: if (column.AllowDBNull)
{
return("Nullable<ulong>");
}
else
{
return("ulong");
};
case DbType.SByte: if (column.AllowDBNull)
{
return("Nullable<sbyte>");
}
else
{
return("sbyte");
};
case DbType.Single: if (column.AllowDBNull)
{
return("Nullable<float>");
}
else
{
return("float");
};
case DbType.Date: if (column.AllowDBNull)
{
return("Nullable<DateTime>");
}
else
{
return("DateTime");
};
case DbType.DateTime: if (column.AllowDBNull)
{
return("Nullable<DateTime>");
}
else
{
return("DateTime");
};
case DbType.Time: if (column.AllowDBNull)
{
return("Nullable<TimeSpan>");
}
else
{
return("TimeSpan");
};
case DbType.VarNumeric: if (column.AllowDBNull)
{
return("Nullable<decimal>");
}
else
{
return("decimal");
};
default: return("string");
}
}
public MemberColumnSchema(ColumnSchema memberColumn, ExtendedProperty[] extendedProperties)
{
this._memberColumn = memberColumn;
this._defaultExtendedProperties = extendedProperties;
base._extendedProperties = new ExtendedPropertyCollection(extendedProperties);
}
public MemberColumnSchema(ColumnSchema memberColumn)
{
this._memberColumn = memberColumn;
base._extendedProperties = new ExtendedPropertyCollection();
}
public ExtendedProperty[] GetExtendedProperties(string connectionString, SchemaObjectBase schemaObject)
{
if (schemaObject is TableSchema)
{
TableSchema tableSchema = (TableSchema)schemaObject;
string text = $"select cast(obj_description(relfilenode,'pg_class') as varchar) as comment from pg_class where relname='{tableSchema.Name}';";
List <ExtendedProperty> list = new List <ExtendedProperty>();
using (NpgsqlConnection npgsqlConnection = new NpgsqlConnection(connectionString))
{
npgsqlConnection.Open();
using (NpgsqlCommand npgsqlCommand = new NpgsqlCommand(text, npgsqlConnection))
{
var comment = npgsqlCommand.ExecuteScalar();
list.Add(ExtendedProperty.Readonly("CS_Description", comment?.ToString()));
}
if (npgsqlConnection.State != ConnectionState.Closed)
{
npgsqlConnection.Close();
}
}
return(list.ToArray());
}
if (schemaObject is ColumnSchema)
{
List <ExtendedProperty> list = new List <ExtendedProperty>();
ColumnSchema columnSchema = schemaObject as ColumnSchema;
string text = $"select pg_get_serial_sequence(a.table_name, a.column_name) as EXTRA,a.COLUMN_DEFAULT,a.data_type,c.DeText as description from information_schema.columns as a left join( select pg_attr.attname as colname,pg_constraint.conname as pk_name from pg_constraint inner join pg_class on pg_constraint.conrelid = pg_class.oid inner join pg_attribute pg_attr on pg_attr.attrelid = pg_class.oid and pg_attr.attnum = pg_constraint.conkey[1] inner join pg_type on pg_type.oid = pg_attr.atttypid where pg_class.relname = '{columnSchema.Table.Name}' and pg_constraint.contype = 'p')as b on b.colname = a.column_name left join( select attname, description as DeText from pg_class left join pg_attribute pg_attr on pg_attr.attrelid = pg_class.oid left join pg_description pg_desc on pg_desc.objoid = pg_attr.attrelid and pg_desc.objsubid = pg_attr.attnum where pg_attr.attnum > 0 and pg_attr.attrelid = pg_class.oid and pg_class.relname = '{columnSchema.Table.Name}')as c on c.attname = a.column_name where table_schema = 'public' and table_name = '{columnSchema.Table.Name}' and COLUMN_NAME = '{columnSchema.Name}' order by ordinal_position; ";
using (NpgsqlConnection npgsqlConnection = new NpgsqlConnection(connectionString))
{
npgsqlConnection.Open();
using (NpgsqlCommand npgsqlCommand = new NpgsqlCommand(text, npgsqlConnection))
{
using (IDataReader dataReader = npgsqlCommand.ExecuteReader(CommandBehavior.CloseConnection))
{
while (dataReader.Read())
{
string text2 = dataReader.IsDBNull(0) ? string.Empty : dataReader.GetString(0).ToLower();
string value = dataReader.IsDBNull(1) ? null : dataReader.GetString(1).ToUpper();
string value2 = dataReader.GetString(2).ToUpper();
string description = dataReader.GetString(3);
bool flag = !string.IsNullOrEmpty(text2);
list.Add(new ExtendedProperty("CS_IsIdentity", flag, columnSchema.DataType));
if (flag)
{
list.Add(new ExtendedProperty("CS_IdentitySeed", 1, columnSchema.DataType));
list.Add(new ExtendedProperty("CS_IdentityIncrement", 1, columnSchema.DataType));
}
list.Add(new ExtendedProperty("CS_Default", value, DbType.String));
list.Add(new ExtendedProperty("CS_SystemType", value2, DbType.String));
list.Add(new ExtendedProperty("CS_Sequence", text2.ToUpper(), DbType.String));
list.Add(ExtendedProperty.Readonly("CS_Description", description?.ToString()));
}
if (!dataReader.IsClosed)
{
dataReader.Close();
}
}
}
if (npgsqlConnection.State != ConnectionState.Closed)
{
npgsqlConnection.Close();
}
}
return(list.ToArray());
}
return(new ExtendedProperty[0]);
}
