It was a long time ago that I was working with C#. And im making a program that will take in 3 sql database tables and to save them in a list.
I followed a guide on how to handle this, but im not sure what is wrong with it. Im getting an output of the data when i print it out, but 0 is going into my list.
Please help me out, and thanks in advance :)
Code:
List<Product> products = null;
string connetionString;
SqlConnection cnn;
connetionString = @"";
cnn = new SqlConnection(connetionString);
cnn.Open();
if (cnn != null)
{
Console.WriteLine("Connection Open!");
}
SqlCommand command = new SqlCommand("SELECT data1.zip_code ZipCode, data1.order_date as OrderDate, data1.order_number as OrderNumber, data1.first_name as firstName, data1.last_name as lastName, data2.order_number as OrderNumb2Two, data2.sku_id as SkuID, data2.quantity as Quantity, data2.price as Price, data3.article_name as articleName, data3.[weight] as Weight FROM data1,data2,data3", cnn);
var dataReader = command.ExecuteReader();
/*
while (dataReader.Read())
{
Console.WriteLine(dataReader["ZipCode"].ToString());
Console.WriteLine(dataReader["OrderDate"].ToString());
Console.WriteLine(dataReader["OrderNumber"].ToString());
Console.WriteLine(dataReader["firstName"].ToString());
Console.WriteLine(dataReader["lastName"].ToString());
Console.WriteLine(dataReader["SkuID"].ToString());
Console.WriteLine(dataReader["Quantity"].ToString());
Console.WriteLine(dataReader["Price"].ToString());
Console.WriteLine(dataReader["articleName"].ToString());
Console.WriteLine(dataReader["Weight"].ToString());
}*/
products = GetList<Product>(dataReader);
if(products != null)
{
Console.WriteLine(products);
}
else
{
Console.WriteLine(products);
}
foreach(object o in products)
{
Console.WriteLine(o);
}
cnn.Close();
}
public class Product
{
public int SkuID { get; set; }
public Int32 Quantity { get; set; }
public Int32 Weight { get; set; }
public Int32 Price { get; set; }
public Int64 OrderNumber { get; set; }
public Int64 OrderNumb2Two { get; set; }
public string articleName { get; set; }
public DateTime OrderDate { get; set; }
public int ZipCode { get; set; }
public string firstName { get; set; }
public string lastName { get; set; }
}
public static List<T> GetList<T>(IDataReader reader)
{
List<T> list = new List<T>();
while (reader.Read())
{
var type = typeof(T);
T obj = (T)Activator.CreateInstance(type);
foreach (var prop in type.GetProperties())
{
var propType = prop.PropertyType;
prop.SetValue(obj, Convert.ChangeType(reader[prop.Name].ToString(), propType));
//Console.WriteLine(Convert.ChangeType(reader[prop.Name].ToString(), propType));
}
list.Add(obj);
}
return list;
CodePudding user response：
Among other issues, as stated in the comments, Convert.ChangeType is not appropriate usage in this specific case. ChangeType does not actually mutate the value at all, it only returns the object that is equivalent to the requested type, if the original object is already in an equivalent type.
Read more on Type Conversion in .NET
In this case, you should be experiencing this error:
Run-time exception (line 103): Input string was not in a correct format.
Stack Trace:
[System.FormatException: Input string was not in a correct format.]
at System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal)
at System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info)
at System.String.System.IConvertible.ToInt32(IFormatProvider provider)
at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider)
at System.Convert.ChangeType(Object value, Type conversionType)
at Program.GetList[T](IDataReader reader) :line 103
at Program.Main() :line 45
The key here is that you have deliberately converted the values into string, and string types are not equivalent to value types like int and DateTime.
If you want to use ChangeType then you need preserve the DbType by using GetValue:
prop.SetValue(obj, Convert.ChangeType(reader.GetValue(reader.GetOrdinal(prop. Name)), propType));
or we can break that down to be more obvious:
int columnIndex = reader.GetOrdinal(prop. Name);
object columnValue = reader.GetValue(columnIndex);
prop.SetValue(obj, Convert.ChangeType(columnValue, propType));
NOTE: This is still only going to work if you make sure that the type in the database is equivalent to the type in the corresponding property on the object you are trying to set.
We would usually store postal or zip codes in a database using a character based type, NOT an integer type, as this will preserve leading zeros and many jurisdictions use non-numeric characters for some of their postal codes... but that is another discussion, the point is,
Convert.ChangeType()will still fail if the types are not correct.
This fiddle shows a simple example where Convert.ChangeType() will still fail: https://dotnetfiddle.net/j0ArmN
So does this, even Int16 is not equivalent to Int32: https://dotnetfiddle.net/Rm2XqO
Run-time exception (line 114): cannot use ChangeType to convert 'Quantity': System.Int16 => System.Int32 (Value=12)
In fact a integer column for Price is also unusual, prices are commonly stored as decimal and as demonstrated here: https://dotnetfiddle.net/yaxLpk Decimal (obviously) can't be changed or converted to an integer without loss of information:
Run-time exception (line 114): cannot use ChangeType to convert 'Price': System.Decimal => System.Int32 (Value=14.0000)
This is a working fiddle to show that the logic can work: https://dotnetfiddle.net/xYBLr3
There are of course many better ways to achieve the same simple outcomes, consider TableAdapters or Linq to SQL or a fully featured ORM like Entity Framework instead of trying to do everything from first principals.
This is a minimal EF6 Implementation that uses serialization techniques to read the data into the expected type: https://dotnetfiddle.net/TVxGfw