使用mvc4 razor从存储过程中获取多个输出

I am using a stored procedure with mvc4 razor to get two values. I have used below code to get userid and user type. Now how could I make it to return both userid and usertype from stored procedure?

我正在使用mvc4 razor的存储过程来获取两个值。我使用下面的代码来获取用户ID和用户类型。现在我怎么能让它从存储过程返回userid和usertype?

logintable.cs

public partial class Userlogintable
{
     public int UserID { get; set; }
     public string Usertypename { get; set; }
     public int Usertype { get; set; }
     public string FullName { get; set; }
     public string Username { get; set; }
     public string Password { get; set; }
 }

homecontroller .cs

[HttpPost]
[AllowAnonymous]
[ValidateAntiForgeryToken]
public ActionResult Login(logintable u)
{
        loginEntitiesdb = new loginEntities();
        int a = db.splogindata(u.Username, u.Password).First().Value;
 //a returns only usertype
        return View(p);
}

Stored procedure:

ALTER PROCEDURE [dbo].[splogindata]
    @Username varchar(30),
    @Password varchar(30)
AS
    declare @Userroletype int
BEGIN
    SET NOCOUNT ON;

    SELECT 
        fullname, userid, usertype 
    FROM
        logintable 
    WHERE
        Username = @Username   
        AND Password = @Password
END

Here a returns only usertype which I get from stored procedure. And logintable contains fullname, userid, usertype, password and username columns.

这里只返回我从存储过程中获得的usertype。 logintable包含fullname,userid,usertype,password和username列。

How could I get usertype and userid from table so that values can be stored in parameters and used to pass it to other function.

如何从表中获取usertype和userid,以便可以将值存储在参数中并用于将其传递给其他函数。

1 个解决方案

#1

Create a model to represent the data you want to return from the stored procedure:

创建一个模型来表示要从存储过程返回的数据:

public class MyReturnModel
{
    public int UserId { get; set; }
    public int UserType { get; set; }
}

Then call your stored procedure and use ObjectContext.Translate to map the results to your model:

然后调用存储过程并使用ObjectContext.Translate将结果映射到模型:

public MyReturnModel DoLogin(logintable u)
{
   using (var db = new MyContext())
   {
       var cmd = db.Database.Connection.CreateCommand();
       cmd.CommandType = CommandType.StoredProcedure;
       cmd.CommandText = "splogindata";

      var u = cmd.CreateParameter();
      u.DbType = DbType.String;
      u.ParameterName = "@Username";
      u.Value = u.Username;
      cmd.Parameters.Add(u);

      var p = cmd.CreateParameter();
      p.DbType = DbType.String;
      p.ParameterName = "@Password";
      p.Value = u.Username;
      cmd.Parameters.Add(p);


      db.Database.Connection.Open();
      var reader = cmd.ExecuteReader();
      //  return list as ObjectList
      var results = ((IObjectContextAdapter) db).ObjectContext.Translate<MyReturnModel>(reader);
      //  cast to entity
      var returnModel = (from s in results select s).FirstOrDefault();

      db.Database.Connection.Close();
   }

   return returnModel;
}

This is a simplified example and in reality, you'd have a try...catch around the call to Open, and the close would be in a finally block.

这是一个简化的例子,实际上,你有一个尝试...赶上Open的调用,关闭将在finally块中。

This approach can be used to return multiple record sets (using to ToList() instead of FirstOrDefault), and is also very useful for returning multiple result sets which would otherwise require multiple database calls.

此方法可用于返回多个记录集(使用ToList()而不是FirstOrDefault),并且对于返回多个结果集非常有用,否则这些结果集将需要多个数据库调用。

UPDATE

To return all the columns from the login table, you need to add the extra properties the 'mapping' model:

要返回登录表中的所有列,您需要添加“映射”模型的额外属性:

public string Usertypename { get; set; }
public string FullName { get; set; }
public string Username { get; set; }
public string Password { get; set; }

Obviously you would update the SELECT statement in to stored procedure to match.

显然,您会将SELECT语句更新到存储过程以进行匹配。

#1