I have a working code that converts from recordsets in C#, but I've also come across other approaches and I have been investigating their uses. Here's the test code:
`using (DataTableReader reader = new DataTableReader(ds2.Tables[0]))
{
while (reader.Read())
{
//sample of working code. The values are on the far right.
string val1 = reader[0] is DBNull ? default(string) : reader[0].ToString(); //value = "test"
int val2 = reader[2] is DBNull ? default(int) : Convert.ToInt32(reader[2].ToString()); //value = 23
bool val3 = reader[26] is DBNull ? default(bool) : reader[26].ToString() == "1" ? true : false; //value = true
DateTime val4 = reader[16] is DBNull ? default(DateTime) : Convert.ToDateTime(reader[16].ToString()); //value= 10/6/2013 6:31:33 AM
//the first approach
string val5 = reader[0] as string;
int? val6 = reader[2] as int? ?? default(int);
bool? val7 = reader[26] as bool? ?? default(bool);
DateTime? val8 = reader[16] as DateTime? ?? default(DateTime);
//the second approach
string val9 = reader[0] is DBNull ? default(string) : reader.GetString(0);
int val10 = reader[2] is DBNull ? default(int) : reader.GetInt32(2); ;
bool val11 = reader[26] is DBNull ? default(bool) : reader.GetBoolean(26);
DateTime val12 = reader[16] is DBNull ? default(DateTime) : reader.GetDateTime(16);
//the third approach
var val13 = reader[0] is DBNull ? default(string) : (string)reader[0];
int val14 = reader[2] is DBNull ? default(int) : (int)reader[2]; ;
bool val15 = reader[26] is DBNull ? default(bool) : (bool)reader[26];
DateTime val16 = reader[16] is DBNull ? default(DateTime) : (DateTime)reader[16];
//the 4th approach
int val17 = reader[2] is DBNull ? default(int) : (int) Int32.Parse(reader[2].ToString());
bool val18 = reader[26] is DBNull ? default(bool) : (bool) Boolean.Parse( reader[26].ToString()=="1"? "true" : "false");
DateTime val19 = reader[16] is DBNull ? default(DateTime) : (DateTime) DateTime.Parse(reader[16].ToString());
}
}`
This SO Q&A shows samples of the first and second approaches.The third approach I took from this SO Q&A. The fourth approach was from here.
In the first approach, the as operator is combined with the ?? operator for default values, inasmuch as the as operator handles casting and checks for DBNull, with the commenters noting that this will fail silently in case of errors. Using this code I'm getting null values for strings and default values for the rest, indicating that somehow the 'as' operator is failing.
The second approach uses the reader's Getxxxx() methods. But this throws an exception "Unable to cast object of type 'System.Data.SqlTypes.SqlString' to type 'System.String'." for strings and "Specified cast is not valid." for the others. The third approach uses casting and generates the same kind of exceptions as the second approach.
The fourth approach works, and converts first the data to string, parses the string and finally casts to the intended type. I've come across a few more using TryParse() and Convert.ChangeType() but they are more verbose.
My question is, those other approaches seem to work on other people (they were answers and upvoted) but why not when I use them? what am I doing wrong here? Which of these is the most efficient approach, if they could be made to work?
EDIT: As suggested by @usr, I should use some helper methods. Here's the helper method that I would use for the working code, with sample usage:
public static T GetColumnValue<T>(this DataTableReader reader, string columnName)
{
T result = default(T);
int index = reader.GetOrdinal(columnName);
if (!reader.IsDBNull(index))
{
result = (T)Convert.ChangeType(reader[index].ToString(), typeof(T));
}
return result;
}
//used as
string val1 = reader.GetColumnValue<string>("Column1");
int val2 = reader.GetColumnValue<int>("Column2");
bool val3 = reader.GetColumnValue<bool>("Column3");
DateTime val4 = reader.GetColumnValue<DateTime>("Column4");
Of course this would not work for the other approaches. I am still curious why the other approaches are not working. Thanks.
