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
If a field allows nulls, don’t use regular primitive types. Use the C# nullable type and the as keyword. int? field_a = reader[“field_a”] as int?; string field_b = reader[“field_a”] as string; Adding a ? to any non-nullable C# type makes it “nullable”. Using the as keyword will attempt to cast an object to the specified … Read more
Here you have a sample about how to handle multiple result sets with a data reader static void RetrieveMultipleResults(SqlConnection connection) { using (connection) { SqlCommand command = new SqlCommand( “SELECT CategoryID, CategoryName FROM dbo.Categories;” + “SELECT EmployeeID, LastName FROM dbo.Employees”, connection); connection.Open(); SqlDataReader reader = command.ExecuteReader(); do { Console.WriteLine(“\t{0}\t{1}”, reader.GetName(0), reader.GetName(1)); while (reader.Read()) { Console.WriteLine(“\t{0}\t{1}”, … Read more
You can use LINQ: return Enumerable.Range(0, reader.FieldCount) .ToDictionary(reader.GetName, reader.GetValue);
if(dr.HasRows) { // …. } else { MessageBox.Show(“Reservation Number Does Not Exist”,”Error”, MessageBoxButtons.OK, MessageBoxIcon.Asterisk); } SqlDataReader.HasRows Property
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
Data Reader About the fastest access you will get to SQL is with the SqlDataReader. Profile it It’s worth actually profiling where your performance issue is. Usually, where you think the performance issue is, is proven to be totally wrong after you’ve profiled it. For example it could be: The time… the query takes to … Read more
You can get the ordinal of the column by using the GetOrdinal method, so your call could be: read.GetValue(read.GetOrdinal(“ColumnID”));
but as far as I can tell the only thing it does is cast the result to a list before returning it You aren’t missing anything. That is the key difference. Except it isn’t a cast as such: the actual returned object is very different. Basically, there are two ways of reading data: in a … 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