using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using MySql.Data.MySqlClient;

namespace SajtLibrary.SajtClasses
{
    public class QueryExecutor
    {

        static void printRecord(IDataReader rcd)
        {
            Console.WriteLine("-------------------------------------------");
            string s = rcd["name"] + ", " + rcd["last_name"];
            Console.WriteLine(s);
            Console.WriteLine("-------------------------------------------");

        }

        public static DataTable executeQuery(string query)
        {
            IDbConnection conn = new MySqlConnection(Properties.Settings.Default.epos_dbConnectionString);
            try
            {
                conn.Open();

                IDbCommand cmd = conn.CreateCommand();
                cmd.CommandText = query;

                IDataReader reader = cmd.ExecuteReader();

                DataTable dt = new DataTable(reader.GetSchemaTable().TableName);
                dt.Load(reader);
                return dt;
            }
            finally
            {
                conn.Close();
            }
        }

        public static int executeUpdate(string sql)
        {
            IDbConnection conn = new MySqlConnection(Properties.Settings.Default.epos_dbConnectionString);
            try
            {
                int rowsAffected;
                conn.Open();
                IDbTransaction trans = conn.BeginTransaction();
                {
                    IDbCommand cmd = conn.CreateCommand();
                    cmd.CommandText = sql;
                    cmd.Transaction = trans;
                    rowsAffected = cmd.ExecuteNonQuery();

                }
                trans.Commit();
                return rowsAffected;
            }
            finally
            {
                conn.Close();
            }
        }

        public static int[] executeUpdateBatch(params string[] sqlBatch)
        {
            IDbConnection conn = new MySqlConnection(Properties.Settings.Default.epos_dbConnectionString);
            try
            {
                int rowsAffected;
                conn.Open();
                IDbTransaction trans = conn.BeginTransaction();
                {
                    IDbCommand cmd = conn.CreateCommand();
                    cmd.Transaction = trans;
                    foreach (string sql in sqlBatch)
                    {
                        cmd.CommandText = sql;
                        rowsAffected = cmd.ExecuteNonQuery();
                    }
                }
                trans.Commit();
                return null;
            }
            finally
            {
                conn.Close();
            }
        }

        public static DataTable getAllStudents()
        {
            return executeQuery("select name, last_name from studenti");
        }



        public static void deleteStudent(string name)
        {
            try
            {
		// Warning: SQL Injection!!
                executeUpdate("delete from studenti WHERE name = ('" + name + "')");
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
        }

        public static void insertNewStudent(string name, string lastName)
        {
            try
            {
		// Warning: SQL Injection!
                executeUpdate("insert into studenti(name, last_name) values('" + name + "', '" + lastName + "')");
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
        }

	private static void SqlCommandPrepareEx(string connectionString)
	{
    		using (SqlConnection connection = new SqlConnection(connectionString))
    		{
        	connection.Open();
	        SqlCommand command = new SqlCommand(null, connection);

        	// Create and prepare an SQL statement.
	        command.CommandText = "INSERT INTO Region (RegionID, RegionDescription) " +
        	    "VALUES (@id, @desc)";
	        SqlParameter idParam = new SqlParameter("@id", SqlDbType.Int, 0);
        	SqlParameter descParam = new SqlParameter("@desc", SqlDbType.Text, 100);
	        idParam.Value = 20;
        	descParam.Value = "First Region";
	        command.Parameters.Add(idParam);
        	command.Parameters.Add(descParam);

	        // Call Prepare after setting the Commandtext and Parameters.
        	command.Prepare();
	        command.ExecuteNonQuery();

        	// Change parameter values and call ExecuteNonQuery.
	        command.Parameters[0].Value = 21;
        	command.Parameters[1].Value = "Second Region";
	        command.ExecuteNonQuery();
    		}
	}

        static void Main(string[] args)
        {
            string mysqlConnStr = Properties.Settings.Default.epos_dbConnectionString;
            //Console.WriteLine(Properties.Settings.Default.epos_dbConnectionString);
            //executeUpdateBatch(
            //    "insert into studenti (name, last_name) values ('Aleksandar','Djordjevic')",
            //    "insert into studenti (name, last_name values ('Vlade','Divac')");
            //insertNewStudent("Pavle", "Pavlovic");
            //insertNewStudent("Janko", "Petrovic");

            // Demo code starts here
            DataTable dt = getAllStudents();
            foreach (DataRow row in dt.Rows)
            {
                string ispis = row["name"] + ", " + row["last_name"];
                Console.WriteLine(ispis);
            }
        }
    }
}