Insert a related table record from c # to sql server

Question

Insert a related table record from c # to sql server

Navigation

#1 by (7 votes)
#2 by (0 votes)

1

I need help! I'm working with inheritance. Client: Person and I need to insert from the Client class, where the methods are (register, modify, delete, list). I need you to insert yourself in a method! The idea that I have is to identify the id of the person to insert the client, and identify the client type.

public void Registrar()
    {
        string sql = string.Format("Insert into Persona (Nombre, ApPaterno,                ApMaterno, FechaNac, Documento, Direccion, Telefono, Sexo, Email) 
 values('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}')",      this.Nombre, this.ApPaterno, this.ApMaterno, this.FechaNac, this.Documento,   this.Direccion, this.Telefono, this.Sexo, this.Email);
        //("Insert into Cliente(Id, codigo, TipoCliente) values({0})",   this.ID, this.codigo, this.TipoCliente)

        Conexion.EjecutarComando(sql);
    }

My database is the following:

create table Persona(
Id int not null identity(1,1),
Nombre varchar(50) not null,
ApPaterno varchar(50),
ApMaterno varchar(50),
FechaNac date not null,
Documento varchar(20) not null,
Direccion varchar(50),
Telefono varchar(20),
Sexo varchar(20) not null,
Email varchar(50),
constraint pk_persona primary key(Id),
)

create table TipoCliente(
Id int identity(1,1) not null,
Nombre varchar(50) not null,
constraint pk_TipoCliente primary key(Id),
)

create table Cliente(
Id int not null,
codigo int not null,
TipoCliente int not null,
constraint pk_cliente primary key(Id),
constraint fk_persona_Cliente foreign key(Id)
references Persona(Id) on delete cascade on update cascade,
constraint fk_Cliente_TipoCliente foreign key(TipoCliente)
references TipoCliente(Id) on delete cascade on update cascade
)

c# sql-server

asked by Sarita Condori 14.04.2016 в 12:09

source

2 answers

Failed to work with pointers retrieve attribute value data-id from html button and bootstrap [duplicated]

score 7 · Answer 1

The first thing I would advise is that you use parameters, join in a string the values of insert is not recommended

The code should look like this

public void Registrar()
{
    using (SqlConnection conn = new SqlConnection("connection string")) 
    { 

    string sql = @"Insert into Persona (Nombre, ApPaterno, ApMaterno, 
                    FechaNac, Documento, Direccion, Telefono, Sexo, Email) 
                values(@nombre, @ApPaterno, @ApMaterno, ...)"; 

        SqlCommand cmd = new SqlCommand(sql, conn); 
        cmd.Parameters.AddWithValue("@nombre", this.Nombre); 
        cmd.Parameters.AddWithValue("@ApPaterno", this.ApPaterno);
        cmd.Parameters.AddWithValue("@ApMaterno", this.ApMaterno);
        .
        .

        cmd.ExecuteNonQuery(); 
    }
}

use ALWAYS parameters to assign values.

Because of the problem in the inheritance, this article analyzes

n-Layer - SchoolManager - Inheritance and navigation of related entities (1 / 2)

You will see how I define an example using Instructor that inherits from Persona then from the method Save () is called first when recording the base class (ie person) is inserted and then proceeds with the instructor. By inserting the person you get the Id to be able to relate the records between the tables.

score 0 · Answer 2

If you have a data structure where one table depends on the other, as is your case

Person - > Client

For this, what you must do first is to insert the record in the parent table, in this case Person, and then with the generated Id insert the record in the child table >, which would become the Client. That should be the order for the Foreign Key reference to work and an error should not occur. In the previous answer it shows you how to make the insert with the use of parameters, you would have to register for Person and one for Client.