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