build an Sql query in c#

Question

I need to pass a sql query to c#.

Here is the query:

select 
    T010.A010_nom_ent A010_nom_ent
from
    T010_ENTIDADE T010,
    T016_USUARIO T016
        left outer join
    T307_CELULA_USUARIO T307 ON T307.A306_seq_celula = 0
        and T307.A016_cod_usuario = T016.A016_cod_usuario
where
    T010.A010_cod_entidade = T016.A016_cod_usuario
order by 1

and the code is here:

public List<Entidade> ListarUsuariosCelula()
    {
        List<Entidade> lstEntidades = null;

        try
        {
            oInvitroEntities = new InvitroEntities();

            lstEntidades = (***here is where i want to put the query***)


            return lstEntidades;
        }
        catch (Exception ex)
        {
            LogErro.Trace(ex);
            throw ex;
        }
        finally
        {
            oInvitroEntities = null;
        }
    }

Here is one exemple of what i want to do (i will put just a piece of the code):

try
        {
            oInvitroEntities = new InvitroEntities();

            lstCelula = (from cel in oInvitroEntities.T306_CELULA
                               select new Celula { Codigo = cel.A306_seq_celula, Descricao = cel.A306_dsc_celula }).ToList();


            return lstCelula;
        }

What is your ORM? Is it EntityFramework or something else? Could you show us your mapping? And your query seems not working. — Kirill Bestemyanov
– Kirill Bestemyanov, Commented Jan 9, 2014 at 14:54
It is working, and i just need to pass that to c#. The other things i have to do i know how — user3107374
– user3107374, Commented Jan 9, 2014 at 14:58
Usually queries are passed to SQL Server not the other way.... what do you mean pass a sql query to c#. — Steve
– Steve, Commented Jan 9, 2014 at 14:58
Do you mean you need to translate the query to C# (I'm assuming Entity Framework, specifically)? — Sven Grosen
– Sven Grosen, Commented Jan 9, 2014 at 15:00
I will edit my question and put there an exemple of what a need — user3107374
– user3107374, Commented Jan 9, 2014 at 15:05

Cameron Tinker · Accepted Answer · 2014-01-09 15:11:28Z

2

You can use a multi-line string for ease of reading. Assuming you have already setup the database connection and command object, you would need to do something like this for storing your query:

    string query = @"select 
    T010.A010_nom_ent as A010_nom_ent
from
    T010_ENTIDADE T010,
    T016_USUARIO T016
        left outer join
    T307_CELULA_USUARIO T307 ON T307.A306_seq_celula = 0
        T307.A016_cod_usuario = T016.A016_cod_usuario
where
    T010.A010_cod_entidade = T016.A016_cod_usuario
order by 1";

Note, I've corrected your query:

select 
    T010.A010_nom_ent as A010_nom_ent
from
    T010_ENTIDADE T010,
    T016_USUARIO T016
        left outer join
    T307_CELULA_USUARIO T307 ON T307.A306_seq_celula = 0
        T307.A016_cod_usuario = T016.A016_cod_usuario
where
    T010.A010_cod_entidade = T016.A016_cod_usuario
order by 1

If you want to rename a column, you use the as keyword to do so. Aliases are only for referring to tables.

edited Jan 9, 2014 at 15:11

answered Jan 9, 2014 at 15:02

Cameron Tinker

9,83710 gold badges48 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

dee-see Over a year ago

If you are really going for ease of reading, a multi-line string prefixed with @ would probably be better, with the added bonus of not instantiating an object for nothing.

Cameron Tinker Over a year ago

Thanks for pointing this out. I edited my answer to use a multi-line string.

user3107374 Over a year ago

There is a rule in my team for the system to use only like i posted in my question

Kirill Bestemyanov Over a year ago

@user3107374, this answer was added for trolling.

Kirill Bestemyanov · Accepted Answer · 2014-01-09 15:30:10Z

1

It seems that part with left outer join can be removed (it will not change result of query). So you sql query can be reqrite in linq to entities (there is single inner join cause i droped off your left outer join):

oInvitroEntities.T010_ENTIDADE.Join(oInvitroEntities.T016_USUARIO,
                                    t10=>t10.A010_cod_entidade, 
                                    t16=>t16.A016_cod_usuario, 
                                    (t10, t16) => t10.A010_nom_ent);

answered Jan 9, 2014 at 15:30

Kirill Bestemyanov

12k2 gold badges26 silver badges38 bronze badges

Collectives™ on Stack Overflow

build an Sql query in c#

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related