0

My database is stored in an .mdf in App_Data and is functioning fine. My application can add, edit, delete records.

But the tables are invisible. When I open the Server Explorer and attach my .mdf and try to view the tables, there are none listed there. I attached the file to SQL Server Management Studio as well, but the only tables that show up there are those in the folder System Tables.

From what I've been able to glean from the technobabble on MSDN this could be a permissions or ownership issue. I don't know about permissions, given that I'm able to connect to the database and query it and edit/delete records. So maybe ownership; I read somewhere that tables not owned by dbo may not show. But if that's the case, I don't know what my application's ownership name is or how to make Management Studio or even Server Explorer show tables owned by other users.

Here's the connection string, in case the answer's in there:

<add name="EFDBContext" connectionString="Data Source=.\SQLEXPRESS;Integrated Security=SSPI;Initial Catalog=|DataDirectory|MLDatabase.mdf;Database=EFDbContext;User Instance=true" providerName="System.Data.SqlClient"/>

Thanks to anyone who might point me in the right direction!

ETA. this seems like a similar issue, but I don't know if the solution there would apply, since my database wasn't generated by scripts but by the Entity Framework (Code-First model).

Community
  • 1
  • 1
Sabrina S
  • 337
  • 8
  • 21
  • What happens if you delete the database and run the application again? Does it create it? What happens if you remove `User Instance` part of connection string? – Ladislav Mrnka Mar 03 '12 at 09:48
  • Hmm, the application still runs when I delete my .mdf. That's not good. Where's it storing my data!? Somewhere in the mists of SQL Express no doubt. How is that even... how can I not know which DB my data's being kept in? *sighs* Well, that explains why I can't see my tables, anyway... – Sabrina S Mar 03 '12 at 18:12
  • I found my data, in MSSQL/DATA. It's keeping it there even when I change the `Initial Catalog` in my connection string to `App_Data/MLDatabase.mdf`. But this is obviously the database it built after I deleted my .mdf, because the row index numbers are different. – Sabrina S Mar 03 '12 at 18:25
  • First of all your connection string is not correct. To use local file it must define file path in AttachDBFileName property, not in Initial Catalog property. If you don't use AttachDBFileName it creates database on SQL Express directly - that is where you found the database. – Ladislav Mrnka Mar 03 '12 at 23:58
  • When I make that one single change I get two error messages, or maybe it's one with two parts: Database 'C:\Users\Sabrina\AppData\Local\Microsoft\Microsoft SQL Server Data\SQLEXPRESS\EFDbContext.mdf' already exists. Choose a different database name. Cannot attach the file 'C:\Users\Sabrina\documents\visual studio 2010\Projects\HW3\HW3\App_Data\MLDatabase.mdf' as database 'EFDbContext'. – Sabrina S Mar 04 '12 at 03:17
  • This makes me wonder if there isn't a second connection string hidden somewhere in my project. I did find in web.config an entity framework element with this child: `` – Sabrina S Mar 04 '12 at 03:18
  • [This article](http://forums.asp.net/t/1750469.aspx/1) offered a promising lead, but there is no such conflict in my machine.config file. – Sabrina S Mar 04 '12 at 03:25

4 Answers4

1

I had a similar problem. I scripted a backup of the database, added it to mssql server and pointed my connectionsstring to the new one instead.

Magnus Karlsson
  • 3,549
  • 3
  • 31
  • 57
  • I'm not sure how that could be applied in my case - the db was created by Entity Framework using the "Code First" convention - so I didn't script it at all. Thanks tho! – Sabrina S Mar 04 '12 at 05:19
  • 1
    Right click your database in SQL Server Management Studio, choose Tasks, choose Generate scripts and let the guide create a backup scirpt for you. Now you have a sql script to use for creating a new database. – Magnus Karlsson Mar 04 '12 at 10:26
0

Check the defaultConnectionFactory type in your config. I expect it is set to LocalConnectionFactory, as this seems to be default.

Change it to the following and your SQL instance will be used.

<defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework">
  <parameters>
    <parameter value="Data Source=.; Integrated Security=True; MultipleActiveResultSets=True;" />
  </parameters>
</defaultConnectionFactory>

Your DB should appears in SQL Management Studio (SQLMS) with a name that matches the namespace.

will webster
  • 574
  • 5
  • 11
0

Please do not be concerned. it is simply necessary to re-configure the connection string in the Web.config file accordingly: - Remove the "\SQLEXPRESS" postfix from the server name; - Remove the "User Instance=true" key/value pair.

If Your DbContext Class is StudentDbContext Then put Database=StudentDb;

Or

I_Pencil
  • 1
0

The reason the tables weren't showing up is because they were not in the .mdf, of course. As I noted in a comment, I found them elsewhere, in a location chosen by SQL Server.

A workaround is to just let EF build the SQL Server database where it wants, stop the SQL Server service, move it to the App_Data folder, change the connection string appropriately, and then attach it to the ASP.NET project.

Another issue is that SQL Server's Network Service does not have permission to do anything in the C:\Users\Me folder, or the Documents and Settings folder, until you go to that folder and assign permission to the Network Service directly. Then and only then will you be able, for instance, to attach the database to the SQL Server Management Studio. This is true even when Visual Studio installs SQL Server... and then denies it permission to access the files where VS keeps it's own projects. I don't get it; IMO any developer who's aware of this, the first thing she is going to do, just in order to get her work done, is to give Network Service that permission anyway.

Sabrina S
  • 337
  • 8
  • 21