SQL Patterns

EXECUTE QUERY - NO VALUE RETURN

try
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();

        string sql = "SELECT * FROM [table] WHERE name=@name";

        using (SqlCommand command = connection.CreateCommand())
        {
            command.CommandText = sql;

            command.Parameters.Add(new SqlParameter("name", dogName));

            int affected = command.ExecuteNonQuery();
        }

    }

}
catch (Exception ex)
{
    [...]
}


COPY DATA TO DATATABLE

try
{
     DataTable dataTable = new DataTable("DataSource");   

     using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();

        string sql = "SELECT * FROM [table];

        using (SqlCommand sqlCommand = new SqlCommand(sql, connection))
        {
            using (SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand))
            {
                sqlDataAdapter.Fill(dataTable);
            }
        }
       
    }

    return dataTable;

}
catch (Exception ex)
{
    [...]
}



READ EACH ROW

try
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();
        const string SQL = "SELECT name FROM [table]";

        using (SqlCommand sqlCommand = new SqlCommand(SQL, connection))
        {
            using (SqlDataReader reader = sqlCommand.ExecuteReader())
            {
                if (reader.HasRows == true)
                {
                    while (reader.Read())
                    {
                        [...] = reader["name"].ToString();
                    }
                }

            }//reader

        }//command

    }//connection

}
catch (Exception)
{               
    [...]
}