How to get value from a database column using C#

Question

I have a web application that uses these 3 tables:

Client:

CREATE TABLE CLIENTE(
ID_CLIENTE INT PRIMARY KEY,
CLIENTE VARCHAR(50),
ENDERECO VARCHAR(50),
CIDADE VARCHAR(30),
CEP VARCHAR(9),
UF CHAR(2)
);

Request:

CREATE TABLE PEDIDO (
NUM_PEDIDO INT PRIMARY KEY,
ID_CLIENTE INT FOREIGN KEY REFERENCES CLIENTE(ID_CLIENTE),
ID_VENDEDOR INT FOREIGN KEY REFERENCES VENDEDOR(ID_VENDEDOR),
PRAZO_ENTREGA VARCHAR (50)
);

Item_request:

CREATE TABLE ITEM_PEDIDO(
ID_ITEM_PEDIDO INT PRIMARY KEY IDENTITY (1,1),
NUM_PEDIDO INT FOREIGN KEY REFERENCES PEDIDO(NUM_PEDIDO),
ID_PRODUTO INT FOREIGN KEY REFERENCES PRODUTO(ID_PRODUTO),
QUANTIDADE INT
);

Because of the Integrity Reference, where I am deleting a customer, I shall exclude not only the orders made by him, but also the items in that order. But, as seen in the code above, the table ITEM_PEDIO has no relationship with Client, It is therefore necessary to first identify all the records that are related to that customer’s order. My question is as follows: How to get the primary key of this request by storing it within a int and then, using as a parameter for deleting order items ?

An example of my goal:

Cliente c = new Cliente();
c.ID_Cliente = 1;
int PK = "select PEDIDO.NUM_PEDIDO where ID_Cliente = "+c.ID_Cliente;
SqlDataSource1.DeleteCommand = "delete from ITEM_PEDIDO where NUM_PEDIDO = "+PK;
SqlDataSource1.Delete();

Answer 1

Here is a way to solve that involves deleting the rows of the ITEM_PEDIO table first, following the ORDER table to finally delete the client from the CLIENT table.

-- código #1
-- cliente a apagar da tabela de clientes
declare @excCliente int;
set @excCliente= ...;


BEGIN TRANSACTION;

DELETE ITEM_PEDIDO
  where NUM_PEDIDO in (SELECT NUM_PEDIDO
                         from PEDIDO
                         where ID_CLIENTE = @excCliente);

DELETE PEDIDO
  where ID_CLIENTE = @excCliente;

DELETE CLIENTE
  where ID_CLIENTE = @excCliente;

COMMIT;

To maintain database consistency, everything involved by the pair BEGIN TRANSACTION / COMMIT.

The @excClient variable must be marked with the customer ID to be deleted.

---

Another way is to declare the columns ID_CLIENTE (REQUEST table) and NUM_PEDIDO (ITEM_PEDIDO table) with the action ON DELETE CASCADE.

-- código #2
tabela PEDIDO
...
ID_CLIENTE INT FOREIGN KEY REFERENCES CLIENTE(ID_CLIENTE)
            ON DELETE CASCADE,
...

tabela ITEM_PEDIDO
...
NUM_PEDIDO INT FOREIGN KEY REFERENCES PEDIDO(NUM_PEDIDO)
                         ON DELETE CASCADE,
...

Thus, by deleting a row from the CUSTOMER table, automatically all orders (and respective order items) of the customer are deleted.

-- código #3
-- cliente a apagar da tabela de clientes
declare @excCliente int;
set @excCliente= ...;

  DELETE CLIENTE
  where ID_CLIENTE = @excCliente;

Answer 2

Simply put, first you need to take all customer orders, and then delete. As there are more than one request, play it on a Sqldatareader and read the values:

using (connection)
{
    SqlCommand command = new SqlCommand("select PEDIDO.NUM_PEDIDO where ID_Cliente = @cliente",
      connection);

    connection.Open();

    command.Parameters.Add("@cliente", SqlDbType.Int).Value = c.ID_Cliente;

    SqlDataReader reader = command.ExecuteReader();

    if (reader.HasRows)
    {
        while (reader.Read())
        {
            SqlCommand command2 = new SqlCommand("delete from ITEM_PEDIDO where NUM_PEDIDO = @pedido",
                connection);

            command2.Parameters.Add("@pedido", SqlDbType.Int).Value = reader.GetInt32(0);

            command2.ExecuteNonQuery();
        }
    }
    else
    {
        Console.WriteLine("No rows found.");
    }
    reader.Close();

    SqlCommand command3 = new SqlCommand("delete from cliente where ID_Cliente = @cliente",
              connection);

    connection.Open();

    command3.Parameters.Add("@cliente", SqlDbType.Int).Value = c.ID_Cliente;

    command3.ExecuteNonQuery();
}