存储过程在managementstudio中返回值,但在c#代码中不返回值
本文关键字:返回值 代码 但在 存储过程 managementstudio | 更新日期: 2023-09-27 18:27:22
我有一个存储过程,它使用链接服务器(ADSI)连接到active directory。它接受一个字符串/vargar guid,然后返回活动目录外用户的thumbnailPhoto属性。我试图从C#代码调用这个过程,但它一直返回null。我试图确保传递的guid是正确的,所以从调试开始,我将传递的guid获取到参数中,然后尝试使用它在management studio中运行存储过程。结果很好,但当我从应用程序中进行操作时,我会得到null。这是代码:
存储过程:
ALTER PROC [dbo].[sp_GetPhotoFromADSI]
@GUID varchar(100)
AS
SET NOCOUNT ON
SET XACT_ABORT ON
BEGIN TRAN
DECLARE @sql NVARCHAR(600)
SET @sql=N'SELECT thumbnailPhoto FROM OPENQUERY(ADSI,
''SELECT thumbnailPhoto
FROM ''''LDAP://<GUID=' + @GUID + '>''''
WHERE objectCategory = ''''Person'''' AND objectClass = ''''user'''''')'
EXEC SP_EXECUTESQL @sql
COMMIT
C#:
private byte[] GetUserPhotoByGuid(Guid userNativeGuid)
{
//dont judge me for not abstracting this
var connString = "connectionStringCrap;";
byte[] photo = null;
using (var conn = new SqlConnection(connString))
{
try
{
string guidString = userNativeGuid.ToString();
//var realGuid = Guid.Parse(userNativeGuid);
using (var comm = new SqlCommand("sp_GetPhotoFromADSI", conn))
{
comm.CommandType = CommandType.StoredProcedure;
comm.Parameters.AddWithValue("@GUID", guidString);
var returnParameter = comm.Parameters.Add("@Output", SqlDbType.Binary);
returnParameter.Direction = ParameterDirection.ReturnValue;
conn.Open();
comm.ExecuteScalar();
photo = returnParameter.Value as byte[];
return photo;
}
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
}
return null;
}
该值将由ExecuteScalar()
返回。
Byte[] photo = (Byte[])comm.ExecuteScalar();
您可能想要ExecuteScalar返回的结果。但请记住,它最多只能返回2033辆克莱斯勒汽车。