title: Data disappearing from sdf database in C#
body: <p>I'm running some experiments to grasp the concept behind managing databases in C# an Visual Studio. What I did was creating a simple sdf database with just one table "Books", consisting of two collumns - auto-incrementing key "ID" and "Author" field. I tried to create a DataSet object to load, modify and store changes. The program writes two numbers to console - the count of rows in the DataSet right after loading data from database, and one after modifying it but before updating it to the sdf. </p>
  
  <p>What happens, and has me at a total loss, is this:<br>
  First run, I get exactly what I expected to see, that is 0 1 - empty table was successfully loaded, and an item was added to the dataset.  </p>
  
  <p>When I run the code again I get 1 2. Then 2 3, 3 4 and so forth, as expected.
  Untill I refresh the database and try to see it's contents via Server Explorer - all that I see is empty table. Also when I run the program once again afterwards, I get 0 1 response like in the beginning.</p>
  
  <p>Here's the code I'm using:</p>
  
  <pre><code>namespace DatasetTesting{
  class Program
  {
      static void Main(string[] args)
      {
          string conString = "Data Source=Books.sdf;Persist Security Info=False;Password=pass";
          SqlCeConnection objConn = new SqlCeConnection(conString);
          objConn.Open();
  
          SqlCeDataAdapter da = new SqlCeDataAdapter("Select * from Books", objConn);
          BooksDataSet booksSet = new BooksDataSet();
          da.FillSchema(booksSet,SchemaType.Source, "Books");
          da.Fill(booksSet, "Books");
  
          Console.Out.WriteLine(booksSet.Books.Rows.Count);
  
          BooksDataSet.BooksRow book = booksSet.Books.NewBooksRow();
          book.Author = "Tolkien";
  
          booksSet.Books.AddBooksRow(book);
  
          Console.Out.WriteLine(booksSet.Books.Rows.Count);
  
          SqlCeCommandBuilder comBuilder = new SqlCeCommandBuilder(da);
          da.Update(booksSet, "Books");
  
          Console.In.ReadLine();
      }
  }
  </code></pre>
  
  <p>I must admit that at this point I have no clue about what might be going wrong - I'd be grateful for any advice.</p>