public static ForeignKeyInfoCollection GetForeignKeyData(this OleDbConnection connection, string tableName) { var foreignKeyRestrictions = new object[] { null, null, null, null, null, tableName }; bool alreadyOpen = (connection.State != ConnectionState.Closed); if (!alreadyOpen) { connection.Open(); } DataTable foreignKeySchema = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys, foreignKeyRestrictions); if (!alreadyOpen) { connection.Close(); } var foreignKeyData = new ForeignKeyInfoCollection(); foreach (DataRow row in foreignKeySchema.Rows) { var fkInfo = new ForeignKeyInfo { ForeignKeyColumn = row.Field <string>("FK_COLUMN_NAME"), ForeignKeyName = row.Field <string>("FK_NAME"), ForeignKeyTable = row.Field <string>("FK_TABLE_NAME"), PrimaryKeyColumn = row.Field <string>("PK_COLUMN_NAME"), PrimaryKeyName = row.Field <string>("PK_NAME"), PrimaryKeyTable = row.Field <string>("PK_TABLE_NAME") }; foreignKeyData.Add(fkInfo); } return(foreignKeyData); }
public static ForeignKeyInfoCollection GetForeignKeyData(this NpgsqlConnection connection, string tableName, string schema = "public") { const string CMD_FOREIGN_KEYS_FORMAT = @"SELECT FK.""table_name"" AS FK_Table, CU.""column_name"" AS FK_Column, PK.""table_name"" AS PK_Table, PT.""column_name"" AS PK_Column, C.""constraint_name"" AS Constraint_Name FROM information_schema.""referential_constraints"" C INNER JOIN information_schema.""table_constraints"" FK ON C.""constraint_name"" = FK.""constraint_name"" INNER JOIN information_schema.""table_constraints"" PK ON C.""unique_constraint_name"" = PK.""constraint_name"" INNER JOIN information_schema.""key_column_usage"" CU ON C.""constraint_name"" = CU.""constraint_name"" INNER JOIN ( SELECT i1.""table_name"", i2.""column_name"" FROM information_schema.""table_constraints"" i1 INNER JOIN information_schema.""key_column_usage"" i2 ON i1.""constraint_name"" = i2.""constraint_name"" WHERE i1.""constraint_type"" = 'PRIMARY KEY' ) PT ON PT.""table_name"" = PK.""table_name"" WHERE FK.""table_name"" = '{0}' ORDER BY 1,2,3,4"; ForeignKeyInfoCollection foreignKeyData = new ForeignKeyInfoCollection(); bool alreadyOpen = (connection.State != ConnectionState.Closed); if (!alreadyOpen) { connection.Open(); } using (var command = new NpgsqlCommand(string.Format(CMD_FOREIGN_KEYS_FORMAT, tableName), connection)) { command.CommandType = CommandType.Text; using (var reader = command.ExecuteReader()) { while (reader.Read()) { foreignKeyData.Add(new ForeignKeyInfo( reader.GetString(0), reader.GetString(1), reader.GetString(2), reader.GetString(3), string.Empty, reader.GetString(4))); } } } if (!alreadyOpen) { connection.Close(); } return(foreignKeyData); }
public static ForeignKeyInfoCollection GetForeignKeyData(this SqlConnection connection, string tableName) { const string CMD_FOREIGN_KEYS_FORMAT = @"SELECT FK_Table = FK.TABLE_NAME, FK_Column = CU.COLUMN_NAME, PK_Table = PK.TABLE_NAME, PK_Column = PT.COLUMN_NAME, Constraint_Name = C.CONSTRAINT_NAME FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME INNER JOIN ( SELECT i1.TABLE_NAME, i2.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1 INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY' ) PT ON PT.TABLE_NAME = PK.TABLE_NAME WHERE FK.TABLE_NAME = '{0}' ORDER BY 1,2,3,4"; var foreignKeyData = new ForeignKeyInfoCollection(); bool alreadyOpen = (connection.State != ConnectionState.Closed); if (!alreadyOpen) { connection.Open(); } using (var command = new SqlCommand(string.Format(CMD_FOREIGN_KEYS_FORMAT, tableName), connection)) { command.CommandType = CommandType.Text; using (var reader = command.ExecuteReader()) { while (reader.Read()) { foreignKeyData.Add(new ForeignKeyInfo( reader.GetString(0), reader.GetString(1), reader.GetString(2), reader.GetString(3), string.Empty, reader.GetString(4))); } } } if (!alreadyOpen) { connection.Close(); } return(foreignKeyData); }
public static ForeignKeyInfoCollection GetForeignKeyData(this MySqlConnection connection, string tableName) { const string CMD_FOREIGN_KEYS_FORMAT = @"SELECT TABLE_NAME AS FK_Table, COLUMN_NAME AS FK_Column, REFERENCED_TABLE_NAME AS PK_Table, REFERENCED_COLUMN_NAME AS PK_Column, CONSTRAINT_NAME AS Constraint_Name FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = @TableName AND CONSTRAINT_NAME <> 'PRIMARY';"; var foreignKeyData = new ForeignKeyInfoCollection(); bool alreadyOpen = (connection.State != ConnectionState.Closed); if (!alreadyOpen) { connection.Open(); } using (var command = new MySqlCommand(CMD_FOREIGN_KEYS_FORMAT, connection)) { command.CommandType = CommandType.Text; command.Parameters.Add(new MySqlParameter { Direction = ParameterDirection.Input, DbType = DbType.String, ParameterName = "@TableName", Value = tableName }); using (var reader = command.ExecuteReader()) { while (reader.Read()) { foreignKeyData.Add(new ForeignKeyInfo( reader.IsDBNull(0) ? null : reader.GetString(0), reader.IsDBNull(1) ? null : reader.GetString(1), reader.IsDBNull(2) ? null : reader.GetString(2), reader.IsDBNull(3) ? null : reader.GetString(3), string.Empty, reader.IsDBNull(4) ? null : reader.GetString(4))); } } } if (!alreadyOpen) { connection.Close(); } return(foreignKeyData); }
//public virtual FieldCollection GetFields(string tableName) //{ // using (DbConnection connection = CreateDbConnection(DbProviderName, ConnectionDetails.ConnectionString)) // { // using (DbCommand command = connection.CreateCommand()) // { // command.CommandType = CommandType.Text; // command.CommandText = string.Format(Constants.Data.CMD_SELECT_INFO_SCHEMA_COLUMNS, tableName); // FieldCollection fields = new FieldCollection(); // connection.Open(); // using (DbDataReader reader = command.ExecuteReader()) // { // while (reader.Read()) // { // Field field = new Field(); // field.Name = reader.GetString(0); // if (!reader.IsDBNull(1)) // { field.Ordinal = reader.GetInt32(1); } // if (!reader.IsDBNull(2)) // { field.Type = GetScaffolderFieldType(reader.GetString(2)); } // if (!reader.IsDBNull(3)) // { field.IsRequired = reader.GetString(3) == "YES"; } // if (!reader.IsDBNull(4)) // { field.MaxLength = reader.GetInt32(4); } // fields.Add(field); // } // } // connection.Close(); // try // { // command.CommandText = string.Format(Constants.Data.CMD_IS_PRIMARY_KEY_FORMAT, tableName); // var foreignKeyColumns = GetForeignKeyInfo(tableName); // connection.Open(); // using (DbDataReader reader = command.ExecuteReader()) // { // while (reader.Read()) // { // string pkColumn = reader.GetString(0); // Field match = fields.SingleOrDefault(f => f.Name == pkColumn); // if (match != null) // { // match.IsPrimaryKey = true; // } // } // } // connection.Close(); // } // catch (Exception x) // { // TraceService.Instance.WriteConcat(TraceEvent.Error, "Error: Could not get primary key info - ", x.Message); // if (connection.State != ConnectionState.Closed) // { // connection.Close(); // } // } // return fields; // } // } //} #endregion Old public virtual ForeignKeyInfoCollection GetForeignKeyInfo(string tableName) { using (var connection = CreateDbConnection(DbProviderName, ConnectionDetails.ConnectionString)) { const string query = @"SELECT FK_Table = FK.TABLE_NAME, FK_Column = CU.COLUMN_NAME, PK_Table = PK.TABLE_NAME, PK_Column = PT.COLUMN_NAME, Constraint_Name = C.CONSTRAINT_NAME FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME INNER JOIN ( SELECT i1.TABLE_NAME, i2.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1 INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY' ) PT ON PT.TABLE_NAME = PK.TABLE_NAME WHERE FK.TABLE_NAME = '{0}' ORDER BY 1,2,3,4"; var foreignKeyData = new ForeignKeyInfoCollection(); connection.Open(); using (var command = connection.CreateCommand()) { command.CommandText = string.Format(query, tableName); command.CommandType = CommandType.Text; using (var reader = command.ExecuteReader()) { while (reader.Read()) { foreignKeyData.Add(new ForeignKeyInfo( reader.GetString(0), reader.GetString(1), reader.GetString(2), reader.GetString(3), string.Empty, reader.GetString(4))); } } } connection.Close(); return(foreignKeyData); } }
public static ForeignKeyInfoCollection GetForeignKeyData(this MySqlConnection connection, string tableName) { const string CMD_FOREIGN_KEYS_FORMAT = @"SELECT TABLE_NAME AS FK_Table, COLUMN_NAME AS FK_Column, REFERENCED_TABLE_NAME AS PK_Table, REFERENCED_COLUMN_NAME AS PK_Column, CONSTRAINT_NAME AS Constraint_Name FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = 'kore_blogposts' AND CONSTRAINT_NAME <> 'PRIMARY';"; var foreignKeyData = new ForeignKeyInfoCollection(); bool alreadyOpen = (connection.State != ConnectionState.Closed); if (!alreadyOpen) { connection.Open(); } using (var command = new MySqlCommand(string.Format(CMD_FOREIGN_KEYS_FORMAT, tableName), connection)) { command.CommandType = CommandType.Text; using (var reader = command.ExecuteReader()) { while (reader.Read()) { foreignKeyData.Add(new ForeignKeyInfo( reader.GetString(0), reader.GetString(1), reader.GetString(2), reader.GetString(3), string.Empty, reader.GetString(4))); } } } if (!alreadyOpen) { connection.Close(); } return(foreignKeyData); }
public static IEnumerable <ColumnInfo> GetColumnData(this SqlConnection connection, string tableName) { const string CMD_COLUMN_INFO_FORMAT = @"SELECT COLUMN_NAME, COLUMN_DEFAULT, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '{0}'"; List <ColumnInfo> list = new List <ColumnInfo>(); try { ForeignKeyInfoCollection foreignKeyColumns = connection.GetForeignKeyData(tableName); connection.Open(); SqlCommand command = new SqlCommand(string.Format(CMD_COLUMN_INFO_FORMAT, tableName), connection); command.CommandType = CommandType.Text; SqlDataReader reader = command.ExecuteReader(); ColumnInfo columnInfo = null; //ForeignKeyInfoCollection foreignKeyColumns = new ForeignKeyInfoCollection(); //using (SqlConnection connection2 = new SqlConnection(connection.ConnectionString)) //{ // //Must use separate connection for this // foreignKeyColumns = connection2.GetForeignKeyData(tableName); //} while (reader.Read()) { columnInfo = new ColumnInfo(); if (!reader.IsDBNull(0)) { columnInfo.Name = reader.GetString(0); } if (!reader.IsDBNull(1)) { columnInfo.DefaultValue = reader.GetString(1); } else { columnInfo.DefaultValue = string.Empty; } if (foreignKeyColumns.Contains(columnInfo.Name)) { columnInfo.IsForeignKeyColumn = true; } else { try { columnInfo.Type = reader.GetString(2).ToEnum <SqlDbType>(true); } catch (ArgumentNullException) { columnInfo.Type = SqlDbType.Variant; } catch (ArgumentException) { columnInfo.Type = SqlDbType.Variant; } } if (!reader.IsDBNull(3)) { columnInfo.MaxLength = reader.GetInt32(3); } if (!reader.IsDBNull(4)) { if (reader.GetString(4).ToUpperInvariant().Equals("NO")) { columnInfo.IsRequired = false; } else { columnInfo.IsRequired = true; } } list.Add(columnInfo); } } finally { if (connection.State != ConnectionState.Closed) { connection.Close(); } } return(list); }
public static ForeignKeyInfoCollection GetForeignKeyData(this SqlConnection connection, string tableName) { #region Testing //string oleConnectionString = connection.ConnectionString; //if (!oleConnectionString.Contains("Provider")) //{ // oleConnectionString = oleConnectionString.Prepend("Provider=SQLOLEDB;"); //} //using (OleDbConnection oleConnection = new OleDbConnection(oleConnectionString)) //{ // return oleConnection.GetForeignKeyData(tableName); //} #endregion Testing const string CMD_FOREIGN_KEYS_FORMAT = @"SELECT FK_Table = FK.TABLE_NAME, FK_Column = CU.COLUMN_NAME, PK_Table = PK.TABLE_NAME, PK_Column = PT.COLUMN_NAME, Constraint_Name = C.CONSTRAINT_NAME FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME INNER JOIN ( SELECT i1.TABLE_NAME, i2.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1 INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY' ) PT ON PT.TABLE_NAME = PK.TABLE_NAME WHERE FK.TABLE_NAME = '{0}' ORDER BY 1,2,3,4"; ForeignKeyInfoCollection foreignKeyData = new ForeignKeyInfoCollection(); connection.Open(); using (SqlCommand command = new SqlCommand(string.Format(CMD_FOREIGN_KEYS_FORMAT, tableName), connection)) { command.CommandType = CommandType.Text; using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { foreignKeyData.Add(new ForeignKeyInfo( reader.GetString(0), reader.GetString(1), reader.GetString(2), reader.GetString(3), string.Empty, reader.GetString(4))); } } } connection.Close(); return(foreignKeyData); }
public static ColumnInfoCollection GetColumnData(this SqlConnection connection, string tableName) { #region Testing //string oleConnectionString = connection.ConnectionString; //if (!oleConnectionString.Contains("Provider")) //{ // oleConnectionString = oleConnectionString.Prepend("Provider=SQLOLEDB;"); //} //using (OleDbConnection oleConnection = new OleDbConnection(oleConnectionString)) //{ // return oleConnection.GetColumnData(tableName); //} #endregion Testing const string CMD_COLUMN_INFO_FORMAT = @"SELECT COLUMN_NAME, COLUMN_DEFAULT, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '{0}'"; const string CMD_IS_PRIMARY_KEY_FORMAT = @"SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_NAME), 'IsPrimaryKey') = 1 AND TABLE_NAME = '{0}'"; ColumnInfoCollection list = new ColumnInfoCollection(); try { ForeignKeyInfoCollection foreignKeyColumns = connection.GetForeignKeyData(tableName); connection.Open(); using (SqlCommand command = new SqlCommand(string.Format(CMD_COLUMN_INFO_FORMAT, tableName), connection)) { command.CommandType = CommandType.Text; using (SqlDataReader reader = command.ExecuteReader()) { ColumnInfo columnInfo = null; while (reader.Read()) { columnInfo = new ColumnInfo(); if (!reader.IsDBNull(0)) { columnInfo.ColumnName = reader.GetString(0); } if (!reader.IsDBNull(1)) { columnInfo.DefaultValue = reader.GetString(1); } else { columnInfo.DefaultValue = string.Empty; } if (foreignKeyColumns.Contains(columnInfo.ColumnName)) { columnInfo.KeyType = KeyType.ForeignKey; } //else //{ try { columnInfo.DataType = DataTypeConvertor.GetSystemType(reader.GetString(2).ToEnum <SqlDbType>(true)); } catch (ArgumentNullException) { columnInfo.DataType = typeof(object); } catch (ArgumentException) { columnInfo.DataType = typeof(object); } //} if (!reader.IsDBNull(3)) { columnInfo.MaximumLength = reader.GetInt32(3); } if (!reader.IsDBNull(4)) { if (reader.GetString(4).ToUpperInvariant().Equals("NO")) { columnInfo.IsNullable = false; } else { columnInfo.IsNullable = true; } } list.Add(columnInfo); } } } } finally { if (connection.State != ConnectionState.Closed) { connection.Close(); } } #region Primary Keys using (SqlCommand command = connection.CreateCommand()) { command.CommandText = string.Format(CMD_IS_PRIMARY_KEY_FORMAT, tableName); connection.Open(); using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { string pkColumn = reader.GetString(0); ColumnInfo match = list[pkColumn]; if (match != null) { match.KeyType = KeyType.PrimaryKey; } } } connection.Close(); } #endregion Primary Keys return(list); }
public static ColumnInfoCollection GetColumnData(this MySqlConnection connection, string tableName) { const string CMD_COLUMN_INFO_FORMAT = @"SELECT column_name, column_default, data_type, character_maximum_length, is_nullable, ordinal_position, numeric_precision, numeric_scale FROM information_schema.columns WHERE table_name = '{0}';"; const string CMD_IS_PRIMARY_KEY_FORMAT = @"SELECT column_name FROM information_schema.key_column_usage kcu INNER JOIN information_schema.table_constraints tc ON kcu.constraint_name = tc.constraint_name WHERE kcu.table_name = '{0}' AND tc.constraint_type = 'PRIMARY KEY'"; ColumnInfoCollection list = new ColumnInfoCollection(); bool alreadyOpen = (connection.State != ConnectionState.Closed); try { ForeignKeyInfoCollection foreignKeyColumns = connection.GetForeignKeyData(tableName); if (!alreadyOpen) { connection.Open(); } using (var command = new MySqlCommand(string.Format(CMD_COLUMN_INFO_FORMAT, tableName), connection)) { command.CommandType = CommandType.Text; using (var reader = command.ExecuteReader()) { ColumnInfo columnInfo = null; while (reader.Read()) { columnInfo = new ColumnInfo(); if (!reader.IsDBNull(0)) { columnInfo.ColumnName = reader.GetString(0); } if (!reader.IsDBNull(1)) { columnInfo.DefaultValue = reader.GetString(1); } else { columnInfo.DefaultValue = string.Empty; } if (foreignKeyColumns.Contains(columnInfo.ColumnName)) { columnInfo.KeyType = KeyType.ForeignKey; } if (!reader.IsDBNull(3)) { columnInfo.MaximumLength = reader.GetInt32(3); } try { // Based on: https://www.devart.com/dotconnect/mysql/docs/datatypemapping.html string type = reader.GetString(2).ToLowerInvariant(); switch (type) { case "bool": columnInfo.DataType = DbType.Boolean; break; case "boolean": columnInfo.DataType = DbType.Boolean; break; case "bit": { // TODO: This won't work. Because that is for character max length. We need to add 2 properties to // ColumnInfo object: "NumericPrecision" and "NumericScale" to be used for numerical data types if (columnInfo.MaximumLength == 1) { columnInfo.DataType = DbType.Boolean; } else { columnInfo.DataType = DbType.Int64; } } break; case "tinyint": columnInfo.DataType = DbType.SByte; break; case "tinyint unsigned": columnInfo.DataType = DbType.Byte; break; case "smallint": columnInfo.DataType = DbType.Int16; break; case "year": columnInfo.DataType = DbType.Int16; break; case "int": columnInfo.DataType = DbType.Int32; break; case "integer": columnInfo.DataType = DbType.Int32; break; case "smallint unsigned": columnInfo.DataType = DbType.UInt16; break; case "mediumint": columnInfo.DataType = DbType.Int32; break; case "bigint": columnInfo.DataType = DbType.Int64; break; case "int unsigned": columnInfo.DataType = DbType.UInt32; break; case "integer unsigned": columnInfo.DataType = DbType.UInt32; break; case "float": columnInfo.DataType = DbType.Single; break; case "double": columnInfo.DataType = DbType.Double; break; case "real": columnInfo.DataType = DbType.Double; break; case "decimal": columnInfo.DataType = DbType.Decimal; break; case "numeric": columnInfo.DataType = DbType.Decimal; break; case "dec": columnInfo.DataType = DbType.Decimal; break; case "fixed": columnInfo.DataType = DbType.Decimal; break; case "bigint unsigned": columnInfo.DataType = DbType.UInt64; break; case "float unsigned": columnInfo.DataType = DbType.Decimal; break; case "double unsigned": columnInfo.DataType = DbType.Decimal; break; case "serial": columnInfo.DataType = DbType.Decimal; break; case "date": columnInfo.DataType = DbType.DateTime; break; case "timestamp": columnInfo.DataType = DbType.DateTime; break; case "datetime": columnInfo.DataType = DbType.DateTime; break; case "datetimeoffset": columnInfo.DataType = DbType.DateTimeOffset; break; case "time": columnInfo.DataType = DbType.Time; break; case "char": { if (columnInfo.MaximumLength == 36) { // This might not be a Guid, but most likely (99% sure) it will be columnInfo.DataType = DbType.Guid; } else { columnInfo.DataType = DbType.String; } } break; case "varchar": columnInfo.DataType = DbType.String; break; case "tinytext": columnInfo.DataType = DbType.String; break; case "text": columnInfo.DataType = DbType.String; break; case "mediumtext": columnInfo.DataType = DbType.String; break; case "longtext": columnInfo.DataType = DbType.String; break; case "set": columnInfo.DataType = DbType.String; break; case "enum": columnInfo.DataType = DbType.String; break; case "nchar": columnInfo.DataType = DbType.String; break; case "national char": columnInfo.DataType = DbType.String; break; case "nvarchar": columnInfo.DataType = DbType.String; break; case "national varchar": columnInfo.DataType = DbType.String; break; case "character varying": columnInfo.DataType = DbType.String; break; case "binary": columnInfo.DataType = DbType.Binary; break; case "varbinary": columnInfo.DataType = DbType.Binary; break; case "tinyblob": columnInfo.DataType = DbType.Binary; break; case "blob": columnInfo.DataType = DbType.Binary; break; case "mediumblob": columnInfo.DataType = DbType.Binary; break; case "longblob": columnInfo.DataType = DbType.Binary; break; case "char byte": columnInfo.DataType = DbType.Binary; break; case "geometry": columnInfo.DataType = DbType.Object; break; default: columnInfo.DataType = DbType.Object; break; } //columnInfo.DataType = DataTypeConvertor.GetSystemType(reader.GetString(2).ToEnum<SqlDbType>(true)); } catch (ArgumentNullException) { columnInfo.DataType = DbType.Object; } catch (ArgumentException) { columnInfo.DataType = DbType.Object; } if (!reader.IsDBNull(4)) { if (reader.GetString(4).ToUpperInvariant().Equals("NO")) { columnInfo.IsNullable = false; } else { columnInfo.IsNullable = true; } } if (!reader.IsDBNull(5)) { columnInfo.OrdinalPosition = reader.GetInt32(5); } if (!reader.IsDBNull(6)) { columnInfo.Precision = reader.GetInt32(6); } if (!reader.IsDBNull(7)) { columnInfo.Scale = reader.GetInt32(7); } list.Add(columnInfo); } } } } finally { if (!alreadyOpen && connection.State != ConnectionState.Closed) { connection.Close(); } } #region Primary Keys using (var command = connection.CreateCommand()) { command.CommandText = string.Format(CMD_IS_PRIMARY_KEY_FORMAT, tableName); alreadyOpen = (connection.State != ConnectionState.Closed); if (!alreadyOpen) { connection.Open(); } using (var reader = command.ExecuteReader()) { while (reader.Read()) { string pkColumn = reader.GetString(0); ColumnInfo match = list[pkColumn]; if (match != null) { match.KeyType = KeyType.PrimaryKey; } } } if (!alreadyOpen) { connection.Close(); } } #endregion Primary Keys return(list); }
public static ForeignKeyInfoCollection GetForeignKeyData(this SqlConnection connection, string tableName, string schema = "dbo") { const string CMD_FOREIGN_KEYS_FORMAT = @"SELECT FK_Table = FK.TABLE_NAME, FK_Column = CU.COLUMN_NAME, PK_Table = PK.TABLE_NAME, PK_Column = PT.COLUMN_NAME, Constraint_Name = C.CONSTRAINT_NAME FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME INNER JOIN ( SELECT i1.TABLE_NAME, i2.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1 INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY' ) PT ON PT.TABLE_NAME = PK.TABLE_NAME WHERE FK.TABLE_NAME = @TableName AND C.CONSTRAINT_SCHEMA = @SchemaName ORDER BY 1,2,3,4"; var foreignKeyData = new ForeignKeyInfoCollection(); bool alreadyOpen = (connection.State != ConnectionState.Closed); if (!alreadyOpen) { connection.Open(); } using (var command = new SqlCommand(CMD_FOREIGN_KEYS_FORMAT, connection)) { command.CommandType = CommandType.Text; command.Parameters.Add(new SqlParameter { Direction = ParameterDirection.Input, DbType = DbType.String, ParameterName = "@TableName", Value = tableName }); command.Parameters.Add(new SqlParameter { Direction = ParameterDirection.Input, DbType = DbType.String, ParameterName = "@SchemaName", Value = schema }); using (var reader = command.ExecuteReader()) { while (reader.Read()) { foreignKeyData.Add(new ForeignKeyInfo( reader.IsDBNull(0) ? null : reader.GetString(0), reader.IsDBNull(1) ? null : reader.GetString(1), reader.IsDBNull(2) ? null : reader.GetString(2), reader.IsDBNull(3) ? null : reader.GetString(3), string.Empty, reader.IsDBNull(4) ? null : reader.GetString(4))); } } } if (!alreadyOpen) { connection.Close(); } return(foreignKeyData); }
public static ForeignKeyInfoCollection GetForeignKeyData(this NpgsqlConnection connection, string tableName, string schema = "public") { const string CMD_FOREIGN_KEYS_FORMAT = @"SELECT FK.""table_name"" AS FK_Table, CU.""column_name"" AS FK_Column, PK.""table_name"" AS PK_Table, PT.""column_name"" AS PK_Column, C.""constraint_name"" AS Constraint_Name FROM information_schema.""referential_constraints"" C INNER JOIN information_schema.""table_constraints"" FK ON C.""constraint_name"" = FK.""constraint_name"" INNER JOIN information_schema.""table_constraints"" PK ON C.""unique_constraint_name"" = PK.""constraint_name"" INNER JOIN information_schema.""key_column_usage"" CU ON C.""constraint_name"" = CU.""constraint_name"" INNER JOIN ( SELECT i1.""table_name"", i2.""column_name"" FROM information_schema.""table_constraints"" i1 INNER JOIN information_schema.""key_column_usage"" i2 ON i1.""constraint_name"" = i2.""constraint_name"" WHERE i1.""constraint_type"" = 'PRIMARY KEY' ) PT ON PT.""table_name"" = PK.""table_name"" WHERE FK.""table_name"" = @TableName AND FK.""table_schema"" = @SchemaName ORDER BY 1,2,3,4"; var foreignKeyData = new ForeignKeyInfoCollection(); bool alreadyOpen = (connection.State != ConnectionState.Closed); if (!alreadyOpen) { connection.Open(); } using (var command = new NpgsqlCommand(CMD_FOREIGN_KEYS_FORMAT, connection)) { command.CommandType = CommandType.Text; command.Parameters.Add(new NpgsqlParameter { Direction = ParameterDirection.Input, DbType = DbType.String, ParameterName = "@TableName", Value = tableName }); command.Parameters.Add(new NpgsqlParameter { Direction = ParameterDirection.Input, DbType = DbType.String, ParameterName = "@SchemaName", Value = schema }); using (var reader = command.ExecuteReader()) { while (reader.Read()) { foreignKeyData.Add(new ForeignKeyInfo( reader.IsDBNull(0) ? null : reader.GetString(0), reader.IsDBNull(1) ? null : reader.GetString(1), reader.IsDBNull(2) ? null : reader.GetString(2), reader.IsDBNull(3) ? null : reader.GetString(3), string.Empty, reader.IsDBNull(4) ? null : reader.GetString(4))); } } } if (!alreadyOpen) { connection.Close(); } return(foreignKeyData); }