none
Replace DataSet with List<> RRS feed

  • Question

  • I would like to replace the usage of a DataSet with a List<>.

    This is the code snippet I am using. I would like to have the table "employee" represented as a List<> instead of the DataSet I'm currently using.

    string sql_query = "select * from employee";
    my_conn.Open();
    SqlCommand cmd_emp = new SqlCommand(sql_query, my_conn);
    DataSet ds_emp = new DataSet();
    SqlDataAdapter da_emp = new SqlDataAdapter(cmd_emp);
    da_emp.Fill(ds_emp, "employee");
    my_conn.Close();

    Any help would be greatly appreciated. Thanks in advance.

    Thursday, May 3, 2012 12:11 PM

Answers

  • Hi Georby.CS,

    You can use SqlDataReader to do that. I wrote a demo, please refer to it below. The player class have all the properties which you want to query out. Please be patient with the index of the reader and the type conversion.

    class Program
        {
            static void Main(string[] args)
            {
                List<Player> playerList = new List<Player>();
                using (SqlConnection con = new SqlConnection(@"Data Source=.\sqlexpress;Initial Catalog=Test;Integrated Security=True"))
                {
                    string sql = "select PlayerID,PlayerName,TeamID,Age from Player";
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = con;
                    cmd.CommandText = sql;
                    con.Open();
                    SqlDataReader reader = cmd.ExecuteReader();                
                    while (reader.Read())
                    {
                        Player player = new Player();
                        player.ID = Convert.ToInt32(reader[0].ToString());
                        player.Name = reader[1].ToString();
                        player.TeamID = Convert.ToInt32(reader[2].ToString());
                        player.Age = Convert.ToInt32(reader[3].ToString());
                        playerList.Add(player);
                    }
                }
    
            }
        }
    
        class Player
        {
            public int ID { get; set; }
            public string Name { get; set; }
            public int TeamID { get; set; }
            public int Age { get; set; }
        }

    Best Regards

    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    • Marked as answer by Georby.CS Friday, May 4, 2012 6:30 AM
    Friday, May 4, 2012 5:47 AM
    Moderator

All replies

  • Hi Georby.CS,

    Welcome to MSDN Forum.

    Converting a dataset to List<T>, you must specify the type of the elements in the generic collection. Based on the code you posted, I think you want to get a List<employee> list, you can do it by Linq, please refer  to the code below.

    List<employee> listEmp = (from e in ds_emp.AsEnumerable() select new employee {field1 = e.filed1, field2 = e.filed2,...}).ToList();
    Best Regards

    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Friday, May 4, 2012 2:16 AM
    Moderator
  • Thanks for the help.

    But, my purpose is to get rid of the DataSet. I do not want to use the DataSet at all. Instead, I do want a List<> to represent my "employee" table.

    Friday, May 4, 2012 5:11 AM
  • Hi Georby.CS,

    You can use SqlDataReader to do that. I wrote a demo, please refer to it below. The player class have all the properties which you want to query out. Please be patient with the index of the reader and the type conversion.

    class Program
        {
            static void Main(string[] args)
            {
                List<Player> playerList = new List<Player>();
                using (SqlConnection con = new SqlConnection(@"Data Source=.\sqlexpress;Initial Catalog=Test;Integrated Security=True"))
                {
                    string sql = "select PlayerID,PlayerName,TeamID,Age from Player";
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = con;
                    cmd.CommandText = sql;
                    con.Open();
                    SqlDataReader reader = cmd.ExecuteReader();                
                    while (reader.Read())
                    {
                        Player player = new Player();
                        player.ID = Convert.ToInt32(reader[0].ToString());
                        player.Name = reader[1].ToString();
                        player.TeamID = Convert.ToInt32(reader[2].ToString());
                        player.Age = Convert.ToInt32(reader[3].ToString());
                        playerList.Add(player);
                    }
                }
    
            }
        }
    
        class Player
        {
            public int ID { get; set; }
            public string Name { get; set; }
            public int TeamID { get; set; }
            public int Age { get; set; }
        }

    Best Regards

    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    • Marked as answer by Georby.CS Friday, May 4, 2012 6:30 AM
    Friday, May 4, 2012 5:47 AM
    Moderator
  • Then use Linq to Entitities or Linq to SQL or even to make it a real vintage way where you build everything yourself. I don't know who pays you but if you want keeping that then don't do that.  

    But this forum is not for how to use Not Datasets.

    Try these forums for the other productive ways.

    http://social.msdn.microsoft.com/Forums/en-US/linqtosql/threads

    http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/threads

     


    Success
    Cor

    Friday, May 4, 2012 6:14 AM