Fill List with sql return

Asked

Viewed 534 times

1

good morning

How do I fill a List with a return of an sql?

What I tried to do for sure will give me an outofrange Exception

List<string> list = new List<string>();
string query = "select * from pedidos_distribuidos";
string connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
SqlConnection con = new SqlConnection(connectionString);
{
    using (SqlCommand cmd = new SqlCommand(query))
    {
        con.Open();

        cmd.CommandTimeout = 300;
        cmd.CommandType = CommandType.Text;
        cmd.Connection = con;
        SqlDataReader reader = cmd.ExecuteReader();

        while (reader.Read())
        {
            list[0] = reader[0].ToString();
        }
        //using (SqlDataAdapter sda = new SqlDataAdapter())
        //{
        //    cmd.Connection = con;
        //    sda.SelectCommand = cmd;
        //    using (DataTable dt = new DataTable())
        //    {
        //        sda.Fill(dt);
        //        pedidos = sda.A ;
        //    }
        //}
    }
}

What am I doing

var list = "";
            string connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
            SqlConnection con = new SqlConnection(connectionString);
            {
                list = con.Query<string>("select * from pedidos_distribuidos" ).ToString() ;

            }


            int total = list.Count();
            if (!String.IsNullOrWhiteSpace(parametrosPaginacao.SearchPhrase))
            {
                //  areaClientes = areaClientes.Where("Area.Contains(@0) OR DescricaoGAreaCliente(@0)", parametrosPaginacao.SearchPhrase);
                list = list.Where("Protocolo.Contains(@0)", parametrosPaginacao.SearchPhrase).ToString();
            }

            var pedidosPaginados = list.OrderBy(parametrosPaginacao.CampoOrdenado).Skip((parametrosPaginacao.Current - 1) * parametrosPaginacao.RowCount).Take(parametrosPaginacao.RowCount);

            //int total = 0;
            DadosFiltrados dadosFiltrados = new DadosFiltrados(parametrosPaginacao)
            {
                rows = pedidosPaginados.ToList(),
                total = total
            };
            return dadosFiltrados;

}

inserir a descrição da imagem aqui I need you to return the result of select

c# sql
  • 1

    would just do that: list.Add(reader[0].ToString()); but, it’s much easier if you use Dapper as the AR put

  • Turns out this rapper is returning me given 12 times more than normal @Rovannlinhalis

  • What do you mean? I don’t understand

  • @Rovannlinhalis put there

  • Let’s go continue this discussion in chat.

  • Even so when I put list = con. Query<string>("select Protocol from pedidos_distributed" ).Tostring(); it returns me 48

  • For God’s sake gabrielfalieri, you need to study the concepts of language before bro. list = con.Query<string>("select * from pedidos_distributed" ).Tostring() ; You get more results because you give a select * from (which should return an object and not string) and you give me a list.Tostring...

  • @Gustavosantos be a little more polite please?

  • I didn’t disrespect you, but of course you don’t know the language and you’re doing something outside of what you know, it’s a tip, studying the language first.

Show 4 more comments

2 answers

4

Why reinvent the wheel? use the Dapper.:

string connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
SqlConnection con = new SqlConnection(connectionString);
{
    var list = con.Query<string>("select coluna from pedidos_distribuidos");
}

  • Meskish, I had forgotten it, even excluded my answer

  • First, who is this Connection, connectionString? Second, is giving "string does not contain a Definition for query error"

  • @gabrielfalieri vi who edited the answer, managed to solve the problem?

  • No, keep making a mistake

  • you declared list as a string.: var list = ""; seeing your code now, you have far more problems than the return of the query.

2


Just complementing the response, and explaining better the problem reported in the chat:

Create a class for your results:

public class PedidosDistribuidos
{
    public string Protocolo {get;set;}
    public DateTime DataProtocolo {get;set;}
    public string Descricao {get;set;}
}

Then run the Query, returning the typed list:

List<PedidosDistribuidos> list;

using (SqlConnection con = new SqlConnection(connectionString))
{
    list = con.Query<PedidosDistribuidos>("select protocolo, data_protocolo, descricao from pedidos_distribuidos").ToList();

}

Note that there is no . Tostring at the end of the query execution; The list is declared before the using block (because you want to use it after);

  • @gabrielfalieri there are still serious problems in your approach, you should filter, page and order directly in your query. today you are bringing the whole table to memory, I assure you that this is one of the most practical ways to commit suicide.

Browser other questions tagged c# sql

You are not signed in. Login or sign up in order to post.