Questions tagged [sqldatareader]

Provides a way of reading a forward-only stream of rows from a SQL Server database.

The SqlDataReader is a member of the .NET framework's System.Data.SqlClient family responsible for reading data from a SQL database. The SqlDataReader is created by calling the ExecuteReader() method of the SqlCommand object, instead of directly using a constructor.

While the SqlDataReader is being used, the associated SqlConnection is busy serving the SqlDataReader, and no other operations can be performed on the SqlConnection other than closing it. This is the case until the Close method of the SqlDataReader is called. For example, you cannot retrieve output parameters until after you call Close.

Changes made to a result set by another process or thread while data is being read may be visible to the user of the SqlDataReader. However, the precise behavior is timing dependent.

For optimal performance, SqlDataReader avoids creating unnecessary objects or making unnecessary copies of data. Therefore, multiple calls to methods such as GetValue return a reference to the same object. Use caution if you are modifying the underlying value of the objects returned by methods such as GetValue.

References

MSDN Article

1132 questions
18
votes
2 answers

How to display database records in asp.net mvc view

Using ASP.NET MVC with C#, how do you pass some database records to a View and display them in table form? I need to know how I can transfer/pass some rows of records from a database that have been returned to an SqlDataReader object and pass that…
doncadavona
  • 7,162
  • 9
  • 41
  • 54
16
votes
1 answer

Unit testing - stubbing an SqlDataReader

We have an n-tier web application that pulls data from SQL Server. Our Data Access logic returns an SqlDataReader whose data is then used to create our Business objects (a.k.a. Data Transfer objects). We wish to build unit tests to check our code…
DrGriff
  • 4,394
  • 9
  • 43
  • 92
16
votes
5 answers

Read boolean values from DB?

In C#, using SqlDataReader, is there a way to read a boolean value from the DB? while (reader.Read()) { destPath = reader["destination_path"].ToString(); destFile = reader["destination_file"].ToString(); createDir =…
Mr. Ant
  • 700
  • 2
  • 15
  • 32
16
votes
2 answers

Reuse of SqlConnection and SqlDataReader

If I want to run multiple SELECT queries on different tables, can I use the same SqlDataReader and SqlConnection for all of them?? Would the following be wise?? (I typed this up fast, so it lacks try/catch): MySqlCommand myCommand = new…
PaulG
  • 6,920
  • 12
  • 54
  • 98
16
votes
1 answer

DataReader: Specified cast is not valid (Int32)

Why does SqlDataReader throw an exception when converting 0 to integer? ?dataReader(3) 0 {Short} Short: 0 ?dataReader.GetInt16(3) 0 ?dataReader.GetInt32(3) {"Specified cast is not valid."} _HResult: -2147467262 _message: "Specified cast…
moldovanu
  • 660
  • 3
  • 8
  • 20
15
votes
2 answers

connected model and disconnected model in EF

I'm confused a lot about connected model and disconnected in entity framework . I was using traditional ADO.net (DataReader for connected model and DataAdapter for disconnected model) and all I know that I use connected model when I have many users…
Anyname Donotcare
  • 11,113
  • 66
  • 219
  • 392
15
votes
6 answers

DataReader - hardcode ordinals?

When returning data from a DataReader I would typically use the ordinal reference on the DataReader to grab the relevant column: if (dr.HasRows) Console.WriteLine(dr[0].ToString()); or if (dr.HasRows) …
David Neale
  • 16,498
  • 6
  • 59
  • 85
14
votes
2 answers

using on SQLDataReader

I know I asked a related question earlier. I just had another thought. using (SqlConnection conn = new SqlConnection('blah blah')) { using(SqlCommand cmd = new SqlCommand(sqlStatement, conn)) { conn.open(); // *** do I need…
xeshu
  • 788
  • 2
  • 11
  • 24
14
votes
6 answers

Check if it's the last record in sqldatareader

Is there a way to check if I'm on the last record ? thanks
wben
  • 247
  • 2
  • 7
  • 20
13
votes
8 answers

How to efficiently write to file from SQL datareader in c#?

I have a remote sql connection in C# that needs to execute a query and save its results to the users's local hard disk. There is a fairly large amount of data this thing can return, so need to think of an efficient way of storing it. I've read…
Sam
  • 946
  • 3
  • 11
  • 22
13
votes
2 answers

Is there any performance gain from CommandBehavior.SequentialAccess?

I realized I always read my fields in the order they are returned by index (using constants). So my code is already compatible with CommandBehavior.SequentialAccess as far as i understand. Would there be any benefits if i turn it on? DataReader is…
Carl R
  • 8,104
  • 5
  • 48
  • 80
12
votes
2 answers

c# IDataReader SqlDataReader difference

Can someone tell me the difference between these two pieces of code? Why use IDataReader? using (IDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { // get data from the reader } } using (SqlDataReader reader =…
Nice
  • 121
  • 1
  • 3
12
votes
5 answers

c# - Fill generic list from SqlDataReader

How can I add values that a SqlDataReader returns to a generic List? I have a method where I use SqlDataReader to get CategoryID from a Category table. I would like to add all the CategoryID a generic List. This dose not work because it returns…
Erik
  • 225
  • 2
  • 5
  • 7
12
votes
2 answers

'while' in async computation expression where the condition is async

I'm playing around with using SqlClient in F# and I'm having difficulty with using SqlDataReader.ReadAsync. I'm trying to do the F# equivalent of while (await reader.ReadAsync) { ... } What is the best way to do this in F#? Below is my full program.…
Jared Moore
  • 3,765
  • 26
  • 31
12
votes
3 answers

SqlDataReader Best way to check for null values -sqlDataReader.IsDBNull vs DBNull.Value

I want to retrieve decimal values from the database and I would like to know which is the recommended way to check for null values. I have seen on MSDN - DBNull.Value Field that this check is rarely used. Thus, is the reader.IsDBNull the best/most…
diver
  • 352
  • 1
  • 4
  • 12
1 2
3
75 76