Why does disposing a SqlCeConnection both solve one exception and raise a different one?

Question

When calling the same query method twice in a session of the app, I get "DBCommandExcept"

As an experiment, I decided to dispose of the connection object at the end of the method, to see if that was the problem.

I no longer get the DBCommandExcept err msg, but instead get, "the connectionstring property has not been initialized."

IOW, it's sort of a Catch-22 situation at the moment. The pertinent code is:

string query = "SELECT Bla FROM Blah";
SqlCeCommand cmd = new SqlCeCommand(query);
cmd.CommandType = CommandType.Text;
SqlCeConnection conn = dbconn.GetConnection(); 
cmd.CommandType = CommandType.Text;//probably unnecessary
cmd.Connection = conn; 

SqlCeDataReader myReader = cmd.ExecuteReader(CommandBehavior.SingleRow);
try
{
    if (myReader.Read())
    {
        itemID = myReader.GetString(ITEMID_INDEX);
        packSize = myReader.GetString(PACKSIZE_INDEX);
        recordFound = true;
    }
}
catch (Exception ex)
{
    RRDR.LogMsgs.Append(string.Format("Exception in PopulateControlsIfVendorItemsFound(): {0}", ex.Message));
}
finally
{
    myReader.Close();
    //if (null != conn)
    //{
    //  conn.Dispose();
    //}
}

// Re: the commented-out block above: When it is active, the DBCommandExcept problem is not seen; however, I then get, "the connectionstring property has not been initialized"

I think the only non-SQL-CE-standard bit above is the dbConn.GetConnection(). Here's some of that code:

SqlCeConnection objCon = null; 

. . .

public SqlCeConnection GetConnection()
{
    return objCon;
}


private DBConnection() // class constructor
{
    try
    {
        . . .
        objCon = new SqlCeConnection(conStr);
        objCon.Open();
        . . .

Again, the error (either one, whichever one I "choose" to have) is seen only the second time through this method during one run of the app. The first time works fine.

UPDATE

I added the code below, and the comments tell the tale of woe:

// With conn check only, still get two consecutive DBCommandExcepts
// With cmd check only, still get two consecutive DBCommandExcepts
// With both, still get two consecutive DBCommandExcepts; IOW, all have the same effect
if (null != conn)
{
    conn.Close();
}
if (null != cmd)
{
    cmd.Dispose();
}

UPDATE 2

Based on unicron's suggestion, I tried using "using."

In two of the three cases (SqlCeCommand and SqlCeDataReader), converting to "using" made no diff; in the other one (SqlCeConnection), it raised the err msgs, "The ConnectionString property has not been initialized."

Still, though, the code is cleaner with the two usings, so thanks for that nudge in the best practices direction.

Here's what it looks like now:

private bool PopulateControlsIfPlatypusItemsFound()
{
    const int ITEMID_INDEX = 0;
    const int PACKSIZE_INDEX = 1;
    bool recordFound = false;

    try
    {
        string PlatypusId = txtPlatypus.Text.ToString().Trim();
        string PlatypusItemId = txtUPC.Text.ToString().Trim();
        string itemID = string.Empty;
        string packSize = string.Empty;

        string query = string.Format("SELECT ItemID, PackSize FROM PlatypusItems WHERE PlatypusID = {0} AND PlatypusItemID = {1}", PlatypusId, PlatypusItemId);
        using (SqlCeCommand cmd = new SqlCeCommand(query))
        {
            cmd.CommandType = CommandType.Text;
            SqlCeConnection conn = dbconn.GetConnection(); 
            if ((null != conn) && (!conn.State.Equals(ConnectionState.Open)))
            {
                conn.Open();
                TTBT.LogMsgs.Append("Connection opened");
            }
            cmd.CommandType = CommandType.Text;//probably unnecessary
            cmd.Connection = conn;

            using (SqlCeDataReader myReader = cmd.ExecuteReader(CommandBehavior.SingleRow))
            {
                if (myReader.Read())
                {
                    itemID = myReader.GetString(ITEMID_INDEX);
                    packSize = myReader.GetString(PACKSIZE_INDEX);
                    recordFound = true;
                }
            }

            txtID.Text = itemID;
            txtSize.Text = packSize;
            return recordFound;
        }
    }
    catch (Exception ex)
    {
        TTBT.LogMsgs.Append(string.Format("Exception in PopulateControlsIfPlatypusItemsFound: {0} - {1}\r\n", ex.Message, ex.InnerException));
        return recordFound;
    }
}

UPDATE 3

I've come even closer to normalcy by replacing the custom connection code with the generic sort, adding another "using" to the mix:

private bool PopulateControlsIfVendorItemsFound()
{
    const int ITEMID_INDEX = 0;
    const int PACKSIZE_INDEX = 1;
    bool recordFound = false;

    DUCKBILL.LogMsgs.Append("Made it into frmEntry.PopulateControlsIfVendorItemsFound()\r\n");

    try
    {
        string vendorId = txtVendor.Text.ToString().Trim();
        string vendorItemId = txtUPC.Text.ToString().Trim();
        string itemID = string.Empty;
        string packSize = string.Empty;

        if ( dbconn.isValidTable( "VendorItems" ) == -1 )
        {
            DUCKBILL.LogMsgs.Append("VendorItems not a valid table");//do not see this msg; good! VendorItems is seen as valid...
            return false;
        }

        string query = string.Format("SELECT ItemID, PackSize FROM VendorItems WHERE VendorID = {0} AND VendorItemID = {1}", vendorId, vendorItemId);
    using (SqlCeCommand cmd = new SqlCeCommand(query))
    {
        cmd.CommandType = CommandType.Text;
        using (SqlCeConnection conn = new SqlCeConnection())
        {
            string filename = "\\badPlace2B\\CCRDB.SDF";
            conn.ConnectionString = string.Format("Data Source = {0}", filename);
            cmd.CommandType = CommandType.Text;//probably unnecessary/moot
            cmd.Connection = conn; 
            conn.Open();

            using (SqlCeDataReader myReader = cmd.ExecuteReader(CommandBehavior.SingleRow))
            {
                if (myReader.Read())
                {
                    itemID = myReader.GetString(ITEMID_INDEX);
                    packSize = myReader.GetString(PACKSIZE_INDEX);
                    recordFound = true;
                }
            }
        }

        txtID.Text = itemID;
        txtSize.Text = packSize;
        return recordFound;
    }
    }
    catch (Exception ex)
    {
        DUCKBILL.LogMsgs.Append(string.Format("Exception in PopulateControlsIfVendorItemsFound: {0} - {1}\r\n", ex.Message, ex.InnerException));
        return recordFound;
    }
}

...yet I still get "DBCommandExcept"...

As to "stop futzing around with opening the connection," isn't it necessary to do so? How could/should the code above be different?

UPDATE 4

What is even more bizarre is that now my debug log file has stopped being written. I have been writing it out both in the global exception handler AND in the main form's Closed event(), and it always has (until now) at least a few entries, but within the last couple of updates to the code, it is no longer being written...????

Both places global exception handler and main form's Closed event(), the code is like so:

public static bool inDebugMode = true;

. . .
if (CCR.inDebugMode)
{
    DateTime dt = DateTime.Now;
    string timeAsStr = string.Format("{0}_{1}_{2}_{3}.txt", dt.Hour, dt.Minute, dt.Second, dt.Millisecond);
    using (StreamWriter file = new StreamWriter(timeAsStr))
    {
        // If the app closes normally, this is how the file is written; if it doesn't, 
        // (it crashed) it's written in PDAClient.ExceptionHandler()
        file.WriteLine(SSCS.LogMsgs.ToString());
    }
}

Answer 1

Since you are making several calls to a database file (that isn't going to change), I'd start out by defining your connection string and your SQL statements at the top of your class as global values:

private const int ITEMID_INDEX = 0;
private const int PACKSIZE_INDEX = 1;
private const string SQL_CONN_STR = "Data Source=\\badPlace2B\\CCRDB.SDF";
private const string SQL_GET_VENDOR_ITEMS = "SELECT ItemID, PackSize " + 
  "FROM VendorItems " +
  "WHERE VendorID=@VendorID AND VendorItemID=@VendorItemID";

These never change, so there is no reason to define them again each time you call your routine.

Personally, I do not like inserting values into SQL statements, like you have shown. Rather, try to use Parameters.

To use Parameters, you'll need to look into your database to see what type of columns VendorID and VendorItemID are. My guess is that they are both int values, but these could be GUID like values, requiring VarChar type strings. If these are strings, you should write down what sizes the columns are defined as.

For example: Below, my Serial_Number column is the SqlDbType.NVarChar and the size is 50. An SqlCeParameter for this column would be:

cmd.Parameters.Add("@Serial_Number", SqlDbType.NVarChar, 50).Value = txtSerial_Number.Text.Trim();

Since I did not know what type of data you use, I created an enumerated type to show how each method would be used. If you do not have access to the table's design, the last resort is "AddWithValue" (I personally hate that one, because it makes me look like I don't know what my database has inside).

enum ParamStyle { AddWithValue, AddIntegers, AddVarChar }

To use this enumerated type, I modified the signature of your method to pass in that value:

private bool PopulateControlsIfVendorItemsFound(ParamStyle style) {

Obviously, you will not need this, because you should know what technique you are going to be coding with.

I wasn't able to figure out what your dbconn object was. Initially, I thought this was your SqlCeConnection, but that does not have an isValidTable method, so I just commented it out:

  //if (dbconn.isValidTable("VendorItems") == -1) {
  //  DUCKBILL.LogMsgs.Append("VendorItems not a valid table");//do not see this msg; good! VendorItems is seen as valid...
  //  return false;
  //}

Speaking of SqlCeConnection...

I combined your SqlCeCommand instance with your SqlCeConnection instance. Less code typically means fewer errors:

  using (var cmd = new SqlCeCommand(SQL_GET_VENDOR_ITEMS, new SqlCeConnection(SQL_CONN_STR))) {

The CommandType, by default, is CommandType.Text, so this line is unnecessary:

    // cmd.CommandType = CommandType.Text; (this is the default)

I moved most of your variable reading outside of the try/catch routine, as none of that should ever cause an exception to be generated.

Also, I used the more targeted SqlCeException instead of the general Exception. The only thing that could fail in the block is something SqlCe related, and the SqlCeException will give you better/more specific error messages than the general Exception object will.

    } catch (SqlCeException err) {

So, what does it look like all put together?

Code:

enum ParamStyle { AddWithValue, AddIntegers, AddVarChar }
private const int ITEMID_INDEX = 0;
private const int PACKSIZE_INDEX = 1;
private const string SQL_CONN_STR = "Data Source=\\badPlace2B\\CCRDB.SDF";
private const string SQL_GET_VENDOR_ITEMS = "SELECT ItemID, PackSize FROM VendorItems WHERE VendorID=@VendorID AND VendorItemID=@VendorItemID";

private bool PopulateControlsIfVendorItemsFound(ParamStyle style) {
  bool recordFound = false;

  //DUCKBILL.LogMsgs.Append("Made it into frmEntry.PopulateControlsIfVendorItemsFound()\r\n");
  string itemID = null;
  string packSize = null;
  //string vendorId = txtVendor.Text.Trim();
  //string vendorItemId = txtUPC.Text.Trim();
  //string query = string.Format("SELECT ItemID, PackSize FROM VendorItems WHERE VendorID = {0} AND VendorItemID = {1}", vendorId, vendorItemId);

  //if (dbconn.isValidTable("VendorItems") == -1) {
  //  DUCKBILL.LogMsgs.Append("VendorItems not a valid table");//do not see this msg; good! VendorItems is seen as valid...
  //  return false;
  //}
  using (var cmd = new SqlCeCommand(SQL_GET_VENDOR_ITEMS, new SqlCeConnection(SQL_CONN_STR))) {
    // cmd.CommandType = CommandType.Text; (this is the default)
    if (style == ParamStyle.AddIntegers) { // Adding Integers:
      cmd.Parameters.Add("@VendorID", SqlDbType.Int).Value = Convert.ToInt32(txtVendor.Text.Trim());
      cmd.Parameters.Add("@VendorItemID", SqlDbType.Int).Value = Convert.ToInt32(txtUPC.Text.Trim());
    } else if (style == ParamStyle.AddVarChar) { // Adding String Values
      // NOTE: Here, you should look in your database table and
      // use the size you defined for your VendorID and VendorItemID columns.
      cmd.Parameters.Add("@VendorID", SqlDbType.VarChar, 25).Value = txtVendor.Text.Trim();
      cmd.Parameters.Add("@VendorItemID", SqlDbType.VarChar, 50).Value = txtUPC.Text.Trim();
    } else if (style == ParamStyle.AddWithValue) { // Adding as Objects (only if you don't know what the data types are)
      cmd.Parameters.AddWithValue("@VendorID", txtVendor.Text.Trim());
      cmd.Parameters.AddWithValue("@VendorItemID", txtUPC.Text.Trim());
    }
    try {
      cmd.Connection.Open();
      using (var myReader = cmd.ExecuteReader(CommandBehavior.SingleRow)) {
        if (myReader.Read()) {
          itemID = myReader.GetString(ITEMID_INDEX);
          packSize = myReader.GetString(PACKSIZE_INDEX);
          recordFound = true;
        }
      }
    } catch (SqlCeException err) {
      //DUCKBILL.LogMsgs.Append(string.Format("Exception in PopulateControlsIfVendorItemsFound: {0}\r\n", err.Message));
      // (I never return from a 'catch' statement) return recordFound;
    } finally {
      if (cmd.Connection.State == ConnectionState.Open) {
        cmd.Connection.Close();
      }
    }
  }
  if (recordFound) { // set these last, and set them OUTSIDE of the try/catch block
    txtID.Text = itemID;
    txtSize.Text = packSize;
  }
  return recordFound;
}

Happy Coding!