How to get value from a field in SQL using C #?

1

The idea is that I insert data about the same person within SQL, but in different Entities, contextualizing:

I have the table Pessoa :

create table Pessoa(
ID_Pessoa int primary key identity (1,1),
Nome_Pessoa varchar(50),
CPF_Pessoa varchar(15),
Categoria(50)
);

I have the table Prestador_Servico :

create table Prestador_Servico(
ID_Prestador int primary key identity (1,1),
ID_Pessoa int foreign key references Pessoa(ID_Pessoa),
...
);

In my application I have a int fk , after entering the data of Person , how should I proceed to "get" the primary key of this table to set the foreign key of table Prestador_Servico ?

I would like something equivalent to this:

obj.Nome = "Daniel";
obj.CPF = "12345"; 
obj.Classe = "PRESTADOR_SERVICO";

SqlDataSource1.InsertCommand = "insert into Pessoa values (obj.Nome,obj.CPF,obj.Classe)";
SqlDataSource1.Insert();

//Pegar PK da pessoa que acabou de ser criada, para vinculá-la ao Prestador, usando CPF como parâmetro
obj.fk = select Pessoa.ID_Pessoa where CPF = obj.CPF

SqlDataSource1.InsertCommand = "insert into Prestador_Servico values (fk, 123, ...)"
SqlDataSource1.Insert();

Relive the "syntax errors", I did this way so that my problem would be better understood.

    
asked by anonymous 29.11.2017 / 18:52

1 answer

1

Everything you can do at once in SQL, do it. Use SCOPE_IDENTITY() . Something like this:

SqlDataSource1.InsertCommand = @"insert into Pessoa values (@Nome, @CPF, @Classe); 
insert into Prestador_Servico values (SCOPE_IDENTITY(), 123, ...);";

I placed GitHub for future reference. .

Do not forget to use Parameters to pass the values to query .

The form I was using had several problems, some not very easy to identify.

    
29.11.2017 / 19:10