/// <summary>
/// Determines whether the database for the given connection exists.
/// There are three cases:
/// 1. Initial Catalog = X, AttachDBFilename = null: (SELECT Count(*) FROM sys.databases WHERE [name]= X) > 0
/// 2. Initial Catalog = X, AttachDBFilename = F: if (SELECT Count(*) FROM sys.databases WHERE [name]= X) > true,
/// if not, try to open the connection and then return (SELECT Count(*) FROM sys.databases WHERE [name]= X) > 0
/// 3. Initial Catalog = null, AttachDBFilename = F: Try to open the connection. If that succeeds the result is true, otherwise
/// if the there are no databases corresponding to the given file return false, otherwise throw.
///
/// Note: We open the connection to cover the scenario when the mdf exists, but is not attached.
/// Given that opening the connection would auto-attach it, it would not be appropriate to return false in this case.
/// Also note that checking for the existence of the file does not work for a remote server. (Dev11 #290487)
/// For further details on the behavior when AttachDBFilename is specified see Dev10# 188936
/// </summary>
protected override bool DbDatabaseExists(DbConnection connection, int?commandTimeout, StoreItemCollection storeItemCollection)
{
EntityUtil.CheckArgumentNull(connection, "connection");
EntityUtil.CheckArgumentNull(storeItemCollection, "storeItemCollection");
SqlConnection sqlConnection = SqlProviderUtilities.GetRequiredSqlConnection(connection);
var connectionBuilder = new SqlConnectionStringBuilder(sqlConnection.ConnectionString);
if (string.IsNullOrEmpty(connectionBuilder.InitialCatalog) && string.IsNullOrEmpty(connectionBuilder.AttachDBFilename))
{
throw EntityUtil.InvalidOperation(Strings.SqlProvider_DdlGeneration_MissingInitialCatalog);
}
if (!string.IsNullOrEmpty(connectionBuilder.InitialCatalog))
{
if (CheckDatabaseExists(sqlConnection, commandTimeout, connectionBuilder.InitialCatalog))
{
//Avoid further processing
return(true);
}
}
if (!string.IsNullOrEmpty(connectionBuilder.AttachDBFilename))
{
try
{
UsingConnection(sqlConnection, (SqlConnection con) => { });
return(true);
}
catch (SqlException e)
{
if (!string.IsNullOrEmpty(connectionBuilder.InitialCatalog))
{
return(CheckDatabaseExists(sqlConnection, commandTimeout, connectionBuilder.InitialCatalog));
}
// Initial catalog not specified
string fileName = GetMdfFileName(connectionBuilder.AttachDBFilename);
bool databaseDoesNotExistInSysTables = false;
UsingMasterConnection(sqlConnection, conn =>
{
SqlVersion sqlVersion = SqlVersionUtils.GetSqlVersion(conn);
string databaseExistsScript = SqlDdlBuilder.CreateCountDatabasesBasedOnFileNameScript(fileName, useDeprecatedSystemTable: sqlVersion == SqlVersion.Sql8);
int result = (int)CreateCommand(conn, databaseExistsScript, commandTimeout).ExecuteScalar();
databaseDoesNotExistInSysTables = (result == 0);
});
if (databaseDoesNotExistInSysTables)
{
return(false);
}
throw EntityUtil.InvalidOperation(Strings.SqlProvider_DdlGeneration_CannotTellIfDatabaseExists, e);
}
}
// CheckDatabaseExists returned false and no AttachDBFilename is specified
return(false);
}
internal static string CreateDatabaseExistsScript(string databaseName, bool useDeprecatedSystemTable)
{
var builder = new SqlDdlBuilder();
builder.AppendSql("SELECT Count(*) FROM ");
AppendSysDatabases(builder, useDeprecatedSystemTable);
builder.AppendSql(" WHERE [name]=");
builder.AppendStringLiteral(databaseName);
return(builder.unencodedStringBuilder.ToString());
}
private static void AppendSysDatabases(SqlDdlBuilder builder, bool useDeprecatedSystemTable)
{
if (useDeprecatedSystemTable)
{
builder.AppendSql("sysdatabases");
}
else
{
builder.AppendSql("sys.databases");
}
}
private static void DropDatabase(SqlConnection sqlConnection, int?commandTimeout, string databaseName)
{
// clear the connection pool in case someone's holding on to the database still
SqlConnection.ClearPool(sqlConnection);
string dropDatabaseScript = SqlDdlBuilder.DropDatabaseScript(databaseName);
UsingMasterConnection(sqlConnection, (conn) =>
{
CreateCommand(conn, dropDatabaseScript, commandTimeout).ExecuteNonQuery();
});
}
/// <summary>
/// Delete the database for the given connection.
/// There are three cases:
/// 1. If Initial Catalog is specified (X) drop database X
/// 2. Else if AttachDBFilename is specified (F) drop all the databases corresponding to F
/// if none throw
/// 3. If niether the catalog not the file name is specified - throw
///
/// Note that directly deleting the files does not work for a remote server. However, even for not attached
/// databases the current logic would work assuming the user does: if (DatabaseExists) DeleteDatabase
/// </summary>
/// <param name="connection"></param>
/// <param name="commandTimeout"></param>
/// <param name="storeItemCollection"></param>
protected override void DbDeleteDatabase(DbConnection connection, int?commandTimeout, StoreItemCollection storeItemCollection)
{
EntityUtil.CheckArgumentNull(connection, "connection");
EntityUtil.CheckArgumentNull(storeItemCollection, "storeItemCollection");
SqlConnection sqlConnection = SqlProviderUtilities.GetRequiredSqlConnection(connection);
var connectionBuilder = new SqlConnectionStringBuilder(sqlConnection.ConnectionString);
string initialCatalog = connectionBuilder.InitialCatalog;
string attachDBFile = connectionBuilder.AttachDBFilename;
if (!string.IsNullOrEmpty(initialCatalog))
{
DropDatabase(sqlConnection, commandTimeout, initialCatalog);
}
// initial catalog not specified
else if (!string.IsNullOrEmpty(attachDBFile))
{
string fullFileName = GetMdfFileName(attachDBFile);
List <string> databaseNames = new List <string>();
UsingMasterConnection(sqlConnection, conn =>
{
SqlVersion sqlVersion = SqlVersionUtils.GetSqlVersion(conn);
string getDatabaseNamesScript = SqlDdlBuilder.CreateGetDatabaseNamesBasedOnFileNameScript(fullFileName, sqlVersion == SqlVersion.Sql8);
var command = CreateCommand(conn, getDatabaseNamesScript, commandTimeout);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
databaseNames.Add(reader.GetString(0));
}
}
});
if (databaseNames.Count > 0)
{
foreach (var databaseName in databaseNames)
{
DropDatabase(sqlConnection, commandTimeout, databaseName);
}
}
else
{
throw EntityUtil.InvalidOperation(Strings.SqlProvider_DdlGeneration_CannotDeleteDatabaseNoInitialCatalog);
}
}
// neither initial catalog nor attachDB file name are specified
else
{
throw EntityUtil.InvalidOperation(Strings.SqlProvider_DdlGeneration_MissingInitialCatalog);
}
}
private static bool CheckDatabaseExists(SqlConnection sqlConnection, int?commandTimeout, string databaseName)
{
bool databaseExistsInSysTables = false;
UsingMasterConnection(sqlConnection, conn =>
{
SqlVersion sqlVersion = SqlVersionUtils.GetSqlVersion(conn);
string databaseExistsScript = SqlDdlBuilder.CreateDatabaseExistsScript(databaseName, useDeprecatedSystemTable: sqlVersion == SqlVersion.Sql8);
int result = (int)CreateCommand(conn, databaseExistsScript, commandTimeout).ExecuteScalar();
databaseExistsInSysTables = (result > 0);
});
return(databaseExistsInSysTables);
}
internal static string CreateDatabaseScript(string databaseName, string dataFileName, string logFileName)
{
var builder = new SqlDdlBuilder();
builder.AppendSql("create database ");
builder.AppendIdentifier(databaseName);
if (null != dataFileName)
{
Debug.Assert(logFileName != null, "must specify log file with data file");
builder.AppendSql(" on primary ");
builder.AppendFileName(dataFileName);
builder.AppendSql(" log on ");
builder.AppendFileName(logFileName);
}
return(builder.unencodedStringBuilder.ToString());
}
private void AppendCreateSchema(string schema)
{
AppendSql("if (schema_id(");
AppendStringLiteral(schema);
AppendSql(") is null) exec(");
// need to create a sub-command and escape it as a string literal as well...
SqlDdlBuilder schemaBuilder = new SqlDdlBuilder();
schemaBuilder.AppendSql("create schema ");
schemaBuilder.AppendIdentifier(schema);
AppendStringLiteral(schemaBuilder.unencodedStringBuilder.ToString());
AppendSql(");");
AppendNewLine();
}
internal static string CreateGetDatabaseNamesBasedOnFileNameScript(string databaseFileName, bool useDeprecatedSystemTable)
{
var builder = new SqlDdlBuilder();
builder.AppendSql("SELECT [d].[name] FROM ");
AppendSysDatabases(builder, useDeprecatedSystemTable);
builder.AppendSql(" AS [d] ");
if (!useDeprecatedSystemTable)
{
builder.AppendSql("INNER JOIN sys.master_files AS [f] ON [f].[database_id] = [d].[database_id]");
}
builder.AppendSql(" WHERE [");
if (useDeprecatedSystemTable)
{
builder.AppendSql("filename");
}
else
{
builder.AppendSql("f].[physical_name");
}
builder.AppendSql("]=");
builder.AppendStringLiteral(databaseFileName);
return(builder.unencodedStringBuilder.ToString());
}
private static string CreateObjectsScript(SqlVersion version, StoreItemCollection storeItemCollection)
{
return(SqlDdlBuilder.CreateObjectsScript(storeItemCollection, createSchemas: version != SqlVersion.Sql8));
}