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

 namespace Prototype3.Database_Access_Data
     {
          public class db
    {
         SqlConnection con = newSqlConnection(ConfigurationManager.ConnectionStrings["localhost"].ConnectionString);
    public void SendLocation(Location cs)
    {

        SqlCommand com = new SqlCommand("SendGPS", con);


        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.AddWithValue("@DeviceImei", cs.DeviceImei);
        com.Parameters.AddWithValue("@Latitude", cs.Latitude);
        com.Parameters.AddWithValue("@Longitude", cs.Longitude);
        com.Parameters.AddWithValue("@Distance", cs.Distance);
        com.Parameters.AddWithValue("@LocationSend", cs.LocationSend);
        con.Open();
        com.Connection = con;
        com.ExecuteNonQuery();
        con.Close();

    }

    public DataTable LocationHistory(LocationHistory cs)
    {
        DataTable dt = new DataTable();

        using (SqlCommand sqlCmd = new SqlCommand("GetLocationHistory", con))
        {
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.Parameters.Add(new SqlParameter("DeviceImei", cs.DeviceImei));

            try
            {
                con.Open();
                dt.Load(sqlCmd.ExecuteReader(CommandBehavior.CloseConnection));
            }
            catch (SqlException ex)
            {
                //Handle errors as you see fit
            }
        }

        return dt;
    }
    public DataSet GetUser()
    {
        SqlCommand com = new SqlCommand("GetUser", con);
        com.CommandType = CommandType.StoredProcedure;
        SqlDataAdapter da = new SqlDataAdapter(com);
        DataSet ds = new DataSet();
        da.Fill(ds);
        return ds;


    }
    public void SendDistance(Location cs)
    {

        SqlCommand com = new SqlCommand("SendDistance", con);


        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.AddWithValue("@DeviceImei", cs.DeviceImei);
        com.Parameters.AddWithValue("@Distance", cs.Distance);
        com.Parameters.AddWithValue("@LocationSend", cs.LocationSend);
        con.Open();
        com.Connection = con;
        com.ExecuteNonQuery();
        con.Close();
    }

    public DataTable FlagingDevice(FlagingDevice cs)
    {
        DataTable dt = new DataTable();

        using (SqlCommand sqlCmd = new SqlCommand("FlagingDevice", con))
        {
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.Parameters.Add(new SqlParameter("Login", cs.Login));

            try
            {
                con.Open();
                dt.Load(sqlCmd.ExecuteReader(CommandBehavior.CloseConnection));
            }
            catch (SqlException ex)
            {
                //Handle errors as you see fit
            }
        }

        return dt;
    }
    public DataTable SearchByOfficerName(SearchHistory cs)
    {
        DataTable dt = new DataTable();

        using (SqlCommand sqlCmd = new SqlCommand("SearchByOfficer", con))
        {
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.Parameters.Add(new SqlParameter("Name", cs.Name));
            sqlCmd.Parameters.Add(new SqlParameter("StationID", cs.StationID));
            sqlCmd.Parameters.Add(new SqlParameter("ID", cs.DivisionID));
            sqlCmd.Parameters.Add(new SqlParameter("DeviceName", cs.DeviceName));

            try
            {
                con.Open();
                dt.Load(sqlCmd.ExecuteReader(CommandBehavior.CloseConnection));
            }
            catch (SqlException ex)
            {
                //Handle errors as you see fit
            }
        }

        return dt;
    }
    public DataTable SearchByDeviceName(SearchHistory cs)
    {
        DataTable dt = new DataTable();

        using (SqlCommand sqlCmd = new SqlCommand("SearchByDeviceName", con))
        {
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.Parameters.Add(new SqlParameter("Name", cs.OfficerName));
            sqlCmd.Parameters.Add(new SqlParameter("StationID", cs.StationID));
            sqlCmd.Parameters.Add(new SqlParameter("DivisionID", cs.DivisionID));
            sqlCmd.Parameters.Add(new SqlParameter("DeviceName", cs.DeviceName));
            sqlCmd.Parameters.Add(new SqlParameter("BoxID", cs.BoxID));
            try
            {
                con.Open();
                dt.Load(sqlCmd.ExecuteReader(CommandBehavior.CloseConnection));
            }
            catch (SqlException ex)
            {
                //Handle errors as you see fit
            }
        }

        return dt;
    }
    public DataTable SearchByElectoralDivision(SearchHistory cs)
    {
        DataTable dt = new DataTable();

        using (SqlCommand sqlCmd = new SqlCommand("SearchByElectoralDivision", con))
        {
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.Parameters.Add(new SqlParameter("DivisionID", cs.DivisionID));
            sqlCmd.Parameters.Add(new SqlParameter("Name", cs.Name));
            sqlCmd.Parameters.Add(new SqlParameter("StationID", cs.StationID));
            sqlCmd.Parameters.Add(new SqlParameter("DeviceName", cs.DeviceName));

            try
            {
                con.Open();
                dt.Load(sqlCmd.ExecuteReader(CommandBehavior.CloseConnection));
            }
            catch (SqlException ex)
            {
                //Handle errors as you see fit
            }
        }

        return dt;
    }
    public DataTable SearchByPollingStation(SearchHistory cs)
    {
        DataTable dt = new DataTable();

        using (SqlCommand sqlCmd = new SqlCommand("SearchByPollingStation", con))
        {
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.Parameters.Add(new SqlParameter("StationID", cs.StationID));
            sqlCmd.Parameters.Add(new SqlParameter("OfficerName", cs.OfficerName));
            sqlCmd.Parameters.Add(new SqlParameter("DivisionID", cs.DivisionID));
            sqlCmd.Parameters.Add(new SqlParameter("DeviceName", cs.DeviceName));

            try
            {
                con.Open();
                dt.Load(sqlCmd.ExecuteReader(CommandBehavior.CloseConnection));
            }
            catch (SqlException ex)
            {
                //Handle errors as you see fit
            }
        }

        return dt;
    }
    public void SendBox(Box cs)
    {
        SqlCommand com = new SqlCommand("SendBox", con);


        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.AddWithValue("@Id", cs.Id);
        com.Parameters.AddWithValue("@StationID", cs.PollingStationID);
        com.Parameters.AddWithValue("@DeviceImei", cs.DeviceImei);
        con.Open();
        com.Connection = con;
        com.ExecuteNonQuery();
        con.Close();
    }

    public DataSet FlagingDeviceWithoutParameters()
    {
        SqlCommand com = new SqlCommand("FlagingDevice(Without Parameters)", con);
        com.CommandType = CommandType.StoredProcedure;
        SqlDataAdapter da = new SqlDataAdapter(com);
        DataSet ds = new DataSet();
        da.Fill(ds);
        return ds;
    }
    public DataTable SearchByBox(SearchHistory cs)
    {

        DataTable dt = new DataTable();

        using (SqlCommand sqlCmd = new SqlCommand("SearchByBox", con))
        {
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.Parameters.Add(new SqlParameter("DeviceName", cs.DeviceName));
            sqlCmd.Parameters.Add(new SqlParameter("StationID", cs.StationID));
            sqlCmd.Parameters.Add(new SqlParameter("ElectoralDivisionID", cs.ElectoralDivisionID));
            sqlCmd.Parameters.Add(new SqlParameter("OfficerName", cs.OfficerName));

            try
            {
                con.Open();
                dt.Load(sqlCmd.ExecuteReader(CommandBehavior.CloseConnection));
            }
            catch (SqlException ex)
            {
            }
            return dt;
        }

    }
}
  }

using Prototype3.Database_Access_Data;
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Linq;
   using System.Net;
   using System.Net.Http;
  using System.Threading.Tasks;
  using System.Web.Http;
  using System.Web.Http.Description;

  namespace Prototype3.Controllers
  {
     public class ValuesController : ApiController
    {
        Database_Access_Data.db dblayer = new Database_Access_Data.db();

    [HttpPost]
    [Route("api/Values/SendLocation")]
    public IHttpActionResult SendLocation([FromBody]Location cs)
    {
        try
        {
            if (!ModelState.IsValid)
            {
                return BadRequest(ModelState);
            }
            dblayer.SendLocation(cs);
            return Ok("Success");
        }
        catch (Exception e)
        {
            return Ok("Something went Wrong" + e);
        }

    }

    [HttpPost]
    [Route("api/Values/GetLocationHistory")]
   public IHttpActionResult GetLocationHistory([FromBody]LocationHistory cs)
    {
        try
        {
            if (!ModelState.IsValid)
            {
                return BadRequest(ModelState);
            }
            var table = dblayer.LocationHistory(cs);
            return Ok(table);
        }
        catch (Exception e)
        {
            return Ok("Something went Wrong" + e);
        }

    }
    [HttpPost]
    [Route("api/Values/SendDistance")]
    public IHttpActionResult SendDistance([FromBody]Location cs)
    {
        try
        {
            if (!ModelState.IsValid)
            {
                return BadRequest(ModelState);
            }
            dblayer.SendDistance(cs);
            return Ok("Success");
        }
        catch (Exception e)
        {
            return Ok("Something went Wrong" + e);
        }

    }

    [HttpGet]
    [Route("api/Values/GetUser")]
    public DataSet GetUser()
    {
        DataSet ds = dblayer.GetUser();
        return ds;
    }
    [HttpPost]
    [Route("api/Values/FlagingDevice")]
    public IHttpActionResult FlagingDevice([FromBody]FlagingDevice cs)
    {
        try
        {
            if (!ModelState.IsValid)
            {
                return BadRequest(ModelState);
            }
            var table = dblayer.FlagingDevice(cs);
            return Ok(table);

        }
        catch (Exception e)
        {
            return Ok("Something went Wrong" + e);
        }

    }

    [HttpPost]
    [Route("api/Values/SendBox")]
    public IHttpActionResult SendBox([FromBody]Box cs)
    {
        try
        {
            if (!ModelState.IsValid)
            {
                return BadRequest(ModelState);
            }
            dblayer.SendBox(cs);

            return Ok("Success");
        }
        catch (Exception e)
        {
            return Ok("Something went Wrong" + e);
        }

    }

    [HttpGet]
    [Route("api/Values/FlagingDevice(WithoutParameters)")]
    public DataSet FlagingDevice1()
    {
        DataSet ds = dblayer.FlagingDeviceWithoutParameters();
        return ds;
    }







}
  }