Retrieving the data from the database based on the parameter

Question

0.00/5 (No votes)

See more:

`ValuesController.cs` (sending the HTTP request such as GET, POST, DELETE and etc):

C#

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);
            }
        }

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

        [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]Timer cs)
        {
            try
            {
                if (!ModelState.IsValid)
                {
                    return BadRequest(ModelState);
                }

                dblayer.FlagingDevice(cs);

                return Ok("Success");
            }
            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);
            }
        }
    }

`db.cs` (used to call the stored procedure as well as sending the parameter

C#

public class db
    {
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["localhost"].ConnectionString);

        Location cs = new Location();

        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 DataSet GetLocationHistory()
        {
            SqlCommand com = new SqlCommand("GetLocationHistory", con);
            com.CommandType = CommandType.StoredProcedure;

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

            return ds;
        }

        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 void FlagingDevice(Timer cs)
        {
            SqlCommand com = new SqlCommand("FlagingDevice", con);
            com.CommandType = CommandType.StoredProcedure;
            com.Parameters.AddWithValue("@Interval", cs.Interval);

            con.Open();
            com.Connection = con;
            com.ExecuteNonQuery();
            con.Close();
        }

        public void SendBox(Box cs)
        {
            SqlCommand com = new SqlCommand("SendBox", con);
            com.CommandType = CommandType.StoredProcedure;

            com.Parameters.AddWithValue("@Id", cs.Id);
            com.Parameters.AddWithValue("@PollingStationID", cs.PollingStationID);
            com.Parameters.AddWithValue("@DeviceImei", cs.DeviceImei);

            con.Open();
            com.Connection = con;
            com.ExecuteNonQuery();
            con.Close();
        }
    }

The stored procedure that used to return the data from the table:

SQL

SET ANSI_NULLS ON
         GO
         SET QUOTED_IDENTIFIER ON
         GO

         ALTER PROCEDURE [dbo].[FlagingDevice]
             @Interval INT 
         AS 
             DECLARE @Time DATETIME

             IF @Interval = 5
             BEGIN 
                 SET @Time = (SELECT MAX(LocationSend) FROM dbo.Location)

                 SELECT D.imei, L.*, L1.*
                 FROM Device D
                 OUTER APPLY
                     (SELECT  * 
                      FROM dbo.Location L1
                      WHERE L1.DeviceImei = D.Imei 
                      GROUP BY DeviceImei, Latitude, Longitude, Distance, LocationSend
                      HAVING DATEDIFF(MINUTE, LocationSend, @Time) <= @Interval) AS L
                 OUTER APPLY
                     (SELECT TOP 1 L4.ID AS 'Station', L3.Name
                      FROM [dbo].[ElectionDivision] L3, dbo.PollingStation L4
                      WHERE L.Latitude IS NOT NULL 
                        AND L.Longitude IS NOT NULL 
                        AND L.Distance IS NOT NULL 
                        AND L.DeviceImei = D.ImeI) AS L1
              END
              ELSE IF @Interval = 0
              BEGIN 
                  SELECT D.imei, L.*, L1.*
                  FROM Device D
                  OUTER APPLY
                      (SELECT TOP 1 * 
                       FROM dbo.Location L1
                       WHERE L1.DeviceImei = D.Imei 
                       ORDER BY (LocationSend) DESC) AS L
                  OUTER APPLY
                      (SELECT TOP 1 L4.ID AS 'Station', L3.Name
                       FROM [dbo].[ElectionDivision] L3, dbo.PollingStation L4
                       WHERE L.Latitude IS NOT NULL 
                         AND L.Longitude IS NOT NULL 
                         AND L.Distance IS NOT NULL 
                         AND L.DeviceImei = D.ImeI) AS L1
          END

**Update**: I have tried to execute the stored procedure with the parameter using the SQL query and it works it did return the rows from the table

What I have tried:

**Update**: I am using sqldatareader and put the data into a list but not sure how to return the FlaggingDevice Method as shown in the valuescontroller class. Should i be creating the list inside a class or something else. Any Suggestions ?

public List<FlagingDevice> FlagingDevice(FlagingDevice cs)
{

    SqlCommand com = new SqlCommand("FlagingDevice", con);
    com.CommandType = CommandType.StoredProcedure;
    com.Parameters.AddWithValue("@Interval", cs.Interval);
    con.Open();
    com.Connection = con;
    using (SqlDataReader sqlDataReader = com.ExecuteReader())
    {

        int movieGenreIDIndex = sqlDataReader.GetOrdinal("DeviceImei");
        int movieIDIndex = sqlDataReader.GetOrdinal("Latitude");
        int genreIDIndex = sqlDataReader.GetOrdinal("Longitude");
        int genreIDIndex1 = sqlDataReader.GetOrdinal("Distance");
        int genreIDIndex2 = sqlDataReader.GetOrdinal("LocationSend");
        int genreIDIndex3 = sqlDataReader.GetOrdinal("Station");
        int genreIDIndex4 = sqlDataReader.GetOrdinal("Name");
        while (sqlDataReader.Read())
        {
            student.Add(new FlagingDevice()
            {
                DeviceImei = sqlDataReader.IsDBNull(movieGenreIDIndex) ? null : sqlDataReader.GetString(movieGenreIDIndex),
                Latitude = sqlDataReader.IsDBNull(movieIDIndex) ? null : sqlDataReader.GetString(movieIDIndex),
                Longitude = sqlDataReader.IsDBNull(genreIDIndex) ? null : sqlDataReader.GetString(genreIDIndex),
                Distance = sqlDataReader.IsDBNull(genreIDIndex1) ? null : sqlDataReader.GetString(genreIDIndex1),
                LocationSend = sqlDataReader.IsDBNull(genreIDIndex2) ? null : Convert.ToString(sqlDataReader["LocationSend"]),
                Name = sqlDataReader.IsDBNull(genreIDIndex4) ? null : sqlDataReader.GetString(genreIDIndex4)



            });






        }
        sqlDataReader.Close();
        con.Close();
        return student;


    }




}

Posted 17-Oct-18 21:02pm

lee loong

Updated 19-Oct-18 4:01am

v5

Add a Solution

Comments

Atlapure Ambrish 18-Oct-18 3:31am

What error you are seeing?

lee loong 18-Oct-18 3:39am

The error that i am seeing is that it return success instead i want it to return the data from the stored procedure

Atlapure Ambrish 18-Oct-18 3:44am

I would suggest you to capture the stored procedure call in 'SQL profiler' and try to run the same from SQL management studio to check if it is returning intended result.

lee loong 18-Oct-18 3:48am

Tried: The stored procedure is working at intended. The only problem is the displaying of data through asp.net

Atlapure Ambrish 18-Oct-18 4:01am

and what about the api get method? are you seeing the data in dataset which you are returning from the method??

On another note, web api should always return a standard format data e.g. JSON, XML. so that it is not restricted to specific applications. So, I would return JSON from GetLocationHistory and GetUser methods.

lee loong 18-Oct-18 4:19am

In my Stored Procedure, i have a parameter which take in the interval and display the rows based on the interval. I have tried using dataset but still no avail. i have ask some people on stackoverflow they tell me to use sqldatareader to retrieve the data and i am able to retrieve but not sure how to display it to the controller

Atlapure Ambrish 18-Oct-18 4:26am

one alternate could be to loop over the datareader and covert the result to a collection of some class and then return to the controller.

F-ES Sitecore 18-Oct-18 4:43am

You're using ExecuteNonQuery which means run the SQL and ignore the results. If you what to read the results you'll need something like ExecuteReader instead. Second issue is that you'll probably need to add

SET NOCOUNT ON

to the top of the SP and statements like "SET @Time = " generate 0 row results as well.

Atlapure Ambrish 18-Oct-18 4:47am

If i have not overlooked the code, he is using dataset in db.cs - GetLocationHistory() method and ExecuteNonQuery is used for other save methods.

F-ES Sitecore 18-Oct-18 4:54am

The SP he posted he is calling using ExecuteNonQuery.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

alexvw · Accepted Answer · 2018-10-19T04:01:00

Hi there;

I would query the DB and load results in a DataTable, to later process to transform into a list of FlagingDevice objects before passing it to the UI; take a look at this:

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

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

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

                return dt; //output
            }

this will create, populate, and return a DataTable, which then you can iterate to build you list<FlagingDevice>.

The method to transform your DataRows into FlagingDevice should not pose any trouble for you.

Hope this helps.