//有参存储过程
//使用SqlParameter传值可以避免SQL注入
var nameParam = new System.Data.SqlClient.SqlParameter
{
ParameterName = "@name",
Value = "张三"
};
var List = db.Database.SqlQuery<实体名>("exec 存储过程名 @name", nameParam );
//有参存储过程且有返回值
//存储过程
Create PROCEDURE proc_testEF
@id int,
@voteCount int OUTPUT --返回值
BEGIN
SELECT @voteCount = COUNT(*)
FROM ConfirmItem
WHERE ConfirmItemID = @id;
select * from ConfirmItem where ConfirmItemID=@id;
//代码实现
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;
多个参数最好进行代码封装
/// <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;
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();
ExecuteSqlCommand() 增、删、改 返回影响行数
//无参存储过程
var ChangeRows= db.Database.ExecuteSqlCommand("exec 存储过程名");
//有参存储过程
//使用SqlParameter传值可以避免SQL注入
var nameParam = new System.Data.SqlClient.SqlParameter
{
ParameterName = "@name",
Value = "张三"
};
var ChangeRows = db.Database.ExecuteSqlCommand("exec 存储过程名 @name", nameParam);
//sql语句
var ChangeRows = db.Database.ExecuteSqlCommand(" 可执行的sql语句 ");
