using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;

namespace DSApp
{
    class MyDataSet
    {
        DataSet ds;
        SqlDataAdapter da;
        SqlConnection con;

        public MyDataSet()
        {
            ds = new DataSet();
            // createStudent();
            ConnectDatabase();
        }
        void ConnectDatabase()
        {
            con = new SqlConnection("server=UNISRV;uid=cs;pwd=cs;database=csharp");
            con.Open();
            da = new SqlDataAdapter("select * from student", con);
            da.Fill(ds, "student");

            SqlCommandBuilder cb = new SqlCommandBuilder(da);
            da.InsertCommand = cb.GetInsertCommand();
            da.UpdateCommand = cb.GetUpdateCommand();
            da.DeleteCommand = cb.GetDeleteCommand();
        }
        void createStudent()
        {
            DataTable dt = new DataTable("Student");

            DataColumn dc1 = new DataColumn("rno", typeof(int));
            DataColumn dc2 = new DataColumn("nm", typeof(string));
            DataColumn dc3 = new DataColumn("phy", typeof(int));
            DataColumn dc4 = new DataColumn("che", typeof(int));
            DataColumn dc5 = new DataColumn("mat", typeof(int));

            dt.Columns.Add(dc1);
            dt.Columns.Add(dc2);
            dt.Columns.Add(dc3);
            dt.Columns.Add(dc4);
            dt.Columns.Add(dc5);

            dt.PrimaryKey = new DataColumn[] { dc1 };

            ds.Tables.Add(dt);
        }
        public DataRow GetNewRow()
        {
            return ds.Tables["student"].NewRow();
        }
        public void AddRow(DataRow dr)
        {
            ds.Tables[0].Rows.Add(dr);
        }
        public int GetCount()
        {
            return ds.Tables[0].Rows.Count;
        }
        public DataRow GetRow(int i)
        {
            return ds.Tables[0].Rows[i];
        }
        public void Update()
        {
            da.Update(ds, "student");
        }
        public void Close()
        {
            con.Close();
        }
        public void DeleteRow(int n)
        {
            ds.Tables[0].Rows[n].Delete();
            Update();
        }
    }
}