Convert SqlDataReader in Generic List

Question

Convert SqlDataReader in Generic List

Navigation

#1 by (1 votes)

1

I have a SqlDataReader in a method and I need to return it, the problem is that when closing the SQL connection the data of SqlDataReader disappears, to what kind of data should I be able to pass it to return it ?, this is my code

static public tipoDato EjecutarSP(string sp, SqlParameter[] parametros)
    {
        try
        {
            SqlCommand command = new SqlCommand(sp, ConexionBD.con);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.AddRange(parametros);
            ConexionBD.Conectar();
            SqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {

            }
            ConexionBD.Desconectar();
            return resultado;
        }
        catch (Exception ex)
        {
            throw new Exception(" Error al ejecutar procedimiento almacenado ", ex);
        }
    }

sql c# sql-server sqldatatypes

asked by Alejandro Ricotti 06.02.2017 в 16:37

source

1 answer

limited and dependent selections between two checkbox groups Validate number of fields and have records csv file

score 1 · Accepted Answer

Assuming you work with strings, create a list at the beginning of the method.

List<string> res = new List<string>()

Inside the reader.read () you can do

 while (reader.Read())
    {
    string aux = reader.GetString(0) 
    res.Add(aux);    
    }

and in this way you have all the strings in the list.

P.D: you would be taking the first column of the query, and you are treating it as a string.

If you want to deal more generically, instead of string create the list of type object

If you want to treat it as a Datatable, it is best to use SQLReader with parameters

>       string con = "Data Source = datascource;" +
>                           "Initial Catalog = catalog;" +
>                           "Persist Security Info=True;" +
>                           "User ID=...;" +
>                           "Password=...;" +
>                           "providerName=System.Data.SqlClient;"; SqlConnection connSQL = new SqlConnection(con);
>         tabla = new DataTable();
>         connSQL.Open();
>         SqlCommand cmd = new SqlCommand("SELECT .... = @par", connSQL);
>         cmd.Parameters.AddWithValue("@par", tu_variable);
>         SqlDataAdapter adapter = new SqlDataAdapter(cmd);
>         Adapter.Fill(tabla);

and at this point in the table you have all the data.