private static TableSchema GetTable(SqlConnection con, DataRow tRow)
{
var eTable = new TableSchema {
Name = tRow["TABLE_NAME"].ToString()
};
// Columns
string[] restrict3 = { null, null, null };
restrict3[0] = null;
restrict3[1] = null;
restrict3[2] = eTable.Name;
var dtShema = SqlSchemaFactory.GetSchema(con, "Columns", restrict3);
if (dtShema.Rows.Count > 0)
{
eTable.Columns = new TableColumnSchema[dtShema.Rows.Count];
for (var j = 0; j < dtShema.Rows.Count; j++)
{
var cRow = dtShema.Rows[j];
var eColumn = new TableColumnSchema
{
Name = cRow["COLUMN_NAME"].ToString(),
Type = ColumnSchemaToSqlDataType(cRow),
Nullable = Convert.ToBoolean(cRow["IS_NULLABLE"], CultureInfo.InvariantCulture),
DefaultValue = cRow["COLUMN_DEFAULT"].ToString(),
Increment = Convert.ToInt32(cRow["IDENTITY_INCREMENT"], CultureInfo.InvariantCulture),
Seed = Convert.ToInt32(cRow["IDENTITY_SEED"], CultureInfo.InvariantCulture),
AutoIncrement = Convert.ToBoolean(cRow["IS_IDENTITY"], CultureInfo.InvariantCulture),
};
eColumn.DefaultValue = string.IsNullOrEmpty(eColumn.DefaultValue)
? null
: UnBracket.ParseUnBracket(eColumn.DefaultValue);
eTable.Columns[j] = eColumn;
}
}
return(eTable);
}
private static List <KeySchema> GetKeys(SqlConnection con, SchemaNamedElement eTable)
{
var keys = new List <KeySchema>();
var aHash = new Dictionary <string, bool>();
string[] restrict3 = { null, null, null };
string[] restrict4 = { null, null, null, null };
#region Primary keys
restrict3[0] = null;
restrict3[1] = null;
restrict3[2] = eTable.Name;
var dtShema = SqlSchemaFactory.GetSchema(con, "PrimaryKeys", restrict3);
aHash.Clear();
for (var x = 0; x < dtShema.Rows.Count; x++)
{
var cRow = dtShema.Rows[x];
var cName = cRow["CONSTRAINT_NAME"].ToString();
if (aHash.ContainsKey(cName))
{
continue;
}
aHash.Add(cName, true);
var eKey = new KeySchema
{
KeyType = ConstraintType.KeyPrimary,
Name = cName,
Clustered = Convert.ToBoolean(cRow["IS_CLUSTERED"], CultureInfo.InvariantCulture)
};
var columns = new StringBuilder();
var dtv = dtShema.DefaultView;
dtv.RowFilter = string.Format("CONSTRAINT_NAME = '{0}'", cName);
dtv.Sort = "ORDINAL_POSITION ASC";
for (var y = 0; y < dtv.Count; y++)
{
columns.Append(dtv[y]["COLUMN_NAME"]);
columns.Append(y == dtv.Count - 1 ? string.Empty : ", ");
}
eKey.Columns = columns.ToString();
keys.Add(eKey);
}
#endregion
#region Foreign keys
restrict4[0] = null;
restrict4[1] = null;
restrict4[2] = eTable.Name;
restrict4[3] = null;
dtShema = SqlSchemaFactory.GetSchema(con, "ForeignKeys", restrict4);
aHash.Clear();
for (var x = 0; x < dtShema.Rows.Count; x++)
{
var cRow = dtShema.Rows[x];
if (Convert.ToBoolean(cRow["IS_DISABLED"], CultureInfo.InvariantCulture))
{
continue;
}
var cName = cRow["CONSTRAINT_NAME"].ToString();
if (aHash.ContainsKey(cName))
{
continue;
}
aHash.Add(cName, true);
var eKey = new KeySchema
{
KeyType = ConstraintType.KeyForeign,
Name = cName,
RelTable = cRow["PK_TABLE_NAME"].ToString(),
UpdateRule = GetDbsmRule(cRow["UPDATE_RULE"].ToString()),
DeleteRule = GetDbsmRule(cRow["DELETE_RULE"].ToString())
};
var fcolumns = new StringBuilder();
var rcolumns = new StringBuilder();
var dtv = dtShema.DefaultView;
dtv.RowFilter = string.Format("CONSTRAINT_NAME = '{0}'", cName);
dtv.Sort = "ORDINAL_POSITION ASC";
for (var y = 0; y < dtv.Count; y++)
{
fcolumns.Append(dtv[y]["FK_COLUMN_NAME"]);
fcolumns.Append(y == dtv.Count - 1 ? string.Empty : ", ");
rcolumns.Append(dtv[y]["PK_COLUMN_NAME"]);
rcolumns.Append(y == dtv.Count - 1 ? string.Empty : ", ");
}
eKey.Columns = fcolumns.ToString();
eKey.RelColumns = rcolumns.ToString();
keys.Add(eKey);
}
#endregion
#region Checks
restrict3[0] = null;
restrict3[1] = null;
restrict3[2] = eTable.Name;
dtShema = SqlSchemaFactory.GetSchema(con, "CheckConstraints", restrict3);
for (var x = 0; x < dtShema.Rows.Count; x++)
{
var row = dtShema.Rows[x];
var eKey = new KeySchema
{
KeyType = ConstraintType.Check,
Name = row["CONSTRAINT_NAME"].ToString(),
Source = row["SOURCE"].ToString()
};
keys.Add(eKey);
}
#endregion
#region Unique
restrict3[0] = null;
restrict3[1] = null;
restrict3[2] = eTable.Name;
dtShema = SqlSchemaFactory.GetSchema(con, "UniqueKeys", restrict3);
aHash.Clear();
for (var x = 0; x < dtShema.Rows.Count; x++)
{
var cRow = dtShema.Rows[x];
var cName = cRow["CONSTRAINT_NAME"].ToString();
if (aHash.ContainsKey(cName))
{
continue;
}
var eKey = new KeySchema();
aHash.Add(cName, true);
eKey.KeyType = ConstraintType.Unique;
eKey.Name = cName;
var columns = new StringBuilder();
var dtv = dtShema.DefaultView;
dtv.RowFilter = "CONSTRAINT_NAME = '" + cName + "'";
dtv.Sort = "ORDINAL_POSITION ASC";
for (var y = 0; y < dtv.Count; y++)
{
columns.Append(dtv[y]["COLUMN_NAME"]);
columns.Append(y == dtv.Count - 1 ? string.Empty : ", ");
}
eKey.Columns = columns.ToString();
keys.Add(eKey);
}
#endregion
#region Default constraints
restrict3[0] = null;
restrict3[1] = null;
restrict3[2] = eTable.Name;
dtShema = SqlSchemaFactory.GetSchema(con, "DefaultConstraints", restrict3);
for (var x = 0; x < dtShema.Rows.Count; x++)
{
var cRow = dtShema.Rows[x];
var eKey = new KeySchema
{
KeyType = ConstraintType.Default,
Name = cRow["CONSTRAINT_NAME"].ToString(),
Columns = cRow["COLUMN_NAME"].ToString(),
Source = UnBracket.ParseUnBracket(cRow["SOURCE"].ToString())
};
keys.Add(eKey);
}
#endregion
return(keys);
}
private static List <TableSchema> GetTables(SqlConnection con)
{
var tables = new List <TableSchema>();
string[] restrict4 = { null, null, null, "TABLE" };
string[] restrict3 = { null, null, null };
var dtTables = SqlSchemaFactory.GetSchema(con, "Tables", restrict4);
for (var i = 0; i < dtTables.Rows.Count; i++)
{
var tRow = dtTables.Rows[i];
var eTable = new TableSchema {
Name = tRow["TABLE_NAME"].ToString()
};
// Columns
restrict3[0] = null;
restrict3[1] = null;
restrict3[2] = eTable.Name;
var dtShema = SqlSchemaFactory.GetSchema(con, "Columns", restrict3);
if (dtShema.Rows.Count > 0)
{
eTable.Columns = new TableColumnSchema[dtShema.Rows.Count];
for (var j = 0; j < dtShema.Rows.Count; j++)
{
var cRow = dtShema.Rows[j];
var eColumn = new TableColumnSchema
{
Name = cRow["COLUMN_NAME"].ToString(),
Size = Convert.ToInt32(cRow["COLUMN_SIZE"], CultureInfo.InvariantCulture),
Type = TypeSqlToDbsm(cRow["COLUMN_DATA_TYPE"].ToString()),
Nullable = Convert.ToBoolean(cRow["IS_NULLABLE"], CultureInfo.InvariantCulture),
DefaultValue = cRow["COLUMN_DEFAULT"].ToString(),
Increment = Convert.ToInt32(cRow["IDENTITY_INCREMENT"], CultureInfo.InvariantCulture),
Seed = Convert.ToInt32(cRow["IDENTITY_SEED"], CultureInfo.InvariantCulture),
AutoIncrement = Convert.ToBoolean(cRow["IS_IDENTITY"], CultureInfo.InvariantCulture),
DecimalPrecision = Convert.ToInt32(cRow["NUMERIC_PRECISION"], CultureInfo.InvariantCulture),
DecimalScale = Convert.ToInt32(cRow["NUMERIC_SCALE"], CultureInfo.InvariantCulture)
};
eColumn.DefaultValue = string.IsNullOrEmpty(eColumn.DefaultValue)
? null
: UnBracket.ParseUnBracket(eColumn.DefaultValue);
eTable.Columns[j] = eColumn;
}
}
tables.Add(eTable);
}
return(tables);
}