/// <summary>
/// Creates a new database on the server
/// </summary>
/// <param name="DatabaseName">The name of the database</param>
/// <returns>The newly created database</returns>
public Database CreateDatabase(string DatabaseName)
{
Microsoft.SqlServer.Management.Smo.Database db;
db = new Microsoft.SqlServer.Management.Smo.Database(_server, DatabaseName);
db.Create();
return new Database(db);
}
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);
}
internal string WriteApiStation(GenerationInfo GenInfo, Microsoft.SqlServer.Management.Smo.Database datab, Microsoft.SqlServer.Management.Smo.Table t, string RepositoryPath)
{
string module_folder_name = String.Format(@"{0}\Modules", RepositoryPath);
string file_path = string.Format(@"{0}\{1}Module.cs", module_folder_name, t.Name);
#region [ Build Module Folder Name ]
if (Directory.Exists(module_folder_name) == false)
{
Directory.CreateDirectory(module_folder_name);
}
#endregion
return("");
}
public static List <EntityProperty> ListColumnsOfTable(string dbName, string tableName)
{
var result = new List <EntityProperty>();
var server = new Microsoft.SqlServer.Management.Smo.Server(serverName);
var db = new Microsoft.SqlServer.Management.Smo.Database(server, dbName);
var table = new Microsoft.SqlServer.Management.Smo.Table(db, tableName);
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);
}
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 EntityProperty()
{
PropertyType = propertyType,
PropertyName = item.Name,
IsForeignKey = item.IsForeignKey,
IsIdentity = item.Identity
};
if (item.IsForeignKey == true)
{
entityProperty.ForeignKeyTableName = UpperFirstLetter(dic[item.Name]);
}
result.Add(entityProperty);
}
return(result);
}
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);
}
/// <summary>
/// Enable File Streaming on the MediaDbContext
/// </summary>
public static void Enable( )
{
Log.Info("Enabling Sql File Streaming");
// TODO: Configurable?
const string fileGroupName = "fg";
const string dataFileName = "file";
// Get the Connection String to the Media DbContext
var mediaDbConnectionString = new SqlConnectionStringBuilder(ContextConnectionStringManager.Get(ContextNames.Media));
// Get the server name from the connection string.
var mediaDbDatasource = mediaDbConnectionString.DataSource;
// Get the catalog name of the media database.
var mediaDbDatabaseName = mediaDbConnectionString.InitialCatalog;
// Connect with SMO.
var server = new Microsoft.SqlServer.Management.Smo.Server(mediaDbDatasource);
server.ConnectionContext.AutoDisconnectMode = Microsoft.SqlServer.Management.Common.AutoDisconnectMode.NoAutoDisconnect;
// Determine the Filestream directory.
var serverName = server.ComputerNamePhysicalNetBIOS;
const string filestreamPathLocal = @"C:\TriTech\InformRMS\Data\FileStream"; // TODO: Derive from input options.
var isLocalDeploy = IsHostLocal(serverName);
var filestreamPathUnc = isLocalDeploy ? "" : String.Format(@"\\{0}\{1}", serverName, filestreamPathLocal.Replace(':', '$'));
// Create the Filestream directory, if necessary.
var filestreamDirectory = isLocalDeploy
? Path.GetDirectoryName(filestreamPathLocal)
: Path.GetDirectoryName(filestreamPathUnc);
if (!Directory.Exists(filestreamDirectory))
{
Directory.CreateDirectory(filestreamDirectory);
}
// Update the Filestream access level, if necessary. This is required before Filestream is enabled on a database.
const int target = (int)Microsoft.SqlServer.Management.Smo.FilestreamAccessLevelType.FullAccess;
if (server.Configuration.FilestreamAccessLevel.ConfigValue != target)
{
Log.Info("Changing Filestream access level to: {0}", target);
server.Configuration.FilestreamAccessLevel.ConfigValue = target;
server.Configuration.Alter();
}
// Manually create the database from the context.
// We need to do this because Filestream has to be enabled on the database before we can create Filestream columns.
var database = server.Databases
.Cast <Microsoft.SqlServer.Management.Smo.Database>()
.FirstOrDefault(x => x.Name.Equals(mediaDbDatabaseName, StringComparison.OrdinalIgnoreCase));
if (database == null)
{
Log.Info("Creating database {0}.", mediaDbDatabaseName);
database = new Microsoft.SqlServer.Management.Smo.Database(server, mediaDbDatabaseName);
database.Create();
}
// Create a FileGroup for Filestream data, if necessary.
if (String.IsNullOrWhiteSpace(database.FilestreamDirectoryName))
{
var fg = new Microsoft.SqlServer.Management.Smo.FileGroup(database, fileGroupName, true);
fg.Create();
var file = new Microsoft.SqlServer.Management.Smo.DataFile(fg, dataFileName, filestreamPathLocal);
file.Create();
}
// Disconnect.
server.ConnectionContext.Disconnect();
// Attempt to determine if FileStreaming is already setup
var result = SqlScripting.ExecuteNonQuery(mediaDbConnectionString.ToString(), @"
SELECT OBJECT_NAME(object_id) AS TableName, name AS ColumnName
FROM sys.columns
WHERE is_filestream = 1 AND OBJECT_NAME(object_id) = 'MEDIASTORAGES' AND name = 'MEDIA'");
// The Drop Column will fail if the table is already setup with File Streaming.
// Make sure we only execute the script if the FileStream is not already setup.
if (result < 1)
{
// Enable Filestream on the MediaStorages table.
SqlScripting.ExecuteScript(mediaDbConnectionString.ToString(), @"
-- Filestream-enabled tables must have a RowGuidCol column. This cannot be added through fluent mapping.
alter table [dbo].[MediaStorages] drop column [MediaGuid]
alter table [dbo].[MediaStorages] add [MediaGuid] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE DEFAULT NEWID()
-- Manually create the filestream column. NOTE: SQL will not allow the FILESTREAM attribute to be added to an existing column, and the column must be created as part of the model (so it can be accessed via code), so drop and recreate it here.
alter table [dbo].[MediaStorages] drop column [Media]
alter table [dbo].[MediaStorages] add [Media] varbinary(max) FILESTREAM null");
}
}
internal DatabaseScriptRunner(Microsoft.SqlServer.Management.Smo.Database Database)
{
_database = Database;
}
