Olukayode Oluwole
asked on
How can i convert the contents of a list into a dataset and make it available anywhere in my application
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");
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.spst ateoriginc ode_getall ", conn))
{
conn.Open();
command.CommandType = CommandType.StoredProcedur e;
command.Parameters.Add(new NpgsqlParameter("countryco dex", NpgsqlTypes.NpgsqlDbType.V archar) { 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["StateDescr iption"];
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
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);
}
}
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.spst
{
conn.Open();
command.CommandType = CommandType.StoredProcedur
command.Parameters.Add(new
var reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
StateOriginModel model = new StateOriginModel();
if ((string)reader["StateID"]
{
model.Id = (int)reader["id"];
model.StateID = (string)reader["StateID"];
model.StateDescription = (string)reader["StateDescr
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
Have a look at this code snippet: https://www.dotnetperls.com/convert-list-datatable
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
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
ASKER
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
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
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.
ASKER
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.spst ateoriginc ode_getall ", conn))
{
conn.Open();
command.CommandType = CommandType.StoredProcedur e;
command.Parameters.Add(new NpgsqlParameter("countryco dex", NpgsqlTypes.NpgsqlDbType.V archar) { 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["StateDescr iption"];
results.Add(model);
}
}
}
}
}
DataTable table = ConvertListToDataTable(res ults);
return results;
}
private DataTable ConvertListToDataTable(Lis t<StateOri ginModel> 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)
And then because array length is zero i have another error down the line (see below)
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
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.spst
{
conn.Open();
command.CommandType = CommandType.StoredProcedur
command.Parameters.Add(new
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"]
{
model.Id = (int)reader["id"];
model.StateID = (string)reader["StateID"];
model.StateDescription = (string)reader["StateDescr
results.Add(model);
}
}
}
}
}
DataTable table = ConvertListToDataTable(res
return results;
}
private DataTable ConvertListToDataTable(Lis
{
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)
And then because array length is zero i have another error down the line (see below)
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
stupid question but I will ask anyway: do you have data in your list?
Hi,
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.
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.
ASKER
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
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
I 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
The list has 36 rows and below is a typical row data as shown in the debugger
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
I 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
Hi,
How your method for creating an array from StateOriginModel looks like?
How your method for creating an array from StateOriginModel looks like?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the suggestions
Will now go ahead to fix the last 2 steps of generating the report.
Will now go ahead to fix the last 2 steps of generating the report.