When dealing with varbinary(max), there are two scenarios: the length of the data is moderate the length of the data is big GetBytes() is intended for the second scenario, when you are using CommandBehaviour.SequentialAccess to ensure that you are streaming the data, not buffering it. In particular, in this usage you would usually be writing … Read more
A SQL null is not the same as a .NET null; you have to compare against System.DBNull.Value: object sqlDateTime = sqldatareader[0]; DateTime? dt = (sqlDateTime == System.DBNull.Value) ? (DateTime?)null : Convert.ToDateTime(sqlDateTime); In answer to your comment, the data type of the Item property of a DataReader is that of the underlying database type. It could … Read more
Try this because this will close connection ,data reader and command once task get over , so that this will not give datareader close exception Also do check like this if(reader.NextResult()) to check there is next result, using (SqlConnection connection = new SqlConnection(“connection string here”)) { using (SqlCommand command = new SqlCommand (“SELECT Column1 FROM … Read more
I see three issues: the way you use a DataReader negates it’s big single-item-in-memory advantage by converting it to list, you’re running the benchmark in an environment that differs significantly from production in a way that favors the DataTable, and you’re spending time converting DataReader record to Artifact objects that is not duplicated in the … Read more
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
If you want something that’ll convert to arbitrary JSON, you could convert by serializing it into a Dictionary(Of string, object) thusly: public IEnumerable<Dictionary<string, object>> Serialize(SqlDataReader reader) { var results = new List<Dictionary<string, object>>(); var cols = new List<string>(); for (var i = 0; i < reader.FieldCount; i++) cols.Add(reader.GetName(i)); while (reader.Read()) results.Add(SerializeRow(cols, reader)); return results; } … Read more
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();
You have to call DataReader.Read to fetch the result: SqlDataReader dr = cmd10.ExecuteReader(); if (dr.Read()) { // read data for 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
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
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); } }