Different users cannot use my WinForms app at the same time

Question

I have a VS WinForms app that uses OleDB to read (only reading, no writing) info from an Excel file. My end-users install the program via click-once, created through the publish-option in Visual Studio Community. The installation works fine. BUT: Only one user at a time is able to use the program. Windows gives me this error:

Files that may help descibe the problem:
  C:\Users\shoo\AppData\Local\Temp\WERA825.tmp.WERInternalMetadata.xml
  C:\Users\shoo\AppData\Local\Temp\WERDFAA.tmp.appcompat.txt
  C:\Users\shoo\AppData\Local\Temp\WERE18F.tmp.mdmp

The files all contain the same information:

    <?xml version="1.0" encoding="UTF-16"?>
<DATABASE>
<EXE NAME="Streetlife Database Lookup.exe" FILTER="CMI_FILTER_PRIVACY">
    <MATCHING_FILE NAME="lcpi.data.oledb.net4.dll" SIZE="841728" CHECKSUM="0x4AEF1CA6" BIN_FILE_VERSION="1.0.1.2490" BIN_PRODUCT_VERSION="1.0.1.2490" PRODUCT_VERSION="1.0.1.2490" FILE_DESCRIPTION="LCPI ADO.NET Data Provider for OLE DB [NET4]" COMPANY_NAME="LCPI" PRODUCT_NAME="lcpi.data.oledb" FILE_VERSION="1.0.1.2490" ORIGINAL_FILENAME="lcpi.data.oledb.net4.dll" INTERNAL_NAME="lcpi.data.oledb.net4.dll" LEGAL_COPYRIGHT="Copyright © LCPI 2011-2015" VERDATEHI="0x0" VERDATELO="0x0" VERFILEOS="0x4" VERFILETYPE="0x2" MODULE_TYPE="WIN32" PE_CHECKSUM="0xD742F" LINKER_VERSION="0x0" UPTO_BIN_FILE_VERSION="1.0.1.2490" UPTO_BIN_PRODUCT_VERSION="1.0.1.2490" LINK_DATE="08/06/2015 14:05:40" UPTO_LINK_DATE="08/06/2015 14:05:40" VER_LANGUAGE="Taalonafhankelijk [0x0]" EXE_WRAPPER="0x0" />
    <MATCHING_FILE NAME="lcpi.lib.net4.dll" SIZE="347136" CHECKSUM="0x3345E399" BIN_FILE_VERSION="1.0.0.1224" BIN_PRODUCT_VERSION="1.0.0.1224" PRODUCT_VERSION="1.0.0.1224" FILE_DESCRIPTION="LCPI Instrumental Library for .NET 4" COMPANY_NAME="LCPI" PRODUCT_NAME="lcpi.lib" FILE_VERSION="1.0.0.1224" ORIGINAL_FILENAME="lcpi.lib.net4.dll" INTERNAL_NAME="lcpi.lib.net4.dll" LEGAL_COPYRIGHT="Copyright © LCPI 2011-2015" VERDATEHI="0x0" VERDATELO="0x0" VERFILEOS="0x4" VERFILETYPE="0x2" MODULE_TYPE="WIN32" PE_CHECKSUM="0x5AFBE" LINKER_VERSION="0x0" UPTO_BIN_FILE_VERSION="1.0.0.1224" UPTO_BIN_PRODUCT_VERSION="1.0.0.1224" LINK_DATE="08/06/2015 14:05:39" UPTO_LINK_DATE="08/06/2015 14:05:39" VER_LANGUAGE="Taalonafhankelijk [0x0]" EXE_WRAPPER="0x0" />
    <MATCHING_FILE NAME="Microsoft.VisualStudio.OLE.Interop.dll" SIZE="118784" CHECKSUM="0xE2A9E029" BIN_FILE_VERSION="7.10.6070.0" BIN_PRODUCT_VERSION="7.10.6070.0" PRODUCT_VERSION="7.10.6070" FILE_DESCRIPTION="" COMPANY_NAME="Microsoft Corporation" PRODUCT_NAME="Microsoft® Visual Studio .NET" FILE_VERSION="7.10.6070" ORIGINAL_FILENAME="" INTERNAL_NAME="" LEGAL_COPYRIGHT="Copyright© Microsoft Corporation.  All rights reserved." VERDATEHI="0x0" VERDATELO="0x0" VERFILEOS="0x4" VERFILETYPE="0x2" MODULE_TYPE="WIN32" PE_CHECKSUM="0x2C347" LINKER_VERSION="0x0" UPTO_BIN_FILE_VERSION="7.10.6070.0" UPTO_BIN_PRODUCT_VERSION="7.10.6070.0" LINK_DATE="08/24/2009 12:53:35" UPTO_LINK_DATE="08/24/2009 12:53:35" VER_LANGUAGE="Engels (Verenigde Staten) [0x409]" EXE_WRAPPER="0x0" />
    <MATCHING_FILE NAME="Streetlife Database Lookup.exe" SIZE="125376" CHECKSUM="0x5042252" BIN_FILE_VERSION="1.0.0.0" BIN_PRODUCT_VERSION="1.0.0.0" PRODUCT_VERSION="1.0.0.0" FILE_DESCRIPTION="WindowsFormsApplication1" COMPANY_NAME="Streetlife" PRODUCT_NAME="Streetlife Databse Lookup" FILE_VERSION="1.0.0.0" ORIGINAL_FILENAME="Streetlife Database Lookup.exe" INTERNAL_NAME="Streetlife Database Lookup.exe" LEGAL_COPYRIGHT="Copyright © SHOO;-)" VERDATEHI="0x0" VERDATELO="0x0" VERFILEOS="0x4" VERFILETYPE="0x1" MODULE_TYPE="WIN32" PE_CHECKSUM="0x2CFAE" LINKER_VERSION="0x0" UPTO_BIN_FILE_VERSION="1.0.0.0" UPTO_BIN_PRODUCT_VERSION="1.0.0.0" LINK_DATE="08/27/2015 10:24:53" UPTO_LINK_DATE="08/27/2015 10:24:53" VER_LANGUAGE="Taalonafhankelijk [0x0]" EXE_WRAPPER="0x0" FILE_ID="00005974d6b5b0b5a80644936a6ef12feedfa35a97e3" PROGRAM_ID="0000da39a3ee5e6b4b0d3255bfef95601890afd80709" />
</EXE>
<EXE NAME="KERNELBASE.dll" FILTER="CMI_FILTER_THISFILEONLY">
    <MATCHING_FILE NAME="KernelBase.dll" SIZE="424448" CHECKSUM="0xBE7BDE30" BIN_FILE_VERSION="6.1.7601.18939" BIN_PRODUCT_VERSION="6.1.7601.18939" PRODUCT_VERSION="6.1.7601.18015" FILE_DESCRIPTION="DLL-bestand voor Windows NT BASE API-client" COMPANY_NAME="Microsoft Corporation" PRODUCT_NAME="Besturingssysteem Microsoft® Windows®" FILE_VERSION="6.1.7601.18015 (win7sp1_gdr.121129-1432)" ORIGINAL_FILENAME="Kernelbase" INTERNAL_NAME="Kernelbase" LEGAL_COPYRIGHT="© Microsoft Corporation. Alle rechten voorbehouden." VERDATEHI="0x0" VERDATELO="0x0" VERFILEOS="0x40004" VERFILETYPE="0x2" MODULE_TYPE="WIN32" PE_CHECKSUM="0x70CFC" LINKER_VERSION="0x60001" UPTO_BIN_FILE_VERSION="6.1.7601.18939" UPTO_BIN_PRODUCT_VERSION="6.1.7601.18939" LINK_DATE="07/22/2015 23:59:55" UPTO_LINK_DATE="07/22/2015 23:59:55" EXPORT_NAME="KERNELBASE.dll" VER_LANGUAGE="Nederlands (Nederland) [0x413]" EXE_WRAPPER="0x0" />
</EXE>
<EXE NAME="kernel32.dll" FILTER="CMI_FILTER_THISFILEONLY">
    <MATCHING_FILE NAME="kernel32.dll" SIZE="1163264" CHECKSUM="0x1FD0A6B3" BIN_FILE_VERSION="6.1.7601.18939" BIN_PRODUCT_VERSION="6.1.7601.18939" PRODUCT_VERSION="6.1.7601.18015" FILE_DESCRIPTION="DLL-bestand voor Windows NT BASE API-client" COMPANY_NAME="Microsoft Corporation" PRODUCT_NAME="Besturingssysteem Microsoft® Windows®" FILE_VERSION="6.1.7601.18015 (win7sp1_gdr.121129-1432)" ORIGINAL_FILENAME="kernel32" INTERNAL_NAME="kernel32" LEGAL_COPYRIGHT="© Microsoft Corporation. Alle rechten voorbehouden." VERDATEHI="0x0" VERDATELO="0x0" VERFILEOS="0x40004" VERFILETYPE="0x2" MODULE_TYPE="WIN32" PE_CHECKSUM="0x12A426" LINKER_VERSION="0x60001" UPTO_BIN_FILE_VERSION="6.1.7601.18939" UPTO_BIN_PRODUCT_VERSION="6.1.7601.18939" LINK_DATE="07/22/2015 23:59:54" UPTO_LINK_DATE="07/22/2015 23:59:54" EXPORT_NAME="KERNEL32.dll" VER_LANGUAGE="Nederlands (Nederland) [0x413]" EXE_WRAPPER="0x0" />
</EXE>
</DATABASE>

I have done the following to help avoid the error:

I only read the Excel file and have made the actual Excel file read-only.
I close all my connections directly after filling my datasets or using my readers.
A WinForms app without OleDB and distributed through publish>click-once works for multiple users at the same time.
If I start a new project, copy/paste all my code(so it is exactly the same, but the app-name is different): Two instances of my program can be run at the same time...

This is my first C# experience, so please be gentle ;-)

It's quite hard to tell from the files what the error could be. How about if you try and make a copy of the excel file you are reading whenever you need it. So instead of every user accessing the same file, just make a temp copy and that way each user is technically reading their "own" file. Let me know if you need help with this route — TheDanMan, Sep 23 '15 at 14:37
@TheDanMan: That is probably a very good idea! I have found the following code to create it. Is this right? 'code'string fileName = System.IO.Path.GetTempPath() + Guid.NewGuid().ToString() + ".xlsx";'code' And do I then use my string fileName in the connectionstring for OleDB? — dreojs16, Sep 23 '15 at 15:16
Yeah something like that could work. So then the user reads that file — TheDanMan, Sep 25 '15 at 07:55
But where do I put the filepath of my existing file that i want to create a temporary file from(a copy)? — dreojs16, Sep 25 '15 at 09:53
It depends how your software is deployed. I would suggest you put the "main" on a network drive if one file is shared across multiple mashines. Then when you application needs the file, it can copy it from the network drive to the application folder Application.StartupPath() and then use that leaving the main file intact. This way as well, if something goes wrong or the file crashes whilst the user is working on it then you know it's just a temp file and not the main file. See answer below — TheDanMan, Sep 25 '15 at 15:10
Every .NET programmer eventually discovers that writing an event handler for the AppDomain.CurrentDomain.UnhandledException event is not optional. Because if you don't then you have no idea why your program doesn't work when it leaves your dev machine. — Hans Passant, Sep 25 '15 at 15:48
Have you tried to connect/disconnect to DB for each operation? If you keep open connection for each session, it is normal to lock the file. — i486, Sep 28 '15 at 09:01
@HansPassant, I have no idea what you are talking about, probably because I am no real .Net programmer. If you truly beleive it is important, please elaborate. @ TheDanMan, The mother file is on a network drive. I am now copying to the users local temp folder, so that I can overwrite at each program startup(to keep the database up to date with the mother file). Thanks for pointing in the right direction! @ i486, I tried that, but every click in my program changes the needed dataset, so a lot of connections are being made with a single user. A big chance that another user wants to connect. — dreojs16, Sep 28 '15 at 12:24
Have a look at my edited answer below and let me know if the code helps — TheDanMan, Sep 29 '15 at 11:35

TheDanMan · Accepted Answer · 2015-09-28T13:43:16.143

I think the problem is that when you open an excel file, it creates a "lock". Normally if you do this via the MS Excel interface, it will prompt you with a warning and you can choose what to do See here. But in this case, since you're reading it from a C# program, it might be returning that weird error

I suggest you try storing the main file on a network drive and make a copy of it locally to the application folder for each user to use their own file instead of reading the same file at once. See here for file copying

Try the following code

string filename = "Streetlife Product Database.xlsx";
string fullfilename = String.Format(@"//MILKYWAY/SO_Arc/template/{1}", Application.StartupPath, filename);
string tempath = System.IO.Path.GetTempPath();
string filenameTemp = String.Format("{0}.xlsx", System.IO.Path.GetTempFileName());
System.IO.File.Copy(fullfilename, filenameTemp, true);
string connetionString = String.Format(@"Provider = Microsoft.ACE.OLEDB.12.0; Data Source ={0};Extended Properties = ""Excel 12.0 Xml;HDR=YES;IMEX=1""", filenameTemp);

using (OleDbConnection oledb = new OleDbConnection(connetionString))
{
    try
    {
        oledb.Open();
    }
    catch (Exception ex)
    {
        oledb.Close();
        oledb.Dispose();

        MessageBox.Show("Error trying to read file: " + ex.Message);                    
    }
 }

Thanks for all the help @TheDanMan! I now recreated my Excel database to SQL server table, and am using it as such in my winform. This works great and I can have as many instances of my form running and reading the database as i want... — dreojs16, Jan 19 '16 at 16:09

score 0 · Answer 2 · edited May 23 '17 at 12:17

It seems that you can only have one OleDB connection to a certain file. So when one user connects to the file, another cannot.

Sadly OleDB driver by default will open file exclusively then you can't open it when it's in use by someone else, even just for reading.

Taken from Read Excel file with OleDB c#, when it is used by other process

So now I copy the (mother) excel file to a temp location on the users computer, and connect to the temp(child)file there. This seems to work, but I cant quit get the code right yet.

I have this so far:

 string filename = @"//MILKYWAY/SO_Arc/template/Streetlife Product Database.xlsx";
            string filenameTemp = System.IO.Path.GetTempFileName();
            System.IO.File.Copy(filename, filenameTemp, true);
            string connetionString = @"Provider = Microsoft.ACE.OLEDB.12.0; Data Source =" + filenameTemp + ";Extended Properties = 'Excel 12.0 Xml;HDR=YES;IMEX=1'";

You should add this as a comment instead of "answer" to avoid confusion. I have edited your code snippet and got it working across multiple users and multiple PC's. Please see my answer. Mark it as answer if it works. — TheDanMan, Sep 28 '15 at 13:44

Different users cannot use my WinForms app at the same time

2 Answers2