CRUD 3内部联接表干净地与SQL Server [英] CRUD 3 Inner Joined Tables Cleanly with SQL Server

问题描述

我正在使用Identity（2.2）一个ASP.NET项目。的事情之一，我试图放在一起是数据网格，其中用户和角色将被加载到一个DataGrid，其中CRUD命令可以运行。我没有问题，存储select语句：

I'm working on a ASP.NET project using Identity (2.2). One of the things I was trying to put together was datagrid where the Users and Roles would be loaded onto one datagrid where CRUD commands could be run. I had no problem storing the select statement:

CREATE PROCEDURE [dbo].SelectUsersRoles
AS
    SET NOCOUNT ON;

    SELECT 
        p.Id, p.Email, p.PhoneNumber, p.LockoutEnabled, p.UserName, 
        p.AspNetUserRole, AspNetRoles.Name 
    FROM 
        AspNetUsers AS p 
    INNER JOIN 
        AspNetUserRoles ON p.Id = AspNetUserRoles.UserId 
    INNER JOIN 
        AspNetRoles ON AspNetUserRoles.RoleId = AspNetRoles.Id
GO

而这个选择数据，我可以加载电网就好了。审查这个问题 后，和<一个href=\"http://stackoverflow.com/questions/13074765/tadoquery-join-tables-insert-delete-records-from-result?s=1%7C1.6577\">this 之一，我被留下的想法是正确的更新code应该是：

And this selects the data and I can load the grid just fine. After reviewing this question, and this one, I was left with the idea that the correct update code should have been:

CREATE PROCEDURE [dbo].UpdateUserRoles
(
    @Email nvarchar(256),
    @UserName nvarchar(256),
    @PhoneNumber nvarchar(MAX),
    @LockoutEnabled bit,
    @original_ID nvarchar(128)
)
AS
    SET NOCOUNT ON;

    UPDATE p
    SET p.Email = @Email, 
        p.Username = @UserName, 
        p.PhoneNumber = @PhoneNumber, 
        p.LockoutEnabled = @LockoutEnabled
    FROM [AspNetUsers] AS p
    INNER JOIN [AspNetUserRoles] AS r ON  p.ID = r.UserID
    INNER JOIN [AspNetRoles] AS b ON r.RoleID = b.ID
    WHERE p.ID = @original_ID
    GO

如果我尝试使用查询生成器，我注意到VS'13自动添加一个 CROSS JOIN 语句到它：[一般使得它的可读性]

If I try to use the Query Builder, I notice VS'13 automatically adds a CROSS JOIN statement to it: [and generally makes it less readable]

UPDATE p
SET p.Email = @Email, p.UserName = @UserName, 
    p.PhoneNumber = @PhoneNumber, p.LockoutEnabled = @LockoutEnabled
FROM            
    AspNetUsers AS p 
INNER JOIN
    AspNetUserRoles AS r ON p.Id = r.UserId 
INNER JOIN
    AspNetRoles AS b ON r.RoleId = b.Id 
CROSS JOIN
    p
WHERE
    (p.Id = @original_ID)

和这个不断抛出的P是一个无效的对象错误，当我尝试运行查询。

And this keeps throwing the error that 'p' is an invalid object when I try to run the query.

有没有一些简单的我失踪，或者这会是更好地分割成连续的更新命令？的最终目标是与分配给用户的角色沿着更新两个用户信息（即电话号码）。

Is there something simple I'm missing, or would this be better to split into consecutive update commands? The end goal is to update both the user information (ie phone number) along with the role assigned to the user.

正好有数据库问题成立，我想你可以启动任何新的MVC /网络从Visual Studio表单模板，使一些用户把一些角色。

Just to have the DB set up in question, I think you could start any new MVC/Web Forms template from Visual Studio, make a few users and put in some roles.

推荐答案

请卸下左侧表别名 = 直属更新P设定。这是隐含的，是你错误的原因。

Please remove the table aliases on the left side of the = directly under the UPDATE P SET. This is implied and is the cause of your errors.

UPDATE p SET
    Email = @Email, 
    Username = @UserName, 
    PhoneNumber = @PhoneNumber, 
    LockoutEnabled = @LockoutEnabled
FROM [AspNetUsers] AS p
    INNER JOIN [AspNetUserRoles] AS r
        ON  p.ID = r.UserID
    INNER JOIN [AspNetRoles] AS b
        ON r.RoleID = b.ID
WHERE p.ID = @original_ID

注：没有理由加入 - 假设这些都是因为你的查询生成器的。你只需要在下面在存储过程。

Note: There is no reason for the joins - assuming these are because of your query builder. You just need below in your stored proc.

UPDATE AspNetUsers SET
    Email = @Email, 
    Username = @UserName, 
    PhoneNumber = @PhoneNumber, 
    LockoutEnabled = @LockoutEnabled
WHERE ID = @original_ID

这篇关于CRUD 3内部联接表干净地与SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！