private static void GeneratePrimaryKeyConstraint(IndentedStringBuilder sqlBuilder, DbPrimaryKey constraint)
{
Debug.Assert(constraint != null);
sqlBuilder.Append("PRIMARY KEY ");
GenerateColumnSortList(sqlBuilder, constraint.IsClustered, constraint.PrimaryKey);
sqlBuilder.Append(")");
}
private static void GeneratePrimaryKeyConstraint(IndentedStringBuilder sqlBuilder, DbPrimaryKey constraint)
{
Debug.Assert(constraint != null);
if (constraint.IsClustered)
{
GeneratePrimaryKeyConstraint(sqlBuilder, constraint.PrimaryKey);
}
else
{
GenerateUniqueConstraint(sqlBuilder, constraint.PrimaryKey);
}
}
private Dictionary <string, DbTable> GetTables(DatabaseConnectionInfo connectionInfo,
SqlCommand getTablesCommand,
SqlCommand getColumnsCommand,
SqlCommand getPrimaryKeysCommand,
SqlCommand getPrimaryKeyColumnsCommand
)
{
var tables = new Dictionary <string, DbTable>();
using (SqlConnection conn = new SqlConnection(connectionInfo.ConnectionString))
{
conn.Open();
//Get tables
getTablesCommand.Connection = conn;
using (var reader = getTablesCommand.ExecuteReader())
{
var fullNamePos = reader.GetOrdinal("fullName");
var namePos = reader.GetOrdinal("name");
var schemaNamePos = reader.GetOrdinal("schemaName");
while (reader.Read())
{
tables.Add(reader.GetString(fullNamePos), new DbTable
{
Name = reader.GetString(namePos),
SchemaName = reader.GetString(schemaNamePos)
});
}
}
//Get Columns
getColumnsCommand.Connection = conn;
using (var reader = getColumnsCommand.ExecuteReader())
{
var fullTableNamePos = reader.GetOrdinal("fullTableName");
var columnNamePos = reader.GetOrdinal("columnName");
var is_nullablePos = reader.GetOrdinal("is_nullable");
var is_identityPos = reader.GetOrdinal("is_identity");
var seedPos = reader.GetOrdinal("Seed");
var stepPos = reader.GetOrdinal("Step");
var datatypePos = reader.GetOrdinal("datatype");
var max_lengthPos = reader.GetOrdinal("max_length");
var ordinalPos = reader.GetOrdinal("ordinal");
var precisionPos = reader.GetOrdinal("precision");
var scalePos = reader.GetOrdinal("scale");
while (reader.Read())
{
var fullTableName = reader.GetString(fullTableNamePos);
var table = tables[fullTableName];
var refname = reader.GetString(columnNamePos);
try
{
var col = new DbColumn();
col.Name = reader.GetString(columnNamePos);
col.IsNullable = reader.GetBoolean(is_nullablePos);
col.IsIdentity = reader.GetBoolean(is_identityPos);
col.DataType = (reader.GetValue(datatypePos) != DBNull.Value) ? reader.GetString(datatypePos) : "";
col.IdentitySeed = Convert.ToInt64(reader.GetValue(seedPos));
col.IdentityStep = Convert.ToInt64(reader.GetValue(stepPos));
col.MaxLength = (Int16)reader.GetValue(max_lengthPos);
col.Ordinal = reader.GetInt32(ordinalPos);
col.Precision = reader.GetByte(precisionPos);
col.Scale = reader.GetByte(scalePos);
table.Columns.Add(col);
}
catch (Exception ex)
{
Debug.WriteLine($"Failed to prosses in Table {fullTableName} , column {refname}");
}
}
}
//Get primary keys
getPrimaryKeysCommand.Connection = conn;
using (var reader = getPrimaryKeysCommand.ExecuteReader())
{
while (reader.Read())
{
var fullTablename = (string)reader["fullTableName"];
var table = tables[fullTablename];
var pk = new DbPrimaryKey();
pk.TableSchema = (string)reader["schema_name"];
pk.Table = (string)reader["table_name"];
pk.Name = (string)reader["index_name"];
pk.Partition = (int)reader["partition_number"];
pk.FileGroup = (string)reader["filegroup_name"];
pk.RowCount = (long)reader["rows"];
pk.Type = (string)reader["type_desc"];
pk.IsPadded = (bool)reader["is_padded"];
pk.IsAllowingPageLocks = (bool)reader["allow_page_locks"];
pk.IsAllowingRowLocks = (bool)reader["allow_row_locks"];
pk.IsDisabled = (bool)reader["is_disabled"];
table.PrimaryKey = pk;
}
}
//get primary Key columns
getPrimaryKeyColumnsCommand.Connection = conn;
using (var reader = getPrimaryKeyColumnsCommand.ExecuteReader())
{
var columnNamePos = reader.GetOrdinal("columnName");
var tableFullName = reader.GetOrdinal("tableFullName");
while (reader.Read())
{
var fullTablename = reader.GetString(tableFullName);
var table = tables[fullTablename];
var columnName = reader.GetString(columnNamePos);
table.Columns.First(c => c.Name == columnName).IsInPrimaryKey = true;
}
}
foreach (var table in tables)
{
table.Value.Definition = table.Value.GenerateDefinition();
}
return(tables);
}
}
private DbSchemaObject ParseSchemaObject(FileInfo file)
{
DbSchemaObject schemaObject = null;
var name = System.IO.Path.GetFileNameWithoutExtension(file.FullName);
var nameparts = name.Split('_');
var objectType = (ESchemaObjectType)Enum.Parse(typeof(ESchemaObjectType), nameparts[0]);
switch (objectType)
{
case ESchemaObjectType.Table:
schemaObject = new DbTable();
break;
case ESchemaObjectType.View:
schemaObject = new DbView();
break;
case ESchemaObjectType.StoredProcedure:
schemaObject = new DbStoredProc();
break;
case ESchemaObjectType.ScalarFunction:
schemaObject = new DbScalarFunction();
break;
case ESchemaObjectType.TableFunction:
schemaObject = new DbTableFunction();
break;
case ESchemaObjectType.Trigger:
schemaObject = new DbTrigger();
break;
case ESchemaObjectType.PrimaryKey:
schemaObject = new DbPrimaryKey();
break;
case ESchemaObjectType.ForeignKey:
schemaObject = new DbForeignKey();
break;
default:
throw new ArgumentOutOfRangeException();
}
if (schemaObject == null)
{
throw new Exception("Unable to identify object");
}
var objectParts = nameparts[1].Split('.');
if (objectParts.Length != 2)
{
throw new Exception($"Problem with name {nameparts[1]}");
}
schemaObject.SchemaName = objectParts[0];
schemaObject.Name = objectParts[1];
schemaObject.Definition = file.ReadAllText();
return(schemaObject);
}
