Reading Data from Databases DataReader c#
In the C# programming language, the SqlDataReader function method in the ADO.NET Framework is an excellent and fast method for reading and accessing data from a desired database. The SqlDataReader method allows C# users to extract data from an existing database in a forward-only, read-only manner. This means that C# users can only move forward in the result set and cannot modify data once it has been found or retrieved from the database.
So, let’s explore the SqlDataReader database extraction method in C# programming.
Some Special Advanced Features of the SqlDataReader.
- Forward-only – In this, C# users can only move through the data in one direction, from top to bottom.
- Read-only – In this, C# users cannot update the data while reading it from a file.
- Benefits – This function is a memory-efficient feature in databases. Because it extracts and displays data from an existing database row-by-row for the C# user, and does not load all the database data into memory at once.
Steps to use the SqlDataReader method in C#.
- First, create a SqlConnection for the database table data.
- Create a SqlCommand with the SQL database command statement query.
- Execute the SQL command using the ExecuteReader() function method.
- Use the SqlDataReader function method to read database data row-by-row.
Example of reading data using the SqlDataReader method in C#.
Here is a complete example of using the SqlDataReader function method to read database data from a SQL Server database management system.
using System;
using System.Data.SqlClient;
class program
{
static void Main()
{
// here we connection string
string connectionString = “Data Source=ServerName;Initial Catalog=DatabaseName;Integrated Security=True”;
// here we define employee table SQL query
string query = “SELECT Id, Name, Age FROM Employee”;
//here we Create a SqlConnection object
using (SqlConnection conn = new SqlConnection(connectionString))
{
// here we open the connection
conn.Open();
// here we Create the SqlCommand object to execute the query
SqlCommand command = new SqlCommand(query, conn);
// here we Execute the query and get a SqlDataReader method
SqlDataReader reader = command.ExecuteReader();
// here it Read the data using the SqlDataReader sql function method
while (reader.Read()) // here it Read data each row
{
// here it Access each column using column names or indexes
int id = reader.GetInt32(0); // here it Get employee Id (first column) data
string name = reader.GetString(1); // here it Get employee Name (second column) data
int age = reader.GetInt32(2); // here it Get employee Age (third column) data
// here it finally Display the employee database table data
Console.WriteLine($”Id: {id}, Name: {name}, Age: {age}”);
}
//here we Close the reader method and its connection
reader.Close();
}
}
}
SqlDataReader method example explanation.
- SqlConnection – This program creates a connection to the database using a string for the database connection.
- Data Source – This locates the server location to connect to the database data.
- Initial Catalog – This is used to represent or indicate the database.
- Integrated Security – This follows Windows authentication principles to connect to the existing database.
- SqlCommand – This displays the SQL query SELECT command statement that will be executed to extract rows from the existing database data table.
SqlDataReader Function/Method Role.
The ExecuteReader() function or method of the SqlCommand object is used to execute the query and retrieve the data from the database table using the SqlDataReader object.
For database data, the SqlDataReader provides a simple and easy way to read data from a result set in a row-by-row order.
The Read() method moves the cursor to the next record in the result set. This returns true if there are more rows of data, and false if there are no more rows of data to read from the current data set.
Accessing the Required Data from the Database.
C# users can use the GetInt32() and GetString() function methods in the database connection to extract data from data columns based on their index (0-based). Additionally, C# users can use the reader[“ColumnName”] method block to access database table data columns by name.
Closing the DataReader and the connection to the database.
- After reading the database data, the reader.Close() function method is called to close the SqlDataReader’s data connection to the database.
- The SqlConnection is then automatically closed when it is disposed of at the end of the using block.
Using the SqlDataReader with the GetValue() function method.
When the database user does not know the table data type or column index, C# users can also use the GetValue() function method to retrieve data.
while (reader.Read())
{
// This dynamically retrieves data from any database data table column
var info = reader.GetInfo(0); // This will return data from the first column in the current database
Console.WriteLine(info);
}
This step is used when C# users are handling or managing multiple data types or want to handle columns generically.
Handling Null Values with SqlDataReader.
Sometimes, NULL values may be generated in the data obtained from a query to the database table data. SqlDataReader provides a method called IsDBNull() function method to check for NULL values in the data.
Handling Null Values with SqlDataReader Example.
while (reader.Read())
{
int emp_id = reader.GetInt32(0);
string emp_name = reader.IsDBNull(1) ? “Random” : reader.GetString(1); // Here it checks for NULL value in the second column
int emp_age = reader.IsDBNull(2) ? 0 : reader.GetInt32(2); // Here it checks for NULL in the third table column
Console.WriteLine($”Employee Id – {emp_id}, Employee Name – {emp_name}, Employee Age – {emp_age}”);
}
Handling Null Values with SqlDataReader explanation.
The IsDBNull(int ordinal) function returns true if the column in the table index indicated here contains a NULL value. If the value here is NULL, you can provide a default value here, or handle or manage it accordingly.
Efficiently handling or controlling large result data sets.
If C# users are dealing with large result database data sets and want to reduce memory consumption on their system servers, SqlDataReader is a good choice for you. Because it reads data from the database row-by-row, if C# users need to perform complex database operations or retain or store database data for a long time, C# users can choose to load the data into a DataTable or DataSet for further manipulation.
If C# users need to process database data, display retrieved results in chunks. Consider using paging in your SQL query, or C# users can load the data gradually.
Data Error Handling with SqlDataReader.
Handling or managing any errors generated or displayed while reading data from a database table is an essential task. Especially in situations or use cases where the database data is not in the expected format or there are database data connection establishment issues.
Example of SqlDataReader error handling.
try
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand command = new SqlCommand(query, conn);
SqlDataReader reader = command.ExecuteReader();
while(reader.Read())
{
try
{
//here it Handle each row of database table data
int emp_id = reader.GetInt32(0);
string emp_name = reader.GetString(1);
Console.WriteLine($”Employee Id – {emp_id}, Employee Name – {emp_name}”);
}
catch (exception exception)
{
Console.WriteLine(“Display Error reading table row – ” + exception.Message);
}
}
}
}
catch (Exception excption)
{
Console.WriteLine(“Display Error with database connection – ” + exception.Message);
}
SqlDataReader error handling explanation.
- The try-catch block in this program ensures that any program database exceptions (whether due to database connection establishment issues or errors reading rows from database table data) can be handled and managed effectively using try and catch blocks.
Reading Data from Databases DataReader Method Summary.
- The SqlDataReader function is a powerful and efficient method for reading data from a database in ADO.NET.
- It manages and controls forward-only, read-only access to database data, making it the best choice for particular situations where a C# user needs to process and handle large amounts of database data without using too much memory.
- Always ensure your system properly closes the SqlDataReader after use to avoid any data resource leaks, which is typically done using a using statement.
- Handling NULL values in database data and any program exceptions in the proper order ensures that your application is robust, complete, error-free, reliable, and fast.
