sql-server - 根据参数从数据库中检索数据
问题描述
ValuesController.cs
(发送 HTTP 请求,如 GET、POST、DELETE 等):
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
(用于调用存储过程以及发送参数):
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();
}
}
用于从表中返回数据的存储过程:
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
更新:我正在使用 sqldatareader 并将数据放入列表中,但不确定如何返回 FlaggingDevice 方法,如 valuescontroller 类中所示。我应该在类或其他东西中创建列表。有什么建议么 ?
公共列表<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; 使用 (SqlDataReader sqlDataReader = com.ExecuteReader()) { int movieGenreIDIndex = sqlDataReader.GetOrdinal("DeviceImei"); int movieIDIndex = sqlDataReader.GetOrdinal("纬度"); intgenreIDIndex = sqlDataReader.GetOrdinal("经度"); int GenreIDIndex1 = sqlDataReader.GetOrdinal("距离"); int GenreIDIndex2 = sqlDataReader.GetOrdinal("LocationSend"); int GenreIDIndex3 = sqlDataReader.GetOrdinal("Station"); int GenreIDIndex4 = sqlDataReader.GetOrdinal("Name"); 而(sqlDataReader.Read()) { student.Add(新的 FlagingDevice() { DeviceImei = sqlDataReader.IsDBNull(movieGenreIDIndex) ?null : sqlDataReader.GetString(movieGenreIDIndex), 纬度 = sqlDataReader.IsDBNull(movieIDIndex) ?null : sqlDataReader.GetString(movieIDIndex), 经度 = sqlDataReader.IsDBNull(genreIDIndex) ?null : sqlDataReader.GetString(genreIDIndex), 距离 = sqlDataReader.IsDBNull(genreIDIndex1) ?null : sqlDataReader.GetString(genreIDIndex1), LocationSend = sqlDataReader.IsDBNull(genreIDIndex2) ?null : Convert.ToString(sqlDataReader["LocationSend"]), 名称 = sqlDataReader.IsDBNull(genreIDIndex4) ?空:sqlDataReader.GetString(genreIDIndex4) }); } sqlDataReader.Close(); con.Close(); 返校生; } }
解决方案
推荐阅读
- c# - 如何将 DataGridTextColumn 样式移动到 Window.Resources
- php - 如何获取xml文件的所有id属性
- javascript - 为什么Javascript事件处理程序被调用两次?
- python - 如何在 for 循环中将元素添加到列表并打印单个列表?
- unit-testing - 使用 Effort 的假 DbConnection 有问题
- python - 在python中为日志添加请求参数
- mysql - 如何规范具有多个用于特定表的表?
- c# - 防止剃刀视图mvc中的跨脚本攻击
- windows - Magento 2.3.6-p1 安装不完整并在安装时在 Magento_Theme 中显示错误
- python - 如何生成每组中具有不同条数的组条形图?