Why would this query generate a file sharing violation?

Question

The following code gives me the error (I get it from the MessageBox.Show() in the catch block)

"Exception in PopulateBla() : There is a file sharing violation. A different process might be using the file [,,,,,,]

CODE

using (SqlCeCommand cmd = new SqlCeCommand(SQL_GET_VENDOR_ITEMS, new SqlCeConnection(SQLCE_CONN_STR))) 
{
    cmd.Parameters.Add("@VendorID", SqlDbType.NVarChar, 10).Value = vendorId; 
    cmd.Parameters.Add("@VendorItemID", SqlDbType.NVarChar, 19).Value = vendorItemId;
    try 
    {
        cmd.Connection.Open();
        using (SqlCeDataReader SQLCEReader = cmd.ExecuteReader(CommandBehavior.SingleRow)) 
        {
            if (SQLCEReader.Read())  
            {
                itemID = SQLCEReader.GetString(ITEMID_INDEX);
                packSize = SQLCEReader.GetString(PACKSIZE_INDEX);
                recordFound = true;
            }
        }
    } 
    catch (SqlCeException err) 
    {
        MessageBox.Show(string.Format("Exception in PopulateControlsIfVendorItemsFound: {0}\r\n", err.Message));//TODO: Remove
    } 
    finally 
    {
        if (cmd.Connection.State == ConnectionState.Open) 
        {
            cmd.Connection.Close();
        }
    }
}

SQL_GET_VENDOR_ITEMS is my query string.

What file sharing problem could be happening here?

UPDATE

This is the kind of code that makes that sort of refactoring recommended by ctacke below difficult:

public void setINVQueryItemGroup( string ID )
{
    try
    {
        dynSQL += " INNER JOIN td_item_group ON t_inv.id = td_item_group.id AND t_inv.pack_size = td_item_group.pack_size WHERE td_item_group.item_group_id = '" + ID + "'";
    } 
    catch( Exception ex )
    {
        CCR.ExceptionHandler( ex, "InvFile.setINVQueryDept" );
    }
}

A SQL statement is being appended to by means of a separate method, altering a global var (dynSQL) while possibly allowing for SQL Injection (depending on where/how ID is assigned). If that's not enough, any exception thrown could mislead the weary bughunter due to indicating it occurred in a different method (doubtless the victim of a careless copy-and-paste operation).

This is "Coding Horror"-worthy. How many best practices can you ignore in a scant few lines of code?

Here's another example:

string dynSQL = "SELECT * FROM purgatory WHERE vendor_item = '" + VendorItem + "' ";

if (vendor_id != "")
{
    dynSQL += "AND vendor_id = '" + vendor_id + "' ";
}

It could be done by replacing the args with "?"s, but the code to then determine which/how many params to assign would be 42X uglier than Joe Garagiola's mean cleats.

Do you by any chance accessing your database file over a network folder? — Quassnoi, Apr 03 '13 at 18:15
No, this is an embedded SQL Server CE database within a Windows CE app installed on a handheld device. — B. Clay Shannon-B. Crow Raven, Apr 03 '13 at 18:25
then probably something holds a lock on its file. It's `new SqlCeConnection(SQLCE_CONN_STR)` which fails. — Quassnoi, Apr 03 '13 at 18:25
That finally block is redundant, isn't it, what with the using statement? — B. Clay Shannon-B. Crow Raven, Apr 03 '13 at 18:26
Problem is likely somewhere else that still has a connection open. Put `using` statements on your connection objects. — LarsTech, Apr 03 '13 at 18:31
I'm thinking I will probably have to weirdify this code to bring it in line with other uses of SqlCeCommand in this codebase, which has set up all kinds of inbred cross-connections, such as casting the moon to a block of cheese and relying on suchlike, &c. — B. Clay Shannon-B. Crow Raven, Apr 03 '13 at 18:42

score 1 · Answer 1 · answered Apr 03 '13 at 18:37

1

Provided the file isn't marked read-only (you checked that, right?), then you have another process with a non-sharing lock on the file.

The isql.exe database browser that comes with SQL CE is a common culprit if it's running in the background.

Depending on your version of SQLCE, it's quite possible that another process has an open connection (can't recall what version started allowing multiple process connections), so if you have any other app in the background that has it open, that may be a problem too.

You're also using a boatload of connections to that database, and they don't always get cleaned up and released immediately up Dispose. I'd highly recommend building a simple connection manager class that keeps a single (or more like two) connections to the database and just reuses them for all operations.

answered Apr 03 '13 at 18:37

ctacke

66,480
18
94
155

The *.sdf files are not read-only. I don't know about my version of SQLCE, but the version of .NET is 1.1. I'm afraid to make any radical/significant changes to this code, as it is so convoluted that I do not understand its whys and wherefores, and am likely to make an even bigger mess. I just want to do the bare minimum necessary and get out, and on to other projects. – B. Clay Shannon-B. Crow Raven Apr 03 '13 at 18:45
I understand the frustration you have, but if it's not working now, making changes seems like a reasonable path forward. There are no doubt loads of opportunities for code improvement that you can forego, but getting it working is probably a must. Personally, I'd have build a non VM dev machine so I could effectively debug long before getting to where you are now. And I'll say again, that the version of .NET is *not* 1.1, it's 1.0. There was no CF 1.1. – ctacke Apr 03 '13 at 20:02
It turns out that a lot of the "improvements" I've made I've had to go back and reverse (such as replacing "?" with "@" in queries) and ".Add(""+bla, Blah)" with ".Add(bla, Blah)" (where "bla" is an int and with the prepended """" is being cast to a a string. Wrong-headed and old-fashioned, it seems, but trying to modernize this stuff has been, for the most part, an exercise in frustration and futility. – B. Clay Shannon-B. Crow Raven Apr 04 '13 at 16:11
The comment I was going to add here is too long, so see my update if interested in having that cobwebby feeling in your noggin. – B. Clay Shannon-B. Crow Raven Apr 04 '13 at 21:41

score 1 · Accepted Answer · answered Apr 03 '13 at 20:17

I really like Chris' idea of using a single connection to your database. You could declare that global to your class like so:

public ClayShannonDatabaseClass
{

    private SqlCeConnection m_openConnection;

    public ClayShannonDatabaseClass()
    {
       m_openConnection = new SqlCeConnection();
       m_openConnection.Open();
    }

    public void Dispose()
    {
       m_openConnection.Close();
       m_openConnection.Dispose();
       m_openConnection = null;
    }

}

I'm guessing your code is crashing whenever you attempt to actually open the database.

To verify this, you could stick an integer value in the code to help you debug.

Example:

int debugStep = 0;
try 
{
    //cmd.Connection.Open(); (don't call this if you use m_openConnection)
    debugStep = 1;
    using (SqlCeDataReader SQLCEReader = cmd.ExecuteReader(CommandBehavior.SingleRow)) 
    {
        debugStep = 2;
        if (SQLCEReader.Read())  
        {
            debugStep = 3;
            itemID = SQLCEReader.GetString(ITEMID_INDEX);
            debugStep = 4;
            packSize = SQLCEReader.GetString(PACKSIZE_INDEX);
            debugStep = 5;
            recordFound = true;
        }
    }
} 
catch (SqlCeException err) 
{
    string msg = string.Format("Exception in PopulateControlsIfVendorItemsFound: {0}\r\n", err.Message);
    string ttl = string.Format("Debug Step: {0}", debugStep);
    MessageBox.Show(msg, ttl); //TODO: Remove
}
// finally (don't call this if you use m_openConnection)
// {
//     if (cmd.Connection.State == ConnectionState.Open) 
//     {
//         cmd.Connection.Close();
//     }
// }

I'm guessing your error is at Step 1.

I've "started over" with this method by copying existing code in the project which I think will solve this particular problem (the project does use a reference to a connection that it manages), but have moved on to the exception that *that* is currently causing: http://stackoverflow.com/questions/15798207/how-can-i-see-the-sql-sent-to-the-database-after-the-parameters-have-replaced-th — B. Clay Shannon-B. Crow Raven, Apr 03 '13 at 21:15

Why would this query generate a file sharing violation?

UPDATE

2 Answers2