using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.IO;
using MySql.Data.MySqlClient;

using System.Data;

namespace ConsoleApplication1
{
    class Program
    {
        public static string SQLuser;
        public static string SQLpassword;
        static void Main(string[] args)
        {
            readconfigfile();
            string connectionString = "Server=sql;" +
                "Database=u_chzz_stalkbot;" +
                "User ID=" + SQLuser + ";" +
                "Password=" + SQLpassword + ";" +
                "Persist Security Info=True;";

            MySqlConnection conn = new MySqlConnection(connectionString);
            conn.Open();

            string SQLStatement = "SELECT * FROM channel";
            MySqlDataAdapter ChanAdapter = new MySqlDataAdapter(SQLStatement, conn);
            DataTable dtResult = new DataTable();
            DataTable dtstalk = new DataTable();

            // Fill the DataTable with the result of the SQL statement
            ChanAdapter.Fill(dtResult);

            // Loop through all entries

            foreach (DataRow drRow in dtResult.Rows)
            {
                string thischan = drRow["Name"].ToString();
                Console.WriteLine(thischan);
                //now, for each channel, populate array of stalks
                SQLStatement = "SELECT text FROM chanstalk WHERE channel = '"+thischan+"'";
                Console.WriteLine(SQLStatement);
                MySqlDataAdapter stalkAdapter = new MySqlDataAdapter(SQLStatement, conn);
                DataTable stResult = new DataTable();
                stalkAdapter.Fill(stResult);
                dtstalk.Rows.Add(stResult);
                foreach (DataRow stRow in stResult.Rows)
                {
                    string thisstalk = stRow["text"].ToString();
                    Console.WriteLine("****"+thisstalk);

                }
                stalkAdapter.Dispose();
            }
            // We don't need the data adapter any more
            ChanAdapter.Dispose();
            conn.Close();
            conn.Dispose();
            Console.WriteLine("XXXXXXX");
         foreach (DataRow row in dtstalk.Rows)
         {
             Console.WriteLine("Row"+row);
             foreach (DataColumn col in dtstalk.Columns)
             {
                 Console.WriteLine("col" + col);
                 Console.WriteLine(row[col]);
             }
         }


        }
                static void readconfigfile()
        {
            Console.WriteLine("reading .my.cnf");
            StreamReader settingsreader = new StreamReader("/home/chzz/.my.cnf");
            try {
               settingsreader.ReadLine();
               SQLuser = settingsreader.ReadLine();
               SQLuser = SQLuser.Remove(0,SQLuser.IndexOf("=")+2);
               SQLpassword = settingsreader.ReadLine();
               SQLpassword = SQLpassword.Remove(0, SQLpassword.IndexOf("=") + 2);
              // SQLpassword = SQLpassword.Replace("\"","");
            }
            catch (Exception ex) {
                Console.WriteLine("Could not read .my.cnf for SQL user/password");
                Console.WriteLine(ex.Message);
            }

        }

    }
}