sql server -在c#中只获得输出参数的第一个字符
本文关键字:输出 参数 字符 第一个 server sql | 更新日期: 2023-09-27 17:49:40
我已经定义了如下所示的输出参数:
c#:scom.Parameters.Add("@User_ID",SqlDbType.VarChar,8).Direction = ParameterDirection.Output ;
Sql: @User_ID varchar(8) output
我在Sql Server中执行过程时得到完整的字符串,但在c#中只得到第一个字符。我搜索了很多,并确保在c#和Sql中都定义了大小。甚至我尝试了固定长度的字符(Char(8)),但仍然只得到c#中的第一个字符。请让我知道有什么问题。
c#代码:public bool CheckPhone(string phoneNumber)
{
SqlConnection myconn=new SqlConnection(connectionString);
try
{
myconn.Open();
SqlCommand scom = new SqlCommand("AuthenticatePhone", myconn);
scom.CommandType = CommandType.StoredProcedure;
scom.Parameters.Add("@phoneNumber", SqlDbType.BigInt).Value = Convert.ToInt64(phoneNumber);
scom.Parameters.Add("@User_ID", SqlDbType.Char, 8).Direction = ParameterDirection.Output;
scom.Parameters.Add("@User_Name", SqlDbType.Char, 120).Direction = ParameterDirection.Output;
scom.ExecuteNonQuery();
if (scom.Parameters["@User_Name"] == null)
{
return false;
}
else
{
UserID = (string)scom.Parameters["@User_ID"].Value;//.ToString();
UserName = (string)scom.Parameters["@User_Name"].Value;//.ToString();
myconn.Close();
return true;
}
}
catch (Exception e)
{
string error = e.InnerException + e.Message;
}
finally
{
myconn.Close();
}
return false;
}
Sql: Create procedure dbo.AuthenticatePhone
@phoneNumber numeric(11,0) ,
@User_ID varchar(8) output ,
@User_Name varchar(120) output
as
begin
Select @User_ID = convert(varchar(8),[User_ID]) ,
@User_Name = [User_Name]
from dbo.NRE_Users
where PhoneNumber = @phoneNumber
;
print @User_ID
print @User_Name
end
尝试使用下面的代码:
Parameter[param number].Size = 200;
不适合我。给定这个存储过程:
create procedure dbo.AuthenticatePhone
@phoneNumber numeric(11,0) ,
@User_ID varchar(8) output ,
@User_Name varchar(120) output
as
set @User_ID = '1234567890' -- should be truncated to '12345678'
set @User_Name = 'The quick brown fox jumped over the lazy dog.'
return 0
go
在SSMS中运行此SQL:
declare @userId varchar(1000) = 'xxx'
declare @userName varchar(1000) = 'yyy'
exec AuthenticatePhone 1 , @User_ID = @userId out , @User_Name = @userName out
select @userId,@userName
得到预期结果:
-
@userId
包含预期的12345678
-
@userName
包含预期的The quick brown fox jumped over the lazy dog.
通过c#执行:
using ( SqlConnection connection = new SqlConnection( "Server=localhost;Database=sandbox;Trusted_Connection=True;" ) )
using ( SqlCommand command = connection.CreateCommand() )
{
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "dbo.AuthenticatePhone" ;
SqlParameter phoneNumber = new SqlParameter {
ParameterName = "@phoneNumber" ,
IsNullable = true ,
Direction = ParameterDirection.Input ,
Value = 2125551212L ,
} ;
command.Parameters.Add( phoneNumber ) ;
SqlParameter userId = new SqlParameter {
ParameterName = "@User_ID" ,
IsNullable = true ,
Direction = ParameterDirection.Output ,
DbType = DbType.String ,
Size = 1000 ,
Value = DBNull.Value ,
} ;
command.Parameters.Add( userId ) ;
SqlParameter userName = new SqlParameter {
ParameterName = "@User_Name" ,
IsNullable = true ,
Direction = ParameterDirection.Output ,
DbType = DbType.String ,
Size = 1000 ,
Value = DBNull.Value ,
} ;
command.Parameters.Add( userName ) ;
connection.Open() ;
int rowsAffected = command.ExecuteNonQuery() ;
connection.Close() ;
Console.WriteLine( "Rows Affected: {0}" , rowsAffected ) ;
Console.WriteLine( "User ID: {{{0}}}" , userId.Value as string ) ;
Console.WriteLine( "User Name: {{{0}}}" , userName.Value as string ) ;
}
同样会产生预期的
Rows Affected: -1
User ID: {12345678}
User Name: {The quick brown fox jumped over the lazy dog.}
甚至替换你的参数定义:
command.Parameters.Add("@phoneNumber", SqlDbType.BigInt).Value = 2125551212L ;
command.Parameters.Add("@User_ID", SqlDbType.Char, 8).Direction = ParameterDirection.Output;
command.Parameters.Add("@User_Name", SqlDbType.Char, 120).Direction = ParameterDirection.Output;
你得到了预期的结果。
虽然你可能注意到SqlDbType.Char
是一个SQL char(X)
。它相当于T-SQL中的' convert(char(120),'John Doe')。. net字符串将以指定长度的空格填充结束。
您可以考虑将类型说明符更改为SqlDbType.VarChar
:它将匹配存储过程和中的参数声明,您将不会发现自己需要从字符串中删除尾部空白以使其有用。
change SqlDbType.Char
to SqlDbType.VarChar
:
scom.Parameters.Add("@User_ID", SqlDbType.Char, 8).Direction = ParameterDirection.Output;
scom.Parameters.Add("@User_Name", SqlDbType.Char, 120).Direction = ParameterDirection.Output;
据我所知。net中的字符只有一个字符长。因此需要将参数类型更改为varchar
scom.Parameters.Add("@User_ID", SqlDbType.Varchar, 8).Direction = ParameterDirect