首页 > 代码库 > 关于ADO.NET参数化查询的提问

关于ADO.NET参数化查询的提问

最近我们的正式环境一直在报错一个异常,首先我贴出来异常信息

BLL层 捕获到了请求的url

  Net.BLL.MobileFun MobileFun.GetBusinessBidPolicy 异常

传入参数为:AucId=643129;CarId=680362;businessid:41773;

异常信息为:必须声明标量变量 "@UID"。 必须声明标量变量 "@UID"。 必须声明标量变量 "@UID"。

URL:http://*********.com/httpHandler/ForMobileServiceHandler.ashx?action=getBidPolicy&aucId=643129&carId=680362&businessid=BC440DE0D6DCA7FD&version=v3.2.8&onlineid=021d162e-ba3e-45ac-8c59-8d265522d6ef&clienttype=0

 

Dao层捕获到的异常信息

  Net.DAL.AuctionDao 位置:Net.DAL.AuctionDao,方法:GetBidPolicy,错误信息:System.Data.SqlClient.SqlException (0x80131904): 必须声明标量变量 "@UID"。 必须声明标量变量 "@UID"。 必须声明标量变量 "@UID"。 在 System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) 在 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) 在 System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) 在 System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() 在 System.Data.SqlClient.SqlDataReader.get_MetaData() 在 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) 在 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds) 在 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite) 在 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) 在 System.Data.SqlClient.SqlCommand.ExecuteScalar() 在 Net.DBUtility.MySqlHelper.ExecuteScalar(SqlConnection connection, CommandType commandType, String commandText, SqlParameter[] commandParameters) 在 Net.DBUtility.MySqlHelper.ExecuteScalar(String connectionString, CommandType commandType, String commandText, SqlParameter[] commandParameters) 在 Net.DBUtility.MySqlDataProvider.GetSingle(String SQLString, IDataParameter[] cmdParms) 在 Net.DAL.AuctionDao.GetBidPolicy(String aucId) ClientConnectionId:48c2084f-c6b0-46b5-93b9-8a59ebcd29ed

 

现在我贴出来 我这几个方法

BLL层

        public string GetBusinessBidPolicy(string AucId, string CarId, string businessid)        {            ConvertToJson ctj = new ConvertToJson();            try            {                int tag = -1;                int Offer = 0;                int price = 0;                int priceNonlocal = 0;                objAuctionDao.GetBusinessBidInfo(AucId, CarId, ref tag, ref Offer, ref  price, ref  priceNonlocal, businessid);                ctj.Append("ResId", "0");                ctj.Append("ResMsg", "获取商户出价方案和报价成功!");                ctj.Append("BidWay", tag);//0:本市;1 外迁;-1 尚未选择出价方式                ctj.Append("price", price);                ctj.Append("priceNonlocal", priceNonlocal);                ctj.Append("MyOptimizationOffer", Offer);            }            catch (Exception e)            {                ctj.Append("ResId", "1");                ctj.Append("ResMsg", "出现异常,获取商户出价方案和报价失败!");                m_objLog.Error("MobileFun.GetBusinessBidPolicy 异常    传入参数为:AucId=" + AucId + ";CarId=" + CarId + ";businessid:" + businessid + ";异常信息为:" + e.Message+"URL:"+HttpContext.Current.Request.Url);            }            return ctj.ToObject();        }

DAO层

   public void GetBusinessBidInfo(string aucId, string CarId, ref int tag, ref int Offer, ref int price, ref int priceNonlocal, string businessID = "")        {                      try            {                string busID = "";                if (string.IsNullOrEmpty(businessID))                    busID = new MemberDto().BusinessId;                else                    busID = businessID;                //-1 需要出价承诺,其他不需要车价承诺                int tag1 = GetBidPolicy(aucId);                if (tag1 != 1 && tag1 != 2 && tag1 != 4 && tag1 != 3)                {                    tag = 0; return;                }                //            _strSql = string.Format(@"SELECT TOP 1 Tag FROM tbAuction_BidPolicyNow with(nolock) WHERE AucId = {0} AND BusinessId = {1} ORDER BY Created DESC                //                                    SELECT Offer FROM tbSHD_OptimizationQuote with(nolock)  WHERE BusinessID={1} AND AucID={0}                //                                    SELECT TOP 1 ISNULL(price,0) price,ISNULL(priceNonlocal,0) priceNonlocal FROM tbAuction_Evaluate with(nolock)  WHERE businessId={1} AND carid={2}  ORDER BY created desc                //                                    ", aucId, busID, CarId);                _strSql =                        @"SELECT TOP 1 Tag FROM tbAuction_BidPolicyNow with(nolock) WHERE AucId = @AucID AND BusinessId = @UID ORDER BY Created DESC                                    SELECT Offer FROM tbSHD_OptimizationQuote with(nolock)  WHERE BusinessID=@UID AND AucID=@AucID                                     SELECT TOP 1 ISNULL(price,0) price,ISNULL(priceNonlocal,0) priceNonlocal FROM tbAuction_Evaluate with(nolock)  WHERE businessId=@UID  AND carid=@CarID  ORDER BY created desc                                    ";                SqlParameter[] par = { new SqlParameter("@AucID", aucId), new SqlParameter("@UID", businessID), new SqlParameter("@CarID", CarId) };                DataSet obj = _loacaldbRead.Query(_strSql, par);                if (obj != null && obj.Tables.Count > 0)                {                    if (obj.Tables[0].Rows.Count > 0)                    {                        tag = int.Parse(obj.Tables[0].Rows[0][0].ToString());                    }                    else                    {                        tag = -1;                    }                    if (obj.Tables[1].Rows.Count > 0)                    {                        Offer = int.Parse(obj.Tables[1].Rows[0][0].ToString());                    }                    if (obj.Tables[2].Rows.Count > 0)                    {                        price = int.Parse(obj.Tables[2].Rows[0][0].ToString());                        priceNonlocal = int.Parse(obj.Tables[2].Rows[0][1].ToString());                    }                }            }            catch (Exception e)            {                string error = string.Format("位置:{0},方法:{1},错误信息:{2}", this.GetType().ToString(),                    System.Reflection.MethodBase.GetCurrentMethod().Name, e);                _log.Error(error);                throw;            }

 

       public int GetBidPolicy(string aucId)        {        //    _strSql = string.Format("SELECT nonlocaltag FROM tbauction_baseinfo with(nolock) WHERE id = {0}", aucId);                    try            {                _strSql = "SELECT nonlocaltag FROM tbauction_baseinfo with(nolock) WHERE id = @AucID";                SqlParameter[] par = { new SqlParameter("@AucID", aucId) };                object obj = _loacaldbRead.GetSingle(_strSql, par);                if (obj == null) return -1;                return Convert.ToInt32(obj);            }            catch (Exception e)            {                string error = string.Format("位置:{0},方法:{1},错误信息:{2}", this.GetType().ToString(),                    System.Reflection.MethodBase.GetCurrentMethod().Name, e);                _log.Error(error);                throw;            }        }

 

我一直很奇怪 我用写的参数化查询语句  然后值初始化在参数数组中丢给ADO.NET执行,为什么会说我没有声明@UID这个变量呢?

而且还有一个异常 无法找到表1 

 

GetBidPolicy  这个方法中根本没有用到@UID的参数但是报错却说
Net.DAL.AuctionDao 位置:Net.DAL.AuctionDao,方法:GetBidPolicy,错误信息:System.Data.SqlClient.SqlException (0x80131904): 必须声明标量变量 "@UID"。 必须声明标量变量 "@UID"。 必须声明标量变量 "@UID"。

现在可以确定 找不到表1 的这个异常是

                    if (obj.Tables[1].Rows.Count > 0)
                    {
                        Offer = int.Parse(obj.Tables[1].Rows[0][0].ToString());
                    }

这里取 table[1].rows.count  的时候 发现dataset中 表1 null  所以抛出这个异常

那么问题来了 我的三个查询一起执行 就算没有查询到数据  dataset 中也会有三张表 即便是空表 也不能没有表

现在请教  各位 这个异常到底是什么原因产生的呢?

URL 我本地,生产环境都试过了 直接敲出来没有报错异常..

另请教 参数化的sql语句  ado.net 去数据库执行的时候 是如何把值赋给参数的 ,求详解!

 

关于ADO.NET参数化查询的提问