c # query with cycles

Hello, do the following:

 SqlConnection conn = new SqlConnection(@"Data Source=HUGO-PC\HUGO;Initial Catalog=fatura;User ID=sa;Password=hugostoso07");
        conn.Open();
        SqlCommand cmd = new SqlCommand(@"SELECT 
                                            my_cab.REGISTRO,
                                            my_cab.DOC,
                                            my_cab.SERIE,
                                            my_cab.DATA,
                                            my_cab.ANO,
                                            my_cab.TERCEIRO,
                                            my_cab.PROCESSO,
                                            my_cab.REQUISICAO,
                                            my_cab.N_DOC,
                                            my_cab.CONTRIBUINTE,
                                            my_cab.NOME,
                                            my_cab.MORADA_1,
                                            my_cab.MORADA_2,
                                            my_cab.LOCALIZACAO,
                                            my_cab.COD_POSTAL,
                                            my_cab.DESC_COD_POSTAL,
                                            mv_lin.ID_LINHA,
                                            mv_lin.T_DESC,
                                            mv_lin.ARTIGO,
                                            mv_lin.DESCRICAO,
                                            mv_lin.QNT,
                                            mv_lin.PRECO,
                                            mv_lin.DESCONTO,
                                            mv_lin.IVA,
                                            mv_lin.VALOR
                                        FROM my_cab
                                        INNER JOIN mv_lin
                                        ON my_cab.REGISTRO = mv_lin.REGISTRO", conn);

        SqlDataReader reader = cmd.ExecuteReader();

        string registroAnt = string.Empty;

        while (reader.Read())
        {
            if (registroAnt == string.Empty || registroAnt != reader["REGISTRO"].ToString())
            {
                Console.WriteLine("Registo: {0}, Doc: {1}, Serie: {2}, Data: {3}, Ano: {4}, Terceiro: {5}, Processo: {6}, Requisição: {7}, NºDoc: {8}, Contribuinte: {9}, Nome: {10}, Morada_1: {11}, Morada_2: {12}, Localização: {13}, Codigo Postal: {14}, Desconto Cod Postal: {15}",
                                    reader["REGISTRO"],
                                    reader["DOC"],
                                    reader["SERIE"],
                                    reader["DATA"],
                                    reader["ANO"],
                                    reader["TERCEIRO"],
                                    reader["PROCESSO"],
                                    reader["REQUISICAO"],
                                    reader["N_DOC"],
                                    reader["CONTRIBUINTE"],
                                    reader["NOME"],
                                    reader["MORADA_1"],
                                    reader["MORADA_2"],
                                    reader["LOCALIZACAO"],
                                    reader["COD_POSTAL"],
                                    reader["DESC_COD_POSTAL"]);
            }

            Console.WriteLine("\t\t LOCALIZACAO: {0}, COD_POSTAL: {1}, DESC_COD_POSTAL: {2}, ID_LINHA,T_DESC: {3} , ARTIGO: {4}, DESCRICAO: {5}, QNT: {5}, PRECO: {6}, DESCONTO: {7}, IVA: {8}, VALOR: {9}",
                                reader["LOCALIZACAO"],
                                reader["COD_POSTAL"],
                                reader["DESC_COD_POSTAL"],
                                reader["ID_LINHA"],
                                reader["T_DESC"],
                                reader["ARTIGO"],
                                reader["DESCRICAO"],
                                reader["QNT"],
                                reader["PRECO"],
                                reader["DESCONTO"],
                                reader["IVA"],
                                reader["VALOR"]);


            registroAnt = reader["REGISTRO"].ToString();
        }

        reader.Close();
        conn.Close();

        if (Debugger.IsAttached)
        {
            Console.ReadLine();
        }

    

First, your question is not with C #, it is in query language to SQL database, as you are using SQL Server, T-SQL. The commands that you mount in C # are just actions to access the database, execute a query (or command / action) and then to read the result.

Second, it would be good to study how to write querys regardless of the application you are using, because the impact on the application is generally large, especially when the application goes live and has large data sets.

Mounting SQL in C # code without a treatment when you need to concatenate filters causes security issues, in which case you do not do this, but it is important to take a look at other places. A reading about the subject (SQL Injection): link

And third, there are other ways to do Joins, depending on the data structure and how information is stored, this knowledge gives you a lot of flexibility when manipulating information, a reference taken from W3S:

  

Different SQL JOINs Before we continue with examples, we will list the   types of the different SQL JOINs you can use:

     

INNER JOIN: Returns all rows when there is at least one match in BOTH   tables LEFT JOIN: Return all rows from the left table, and the matched   rows from the right table RIGHT JOIN: Return all rows from the right   table, and the matched rows from the left table FULL JOIN: Return all   rows when there is a match in ONE of the tables

And the link: link

Edit by comments:

Only for those who do not click on the link above, the first example of this link teaches you how to do a join that solves the problem. If you just want to copy the structure of a join without knowing anything else, enter the link, copy and change the names of the columns and tables, if you want to learn what can be done with joins, read the link that is very basic. / p>

In my experiences, when I had to work with large data sets, or even in routine performance analysis activities, I had to deal with the databases. Sometimes avoiding a join improves things, sometimes, not doing a select without a conditional improves things, but in all cases, I needed to know SQL and its operation to solve my problems, whether in a cluster or in the applications. scope.

People make common mistakes just because they deliver the easy way, they think it's the DBA's fault because the data layer provides slow responses or claims the server is not good, that's the worst. But in the end, and with so much of the ORM tools being used now, simple do-it-yourself concepts solve the case. So my advice is, Understand the flow of your application so you do not mess up.

A post on Martin Fowler's website that I found interesting and illustrates well the importance of working as a DBA friend.

link

Even CI or CD require a database knowledge that goes beyond Joins. If you've worked on projects with more than 30 people, you know how difficult it is to manage this kind of thing if your software process and knowledge between bank and application is not balanced, roles have to work together so you do not have problems.

In short, I will not copy a Join to make life easier now, when I can teach you other things that can improve your life in the long run.

Luã Govinda Mendes Souza's post solves more immediately, but, you should look for knowledge that brings a "more definitive solution."