Fill List with return from sql

Question

Fill List with return from sql

Navigation

#1 by (5 votes)
#2 by (2 votes)

1

Good morning

How do I populate a list with a return from 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 I'm 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;

}

I need to return the result of select

sql c#

asked by anonymous 20.07.2018 / 15:20

2 answers

5

Why reinvent the wheel? use Dapper .:

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

20.07.2018 / 15:40

Union of two vectors in C Problem with menu switch case and while

score 2 · Accepted Answer

Just complementing the answer, 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 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();

}

Notice that you do not have .ToString at the end of the query execution; The list is declared before the using block (since you want to use it later);