Returns List of Customers from Northwind DB, and Displays Customer uisng LINQ

using System;

using System.Collections.Generic;

using System.Data;

using System.Linq;

using System.Data.SqlClient;

namespace ConsoleApplication2

{

class Customer

{

public string CustomerId { get; set; }

public string CompanyName { get; set; }

public string ContactName { get; set; }

public string Address { get; set; }

public string City { get; set; }

public string Country { get; set; }

public static List<Customer> getCustomers()

{

using (SqlConnection objcon = new SqlConnection("Data source=(local);Initial Catalog=northwind;user id=sa;password=sa"))

{

SqlCommand objcmd = new SqlCommand("Select CustomerId, CompanyName, ContactName, Address, City, Country from Customers", objcon);

SqlDataAdapter objAda = new SqlDataAdapter(objcmd);

DataSet ds = new DataSet();

objAda.Fill(ds, "Customers");

List<Customer> lstcust = new List<Customer>();

foreach (DataRow row in ds.Tables[0].Rows)

{

lstcust.Add(new Customer

{

CustomerId = (string)row[0],

CompanyName = (string)row[1],

ContactName = (string)row[2],

Address = (string)row[3],

City = (string)row[4],

Country = (string)row[5]

});

}

return lstcust;

}

}

}

class Program

{

static void Main(string[] args)

{

List<Customer> lstCustomers = Customer.getCustomers();

var customers = from c in lstCustomers

where c.Country == "USA"

select c;

//Or

//var customers = lstCustomers.Where(p => p.Country == "USA");

foreach (var r in customers)

Console.WriteLine("Customer ID: {0} \nCompanyName: {1} \nContactName: {2} \nAddress : {3} \nCity : {4} \nCountry : {5}\n",

r.CustomerId, r.CompanyName, r.ContactName, r.Address, r.City, r.Country);

Console.ReadLine();

}

}

}