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

Question

The idea is that I insert data referring to 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)
);

and 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),
...
);

Within my application I have a int fk, after entering the data of Person, how should I proceed to "pick up" the primary key of this table to define the Foreign key table Prestador_Servico?

I’d 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();

Recall the syntax "errors", I did so so so that my problem was better understood.

Answer 1

Anything you can do at once in SQL, do. Use SCOPE_IDENTITY(). Something like that:

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

I put in the Github for future reference..

Don’t forget to use the Parameters to pass the values to the query.

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