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:

<Window x:Class="DataReaderToList.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        Title="EmployeeList" Height="350" Width="525">
    <Grid>
        <Grid.RowDefinitions>
            <RowDefinition Height="*"/>
        </Grid.RowDefinitions>
        <DataGrid AutoGenerateColumns="False" ItemsSource="{Binding Path=EmployeeList}">
            <DataGrid.Columns>
                <DataGridTextColumn Header="Empno" Binding="{Binding Path=Eno}" Width="100"/>
                <DataGridTextColumn Header="Empname" Binding="{Binding Path=Ename}" Width="130"/>
                <DataGridTextColumn Header="Job" Binding="{Binding Path=Job}" Width="130"/>
                <DataGridTextColumn Header="Salary" Binding="{Binding Path=Salary}" Width="150"/>
            </DataGrid.Columns>
        </DataGrid>

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

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <startup>
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
    </startup>
  <connectionStrings>
    <add  name="ConString" connectionString="Data Source=.; User Id=sa;word=Test123!;Database=WpfPractice" providerName="System.Data.SqlClient"/>
  </connectionStrings>
</configuration>

Step 5

 

The following is the code for converting the DataReader to a List.

using System;
using System.Collections.ObjectModel;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Windows;

namespace DataReaderToList.ViewModels
{
    class DataReaderToListModel
    {
        #region Varible declarations
        private SqlConnection _sqlConnection;
        private SqlCommand _sqlCommand;
        private SqlDataReader _sqlDataReader;
        private string _strsqlcommand;
        #endregion

        #region Constructor
        public DataReaderToListModel()
        {
            EmployeeList = new ObservableCollection<EmployeeModel>();
            LoadData();
        }
        #endregion

        public ObservableCollection<EmployeeModel> EmployeeList { get; set; }
        #region Method for Getting the data from Databse
        public void LoadData()
        {
            try
            {
                _sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConString"].ConnectionString);
                if (_sqlConnection.State != ConnectionState.Open)
                    _sqlConnection.Open();
                _strsqlcommand = "Select *From Emp";
                _sqlCommand = new SqlCommand(_strsqlcommand, _sqlConnection);
                _sqlDataReader = _sqlCommand.ExecuteReader();
                var employeemodellist = new ObservableCollection<EmployeeModel>();

                while (_sqlDataReader.Read())
                {
                    var employeeModel = new EmployeeModel
                    {
                        Eno = _sqlDataReader.GetInt32(_sqlDataReader.GetOrdinal("Empno")),
                        Ename = _sqlDataReader.GetString(_sqlDataReader.GetOrdinal("Ename")),
                        Job = _sqlDataReader.GetString(_sqlDataReader.GetOrdinal("Job")),
                        Salary = _sqlDataReader.GetDecimal(_sqlDataReader.GetOrdinal("Sal"))
                    };
                    employeemodellist.Add(employeeModel);
                }

                EmployeeList = employeemodellist;
                _sqlConnection.Close();
            }
            catch (Exception exception)
            {
                MessageBox.Show("DataLoading Failed beacause of following  Reason \n" + exception.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
            }
        }
        #endregion
    }
    class EmployeeModel
    {
        #region Properties
        public int Eno { get; set; }
        public string Ename { get; set; }
        public string Job { get; set; }
        public decimal Salary { get; set; }

        #endregion
    }
}

Step 6

 

The following is the code for converting the DataSet to a List.

using System.Windows;
using System;
using System.Collections.ObjectModel;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace WpfApplication1.ViewModels
{
    class MainWindowViewModelcs
    {
        #region Variable Declaration
        private SqlConnection _cn;
        private SqlDataAdapter _da;
        private DataSet _ds;
        private string _strsqlcommand;
        #endregion
        #region Constructor
        public MainWindowViewModelcs()
        {
            EmployeeList = new ObservableCollection<EmployeeModel>();
            LoadData();
        }
        #endregion
        public ObservableCollection<EmployeeModel> EmployeeList { get; set; }
        #region Method for getting the data from database
        public void LoadData()
        {
            try
            {
                _cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConString"].ConnectionString);
                if (_cn.State != ConnectionState.Open)
                    _cn.Open();
                _strsqlcommand = "Select *From Emp";
                _da = new SqlDataAdapter(_strsqlcommand, _cn);
                _ds = new DataSet();
                _da.Fill(_ds, "Employee");
                var dt = _ds.Tables["Employee"];
                var employeemodellist = new ObservableCollection<EmployeeModel>();
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    var om = new EmployeeModel
                    {
                        Eno = Convert.ToInt32(dt.Rows[i]["Empno"]),
                        Ename = dt.Rows[i]["Ename"].ToString(),
                        Job = dt.Rows[i]["Job"].ToString(),
                        Salary = Convert.ToDecimal(dt.Rows[i]["Sal"].ToString()),
                    };
                    employeemodellist.Add(om);
                }
                EmployeeList = employeemodellist;
                _cn.Close();
            }
            catch (Exception exception)
            {
                MessageBox.Show("DataLoading Failed beacause of following  Reason \n" + exception.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
            }

        }
        #endregion

    }
    class EmployeeModel
    {
        #region Properties
        public int Eno { get; set; }
        public string Ename { get; set; }
        public string Job { get; set; }
        public decimal Salary { get; set; }
        #endregion
    }
}

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.