How to Connect to a SQL Database in C# Using ADO.NET

When working with SQL Server, you may often need to count the number of unique values in a specific column. This is useful for analyzing data, detecting duplicates, and understanding dataset distributions.

Using COUNT(DISTINCT column_name)

To count the number of unique values in a column, SQL Server provides the COUNT(DISTINCT column_name) function. Here’s a simple example:

SELECT COUNT(DISTINCT column_name) AS distinct_count
FROM table_name;

This query will return the number of unique values in column_name.

Counting Distinct Values Across Multiple Columns

If you need to count distinct combinations of multiple columns, you can use a subquery:

SELECT COUNT(*) AS distinct_count
FROM (SELECT DISTINCT column1, column2 FROM table_name) AS subquery;

This approach ensures that only unique pairs of column1 and column2 are counted.

Why Use COUNT DISTINCT?

• Helps in identifying unique entries in a dataset.
• Useful for reporting and analytics.
• Efficient way to check for duplicates.

By leveraging COUNT(DISTINCT column_name), you can efficiently analyze your database and extract meaningful insights. Happy querying!

Storing passwords as plain text is dangerous. Instead, you should hash them using a strong, slow hashing algorithm like BCrypt, which includes built-in salting and resistance to brute-force attacks.

Step 1: Install BCrypt NuGet Package

Before using BCrypt, install the BCrypt.Net-Next package:

dotnet add package BCrypt.Net-Next

or via NuGet Package Manager:

Install-Package BCrypt.Net-Next

Step 2: Hash a Password

Use BCrypt.HashPassword() to securely hash a password before storing it:

using BCrypt.Net;

string password = "mySecurePassword123";
string hashedPassword = BCrypt.HashPassword(password);

Console.WriteLine(hashedPassword); // Output: $2a$12$...

Step 3: Verify a Password

To check a user's login attempt, use BCrypt.Verify():

bool isMatch = BCrypt.Verify("mySecurePassword123", hashedPassword);
Console.WriteLine(isMatch); // Output: True

Ensuring proper hashing should be at the top of your list when it comes to building authentication systems.

Closing a SqlDataReader correctly prevents memory leaks, connection issues, and unclosed resources. Here’s the best way to do it.

Use 'using' to Auto-Close

Using using statements ensures SqlDataReader and SqlConnection are closed even if an exception occurs.

Example

using (SqlConnection conn = new SqlConnection(connectionString))
{
    conn.Open();
    using (SqlCommand cmd = new SqlCommand("SELECT * FROM Users", conn))
    using (SqlDataReader reader = cmd.ExecuteReader())
    {
        while (reader.Read())
        {
            Console.WriteLine(reader["Username"]);
        }
    } // ✅ Auto-closes reader here
} // ✅ Auto-closes connection here

This approach auto-closes resources when done and it is cleaner and less error-prone than manual closing.

⚡ Alternative: Manually Close in finally Block

If you need explicit control, you can manually close it inside a finally block.

SqlDataReader? reader = null;
try
{
    using SqlConnection conn = new SqlConnection(connectionString);
    conn.Open();
    using SqlCommand cmd = new SqlCommand("SELECT * FROM Users", conn);
    reader = cmd.ExecuteReader();

    while (reader.Read())
    {
        Console.WriteLine(reader["Username"]);
    }
}
finally
{
    reader?.Close();  // ✅ Closes reader if it was opened
}

This is slightly more error prone if you forget to add a finally block. But might make sense when you need to handle the reader separately from the command or connection.