How to get value from a database column using C #

Question

How to get value from a database column using C #

Navigation

#1 by (3 votes)
#2 by (1 votes)

3

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)
);

Order:

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)
);

Order_id:

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 Referential Integrity , whenever I go delete a client, I must exclude not only the requests made by him, but also the items of that request. But, as seen in the above code, the REQUIRED ITEM table does not have the relationship with Customer , so it is first necessary to identify all records that are related to that customer's request. My question is: How do I get the primary key of this request, storing it inside a int and then, using as a parameter to delete the 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();

sql sql-server c# asp.net

asked by anonymous 02.12.2017 / 15:13

2 answers

1

Simply put, you first need to get all customer orders, then delete them. As they are more of a request, throw this in a SqlDataReader and it will 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();
}

02.12.2017 / 17:56

Alternative String.replace () How to insert sequence in non-self-incrementing field

score 3 · Accepted Answer

This is a workaround that involves first deleting the rows from the REQUESTED table, then from the REQUEST 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 BEGIN TRANSACTION / COMMIT pair.

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

Another way is to declare the columns CUSTOM_ID (REQUEST table) and REQUEST_NOUNT (table REQUIRED_STATE) 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,
...

In this way, deleting a row from the CLIENT table automatically deletes all requests (and their purchase order items) from the client.

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

  DELETE CLIENTE
  where ID_CLIENTE = @excCliente;