none
Updating, Viewing, Accessing, and Persisting Data in a SQLSC3.5 Database of Visual C# 2010 RRS feed

  • Question

  • I have been trying for DAYS to implement a simple storage feature for data that I create in a form based project; my code generates a unique set of attriburtres that I wish to Insert into a database for additional consideration at a later time; to accomplish this I have created a MS SQL Server Compact 3.5 database called PegSolitaireDB.sdf using the wizards of the IDE; additionally I have created a dataset called DataSet8x8.xsd for two unrelated and empty tables called Table8x8 and Pending8x8 using the appropriate wizards of the IDE; in code (see below) I attempt to create a new row and Update and/or Insert a new row  in the respective tables using the table adapter of the dataset; there are no errors generated in either execution but then as I try to view the new data in the database by opening the Database Explorer, right clicking the table and selecting Show Table Data, the table doesnot show the new data; I am running the IDE as administrater and have created the data source as directed by a walkthrough... Please consider the following additional information about the details of the implemented project:

    Solution Explorer -> Properties -> Settings ->

    Name: PegSolitaireDB.sdf   Type : (Connection string)   Scope : Application   Value : Data Source=|DataDirectory|\PegSolitaireDB.sdf;Password=admin;Persist Security Info=True

    Code to add a row and update Table8x8->

     public void updateDB()
    
     {
    
      DataSet8x8 dataSet8x8 = new DataSet8x8();
    
      DataSet8x8.Table8x8Row newTable8x8Row = dataSet8x8.Table8x8.NewTable8x8Row();
    
      newTable8x8Row.Board = BitConverter.GetBytes(valBoard);
    
      newTable8x8Row.NumPegs = (byte)valNumPegs;
    
      newTable8x8Row.Quad1 = valQuad1;
    
      newTable8x8Row.Quad2 = valQuad2;
    
      newTable8x8Row.Quad3 = valQuad3;
    
      newTable8x8Row.Quad4 = valQuad4;
    
      newTable8x8Row.Move1 = (byte)valMove1;
    
      newTable8x8Row.Move2 = (byte)valMove2;
    
      newTable8x8Row.Space1 = (byte)valSpace1;
    
      newTable8x8Row.Space2 = (byte)valSpace2;
    
      newTable8x8Row.Island1 = (byte)valIsland1;
    
      newTable8x8Row.Island2 = (byte)valIsland2;
    
      newTable8x8Row.Class = (byte)valC;
    
      newTable8x8Row.Prob = valP;
    
      newTable8x8Row.Freq = valFreq;
    
      newTable8x8Row.STD = valSTD;
    
      newTable8x8Row.OPT = valOPT;
    
      newTable8x8Row.NumMoves = valNumMoves;
    
      newTable8x8Row.ValidMoves = BitConverter.GetBytes(valValidMoves);
    
      newTable8x8Row.Solution = valSolution;
    
      newTable8x8Row.Type = valType;
    
      dataSet8x8.Table8x8.Rows.Add(newTable8x8Row);
    
      try
    
      {
    
      DataSet8x8TableAdapters.Table8x8TableAdapter table8x8TableAdapter = new DataSet8x8TableAdapters.Table8x8TableAdapter();
    
      table8x8TableAdapter.Update(dataSet8x8.Table8x8);
    
      dataSet8x8.Table8x8.AcceptChanges();
    
      MessageBox.Show("Attributes.updateDB -> SUCCESS! -> Board : " + BitConverter.ToString(newTable8x8Row.Board) +
    
         " -> dataSet8x8.Table8x8.Count : " + dataSet8x8.Table8x8.Count.ToString());
    
      }
    
      catch (Exception e)
    
      {
    
      MessageBox.Show("Attributes.updateDB -> FAILED! -> " + e.Message);
    
      }
    
     }
    
    
    
    

    Code to insert a row in table Pending8x8 ->

     public void loadPending(UInt64 board)
    
     {
    
      try
    
      {
    
      DataSet8x8 dataSet8x8 = new DataSet8x8();
    
      DataSet8x8TableAdapters.Pending8x8TableAdapter pending8x8TableAdapter = new DataSet8x8TableAdapters.Pending8x8TableAdapter();
    
      pending8x8TableAdapter.Insert(BitConverter.GetBytes(board));
    
      MessageBox.Show("Form8x8.loadPending -> SUCCESS!");
    
      }
    
      catch (Exception e)
    
      {
    
      Console.WriteLine("Form8x8.loadPending -> FAIL! -> " + e.Message);
    
      }
    
     }
    
    
    
    
    I am an accomplished academic at the masters level in computerscience and engineering; attempting to write my thesis in C# primarily because of the fact that it offers an unsigned 64 bit data type; but now totally exhasperated with the inability to accomplish this simple database implementation... any help would be greatly appreciated; allow me to Thank You in advance for any response that would lead to resolution, Sincerely SLOMO59

    SLOMO59
    Wednesday, June 8, 2011 4:28 PM

Answers

  • Your best option is to remove DataDirectory from the connection string, and replace with the full path to the database file.
    Please mark as answer, if this was it. Visit my SQL Server Compact blog
    • Marked as answer by SLOMO59 Friday, June 10, 2011 4:17 PM
    Thursday, June 9, 2011 2:54 PM

All replies

  • Thank you for the response.. and as such the code as written does work... it does add a row to an obscure version of the database... additional research has revealed that the file system of the IDE hides a copy of the database in the bin/debug directory. I find this infuriatingly counterintuitive because I cant easily go to the "database" and see the changes...

    In this project I generate data and want to hold it... then access it again through a Select query, process it to modify some of the values at each row, and then save the updated results back to the database; this process occurs repeatedly. I wish to continue to develop the application as I collect data but I find it extremely unnerving to be confounded with this database duality as data is not readily available to view or manipulate and may be lost in the very process of development.

    As it seems Im not alone in my frustration (see this forum thread of past issues with the same problem -> http://social.msdn.microsoft.com/forums/en-US/sqlce/thread/e7f7a713-b0fd-4716-a19d-4421e8be4c88/). All I want to do is store the data and then access it, modify it, and update it in a way that is sucure for later consideration and I cant find any acceptable way to do this within the current scope of capability using the database, dataset, tableadapter namespace offered by the IDE...

    In the past I have written this application in the Java NetBeans IDE using direct SQL commands effectively but unfortunately Java does not offer an unsigned 64 bit datatype and so for Java I had to wirte a class to emulate such a datatype but at great cost to computational efficiency and since my thesis attempts to demonstrate an efficency in backtrack search I need to be able to manipulate a 64 bit entity using bitwise operations.

    Based on the thread referenced above I find the following insight and have interjected specific questions within the context of the response parenthetically referenced with "<...>"

    Question...

    Is there any way i can commit changes which happens during runtime (when i am developing the application) such as inserts/updates and deletes to the .sdf DB on the machine ?????

    Response...

    You have several options to change this behavior. If your sdf file is part of the content of your project, this will affect how data is persisted. Remember that when you debug, all output of your project (including the sdf) if in the bin/debug folder.

    - You can decide not to include the sdf file as part of your project and manage the file location runtime <so to do this the connection must be specific to this out-of-project location of the database?>.

    - If you are using "copy if newer", and project changes you make to the database will overwrite any runtime/debug changes <so if "copy if newer" is a selected option then the "project database" overwrites the "debug database"?>.

    - If you are using "Do not copy", you will have to specify the location in code (as two levels above where your program is running) <so by "specify the location in code" then the connection string must be specific to the database that I wish to persist?>.

    - If you have "Copy always", any changes made during runtime will always be overwritten <so if I select the "Copy Always" option then the "debug database" is overwriten by the "project database" at runtime?... as it seems that what is meant here but not what I need>.

    To be clear about what I wish to do... save data generated during a debug session of code development... keep that development data persistent so that at another runtime i can access it and continue to work on it...

    Any insight as to how to do this will be greatly appreciated... Thank You all again for reading and considering my dilemma.

    Sincerely,

    SLOMO59


    SLOMO59
    Wednesday, June 8, 2011 4:30 PM
  • Your best option is to remove DataDirectory from the connection string, and replace with the full path to the database file.
    Please mark as answer, if this was it. Visit my SQL Server Compact blog
    • Marked as answer by SLOMO59 Friday, June 10, 2011 4:17 PM
    Thursday, June 9, 2011 2:54 PM
  • Thank You very much for the reply... finally the insight to solve this problem... and to be absolutely clear to its solution...

    Focus Visual C# 2010 Express to consider the local SQLCE 3.5 database located in the project directory (instead of the copy that is generated in the bin/debug directory) by the following steps...

    Solution Explorer -> R Click Project Node -> Properties -> Settings

    In the Value column of the connection string, revise by replacing the parameter DataDirectory to the full path for the location of the database

    Changes made during runtime that would modify the database are now persistant throughout the development process


    SLOMO59
    Friday, June 10, 2011 4:38 PM
  • Had this problem for some time and this is my solution, hope it helps someone. Maybe this is bad, so if anyone knows better then me ( Im new to c#) let me know.

    Go to Settings->Build->Outputh path -> erase everything ( check Configuration option on top, it must be on Debug).

    Now VS will not add \bin\Debug to your |DataDirectory|, and all changes you make to your DataBase will stay.

    Reason why I did not want to change connection string to full path, was that I was afraid that It would make problems once I want to deploy my program. Am I wrong with this one?

    Friday, February 17, 2012 7:33 AM