How to use Insert with multiple C# SQL records

Question

I’m new here and also in the world of programming, today I managed to connect C# to SQL, by making a simple connection with the BD I can insert records, but when trying to insert 2 data in the same table simultaneously it creates 2 entries or just inserts the last one, please help me, follow the code below...

    private void bt_Cadastrar_Click(object sender, EventArgs e)
    {
        if (bt_Cadastrar.Text == "Salvar")
        {
            tb_ID.Enabled = false;
            bt_Cadastrar.Text = "Cadastrar";
        }

        Int16 Cheque, Convenio;

        if (cb_Cheque.Checked) Cheque = 1;
        else Cheque = 0;
        if (cb_Convenio.Checked) Convenio = 1;
        else Convenio = 0;

        SqlConnection conn = new SqlConnection("Server= localhost; Database=Cadastros; Integrated Security=true"); //Cria a conexão com o Banco de dados devido a Dll que tem na pasta do programa.
        SqlCommand cmd = new SqlCommand("insert into ccliente (Nome) values ('" + tb_Nome.Text.ToUpper() + "')", conn); //Comando SQL para criar inserir um item em uma tabela existente

        conn.Open();
        cmd.ExecuteNonQuery();
        conn.Close();

        /*
        cmd = new SqlCommand("insert into ccliente (CPF) values ('" + tb_CPF.Text.ToUpper() + "')", conn);
        cmd = new SqlCommand("insert into ccliente (RG) values ('" + tb_RG.Text.ToUpper() + "')", conn);
        cmd = new SqlCommand("insert into ccliente (CEP) values ('" + tb_CEP.Text.ToUpper() + "')", conn);
        cmd = new SqlCommand("insert into ccliente (Cidade) values ('" + tb_Cidade.Text.ToUpper() + "')", conn);
        cmd = new SqlCommand("insert into ccliente (Email) values ('" + tb_Email.Text.ToUpper() + "')", conn);
        cmd = new SqlCommand("insert into ccliente (Telefone1) values ('" + tb_Telefone1.Text.ToUpper() + "')", conn);
        cmd = new SqlCommand("insert into ccliente (Telefone2) values ('" + tb_Telefone2.Text.ToUpper() + "')", conn);
        cmd = new SqlCommand("insert into ccliente (Observacao) values ('" + tb_Observacao.Text.ToUpper() + "')", conn);
        cmd = new SqlCommand("insert into ccliente (Cheque) values ('" + Cheque + "')", conn);
        cmd = new SqlCommand("insert into ccliente (Convenio) values ('" + Convenio + "')", conn);
        */

    }

If necessary follow below the link to the complete project: goo.Gl/6P8Hwc (Please, if any moderator thinks this is wrong can delete the link, I’m new here and do not know the rules yet).

Answer 1

I believe your problem is in the syntax of Insert, if you perform all cmd that are commented on, you would make a record for each line.. a solution would be:

insert into ccliente TODAS AS PROPRIEDADES values (TODOS OS RESPECTIVOS VALORES)", conn); 

But it depends on the structure of your project, for example: you can have a class DAO (Data Access Object) for each table in your database. In my work journey, when we make a class, we also make a class in a DB context, that is, the class that will be instantiated and used to transform the data on the screen into data for the database, follows example:

  private void bt_Cadastrar_Click(object sender, EventArgs e)
    {
        Cliente novoCliente = new Cliente(); // instância da classe cliente, a qual referencia a tabela ccliente.

        if (bt_Cadastrar.Text == "Salvar")
        {
            tb_ID.Enabled = false;
            bt_Cadastrar.Text = "Cadastrar";
        }

        if (cb_Cheque.Checked) novoCliente.Cheque = true;
        if (cb_Convenio.Checked) novoCliente.Convenio = true;


        SqlConnection conn = new SqlConnection("Server= localhost; Database=Cadastros; Integrated Security=true"); //Cria a conexão com o Banco de dados devido a Dll que tem na pasta do programa.
        SqlCommand cmd = new SqlCommand("insert into ccliente values ('" + novoCliente.Nome + "')", conn); //Comando SQL para criar inserir um item em uma tabela existente

        conn.Open();
        cmd.ExecuteNonQuery();
        conn.Close(); 
    }

PS: in this my code missing an important point: assign the rest of the properties in the class.

Another point, when using objects and classes for communication with the bank, I believe that the ideal is to use also Querys to perform actions with the bank, which makes your work MUCH easier in conjunction with LAMBDA.

Anyway, what I tried to pass you is to study that context and your life will become much easier when the subject is capture and record information in DB.

I hope I helped :D

Answer 2

I advise you to study more SQL syntax. You can do as M.Bertolazo said, but for this, the fields have to be in order, and, be all fields of the table.

The correct syntax is:

"INSERT INTO TABELA (Nome,CPF,RG,CEP,Cidade,...) VALUES ('Nome','CPF','RG','CEP','Cidade',...);"

and taking advantage, the question, to execute two commands, you can do so:

string sql = @"INSERT INTO TABELA1 (CAMPO1,CAMPO2,CAMPO3) VALUES (1,2,3);";
sql += @"INSERT INTO TABELA2 (CAMPOA,CAMPOB,CAMPOC) VALUES ('A','B','C');";

SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();

or so:

string sql = @"INSERT INTO TABELA1 (CAMPO1,CAMPO2,CAMPO3) VALUES (1,2,3);";
string sql2 = @"INSERT INTO TABELA2 (CAMPOA,CAMPOB,CAMPOC) VALUES ('A','B','C');";

SqlCommand cmd = new SqlCommand(sql, conn);
SqlCommand cmd2 = new SqlCommand(sql2, conn);
conn.Open();
cmd.ExecuteNonQuery();
cmd2.ExecuteNonQuery();
conn.Close();

there are still other ways to do it, depends a lot on your need.

Recommended reading: C# - Working with parameters in SQL queries http://www.macoratti.net/09/07/c_adn_7.htm