using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Diagnostics;
using System.Collections;
using MySql.Data.MySqlClient;

public class SqlInterface : IDisposable
{
    public long lastInsertedId;
    private MySqlConnection conn;

    public SqlInterface()
    {
        this.conn = new MySqlConnection(ConfigurationManager.AppSettings["DSN"]);
        this.conn.Open();
    }

    public void Dispose()
    {
        this.conn.Dispose();
        GC.SuppressFinalize(this);
    }

    public string createInsert(string tableName, Hashtable data)
    {
        string sql = "INSERT INTO " + tableName + " ";

        string fieldNames;
        string fieldValues;

        fieldNames = "(";
        foreach (DictionaryEntry key in data)
            fieldNames += key.Key + ", ";
        fieldNames = fieldNames.Substring(0, fieldNames.Length - 2) + ")";

        fieldValues = "VALUES('";
        foreach (DictionaryEntry key in data)
            fieldValues += "'" + key.Value + "', ";
        fieldValues = fieldValues.Substring(0, fieldValues.Length - 2) + ")";

        sql += fieldNames + " " + fieldValues;
        return sql;
    }

    public string createUpdate(string tableName, Hashtable data, string clause)
    {
        string sql = "UPDATE " + tableName + " SET ";
        string updateSet = "";

        foreach (DictionaryEntry key in data)
            updateSet += key.Key + "='" + key.Value + "', ";
        updateSet = updateSet.Substring(0, updateSet.Length - 2);

        sql += updateSet + "WHERE " + clause;
        return sql;
    }

    public IDbCommand createUpdateCmd(string tableName, Hashtable data, string clause)
    {
        MySqlCommand cmd = new MySqlCommand();

        string sql = "UPDATE " + tableName + " SET ";
        string updateSet = "";

        foreach (DictionaryEntry key in data)
        {
            updateSet += key.Key + "=?" + key.Key + ", ";

            MySqlParameter param = new MySqlParameter();
            param.ParameterName = "?" + key.Key;
            param.Value = key.Value;
            param.SourceColumn = key.Key.ToString();
            cmd.Parameters.Add(param);
        }

        updateSet = updateSet.Substring(0, updateSet.Length - 2);
        sql += updateSet + " WHERE " + clause;
        cmd.CommandText = sql;

        return cmd;
    }

    public IDbCommand createInsertCmd(string tableName, Hashtable data)
    {
        string sql = "INSERT INTO " + tableName + " ";

        string fieldNames;
        string fieldValues;

        fieldNames = "(";
        fieldValues = "VALUES(";

        foreach (DictionaryEntry key in data)
        {
            fieldNames += key.Key + ", ";
            fieldValues += "?" + key.Key + ", ";
        }

        fieldNames = fieldNames.Substring(0, fieldNames.Length - 2) + ")";
        fieldValues = fieldValues.Substring(0, fieldValues.Length - 2) + ")";

        sql += fieldNames + " " + fieldValues;

        MySqlCommand cmd = new MySqlCommand(sql);

        foreach (DictionaryEntry key in data)
        {
            MySqlParameter param = new MySqlParameter();
            param.ParameterName = "?" + key.Key;
            param.Value = key.Value;
            param.SourceColumn = key.Key.ToString();
            cmd.Parameters.Add(param);
        }

        return cmd;
    }

    IDbCommand createSelectCmd(string tableName, ArrayList fieldNames, string clause)
    {
        string sql;
        string fields = "";
        sql = "SELECT ";

        ArrayList list = new ArrayList();

        foreach (Object field in fieldNames)
            fields += field + ", ";
        fields = fields.Substring(0, fields.Length - 2);

        sql += fields + " FROM " + tableName;

        if (clause != null)
            sql += " WHERE " + clause;
        
        MySqlCommand cmd = new MySqlCommand();
        return cmd;
    }

    public DataTable execute(MySqlCommand cmd)
    {
        cmd.Connection = this.conn;

        if (cmd.CommandText.StartsWith("INSERT") || cmd.CommandText.StartsWith("UPDATE"))
        {
            cmd.ExecuteNonQuery();
            this.lastInsertedId = cmd.LastInsertedId;
            return null;
        }
        else
        {
            MySqlDataReader reader = cmd.ExecuteReader();
            DataTable dataTable = new DataTable();
            dataTable.Load(reader);
            this.lastInsertedId = 0;
            return dataTable;
        }
    }

    public DataTable execute(string sql)
    {
        MySqlCommand cmd = new MySqlCommand(sql);

        if (sql.StartsWith("INSERT") || sql.StartsWith("UPDATE"))
        {
            cmd.ExecuteNonQuery();
            this.lastInsertedId = cmd.LastInsertedId;
            return null;
        }
        else
        {
            MySqlDataReader reader = cmd.ExecuteReader();
            DataTable dataTable = new DataTable();
            dataTable.Load(reader);
            this.lastInsertedId = 0;
            return dataTable;
        }
    }
}

///
///
/// NEXT CLASS
///
///


using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Diagnostics;
using System.Collections;

public class article : SqlInterface
{
    private string tableName = "Articles";

    public int articleId;

    public string title;
    public string body;
    public string date;
    public string author;

	public article()
	{
        
	}

    void get(int id)
    {
        IDbCommand cmd;

        ArrayList fields = new ArrayList();
        fields.Add("Title");
        fields.Add("Body");
        fields.Add("Date");
        fields.Add("Author");

        cmd = createSelectCmd(this.tableName, fields, "1 = 1");
        execute(cmd);
    }
}