Why is this SqlCeCommand ExecuteReader call failin

2019-06-14 20:46发布

站内文章 / 后端开发
58 0 0

问题:

I adapted my code below from "Microsoft .NET Compact Framework" by Wigley and Wheelwright:

SqlCeConnection cn = new SqlCeConnection(@"Data Source=\My Documents\Traffic.sdf");
String sSQL= "SELECT CarID, Reg, Location FROM Cars");
SqlCeCommand cmdSelect = new SqlCeCommand(sSQL, cn);
cmdSelect.CommandType = CommandType.Text;
SqlCeDataReader dtr = cmdSelect.ExecuteReader(CommandBehavior.Default); // It's "cmd." instead of "cmdSelect." in the book, but that doesn't compile
while (dtr.Read())
{
    ListViewItem item = new ListViewItem(dtr.GetInt32(0).ToString());
    item.SubItems.Add(dtr.GetString(1));
    item.SubItems.Add(dtr.GetString(2));
    listViewCars.Items.Add(item);
}

...so that my code is this:

    private String getDataAsXMLFromTable(String tableName, String siteNum)
    {
        String strXML;
        StringBuilder sbXML = new StringBuilder();

        String lineId;
        String refNum;
        String upcCode;
        String desc;
        String dept;
        String vendorId;
        String upcPackSize;
        String Id;
        String packSize;
        String unitCost;
        String unitList;
        String unitQty;
        String newItem;

        String paddedSiteNum = Prepad(3, siteNum); // this turns "3" into "003" etc.
        String conStr = String.Format(@"Data Source=\My Documents\HHSDB{0}.sdf", paddedSiteNum);
        MessageBox.Show(String.Format("conStr is {0}", conStr));
        SqlCeConnection cn = new SqlCeConnection(conStr);
        String qry = String.Format("SELECT * FROM {0}", tableName);
        MessageBox.Show(String.Format("qry is {0}", qry));
        SqlCeCommand cmdSelect = new SqlCeCommand(qry, cn);
        MessageBox.Show("Made it to pt 1"); // <= I see this
        cmdSelect.CommandType = CommandType.Text;
        SqlCeDataReader dtr = cmdSelect.ExecuteReader(CommandBehavior.Default);
        MessageBox.Show("Made it to pt 2"); // <= I don't see this
        try
        {
            while (dtr.Read())
            {
                lineId = dtr["line_id"].ToString(); // Wiggly uses dtr.GetString(1); would then 
have to use GetX()...
                refNum = dtr["ref_no"].ToString();
                upcCode = dtr["upc_code"].ToString();
                desc = dtr["description"].ToString();
                dept = dtr["department"].ToString();
                vendorId = dtr["vendor_id"].ToString();
                upcPackSize = dtr["upc_pack_size"].ToString();
                Id = dtr["id"].ToString();
                packSize = dtr["pack_size"].ToString();
                unitCost = dtr["unit_cost"].ToString();
                unitList = dtr["unit_list"].ToString();
                unitQty = dtr["unit_qty"].ToString();
                newItem = dtr["new_item"].ToString();
                strXML = String.Format("<INV><line_id>{0}</line_id><ref_no>{1}</ref_no><upc_code>
{2}</upc_code><description>{3}</description><department>{4}</department><vendor_id>{5}
</vendor_id><upc_pack_size>{6}</upc_pack_size><id>{7}</id><pack_size>{8}</pack_size><unit_cost>{9}
</unit_cost><unit_list>{10}</unit_list><unit_qty>{11}</unit_qty><new_item>{12}</new_item></INV>", 
                    lineId, refNum, upcCode, desc, dept, vendorId, upcPackSize, Id, packSize,
                    unitCost, unitList, unitQty, newItem);
                sbXML.Append(strXML);

            }
        }
        catch (Exception ex)
        {
            SSCS.ExceptionHandler(ex, "frmCentral.getDataAsXMLFromTable()");
        }
        return String.Format("<Command>{0}</Command>", sbXML.ToString());
    }

The database I'm trying to access is in the handheld device's My Documents folder, and is named "HHSDB003.sdf"

My first MessageBox.Show() shows this:

...and this is, indeed, where the database is:

My second MessageBox.Show() shows this:

...and there is such a table:

I see "Made it to pt 1" but not "Made it to pt 2", so it must be failing on the call to ExecuteReader() - but why?

This is the err msg I see:

I copied NETCFv35.Messages.EN.wm.cab to the folder on the handheld where the .exe is, but it still shows the same err msg as above. Do I need to put it elsewhere or install it / set it up somehow? 2-clicking it didn't do anything but show me the files that were inside it.

compactf SqlCeCommand ExecuteReader SqlCeConnection SqlCeDataReader windowsce c#

UPDATE

Based on what I read here, I put the path/filename in single quotes like so:

String conStr = String.Format(@"Data Source='\My Documents\HHSDB{0}.sdf'", paddedSiteNum);

...but it made no diffrence.

UPDATE 2

I changed my code, based on the answer, to this:

try
{
    . . .
    string connStr = "Data Source=\"\\My Documents\\HHSDB003.SDF\"";
    MessageBox.Show(String.Format("connStr is {0}", connStr));
    try
    {
        SqlCeConnection conn = new SqlCeConnection(connStr);
        String qry = String.Format("SELECT * FROM {0}", tableName);
        MessageBox.Show(String.Format("qry is {0}", qry));
        SqlCeCommand cmdSelect = new SqlCeCommand(qry, conn);
        MessageBox.Show("Made it to pt 1"); // <= I see this
        cmdSelect.CommandType = CommandType.Text;
        conn.Open(); // <= I was missing this
        MessageBox.Show("Made it to pt 2"); // <= I don't see this
        SqlCeDataReader dtr = cmdSelect.ExecuteReader(CommandBehavior.Default);
        . . .
}
catch (Exception ex)
{
    MessageBox.Show(ex.Message);
}

...but I get an exception after seeing "Made it to pt 1" - I never see "Made it to pt 2"

The exception message that displays from the catch block shows nothing / an empty string.

There is a database named HHSDB003.SDF in the handheld's My Documents folder, and it does contain a table named ~; so what am I missing here still?

UPDATE 3

I changed the catch block to this:

catch (Exception ex)
{
    MessageBox.Show(String.Format("inner ex is {0}", ex.InnerException.ToString()));
}

...and now see a Null Reference Exception as the Inner Exception. ???

UPDATE 4

Since I see "Made it to pt 1" here:

string connStr = "Data Source=\"\\My Documents\\HHSDB003.SDF\"";
String qry = String.Format("SELECT * FROM {0}", tableName);

MessageBox.Show(String.Format("connStr is {0}", connStr));
try
{
    SqlCeConnection conn = new SqlCeConnection(connStr);
    SqlCeCommand cmdSelect = new SqlCeCommand(qry, conn);
    MessageBox.Show("Made it to pt 1"); // <= I see this
    cmdSelect.CommandType = CommandType.Text;
    conn.Open(); // <= I was missing this

...conn and qry must be fine; so why does calling open on conn lead to an NRE?

UPDATE 5

I aded this code to see the vals of the SqlCeConnection's Database and DataSource properties:

MessageBox.Show(
    String.Format("db is {0}; data source is {1}", conn.Database, conn.DataSource));
// This should be redundant, but getting an NRE on the Open call...
conn.ConnectionString = connStr;

...and I see:

db is \My Documents\HHSDB003.SDF
data source is  \My Documents\HHSDB003.SDF

Should Database and DataSource really be the same thing?

UPDATE 6

It's possible that I already have a "live" (global) connection to the database established elsewhere/previously; could this cause a second connection to implode?

I know[,] this is bad design, but "it is what it is" for now

UPDATE 7

I never get past the call to Open -- it blows up hotter than George Brett caught with pine tar on his bat. Here's the pertinent part of the code:

string connStr = String.Format("Data Source=\"\\My Documents\\HHSDB{0}.SDF\"", paddedSiteNum);
String qry = String.Format("SELECT * FROM {0}", tableName);
MessageBox.Show(String.Format("connstr is {0}; qry is {1}", connStr, qry));
SqlCeConnection conn;
SqlCeCommand cmdSelect;
try
{
    conn = new SqlCeConnection(connStr);
    cmdSelect = new SqlCeCommand(qry, conn);
    cmdSelect.CommandType = CommandType.Text;
    MessageBox.Show(
        String.Format("db is {0}; data source is {1}, conn string is {2}", conn.Database, conn.DataSource, conn.ConnectionString));
    conn.ConnectionString = connStr;
    MessageBox.Show("Made it just before conn.Open()"); // <= I always see this
    if ((null != conn) && (!conn.State.Equals(ConnectionState.Open)))
    {
        MessageBox.Show("Will try to Open");
        conn.Open();
    }
    MessageBox.Show("Made it just after conn.Open()"); // <= I never see this; the NRE steals the show

The message box that displays (before "Made it just before conn.Open()") is:

I then see "Will try to Open", then the NRE.

UPDATE 8

Below is, not just the code itself, but the code that calls it, too. Is it a possible code-reentrancy issue? Could the second foreach loop in SendInventoryData() be calling getDataAsXMLFromTable() while the previous call to getDataAsXMLFromTable() has not yet completed? That would seem not to be the case, as I never see "Made it just after conn.Open()" - not even once. It seems if that were the issue, I would at least see it once...

private void mnuSendINV_Click(object sender, EventArgs e)
{
    // TODO: Remove after testing; "hijacking" this to query and send the file directly
    SendInventoryData();
    return;

private void SendInventoryData()
{
    String siteNum = String.Empty;
    ArrayList sbInventories = new ArrayList();
    foreach (String tbl in listboxWork.Items)
    {
        // Ignore DSD tables; just get INV tables
        if (tbl.IndexOf("DSD") >-1) continue;
        String tblName = getTableNameForInventoryName(tbl);
        siteNum = getSiteNumberFromInventoryName(tbl);
        sbInventories.Add(tblName);
    }
    foreach (string tbl in sbInventories) 
    {
        string strData = getDataAsXMLFromTable(tbl, "003"); 
        string fileName = getINVFileName(siteNum); 
        String uri = String.Format("http://192.168.125.50:21608/api/inventory/sendXML/duckbill/platypus/{0}", fileName);
        RESTfulMethods rm = new RESTfulMethods();
        rm.CreateRequestNoCredentials(uri, RESTfulMethods.HttpMethods.POST, strData, "application/xml"); 
    }
}

private String getDataAsXMLFromTable(String tableName, String siteNum)
{
    string xmlOutput = String.Empty;
    // data/xml fields
    String lineId;
    String refNum;
    . . .
    String newItem;

    String paddedSiteNum = Prepad(3, siteNum);
    string connStr = String.Format("Data Source=\"\\My Documents\\HHSDB{0}.SDF\"", paddedSiteNum);
    String qry = String.Format("SELECT * FROM {0}", tableName);
    MessageBox.Show(String.Format("connstr is {0}; qry is {1}", connStr, qry));
    SqlCeConnection sqlceConn;
    SqlCeCommand sqlceCmd;
    try
    {
        sqlceConn = new SqlCeConnection(connStr);
        sqlceCmd = new SqlCeCommand(qry, sqlceConn);
        sqlceCmd.CommandType = CommandType.Text;
        MessageBox.Show("Made it just before conn.Open()"); // <= I see this
        if ((null != sqlceConn) && (!sqlceConn.State.Equals(ConnectionState.Open)))
        {
            MessageBox.Show("Will try to Open"); // <= I see this
            sqlceConn.Open();
        }
        MessageBox.Show("Made it just after conn.Open()"); // <= I don't see this/make it to here; I see the NRE instead
        SqlCeDataReader dtr = sqlceCmd.ExecuteReader(CommandBehavior.Default);
        XmlDocument doc = new XmlDocument();// Create the XML Declaration, and append it to XML document
        XmlDeclaration dec = doc.CreateXmlDeclaration("1.0", null, null);
        doc.AppendChild(dec);// Create the root element
        XmlElement root = doc.CreateElement("Command");
        doc.AppendChild(root);

        try
        {
            while (dtr.Read())
            {
                // outer INV
                XmlElement invRec = doc.CreateElement("INV");

                // Line ID
                lineId = dtr["line_id"].ToString();
                XmlElement _lineId = doc.CreateElement("line_id");
                _lineId.InnerText = lineId;
                invRec.AppendChild(_lineId);

                // Ref Num
                refNum = dtr["ref_no"].ToString();
                XmlElement _refNum = doc.CreateElement("ref_no");
                _refNum.InnerText = refNum;
                invRec.AppendChild(_refNum);

                . . .

                // New Item
                newItem = dtr["new_item"].ToString();
                XmlElement _new_item = doc.CreateElement("new_item");
                _new_item.InnerText = newItem;
                invRec.AppendChild(_new_item);

                root.AppendChild(invRec);
            }
        }
        finally
        {
            xmlOutput = doc.OuterXml;
            dtr.Close();
            if (sqlceCmd.Connection.State == ConnectionState.Open)
            {
                sqlceCmd.Connection.Close();
            }
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(String.Format("inner ex is {0}", ex.InnerException.ToString()));
        SSCS.ExceptionHandler(ex, "frmCentral.getDataAsXMLFromTable()");
    }
    return xmlOutput;
} //getDataAsXMLFromTable

Note: With this code, I see:

0) "connstr is Data Source="\My Documents\HHSDB003.SDF"; qry is SELECT * FROM INV12262006091415"
1) "Made it just before conn.open"
2) "Will try to open"
3) "Exception: Null Reference Exception"

INV12262006091415 does exist in HHSDB003.SDF

UPDATE 9

I tried wrapping getDataAsXMLFromTable() in a lock:

lock (this)
{
    . . .
}

That didn't help; neither did this:

public class HHSUtils
{       
    public static bool InGetDataAsXMLFromTable;

private String getDataAsXMLFromTable(String tableName, String siteNum)
{
    if (HHSUtils.InGetDataAsXMLFromTable) return String.Empty;
    HHSUtils.InGetDataAsXMLFromTable = true;
    . . .
    HHSUtils.InGetDataAsXMLFromTable = false;
    return xmlOutput;
}

Bountifying for 100 now.

UPDATE 10

See Update 3 here: How can I circument the "80004005 There is a file sharing violation...." err msg on a SQL Server CE database?

回答1:

You forgot to open the connection.

string connString = "Data Source=\"\\My Documents\\HHSDB003.sdf\"";
string query = "SELECT * FROM MyTable";

SqlCeConnection conn = new SqlCeConnection(connString);
SqlCeCommand cmd = new SqlCeCommand(query, conn);

conn.Open();  // <--- THIS
SqlCeDataReader rdr = cmd.ExecuteReader();

try
{
    // Iterate through the results
    while (rdr.Read())
    {
    }
}
finally
{
    // Always call Close when done reading
    rdr.Close();
    // Always call Close when done reading
    conn.Close();
}


标签： sql-server-ce compact-framework windows-ce executereader sqlcedatareader
举报

收藏的人(0)

Ta的文章 更多文章
登录 后发表评论
0条评论
还没有人评论过~