如何查看发送到数据库的SQL后的参数已经取代了他们的占位符？ [英] How can I see the SQL sent to the database after the parameters have replaced their placeholders?

问题描述

第一个MessageBox.Show（）下面简单地表明我同样的事情作为常量字符串SQL_GET_VENDOR_ITEMS，这似乎没什么问题，但我发现，有分析查询出错。[令牌行号，令牌行偏移,,令牌错误,,] 的

有没有办法窥视SQL的内容已被添加的参数后， ;那么就应该是这样的：选择项ID，PackSize FROM VendorItems其中，vendorid ='测试'AND VendorItemID ='852963'

下面是相关代码：

 常量字符串SQL_GET_VENDOR_ITEMS =选择项ID，PackSize+ 
FROM VendorItems+ 
其中，vendorid = @VendorID和VendorItemID = @VendorItemID; 
 
串retVal的=的String.Empty; 
 checkConnection（）; 
 SqlCeCommand vendorCMD = objCon.CreateCommand（）; 
试
 {
 vendorCMD.CommandText = SQL_GET_VENDOR_ITEMS; 
 vendorCMD.Parameters.Add（@厂商ID，SqlDbType.NVarChar，10）.value的=厂商ID; 
 vendorCMD.Parameters.Add（ @VendorItemID，SqlDbType.NVarChar，19）.value的= VendorItemID; 
 
 MessageBox.Show（的String.Format（用SQL {0}，vendorCMD上到vendorCMD.ExecuteReader（）。 CommandText中））; 
 
 
 
 vendorReader.Close（）; 
} 
赶上（SqlCeException sqlceex）
 {
 MessageBox.Show（的String.Format（SqlCeException在GetValsForVendorAndItem == {0}，sqlceex.Message））; // TODO：删除
} 
终于
 {
 vendorCMD.Dispose（）; 
} 
返回retVal的; 
  

。 。

解决方案

但我几乎可以保证不会在我的工作VS2003 / .NET 1.0世界

唉唉......版本 - 看的 MSDN ：

用于SQL Server CE .NET精简框架数据提供程序不支持命名参数，将参数传递给一个SqlCeCommand调用的时候CommandType设置为文字的SQL语句。您必须使用问号（？）占位符。例如： SELECT * FROM客户WHERE客户id =

？

The first MessageBox.Show() below simply shows me the exact same thing as const string SQL_GET_VENDOR_ITEMS, which seems fine to me, but I'm getting, "There was an error parsing the query. [Token line number, Token line offset,, Token in error,,]"

Is there a way to spy on the contents of the SQL after parameters have been added; it should then be something like: "SELECT ItemID, PackSize FROM VendorItems WHERE VendorID = 'TEST' AND VendorItemID = '852963'

Here's the pertinent code:

    const string SQL_GET_VENDOR_ITEMS = "SELECT ItemID, PackSize " + 
        "FROM VendorItems " +
         "WHERE VendorID = @VendorID AND VendorItemID = @VendorItemID";

    string retVal = string.Empty;
    checkConnection();
    SqlCeCommand vendorCMD = objCon.CreateCommand();
    try 
    {
        vendorCMD.CommandText = SQL_GET_VENDOR_ITEMS;
        vendorCMD.Parameters.Add("@VendorID", SqlDbType.NVarChar, 10).Value = VendorID; 
        vendorCMD.Parameters.Add("@VendorItemID", SqlDbType.NVarChar, 19).Value = VendorItemID;

        MessageBox.Show(string.Format("Made it up to vendorCMD.ExecuteReader() with sql {0}", vendorCMD.CommandText));

. . .

        vendorReader.Close();
    } 
    catch (SqlCeException sqlceex)
    {
        MessageBox.Show(string.Format("SqlCeException in GetValsForVendorAndItem == {0}", sqlceex.Message));//TODO: Remove
    }
    finally 
    {
        vendorCMD.Dispose();
    }
    return retVal;

. . .

解决方案

but I can almost guarantee that won't work in my VS2003/.NET 1.0 world

ahhh... version - see MSDN:

The .NET Compact Framework data provider for SQL Server CE does not support named parameters for passing parameters to an SQL statement called by a SqlCeCommand when CommandType is set to Text. You must use the question mark (?) placeholder. For example: SELECT * FROM Customers WHERE CustomerID = ?

这篇关于如何查看发送到数据库的SQL后的参数已经取代了他们的占位符？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！