using System.Data;
using System.Data.Odbc;
using System.Data.SqlClient;
using MySql.Data.MySqlClient;

namespace SQLHelper
{

    public class SQL
    {

	public static DataTable GetDataSource(string sSQL, MySqlConnection conn) {
		DataTable dt 		= new DataTable();
		MySqlDataAdapter da 	= new MySqlDataAdapter(sSQL, conn);
		da.Fill( dt );
		return dt;
	}

	public static DataRowCollection Select( string sSQL, MySqlConnection conn )
	{
		DataSet ds = new DataSet();
		MySqlDataAdapter da = new MySqlDataAdapter (sSQL, conn);
		da.Fill(ds);
		return ds.Tables[0].Rows;
	}

	public static DataRow GetRow( string sSQL, MySqlConnection conn )
	{
		DataSet ds = new DataSet();
		MySqlDataAdapter da = new MySqlDataAdapter (sSQL, conn);
		da.Fill(ds);
		if(ds.Tables[0].Rows.Count != 0) {
		   return ds.Tables[0].Rows[0];
		} else {
		   return null;
		}
	}

        public static string GetField( string sSQL, MySqlConnection conn )
        {
        	DataSet ds = new DataSet();
		MySqlDataAdapter da = new MySqlDataAdapter (sSQL, conn);
		da.Fill(ds);
		if(ds.Tables[0].Rows.Count != 0) {
		   DataRow dr = ds.Tables[0].Rows[0];
		   return dr[0].ToString();
		} else {
		   return null;
		}
	}

	public static void Exec( string sSQL, MySqlConnection conn )
	{
		MySqlCommand cmd = new MySqlCommand(sSQL, conn);
		conn.Open();
		cmd.ExecuteNonQuery();
		conn.Close();
	}

        public static string InsertReturnIdentity( string sSQL, MySqlConnection conn )
        {
		try {
			string insertID = "";
			MySqlCommand cmd = new MySqlCommand(sSQL + "; SELECT @@IDENTITY", conn);
			conn.Open();
			IDataReader dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);

			while (dataReader.Read()) 
			{
			   insertID = dataReader[0].ToString();
			}
			dataReader.Close();
			
			return insertID;

		} catch(MySqlException e) {
			return e.ToString();
		}
	}

    }
}