c# query with cycles

Question

Good people I am doing a work in using c# and my knowledge is still very limited and I am having a doubt. I have 2 tables and what I wanted to do was show the information of the 2 where the information would not be repeated.

This is a relation of 1-n in which 1 header can have n lines and what I wanted was to know if there is any query alone that can return me exactly what I want without repeating values or if I have to use several querys I’ll show you my example of the code I have so far.

SqlConnection conn = new SqlConnection(@"Data Source=HUGO-PC\HUGO;Initial Catalog=fatura;User ID=sa;Password=hugostoso07");
conn.Open();
SqlCommand cmd = new SqlCommand("select * from my_cab", conn);
SqlDataReader reader = cmd.ExecuteReader();

while(reader.Read()){
    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.GetString(0), reader.GetString(1), reader.GetString(2), reader.GetDateTime(3), reader.GetDecimal(4), reader.GetString(5), reader.GetString(6), reader.GetString(7), reader.GetDecimal(8), reader.GetString(9), reader.GetString(10), reader.GetString(11), reader.GetString(12), reader.GetString(13), reader.GetString(14), reader.GetString(15));
}
reader.Close();
conn.Close();
if (Debugger.IsAttached)
{
    Console.ReadLine();
}

Summing up what I wanted you to show me on the console was for example

Heading No 1 lines #1 line 2 line no etc

Heading No 2 line 1 line 2 line nºetc

Is it possible to do this? Thank you!

Answer 1

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

Answer 2

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

Second, it would be good to study how to write querys independent of the application you are using, because the impact on the application is usually great, especially when the application goes into maintenance and has large data sets.

Mounting SQL in C# code without a treatment when you need to concatenate filters causes security problems, in which case you don’t, but it is important to take a look elsewhere. A reading on the subject (SQL Injection): https://www.owasp.org/index.php/SQL_Injection

And third, there are other ways to perform Joins, depending on the data structure and how the information is stored, this knowledge promotes 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: http://www.w3schools.com/sql/sql_join.asp

---

Edit by comments:

Only for those who don’t click in the link above, the first example of this link teaches how to make 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.

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 making a select without a conditional improves things, but in all cases, I needed to know SQL and how it works to solve my problems, whether in a cluster or in isolated scope applications.

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 they claim the server sucks, that’s the worst. But in the end, and with the amount of ORM tools being used now, the simple concepts of what not to do solve the case. So my advice is, Understand the flow of your application not to screw up.

A post on the website of Martin Fowler that I found interesting and that illustrates well the importance of working as a friend of DBA (I say in English):

http://martinfowler.com/articles/evodb.html#DbasCollaborateCloselyWithDevelopers

Even CI or CD require a database knowledge that goes beyond Joins. If you have worked on projects with more than 30 people, you know the difficulty of managing this kind of thing if your software process and the knowledge between bank and application is not balanced, the papers have to work together so you have no problems.

In short, I won’t copy a Join to make life easier now, when I can teach you other things that can improve your long-term life.

---

The post of @Luã Govinda Mendes Souza resolves more immediately, but, you should look for knowledge that brings a "more definitive solution".