sqlserver & c#：关于CommandType的取值

环境：

• window10
• vs2022
• .net core 6.0
• sqlserver 2014

1. 问题来源：

在使用SqlCommand执行sql语句时，一直不明白属性CommandType的作用。
它取值如下：

• Text：表明执行的是普通sql语句；
• StoredProcedure：表明执行的是存储过程；
• TableDirect：实验时报错，不支持；

先说下我的看法，它们两个在功能上没有区别，性能上应该可以忽略，写法上会有不同，但大部分场景可以忽略该属性的取值。

为了下面测试，准备新建存储过程：

-- 使用存储过程，根据用户id获取姓名、年龄信息，并通过输出参数返回密码
create proc usp_test
@userid int =1,
@pwd varchar(50) output
as
set nocount on;
begin
	declare @age int=18
    select @userid as id,@age as age,'小明' as name
	set @pwd=CONVERT(varchar,@userid)+'_'+'123456'
end

-- 调用示例
declare @pwd varchar(50)
exec usp_test @userid=1,@pwd=@pwd output
select @pwd

效果如下：

2. ado.net 中使用Text

//<PackageReference Include="Microsoft.Data.SqlClient" Version="4.0.1" />

//使用 CommandType.Text(也可以不写，默认的)
var conn = new SqlConnection("Data Source=192.168.252.129;Initial Catalog=test;User ID=sa;Password=123456;Encrypt=True; TrustServerCertificate=True");
conn.Open();
var cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "exec usp_test @userid,@pwd output";
cmd.Parameters.Add(new SqlParameter("userid", 1));
var paraout = new SqlParameter("pwd", SqlDbType.VarChar, 50);
paraout.Direction = ParameterDirection.Output;
cmd.Parameters.Add(paraout);
cmd.CommandType = CommandType.Text;//也可以不写
var adp = new SqlDataAdapter(cmd);
var ds = new DataSet();
adp.Fill(ds);// datatable: [1	18	小明]
conn.Close();
var pwd = cmd.Parameters[1].Value;// 输出: 1_123456

此时监视到sqlserver执行：

这里监视的结果有些奇怪，应该是监视到sqlserver分析完的命令，将密码已经计算完成了。

3. ado.net 中使用StoredProcedure调用存储过程

//<PackageReference Include="Microsoft.Data.SqlClient" Version="4.0.1" />
var conn = new SqlConnection("Data Source=192.168.252.129;Initial Catalog=test;User ID=sa;Password=123456;Encrypt=True; TrustServerCertificate=True");
conn.Open();
var cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "usp_test";
cmd.Parameters.Add(new SqlParameter("userid", 1));
var paraout = new SqlParameter("pwd", SqlDbType.VarChar, 50);
paraout.Direction = ParameterDirection.Output;
cmd.Parameters.Add(paraout);
cmd.CommandType = CommandType.StoredProcedure;
var adp = new SqlDataAdapter(cmd);
var ds = new DataSet();
adp.Fill(ds);// datatable: [1	18	小明]
conn.Close();
var pwd = cmd.Parameters[1].Value;// 输出: 1_123456

此时监视到sqlserver执行：

4. 总结：

1. 当使用StoredProcedure时，我们只需要在CommandText上填写存储过程名称即可，ado.net会自动帮我们生成sql。
2. 当使用Text时，ado.net会认为CommandText上的是普通sql语句，所以需要我们自己生成sql，可以对比下两种写法的区别：

   StoredProcedure：usp_test
   Text：exec usp_test @userid,@pwd output

另外，如果我们使用Text并且使用参数的时候，ado.net生成的sql是：exec sp_executesql N'exec usp_test ......，而不是：exec usp_test......，当然，如果我们不使用参数，生成的sql自然不使用exec sp_executesql ......。