Executing queries with SqlConnection and SqlCommand.

NonQuery.cs

public void NonQuery()
{
    var connectionString = ConfigurationManager.ConnectionStrings["Default"].ConnectionString;
    var sqlCommandText = "UPDATE People SET Name = 'John'";
    
    using (var sqlConnection = new SqlConnection(connectionString))
    using (var sqlCommand = new SqlCommand(sqlCommandText, sqlConnection))
    {
        sqlCommand.Connection.Open();
    
        sqlCommand.ExecuteNonQuery();
    
        sqlCommand.Connection.Close();
    }
}

Query.cs

public void Query()
{
    var connectionString = ConfigurationManager.ConnectionStrings["Default"].ConnectionString;
    var sqlCommandText = "SELECT FirstName FROM People WHERE LastName = @LastName";
    
    using (var sqlConnection = new SqlConnection(connectionString))
    using (var sqlCommand = new SqlCommand(sqlCommandText, sqlConnection))
    {
        sqlCommand.Parameters.AddWithValue("LastName", "Bubriski");
        
        sqlCommand.Connection.Open();
    
        using(var sqlReader = sqlCommand.ExecuteReader())
        {
            while(sqlReader.Read())
            {
                // Do something with the data
                var firstName = sqlReader["FirstName"];
            }
        }
    
        sqlCommand.Connection.Close();
    }
}

QueryDataSet.cs

public DataSet QueryDataSet()
    {
        var dataSet = new DataSet();
        var connectionString = ConfigurationManager.ConnectionStrings["Default"].ConnectionString;
        var sqlCommandText = "SELECT FirstName FROM People WHERE LastName = @LastName";

        using (var sqlConnection = new SqlConnection(connectionString))
        using (var sqlCommand = new SqlCommand(sqlCommandText, sqlConnection))
        {
            sqlCommand.Parameters.AddWithValue("LastName", "Bubriski");

            sqlCommand.Connection.Open();

            using (var sqlDataAdapter = new SqlDataAdapter(sqlCommand))
            {
                sqlDataAdapter.Fill(dataSet);
            }

            sqlCommand.Connection.Close();
        }

        return dataSet;
    }

Scalar.cs

public int Scalar()
{
    var peopleCount = 0;
    
    var connectionString = ConfigurationManager.ConnectionStrings["Default"].ConnectionString;
    var sqlCommandText = "SELECT COUNT(*) FROM People";
    
    using (var sqlConnection = new SqlConnection(connectionString))
    using (var sqlCommand = new SqlCommand(sqlCommandText, sqlConnection))
    {
        sqlCommand.Connection.Open();
    
        peopleCount = sqlCommand.ExecuteScalar();
    
        sqlCommand.Connection.Close();
    }
    
    return peopleCount;
}