Currently , EF support for stored procedures are not perfect . The following problems :
EF does not support stored procedure returns multi-table query result set.
EF supports only return back all the fields in a table to be converted into the corresponding entities. Can not support the return portion field situation.
Although you can import properly stored procedure that returns a scalar value , but not for us to automatically generate the corresponding entity . cs code, we still can not be called directly in code or use a scalar stored procedure
EF does not directly support stored procedures Output type parameter.
some other issues .
Today we use the EF way to execute sql statement execute a stored procedure and get OutPut value.
First create a stored procedure :
Create PROCEDURE proc_testEF
(
@id int,
@voteCount int OUTPUT --返回值
)
AS
BEGIN
SELECT @voteCount = COUNT(*)
FROM ConfirmItem
WHERE ConfirmItemID = @id;
select * from ConfirmItem where ConfirmItemID=@id;
END
then write EF invoke methods :
using (DBEntities context = new DBEntities())
{
var idParam = new System.Data.SqlClient.SqlParameter
{
ParameterName = "@id",
Value = 1
};
var votesParam = new System.Data.SqlClient.SqlParameter
{
ParameterName = "@voteCount",
Value = 0,
Direction = ParameterDirection.Output
};
var results = context.Database.SqlQuery<Models.ConfirmItem>(
"proc_testEF @id, @voteCount out",
idParam,
votesParam);
var person = results.Single();
var votes = (int)votesParam.Value; //得到OutPut类型值
return votes;
}
test several times , no problem ; Finally I encapsulation method :
/// <summary>
/// 执行原始SQL命令
/// </summary>
/// <param name="commandText">SQL命令</param>
/// <param name="parameters">参数</param>
/// <returns>影响的记录数</returns>
public Object[] ExecuteSqlNonQuery<T>(string commandText, params Object[] parameters)
{
using (DBEntities context = new DBEntities())
{
var results = context.Database.SqlQuery<T>(commandText, parameters);
results.Single();
return parameters;
}
}
invocation :
var idParam = new System.Data.SqlClient.SqlParameter
{
ParameterName = "@id",
Value = 1
};
var votesParam = new System.Data.SqlClient.SqlParameter
{
ParameterName = "@voteCount",
Value = 0,
Direction = ParameterDirection.Output
};
System.Data.SqlClient.SqlParameter[] parm = {
idParam,
votesParam
};
parm = (System.Data.SqlClient.SqlParameter[])new BLL.Usual.ConfirmItemManager().ExecuteSqlNonQuery<Models.ConfirmItem>("proc_testEF @id, @voteCount out", parm);
string s = parm[1].Value.ToString();
course there are other ways , I just think this is simple and convenient , compared to adding solid model , it should be a lot easier !
没有评论:
发表评论