locked
Entity framework 4 doesn't save data entries in SQL Express database RRS feed

  • Question

  • At first I should mention that this problem only occurs in windows form applications and the same program in web mode for example with MVC3 works perfect.

    Some days ago I wrote a very simple windows form program using Visual studio 2010 ultimate with a SQL Express database. I added the database by choosing Add > New item > Service-Based database and an entity data model based on this database in same way. I used Entity framework for adding some new records to tables. I had done such thing with VS 2008 SP1 before with no problem so I did the same. The program compiled and ran with no errors and I entered some new data. after exiting the program I came back to the database and nothing was happened. None of information I had entered had been saved. I debug the program step by step and everything was alright. The code below is related to a very simple program with mentioned problem. Database has one table (book):

    namespace Book
    {
        public partial class BookForm : Form
        {
            BookDatabaseEntities db = new BookDatabaseEntities();
    
            public BookForm()
            {
                InitializeComponent();
            }
    
            private void saveButton_Click(object sender, EventArgs e)
            {
                Book bookToCreate = new Book();
    
                bookToCreate.Id = Guid.NewGuid();
                bookToCreate.Title = titleTextBox.Text;
    
                db.Books.AddObject(bookToCreate);
                db.SaveChanges();
            }
        }
    }
    
    

    I'll be very grateful if anyone help me. Thanks in advance.

    Monday, September 12, 2011 7:13 AM

Answers

  • |DataDirectory| is a place holder that is replaced with the actual data directory for this application at runtime.

    So you likely ran into the usual problem when the code seems to run fine but don't save that is something like :
    - the mdf is part of the VS project
    - it's "copy to output directory" is set to "always copy"

    As a result data are overwritten on each run giving the impression that no update took place.

    Or data are checked through the VS interface but the updated mdf file is the one that is in the application output directory, not the one that is in the VS project. So here the updates does take place but doesn't seems to be visible when using the VS database tools. It could be still visible (if not using the "always copy" option) when the application runs again.

    See http://blogs.msdn.com/b/smartclientdata/archive/2005/08/26/456886.aspx for details. |DataDirectory| itself is not bad. You just need to double check your mdf file property to ensure it works as expected (sometimes it can be usefull to always start with fresh, known data on each run whatever you do with your app).

     


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".
    • Marked as answer by Monoloox Saturday, September 17, 2011 4:02 AM
    Wednesday, September 14, 2011 12:13 PM
  • Problemo solved!

    As I said I had created the database by adding new "Service-Based database" object to my project and then I created the table in it. after creating data model based on this database item, the connection string below had been created:

     

    <add name="BookDatabaseEntities" connectionString="metadata=res://*/BookDatabaseModel.csdl|res:
    //*/BookDatabaseModel.ssdl|res://*/BookDatabaseModel.msl;provider=System.Data.SqlClient;
    provider connection string=&quot;data source=.\SQLEXPRESS;
    attachdbfilename=|DataDirectory|\BookDatabase.mdf;integrated security=True;user instance=True;
    multipleactiveresultsets=True;App=EntityFramework&quot;
    "
    providerName="System.Data.EntityClient" /> 

    As Patrice said it contains |DataDirectory|.

    I wrote a new program (NewTest) and this time I created a new SQL server database by right clicking on "DataConnections" in the Server Explorer window and I made a model based on this one.

     

    <add name="NewTestDatabaseEntities" connectionString="metadata=res://*/Model.csdl|res://*/Model.ssdl|res:
    //*/Model.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=A-BAYATI;
    Initial Catalog=NewTestDatabase;Integrated Security=True;Pooling=False;MultipleActiveResultSets=True&quot;
    " providerName="System.Data.EntityClient" /> 

     

    This connection string has no |DataDirectory| and DataSource has a value same as my server name.

    This is also the code which is somehow the same:

     

    namespace NewTest
    {
        public partial class Form1 : Form
        {
            NewTest.NewTestDatabaseEntities db = new NewTest.NewTestDatabaseEntities();
            public Form1()
            {
                InitializeComponent();
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
                NewTest.Book book = new NewTest.Book();
    
                book.Id = Guid.NewGuid();
                book.Title = "Hello";
    
                db.Books.AddObject(book);
                db.SaveChanges();
            }
        }
    }
    

     


    And now program works without any problem thanks you all.

    Dear Patrice would you please give me some hints about |DataDirectory|? So I can understand better what was wrong? Thank you

    • Edited by Monoloox Wednesday, September 14, 2011 10:41 AM
    • Marked as answer by Monoloox Wednesday, September 14, 2011 10:41 AM
    Wednesday, September 14, 2011 10:37 AM

All replies

  • Hi,

    What is the connection string ? Usually it is caused by not checking the proper db.

    You could either reread the db once it has been updated or use SQL Server profiler. It would also to quickly check that it really does an update somewhere.

    It could happens also if your EF initializer always recreates the db. Definitely try SQL Server Profiler... Make sure it doesn't update a mdf file that is located somewhere else than you think.

     


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".
    Monday, September 12, 2011 3:48 PM

  • It could happens also if your EF initializer always recreates the db

    Hi ..

    I'm sure that I am checking the correct database file. By the way how can I change the settings of EF initializer?

    Tuesday, September 13, 2011 5:40 AM
  • Hi, 

    please check this links:

    http://weblogs.asp.net/scottgu/archive/2010/07/16/code-first-development-with-entity-framework-4.aspx

    http://davidhayden.com/blog/dave/archive/2011/04/27/DatabaseInitializersEFCodeFirstDatabaseSetInitializer.aspx

     

    And by the way, when using Code First approach, DO NOT create a database by hand, EF 4.1 will do everything on its own, just specify the right connection string and you're done.

     

    Regards

     

     


    Clarity VS Precision
    Tuesday, September 13, 2011 7:16 AM
  • What if you try to count how many books you have in the db using some code right after having saved the new one ?

    Do also the same query on startup ? If it decreases, this is that you really don't have the same data in your db.

    BTW how do you check that no data were written ?

     


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".
    Tuesday, September 13, 2011 4:19 PM
  • Any update? Would you mind letting us know how it goes now?

    Please feel free to let us know if you need further support.

    Thanks,


    Larcolais Gong[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, September 14, 2011 2:57 AM
  • @ Giorgi:

    Hi

    I've already done this. Upgrading to EF4.1 and making database from model or code .. useless

    I don't know why this code is working in web mode and not working in windows form .. strange


    • Edited by Monoloox Wednesday, September 14, 2011 4:24 AM
    Wednesday, September 14, 2011 4:06 AM
  • @ Patrice:

    I made a DataGridView and upgrade it after each data entrance. after submitting data the GridView is still without any records. I also enter data with exactly same primary key and no error occurs. So no data have been saved in database and this is why I said that I am sure about it

    • Edited by Monoloox Wednesday, September 14, 2011 4:25 AM
    Wednesday, September 14, 2011 4:14 AM
  • @Larcolais:

    Hi and thanks for your attention.

    no changes .. my mind in blocked

    I'm going to have to upgrade from SQL express to SQL server in order to use sql profiler to trace data saving process. I don't know whether it is useful or not because I have no problem in MVC with the same code. If SQL is the cause so the same issue should be viewed in web mode too


    • Edited by Monoloox Wednesday, September 14, 2011 4:26 AM
    Wednesday, September 14, 2011 4:23 AM
  • Hi,

    yes, it sounds strange, did you try DbContext.MyEntities.ToList() or foreach or else in your WinForms application and didn't it help?

    or just write this at application start:

    mycontext.Database.Initialize(true);

    if this doesn't help either please let us look into your code.

     

    Regards


    Clarity VS Precision
    Wednesday, September 14, 2011 7:11 AM
  • My approach would be to create a test console application that displays how many books are found, add a book, and then requery the db to see how many books are found. The goal is to avoid using non essential code to show the issue (i.e. you don't need an UI if you want just to test a correct coded insertion and it could obscur things).

    If both numbers are the same, the insertion is not properly done.

    If they are correct (shows value, välue+1) but start again at the same value on the next run the db is overwritten.

    If you still don't find the issue, you'll have some minimal amount of code to post so that others could see the issue (without having to bother with some UI code that is not needed to repro the issue).

    Do you use |DataDirectory| in your connection string ?

     

     

     


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".
    Wednesday, September 14, 2011 8:29 AM
  • Problemo solved!

    As I said I had created the database by adding new "Service-Based database" object to my project and then I created the table in it. after creating data model based on this database item, the connection string below had been created:

     

    <add name="BookDatabaseEntities" connectionString="metadata=res://*/BookDatabaseModel.csdl|res:
    //*/BookDatabaseModel.ssdl|res://*/BookDatabaseModel.msl;provider=System.Data.SqlClient;
    provider connection string=&quot;data source=.\SQLEXPRESS;
    attachdbfilename=|DataDirectory|\BookDatabase.mdf;integrated security=True;user instance=True;
    multipleactiveresultsets=True;App=EntityFramework&quot;
    "
    providerName="System.Data.EntityClient" /> 

    As Patrice said it contains |DataDirectory|.

    I wrote a new program (NewTest) and this time I created a new SQL server database by right clicking on "DataConnections" in the Server Explorer window and I made a model based on this one.

     

    <add name="NewTestDatabaseEntities" connectionString="metadata=res://*/Model.csdl|res://*/Model.ssdl|res:
    //*/Model.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=A-BAYATI;
    Initial Catalog=NewTestDatabase;Integrated Security=True;Pooling=False;MultipleActiveResultSets=True&quot;
    " providerName="System.Data.EntityClient" /> 

     

    This connection string has no |DataDirectory| and DataSource has a value same as my server name.

    This is also the code which is somehow the same:

     

    namespace NewTest
    {
        public partial class Form1 : Form
        {
            NewTest.NewTestDatabaseEntities db = new NewTest.NewTestDatabaseEntities();
            public Form1()
            {
                InitializeComponent();
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
                NewTest.Book book = new NewTest.Book();
    
                book.Id = Guid.NewGuid();
                book.Title = "Hello";
    
                db.Books.AddObject(book);
                db.SaveChanges();
            }
        }
    }
    

     


    And now program works without any problem thanks you all.

    Dear Patrice would you please give me some hints about |DataDirectory|? So I can understand better what was wrong? Thank you

    • Edited by Monoloox Wednesday, September 14, 2011 10:41 AM
    • Marked as answer by Monoloox Wednesday, September 14, 2011 10:41 AM
    Wednesday, September 14, 2011 10:37 AM
  • |DataDirectory| is a place holder that is replaced with the actual data directory for this application at runtime.

    So you likely ran into the usual problem when the code seems to run fine but don't save that is something like :
    - the mdf is part of the VS project
    - it's "copy to output directory" is set to "always copy"

    As a result data are overwritten on each run giving the impression that no update took place.

    Or data are checked through the VS interface but the updated mdf file is the one that is in the application output directory, not the one that is in the VS project. So here the updates does take place but doesn't seems to be visible when using the VS database tools. It could be still visible (if not using the "always copy" option) when the application runs again.

    See http://blogs.msdn.com/b/smartclientdata/archive/2005/08/26/456886.aspx for details. |DataDirectory| itself is not bad. You just need to double check your mdf file property to ensure it works as expected (sometimes it can be usefull to always start with fresh, known data on each run whatever you do with your app).

     


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".
    • Marked as answer by Monoloox Saturday, September 17, 2011 4:02 AM
    Wednesday, September 14, 2011 12:13 PM
  • Thank you very much for helping me.
    Saturday, September 17, 2011 4:03 AM