// http://svc.luckstar.com.tw/CodeHelper/cs/index.html
// 2017-04-11, honda@luckstar.com.tw, Create for demo dispose template. e.g. Implement CSqlClient usage.

// Please ref to: 
// CSqlClient.cs.txt, 
// CDBPubs.cs.txt, 
// CDBNorthwind.cs.txt, 
// CRunSqlClient.cs.tx 
// and Dispose-Template.txt

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
// add
using System.Data;
using System.Data.SqlClient;


namespace ConsoleBase
{
    public class CRunSqlClient
    {
        public void Run()
        {
            string sCmd = "Select au_fname, au_lname from authors where au_fname like 'S%' order by au_fname, au_lname";
            Console.WriteLine("CRunSqlClient.cs: ");
            Console.WriteLine("mList1: ");
            CDBPubs mDB1 = new CDBPubs();
            DataTable dt1 = mDB1.GetDataTable(sCmd);
            Console.WriteLine("DataTable: Table name={0}, sCmd={1}.", dt1.TableName, sCmd);
            if (dt1.Rows.Count < 1)
                Console.WriteLine("Rows.Count<1. {0}.", sCmd);

            for (int i = 0; i < dt1.Rows.Count; i++)
            {
                string sF1;
                string sF2;
                sF1 = dt1.Rows[i][0].ToString();
                sF2 = dt1.Rows[i][1].ToString();
                Console.WriteLine("{0}, {1}.", sF1, sF2);
            }

            Console.WriteLine();
            Console.WriteLine("----------");
            Console.WriteLine("SqlDataReader:", sCmd);
            SqlDataReader rd1 = mDB1.ExecuteReader(sCmd);
            while (rd1.Read())
            {
                string sF1;
                string sF2;
                sF1 = rd1[0].ToString();
                sF2 = rd1[1].ToString();
                Console.WriteLine("{0}, {1}.", sF1, sF2);
            }
            rd1.Close();

            using (CDBNorthwind mDB2 = new CDBNorthwind())
            {
                Console.WriteLine();
                Console.WriteLine("----------");
                sCmd = "Select LastName, FirstName from Employees where LastName like 'D%' order by LastName, FirstName";
                dt1 = mDB2.GetDataTable(sCmd);
                Console.WriteLine("Using: Table name={0}, sCmd={1}.", dt1.TableName, sCmd);
                if (dt1.Rows.Count < 1)
                    Console.WriteLine("Rows.Count<1. {0}.", sCmd);

                for (int i = 0; i < dt1.Rows.Count; i++)
                {
                    string sF1;
                    string sF2;
                    sF1 = dt1.Rows[i][0].ToString();
                    sF2 = dt1.Rows[i][1].ToString();
                    Console.WriteLine("{0}, {1}.", sF1, sF2);
                }

                Console.WriteLine();
                Console.WriteLine("----------");
                Console.WriteLine("Dataset with 2 tables:");
                string sCmd2 = sCmd + " select CompanyName, Phone from Customers where CompanyName like 'D%' order by CompanyName desc, Phone desc ";
                DataSet ds1 = mDB2.GetDataSet(sCmd2);
                for (int iTable = 0; iTable < ds1.Tables.Count; iTable++)
                {
                    dt1 = ds1.Tables[iTable];
                    Console.WriteLine();
                    Console.WriteLine("Table{0}={1}.", iTable, dt1.TableName);
                    for (int i = 0; i < dt1.Rows.Count; i++)
                    {
                        string sF1;
                        string sF2;
                        sF1 = dt1.Rows[i][0].ToString();
                        sF2 = dt1.Rows[i][1].ToString();
                        Console.WriteLine("{0}, {1}.", sF1, sF2);
                    }
                }
            }
        }
    }
}

result:

CRunSqlClient.cs: 
mList1: 
DataTable: Table name=Table, sCmd=Select au_fname, au_lname from authors where au_fname like 'S%' order by au_fname, au_lname.
Sheryl, Hunter.
Stearns, MacFeather.
Sylvia, Panteley.

----------
SqlDataReader:
Sheryl, Hunter.
Stearns, MacFeather.
Sylvia, Panteley.

----------
Using: Table name=Table, sCmd=Select LastName, FirstName from Employees where LastName like 'D%' order by LastName, FirstName.
Davolio, Nancy.
Dodsworth, Anne.

----------
Dataset with 2 tables:

Table0=Table.
Davolio, Nancy.
Dodsworth, Anne.

Table1=Table1.
Du monde entier, 40.67.88.88.
Drachenblut Delikatessen, 0241-039123.
Die Wandernde Kuh, 0711-020361.