Convert a multi column data-table in list and use Linq RSS

• • Reply
  

  aarsh

  Participant

  1547 Points

  435 Posts

  Convert a multi column data-table in list and use Linq

  Nov 21, 2012 05:57 AM|LINK

  There are some requiremetns when we need to convert the database as list and fire Linq queries. For single table scenario it's very easy to plan, but for dealing with multiple columns following is the suggested / one of the possible ways :

  Here is my main code file:

  using System;
  using System.Collections.Generic;
  using System.Linq;
  using System.Text;
  
  using MySql.Data.MySqlClient;
  using System.Data;
  
  
  namespace DataTableToList
  {
      class Program
      {
          private static DataTable GetData()
          {
              string strMySqlConnectionString =@"";
              MySqlConnection MSqlConnDummy = new MySqlConnection(strMySqlConnectionString);
              MySqlCommand mSqlCmdSelectCustomers = MSqlConnDummy.CreateCommand();
              //mSqlCmdSelectCustomers.CommandText = @"select * from customers limit 10";
              mSqlCmdSelectCustomers.CommandText = @"select concat_ws(' ',contactLastName, contactFirstName) name, phone, addressLine1, addressLine2, city, state, postalCode from customers limit 10;";
              MySqlDataReader mSqlReader_Customers;
              MSqlConnDummy.Open();
              mSqlReader_Customers = mSqlCmdSelectCustomers.ExecuteReader();
              DataTable dtCustomers = new DataTable();
              dtCustomers.Load(mSqlReader_Customers);
              return dtCustomers;
          }
  
  
          static void Main(string[] args)
          {
              
              List<Customer> customers = RetrieveCustomers();
              foreach(Customer c in customers)
                  Console.WriteLine(c.ToString());
              //King Jean
              Console.ReadKey();
          }
  
          private static List<Customer> RetrieveCustomers()
          {
              DataTable t = GetData();
              List<Customer> customers = new List<Customer>();
              
                  foreach (DataRow dr in t.Rows)
                  {
                      customers.Add(
                      new Customer
                      {
                          Name = dr["name"].ToString(),
                          Phone = dr["phone"].ToString(),
                          Add1 = dr["addressLine1"].ToString(),
                          Add2 = dr["addressLine2"].ToString(),
                          City = dr["city"].ToString(),
                          State = dr["state"].ToString(),
                          Zip = dr["postalCode"].ToString()
                      });
              };
              return customers;
          }
  
  
      }
  }

  
  
  

  
  

  And I added a class called 'Customer'

  
  

  using System;
  using System.Collections.Generic;
  using System.Linq;
  using System.Text;
  
  namespace DataTableToList
  {
      class Customer
      {
          public string Name { get; set; }
          public string Phone { get; set; }
          public string Add1 { get; set; }
          public string Add2 { get; set; }
          public string City { get; set; }
          public string State { get; set; }
          public string Zip { get; set; }
  
          public override string ToString()
          {
              return string.Format("Name: {0}\nPhone {1}\n{2},\n{3}\n{4}, {5} {6}", Name, Phone, Add1, Add2, City, State, Zip);
          }
      }
  }

  
  
  

  
  

  http://sdrv.ms/TXfAYE to downlod the sample example, just add the connection string and you should be good to go.

  I have prepared this example using

  Visual C# Express Edition

  Database Server : MySQL

  Click here to browse other samples I've posted ...

  • Edited by aarsh on Nov 21, 2012 04:59 PM