using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;
using System.Configuration; //see http://weblogs.asp.net/scottcate/archive/2005/06/13/Looking-for-the-new-ConfigurationManager_3F00_.aspx
using System.Windows.Forms;

/// <summary>
/// An example class that demonstrates SqlDataHelper.
/// This hasn't been tested
/// </summary>
class SqlReaderExample
{
    /// <summary>
    /// Returns a connection string like <example>Driver={SQL Native Client};Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;</example>
    /// Using Microsoft Configuration Manager. Also you can hardcode this here or set it to whatever you like.
    /// </summary>
    private static string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["Default"].ConnectionString;

    /// <summary>
    /// Use SqlReader to connect to a database, run some query or stored-procedure, and show results.
    /// (Not using SqlHelper)
    /// </summary>
    /// <remarks>By Ashiq Alibhai</remarks>
    public static void UseSqlReader()
    {        
        // Initialize our connection to the DB
        string query = "select * from dbo.users";
        
        SqlConnection conn = new SqlConnection(connectionString);

        try
        {
            conn.Open();
        }
        catch (Exception ex)
        {
            MessageBox.Show("Couldn't open a connection to the database.  The exception is " + ex.ToString());
            conn.Close();
            return;
        }
        
        SqlDataReader reader;
        // Connect to the database
        using (reader = new SqlCommand(query, conn).ExecuteReader())
        {
            // Make sure we have data
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    string id = reader.GetString(reader.GetOrdinal("Id"));
                    string firstName = reader.GetString(reader.GetOrdinal("FirstName"));
                    string lastName = reader.GetString(reader.GetOrdinal("LastName"));
                    // Do something interesting, albeit trivial
                    MessageBox.Show(string.Format("[{0}] {1} {2}", id, firstName, lastName));
                    // ... more data-processing code ...
                }
            }
            // We're done with the reader, so close it.
            reader.Close();
        }
        // Close our connection to the DB
        conn.Close();
    }

    /// <summary>
    /// Use SqlReader to connect to a database, run some query or stored-procedure, and show results.
    /// (Using SqlHelper)
    /// </summary>
    /// <remarks>By Ashiq Alibhai</remarks>
    public static void UseSqlReaderAppBlocksVersion()
    {
        // Initialize our connection to the DB
        string query = "select * from dbo.users";
       
        SqlDataReader reader;
        // Connect to the database
        using (reader = SqlHelper.ExecuteReader(connectionString, System.Data.CommandType.Text, query))
        {
            // Make sure we have data
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    string id = reader.GetString(reader.GetOrdinal("Id"));
                    string firstName = reader.GetString(reader.GetOrdinal("FirstName"));
                    string lastName = reader.GetString(reader.GetOrdinal("LastName"));
                    // Do something interesting, albeit trivial
                    MessageBox.Show(string.Format("[{0}] {1} {2}", id, firstName, lastName));
                    // ... more data-processing code ...
                }
            }
            // We're done with the reader, so close it.
            reader.Close();
        }
    }

}