Accessing Your Database with SqlCommand and SqlDataReader in ASP.NET Web Pages

In the world of web development, connecting to a database and retrieving or manipulating data is a fundamental task. ASP.NET Web Pages, with their Razor syntax, provide a streamlined way to achieve this using SqlCommand and SqlDataReader. Let’s dive into how to leverage these powerful tools.

Setting the Stage: Database Connection

First, make sure you have a valid connection string for your database. This string typically includes information about the database server, the database name, and credentials. Store this connection string securely, either in your web.config file or in a separate configuration file.

C#

var connectionString = “Your_Connection_String_Here”; // Replace with your actual connection string

Crafting Your SQL Command

Next, create a SqlCommand object. This object encapsulates your SQL query or stored procedure and the database connection.

C#

using (var connection = new SqlConnection(connectionString))

{

    connection.Open();

    var command = new SqlCommand(“SELECT * FROM Products”, connection); // Example query

    // Execute the command and process the results (see below)

}

Fetching Data with SqlDataReader

To retrieve data from the database, use the ExecuteReader method of the SqlCommand object. This returns a SqlDataReader that you can use to read the results row by row.

C#

using (var reader = command.ExecuteReader())

{

    while (reader.Read())

    {

        // Process each row of data here

        var productName = reader[“ProductName”].ToString();

        var unitPrice = (decimal)reader[“UnitPrice”];

        // … (access other columns as needed)

    }

}

Important Considerations:

• Using Blocks: Always wrap your database interactions within using blocks. This ensures that the connection and other resources are properly disposed of, even if an exception occurs.
• Parameterization: For security and performance, use parameterized queries to avoid SQL injection attacks and optimize query execution plans.

Example: Displaying Products in a Table

HTML

@{

    var connectionString = “Your_Connection_String_Here”;

    using (var connection = new SqlConnection(connectionString))

    {

        connection.Open();

        var command = new SqlCommand(“SELECT ProductName, UnitPrice FROM Products”, connection);

        <table>

            <tr>

                <th>Product Name</th>

                <th>Unit Price</th>

            </tr>

            using (var reader = command.ExecuteReader())

            {

                while (reader.Read())

                {

                    <tr>

                        <td>@reader[“ProductName”]</td>

                        <td>@reader[“UnitPrice”]</td>

                    </tr>

                }

            }

        </table>

    }

}

Key Takeaways:

• SqlCommand allows you to execute SQL queries and stored procedures.
• SqlDataReader provides an efficient way to read the results of a query row by row.
• Always use parameterized queries to protect against SQL injection.
• Dispose of connections and resources properly using using blocks.