George George - 2 months ago 6
C# Question

linq manipulate list of custom object

Here is my code so far, returning some data from SQL server:

try
{
connection.Open();
cmd.CommandText = "select * from dbo.contact";
SqlDataReader dr = cmd.ExecuteReader();
List<person> persons = new List<person>();
person person;
while (dr.Read())
{
person = new person();
persons.Add(person);
}
persons.Count();
}
catch {

}


what I want to next is to extract specific records from the list by using LINQ. For example, a linq that returns all persons that have a DName='D'.

Here is the code of the object:

public class person
{
public string idvalue { get; set; }

public string DName { get; set; }

public string FName { get; set; }

}


Any help is welcome!

Answer

This is how I'd do it:

    static void Main(string[] args)
    {
        List<person> FilteredPeople = GetPeople("D");
    }
    static List<person> GetPeople(string condition)
    {
        try
        {
            connection.Open();
            using (SqlCommand command = new SqlCommand("select * from dbo.contact WHERE DName LIKE @mycondition", connection))
            {
                 cmd.Parameters.Add(new SqlParameter("mycondition", condition));
                 SqlDataReader dr = cmd.ExecuteReader();
                 List<person> persons = new List<person>();
                while (dr.Read())
                {
                     person p = new person();
                     p.idValue = dr["Id"].ToString();
                     p.DName = dr["DName"].ToString();
                     p.FName = dr["FName"].ToString();
                     persons.Add(p);
                 }
                 return persons;
            }
        }
        catch { }
    }

As others pointed out, it is wise to minimize the load on the database, therefore it's better to insert the condition on the SQLCommand so as to draw only the necessary records.

EDIT: woops forgot to close the connection, I suggest inserting another "using" clause for the sqlConnection, so that it disposes itself once it reaches the end (using (SqlConnection connection = new SqlConnection(connectionString)) {})

Comments