using System;
using System.Data.SqlClient;
using System.Collections;
using System.Collections.Generic;
using System.Data.SqlTypes;
using System.Text;
using MySql.Data.MySqlClient;
//using MarketData.DataAccess;

namespace CopyDb
{
/// <summary>SQlUtils - SQL utility class</summary>
  public class SqlUtils
  {
    public static int COMMAND_TIMEOUT=300;
    public static int MIN_POOL_SIZE=10;
    public static int MAX_POOL_SIZE=100;

/// <summary>CreateMySqlConnection - Creates an SQL connection.</summary>
/// <param name="datasource">datasource.</param>
/// <param name="database">database.</param>
/// <param name="username">username.</param>
/// <param name="password">users password.</param>
/// <returns>SqlConnection or null on error.</returns>
    public static MySqlConnection CreateMySqlConnection(string datasource,string port,string database,string username,string password)
    {
      try
      {
        String connectionString=null;
        StringBuilder sb=new StringBuilder();
        sb.Append("server=").Append(datasource).Append("; ");
        sb.Append("user id=").Append(username).Append("; ");
        sb.Append("password=").Append(password).Append("; ");
        sb.Append("database=").Append(database).Append("; ");
        sb.Append("SslMode=").Append("None").Append("; ");
        sb.Append("pooling=").Append("true").Append(";");
        sb.Append("allow user variables=").Append("true").Append("; ");
        sb.Append("default command timeout=").Append("300").Append("; ");
        sb.Append("connect timeout=").Append("300").Append(" ");
        connectionString=sb.ToString();
        MySqlConnection connection=new MySqlConnection(connectionString);
        connection.Open();
        return connection;
      }
      catch(SqlException exception)
      {
        throw exception;
        //MDTrace.WriteLine(LogLevel.DEBUG,exception.ToString());
        //return null;
      }
    }
/// <summary>CreateSqlConnection - Creates an SQL connection.</summary>
/// <param name="datasource">the datasource.</param>
/// <returns>SqlConnection or null on error.</returns>
    //public static MySqlConnection CreateMySqlConnection(DataSourceEx datasource)
    //{
    //  try
    //  {
    //    String connectionString=null;
    //    StringBuilder sb=new StringBuilder();
    //    sb.Append("server=").Append(datasource.Datasource).Append("; ");
    //    sb.Append("user id=").Append(datasource.Username).Append("; ");
    //    sb.Append("password=").Append(datasource.Password).Append("; ");
    //    sb.Append("database=").Append(datasource.Database).Append("; ");
    //    sb.Append("SslMode=").Append("None").Append("; ");
    //    sb.Append("pooling=").Append("true").Append(";");
    //    sb.Append("allow user variables=").Append("true").Append("; ");
    //    sb.Append("default command timeout=").Append("300").Append("; ");
    //    sb.Append("connect timeout=").Append("300").Append(" ");
    //    connectionString=sb.ToString();
    //    MySqlConnection connection=new MySqlConnection(connectionString);
    //    connection.Open();
    //    return connection;
    //  }
    //  catch(SqlException exception)
    //  {
    //    MDTrace.WriteLine(LogLevel.DEBUG,exception.ToString());
    //    return null;
    //  }
    //}    
    //public static String FormatDate(DateTime dateTime)
    //{
    //  return Utility.DateTimeToStringYYYYHMMHDD(dateTime);
    //}
/// <summary>CreateInClause - Creates an In Clause.</summary>
/// <param name="list">ArrayList of items.</param>
/// <returns>String.</returns>
    public static String CreateInClause(ArrayList list)
    {
		  StringBuilder sb=new StringBuilder();
			Hashtable hashtable=new Hashtable();
			foreach(string item in list)if(!hashtable.ContainsKey(item))hashtable.Add(item,item);
			list=new ArrayList(hashtable.Keys);
		  sb.Append("(");
		  for(int index=0;index<list.Count;index++)
		  {
			  sb.Append("'").Append((string)list[index]).Append("'");
			  if(index<list.Count-1)sb.Append(",");
		  }
		  sb.Append(")");
		  return sb.ToString();
	  }
/// <summary>CreateInClause - Creates an In Clause.</summary>
/// <param name="list">List<String></String> of items.</param>
/// <returns>String.</returns>
    public static String CreateInClause(List<String> list)
    {
      StringBuilder sb = new StringBuilder();
      Hashtable hashtable = new Hashtable();
      foreach (string item in list) if (!hashtable.ContainsKey(item)) hashtable.Add(item, item);
      ArrayList uniqueKeys = new ArrayList(hashtable.Keys);
      sb.Append("(");
      for (int index = 0; index < uniqueKeys.Count; index++)
      {
        sb.Append("'").Append((string)uniqueKeys[index]).Append("'");
        if (index < uniqueKeys.Count - 1) sb.Append(",");
      }
      sb.Append(")");
      return sb.ToString();
    }
/// <summary>CreateInClause - Creates an In Clause.</summary>
/// <param name="list">ArrayList of items.</param>
/// <returns>String.</returns>
    public static String CreateInClauseInt(List<String> list)
    {
      ArrayList uniqueList=new ArrayList();
		  StringBuilder sb=new StringBuilder();
			Hashtable hashtable=new Hashtable();
			foreach(string item in list)if(!hashtable.ContainsKey(item))hashtable.Add(item,item);
			uniqueList=new ArrayList(hashtable.Keys);
		  sb.Append("(");
		  for(int index=0;index<uniqueList.Count;index++)
		  {
			  sb.Append((string)uniqueList[index]);
			  if(index<uniqueList.Count-1)sb.Append(",");
		  }
		  sb.Append(")");
		  return sb.ToString();
	  }
/// <summary>CreateInClause - Creates an In Clause.</summary>
/// <param name="list">ArrayList of items.</param>
/// <returns>String.</returns>
    public static String CreateInClauseInt(List<int> list)
    {
      ArrayList uniqueList=new ArrayList();
		  StringBuilder sb=new StringBuilder();
			Hashtable hashtable=new Hashtable();
			foreach(int item in list)if(!hashtable.ContainsKey(item))hashtable.Add(item,item);
			uniqueList=new ArrayList(hashtable.Keys);
		  sb.Append("(");
		  for(int index=0;index<uniqueList.Count;index++)
		  {
			  sb.Append((int)uniqueList[index]);
			  if(index<uniqueList.Count-1)sb.Append(",");
		  }
		  sb.Append(")");
		  return sb.ToString();
	  }
    /// <summary>CreateInClause - Creates an In Clause.</summary>
    /// <param name="list">ArrayList of items.</param>
    /// <returns>String.</returns>
    //public static String CreateInClauseYear(List<DateTime> list)
    //{
    //  ArrayList uniqueList = new ArrayList();
    //  StringBuilder sb = new StringBuilder();
    //  Hashtable hashtable = new Hashtable();
    //  foreach (DateTime item in list)
    //  {
    //    String strRep = Utility.DateTimeToStringYYYYHMMHDD(item);
    //    if (!hashtable.ContainsKey(strRep)) hashtable.Add(strRep, strRep);
    //  }
    //  uniqueList = new ArrayList(hashtable.Keys);
    //  sb.Append("(");
    //  for (int index = 0; index < uniqueList.Count; index++)
    //  {
    //    sb.Append(Utility.AddQuotes((string)uniqueList[index]));
    //    if (index < uniqueList.Count - 1) sb.Append(",");
    //  }
    //  sb.Append(")");
    //  return sb.ToString();
    //}
// 
    public static String CreateInClause(List<int> list)
    {
      StringBuilder sb = new StringBuilder();
      sb.Append("(");
      for (int index = 0; index < list.Count; index++)
      {
        sb.Append(list[index].ToString());
        if (index < list.Count - 1) sb.Append(",");
      }
      sb.Append(")");
      return sb.ToString();
    }
/// <summary>MinDate - Return minimum sql date.</summary>
/// <returns>DateTime</returns>
    //public static DateTime MinSqlDate()
    //{
    //  return SqlDateTime.MinValue.Value;
    //}
    //public static String AddQuotes(String item)
    //{
    //  return "'" + item + "'";
    //}
    //public static String SqlDate(DateTime dateTime)
    //{
    //  if (Utility.IsEpoch(dateTime)) return null;
    //  return Utility.DateTimeToStringYYYYHMMHDD(dateTime);
    //}
    //public static String ToSqlDateTime(DateTime dateTime)
    //{
    //  return Utility.DateTimeToStringYYYYHMMHDDHHMMSS(dateTime);
    //}
    //public static String ToSqlDateTimeTT(DateTime dateTime)
    //{
    //  return Utility.DateTimeToStringYYYYHMMHDDHHMMSSTT(dateTime);
    //}
/// <summary>SqlString - Return minimum sql date.</summary>
/// <returns>DateTime</returns>
    public static String SqlString(String value)
    {
      StringBuilder sb=new StringBuilder();
      foreach(char ch in value)
      {
        sb.Append(ch);
        if(ch=='\'')sb.Append("'");
      }
      return sb.ToString();
    }
    public static String ToSqlString(String value)
    {
      StringBuilder sb=new StringBuilder();
      return sb.Append("'").Append(SqlString(value)).Append("'").ToString();
    }
  }
}