/// <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)); }