public ColumnData(string name, SqlDbType type, int typeLength = 100, bool allowNull = true, bool isPrimaryKey = false, bool identity = false, ForeignKey foreignKey = null, bool isUnique = false)
{
Name = name;
Type = type;
TypeLength = typeLength;
IsPrimaryKey = isPrimaryKey;
IsUnique = isUnique;
AllowNull = allowNull;
Identity = identity;
ForeignKey = foreignKey;
}
public TableData GetTable(string tableName)
{
if (this.IsPhysical == false)
{
throw new Exception("Cannot open database");
}
List <ColumnData> columns = new List <ColumnData>();
bool isPrimary = false;
bool isNull;
ForeignKey foreignKey = null;
DataTable tableColumnInfo;
DataTable tableColumns;
using (SqlConnection connection = ConnectToDataBase(this.Name, this.DataSource))
{
try
{
string strCommandGetColumnsInfo = String.Format("SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE FROM information_schema.COLUMNS WHERE TABLE_NAME = '{0}'", tableName);
SqlCommand commandGetColumnsInfo = new SqlCommand(strCommandGetColumnsInfo, connection);
using (SqlDataReader readerColumnsInfo = commandGetColumnsInfo.ExecuteReader())
{
tableColumnInfo = new DataTable();
tableColumnInfo.Load(readerColumnsInfo);
}
string strCommandGetColumns = String.Format("SELECT * FROM [{0}]", tableName);
SqlCommand commandGetColumns = new SqlCommand(strCommandGetColumns, connection);
using (SqlDataReader readerColumns = commandGetColumns.ExecuteReader())
{
tableColumns = new DataTable();
tableColumns.Load(readerColumns);
}
var cols = tableColumns.Columns;
for (int i = 0; i < tableColumnInfo.Rows.Count; i++)
{
string name = tableColumnInfo.Rows[i][0].ToString();
SqlDbType type = ConvertFromStr(tableColumnInfo.Rows[i][1].ToString());
int maxlength = (tableColumnInfo.Rows[i][2].ToString() != "") ? Int16.Parse(tableColumnInfo.Rows[i][2].ToString()) : 0;
if (tableColumnInfo.Rows[i][3].ToString() == "NO")
{
isNull = false;
}
else
{
isNull = true;
}
bool identity = cols[i].AutoIncrement;
bool isUnique = cols[i].Unique;
string strCommandGetColumnKeys = String.Format("SELECT CONSTRAINT_NAME, COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_NAME = '{0}' AND COLUMN_NAME = '{1}'", tableName, name);
SqlCommand commandGetColumnsKeys = new SqlCommand(strCommandGetColumnKeys, connection);
using (SqlDataReader readerColumnsKeys = commandGetColumnsKeys.ExecuteReader())
{
while (readerColumnsKeys.Read())
{
string constraintName = readerColumnsKeys[0].ToString();
string[] parts = constraintName.Split(new char[] { '_' }, StringSplitOptions.RemoveEmptyEntries);
if (parts[0] == "FK")
{
foreignKey = new ForeignKey(parts[2], parts[1]);
}
else if (parts[0] == "PK")
{
isPrimary = true;
}
}
}
columns.Add(new ColumnData(name, type, maxlength, isNull, isPrimary, identity, foreignKey, isUnique));
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
return(TableData.CreateNew(tableName, this, columns));
}
}
public TableData GetTable(string tableName)
{
if (this.IsPhysical == false)
throw new Exception("Cannot open database");
List<ColumnData> columns = new List<ColumnData>();
bool isPrimary = false;
bool isNull;
ForeignKey foreignKey = null;
DataTable tableColumnInfo;
DataTable tableColumns;
using (SqlConnection connection = ConnectToDataBase(this.Name, this.DataSource))
{
try
{
string strCommandGetColumnsInfo = String.Format("SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE FROM information_schema.COLUMNS WHERE TABLE_NAME = '{0}'", tableName);
SqlCommand commandGetColumnsInfo = new SqlCommand(strCommandGetColumnsInfo, connection);
using (SqlDataReader readerColumnsInfo = commandGetColumnsInfo.ExecuteReader())
{
tableColumnInfo = new DataTable();
tableColumnInfo.Load(readerColumnsInfo);
}
string strCommandGetColumns = String.Format("SELECT * FROM [{0}]", tableName);
SqlCommand commandGetColumns = new SqlCommand(strCommandGetColumns, connection);
using (SqlDataReader readerColumns = commandGetColumns.ExecuteReader())
{
tableColumns = new DataTable();
tableColumns.Load(readerColumns);
}
var cols = tableColumns.Columns;
for (int i = 0; i < tableColumnInfo.Rows.Count; i++)
{
string name = tableColumnInfo.Rows[i][0].ToString();
SqlDbType type = ConvertFromStr(tableColumnInfo.Rows[i][1].ToString());
int maxlength = (tableColumnInfo.Rows[i][2].ToString() != "") ? Int16.Parse(tableColumnInfo.Rows[i][2].ToString()) : 0;
if (tableColumnInfo.Rows[i][3].ToString() == "NO")
isNull = false;
else
isNull = true;
bool identity = cols[i].AutoIncrement;
bool isUnique = cols[i].Unique;
string strCommandGetColumnKeys = String.Format("SELECT CONSTRAINT_NAME, COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_NAME = '{0}' AND COLUMN_NAME = '{1}'", tableName, name);
SqlCommand commandGetColumnsKeys = new SqlCommand(strCommandGetColumnKeys, connection);
using (SqlDataReader readerColumnsKeys = commandGetColumnsKeys.ExecuteReader())
{
while (readerColumnsKeys.Read())
{
string constraintName = readerColumnsKeys[0].ToString();
string[] parts = constraintName.Split(new char[] { '_' }, StringSplitOptions.RemoveEmptyEntries);
if (parts[0] == "FK")
foreignKey = new ForeignKey(parts[2], parts[1]);
else if (parts[0] == "PK")
isPrimary = true;
}
}
columns.Add(new ColumnData(name, type, maxlength, isNull, isPrimary, identity, foreignKey, isUnique));
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
return TableData.CreateNew(tableName, this, columns);
}
}
