my DataRow object is emtpy no matter how I try to initialize it. Here is the source code:
private DataTable ReadFileIntoDb(MemoryStream file)
{
file.Position = 0;
var sr = new StreamReader(file,Encoding.GetEncoding(1251),true);
var dt = new DataTable();
Console.WriteLine("datatable success");
dt.Columns.Add(new DataColumn("description", typeof(String)));
dt.Columns.Add(new DataColumn("code", typeof(String)));
dt.Columns.Add(new DataColumn("cardnumber", typeof(String)));
dt.Columns.Add(new DataColumn("costcentrecode", typeof(int)));
dt.Columns.Add(new DataColumn("costcentre", typeof(String)));
dt.Columns.Add(new DataColumn("article_description", typeof(String)));
dt.Columns.Add(new DataColumn("trans_date", typeof(DateTime)));
dt.Columns.Add(new DataColumn("article_price", typeof(Double)));
dt.Columns.Add(new DataColumn("article_quantity", typeof(Double)));
dt.Columns.Add(new DataColumn("netto", typeof(Double)));
dt.Columns.Add(new DataColumn("brutto", typeof(Double)));
dt.Columns.Add(new DataColumn("mwst", typeof(Double)));
dt.Columns.Add(new DataColumn("beleg", typeof(Double)));
dt.Columns.Add(new DataColumn("oberverkaufsgruppe", typeof(int)));
dt.Columns.Add(new DataColumn("house", typeof(String)));
dt.Columns.Add(new DataColumn("id", typeof(String)));
//1. row gets skipped
string line = sr.ReadLine();
Console.WriteLine("line contains:" + line);
line = sr.ReadLine();
do
{
string[] itemArray = line.Split(';');
DataRow row = dt.NewRow();
row.ItemArray = itemArray; //doesnt work and throws ArgumentException
itemArray.CopyTo(row.ItemArray, 0); //doesnt work either
for (int i = 0; i < 14; i++) //also doesnt work and leaves it empty
{
row.ItemArray[i] = itemArray[i];
Console.WriteLine(row.ItemArray[i]);
}
row["id"] = Guid.NewGuid().ToString();
row["house"] = "";
dt.Rows.Add(row);
line = sr.ReadLine();
} while (!string.IsNullOrEmpty(line));
ReadFileIntoDb gets the content of a csv file as a parameter. Right now i reduced the csv content to only two lines which of one is just the header. The content looks like this and gets parsed successfully into var sr: Bewohner;1102284;1102284;42600;ASG ROT;Menü (Inklusivleistung);01.07.2022;0;1;;;;;2000003
As you can see this file contains 14 elements, some of which are empty. My DataTable also contains 14 columns and 2 additional which I set inside the code however and their values do not come from the csv. After performing the database operations my tables remain empty because I believe that row.ItemArray always remains empty so nothing gets added to the database. But I don't understand why it stays empty and why i get a ArgumentException. The source array itemArray is NOT empty and is also of length 14. So why does the exception get thrown if row.ItemArray is bigger in length? It has 16.
Look into this ,I change the input string a bit
The line i added is
for (int i = 0; i < itemArray.Length; i++)
{
if (string.IsNullOrWhiteSpace(itemArray[i]))
{
itemArray[i] = null;
}
}
for replacing the empty values as null in the given input , without this i got the error
Unhandled exception. System.ArgumentException: Input string was not in a correct format.Couldn't store <> in netto Column. Expected type is Double.
---> System.FormatException: Input string was not in a correct format.
at System.Number.ThrowOverflowOrFormatException(ParsingStatus status, TypeCode type)
at System.String.System.IConvertible.ToDouble(IFormatProvider provider)
at System.Data.Common.DoubleStorage.Set(Int32 record, Object value)
at System.Data.DataColumn.set_Item(Int32 record, Object value)
--- End of inner exception stack trace ---
at System.Data.DataColumn.set_Item(Int32 record, Object value)
at System.Data.DataRow.set_ItemArray(Object[] value)
at Program.Main()
Command terminated by signal 6
using System;
using System.Data;
public class Program
{
public static void Main()
{
string sp="Bewohner;1102284;1102284;42600;ASG ROT;Menü (Inklusivleistung);01.07.2022;0;1;;;;;2000003^Bewohner2;11022842;11022824;426200;ASG2 ROT;Menü (Inklusivleistung);01.07.2022;0;1;;;;;2000004";
var dt = new DataTable();
Console.WriteLine("datatable success");
dt.Columns.Add(new DataColumn("description", typeof(String)));
dt.Columns.Add(new DataColumn("code", typeof(String)));
dt.Columns.Add(new DataColumn("cardnumber", typeof(String)));
dt.Columns.Add(new DataColumn("costcentrecode", typeof(int)));
dt.Columns.Add(new DataColumn("costcentre", typeof(String)));
dt.Columns.Add(new DataColumn("article_description", typeof(String)));
dt.Columns.Add(new DataColumn("trans_date", typeof(DateTime)));
dt.Columns.Add(new DataColumn("article_price", typeof(Double)));
dt.Columns.Add(new DataColumn("article_quantity", typeof(Double)));
dt.Columns.Add(new DataColumn("netto", typeof(Double)));
dt.Columns.Add(new DataColumn("brutto", typeof(Double)));
dt.Columns.Add(new DataColumn("mwst", typeof(Double)));
dt.Columns.Add(new DataColumn("beleg", typeof(Double)));
dt.Columns.Add(new DataColumn("oberverkaufsgruppe", typeof(int)));
dt.Columns.Add(new DataColumn("house", typeof(String)));
dt.Columns.Add(new DataColumn("id", typeof(String)));
string[] mainarray = sp.Split('^');
for(int a=0;a<mainarray.Length;a++)
{
string[] itemArray = mainarray[a].Split(';');
DataRow row = dt.NewRow();
for (int i = 0; i < itemArray.Length; i++)
{
if (string.IsNullOrWhiteSpace(itemArray[i]))
{
itemArray[i] = null;
}
}
row.ItemArray = itemArray; //doesnt work and throws ArgumentException
itemArray.CopyTo(row.ItemArray, 0); //doesnt work either
for (int i = 0; i < 14; i++) //also doesnt work and leaves it empty
{
row.ItemArray[i] = itemArray[i];
//Console.WriteLine(row.ItemArray[i]);
}
row["id"] = Guid.NewGuid().ToString();
row["house"] = "";
dt.Rows.Add(row);
}
foreach(DataRow dataRow in dt.Rows)
{var ix = 0;
foreach(var item in dataRow.ItemArray)
{
Console.WriteLine(ix+"-----"+item);
ix++;
}
}
}
}
OUTPUT :
datatable success
0-----Bewohner
1-----1102284
2-----1102284
3-----42600
4-----ASG ROT
5-----Menü (Inklusivleistung)
6-----01/07/2022 00:00:00
7-----0
8-----1
9-----
10-----
11-----
12-----
13-----2000003
14-----
15-----ccf0a446-610e-4396-896d-7e8c4a43f729
0-----Bewohner2
1-----11022842
2-----11022824
3-----426200
4-----ASG2 ROT
5-----Menü (Inklusivleistung)
6-----01/07/2022 00:00:00
7-----0
8-----1
9-----
10-----
11-----
12-----
13-----2000004
14-----
15-----0c9cb9b1-8640-4626-8790-0d80fad9d8da