how to check if a datareader is null or empty
if (myReader[“Additional”] != DBNull.Value) { ltlAdditional.Text = “contains data”; } else { ltlAdditional.Text = “is null”; }
sqldatareader
How to get number of rows using SqlDataReader in C#
There are only two options: Find out by reading all rows (and then you might as well store them) run a specialized SELECT COUNT(*) query beforehand. Going twice through the DataReader loop is really expensive, you would have to re-execute the query. And (thanks to Pete OHanlon) the second option is only concurrency-safe when you … Read more
Invalid attempt to read when no data is present
You have to call DataReader.Read() to fetch the result: SqlDataReader dr = cmd10.ExecuteReader(); if (dr.Read()) { // read data for single/first record here } DataReader.Read() returns a bool indicating if there are more blocks of data to read, so if you have more than 1 result, you can do: while (dr.Read()) { // read data … Read more
Read data from SqlDataReader
using(SqlDataReader rdr = cmd.ExecuteReader()) { while (rdr.Read()) { var myString = rdr.GetString(0); //The 0 stands for “the 0’th column”, so the first column of the result. // Do somthing with this rows string, for example to put them in to a list listDeclaredElsewhere.Add(myString); } }
Check for column name in a SqlDataReader object
public static class DataRecordExtensions { public static bool HasColumn(this IDataRecord dr, string columnName) { for (int i=0; i < dr.FieldCount; i++) { if (dr.GetName(i).Equals(columnName, StringComparison.InvariantCultureIgnoreCase)) return true; } return false; } } Using Exceptions for control logic like in some other answers is considered bad practice and has performance costs. It also sends false positives … Read more
Can you get the column names from a SqlDataReader?
var reader = cmd.ExecuteReader(); var columns = new List<string>(); for(int i=0;i<reader.FieldCount;i++) { columns.Add(reader.GetName(i)); } or var columns = Enumerable.Range(0, reader.FieldCount).Select(reader.GetName).ToList();
SQL Data Reader – handling Null column values
You need to check for IsDBNull: if(!SqlReader.IsDBNull(indexFirstName)) { employee.FirstName = sqlreader.GetString(indexFirstName); } That’s your only reliable way to detect and handle this situation. I wrapped those things into extension methods and tend to return a default value if the column is indeed null: public static string SafeGetString(this SqlDataReader reader, int colIndex) { if(!reader.IsDBNull(colIndex)) return reader.GetString(colIndex); … Read more