Problems with functions 3

Question

I have a problem, I have a view in the database that makes agroup BY, and have another table GraficoCor, I make every time theWhile the first function makes an increment he pass the counter value for the variable IdCor the second function, the second function will query having as reference the value ofIdCor returning to color in hex for the first function. How to do that?

Function GetFaturamentoIVEL

public static FatoFaturamentoIVELBO[] GetFaturamentoIVEL(string Operacao, Connection Cn)
        {            
            var RsFaturamento = new Recordset();
            int Cont = 0;
            try
            {
                RsFaturamento.Open(String.Format("SELECT Operacao, AnoMes, TradeMarketing, SUM(ValorNF)AS ValorTotal FROM dbo.FatoFaturamentoIVEL WHERE TradeMarketing = 0  and AnoMes = '2016/04' GROUP BY Operacao, AnoMes, TradeMarketing ORDER BY SUM(ValorNF) ASC", Operacao), Cn, CursorTypeEnum.adOpenStatic, LockTypeEnum.adLockReadOnly);
                var ArrayRetorno = new FatoFaturamentoIVELBO[RsFaturamento.RecordCount];
                while (!RsFaturamento.EOF)
                {
                    FatoFaturamentoIVELBO Faturamento = new FatoFaturamentoIVELBO();
                    Faturamento.Operacao = RsFaturamento.Fields["Operacao"].Value.ToString();
                    Faturamento.AnoMes = RsFaturamento.Fields["AnoMes"].Value.ToString();
                    Faturamento.ValorNF = decimal.Parse(RsFaturamento.Fields["ValorTotal"].Value.ToString());
                    ArrayRetorno[Cont] = Faturamento;    
                    Cont++;
                    RsFaturamento.MoveNext();
                }
                RsFaturamento.Close();
                return ArrayRetorno;
            }
            catch (Exception ex)
            {
                throw new Exception("Erro: " + ex.Message);
            }
        }

Function GetCor

public  static FatoFaturamentoIVELBO GetCor(int IdCor, Connection Cn)
        {
            var Cor = new FatoFaturamentoIVELBO();
            var RsCor = new Recordset();
            try
            {
                RsCor.Open(String.Format("SELECT IdCor, CodHex from dbo.GraficoCor  where IdCor = " + IdCor), Cn, CursorTypeEnum.adOpenStatic, LockTypeEnum.adLockReadOnly);
                if (!RsCor.EOF)
                {
                    Cor.CodHex = RsCor.Fields["CodHex"].Value.ToString();
                }
                return Cor;
            }
            catch (Exception ex)
            {
                throw new Exception("Erro :" + ex.Message);
            }
        }

Which part isn't working, or which part do you not know how to implement? — bmm6o, Apr 18 '16 at 19:06
What is it that you don't know how to implement? You see the problem here is that you need help but you have not explained what you are trying to do or what you need help with. As of now you have not asked a complete question, just a vague bit of code with no basis on anything concrete. — Sean Lange, Apr 18 '16 at 19:18

score 0 · Accepted Answer · edited May 23 '17 at 11:59

Presumably, you just call something like:

Faturamento.SomeProperty = GetCor(Cont, Cn)

This is unlikely to be what you want for two reasons. It's usually a mistake to use a counter like that to produce database ids, instead the id should be returned as part of the query. And instead of making a separate request for each row, you should join the queries together and get the data all at once. See e.g. this question.

Jesus is Lord · Answer 2 · 2016-04-18T20:39:34.823

I'm guessing that you primarily speak another language - that's fine and not your fault - but it's really hard to understand what you're asking. Could you edit what you wrote and word it differently?

This is a total guess, but I think you need to rewrite your first query:

SELECT Operacao, AnoMes, TradeMarketing, SUM(ValorNF)AS ValorTotal FROM dbo.FatoFaturamentoIVEL WHERE TradeMarketing = 0 and AnoMes = '2016/04' GROUP BY Operacao, AnoMes, TradeMarketing ORDER BY SUM(ValorNF) ASC

So that it uses an INNER JOIN or LEFT OUTER JOIN to the dbo.GraficoCor so that you don't have to call GetCor each time through your while loop.

I think you could either do a sub-select or something like this should show you how to use ROW_NUMBER():

SELECT 
[Person Name], 
[Row Type]
FROM
(
    SELECT 
    *,
    ROW_NUMBER() OVER(ORDER BY [Person Name] ASC) AS [Id]
    FROM
    (
        SELECT 'Alice' [Person Name] UNION
        SELECT 'Bob' [Person Name] UNION
        SELECT 'Charlie' [Person Name]  UNION
        SELECT 'Daniel' [Person Name] 
    ) [Alias]
) AS [Main]

LEFT OUTER JOIN
(
    SELECT 0 [Parity], 'Even Row' [Row Type] UNION
    SELECT 1 [Parity], 'Odd Row' [Row Type]
) [Lookup]
ON [Main].[Id] % 2 = [Lookup].[Parity]

Results:

Person Name | Row Type
----------------------
Alice       | Odd Row
Bob         | Even Row
Charlie     | Odd Row
Daniel      | Even Row

So in your case inside your GetFaturamentoIVEL function you can do this (untested):

RsFaturamento.Open(String.Format("@
SELECT 
[OrderedQuery].[Operacao], 
[OrderedQuery].[AnoMes], 
[OrderedQuery].[TradeMarketing],
[OrderedQuery].[ValorTotal],
[GraficoCor].[CodHex]
FROM
(
    SELECT 
    *,
    (ROW_NUMBER() OVER(ORDER BY SUM(ValorNF) ASC)) - 1 AS [IdCor]
    FROM
    (
        SELECT 
        Operacao, 
        AnoMes, 
        TradeMarketing,
        SUM(ValorNF) AS ValorTotal 
        FROM dbo.FatoFaturamentoIVEL 
        WHERE TradeMarketing = 0 and AnoMes = '2016/04' 
        GROUP BY Operacao, AnoMes, TradeMarketing           
    ) [Query]
) AS [OrderedQuery]    
LEFT OUTER JOIN [dbo].[GraficoCor] [GraficoCor] ON [OrderedQuery].[IdCor] = [GraficoCor].[IdCor]
", Operacao), Cn, CursorTypeEnum.adOpenStatic, LockTypeEnum.adLockReadOnly);

Problems with functions 3

2 Answers2