none
Data no longer shows up after restarting application

    Question

  • I have a C# 4.0 App created in Visual Studio 2010 with a SQL Server 2012 database.
    Because ClickOnce uses a long obfuscated data path, I create a different working directory on the C:\ drive and during deployment I copy the SQL database from the Application.UserAppDataPath to my work directory, i.e., C:\AppDB.

    Here is my dilemma. I pre-populated one of the database tables with content.
    After deployment, when ClickOnce first opens my App, I can see all of the content I placed in the one table.
    However, when I close the App and reopen it, the data no longer displays in my application.

    But, if I open up the IDE, the data from my work directory "C:\AppDB" is present, but not in the deployed App.
    My program code both in the IDE and in the deployed App accesses the database from that same local directory.

    Do you have any idea why the deployed application shows the data when it initially launches after deployment, but not afterward, even though it shows when I open up Visual Studio?

    It would seem to me that my code to copy the database from the ClickOnce obfuscated data path was successful, as it displayed initially after a successful deployment, and it displays through the IDE. Only not after a restart of the actual deployed App.
    Thursday, February 01, 2018 12:37 AM

Answers

  • Hello Stanly

    Sorry it took so long to respond to your last request. While creating the "simple project" to reproduce the issue, I stumbled on the reason for the behavior. Still don't understand why the install on Vista didn't have this issue, but any ways here is the reason.

    Because I needed to implement the capability of a database Backup and Restore in my App, and due to the long ClickOnce obfuscated data path which interfered with this; I followed the excellent suggestions of a Microsoft SQL Server expert, Dan Guzman, to embed the backup and restore command in my application code.

    His comments were: "It's easy to embed the backup/restore commands in your app. Rather than rely on the generated database name, I suggest you provide a user-friendly database name along with the AttachDbFileName to facilitate the backup and restore."

    Therefore, my SQL Server Management Studio saw 2-databases, i.e., the one I created and one that read "Initial Catalog=xxxDatabase". When I would perform an import of data into my App, it was this latter database that became populated.

    When ClickOnce deployed the App and recreated my working directory of "C:\AppDB" the deployed application initially "Saw" the data because the data was in the AppDB folder, but the actual deployed App used the empty .mdf and not the xxxDatabase, if that makes sense to you.

    When I discovered this, I purposely copied the data over to the blank .mdf and ClickOnce successfully used the populated DB.

    Thanks for your suggestions.

    • Marked as answer by CodingMann Friday, February 09, 2018 5:29 PM
    Friday, February 09, 2018 5:08 PM

All replies

  • Hi,

    It is strange, I can not reproduce this issue, I'm not sure if it is because you copy the file to another folder, have you try to do not copy it?

    I think you can try to use the original path to debug if it still can not display the data in your clickonce project.

    Regards,

    Stanly


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, February 01, 2018 7:56 AM
    Moderator
  • Hello Stanly

    One of the things I built into my App was the ability to perform a backup and restore of the SQL Server database for the end-user.

    Early on I discovered that this would not be practical if I relied on the unusually long obfuscated data path that ClickOnce creates. I communicated with one of the Microsoft SQL experts and he suggested I provide a "user-friendly" database name along with a local data directory. Using his suggestion, the backup and restore works flawlessly.

    Therefore, it is absolutely necessary for me to connect to the database in the "C:\AppDB" directory, rather than the original data path.

    My App during form_load creates the "C:\AppDB" directory, then copies the database from the ClickOnce path to this newly created directory. I know the DB has been copied, and in fact contains data. I know this for 2-reasons.

    1. After ClickOnce deploys the App, it opens the App and I can "See" the data that I populated the one table with.
    2. If I open the Visual Studio IDE and select my application, again I can see that same data, i.e. the "C:\AppDB" directory contains the DB.

    It's just that once the deployed App is closed and reopened, the data does NOT display!!

    Here is the code I use to copy the database.

    private static void CopyData2DbDir()
    {
        //This method copies the application's database from its startup path
        //to a new, working directory, instead of the ClickOnce obfuscated path
        string theUser = Environment.UserName;
        string dataPath = Application.UserAppDataPath + "\\";
     
        String workDir = @"C:\AppDB";
                  
        string sourceFilePath = Path.Combine(
             dataPath, @"myDatabase.mdf");
     
        string destFilePath = Path.Combine(workDir, "myDatabase.mdf ");
        if (!File.Exists(destFilePath))
           File.Copy(sourceFilePath, destFilePath);
    }
    

    This anomaly is frustrating for 2-additional reasons.

    1. I initially created this App with a SQL Server 2008 Express database, which in fact installs fine on the earlier Windows OS's, i.e. XP to Win 8.1, but not on Windows 10. This required me to copy the working Visual Studio application to a Windows 10 environment, download and install SQL Server 2012 Express and SQL Server Management Studio 2012 Express, recreate the exact database in the 2012 SQL version, and incorporate the 2012 DB in my App, which is supposed to be compatible with Windows 10.
    2. Using the same program code (only the database is different), the App deploys via ClickOnce, but has the above behavior.

    Again, the application with the 2008 SQL database deploys via ClickOnce without any issues on the older Windows platform. The data is visible and usable, but NOT on Windows 10. What has Microsoft done with Windows 10 that is so very different from XP, Vista, Win7, Win8, Win8.1?

    I am posting this inquiry on this site, because if I post it to the SQL Server Express forum, they will merely tell me to ask my question on the ClickOnce forum. It seems to me that this is definitely a ClickOnce issue and somehow a Windows 10 issue.

    It is my sincere hope that someone may suggest what approach to take to resolve this matter, as it is holding up my deployment for Windows 10.

    Thanks

    Thursday, February 01, 2018 9:09 PM
  • Hi,

    There are three questions for your problem:

    1, Just as I said above, have you try to use the original path to test it? I do not let you use the original path, I just want to know if it is related to it.

    2, When you changed(copy) the path, which path did you use in your connection code? I mean how you spell the connection string? With the new path(C:\AppDB)?

    3, As you said, do you mean the Clickonce project does not have any issue in Win7 or Win8? The problem just occurred in Win10? Sorry that I do not know much for the difference about Win10 and the previous Windows version, so about your question "What has Microsoft done with Windows 10 that is so very different from XP, Vista, Win7, Win8, Win8.1?", I suggest you asked for help from Windows 10 Forum.

    Regards,

    Stanly


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, February 02, 2018 6:49 AM
    Moderator
  • Hello Stanly

    I'm going to answer questions 2 and 3, as I would have to change my connection string in too many places, just to see if the App can access the DB in the ClickOnce data directory. As explained earlier, I cannot use that directory.

    2. The connection is this:

    using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\AppDB\MyDatabase.mdf;Database=MyDatabase;Integrated Security=True;Connection Timeout=30;User Instance=False"))
    3. The Clickonce project does not have any issue in Vista. Therefore, I'm assuming it also does not in XP, or Win7, Win8.

    I'm not positive, but I wonder if in my "CopyData2DbDir" method, ClickOnce is somehow copying the database to my C:\AppDB folder with data in it, but placing a BLANK copy in the deployed App.

    In that method, I have tried a variety of approaches to accessing the ClickOnce data directory, i.e., as shown below

    string theUser = Environment.UserName;

    string workDir = @"C:\AppDB";

    1. string dataPath = @"C:\Users\" + theUser + @"\AppData\Local\Apps\2.0\MyDatabase.mdf"; OR 2. string dataPath = Application.UserAppDataPath + "\\"; OR 3. string dataPath = ApplicationDeployment.CurrentDeployment.DataDirectory + @"\MyDatabase.mdf"; --------------------- then 1. string sourceFilePath = Path.Combine( System.Windows.Forms.Application.StartupPath, "MyDatabase.mdf"); OR 2. string sourceFilePath = Path.Combine( dataPath, @"MyDatabase.mdf"); finally the copy... string destFilePath = Path.Combine(workDir, "MyDatabase.mdf"); if (!File.Exists(destFilePath)) File.Copy(dataPath, destFilePath);

    Could someone examine the above approaches and suggest what method is best to:

    1. Access the actual ClickOnce Data Directory
    2. suggest how to copy the .mdf file from the data directory into my C:\AppDB folder

    I ask this because, when ClickOnce first opens the deployed App, it actually displays my data, but not after closing and reopening the App. The Visual Studio IDE always successfully reads data from the C:\AppDB folder.

    This is very confusing why this happens. The problem does not exist on the Vista machine with the same exact code.

    Thank you

    Friday, February 02, 2018 8:19 PM
  • Hi,

    I'm not sure if it is because of Windows10, could you create a simple project that can reproduce this issue then provide it in Microsoft OneDrive so that I can test it in my Win10 system?

    Regards,

    Stanly


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, February 05, 2018 7:47 AM
    Moderator
  • Hello Stanly

    Sorry it took so long to respond to your last request. While creating the "simple project" to reproduce the issue, I stumbled on the reason for the behavior. Still don't understand why the install on Vista didn't have this issue, but any ways here is the reason.

    Because I needed to implement the capability of a database Backup and Restore in my App, and due to the long ClickOnce obfuscated data path which interfered with this; I followed the excellent suggestions of a Microsoft SQL Server expert, Dan Guzman, to embed the backup and restore command in my application code.

    His comments were: "It's easy to embed the backup/restore commands in your app. Rather than rely on the generated database name, I suggest you provide a user-friendly database name along with the AttachDbFileName to facilitate the backup and restore."

    Therefore, my SQL Server Management Studio saw 2-databases, i.e., the one I created and one that read "Initial Catalog=xxxDatabase". When I would perform an import of data into my App, it was this latter database that became populated.

    When ClickOnce deployed the App and recreated my working directory of "C:\AppDB" the deployed application initially "Saw" the data because the data was in the AppDB folder, but the actual deployed App used the empty .mdf and not the xxxDatabase, if that makes sense to you.

    When I discovered this, I purposely copied the data over to the blank .mdf and ClickOnce successfully used the populated DB.

    Thanks for your suggestions.

    • Marked as answer by CodingMann Friday, February 09, 2018 5:29 PM
    Friday, February 09, 2018 5:08 PM