public void FillTableSchema(Table tbl, TableType tableType)
{
TableColumnCollection columns = new TableColumnCollection();
DataSet dsColumns;
tbl.ForeignKeys = new ForeignKeyCollection();
if (tableType == TableType.Function)
{
if (dsColumns2.Tables[Name] == null)
{
var cmdColumns = new SqlCommand(ROUTINE_COLUMN_SQL_ALL);
var dt = new DataTable(Name);
dt.Load(cmdColumns.GetReader());
dsColumns2.Tables.Add(dt);
}
dsColumns = dsColumns2;
}
else
{
if (dsColumns1.Tables[Name] == null)
{
var cmdColumns = new SqlCommand(TABLE_COLUMN_SQL_ALL);
var dt = new DataTable(Name);
dt.Load(cmdColumns.GetReader());
dsColumns1.Tables.Add(dt);
}
dsColumns = dsColumns1;
}
DataRow[] drColumns = dsColumns.Tables[Name].Select("TableName ='" + tbl.Name + "'", "OrdinalPosition ASC");
for (int i = 0; i < drColumns.Length; i++)
{
TableColumn column = new TableColumn(tbl);
column.ColumnName = drColumns[i][SqlSchemaVariable.COLUMN_NAME].ToString();
column.NativeDataType = drColumns[i][SqlSchemaVariable.DATA_TYPE].ToString();
if (column.NativeDataType == "numeric")
{
column.NativeDataType = string.Format("Decimal({0},{1})", drColumns[i]["precision"], drColumns[i]["scale"]);
}
column.DataType = GetDbType(column.NativeDataType);
if (drColumns[i][SqlSchemaVariable.COLUMN_DEFAULT] != DBNull.Value)
{
string defaultSetting = drColumns[i][SqlSchemaVariable.COLUMN_DEFAULT].ToString().Trim();
if (defaultSetting.ToLower().IndexOf("newsequentialid()") > -1)
{
column.DefaultSetting = SqlSchemaVariable.DEFAULT;
}
else
{
column.DefaultSetting = defaultSetting;
}
}
column.AutoIncrement = Convert.ToBoolean(drColumns[i][SqlSchemaVariable.IS_IDENTITY]);
int maxLength;
int.TryParse(drColumns[i][SqlSchemaVariable.MAX_LENGTH].ToString(), out maxLength);
column.MaxLength = maxLength;
if (maxLength > 0)
{
column.NativeDataType += "(" + maxLength + ")";
}
column.IsNullable = drColumns[i][SqlSchemaVariable.IS_NULLABLE].ToString() == "YES";
bool isComputed = (drColumns[i][SqlSchemaVariable.IS_COMPUTED].ToString() == "1");
column.IsReadOnly = (column.NativeDataType == "timestamp" || isComputed);
columns.Add(column);
tbl.SchemaName = drColumns[i]["Owner"].ToString();
}
tbl.Columns = columns;
if (dsIndex.Tables[Name] == null)
{
var cmdIndex = new SqlCommand(INDEX_SQL_ALL);
DataTable dt = new DataTable(Name);
dt.Load(cmdIndex.GetReader());
dsIndex.Tables.Add(dt);
}
DataRow[] drIndexes = dsIndex.Tables[Name].Select("TableName ='" + tbl.Name + "'");
for (int i = 0; i < drIndexes.Length; i++)
{
string colName = drIndexes[i][SqlSchemaVariable.COLUMN_NAME].ToString();
string constraintType = drIndexes[i][SqlSchemaVariable.CONSTRAINT_TYPE].ToString();
TableColumn column = columns.GetColumn(colName);
if (Utility.IsMatch(constraintType, SqlSchemaVariable.PRIMARY_KEY))
{
column.IsPrimaryKey = true;
}
else if (Utility.IsMatch(constraintType, SqlSchemaVariable.FOREIGN_KEY))
{
column.IsForeignKey = true;
}
//HACK: Allow second pass naming adjust based on whether a column is keyed
column.ColumnName = column.ColumnName;
}
if (dtFKR == null)
{
var cmdFKR = new SqlCommand(FOREIGN_KEY_RELATIONSHIPS);
dtFKR = new DataTable();
dtFKR.Load(cmdFKR.GetReader());
var cmdFK = new SqlCommand(FOREIGN_KEYS);
dtFK = new DataTable();
dtFK.Load(cmdFK.GetReader());
}
DataRow[] drfkr;
drfkr = dtFKR.Select("PkTable ='" + tbl.Name + "'");
for (int i = 0; i < drfkr.Length; i++)
{
Relationship rel = new Relationship();
rel.Name = drfkr[i]["RelName"].ToString();
Table fktable = Db.Service.GetSchema(drfkr[i]["FkTable"].ToString());
rel.ClassNameOne = tbl.ClassName;
rel.ClassNameMany = fktable.ClassName;
string[] a = Regex.Split(rel.Name, "__");
if (a.Length == 2)
{
rel.PropertyNameMany = a[0]; // name used in primary table to fetch many of this table
rel.PropertyNameOne = a[1]; // named used for foreign key in this table
}
else
{
rel.PropertyNameOne = tbl.ClassName;
rel.PropertyNameMany = fktable.ClassNamePlural;
}
DataRow[] drfk = dtFK.Select("PkTable = '" + tbl.Name + "' and RelName = '" + rel.Name + "'");
for (int ii = 0; ii < drfk.Length; ii++)
{
Relationship.KeyPair kp = new Relationship.KeyPair();
TableColumn fkcol = fktable.Columns.GetColumn(drfk[ii]["FkColumn"].ToString());
TableColumn pkcol = columns.GetColumn(drfk[ii]["PkColumn"].ToString());
kp.vartype = fkcol.VarType;
kp.ForeignKey = fkcol.Name;
kp.PrimaryKey = pkcol.Name;
rel.KeyPairs.Add(kp);
if (ii > 0)
{
rel.ForeignKey += ",";
rel.PrimaryKey += ",";
}
rel.PrimaryKey += kp.PrimaryKey;
rel.ForeignKey += kp.ForeignKey;
}
tbl.ForeignKeyTables.Add(rel);
}
drfkr = dtFKR.Select("FkTable ='" + tbl.Name + "'");
for (int i = 0; i < drfkr.Length; i++)
{
Relationship rel = new Relationship();
rel.Name = drfkr[i]["RelName"].ToString();
Table pktable = Db.Service.GetSchema(drfkr[i]["PkTable"].ToString());
rel.ClassNameOne = pktable.ClassName;
rel.ClassNameMany = tbl.ClassName;
string[] a = Regex.Split(rel.Name, "__");
if (a.Length == 2)
{
rel.PropertyNameMany = a[0]; // name used in primary table to fetch many of this table
rel.PropertyNameOne = a[1]; // named used for foreign key in this table
}
else
{
rel.PropertyNameOne = pktable.ClassName;
rel.PropertyNameMany = tbl.ClassNamePlural;
}
DataRow[] drfk = dtFK.Select("FkTable = '" + tbl.Name + "' and RelName = '" + rel.Name + "'");
for (int ii = 0; ii < drfk.Length; ii++)
{
Relationship.KeyPair kp = new Relationship.KeyPair();
TableColumn fkcol = columns.GetColumn(drfk[ii]["FkColumn"].ToString());
TableColumn pkcol = pktable.Columns.GetColumn(drfk[ii]["PkColumn"].ToString());
kp.vartype = fkcol.VarType;
kp.ForeignKey = fkcol.Name;
kp.PrimaryKey = pkcol.Name;
rel.KeyPairs.Add(kp);
if (ii > 0)
{
rel.ForeignKey += ",";
rel.PrimaryKey += ",";
}
rel.PrimaryKey += kp.PrimaryKey;
rel.ForeignKey += kp.ForeignKey;
fkcol.PropertyNameOne = rel.PropertyNameOne;
}
tbl.ForeignKeys.Add(rel);
}
if (dsManyToManyCheck.Tables[Name] == null)
{
var cmdM2M = new SqlCommand(MANY_TO_MANY_CHECK_ALL);
var dt = new DataTable(Name);
dt.Load(cmdM2M.GetReader());
dsManyToManyCheck.Tables.Add(dt);
}
DataRow[] drs = dsManyToManyCheck.Tables[Name].Select("PK_Table = '" + tbl.Name + "'");
if (drs.Length > 0)
{
for (int count = 0; count < drs.Length; count++)
{
string mapTable = drs[count]["FK_Table"].ToString();
string localKey = drs[count]["FK_Column"].ToString();
if (dsManyToManyMap.Tables[Name] == null)
{
var cmdM2MMap = new SqlCommand(MANY_TO_MANY_FOREIGN_MAP_ALL);
DataTable dt = new DataTable(Name);
dt.Load(cmdM2MMap.GetReader());
dsManyToManyMap.Tables.Add(dt);
}
DataRow[] drMap = dsManyToManyMap.Tables[Name].Select("FK_Table = '" + mapTable + "' AND PK_Table <> '" + tbl.Name + "'");
for (int i = 0; i < drMap.Length; i++)
{
ManyToManyRelationship m = new ManyToManyRelationship(mapTable);
m.ForeignTableName = drMap[i]["PK_Table"].ToString();
m.ForeignPrimaryKey = drMap[i]["PK_Column"].ToString();
m.MapTableLocalTableKeyColumn = localKey;
m.MapTableForeignTableKeyColumn = drMap[i]["FK_Column"].ToString();
tbl.ManyToManys.Add(m);
}
}
}
}
public void FillTableSchema(Table tbl, TableType tableType)
{
TableColumnCollection columns = new TableColumnCollection();
DataSet dsColumns;
tbl.ForeignKeys = new ForeignKeyCollection();
if (tableType == TableType.Function)
{
if (dsColumns2.Tables[Name] == null)
{
var cmdColumns = new SqlCommand(ROUTINE_COLUMN_SQL_ALL);
var dt = new DataTable(Name);
dt.Load(cmdColumns.GetReader());
dsColumns2.Tables.Add(dt);
}
dsColumns = dsColumns2;
}
else
{
if (dsColumns1.Tables[Name] == null)
{
var cmdColumns = new SqlCommand(TABLE_COLUMN_SQL_ALL);
var dt = new DataTable(Name);
dt.Load(cmdColumns.GetReader());
dsColumns1.Tables.Add(dt);
}
dsColumns = dsColumns1;
}
DataRow[] drColumns = dsColumns.Tables[Name].Select("TableName ='" + tbl.Name + "'", "OrdinalPosition ASC");
for (int i = 0; i < drColumns.Length; i++)
{
TableColumn column = new TableColumn(tbl);
column.ColumnName = drColumns[i][SqlSchemaVariable.COLUMN_NAME].ToString();
column.NativeDataType = drColumns[i][SqlSchemaVariable.DATA_TYPE].ToString();
if (column.NativeDataType == "numeric")
column.NativeDataType = string.Format("Decimal({0},{1})", drColumns[i]["precision"], drColumns[i]["scale"]);
column.DataType = GetDbType(column.NativeDataType);
if (drColumns[i][SqlSchemaVariable.COLUMN_DEFAULT] != DBNull.Value)
{
string defaultSetting = drColumns[i][SqlSchemaVariable.COLUMN_DEFAULT].ToString().Trim();
if (defaultSetting.ToLower().IndexOf("newsequentialid()") > -1)
column.DefaultSetting = SqlSchemaVariable.DEFAULT;
else
column.DefaultSetting = defaultSetting;
}
column.AutoIncrement = Convert.ToBoolean(drColumns[i][SqlSchemaVariable.IS_IDENTITY]);
int maxLength;
int.TryParse(drColumns[i][SqlSchemaVariable.MAX_LENGTH].ToString(), out maxLength);
column.MaxLength = maxLength;
if (maxLength > 0)
column.NativeDataType += "(" + maxLength + ")";
column.IsNullable = drColumns[i][SqlSchemaVariable.IS_NULLABLE].ToString() == "YES";
bool isComputed = (drColumns[i][SqlSchemaVariable.IS_COMPUTED].ToString() == "1");
column.IsReadOnly = (column.NativeDataType == "timestamp" || isComputed);
columns.Add(column);
tbl.SchemaName = drColumns[i]["Owner"].ToString();
}
tbl.Columns = columns;
if (dsIndex.Tables[Name] == null)
{
var cmdIndex = new SqlCommand(INDEX_SQL_ALL);
DataTable dt = new DataTable(Name);
dt.Load(cmdIndex.GetReader());
dsIndex.Tables.Add(dt);
}
DataRow[] drIndexes = dsIndex.Tables[Name].Select("TableName ='" + tbl.Name + "'");
for (int i = 0; i < drIndexes.Length; i++)
{
string colName = drIndexes[i][SqlSchemaVariable.COLUMN_NAME].ToString();
string constraintType = drIndexes[i][SqlSchemaVariable.CONSTRAINT_TYPE].ToString();
TableColumn column = columns.GetColumn(colName);
if (Utility.IsMatch(constraintType, SqlSchemaVariable.PRIMARY_KEY))
column.IsPrimaryKey = true;
else if (Utility.IsMatch(constraintType, SqlSchemaVariable.FOREIGN_KEY))
column.IsForeignKey = true;
//HACK: Allow second pass naming adjust based on whether a column is keyed
column.ColumnName = column.ColumnName;
}
if (dtFKR == null)
{
var cmdFKR = new SqlCommand(FOREIGN_KEY_RELATIONSHIPS);
dtFKR = new DataTable();
dtFKR.Load(cmdFKR.GetReader());
var cmdFK = new SqlCommand(FOREIGN_KEYS);
dtFK = new DataTable();
dtFK.Load(cmdFK.GetReader());
}
DataRow[] drfkr;
drfkr = dtFKR.Select("PkTable ='" + tbl.Name + "'");
for (int i = 0; i < drfkr.Length; i++)
{
Relationship rel = new Relationship();
rel.Name = drfkr[i]["RelName"].ToString();
Table fktable = Db.Service.GetSchema(drfkr[i]["FkTable"].ToString());
rel.ClassNameOne = tbl.ClassName;
rel.ClassNameMany = fktable.ClassName;
string[] a = Regex.Split(rel.Name, "__");
if (a.Length == 2)
{
rel.PropertyNameMany = a[0]; // name used in primary table to fetch many of this table
rel.PropertyNameOne = a[1]; // named used for foreign key in this table
}
else
{
rel.PropertyNameOne = tbl.ClassName;
rel.PropertyNameMany = fktable.ClassNamePlural;
}
DataRow[] drfk = dtFK.Select("PkTable = '" + tbl.Name + "' and RelName = '" + rel.Name + "'");
for (int ii = 0; ii < drfk.Length; ii++)
{
Relationship.KeyPair kp = new Relationship.KeyPair();
TableColumn fkcol = fktable.Columns.GetColumn(drfk[ii]["FkColumn"].ToString());
TableColumn pkcol = columns.GetColumn(drfk[ii]["PkColumn"].ToString());
kp.vartype = fkcol.VarType;
kp.ForeignKey = fkcol.Name;
kp.PrimaryKey = pkcol.Name;
rel.KeyPairs.Add(kp);
if (ii > 0)
{
rel.ForeignKey += ",";
rel.PrimaryKey += ",";
}
rel.PrimaryKey += kp.PrimaryKey;
rel.ForeignKey += kp.ForeignKey;
}
tbl.ForeignKeyTables.Add(rel);
}
drfkr = dtFKR.Select("FkTable ='" + tbl.Name + "'");
for (int i = 0; i < drfkr.Length; i++)
{
Relationship rel = new Relationship();
rel.Name = drfkr[i]["RelName"].ToString();
Table pktable = Db.Service.GetSchema(drfkr[i]["PkTable"].ToString());
rel.ClassNameOne = pktable.ClassName;
rel.ClassNameMany = tbl.ClassName;
string[] a = Regex.Split(rel.Name, "__");
if (a.Length == 2)
{
rel.PropertyNameMany = a[0]; // name used in primary table to fetch many of this table
rel.PropertyNameOne = a[1]; // named used for foreign key in this table
}
else
{
rel.PropertyNameOne = pktable.ClassName;
rel.PropertyNameMany = tbl.ClassNamePlural;
}
DataRow[] drfk = dtFK.Select("FkTable = '" + tbl.Name + "' and RelName = '" + rel.Name + "'");
for (int ii = 0; ii < drfk.Length; ii++)
{
Relationship.KeyPair kp = new Relationship.KeyPair();
TableColumn fkcol = columns.GetColumn(drfk[ii]["FkColumn"].ToString());
TableColumn pkcol = pktable.Columns.GetColumn(drfk[ii]["PkColumn"].ToString());
kp.vartype = fkcol.VarType;
kp.ForeignKey = fkcol.Name;
kp.PrimaryKey = pkcol.Name;
rel.KeyPairs.Add(kp);
if (ii > 0)
{
rel.ForeignKey += ",";
rel.PrimaryKey += ",";
}
rel.PrimaryKey += kp.PrimaryKey;
rel.ForeignKey += kp.ForeignKey;
fkcol.PropertyNameOne = rel.PropertyNameOne;
}
tbl.ForeignKeys.Add(rel);
}
if (dsManyToManyCheck.Tables[Name] == null)
{
var cmdM2M = new SqlCommand(MANY_TO_MANY_CHECK_ALL);
var dt = new DataTable(Name);
dt.Load(cmdM2M.GetReader());
dsManyToManyCheck.Tables.Add(dt);
}
DataRow[] drs = dsManyToManyCheck.Tables[Name].Select("PK_Table = '" + tbl.Name + "'");
if (drs.Length > 0)
{
for (int count = 0; count < drs.Length; count++)
{
string mapTable = drs[count]["FK_Table"].ToString();
string localKey = drs[count]["FK_Column"].ToString();
if (dsManyToManyMap.Tables[Name] == null)
{
var cmdM2MMap = new SqlCommand(MANY_TO_MANY_FOREIGN_MAP_ALL);
DataTable dt = new DataTable(Name);
dt.Load(cmdM2MMap.GetReader());
dsManyToManyMap.Tables.Add(dt);
}
DataRow[] drMap = dsManyToManyMap.Tables[Name].Select("FK_Table = '" + mapTable + "' AND PK_Table <> '" + tbl.Name + "'");
for (int i = 0; i < drMap.Length; i++)
{
ManyToManyRelationship m = new ManyToManyRelationship(mapTable);
m.ForeignTableName = drMap[i]["PK_Table"].ToString();
m.ForeignPrimaryKey = drMap[i]["PK_Column"].ToString();
m.MapTableLocalTableKeyColumn = localKey;
m.MapTableForeignTableKeyColumn = drMap[i]["FK_Column"].ToString();
tbl.ManyToManys.Add(m);
}
}
}
}
