C# Code Snippet - Get Data From SqlDataReader using open connection

(C-Sharp) C# code snippet connects to SQL server and executes SQL statement and return SqlDataReader. GetSqlDataReader returns SqlDataReader using open database connection and SQL statement.

Bookmark:

C# Code Snippet - Get Data From SqlDataReader using open connection

This .Net C# code snippet connect connects to SQL server and executes SQL statement and return SqlDataReader. To use this function simply provide open database connection and SQL statement. This function uses SqlClient name space to get data using SqlDataReader. Modify the exception handling section for your project requirements.

01public System.Data.SqlClient.SqlDataReader GetSqlDataReader(
02                ref System.Data.SqlClient.SqlConnection _SqlConnection, 
03                string _SQL)
04{
05    // Set temporary variable to create data reader
06    System.Data.SqlClient.SqlDataReader _SqlDataReader = null;
07 
08    // lets run the given SQL statement and create SqlDataReader
09    try
10    {
11        // Pass the connection to a command object
12        System.Data.SqlClient.SqlCommand _SqlCommand =
13                new System.Data.SqlClient.SqlCommand(_SQL, _SqlConnection);
14 
15        // get query results
16        _SqlDataReader = _SqlCommand.ExecuteReader();
17    }
18    catch (Exception _Exception)
19    {
20        // Error occurred while trying to execute reader
21        // send error message to console (change below line to customize error handling)
22        Console.WriteLine(_Exception.Message);
23 
24        // failed SQL execution, lets return null
25        return null;
26    }
27 
28    // SQL successfully executed, lets return the SqlDataReader
29    return _SqlDataReader;
30}

Here is a simple example showing how to use above function (GetSqlDataReader) to connect to SQL database and get SqlDataReader for given SQL statement.

01// set temporary variable for database connection
02System.Data.SqlClient.SqlConnection _SqlConnection = new System.Data.SqlClient.SqlConnection();
03 
04// assign database connection string
05_SqlConnection.ConnectionString = "Server=SERVERADDRESS;Database=DATABASENAME;Uid=USERID;Pwd=PASSWORD;";
06 
07// Connect to database
08try
09{
10    _SqlConnection.Open();
11}
12catch (Exception _Exception)
13{
14    // Error occurred while trying to connect to database
15    Console.WriteLine(_Exception.Message);
16}
17 
18 
19// Check for valid open database connection before query database
20if (_SqlConnection != null && _SqlConnection.State == ConnectionState.Open)
21{
22    // Lets call above function to create a new SqlDataReader
23    // using open database connection and SQL statement
24    System.Data.SqlClient.SqlDataReader _SqlDataReader = GetSqlDataReader(
25        // Pass open database connection to function
26        ref _SqlConnection,
27        // Pass SQL statement to create SqlDataReader
28        "SELECT NAME, PRICE, COST FROM INVENTORY");
29 
30    // Check we have data
31    if (_SqlDataReader != null && _SqlDataReader.HasRows)
32    {
33        Console.WriteLine(_SqlDataReader["NAME"].ToString());
34    }
35     
36    // close sql reader
37    if (_SqlDataReader != null)
38    {
39        _SqlDataReader.Close();
40        _SqlDataReader.Dispose();
41    }    
42 
43    // close database connection
44    _SqlConnection.Close();
45}

C# Keywords Used:

SqlDataReader
SqlConnection
ConnectionString
SqlCommand
ExecuteReader
Exception

Code Snippet Information:

Applies To: .Net, C#, CLI, SQL, SQL Server, SQL Client, Connection String, Database Connection, SQL Data Reader
Programming Language : C# (C-Sharp)

External Resources:

C# Code Snippet - Get Data From SqlDataReader using open connection

C# Code Snippet - Get Data From SqlDataReader using open connection

C# Code Snippet - Get Data From SqlDataReader using open connection

Bookmark:

C# Code Snippet - Get Data From SqlDataReader using open connection

Leave a comment