根据参数从数据库中检索数据 [英] Retrieving the data from the database based on the parameter

查看：71 发布时间：2019/6/7 15:36:26 ASP.NET SQL-Server

本文介绍了根据参数从数据库中检索数据的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

`ValuesController.cs`（发送GET，POST，DELETE等HTTP请求）：

  public   class  ValuesController：ApiController 
 {
 Database_Access_Data.db dblayer =  new  Database_Access_Data.db（）; 
 
 [HttpPost] 
 [路线（  api / Values / SendLocation）] 
  public  IHttpActionResult SendLocation（[FromBody] Location cs）
 {
 尝试 
 {
  if （！ModelState.IsValid）
 {
  return  BadRequest（ModelState）; 
} 
 
 dblayer.SendLocation（cs）; 
 
 返回确定（ 成功）; 
} 
  catch （例外e）
 {
  return  Ok（ 出错了 + e）; 
} 
} 
 
 [HttpGet] 
 [路线（  api / Values / GetLocationHistory）] 
  public  DataSet GetLocationHistory（）
 {
 DataSet ds = dblayer.GetLocationHistory（）; 
  return  ds; 
} 
 
 [HttpPost] 
 [路线（  api /值/发送距离）] 
  public  IHttpActionResult SendDistance（[FromBody] Location cs）
 {
 尝试 
 {
  if （！ModelState.IsValid）
 {
  return  BadRequest（ModelState）; 
} 
 
 dblayer.SendDistance（cs）; 
 
 返回确定（ 成功）; 
} 
  catch （例外e）
 {
  return  Ok（ 出错了 + e）; 
} 
} 
 
 [HttpGet] 
 [路线（  api / Values / GetUser）] 
  public  DataSet GetUser（）
 {
 DataSet ds = dblayer.GetUser（）; 
  return  ds; 
} 
 
 [HttpPost] 
 [路线（  api /值/ FlagingDevice）] 
  public  IHttpActionResult FlagingDevice（[FromBody] Timer cs）
 {
 尝试 
 {
  if （！ModelState.IsValid）
 {
  return  BadRequest（ModelState）; 
} 
 
 dblayer.FlagingDevice（cs）; 
 
 返回确定（ 成功）; 
} 
  catch （例外e）
 {
  return  Ok（ 出错了 + e）; 
} 
} 
 
 [HttpPost] 
 [路线（  api / Values / SendBox）] 
  public  IHttpActionResult SendBox（[FromBody] Box cs）
 {
 尝试 
 {
  if （！ModelState.IsValid）
 {
  return  BadRequest（ModelState）; 
} 
 
 dblayer.SendBox（cs）; 
 
 返回确定（ 成功）; 
} 
  catch （例外e）
 {
  return  Ok（ 出错了 + e）; 
} 
} 
}

`db.cs`（用于调用存储过程以及发送参数

  public   class  db 
 {
 SqlConnection con =  new  SqlConnection（ConfigurationManager.ConnectionStrings [  localhost]。ConnectionString）; 
 
位置cs = 新位置（）; 
 
  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（）; 
} 
}

用于从表中返回数据的存储过程：

  SET   ANSI_NULLS   ON  
  GO  
  SET   QUOTED_IDENTIFIER   ON  
  GO  
 
  ALTER   PROCEDURE  [dbo]。[FlagingDevice] 
  @ Interval   INT  
  AS  
  DECLARE   @ Time   DATETIME  
 
  IF   @ Interval  =  5  
  BEGIN  
 < span class =code-keyword> SET   @ Time  =（ SELECT  MAX（LocationSend ） FROM  dbo.Location）
 
  SELECT  D.imei，L。* ，L1。* 
  FROM 设备D 
  OUTER   APPLY  
（ SELECT  * 
  FROM  dbo .Location L1 
  WHERE  L1.DeviceImei = D.Imei 
  GROUP   BY  DeviceImei，纬度，经度，距离，位置onSend 
  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.Longitud e  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 设备D 
  OUTER   APPLY  
 （ SELECT   TOP   1  * 
  FROM  dbo.Location L1 
  WHERE  L1.DeviceImei = D.Imei 
  ORDER   BY （LocationSend） DESC ）< span class =code-keyword> 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

**更新**：我尝试使用SQL查询使用参数执行存储过程，它确实可以返回来自表

我尝试过：

**更新* *：我正在使用sqldatareader并将数据放入列表但不确定如何返回如valuescontroller类中所示的FlaggingDevice方法。我应该在类或其他内容中创建列表。任何建议？

公开列表< 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）， 
距离= 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（）; 
返回学生; 
 
 
} 
 
 
 
 
}

解决方案

你好;

我会查询数据库并在DataTable中加载结果，以后再处理转换为将 FlagingDevice 对象传递给UI之前的列表;看看这个：
 public DataTable FlagingDevice（FlagingDevice cs）
 {
 DataTable dt = new DataTable（ ）; 
 
使用（SqlConnection rootConn = con）
使用（SqlCommand sqlCmd = new SqlCommand（FlagingDevice，rootConn））
 {
 sqlCmd.CommandType = CommandType.StoredProcedure ; 
 sqlCmd.Parameters.Add（new SqlParameter（@ Interval，cs.Interval））; 
 
尝试
 {
 rootConn.Open（）; 
 dt.Load（sqlCmd.ExecuteReader（CommandBehavior.CloseConnection））; 
} 
 catch（SqlException ex）
 {
 //处理你认为合适的错误
} 
} 
 
返回DT; //输出
} 
这将创建，填充和返回一个DataTable，然后你可以迭代建立你的列表< FlagingDevice > ;.

将DataRows转换为 FlagingDevice 的方法不应该给你带来任何麻烦。

希望这会有所帮助。

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

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

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:

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;


    }




}

解决方案

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.

这篇关于根据参数从数据库中检索数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文

根据参数从数据库中检索数据 [英] Retrieving the data from the database based on the parameter

问题描述

相关文章

其他开发语言最新文章

热门教程

热门工具

登录关闭

根据参数从数据库中检索数据 [英] Retrieving the data from the database based on the parameter

问题描述

相关文章

其他开发语言最新文章

热门教程

热门工具

登录 关闭

登录关闭