Cannot drop database "Officework" because it is currently in use

Question

I am trying to get a user registered through a sign up page. When I am clicking register button an error shows syaing:

System.Data.SqlClient.SqlException: 'Cannot drop database "Officework" because it is currently in use.'

"Officework" is the name of my project.

I have made this project using code first approach. To make a distinction between business logic and database I have implemented repository.

This is were error is showing when I'm clicking on Register button:

public void InsertStudent(Population student)
        {
            context.Populations.Add(student);
        }

This is my Sign Up controller:

 [HttpPost]
        [ValidateAntiForgeryToken]
        public ActionResult SignUp([Bind(Include = "Email,FirstName,LastName,MobileNumber,DateOfBirth,Password")] Population population)
        {
            if (ModelState.IsValid)
            {
                population.Password =password_hiding.encrypt(population.Password);
                studentRepository.InsertStudent(population);
                studentRepository.Save();
                return RedirectToAction("SignIn");
            }

            return View(population);
        }

The password_hiding is the object of the class ""Password", which I have used to encrypt password in database and decrypt it when showing to "User".

Password.cs class code

using System;
using System.IO;
using System.Security.Cryptography;
using System.Text;


namespace OfficeWork.ED
{
    public class Password
    {
        public string encrypt(string clearText)
        {
            string EncryptionKey = "MAKV2SPBNI99212";
            byte[] clearBytes = Encoding.Unicode.GetBytes(clearText);
            using (Aes encryptor = Aes.Create())
            {
                Rfc2898DeriveBytes pdb = new Rfc2898DeriveBytes(EncryptionKey, new byte[] { 0x49, 0x76, 0x61, 0x6e, 0x20, 0x4d, 0x65, 0x64, 0x76, 0x65, 0x64, 0x65, 0x76 });
                encryptor.Key = pdb.GetBytes(32);
                encryptor.IV = pdb.GetBytes(16);
                using (MemoryStream ms = new MemoryStream())
                {
                    using (CryptoStream cs = new CryptoStream(ms, encryptor.CreateEncryptor(), CryptoStreamMode.Write))
                    {
                        cs.Write(clearBytes, 0, clearBytes.Length);
                        cs.Close();
                    }
                    clearText = Convert.ToBase64String(ms.ToArray());
                }
            }
            return clearText;
        }


        public string Decrypt(string cipherText)
        {
            string EncryptionKey = "MAKV2SPBNI99212";
            byte[] cipherBytes = Convert.FromBase64String(cipherText);
            using (Aes encryptor = Aes.Create())
            {
                Rfc2898DeriveBytes pdb = new Rfc2898DeriveBytes(EncryptionKey, new byte[] { 0x49, 0x76, 0x61, 0x6e, 0x20, 0x4d, 0x65, 0x64, 0x76, 0x65, 0x64, 0x65, 0x76 });
                encryptor.Key = pdb.GetBytes(32);
                encryptor.IV = pdb.GetBytes(16);
                using (MemoryStream ms = new MemoryStream())
                {
                    using (CryptoStream cs = new CryptoStream(ms, encryptor.CreateDecryptor(), CryptoStreamMode.Write))
                    {
                        cs.Write(cipherBytes, 0, cipherBytes.Length);
                        cs.Close();
                    }
                    cipherText = Encoding.Unicode.GetString(ms.ToArray());
                }
            }
            return cipherText;
        }
    }
}

The code of my model class

using System;
using System.ComponentModel.DataAnnotations;
using System.Data.Entity;

namespace OfficeWork.Models
{
    public class Population
    {
        [Key]
        public int ID { get; set; }
        [Required]
        [Display(Name ="Email")]
        [EmailAddress(ErrorMessage ="Enter valid email address")]
        public string Email { get; set; }
        [StringLength(10)]
        [Required]
        [Display(Name = "First Name")]
        public string FirstName { get; set; }
        [StringLength(10)]
        [Required]
        [Display(Name = "Last Name")]
        public string LastName { get; set; }
        [Display(Name = "Mobile Number")]
        [Required]
        public long MobileNumber { get; set; }
        [Display(Name = "Date Of Birth")]
        [Required]
        public DateTime DateOfBirth { get; set; }
        [Display(Name = "Password")]
        [Required]
        [MaxLength(10000)]
        public string Password { get; set; }
    }

    

}

I also looked for the different kind of solution. One of them suggested to use Pooling=false in my connection string to close other connection, but it doesn't work in my case. Here is my complete connection string:

user_1690217 · Answer

You need to replace the current Database Initialization Strategy, this sounds like you have the default DropCreateDataBaseAlways or the DropCreateDatabaseIfModelChanges strategy.

There are four different database initialization strategies:

CreateDatabaseIfNotExists: This is the default initializer. As the name suggests, it will create the database if none exists as per the configuration. However, if you change the model class and then run the application with this initializer, then it will throw an exception.

DropCreateDatabaseIfModelChanges: This initializer drops an existing database and creates a new database, if your model classes (entity classes) have been changed. So, you don't have to worry about maintaining your database schema, when your model classes change.

DropCreateDatabaseAlways: As the name suggests, this initializer drops an existing database every time you run the application, irrespective of whether your model classes have changed or not. This will be useful when you want a fresh database every time you run the application, for example when you are developing the application.

Custom DB Initializer: You can also create your own custom initializer, if the above do not satisfy your requirements or you want to do some other process that initializes the database using the above initializer.

This is usually declared in the constructor for your DbContext, but it can also be configred from a static initializer or in the app.config, the following is an example on how to remove the Db Initialization Strategy Altogether:

public partial class ConfigData : DbContext
{
    static ConfigData()
    {
        //Database.SetInitializer(
        //    new System.Data.Entity.MigrateDatabaseToLatestVersion());
        Database.SetInitializer(null);

        SqlProviderServices.SqlServerTypesAssemblyName = typeof(SqlGeography).Assembly.FullName;
    }
...
}

If you remove the strategy then you will be forced to manually call the EF update-database command from the package manager console, for small projects or singleton databases, this might be an option.

You can easily write your own strategy, say to only apply Upgrades:

public class CheckAndMigrateDatabaseToLatestVersionOnly
    : IDatabaseInitializer
    where TContext : DbContext
    where TMigrationsConfiguration : DbMigrationsConfiguration, new()
{
    public virtual void InitializeDatabase(TContext context)
    {
        var migratorBase = ((MigratorBase)new DbMigrator(Activator.CreateInstance()));
        if (migratorBase.GetPendingMigrations().Any())
        {
            migratorBase.Update();
        }
    }
}

Then you could use that in the following change to the previous snippet:

Database.SetInitializer(
            new CheckAndMigrateDatabaseToLatestVersionOnly());

Cannot drop database "Officework" because it is currently in use

1 Answers