using System;
using System.Data;
using System.Data.SqlClient;

//Provides simple operations with (ms) sql database
public class SQL
{
    private string _ConnectionString;
    private readonly SqlConnection sqlConnection = new SqlConnection();
    private readonly SqlDataAdapter sqlAdapter = new SqlDataAdapter();
    private SqlCommand sqlCommand = new SqlCommand();

    private Exception _Error;
    private DataSet dataSet = new DataSet("dataSet");
    private bool _IsInit;

    public bool IsInit
    {
        get
        {
            return _IsInit;
        }
    }
    public Exception Error
    {
        get
        {
            return _Error;
        }
    }

    public SQL()
    {
        _IsInit = false;
    }

    public SQL(string ConnectionString)
    {
        Init(ConnectionString);
    }

    public void Init(string ConnectionString)
    {
        _ConnectionString = ConnectionString;
        _IsInit = true;
        try
        {
            if (_ConnectionString == null & _IsInit == false) throw new Exception("Connection string is empty.");
            InitTables();
        }
        catch (Exception Ex)
        {
            _Error = Ex;
            _IsInit = false;
            return;
        }
    }

    private void CheckIsInit()
    {
        _Error = null;
        if (_IsInit == false) throw new Exception("Connection to database does not initialized properly.");
    }

    //Opens sql connection
    private bool Open()
    {
        CheckIsInit();
        if (sqlConnection.State == ConnectionState.Closed)
        {
            sqlConnection.ConnectionString = _ConnectionString;
            sqlConnection.Open();
        }

        return true;
    }

    //Close sql connection
    private bool Close()
    {
        if (sqlConnection.State == ConnectionState.Open)
        {
            sqlConnection.Close();
        }

        return true;
    }

    //Convert sql type to .net
    private static Type DotNetType(string szType)
    {
        string DotNetType = "";
        switch (szType)
        {
            case "int":
                DotNetType = "System.Int32";
                break;
            case "smallint":
                DotNetType = "System.Int16";
                break;
            case "tinyint":
                DotNetType = "System.Byte";
                break;
            case "bigint":
                DotNetType = "System.Int64";
                break;
            case "money":
                DotNetType = "System.Decimal";
                break;
            case "smallmoney":
                DotNetType = "System.Decimal";
                break;
            case "numeric":
                DotNetType = "System.Decimal";
                break;
            case "decimal":
                DotNetType = "System.Decimal";
                break;
            case "real":
                DotNetType = "System.Single";
                break;
            case "float":
                DotNetType = "System.Double";
                break;
            case "bit":
                DotNetType = "System.Boolean";
                break;
            case "datetime":
                DotNetType = "System.DateTime";
                break;
            case "smalldatetime":
                DotNetType = "System.DateTime";
                break;
            case "rowversion":
                DotNetType = "System.Byte[]";
                break;
            case "uniqueidentifier":
                DotNetType = "System.Guid";
                break;
            case "ntext":
                DotNetType = "System.String";
                break;
            case "text":
                DotNetType = "System.String";
                break;
            case "varchar":
                DotNetType = "System.String";
                break;
            case "char":
                DotNetType = "System.String";
                break;
            case "nvarchar":
                DotNetType = "System.String";
                break;
            case "image":
                DotNetType = "System.Byte[]";
                break;
            case "varbinary":
                DotNetType = "System.Byte[]";
                break;
            case "binary":
                DotNetType = "System.Byte[]";
                break;
            default:
                DotNetType = "System.String";
                break;
        }
        return Type.GetType(DotNetType);
    }

    //Convert sql type to SqlDbType
    private static SqlDbType SQLDbType(string szType)
    {
        SqlDbType Result;
        switch (szType)
        {
            case "int":
                Result = SqlDbType.Int;
                break;
            case "smallint":
                Result = SqlDbType.SmallInt;
                break;
            case "tinyint":
                Result = SqlDbType.TinyInt;
                break;
            case "bigint":
                Result = SqlDbType.BigInt;
                break;
            case "money":
                Result = SqlDbType.Money;
                break;
            case "smallmoney":
                Result = SqlDbType.SmallMoney;
                break;
            case "numeric":
                Result = SqlDbType.Decimal;
                break;
            case "decimal":
                Result = SqlDbType.Decimal;
                break;
            case "real":
                Result = SqlDbType.Real;
                break;
            case "float":
                Result = SqlDbType.Float;
                break;
            case "bit":
                Result = SqlDbType.Bit;
                break;
            case "datetime":
                Result = SqlDbType.DateTime;
                break;
            case "smalldatetime":
                Result = SqlDbType.SmallDateTime;
                break;
            //case "rowversion":
            //Result = SqlDbType.;
            //break;
            case "uniqueidentifier":
                Result = SqlDbType.UniqueIdentifier;
                break;
            case "ntext":
                Result = SqlDbType.NText;
                break;
            case "text":
                Result = SqlDbType.Text;
                break;
            case "varchar":
                Result = SqlDbType.VarChar;
                break;
            case "char":
                Result = SqlDbType.Char;
                break;
            case "nvarchar":
                Result = SqlDbType.NVarChar;
                break;
            case "image":
                Result = SqlDbType.Image;
                break;
            case "varbinary":
                Result = SqlDbType.VarBinary;
                break;
            case "binary":
                Result = SqlDbType.Binary;
                break;
            case "xml":
                Result = SqlDbType.Xml;
                break;
            default:
                Result = SqlDbType.VarChar;
                break;
        }
        return Result;
    }

    //Inits tables in dataSet 
    private int InitTables()
    {
        dataSet.Tables.Clear();
        if (dataSet.Tables["INFORMATION_SCHEMA.COLUMNS"] != null)
        {
            dataSet.Tables["INFORMATION_SCHEMA.COLUMNS"].Clear();
            dataSet.Tables["INFORMATION_SCHEMA.COLUMNS"].Columns.Clear();
        }

        else dataSet.Tables.Add("INFORMATION_SCHEMA.COLUMNS");
        dataSet.Tables["INFORMATION_SCHEMA.COLUMNS"].Columns.Add("TABLE_NAME", Type.GetType("System.String"));
        dataSet.Tables["INFORMATION_SCHEMA.COLUMNS"].Columns.Add("COLUMN_NAME", Type.GetType("System.String"));
        dataSet.Tables["INFORMATION_SCHEMA.COLUMNS"].Columns.Add("DATA_TYPE", Type.GetType("System.String"));
        sqlCommand = new SqlCommand("select [TABLE_NAME], [COLUMN_NAME], [DATA_TYPE] from INFORMATION_SCHEMA.COLUMNS ORDER BY TABLE_NAME", sqlConnection);
        sqlAdapter.SelectCommand = sqlCommand;
        Open();
        sqlAdapter.Fill(dataSet.Tables["INFORMATION_SCHEMA.COLUMNS"]);
        using (DataTable DBTables = new DataTable())
        {
            sqlCommand.CommandText = "select TABLE_NAME from INFORMATION_SCHEMA.TABLES  ORDER BY TABLE_NAME";
            sqlAdapter.Fill(DBTables);

            for (int i = 0; i < DBTables.Rows.Count; i++)
            {
                DataRow[] Columns = dataSet.Tables["INFORMATION_SCHEMA.COLUMNS"].Select(String.Format("TABLE_NAME = '{0}'", DBTables.Rows[i]["TABLE_NAME"]));
                string TableName = DBTables.Rows[i]["TABLE_NAME"].ToString();
                dataSet.Tables.Add(TableName);
                for (int n = 0; n < Columns.Length; n++)
                {
                    string ColumnName = Columns[n]["COLUMN_NAME"].ToString();
                    Type DataType = DotNetType(Columns[n]["DATA_TYPE"].ToString());

                    dataSet.Tables[TableName].Columns.Add(ColumnName, DataType);
                }
            }
        }
        Close();

        return 0;
    }

    //Fills specific data table in dataSet with data
    private DataTable FillTable(string Table, string Filter)
    {
        DataTable Result = new DataTable();
        try
        {
            CheckIsInit();
            lock (_ConnectionString)
            {
                sqlCommand.CommandText = String.Format("SELECT * FROM [{0}] {1}", Table, (Filter == null ? "" : " Where " + Filter));
                sqlAdapter.SelectCommand = sqlCommand;
            
                //Open();
                sqlAdapter.Fill(Result);
                //Close();
            }
        }
        catch (Exception ex)
        {
            _Error = ex;
            return null;
        }
        return Result;
    }

    //Inserting row to specific table. 
    //TableName - Table name in database.
    //DataItems - Inserting data items. Items should be in same order as corresponding columns in table.
    //To skip some columns, just put null instead of its data item.
    public int AddRow(string TableName, params object[] DataItems)
    {
        string Columns = "";
        string Values = "";
        int Result = 0;
        try
        {
            CheckIsInit();
            if (DataItems.Length != dataSet.Tables[TableName].Columns.Count) throw new Exception(String.Format("Number of data items doesn't match number of columns in table \"{0}\".", TableName));
            lock (_ConnectionString)
            {
                sqlCommand.Parameters.Clear();
                for (int i = 0; i < dataSet.Tables[TableName].Columns.Count; i++)
                {
                    // excepting columns
                    if (DataItems[i] == null) continue;
                    // adding column names
                    if (Columns != "") Columns += ",";
                    Columns += String.Format("[{0}]", dataSet.Tables[TableName].Columns[i]);
                    // filling with data
                    string ParamName = "@" + dataSet.Tables[TableName].Columns[i].ColumnName;
                    Values += ParamName + ",";
                    sqlCommand.Parameters.AddWithValue(ParamName, DataItems[i]);
                }
                Values = Values.Substring(0, Values.Length - 1);
                sqlCommand.CommandText = String.Format("INSERT INTO [{0}] ({1}) VALUES ({2}) SELECT @@IDENTITY", TableName, Columns, Values);
                string szRet = "";
            
                Open();
                szRet = sqlCommand.ExecuteScalar().ToString();
                Close();

                Result = Convert.ToInt32(szRet == "" ? "0" : szRet);
            }
            
        }
        catch (Exception Ex)
        {
            _Error = Ex;
            return -1;
        }
        return Result;
    }

    //Updating rows in specific table. 
    //TableName - Table name in database.
    //Condition - Data selection codition, for example "id=1".
    //DataItems - Inserting data items. Items should be in same order as corresponding columns in table.
    //To skip some columns, just put null instead of its data item.
    public int UpdateRow(string TableName, string Condition, params object[] DataItems)
    {
        string Values = "";
        int Result;
        try
        {
            CheckIsInit();
            if (DataItems.Length != dataSet.Tables[TableName].Columns.Count) throw new Exception(String.Format("Number of data items doesn't match number of columns in table \"{0}\".", TableName));
            lock (_ConnectionString)
            {
                sqlCommand.Parameters.Clear();
                for (int i = 0; i < dataSet.Tables[TableName].Columns.Count; i++)
                {
                    // excepting columns
                    if (DataItems[i] == null) continue;
                    // adding columns names
                    if (Values != "") Values += ",";
                    Values += String.Format("[{0}]", dataSet.Tables[TableName].Columns[i]);
                    // filling with data
                    string ParamName = "@" + dataSet.Tables[TableName].Columns[i].ColumnName;
                    Values += " = " + ParamName;
                    sqlCommand.Parameters.AddWithValue(ParamName, DataItems[i]);
                }
                sqlCommand.CommandText = String.Format("UPDATE [{0}] SET {1} WHERE {2}", TableName, Values, Condition);
            
                Open();
                Result = sqlCommand.ExecuteNonQuery();
                Close();
            }
        }
        catch (Exception Ex)
        {
            _Error = Ex;
            return -1;
        }
        return Result;
    }

    //Deleting rows from specific table.
    //TableName - Table name in database.
    //Condition - Data selection codition, for example "id=1".
    public int DeleteRow(string TableName, string Condition)
    {
        int Result;
        try
        {
            CheckIsInit();
            lock (_ConnectionString)
            {
                sqlCommand.CommandText = String.Format("DELETE FROM [{0}] WHERE {1}", TableName, Condition);
                Open();
                Result = sqlCommand.ExecuteNonQuery();
                Close();
            }
        }
        catch (Exception Ex)
        {
            _Error = Ex;
            return -1;
        }
        return Result;
    }

    //Executes custom databse query.
    //Query - TSQL custom query.
    public int ExecuteQuery(string Query)
    {
        int Result = 0;
        try
        {
            CheckIsInit();
            lock (_ConnectionString)
            {
                sqlCommand.CommandText = Query;
                object ob = null;
            
                Open();
                ob = sqlCommand.ExecuteScalar();
                Close();

                Result = Convert.ToInt32(ob == null ? "0" : (ob.ToString() == "" ? "0" : ob));
            }
            
        }
        catch (Exception Ex)
        {
            _Error = Ex;
            return -1;
        }
        return Result;
    }

    //Executes custom databse query with filling DataTable.
    //ResultTable - DataTable object contains query resul.
    //Query - TSQL custom query.
    public int ExecuteQueryAndFillDataTable(out DataTable ResultTable, string Query)
    {
        ResultTable = new DataTable();
        int ret = 0;
        try
        {
            CheckIsInit();
            lock (_ConnectionString)
            {
                sqlCommand.CommandText = Query;
                sqlAdapter.SelectCommand = sqlCommand;
            
                Open();
                ret = sqlAdapter.Fill(ResultTable);
                Close();
            }
        }
        catch (Exception Ex)
        {
            _Error = Ex;
            return -1;
        }
        return ret;
    }

    //Returns DataTable object with specific condition.
    //TableName - Table name in database.
    //Condition - Data selection codition, for example "id=1".
    public DataTable GetDataTable(string TableName, string Condition)
    {
        return FillTable(TableName, Condition);
    }

    public DataTable GetDataTablePage(string TableName, string Condition, int PageSize, int PageNumber, string OrderColumn, string Sorting = "Asc")
    {
        DataTable Result = new DataTable();

        string query = string.Format("SELECT * FROM (SELECT TOP {1} ROW_NUMBER() OVER(ORDER BY {4} " + Sorting + ") As [Number], * FROM {0} {3}) AS t1 WHERE [Number] NOT IN (SELECT TOP {2} ROW_NUMBER() OVER(ORDER BY {4} " + Sorting + ") As [Number] From {0})", TableName, PageSize * (PageNumber + 1), PageSize * PageNumber, (string.IsNullOrEmpty(Condition) ? "" : "WHERE " + Condition), OrderColumn);
        ExecuteQueryAndFillDataTable(out Result, query);
        return Result;
    }

    //Returns server date format.
    public string GetDateFormat()
    {
        string Result;
        try
        {
            CheckIsInit();
            sqlCommand = new SqlCommand("select date_format from sys.dm_exec_requests where session_id = @@spid", sqlConnection);
            Open();
            Result = sqlCommand.ExecuteScalar().ToString();
            Close();
        }
        catch (Exception ex)
        {
            _Error = ex;
            return null;
        }
        return Result;
    }
}

class Product
{
    string Id;
    string Name;
}
DataTable dt = sql.FillTable("Product",null);

Product p = new Product();
p.Id = dt.Rows[0]["Id"].TString();
p.Name = dt.Rows[0]["Name"].TString();

class Product
{
    string Id;
    string Name;
}
DataTable dt = sql.FillTable("Product",null);

Product p = new Product();
p.Id = dt.Rows[0]["Id"].TString();
p.Name = dt.Rows[0]["Name"].TString();