using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

namespace WebApplication11
{
    public class DataLayer
    {
        SqlConnection Con;
        string S_Con;
        public DataLayer()
        {
            connection();
        }
        private void connection()
        {
            S_Con = ConfigurationManager.ConnectionStrings["Db"].ConnectionString;
            Con = new SqlConnection(S_Con);
            if (Con.State == System.Data.ConnectionState.Open)
            {
                Con.Close();
            }
            Con.Open();
        }
        public List<Student> GetStudentList(int id = 0)
        {
            try
            {
                SqlCommand cmd = new SqlCommand("sp_StJal", Con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@ProcId", 1);
                if (id != 0)
                {
                cmd.Parameters.Add("@ID", id); }
                SqlDataReader rdr = cmd.ExecuteReader();
                List<Student> mlist = new List<Student>();
                while (rdr.Read())
                {
                    Student ob = new Student();
                    ob.ID = Convert.ToInt32(rdr["ID"]);
                    ob.Name = rdr["Name"].ToString();
                    ob.Age = Convert.ToInt32(rdr["Age"]);
                    ob.Email = rdr["Email"].ToString();
                    mlist.Add(ob);
                }
                return mlist;
            }


            catch (Exception E)
            {
                return null;
            }
        }
        public bool AddStudent(Student ob)
        {
            SqlCommand cmd = new SqlCommand("sp_StJal", Con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@Procid", 2);
            cmd.Parameters.Add("@Name", ob.Name);
            cmd.Parameters.Add("@Age", ob.Age);
            cmd.Parameters.Add("@Email", ob.Email);
            cmd.ExecuteNonQuery();
            return true;
        }
    }
}