0

I decided to use int instead of DateTime at this stage. I created a migration with add-migration, but that failed and said I have to run CONVERT command manually. I don't know SQL Server, so I went to the Visual Studio Server Explorer and used the Table design tool to change the values to int and ran Update. It succeeded, however, when I try to save something to my database it throws an error:

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

I deleted manually the database files, located in my App_Data folder and rebuilt my app, but same error occurs. When I check the table structure with the design tool, it reflects my changes - the desired column is now int instead of DateTime.

This is the code that accesses the database:

db.Contests.Add(contest);

Contests is indeed the table I changed, but the database structure seems alright. I don't understand why it attempts to drop the database. How can I fix it?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Alex
  • 715
  • 1
  • 8
  • 29
  • so drop database is your intended action ? – Ven Feb 28 '18 at 17:09
  • @BHouse I am unsure of my intended action :D. Its a Proof of concept porject, no data, so I don't care if drop is required. Just want to fix my problem and learn how to deal with similar situations. – Alex Feb 28 '18 at 17:54
  • If i am reading it right, you want to change datatype of a column from datetime to int ?. It is always a good practice to change datatype at database level instead of using visual studio – Ven Feb 28 '18 at 18:04
  • @BHouse You mean I should write actual SQL script myself? Isn't that was Designer tool already offers? Anyway, I still find SQL confusting, it was way simpler with Node.js and MongoDB ;d. Could you please provide more concrete instructions or a good source to read on, because I am in unknown waters here. However, why doesn't a manual delete of database files work? I mean I have corrupted database, then I delete it and let EF re-create the new database from my current models. Don't get the problem. – Alex Feb 28 '18 at 18:13
  • Manual delete of Database files doesn't work straightaway because you have to bring database to single user mode or offline. It is always good to work at SQL level. W3schools has best explained SQL in simplified way. I can provide u simple instructions/Help if u like. It is quite simple process to alter datatype of column. I have explained similar scenario here yesterday https://stackoverflow.com/questions/49015797/exclusive-access-could-not-be-obtained-because-the-database-is-in-use-but-the/49015923#49015923 – Ven Feb 28 '18 at 18:17
  • @BHouse Okey, I will read on. Do you know why EF is attempting to drop the database in my case? Seems odd. – Alex Feb 28 '18 at 18:19
  • I can see best possible solution here https://stackoverflow.com/questions/6131840/entity-framework-4-code-first-prevent-db-drop-create – Ven Feb 28 '18 at 18:21
  • @BHouse You were right. Using **SQL Server Management Studio** to update the database worked out. Also I configured my `DbInitializer` to only create database, if such is not existing. This resolved my problems and I can now continue working on my app. Thanks. If you want you could write down a brief answer, or shall I? – Alex Feb 28 '18 at 20:05
  • hi @alex i have just picked up, glad it is working. You could answer your own question and will be helpful for others to see aswel. Write down step wise approach you have performed along with script in answer area – Ven Mar 01 '18 at 08:36

1 Answers1

0

Summary

Always work at Database level when modifying tables - I installed and used SQL Server Managment Studio and changed changed the tables with it successfully.

Error

As per my Cannot drop Database error, it happened, because my DbInitiallizer class was set to drop and re-recreate the database each time the model is changed. I removed that setting and instead configured it to create the database only if it does not exist. Afterwards all I needed to do was run the migration, to notify EF that all the table is now modified to reflect my models and it worked.

Simple steps to change table column property:

  1. Make the desired model changes.
  2. Use SQL Server Management Studio or similar database-level tool to modify the table column
  3. Run add-migration <Name> and update-database in Package manager console tool in Visual studio
  4. Done :)
Alex
  • 715
  • 1
  • 8
  • 29