using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using PersonAPI.Models;

using System.Web.Http;

namespace PersonAPI.Controllers
{
    public class PersonController : ApiController
    {
        DB DBlayer = new DB();

        string message = string.Empty;
        // GET: Person


        public IEnumerable<string> Get()
        {
            return new string[] { "value1", "value2" };
        }

        [System.Web.Http.HttpGet]

        public DataSet GetRecord(int id)
        {
            DataSet ds = DBlayer.GetRecordbyID(id, out message);
            return ds;
        }

        [System.Web.Http.HttpPost]
        public string PostCreatePerson([FromBody] PersonModel person)
        {
            string message = "";
            message = DBlayer.InsertPerson(person);
            return message;
        }

        [System.Web.Http.HttpPut]

        public string PutUpdatePerson(int id, [FromBody] PersonModel person) 
        {
            string message = "";
            message = DBlayer.UpdatePerson(person);
            return message;
        }

        [System.Web.Http.HttpDelete]

        public string DeletePerson(int id)
        {
            string message = "";
            message = DBlayer.DeletePerson(id);
            return message;
        }
    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using PersonAPI.Models;

namespace PersonAPI
{
    public class DB
    {
        public SqlConnection ConnecttoDB()
        {
            string connectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename='D:\NUV\Second Year\Sem 4\ASP.NET\PersonAPI\PersonAPI\App_Data\Person.mdf';Integrated Security=True";
            SqlConnection con = new SqlConnection(connectionString);
            con.Open();
            return con;

        }

        public DataSet GetRecordbyID(int id, out string message)
        {
            message = "";
            SqlConnection con = ConnecttoDB();
            SqlCommand com = new SqlCommand("getPersonbyID", con);
            com.CommandType = System.Data.CommandType.StoredProcedure;
            com.Parameters.AddWithValue("@id", id);

            SqlDataAdapter adp = new SqlDataAdapter(com);
            DataSet ds = new DataSet();
            adp.Fill(ds);

            return ds;
            con.Close();
            message = "Success";
        }

        public string InsertPerson(PersonModel person)
        {
            string message = "";
            SqlConnection con = ConnecttoDB();
            SqlCommand com = new SqlCommand("gpInsertPerson", con);
            com.CommandType = CommandType.StoredProcedure;

            com.Parameters.AddWithValue("@id", person.id);
            com.Parameters.AddWithValue("@Name", person.Name);
            com.Parameters.AddWithValue("@City", person.City);
            com.Parameters.AddWithValue("@Phoneno", person.phoneno);

            com.ExecuteNonQuery();
            message = "Success";
            return message;

        }

        public string UpdatePerson(PersonModel person)
        {
            string message = "";
            SqlConnection con = ConnecttoDB();
            SqlCommand com = new SqlCommand("gpUpdatePerson", con);
            com.CommandType = CommandType.StoredProcedure;

            com.Parameters.AddWithValue("@id", person.id);
            com.Parameters.AddWithValue("@Name", person.Name);
            com.Parameters.AddWithValue("@City", person.City);
            com.Parameters.AddWithValue("@Phoneno", person.phoneno);

            com.ExecuteNonQuery();
            message = "Success";
            return message;

        }

        public string DeletePerson(int id)
        {
            string message = "";
            SqlConnection con = ConnecttoDB();
            SqlCommand com = new SqlCommand("spDeletePerson", con);
            com.CommandType = CommandType.StoredProcedure;

            com.Parameters.AddWithValue("@id", id);


            com.ExecuteNonQuery();
            message = "Success";
            return message;

        }

    }

}

public int id { get; set; }

public string Name { get; set; }

public string City { get; set; }

public string phoneno { get; set; }

CREATE PROCEDURE [dbo].[DeletePerson]
    @id int
AS
BEGIN
    delete from Person where Id = @id;
END;

CREATE PROCEDURE [dbo].[getInsertPerson]
    @id int,
    @name varchar(50),
    @city varchar(50),
    @phone nvarchar(50)
AS
BEGIN
    insert into Person (ID, Name, City, Phone) values (@id,@name, @city, @phone);
END;

CREATE PROCEDURE [dbo].[getPerson]

AS
BEGIN
    SELECT * from Person
END;

CREATE PROCEDURE [dbo].[getPerson]

AS
BEGIN
    SELECT * from Person
END;

CREATE PROCEDURE [dbo].[getUpdatePersonn]
    @id int,
    @name varchar(50),
    @city varchar(50),
    @phone nvarchar(50)
AS
BEGIN
    Update Person set Name = @name, City = @city, Phone = @phone where Id = @id
END