using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using EMISOnline.DataLogic.Repositories;

namespace EMISOnline.DataLogic.ExamSetting
{
    public class SqlHelper
    {
        public paperRepository PaperRepository { get; set; }
        public DataSet Query(string storedProcedure, params KeyValuePair<string, object>[] param)
        {
            using (var connection = new SqlConnection(PaperRepository.UnitOfWork.Database.Connection.ConnectionString))
            {
                var cmd = new SqlCommand(storedProcedure, connection);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                foreach (var kv in param)
                {
                    var p = cmd.CreateParameter();
                    p.ParameterName = kv.Key;
                    p.Value = kv.Value ?? DBNull.Value;
                    cmd.Parameters.Add(p);
                }
                var sqlAdapter = new SqlDataAdapter(cmd);
                var ds = new DataSet();
                sqlAdapter.Fill(ds);
                return ds;
            }
        }
        public void Execute(string storedProcedure, params KeyValuePair<string, object>[] param)
        {
            using (var connection = new SqlConnection(PaperRepository.UnitOfWork.Database.Connection.ConnectionString))
            {
                var cmd = new SqlCommand(storedProcedure, connection);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                foreach (var kv in param)
                {
                    var p = cmd.CreateParameter();
                    p.ParameterName = kv.Key;
                    p.Value = kv.Value ?? DBNull.Value;
                    cmd.Parameters.Add(p);
                }
                try
                {
                    cmd.Connection.Open();
                    cmd.ExecuteNonQuery();
                }
                finally
                {
                    if (cmd.Connection.State == ConnectionState.Open)
                        cmd.Connection.Close();
                }
            }
        }
    }
}