C#，sp_executesql和不正确的语法 [英] C#, sp_executesql and Incorrect Syntax

问题描述

我正在调用下面的代码.

I'm calling the code below.

在(IDataReader dr = cmd.ExecuteReader()) sql barfs上，在'CompanyUpdate'附近使用不正确的语法.

On the line (IDataReader dr = cmd.ExecuteReader()) sql barfs with an Incorrect syntax near 'CompanyUpdate'.

   using (SqlCommand cmd = new SqlCommand("CompanyUpdate"))
        {
            cmd.Parameters.Add("@CompanyID",SqlDbType.Int);
            cmd.Parameters.Add("@Description",SqlDbType.VarChar,50);
            cmd.Parameters["@CompanyID"].Value = companyid;
            cmd.Parameters["@Description"].Value = description;

            SqlConnection cn = new SqlConnection("Data Source=[datasource];Initial Catalog=dotNext;User ID=[user];Password=[password];Pooling=True;Application Name=dotNext");
            cn.Open();
            cmd.Connection = cn;
            using (IDataReader dr = cmd.ExecuteReader())
            {
                if (dr.Read())
                {
                    this.CompanyID = dr.GetInt32(0);
                }
            }
        }

我查看了sqlprofiler并注意到以下内容:

I had a look at sqlprofiler and noticed the following:

exec sp_executesql N'CompanyUpdate',N'@CompanyID int,@Description varchar(50)',@CompanyID=56,@Description='APC'

它用sp_executesql包装我的命令.我所有其他的sql命令都没有问题.

Its wrapping my command wit a sp_executesql. All my other sql commands are just executed with no issues.

所以我的问题有两个:1.为什么使用sp_executesql?2.我在做什么错了?

So my question is two fold: 1. Why is it using sp_executesql? 2. What am I doing wrong?

详细信息:sql2005，c#，vs2005

Details: sql2005, c#, vs2005

推荐答案

我注意到您尚未将CommandType设置为StoredProcedure ...我不知道这是否是造成问题的原因:

I notice that you've not set the CommandType to StoredProcedure... I don't know if that's the cause of your problem or not:

cmd.CommandType = CommandType.StoredProcedure;

我自己做过很多次，我无法数.

I've done this so many times myself I can't count.

提示以在下次引发异常时触发您的记忆:

在运行应用程序时打开SQL查询分析器.当每个命令执行时，它显示生成的SQL并在服务器端运行.如果生成的SQL以 sp_executesql 开头，后跟您的查询，则它将作为常规查询运行-即 cmd.CommandType = CommandType.Text (如果以开头)exec ，它有可能作为存储的proc运行.确保您要为要运行的查询类型生成正确的SQL.

Have SQL Query Profiler open while you're running your app. When each command executes, it shows the SQL generated and run on the server side. If the SQL generated begins with sp_executesql followed by your query then it's being run as a regular query - i.e. cmd.CommandType = CommandType.Text, if it starts with exec, chances are it's run as a stored proc. Make sure you're getting the correct SQL generated for the type of query you're trying to run.

这篇关于C#，sp_executesql和不正确的语法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！