how to do a c# insert with a select values in a select from database-CodePudding

I'm trying to make my select values enter my insert but I don't know how to make this loop. Currently this my select returns more than 1 value. But he only inserts the first one. Can someone help me with this loop???

            {
                string cs = @"Data Source=xxxx";
                SqlConnection cn = new SqlConnection(cs);
                cn.Open();
                Console.WriteLine("established connection");

                string query = @"SELECT a.idfaturamento,
                                       a.id_capa,
                                       a.pagamentodata,
                                       a.mensal,
                                       b.id_status
                                FROM   capas_faturamentos AS a
                                       INNER JOIN capas AS b
                                               ON a.id_capa = b.id_capa
                                WHERE  b.id_status = 4
                                       AND a.mensal = 1
                                       AND Month(a.pagamentodata) != Month(Getdate()) ";

                SqlCommand cmd1 = new SqlCommand(query, cn);
                SqlDataReader r = cmd1.ExecuteReader();

                DateTime pagamentoData = DateTime.Today;
                int id_Capa = 0;
                bool temFaturamentoMensal = false;

                while (r.Read() == true)
                {
                    int idFaturamento = r.GetInt32(0);
                    id_Capa = r.GetInt32(1);
                    pagamentoData = r.GetDateTime(2);

                    Console.WriteLine("ID FATURAMENTO: {0}\t ID CAPA:{1}\t DATA:{2}\t",
                        idFaturamento, id_Capa, pagamentoData);
                    temFaturamentoMensal = true;
                }
                cn.Close();

                Console.WriteLine("___________________");

                cn.Open();
                if (temFaturamentoMensal)
                { 
                    string query2 = @"insert into capas_faturamentos values ('"  id_Capa  "','"   pagamentoData.ToString("yyyy/MM/dd")   "', '"   pagamentoData.ToString("yyyy/MM/dd")   "', '"   pagamentoData.ToString("yyyy/MM/dd")   "', '"   pagamentoData.ToString("yyyy/MM/dd")   "', '"   pagamentoData.ToString("yyyy/MM/dd")   "', '"   pagamentoData.ToString("yyyy/MM/dd")   "','0','0','0','0','0','0', NULL, NULL, NULL, NULL, NULL, NULL, '1')";
                    Console.WriteLine(query2);
                    SqlCommand cmd = new SqlCommand(query2, cn);
                    int result = cmd.ExecuteNonQuery();
                    Console.WriteLine(result   " record/s insert in table capas_faturamentos");
                }
                cn.Close();```

I need help creating this loop.

CodePudding user response：

you don't need any loop. Put everything in one sql script

string query = @"
insert into capas_faturamentos 
SELECT a.idfaturamento,
         a.id_capa,
         a.pagamentodata,
           a.pagamentodata,
            a.pagamentodata,
           a.pagamentodata, 
             a.pagamentodata,
           a.pagamentodata, 
            '0','0','0','0','0','0', NULL, NULL, NULL, NULL, NULL, NULL, '1' 
  FROM   capas_faturamentos AS a
         INNER JOIN capas AS b
                 ON a.id_capa = b.id_capa
  WHERE  b.id_status = 4
         AND a.mensal = 1
         AND Month(a.pagamentodata) != Month(Getdate()) ";

and code

 string cs = @"Data Source=xxxx";
var myConnection = new MySqlConnection(cs);
MySqlCommand myCommand = new MySqlCommand(query, myConnection);
myCommand.Connection.Open();
myCommand.ExecuteNonQuery();
myConnection.Close();

CodePudding user response：

for your insert you don't need a loop

Run only

insert into capas_faturamentos 
SELECT 
       a.id_capa,
       a.pagamentodata,
       a.pagamentodata,
       a.pagamentodata,
       a.pagamentodata,
       a.pagamentodata,
       a.pagamentodata,
       '0','0','0','0','0','0', NULL, NULL, NULL, NULL, NULL, NULL, '1'    
FROM   capas_faturamentos AS a
       INNER JOIN capas AS b
               ON a.id_capa = b.id_capa
WHERE  b.id_status = 4
       AND a.mensal = 1
       AND Month(a.pagamentodata) != Month(Getdate())

If you want to use the loop at all use prepared statements like here How do multi rows insert with MySqlCommand and prepare statement?(#C)