网上有很多关于Entity 存储过程返回标量值的文章,千篇一律,都是拷贝,不知道有人用过没有。
今天偶尔做了一下,发现有一个问题
原文代码和存储过程如下:
存储过程:
CREATE PROCEDURE [dbo].[GetNameByCustomerId]
@CustomerId varchar(5),
@ContactName varchar(30) output
AS
BEGIN
SET NOCOUNT ON;
SELECT @ContactName=ContactName
FROM Customers
WHERE CustomerID=@CustomerId;
END
部分类:
public void ExecuteNonQuery(string functionName, System.Data.EntityClient.EntityParameter[] parameters)
{
System.Data.EntityClient.EntityCommand cmd = ((System.Data.EntityClient.EntityConnection)this.Connection).CreateCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddRange(parameters);
cmd.CommandText = this.DefaultContainerName + "." + functionName;
try
{
if (cmd.Connection.State != System.Data.ConnectionState.Open)
cmd.Connection.Open();
cmd.ExecuteNonQuery();
}
catch (System.Exception)
{
throw;
}
finally
{
cmd.Connection.Close();
}
}
调用:
string s;
GetNameByCustomerId("VINET",outs);
Response.Write(s);
发生如下错误
原因是存储过程执行结果是一个无列名的记录,所以报错,因此,修改存储过程为
ALTER PROCEDURE [dbo].[GetNameByCustomerId]
@CustomerId varchar(5),
@ContactName varchar(30) output
AS
BEGIN
SET NOCOUNT ON;
SELECT @ContactName=ContactName
FROM Customers
WHERE CustomerID=@CustomerId;
SELECT @ContactName as N'@ContactName';
END
红色部分是取列名。
再次调试通过
今天偶尔做了一下,发现有一个问题
原文代码和存储过程如下:
存储过程:
CREATE PROCEDURE [dbo].[GetNameByCustomerId]
@CustomerId varchar(5),
@ContactName varchar(30) output
AS
BEGIN
SET NOCOUNT ON;
SELECT @ContactName=ContactName
FROM Customers
WHERE CustomerID=@CustomerId;
END
部分类:
public void ExecuteNonQuery(string functionName, System.Data.EntityClient.EntityParameter[] parameters)
{
System.Data.EntityClient.EntityCommand cmd = ((System.Data.EntityClient.EntityConnection)this.Connection).CreateCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddRange(parameters);
cmd.CommandText = this.DefaultContainerName + "." + functionName;
try
{
if (cmd.Connection.State != System.Data.ConnectionState.Open)
cmd.Connection.Open();
cmd.ExecuteNonQuery();
}
catch (System.Exception)
{
throw;
}
finally
{
cmd.Connection.Close();
}
}
调用:
string s;
GetNameByCustomerId("VINET",outs);
Response.Write(s);
发生如下错误
原因是存储过程执行结果是一个无列名的记录,所以报错,因此,修改存储过程为
ALTER PROCEDURE [dbo].[GetNameByCustomerId]
@CustomerId varchar(5),
@ContactName varchar(30) output
AS
BEGIN
SET NOCOUNT ON;
SELECT @ContactName=ContactName
FROM Customers
WHERE CustomerID=@CustomerId;
SELECT @ContactName as N'@ContactName';
END
红色部分是取列名。
再次调试通过