How can i convert  the contents of a list into a dataset and make it available anywhere in my application

Olukayode Oluwole
Olukayode Oluwole used Ask the Experts™
on
In my c# application I need to read a set of data  from a table "stateorigin"
and get it ready as a dataset  for preparing a crystal report

I have 2 scripts

My first script defines the  fields in the dataset ( see script below)

  //Creates a new dataset, 2 datatables and adds data to them
   _dsData = new DataSet("TestDataSet");

  private void CreateDataSet2()
        {
            //Creates a new dataset, 2 datatables and adds data to them
            _dsData = new DataSet("TestDataSet");

            //byte[] arrBytes;
            //System.IO.MemoryStream ms;

            using (DataTable dt = new DataTable("stateorigin"))
            {
                dt.Columns.Add("stateid", typeof(int));
                dt.Columns.Add("statedescription", typeof(string));
              
                GlobalConfig.Connection.GetStateCode_All();

                _dsData.Tables.Add(dt);
            }
         
        }

Open in new window


So basically I want to read 2 fields  stateid and statedescription  from stateorigin table

Already during the creation of my application i have  a script that get a list of these same fields BUT

into a list.  ( See  script below )

[public List<StateOriginModel> GetStateCode_All()
        {
             List<StateOriginModel> results = new List<StateOriginModel>();

            using (var conn = new NpgsqlConnection(pgrstring))
            {
                using (NpgsqlCommand command = new NpgsqlCommand("public.spstateorigincode_getall", conn))
                {
                    conn.Open();
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.Add(new NpgsqlParameter("countrycodex", NpgsqlTypes.NpgsqlDbType.Varchar) { Direction = ParameterDirection.Input, Value = LoginDetails.staticcountry.ToUpper() });

                    var reader = command.ExecuteReader();
                   
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            StateOriginModel model = new StateOriginModel();

                           
                            if ((string)reader["StateID"] != null)
                            {
                                model.Id = (int)reader["id"];
                                model.StateID = (string)reader["StateID"];  
                                model.StateDescription = (string)reader["StateDescription"];  
                                results.Add(model);
                            }
                        }
                    }
                }
            }
            return results;
        }[/code]

Using the debugger i know that the List is populated  into a list called results

My questions are

1. Is there a way for me to  assign the contents of the list results to the dataset    _dsData

2. I need to make the contents of _dsData   available to the calling script 1
How do i define _dsData as a static class so that  i can get its data  anywhere in my application


Thanks

Olukay
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
Have a look at this code snippet: https://www.dotnetperls.com/convert-list-datatable
Eduard GherguArchitect - Coder - Mentor

Commented:
Hi,

If you don't have any other reason for having the results list, you can use a SqlDataAdapter for reading the data and fill the DataSet. You have a sample here:
https://www.webtrainingroom.com/adonet/dataset
Olukayode OluwoleSystems Analyst

Author

Commented:
Hello Mike
The topic is related to Crystal Reports  ( Please  ask Eric who will confirm
that this is a first step in creating a .NET report in Crystal Reports)

I am trying to create a dataset  that will be an input
into my CrystalReportViewer

I was trying to narrow the issue to where i currently have an error so that
the expert helping  will focus on where i have a problem

I need to get back to Eric on my efforts and how far i have gone with his suggestion

How do I progress

Thanks

Olukay
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Learn More!
Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
I have already provided a link to transform a list into a datatable (and that datatable can be added to a dataset). Also the idea of Eduard to fill the dataset right from the database can also be used.
Olukayode OluwoleSystems Analyst

Author

Commented:
Thanks Eric for opening this thread again.

I need both the list and the DataTable so will be going with the snippet you sent

I have tried to get the data table working  with the 2 scripts below.

The first is my old list  and it then call  the new  script that should prepare the DataTable  (see below)

[] public List<StateOriginModel> GetStateCode_All()
        {
            List<StateOriginModel> results = new List<StateOriginModel>();
                       
            using (var conn = new NpgsqlConnection(pgrstring))
            {
                using (NpgsqlCommand command = new NpgsqlCommand("public.spstateorigincode_getall", conn))
                {
                    conn.Open();
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.Add(new NpgsqlParameter("countrycodex", NpgsqlTypes.NpgsqlDbType.Varchar) { Direction = ParameterDirection.Input, Value = LoginDetails.staticcountry.ToUpper() });

                    var reader = command.ExecuteReader();
                    // Bring in Parameters to limit selection to Login.Staticcompany
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            StateOriginModel model = new StateOriginModel();

                            if ((string)reader["StateID"] != null)
                            {
                                model.Id = (int)reader["id"];
                                model.StateID = (string)reader["StateID"];  
                                model.StateDescription = (string)reader["StateDescription"];
                                results.Add(model);
                               
                            }
                        }
                    }
                }
            }
           
            DataTable table = ConvertListToDataTable(results);
            return results;
        }

        private DataTable ConvertListToDataTable(List<StateOriginModel> results)
        {
            DataTable table = new DataTable();

            // Get max columns.
            int columns = 0;
            foreach (var array in results)
            {
                if (array.Length > columns)
                {
                    columns = array.Length;
                }
            }
           
            // Add columns.
            for (int i = 0; i < columns; i++)
            {
                table.Columns.Add();
            }

            // Add rows.
            foreach (var array in results)
            {
                table.Rows.Add(array);
            }

            return table;
        }[/code]

Where I have an issue is that my list is not being seen as an array  

So array length  is zero even though it should not be  (see the 2 screens below)

Array Length being Reported as Zero
And then because array length is zero i have another error down the line (see below)

Final Error During Debug
I have tried to see how to specify that the list should be prepared as an array but i keep

having error.

Please assist to check what i might be doing wrong

Olukay
Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
stupid question but I will ask anyway: do you have data in your list?
Eduard GherguArchitect - Coder - Mentor

Commented:
Hi,
foreach (var array in results)
            {
                table.Rows.Add(array);
            }


array is an instance of StateOriginModel class, is not an array. You can implement a method to convert a such object to an array, if you want.
Olukayode OluwoleSystems Analyst

Author

Commented:
Answer to the first question is YES  . The List contains data
The list has  36 rows  and below is a typical row data  as shown in the debugger
List Has data
Typical data in List But Strangely  showing Array length as Zero

The new error screen after trying Eduard suggestion is  attached below

New  Array Error after Trying Eduard Suggestion is shown below

New Error After Eduards SuggestionI assumed Eduard wants me to comment out everything and just have what he has
in his suggestion   ie

foreach (var array in results)             {
                table.Rows.Add(array);
            }

Thats exactly what  I have done

What should i try to resolve this error

Thanks

Olukay
Eduard GherguArchitect - Coder - Mentor

Commented:
Hi,
How your method for creating an array from StateOriginModel looks like?
Senior .Net Consultant
Top Expert 2016
Commented:
I have found another method for you:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace WindowsFormsApp3
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            List<StateOriginModel> results = new List<StateOriginModel>();
            for (int i = 0; i < 10; i++)
            {
                StateOriginModel model = new StateOriginModel();
                model.Id = i;
                model.StateID = "StateID " + i.ToString();
                model.StateDescription = "StateDescription" + i.ToString();
                results.Add(model);
            }

            DataTable table = ToDataTable(results); // ConvertListToDataTable(results);
            dataGridView1.DataSource = table;
        }

        public static DataTable ToDataTable<T>(IList<T> data)
        {
            PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));
            DataTable table = new DataTable();
            foreach (PropertyDescriptor prop in properties)
                table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);

            foreach (T item in data)
            {
                DataRow row = table.NewRow();
                foreach (PropertyDescriptor prop in properties)
                    row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
                table.Rows.Add(row);
            }
            return table;
        }
    }

    public class StateOriginModel
    {

        public int Id { get; set; }
        public string StateID { get; set; }
        public string StateDescription { get; set; }
    }
}

Open in new window

Olukayode OluwoleSystems Analyst

Author

Commented:
Thanks  for the suggestions

Will now go ahead to fix the last 2 steps of generating  the report.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial