C# ADO.NET SqlDataReader

The SqlDataReader Object in C# provides connection-oriented data access to SQL Server data sources from C# applications.

ExecuteReader() method

To retrieve data using the SqlDataReader, you typically utilize the ExecuteReader() method in the SqlCommand Object. This method sends the SQL statements or Stored Procedures to the associated SqlConnection Object and populates a SqlDataReader Object based on the executed SQL statement.

SqlDataReader sqlReader = sqlCmd.ExecuteReader();

When the ExecuteReader method is executed on the SqlCommand Object, it instantiates a SqlClient.SqlDataReader Object. This SqlDataReader Object serves as a cursor that allows you to efficiently read and access the retrieved data.

Before reading from the SqlDataReader, it's important to ensure that the SqlDataReader is open and positioned prior to the first record. You can accomplish this by invoking the Read() method, which advances the SqlDataReader to the next row and returns a boolean value indicating whether there are more rows available.

Read() method

The Read() method of the DataReader is utilized to read the rows sequentially from the SqlDataReader. It always moves the cursor forward to the next valid row, if any rows exist.

SqlDataReader.Read()

By invoking the Read() method iteratively, you can retrieve the data from each row and access the values of individual columns using the appropriate getter methods (e.g., GetString(), GetInt32(), etc.) based on the data type.

Full Source C#
using System; using System.Windows.Forms; using System.Data.SqlClient; namespace WindowsApplication1 { public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void button1_Click(object sender, EventArgs e) { string connetionString = null; SqlConnection sqlCnn ; SqlCommand sqlCmd ; string sql = null; connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password"; sql = "Your SQL Statement Here , like Select * from product"; sqlCnn = new SqlConnection(connetionString); try { sqlCnn.Open(); sqlCmd = new SqlCommand(sql, sqlCnn); SqlDataReader sqlReader = sqlCmd.ExecuteReader(); while (sqlReader.Read()) { MessageBox.Show(sqlReader.GetValue(0) + " - " + sqlReader.GetValue(1) + " - " + sqlReader.GetValue(2)); } sqlReader.Close(); sqlCmd.Dispose(); sqlCnn.Close(); } catch (Exception ex) { MessageBox.Show("Can not open connection ! "); } } } }

Conclusion

The SqlDataReader Object in C# facilitates connection-oriented data access to SQL Server data sources. It is instantiated using the ExecuteReader() method in the SqlCommand Object and provides methods like Read() to sequentially retrieve and access data rows. Ensuring the SqlDataReader is open and positioned correctly before reading is crucial, and it is important to manage the connection and close the SqlDataReader and SqlConnection when finished.






Related Topics
  1. C# ADO.NET Connection
  2. C# SQL Server Connection
  3. C# OLEDB Connection
  4. C# ODBC Connection
  1. C# ADO.NET Command
  2. C# ADO.NET SqlCommand - ExecuteNonQuery
  3. C# ADO.NET OleDbCommand - ExecuteNonQuery
  4. C# ADO.NET SqlCommand - ExecuteScalar
  5. C# ADO.NET OleDbCommand - ExecuteScalar
  6. C# ADO.NET SqlCommand - ExecuteReader
  7. C# ADO.NET OleDbCommand - ExecuteReader
  8. C# ADO.NET DataReader
  9. C# ADO.NET OleDbDataReader
  10. C# Multiple Result Sets
  11. C# Table Schema from SqlDataReader
  12. C# Table Schema from OleDbDataReader
  13. C# ADO.NET DataAdapter
  14. C# ADO.NET SqlDataAdapter
  15. C# ADO.NET OleDbDataAdapter
  16. C# ExecuteReader and ExecuteNonQuery
  17. System.Data.SqlClient.SqlException: Login failed for user

Related Links
  1. Stored Procedures in C#
  2. C# Stored Procedure with Parameter