public ColumnDetails GetTableDetails(string selectedTableName)
{
var columnDetails = new ColumnDetails();
var conn = new SqlConnection(connectionStr);
conn.Open();
using (conn)
{
using (var tableDetailsCommand = conn.CreateCommand())
{
tableDetailsCommand.CommandText = "select column_name, data_type, character_maximum_length, NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE, IS_NULLABLE from information_schema.columns where table_name = '" + selectedTableName + "'";
using (var sqlDataReader = tableDetailsCommand.ExecuteReader(CommandBehavior.Default))
{
if (sqlDataReader != null)
{
while (sqlDataReader.Read())
{
var columnName = sqlDataReader.GetString(0);
var dataType = sqlDataReader.GetString(1);
int dataLength = 0;
int dataPrecision = 0;
int dataScale = 0;
bool isNullable = false;
try
{
dataLength = sqlDataReader.GetInt32(2);
dataPrecision = sqlDataReader.GetInt32(3);
dataScale = sqlDataReader.GetInt32(4);
isNullable = sqlDataReader.GetBoolean(5);
}catch (Exception)
{
}
columnDetails.Add(new ColumnDetail(columnName, dataType, dataLength, dataPrecision, dataScale, isNullable));
}
}
}
}
using (var constraintCommand = conn.CreateCommand())
{
constraintCommand.CommandText = "select constraint_name from information_schema.TABLE_CONSTRAINTS where table_name = '" + selectedTableName + "' and constraint_type = 'PRIMARY KEY'";
var value = constraintCommand.ExecuteScalar();
if (value != null)
{
var constraintName = (string)value;
using (var pkColumnCommand = conn.CreateCommand())
{
pkColumnCommand.CommandText = "select column_name from information_schema.CONSTRAINT_COLUMN_USAGE where table_name = '" + selectedTableName + "' and constraint_name = '" + constraintName + "'";
var colName = pkColumnCommand.ExecuteScalar();
if (colName != null)
{
var pkColumnName = (string)colName;
var columnDetail = columnDetails.Find(detail => detail.ColumnName.Equals(pkColumnName));
columnDetail.IsPrimaryKey = true;
}
}
}
}
}
columnDetails.Sort((x, y) => x.ColumnName.CompareTo(y.ColumnName));
return columnDetails;
}
public ColumnDetails GetTableDetails(string selectedTableName)
{
var columnDetails = new ColumnDetails();
var conn = new OracleConnection(connectionStr);
conn.Open();
using (conn)
{
using (var tableCommand = conn.CreateCommand())
{
tableCommand.CommandText = "select column_name, data_type, data_length, data_precision, data_scale, nullable from user_tab_cols where table_name = '" + selectedTableName + "'";
using (var oracleDataReader = tableCommand.ExecuteReader(CommandBehavior.Default))
{
while (oracleDataReader.Read())
{
var columnName = oracleDataReader.GetString(0);
var dataType = oracleDataReader.GetString(1);
int dataLength = 0;
int dataPrecision = 0;
int dataScale = 0;
string isNullableStr = "N";
try
{
dataLength = oracleDataReader.GetInt32(2);
dataPrecision = oracleDataReader.GetInt32(3);
dataScale = oracleDataReader.GetInt32(4);
isNullableStr = oracleDataReader.GetString(5);
}
catch (InvalidOperationException)
{
}
bool isNullable = false;
if (isNullableStr.Equals("Y", StringComparison.CurrentCultureIgnoreCase))
{
isNullable = true;
}
columnDetails.Add(new ColumnDetail(columnName, dataType, dataLength, dataPrecision, dataScale, isNullable));
}
}
}
using(var constraintCommand = conn.CreateCommand())
{
constraintCommand.CommandText = "select constraint_name from user_constraints where table_name = '" + selectedTableName + "' and constraint_type = 'P'";
var value = constraintCommand.ExecuteOracleScalar();
if (value != null)
{
var constraintName = (OracleString) value;
using(var pkColumnCommand = conn.CreateCommand())
{
pkColumnCommand.CommandText = "select column_name from user_cons_columns where table_name = '" + selectedTableName+ "' and constraint_name = '"+ constraintName.Value +"'";
var colNames = pkColumnCommand.ExecuteReader();
//var colName = pkColumnCommand.ExecuteOracleScalar();
if (colNames.HasRows)
{
while (colNames.Read())
{
var pkColumnName = (OracleString)colNames.GetOracleValue(0);
var columnDetail = columnDetails.Find(detail => detail.ColumnName.Equals(pkColumnName.Value));
columnDetail.IsPrimaryKey = true;
}
}
}
}
}
}
columnDetails.Sort((x, y) => x.ColumnName.CompareTo(y.ColumnName));
return columnDetails;
}