//学生控制器
public class Student
{
[Key]
public int StudentId { get; set; }
public string Name { get; set; }
public string UserName { get; set; }
public string Password { get; set; }
public string Emial { get; set; }
public string Role { get; set; }
}//StudentConroller (这里我调用了存储过程)
public class AccountController : Controller
{
public ApplicationDbClass applicationDbClass;
public AccountController()
{
applicationDbClass = new ApplicationDbClass();
}
public ActionResult Login()
{
return View();
}
[HttpPost]
public ActionResult Login(Student student)
{
var v1 = new SqlParameter();
v1.ParameterName = "@role";
v1.SqlDbType = SqlDbType.NVarChar;
v1.Value = "Admin";
var v2 = new SqlParameter();
v2.ParameterName = "@count";
v2.SqlDbType = SqlDbType.Int;
try
{
var result = applicationDbClass.Students.SqlQuery("StudentProcedure @role,@count OUT", v1, v2).ToArray();
}
catch(Exception e)
{
var m = e.Message;
}
return RedirectToAction("Welcome", "Student");
}
}//存储过程
CREATE OR ALTER PROCEDURE StudentProcedure
@role NVARCHAR(30),
@count INT OUTPUT
AS
BEGIN
SELECT @count=COUNT(dbo.Students.Role)
FROM dbo.Students
WHERE Role=@role;
END//DbContext类
public class ApplicationDbClass : DbContext
{
public ApplicationDbClass() : base()
{
Database.SetInitializer<ApplicationDbClass>(new DropCreateDatabaseIfModelChanges<ApplicationDbClass>());
}
public DbSet<Student> Students { get; set; }
public DbSet<LogTable> LogTables { get; set; }
}//这里我使用代码优先的方法来处理数据库,使用实体框架来调用用户创建的存储过程。如果我在存储过程上做了一些更改,它将不会被直接反射。请给我任何解决方案,以反映变化。
发布于 2019-01-14 20:56:23
您也可以通过这种方式传递参数
SqlConnection cnn = new SqlConnection(cnnString);
SqlCommand cmd = new SqlCommand("StudentProcedure", cnn);
cmd.Connection = cnn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@role", "Admin");
cmd.Parameters.Add("@count", SqlDbType.Int);
cmd.Parameters["@count"].Direction = ParameterDirection.Output;
string Query = cmd.ExecuteNonQuery().ToString();这里我的目标是展示如何将normal和output参数传递给过程。
发布于 2019-01-14 21:34:54
您的代码中有两个问题。
您应该为output参数设置默认值。否则,如果您没有为输出参数设置默认值,C#代码将引发异常。因此,您的存储过程应该如下所示:
ALTER PROCEDURE StudentProcedure
@role NVARCHAR(30),
@count INT = NULL OUTPUT
AS
BEGIN
SELECT @count=COUNT(dbo.Students.Role)
FROM dbo.Students
WHERE Role=@role;
SELECT @count;
END
GO第二个问题是您忘记设置输出参数的Direction:
var sqlParameter_Role = new SqlParameter("@role", "Admin");
var sqlParameter_Count = new SqlParameter();
sqlParameter_Count.ParameterName = "@count";
sqlParameter_Count.SqlDbType = SqlDbType.Int;
sqlParameter_Count.Direction = ParameterDirection.Output;
var result = db.Database
.SqlQuery<ResultForStudentProcedure>("dbo.StudentProcedure @role"
, sqlParameter_Role
, sqlParameter_Count)
.ToList();
public class ResultForStudentProcedure
{
public int Count { get; set; }
}https://stackoverflow.com/questions/54181474
复制相似问题