SqlDataReader to read in List <string>

Question

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

The method takes two lines ( fname, lname ), and then returns the Health Insurance attribute from the corresponding record. I want to read this in a list (there are other attributes to retrieve).

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

 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; } 

Answer 1

You are trying to load a DataTable through DataTable.Load > in a <loop. You just need it once. You also use reader.Read() in a loop. SqlDataReader.Read() pushes the reader to the next record without consuming it. If you are going to use DataTable.Load , you do not need to read the reader first. Therefore, you just need to completely remove the loop to load the table.

But since you want to return the list, you don't need a 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"])); } } } 

In addition, you are open to sql-injection without sql parameters.

Answer 2

I would do it like this:

  conn.Open(); using (SqlDataReader reader = command.ExecuteReader()) { dt.Load(reader); } foreach (var row in dt.AsEnumerable()) { result.Add(row["Health Insurance NO"].ToString()); }