0

I have a little question.

Short description of the project: I'm working on a little API project written in C#. Inside this project I'm using the onion architecture, in short it's something like Controller -> Service -> Repository.

I have an Account controller, which manages create & update User and beside this I have a databas ewith the following tables:

User, Password, UserInformation

In User I have the following columns: Id(PK), userName and role.

In Password I have: User_Id(PK and FK to User - Id column), password and oldPassword (if necessary).

In UserInformation I have: User_Id(PK and FK to User - Id column), firstName, lastName and email.

As for inserting data, I have a separate class with string constants inside which are queries and I'm executing them by doing

sqlConnection.QueryAsync(QueryFromStringConstants, etcParams).

As I'm a little bit noobish with the SQL syntax and everything related to SQL in general(haha), my question is: if I create a User, how can I insert data into all 3 tables at the same time?

Thanks :)

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
C.Rus
  • 3
  • 1

3 Answers3

0

Each insert requires a separate insert statement, and you should also get an identity value after each insert in order to use it for next insert, for example:

  1. insert user: INSERT INTO User (userName, role) VALUES (...)
  2. get user Id (I'm sure you definded it as IDENTITY): DECLARE @userId AS integer = (SELECT @@IDENTITY);
  3. insert user password (with user id obtained on prevoius step)
  4. insert user info (the same)

But you can also use Entity framework. You should create class User, which have virtual properties of type Password and UserInformation (also classes you should create as well). Then create a context which has a property DbSet<User> Users. In this case you just create a User instance, populate its fields, Password and UserInformation with correspondent instances of Password and UserInformation classes. Then use:

context.Users.Add(userInstanceVar);
context.SaveChanges();

That's all! EF will save all instances into your database, and also populates all identity values automatically.

Evgeny Gorb
  • 1,442
  • 2
  • 13
  • 24
  • 1
    [`@@IDENTITY` has issues](https://stackoverflow.com/questions/481395/identity-scope-identity-output-and-other-methods-of-retrieving-last-identi), use `SCOPE_IDENTITY` instead – Charlieface Feb 07 '21 at 11:15
  • @Charlieface Hmmm, cool, I didn't know about this issue. They also recomment to use OUTPUT instead)). However I think it's a good point to start from)) – Evgeny Gorb Feb 07 '21 at 11:20
  • Hi, Thank you for your answer :D. I will take that into account for my future projects. For now I chose to learn how to "play" with Dapper. – C.Rus Feb 07 '21 at 11:51
0

If your Primary Key on your User table is an int or bigint, you use the function SCOPE_IDENTITY to get the PK.

set nocount, xact_abort on;   --recommended

begin transaction;

insert into user(User_Id,userName,...)
values(...);

declare @user_id int = scope_identity();   -- make sure this is the very next line after insert

insert into Password(User_Id,....)
values(@user_id,...);

insert into UserInformation(User_Id,....)
values(@user_id,...);

commit transaction;

If you are doing a multi-row insert, you can use Table-Valued Parameters. And then you will need the OUTPUT clause to output the IDs into a new table variable:

set nocount, xact_abort on;   --recommended

begin transaction;

declare @new_users table (user_id int, username varchar(50));

insert into user(User_Id,userName,...)
output inserted.user_id, inserted.username
select ...
from @users;

insert into Password(User_Id,....)
select u.user_id, ...
from passwords p
join @new_users u on u.username = p.username;

commit transaction;

I must say though, that I don't see the point in having a separate table for UserInformation, after all, you cannot have a User without UserInformation, so you shouold combine these into one.

Charlieface
  • 52,284
  • 6
  • 19
  • 43
  • Hi, Thank you for your answer :D As it is right now, is the query affected if I'm using Microsoft SQL Manager Studio with SQL Server? Also, it seems that when I created my tables (with a schema) they got created into System Databases - Master part... It seems a bit weird as it won't let me access the tables. I tried: master.users, master.schema.users, users ... do you have any ideea as to how am I supposed to access the tables? – C.Rus Feb 07 '21 at 11:51
  • Don't know, do you have permissions on `master`? Try `USE master;` then just access the tables straight. SSMS: you can still use that, just declare your variables first (instead of parameters) then run the rest of the script. – Charlieface Feb 07 '21 at 20:50
-1

You have to insert in the primary table then into the others. and use transaction to run the three statements. the following is an example using oracle procedure:

create or replace PROCEDURE InsertUser(.. params ...) 
as 
temp_id number;
BEGIN
insert into user(User_Id,userName,...) values(...) returning User_Id into temp_id;
insert into Password(User_Id,....) values(temp_id,...);
insert into UserInformation(User_Id,....) values(temp_id,...);
commit;
exception when others then rollback;
END InsertUser;
Anas Sharabati
  • 67
  • 2
  • 6