Convert Dataset and Datareader to List

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.)

Download the attached files for reference.

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. 
   
2. <Window x:Class="DataReaderToList.MainWindow"  
3.         xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"  
4.         xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"  
5.         Title="EmployeeList" Height="350" Width="525">  
6.     <Grid>  
7.         <Grid.RowDefinitions>  
8.             <RowDefinition Height="*"/>  
9.         </Grid.RowDefinitions>  
10.         <DataGrid AutoGenerateColumns="False" ItemsSource="{Binding Path=EmployeeList}">  
11.             <DataGrid.Columns>  
12.                 <DataGridTextColumn Header="Empno" Binding="{Binding Path=Eno}" Width="100"/>  
13.                 <DataGridTextColumn Header="Empname" Binding="{Binding Path=Ename}" Width="130"/>  
14.                 <DataGridTextColumn Header="Job" Binding="{Binding Path=Job}" Width="130"/>  
15.                 <DataGridTextColumn Header="Salary" Binding="{Binding Path=Salary}" Width="150"/>  
16.             </DataGrid.Columns>  
17.         </DataGrid>  
18.   
19.     </Grid>  
20. </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 { get; set; }  
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 { get; set; }  
67.         public string Ename { get; set; }  
68.         public string Job { get; set; }  
69.         public decimal Salary { get; set; }   
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 { get; set; }    
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 { get; set; }    
66.         public string Ename { get; set; }    
67.         public string Job { get; set; }    
68.         public decimal Salary { get; set; }     
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.

Supporting files:

ListConversions.rar

Emp.rar