using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

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

namespace f00DisconnectedDataset
{
    class DAL_NTX
    {
        //ctor

        public DAL_NTX() { }

        public DAL_NTX(string s)
        {
            ConnString = s;
        }

        //properties
        public string ConnString { get; set;}

        private void AssignParams(SqlParameterCollection cmdParams, SqlParameter[] sqlParams)
        {
            if (sqlParams == null) return;

            for (int i = 0; i < sqlParams.Length; i++)
            {
                cmdParams.Add(sqlParams[i]);
            }
        }

        //methods
        private IDbConnection DBGetConn(bool bOpen)
        {
            SqlConnection cn = null;

            try
            {
                cn = new SqlConnection();
                cn.ConnectionString = ConnString;
                if (bOpen) cn.Open();
            }
            catch (Exception ex)
            {
                throw;
            }

            return cn;
        }

        public DataSet DBGetDataSet(string sqlCmd,SqlParameter[] sqlParams)
        {
            SqlConnection cn = null;
            DataSet ds = null;
            SqlDataAdapter adr = null;

            try
            {
                cn = (SqlConnection)DBGetConn(false);
                adr = new SqlDataAdapter(sqlCmd, cn);
                ds = new DataSet();
                ds.EnforceConstraints = false;

                adr.SelectCommand.CommandType = CommandType.StoredProcedure;

                AssignParams(adr.SelectCommand.Parameters, sqlParams);

                adr.Fill(ds); //opens and closes the DB connection automatically !!
            }
            catch (Exception ex)
            {
                throw;
            }

            return ds;
        }

        public DataTable DBGetDataTable(string sqlCmd, SqlParameter[] sqlParams)
        {
            SqlConnection cn = null;
            DataTable dt = null;
            SqlDataAdapter adr = null;

            try
            {
                cn = (SqlConnection)DBGetConn(false);
                adr = new SqlDataAdapter(sqlCmd, cn);

                adr.SelectCommand.CommandType = CommandType.StoredProcedure;

                AssignParams(adr.SelectCommand.Parameters, sqlParams);

                dt = new DataTable();
                adr.Fill(dt); //opens and closes the DB connection automatically !!
            }
            catch (Exception ex)
            {
                throw;
            }
            return dt;
        }


    }
}