using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;

/// <summary>
/// Summary description for basic
/// </summary>
public class basic
{
    public SqlConnection SqlConnection = new SqlConnection();
    protected SqlCommandBuilder SqlComBuilder;
    protected SqlCommand SqlCommand;
    public SqlDataReader SqlReader;
    public SqlDataAdapter SqlAdapter = new SqlDataAdapter();
    public DataSet DataSet = new DataSet();
    bool bool_target;
    int int_target;
    string string_target;
    public void FullDataSet(string TableName, string SqlSelect)
    {
        SqlConnection.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        SqlComBuilder = new SqlCommandBuilder(SqlAdapter);
        SqlAdapter.SelectCommand = new SqlCommand(SqlSelect, SqlConnection);
        SqlAdapter.Fill(DataSet, TableName); 
    }
    public void FullDataReader(string SqlSelect)
    {
        SqlConnection.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        SqlCommand = SqlConnection.CreateCommand();
        SqlCommand.CommandText = SqlSelect;
        SqlConnection.Open();
        SqlReader = SqlCommand.ExecuteReader();
    }
    public bool isExist(string TableName, string ColName, string Value)
    {
        FullDataReader("SELECT * FROM " + TableName + " WHERE " + ColName + "=" + Value);
        if (SqlReader.Read())
            bool_target = true;
        else
            bool_target = false;
        SqlReader.Close();
        SqlConnection.Close();
        return bool_target;
    }
    public int getLastId(string TableName, string ColName)
    {
        FullDataReader("SELECT MAX(" + ColName + ") FROM " + TableName);
        if (SqlReader.Read())
        {
            int_target = int.Parse(SqlReader[0].ToString());
        }
        else
            int_target = 0;
        return int_target;
    }
    string[] data;
    public string[] displayData(string TableName, string idCol, int id , int dataNo)
    {
        data = new string[dataNo];
        int counter = 0;
        FullDataReader("SELECT * FROM " + TableName + " WHERE " + idCol + "=" + id);
        while (SqlReader.Read())
        {
            data[counter] = SqlReader[counter].ToString();
        }
        return data;
    }
    DataTable DataTable = new DataTable();
    public DataTable displayTable(string TableName , string sqlSelect)
    {
        FullDataSet(TableName, sqlSelect);
        DataTable = DataSet.Tables[TableName];
        return DataTable;
    }
    public void deleteRow(string TableName, string idCol, int id)
    {
        FullDataSet(TableName, "SELECT * FROM " + TableName + " WHERE " + idCol + "=" + id);
        foreach (DataRow deletedRow in DataSet.Tables[TableName].Select())
        {
            deletedRow.Delete();
        }
        DataSet.AcceptChanges();
        SqlAdapter.Update(DataSet,TableName);
    }
    public int insertRow(string TableName, string[] data, int dataNo ,  string idCol)
    {
        FullDataSet(TableName, "SELECT * FROM " + TableName);
        DataRow newRow = DataSet.Tables[TableName].NewRow();
        for (int counter = 0; counter < dataNo; counter++)
        {
            newRow[counter + 1] = data[counter];
        }
        int newKey = getLastId(TableName, idCol) + 1;
        newRow[0] = newKey;
        DataSet.Tables[TableName].Rows.Add(newRow);
        DataSet.AcceptChanges();
        SqlAdapter.Update(DataSet, TableName);
        return newKey;
    }

    
// override from insertRow function , use it when u know Id value .
    public void insertRow(string TableName, string[] data, int dataNo , int id)
    {
        FullDataSet(TableName, "SELECT * FROM " + TableName);
        DataRow newRow = DataSet.Tables[TableName].NewRow();
        for (int counter = 1; counter < dataNo; counter++)
        {
            newRow[counter] = data[counter - 1];
        }
        newRow[0] = id;
        DataSet.Tables[TableName].Rows.Add(newRow);
        DataSet.AcceptChanges();
        SqlAdapter.Update(DataSet, TableName);
    }
    public void update(string TableName, string[] data, int dataNo , string idCol, int id)
    {
        FullDataSet(TableName, "SELECT * FROM " + TableName + " WHERE " + idCol + "=" + id );
        foreach (DataRow updatedRow in DataSet.Tables[TableName].Rows)
        {
            for(int counter = 1; counter < dataNo ; counter ++)
                updatedRow[counter] = data[counter - 1];
        }
        DataSet.AcceptChanges();
        SqlAdapter.Update(DataSet, TableName);
    }
}