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.
  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>
  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;
  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
  19. #region Constructor
  20. public DataReaderToListModel()
  21. {
  22. EmployeeList = new ObservableCollection<EmployeeModel>();
  23. LoadData();
  24. }
  25. #endregion
  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>();
  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. }
  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; }
  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. }
  58. }
  59. #endregion
  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.