Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SqlDataReader to read into List<string>

I am writing a method in C# to query a SQL Server Express database from a WCF service. I have to use ADO.NET to do this (then rewrite it with LINQ later on).

The method takes two strings (fname, lname) then returns a "Health Insurance NO" attribute from the matching record. I want to read this into a list (there are some other attribs to retrieve as well).

The current code returns an empty list. Where am I going wrong?

public List<string> GetPatientInfo(string fname, string lname)
{
    string connString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=C:\\Users\\xxxx\\Documents\\Visual Studio 2010\\Projects\\ADOWebApp\\ADOWebApp\\App_Data\\ADODatabase.mdf;Integrated Security=True;User Instance=True";

    SqlConnection conn = new SqlConnection(connString);

    string sqlquery = "SELECT Patient.* FROM Patient WHERE ([First Name] = '"+fname+"') AND ([Last Name] = '"+lname+"')";
    SqlCommand command = new SqlCommand(sqlquery, conn);
    DataTable dt = new DataTable();

    List<string> result = new List<string>();

    using (conn)
    {
        conn.Open();

        using (SqlDataReader reader = command.ExecuteReader())
        {
            while (reader != null && reader.Read())
            {
               dt.Load(reader);
               result.Add(Convert.ToString(reader["Health Insurance NO"]));
            }
        }
     }

     return result;
}
like image 413
reallybadatmath Avatar asked Oct 10 '13 15:10

reallybadatmath


People also ask

What is ExecuteReader ()?

ExecuteReader() Sends the CommandText to the Connection and builds a SqlDataReader. ExecuteReader(CommandBehavior) Sends the CommandText to the Connection, and builds a SqlDataReader using one of the CommandBehavior values.

What does SqlDataReader return?

As explained earlier, the SqlDataReader returns data via a sequential stream. To read this data, you must pull data from a table row-by-row Once a row has been read, the previous row is no longer available.

What is SqlDataReader in Ado net?

ADO.NET SqlDataReader Class. This class is used to read data from SQL Server database. It reads data in forward-only stream of rows from a SQL Server database. it is sealed class so that cannot be inherited. It inherits DbDataReader class and implements IDisposable interface.

What is SqlDataReader explain it with relevant example?

The SqlDataReader is used to read a row of record at a time which is got using SqlCommand. It is read only, which means we can only read the record; it can not be edited. And also it is forward only, which means you can not go back to a previous row (record).


1 Answers

You are trying to load a DataTable via DataTable.Load >in a loop<. You just need that once. You're also using reader.Read() in the loop. SqlDataReader.Read() advances the reader to the next record without to consume it. If you're going to use DataTable.Load you don't need to read the reader first. So you just have to remove the loop completely to load the table.

But since you want to return a list you don't need the DataTable at all, just loop the reader:

List<string> result = new List<string>();
using (conn)
{
    conn.Open();
    using (SqlDataReader reader = command.ExecuteReader())
    {
        while(reader.Read())
        {
            result.Add(Convert.ToString(reader["Health Insurance NO"]));
        }
    }
}

Apart from that, you are open for sql-injection without sql-parameters.

like image 161
Tim Schmelter Avatar answered Oct 01 '22 19:10

Tim Schmelter