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.

Answer 1

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.

Answer 2

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.