asp.net-mvc-3 - 使用函数从表中返回一个值

Question

我有这个代码:

public int GetUserIdByEmail(string email)
{
  using (SqlConnection conn = new SqlConnection(ZincModelContainer.CONNECTIONSTRING))
  {
    using (SqlCommand cmd = conn.CreateCommand())
    {
      conn.Open();
      cmd.CommandType = System.Data.CommandType.Text;
      cmd.CommandText = String.Concat("SELECT [Zinc].[GetUserIdByEmail] (", email, ")");  //is this correct??? the problem lies here

      return (int)cmd.ExecuteScalar();
     } 
  }
}

我在上面的代码中得到错误。这仍然不对
我现在有我的功能，如下面由 veljasije 建议的
谢谢

Answer 1

修改您的程序:

CREATE PROCEDURE [Zinc].[GetUserIdByEmail]
 (
   @Email varchar (100)
 )

 AS
 BEGIN

   SELECT zu.UserId from Zinc.Users zu WHERE Email = @Email

 END

并在您的代码中更改来自 NVarChar 的参数类型至 VarChar
功能

CREATE FUNCTION [Zinc].[GetUserIdByEmail]
 (
   @Email varchar(100)
 )
 RETURNS int
 AS
 BEGIN

   DECLARE @UserId int;
   SET @UserId = (SELECT zu.UserId from Zinc.Users zu WHERE Email = @Email)

   RETURN @UserId                
 END