因此,我正在使用存储过程将新用户添加到使用C#的测试MVC应用程序中的DB。
我一直遇到错误,我认为这是由于我将新添加的用户的ID返回到数据库的事实,评论代码什么也没做:(或更确切地说给我带来了相同的错误但是抱怨不同的变量。
有趣的是,它成功地添加了一个用户,除了抛出异常。
我仍然想将ID返回到调用程序中,我仍然想使用参数化查询。
代码下面:
#region AddUser
public static bool AddUser(Models.User user)
{
bool result = false;
Models.UserRegistrationPasswordsEntities1 db = new Models.UserRegistrationPasswordsEntities1();
var queryResult = db.Users.SqlQuery(@"EXECUTE uspAddUser @1, @2, @3, @4, @5, @6",
new SqlParameter("1", user.userFirstName),
new SqlParameter("2", user.userLastName),
new SqlParameter("3", user.userName),
new SqlParameter("4", user.userEmail),
new SqlParameter("5", user.userPassword),
new SqlParameter("6", user.userDateOfBirth)).Single();
// the ternary opertor is pretty awesome
result = queryResult == null ? false : true;
return result;
}
#endregion
model.user下面:
public partial class User
{
public int userID { get; set; }
[Display(Name = "First Name")]
[DataType(DataType.Text)]
[Required(AllowEmptyStrings = false, ErrorMessage ="First name required")]
public string userFirstName { get; set; }
[Display(Name = "Last Name")]
[DataType(DataType.Text)]
[Required(AllowEmptyStrings = false, ErrorMessage = "Last name required")]
public string userLastName { get; set; }
[Display(Name = "Username")]
[DataType(DataType.Text)]
[Required(AllowEmptyStrings = false, ErrorMessage = "Username required")]
public string userName { get; set; }
[Display(Name = "Email")]
[DataType(DataType.EmailAddress)]
[Required(AllowEmptyStrings = false, ErrorMessage = "email is required")]
public string userEmail { get; set; }
[Display(Name = "Date Of Birth")]
[DataType(DataType.DateTime)]
[Required(AllowEmptyStrings = false, ErrorMessage = "Date of Birth is required")]
[DisplayFormat(ApplyFormatInEditMode = true, DataFormatString = "{0:MM/dd/yyyy}")]
public DateTime userDateOfBirth { get; set;}
[Display(Name = "Password")]
[DataType(DataType.Password)]
[Required(AllowEmptyStrings = false, ErrorMessage = "Password is required")]
[MinLength(6, ErrorMessage = "Minimum of 6 characters required")]
public string userPassword { get; set; }
[Display(Name = "Confirm Password")]
[DataType(DataType.Password)]
[Required(AllowEmptyStrings = false, ErrorMessage = "Confirm password is required")]
[Compare("userPassword", ErrorMessage = "Confirm password and password do not match")]
public string userConfirmPassword { get; set; }
public bool userStatus { get; set; }
public bool userEmailVerificationStatus { get; set; }
public System.Guid userActivationCode { get; set; }
}
错误下面:
这是我收到的错误,该错误已在存储过程中注释,如下所示。如果我不使用 userFirstName
数据读取器与指定的" userRegistrationpassmodel.user"不兼容。类型"用户ID"的成员在数据读取器中没有相同名称的相应列。
创建表下:
CREATE TABLE [dbo].[Users] (
[userID] INT IDENTITY (1, 1) NOT NULL,
[userFirstName] VARCHAR (50) NOT NULL,
[userLastName] VARCHAR (50) NOT NULL,
[userName] VARCHAR (50) NOT NULL,
[userEmail] VARCHAR (50) NOT NULL,
[userPassword] VARCHAR (255) NOT NULL,
[userStatus] BIT DEFAULT ((0)) NOT NULL,
[userEmailVerificationStatus] BIT DEFAULT ((0)) NOT NULL,
[userActivationCode] UNIQUEIDENTIFIER DEFAULT (newid()) NOT NULL,
[userDateOfBirth] DATETIME NOT NULL,
PRIMARY KEY CLUSTERED ([userID] ASC)
);
存储过程以下:
请注意,底部的选择被评论出来,因为我认为可能将ID返回到呼叫程序是问题所在。在我的最终解决方案中,如果可能的话,我仍然希望以这种方式将ID返回到调用程序中。
CREATE PROCEDURE uspAddUser
@userFirstName VARCHAR(50)
,@userLastName VARCHAR(50)
,@userName VARCHAR(50)
,@userEmail VARCHAR(50)
,@userPassword VARCHAR(100)
,@userDateOfBirth DATETIME
AS
SET NOCOUNT ON -- Report Only Errors
SET XACT_ABORT ON -- Rollback transaction on error
BEGIN TRANSACTION
DECLARE @userID INTEGER
-- CREATE new record
INSERT INTO Users(userFirstName, userLastName, userName, userEmail, userPassword, userStatus, userEmailVerificationStatus, userDateOfBirth)
VALUES(@userFirstName, @userLastName, @userName, @userEmail, @userPassword, 0 ,0, @userDateOfBirth) -- 1 = Active
---- return ID to calling program
--SELECT UserID FROM Users WHERE userFirstName = @userFirstName AND
-- userLastName = @userLastName AND
-- userName = @userName AND
-- userEmail = @userEmail
COMMIT TRANSACTION
资源我访问了以下搜索解决方案:
异常:使用实体框架executestorequery函数执行插入存储过程
https://blogs.msdn.microsoft.com/diego/2012/01/09/stored-procedures-with-with-enput-parameters-using-sqlquery-sqlquery-in-the-the-dbcontext-api/
从存储过程中获取返回值
使用实体框架,我应该在存储过程中使用返回还是选择?
任何帮助都非常感谢,谢谢。
作为一般规则,在使用SELECT
语句使用SqlQuery
方法(而不是单个属性(时,您需要返回实体的完整部分。将存储过程中的SELECT
语句更改为:
SELECT TOP 1 * FROM Users
WHERE userFirstName = @userFirstName AND userLastName = @userLastName
AND userName = @userName AND userEmail = @userEmail
然后将其执行到queryResult
:
var queryResult = db.Users.SqlQuery(@"EXECUTE uspAddUser @1, @2, @3, @4, @5, @6",
new SqlParameter("1", user.userFirstName),
new SqlParameter("2", user.userLastName),
new SqlParameter("3", user.userName),
new SqlParameter("4", user.userEmail),
new SqlParameter("5", user.userPassword),
new SqlParameter("6", user.userDateOfBirth)).Single();
如果您想从AddUser
方法获得userID
,则可以从上面的查询结果调用它(并将返回类型更改为int
而不是bool
(:
int userId = queryResult.userID;
return userId;
旁注:
由于userID
称为主要密钥身份列,请将KeyAttribute
和DatabaseGeneratedAttribute
设置为EF模型类中的主要键:
public partial class User
{
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int userID { get; set; }
// other properties
}
类似的问题:
在数据读取器中没有相同名称