How to Convert Dataset and Datareader to List

Introduction

In this article, we will learn how to convert a DataSet and DataReader to a list using a simple WPF application.

(This is simple only but many people are unaware of it so that is why I posted this article.)

Procedure

Step 1

Create a new project using "File" > "New" > "Project..." > "WPF Application" and name it: "ListConversions".

 


Step 2
 
Now to design your MainWindow.xaml View design part use the following code:

  1. <Window x:Class="DataReaderToList.MainWindow"  
  2.         xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"  
  3.         xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"  
  4.         Title="EmployeeList" Height="350" Width="525">  
  5.     <Grid>  
  6.         <Grid.RowDefinitions>  
  7.             <RowDefinition Height="*"/>  
  8.         </Grid.RowDefinitions>  
  9.         <DataGrid AutoGenerateColumns="False" ItemsSource="{Binding Path=EmployeeList}">  
  10.             <DataGrid.Columns>  
  11.                 <DataGridTextColumn Header="Empno" Binding="{Binding Path=Eno}" Width="100"/>  
  12.                 <DataGridTextColumn Header="Empname" Binding="{Binding Path=Ename}" Width="130"/>  
  13.                 <DataGridTextColumn Header="Job" Binding="{Binding Path=Job}" Width="130"/>  
  14.                 <DataGridTextColumn Header="Salary" Binding="{Binding Path=Salary}" Width="150"/>  
  15.             </DataGrid.Columns>  
  16.         </DataGrid>  
  17.   
  18.     </Grid>  
  19. </Window>  
Step 3
 
You will get the MainWindow.xaml window as below.

 
 
Step 4
 
Now add the configuration settings to the App.config file as iin the following (the username and word may be different in your SQL Server). 
  1. <?xml version="1.0" encoding="utf-8" ?>  
  2. <configuration>  
  3.     <startup>   
  4.         <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />  
  5.     </startup>  
  6.   <connectionStrings>  
  7.     <add  name="ConString" connectionString="Data Source=.; User Id=sa;word=Test123!;Database=WpfPractice" providerName="System.Data.SqlClient"/>  
  8.   </connectionStrings>  
  9. </configuration>  
Step 5
 
The following is the code for converting the DataReader to a List.
  1. using System;  
  2. using System.Collections.ObjectModel;  
  3. using System.Configuration;  
  4. using System.Data;  
  5. using System.Data.SqlClient;  
  6. using System.Windows;  
  7.   
  8. namespace DataReaderToList.ViewModels  
  9. {  
  10.     class DataReaderToListModel  
  11.     {  
  12.         #region Varible declarations  
  13.         private SqlConnection _sqlConnection;  
  14.         private SqlCommand _sqlCommand;  
  15.         private SqlDataReader _sqlDataReader;  
  16.         private string _strsqlcommand;   
  17.         #endregion  
  18.  
  19.         #region Constructor  
  20.         public DataReaderToListModel()  
  21.         {  
  22.             EmployeeList = new ObservableCollection<EmployeeModel>();  
  23.             LoadData();  
  24.         }   
  25.         #endregion  
  26.          
  27.         public ObservableCollection<EmployeeModel> EmployeeList { getset; }  
  28.         #region Method for Getting the data from Databse  
  29.         public void LoadData()  
  30.         {  
  31.             try  
  32.             {  
  33.                 _sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConString"].ConnectionString);  
  34.                 if (_sqlConnection.State != ConnectionState.Open)  
  35.                     _sqlConnection.Open();  
  36.                 _strsqlcommand = "Select *From Emp";  
  37.                 _sqlCommand = new SqlCommand(_strsqlcommand, _sqlConnection);  
  38.                 _sqlDataReader = _sqlCommand.ExecuteReader();  
  39.                 var employeemodellist = new ObservableCollection<EmployeeModel>();  
  40.   
  41.                 while (_sqlDataReader.Read())  
  42.                 {  
  43.                     var employeeModel = new EmployeeModel  
  44.                     {  
  45.                         Eno = _sqlDataReader.GetInt32(_sqlDataReader.GetOrdinal("Empno")),  
  46.                         Ename = _sqlDataReader.GetString(_sqlDataReader.GetOrdinal("Ename")),  
  47.                         Job = _sqlDataReader.GetString(_sqlDataReader.GetOrdinal("Job")),  
  48.                         Salary = _sqlDataReader.GetDecimal(_sqlDataReader.GetOrdinal("Sal"))  
  49.                     };  
  50.                     employeemodellist.Add(employeeModel);  
  51.                 }  
  52.   
  53.                 EmployeeList = employeemodellist;  
  54.                 _sqlConnection.Close();  
  55.             }  
  56.             catch (Exception exception)  
  57.             {  
  58.                 MessageBox.Show("DataLoading Failed beacause of following  Reason \n" + exception.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);  
  59.             }  
  60.         }   
  61.         #endregion  
  62.     }  
  63.     class EmployeeModel  
  64.     {  
  65.         #region Properties  
  66.         public int Eno { getset; }  
  67.         public string Ename { getset; }  
  68.         public string Job { getset; }  
  69.         public decimal Salary { getset; }   
  70.  
  71.         #endregion  
  72.     }  
  73. }  
Step 6
 
The following is the code for converting the DataSet to a List.
  1. using System.Windows;    
  2. using System;    
  3. using System.Collections.ObjectModel;    
  4. using System.Data;    
  5. using System.Data.SqlClient;    
  6. using System.Configuration;    
  7. namespace WpfApplication1.ViewModels    
  8. {    
  9.     class MainWindowViewModelcs    
  10.     {    
  11.         #region Variable Declaration    
  12.         private SqlConnection _cn;    
  13.         private SqlDataAdapter _da;    
  14.         private DataSet _ds;    
  15.         private string _strsqlcommand;     
  16.         #endregion    
  17.         #region Constructor    
  18.         public MainWindowViewModelcs()    
  19.         {    
  20.             EmployeeList = new ObservableCollection<EmployeeModel>();    
  21.             LoadData();    
  22.         }     
  23.         #endregion    
  24.         public ObservableCollection<EmployeeModel> EmployeeList { getset; }    
  25.         #region Method for getting the data from database    
  26.         public void LoadData()    
  27.         {    
  28.             try    
  29.             {    
  30.                 _cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConString"].ConnectionString);    
  31.                 if (_cn.State != ConnectionState.Open)    
  32.                     _cn.Open();    
  33.                 _strsqlcommand = "Select *From Emp";    
  34.                 _da = new SqlDataAdapter(_strsqlcommand, _cn);    
  35.                 _ds = new DataSet();    
  36.                 _da.Fill(_ds, "Employee");    
  37.                 var dt = _ds.Tables["Employee"];    
  38.                 var employeemodellist = new ObservableCollection<EmployeeModel>();    
  39.                 for (int i = 0; i < dt.Rows.Count; i++)    
  40.                 {    
  41.                     var om = new EmployeeModel    
  42.                     {    
  43.                         Eno = Convert.ToInt32(dt.Rows[i]["Empno"]),    
  44.                         Ename = dt.Rows[i]["Ename"].ToString(),    
  45.                         Job = dt.Rows[i]["Job"].ToString(),    
  46.                         Salary = Convert.ToDecimal(dt.Rows[i]["Sal"].ToString()),    
  47.                     };    
  48.                     employeemodellist.Add(om);    
  49.                 }    
  50.                 EmployeeList = employeemodellist;    
  51.                 _cn.Close();    
  52.             }    
  53.             catch (Exception exception)    
  54.             {    
  55.                 MessageBox.Show("DataLoading Failed beacause of following  Reason \n" + exception.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);    
  56.             }    
  57.     
  58.         }     
  59.         #endregion    
  60.            
  61.     }    
  62.     class EmployeeModel    
  63.     {    
  64.         #region Properties    
  65.         public int Eno { getset; }    
  66.         public string Ename { getset; }    
  67.         public string Job { getset; }    
  68.         public decimal Salary { getset; }     
  69.         #endregion    
  70.     }    
  71. }       
Step 7
 
The following is the output window you will get after executing the project.
 
  
 
I hope you find this article useful. Please provide your valuable feedback, question, or comments about this article.