question

DIEGOFELIPEROMEROVALDES-5270 avatar image
0 Votes"
DIEGOFELIPEROMEROVALDES-5270 asked AgaveJoe edited

MS SQL Server: How can i copy data from two tables into another AFTER a user registers? (ASP.NET MVC)

Hello everyone! I have a web project in ASP.NET MVC and SQL Server 2019, my app already have a functional user registration and roles system, but i have to assign the roles in Managment Studio manually and then i can change them in a view in my app.

I have these tables:

User: PK UserID - FirstName - LastName - Email
RoleMaster: PK ID - RolName
UserRoleMapping: PK ID - FK UserID - FK RoleID

The RoleMaster table has three roles: Admin (ID 1), User (ID 2), and Guest (ID 3)

I need that everytime a new user registers in the system, the database assign its UserID with the Guest role in the UserRoleMapping table.

There's a way to complete the FK UserID and FK RoleID fields at database-level? Maybe a sql script or a trigger that copies the fields into the UserRoleMapping table after a user registration or something, since i just need the new UserID and RoleMaster ID:3 to be copied into the table.


sql-server-generaldotnet-aspnet-mvc
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Should we assume the UserID is an identity column??? If so, insert the User record to generate the UserID. Then use SCOPE_IDENTITY (Transact-SQL) to get the UserID. Next use the UserID to insert the UserRoleMapping record. You did not share how your data access layer works or what you are using but this requirement can be one SQL script or a stored procedure.

 INSERT INTO [dbo].[User] (FirstName, LastName, Email)
 VALUES('Hello', 'World', 'email@emial.com')
    
 DECLARE @Identity INT
 SELECT @Identity = SCOPE_IDENTITY()
    
 INSERT INTO [dbo].[UserRoleMapping] (UserID, RoleID)
 VALUES(@Identity, 3)

If you are using Entity Framework, then simply add a new user record. Entity Framework will add the ID to the entity, Then simply use the newly created UserId to add a new UserRoleMapping record.


0 Votes 0 ·

Hey, thank you very much for your response. But, how can i actually make this automatically? I mean, that I don't have to run the script every time a user registers

0 Votes 0 ·
AgaveJoe avatar image AgaveJoe DIEGOFELIPEROMEROVALDES-5270 ·

Keep in mind, the post tags are SQL Server and MVC. It turns out this is an Entity Framework question and you have not provided the entities. Anyway, it should be as simply as populating the User Entity which should include the UserRoleMapping navigation property then save the User entity.

All you have to do is populate a regular old C# class's properties and save.

Saving Related Data

I want to point out the UserRoleMapping does not require a separate primary key since the UserId and RoleId are unique. The way the tables are designed it is possible for a user to have duplicate roles. For example, one user with two Guest role records. This might cause unexpected behavior because I assume the code does not expect this to happen. Again, we cannot see the entities so you might have a constraint.

0 Votes 0 ·
AgaveJoe avatar image
0 Votes"
AgaveJoe answered AgaveJoe edited

Below is an example similar to your tables. I used the sample code from the following EF documentation. The example uses Sqlite and why the DbContext constructor has a file path rather than a connection string. The rest of the code illustrates how to configure the many-to many relationship in the OnModelCreating() method and the entity class structures.

Relationships
Keys

Models.cs

 using System;
 using System.Collections.Generic;
 using Microsoft.EntityFrameworkCore;
    
 namespace EFGetStarted
 {
     public class DemoDbContext : DbContext
     {
         public DbSet<User> Users { get; set; }
         public DbSet<Role> Roles { get; set; }
         public DbSet<UserRole> UserRoles { get; set; }
    
         public string DbPath { get; private set; }
    
         public DemoDbContext()
         {
             var folder = Environment.SpecialFolder.LocalApplicationData;
             var path = Environment.GetFolderPath(folder);
             DbPath = $"{path}{System.IO.Path.DirectorySeparatorChar}DemoDb.db";
         }
    
         protected override void OnModelCreating(ModelBuilder modelBuilder)
         {
             modelBuilder.Entity<UserRole>()
                 .HasKey(ur => new { ur.UserId, ur.RoleId });
    
             modelBuilder.Entity<UserRole>()
                 .HasOne(u => u.User)
                 .WithMany(ur => ur.UserRoles)
                 .HasForeignKey(u => u.UserId);
    
             modelBuilder.Entity<UserRole>()
                 .HasOne(r => r.Role)
                 .WithMany(ur => ur.UserRoles)
                 .HasForeignKey(r => r.RoleId);
         }
    
         // The following configures EF to create a Sqlite database file in the
         // special "local" folder for your platform.
         protected override void OnConfiguring(DbContextOptionsBuilder options)
             => options.UseSqlite($"Data Source={DbPath}");
     }
    
     public class User
     {
         public int UserId { get; set; }
         public string FirstName { get; set; }
         public string LastName { get; set; }
         public string Email { get; set; }
         public List<UserRole> UserRoles { get; set; }
     }
    
     public class Role
     {
         public int RoleId { get; set; }
         public string Name { get; set; }
         public List<UserRole> UserRoles { get; set; }
    
     }
    
     public class UserRole
     {
         public int UserId { get; set; }
         public User User { get; set; }
         public int RoleId { get; set; }
         public Role Role { get; set; }
     }
    
 }


EF Code first commands

 dotnet ef migrations add InitialCreate
 dotnet ef database update


Notice the code to create the user with the Guest role is simply populating the User entity, adding the entity, then saving.

 class Program
     {
         static void Main(string[] args)
         {
             SeedRoles();
    
             using (var db = new DemoDbContext())
             {
                 //Save the user and role if the user does not already exist
                 if (!db.Users.Any(u => u.Email == "email@email.com"))
                 {
                     //New user
                     User user = new User()
                     {
                         FirstName = "Hello",
                         LastName = "World",
                         Email = "email@email.com",
                         UserRoles = new List<UserRole>() { new UserRole() { RoleId = 3 } }
                     };
    
                     db.Add(user);
                     db.SaveChanges();
                 }
             }
    
             FetchUserByUsername("email@email.com");
         }
    
         public static void SeedRoles()
         {
             using (var db = new DemoDbContext())
             {
                 if (!db.Roles.Any())
                 {
                     db.Roles.Add(new Role() { Name = "Admin" });
                     db.Roles.Add(new Role() { Name = "User" });
                     db.Roles.Add(new Role() { Name = "Guest" });
                     db.SaveChanges();
                     Console.WriteLine("Created Roles");
                     foreach (var r in db.Roles)
                     {
                         Console.WriteLine($"{r.RoleId}\t{r.Name}");
                     }
                 }
             }
         }
    
         public static void FetchUserByUsername(string username)
         {
             using (var db = new DemoDbContext())
             {
                 var u = db.Users
                     .Include(ur => ur.UserRoles)
                     .ThenInclude(r => r.Role)
                     .FirstOrDefault(u => u.Email == username);
    
                 Console.WriteLine($"User: {u.FirstName} | {u.LastName} | {u.Email}");
                 Console.WriteLine("Roles:");
                 foreach (var ur in u.UserRoles)
                 {
                     Console.WriteLine($"\t{ ur.Role.Name}");
                 }
             }
         }
    
     }




5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered DIEGOFELIPEROMEROVALDES-5270 commented

This would be better done in your application. You are already saving the user to the database, just save a role as "guest" at the same time in your application.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hey Tom, thank you for your response
Well, that's what i don't know how to do, i've reading the microsoft documentation but i cannot implement what i want (that's basically what you said)

In case you know about this, i'll share you my code. This is the GetRolesForUser in the UsersRoleProvider class:

 public override string[] GetRolesForUser(string username)
         {
             using (MVC_DBEntities context = new MVC_DBEntities())
             {
                 var userRoles = (from user in context.User
                                  join roleMapping in context.UserRolesMapping
                                  on user.UserID equals roleMapping.UserID
                                  join role in context.RoleMaster
                                  on roleMapping.RoleID equals role.ID
                                  where user.EmailID == username
                                  select role.RollName).ToArray();
                 return userRoles;
             }
         }






0 Votes 0 ·