public static List <string> ListTables(string dbName)
{
List <string> tables = new List <string>();
var server = new Microsoft.SqlServer.Management.Smo.Server(serverName);
var db = new Microsoft.SqlServer.Management.Smo.Database(server, dbName);
db.Refresh();
foreach (Microsoft.SqlServer.Management.Smo.Table item in db.Tables)
{
tables.Add(UpperFirstLetter(item.Name));
}
return(tables);
}
public static List <DbTable> FromDB(string dbName, string serverName, string user = null, SecureString pass = null)
{
var tables = new List <DbTable>();
var dicReferenceTable = new Dictionary <string, List <Reference> >();
var serverConnection = new Microsoft.SqlServer.Management.Common.ServerConnection(serverName);
if (string.IsNullOrEmpty(user) == false)
{
serverConnection = new Microsoft.SqlServer.Management.Common.ServerConnection(serverName, user, pass);
}
var server = new Microsoft.SqlServer.Management.Smo.Server(serverConnection);
var db = new Microsoft.SqlServer.Management.Smo.Database(server, dbName);
db.Refresh();
foreach (Microsoft.SqlServer.Management.Smo.Table table in db.Tables)
{
table.Refresh();
var dic = new Dictionary <string, string>();
foreach (Microsoft.SqlServer.Management.Smo.ForeignKey item in table.ForeignKeys)
{
dic.Add(item.Columns[0].Name, item.ReferencedTable);
if (dicReferenceTable.ContainsKey(item.ReferencedTable) == false)
{
dicReferenceTable.Add(item.ReferencedTable, new List <Reference>());
}
dicReferenceTable[item.ReferencedTable].Add(new Reference()
{
PropertyName = item.Columns[0].Name,
ReferenceTableName = table.Name
});
}
var indexes = GetIndexes(table);
var foreignKeys = GetForeignKey(table);
var columns = new List <DbTableColumn>();
var requiredMaxLengths = new List <RequiredMaxLength>();
var defaultValues = new List <DefaultValue>();
var hasColumnTypes = new List <HasColumnType>();
foreach (Microsoft.SqlServer.Management.Smo.Column item in table.Columns)
{
var sqlDataType = item.DataType.Name;
var dotnetType = _typeMapping[sqlDataType];
if (item.Nullable == true && dotnetType != "string" && dotnetType != "byte[]")
{
dotnetType = dotnetType + "?";
}
var entityProperty = new DbTableColumn()
{
DataType = dotnetType,
ColumnName = item.Name,
IsForeignKey = item.IsForeignKey,
IsPrimaryKey = item.InPrimaryKey,
IsIdentity = item.Identity
};
if (item.IsForeignKey == true)
{
entityProperty.ForeignKeyTableName = dic[item.Name];
}
columns.Add(entityProperty);
//hascolumntype
if (sqlDataType == "decimal" ||
sqlDataType == "numeric" ||
sqlDataType == "datetime2" ||
sqlDataType == "datetimeoffset" ||
sqlDataType == "time")
{
hasColumnTypes.Add(new HasColumnType()
{
PropertyName = item.Name,
TypeName = sqlDataType,
NumericPrecision = item.DataType.NumericPrecision,
NumericScale = item.DataType.NumericScale
});
}
//requiredmaxlength
var requiredMaxLength = new RequiredMaxLength()
{
PropertyName = item.Name, MaxLength = -1
};
if (item.Nullable == false && dotnetType == "string")
{
requiredMaxLength.NeedIsRequired = true;
}
if (dotnetType == "string" || dotnetType == "byte[]")
{
requiredMaxLength.MaxLength = item.DataType.MaximumLength;
}
if (requiredMaxLength.NeedIsRequired == true || requiredMaxLength.MaxLength > 0)
{
requiredMaxLengths.Add(requiredMaxLength);
}
//defaultvalue
if (item.DefaultConstraint != null)
{
defaultValues.Add(new DefaultValue()
{
PropertyName = item.Name,
Value = item.DefaultConstraint.Text
});
}
}
var t = new DbTable()
{
TableName = table.Name,
Columns = new ObservableCollection <DbTableColumn>(columns),
ForeignKeys = new ObservableCollection <ForeignKey>(foreignKeys),
Indexes = new ObservableCollection <Index>(indexes),
RequiredMaxLengths = new ObservableCollection <RequiredMaxLength>(requiredMaxLengths),
DefaultValues = new ObservableCollection <DefaultValue>(defaultValues),
HasColumnTypes = new ObservableCollection <HasColumnType>(hasColumnTypes)
};
if (table.Name.StartsWith("Smt"))
{
t.IsSelected = false;
}
else
{
t.IsSelected = true;
}
tables.Add(t);
}
foreach (var table in tables)
{
List <Reference> reference;
if (dicReferenceTable.TryGetValue(table.TableName, out reference) == true)
{
table.ReferencesToThisTable = new ObservableCollection <Reference>(reference);
}
else
{
table.ReferencesToThisTable = new ObservableCollection <Reference>();
}
}
CalculateReferenceLevel(tables);
return(tables);
}
public static List <DbTable> FromDB(string dbName)
{
var tables = new List <DbTable>();
var dicReferenceTable = new Dictionary <string, List <Reference> >();
var server = new Microsoft.SqlServer.Management.Smo.Server(serverName);
var db = new Microsoft.SqlServer.Management.Smo.Database(server, dbName);
db.Refresh();
foreach (Microsoft.SqlServer.Management.Smo.Table table in db.Tables)
{
table.Refresh();
var dic = new Dictionary <string, string>();
foreach (Microsoft.SqlServer.Management.Smo.ForeignKey item in table.ForeignKeys)
{
dic.Add(item.Columns[0].Name, item.ReferencedTable);
if (dicReferenceTable.ContainsKey(item.ReferencedTable) == false)
{
dicReferenceTable.Add(item.ReferencedTable, new List <Reference>());
}
dicReferenceTable[item.ReferencedTable].Add(new Reference()
{
PropertyName = table.Name + item.Columns[0].Name,
ReferenceTableName = table.Name
});
}
var indexes = GetIndexes(table);
var foreignKeys = GetForeignKey(table);
var columns = new List <DbTableColumn>();
var requiredMaxLengths = new List <RequiredMaxLength>();
var defaultValues = new List <DefaultValue>();
var hasColumnTypes = new List <HasColumnType>();
foreach (Microsoft.SqlServer.Management.Smo.Column item in table.Columns)
{
var propertyType = _typeMapping[item.DataType.Name];
if (item.Nullable == true && propertyType != "string")
{
propertyType = propertyType + "?";
}
var entityProperty = new DbTableColumn()
{
DataType = propertyType,
ColumnName = item.Name,
IsForeignKey = item.IsForeignKey,
IsPrimaryKey = item.InPrimaryKey,
IsIdentity = item.Identity
};
if (item.IsForeignKey == true)
{
entityProperty.ForeignKeyTableName = dic[item.Name];
}
columns.Add(entityProperty);
//hascolumntype
if (entityProperty.DataType == "System.DateTime" ||
entityProperty.DataType == "System.TimeSpan")
{
hasColumnTypes.Add(new HasColumnType()
{
PropertyName = item.Name,
TypeName = item.DataType.Name + "(" + item.DataType.NumericScale + ")"
});
}
else if (item.DataType.Name == "decimal" || item.DataType.Name == "numeric")
{
hasColumnTypes.Add(new HasColumnType()
{
PropertyName = item.Name,
TypeName = item.DataType.Name + "(" + item.DataType.NumericPrecision + "," + item.DataType.NumericScale + ")"
});
}
//requiredmaxlength
var requiredMaxLength = new RequiredMaxLength()
{
PropertyName = item.Name, MaxLength = -1
};
if (item.Nullable == false && propertyType == "string")
{
requiredMaxLength.NeedIsRequired = true;
}
if (propertyType == "string" || propertyType == "byte[]")
{
requiredMaxLength.MaxLength = item.DataType.MaximumLength;
}
if (requiredMaxLength.NeedIsRequired == true || requiredMaxLength.MaxLength > 0)
{
requiredMaxLengths.Add(requiredMaxLength);
}
//defaultvalue
if (item.DefaultConstraint != null)
{
defaultValues.Add(new DefaultValue()
{
PropertyName = item.Name,
Value = item.DefaultConstraint.Text
});
}
}
tables.Add(new DbTable()
{
TableName = table.Name,
Columns = new ObservableCollection <DbTableColumn>(columns),
ForeignKeys = new ObservableCollection <ForeignKey>(foreignKeys),
Indexes = new ObservableCollection <Index>(indexes),
RequiredMaxLengths = new ObservableCollection <RequiredMaxLength>(requiredMaxLengths),
DefaultValues = new ObservableCollection <DefaultValue>(defaultValues),
HasColumnTypes = new ObservableCollection <HasColumnType>(hasColumnTypes)
});
}
foreach (var table in tables)
{
List <Reference> reference;
if (dicReferenceTable.TryGetValue(table.TableName, out reference) == true)
{
table.ReferencesToThisTable = new ObservableCollection <Reference>(reference);
}
}
return(tables);
}
