计算结余数，API与文件操作
- 七月 21, 2019
- 澳门新蒲京娱乐
- 没有评论
有二个网上好友问及，在SQL中，计算每一笔的剩下数。他提供的截图表明：
近来，一贯有演练ASP.NET MVC与Web
API交互，接下去，Insus.NET再做一些相关的演练，Web
API与公事操作，如POST文件至Web API，更新或是删除等。
不管怎么着，先在数据库创制一张表，用来储存上传的公文。本实例中是把文件存款和储蓄过数据库的。
福寿无疆那几个效果，关键是获得前一笔记录eqty字段的值。
CREATE TABLE ApiFileDemo
(
[Afd_nbr] INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
[Picture] [image] NULL,
[PictureType] [nvarchar](30) NULL,
[FileExtension] [nvarchar](10) NULL
)
GO
CREATE PROCEDURE [dbo].[usp_ApiFileDemo_Insert]
(
@Picture IMAGE,
@PictureType NVARCHAR(30),
@FileExtension NVARCHAR(10)
)
AS
INSERT INTO [dbo].[ApiFileDemo] ([Picture],[PictureType],[FileExtension]) VALUES (@Picture,@PictureType,@FileExtension)
GO
CREATE PROCEDURE [dbo].[usp_ApiFileDemo_Update]
(
@Afd_nbr INT,
@Picture IMAGE,
@PictureType NVARCHAR(30),
@FileExtension NVARCHAR(10)
)
AS
UPDATE [dbo].[ApiFileDemo] SET [Picture] = @Picture,[PictureType] = @PictureType,[FileExtension] = @FileExtension WHERE [Afd_nbr] = @Afd_nbr
GO
CREATE PROCEDURE [dbo].[usp_ApiFileDemo_Delte]
(
@Afd_nbr INT
)
AS
DELETE FROM [dbo].[ApiFileDemo] WHERE [Afd_nbr] = @Afd_nbr
GO
上边Insus.NET尝试写一下。使用最简便的方法，正是循环每一笔记录。然后能够计算qty加上前一笔的eqty。
Source Code
始建一个不经常表存款和储蓄原数：
写到这里，开掘少了叁个积存进程，正是赢得某一张图纸的：
CREATE TABLE #tt
(
[empid] char(3),
[fdate] date,
[qty] int
)
INSERT INTO #tt([empid],[fdate],[qty]) VALUES('100','2018-01-01',100)
INSERT INTO #tt([empid],[fdate],[qty]) VALUES('100','2018-01-01',100)
INSERT INTO #tt([empid],[fdate],[qty]) VALUES('100','2018-01-01',120)
INSERT INTO #tt([empid],[fdate],[qty]) VALUES('100','2018-01-01',145)
INSERT INTO #tt([empid],[fdate],[qty]) VALUES('100','2018-01-02',30)
INSERT INTO #tt([empid],[fdate],[qty]) VALUES('100','2018-01-02',150)
INSERT INTO #tt([empid],[fdate],[qty]) VALUES('100','2018-01-02',160)
INSERT INTO #tt([empid],[fdate],[qty]) VALUES('100','2018-01-02',170)
INSERT INTO #tt([empid],[fdate],[qty]) VALUES('100','2018-01-02',121)
INSERT INTO #tt([empid],[fdate],[qty]) VALUES('100','2018-01-02',106)
CREATE PROCEDURE [dbo].[usp_ApiFileDemo_GetByPrimarykey]
(
@Afd_nbr INT
)
AS
SELECT [Afd_nbr],[Picture],[PictureType],[FileExtension] FROM [dbo].[ApiFileDemo] WHERE [Afd_nbr] = @Afd_nbr
GO
Source Code
Source Code
接下去，大家得以设计Web
API接口，待完结了，宣布至互连网，其余客户端就能够操作了。
小编们初阶拍卖，创造其余贰个有的时候表，原始表相似，然则须要增添2个字段，id和eqty。个中id是当然增加的identity(1,1)。
依照数量库表，可以在API项目中，创制Model:
还必要把原有数据搬到此新建的有的时候表中：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace Insus.NET.Models
{
public class File
{
public int Afd_nbr { get; set; }
public byte[] Picture { get; set; }
public string PictureType { get; set; }
public string FileExtension { get; set; }
}
}
Source Code
CREATE TABLE #ttt
(
[id] int identity(1,1),
[empid] char(3),
[fdate] date,
[qty] int,
[eqty] int
)
INSERT INTO #ttt([empid],[fdate],[qty]) SELECT [empid],[fdate],[qty] FROM #tt
写好model之后，还索要为API写三个实体，那么些指标只是让程序与数据库举办相互。获取与积存等操作：
Source Code
using Insus.NET.DataBases;
using Insus.NET.Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Insus.NET;
namespace Insus.NET.Entities
{
public class FileEntity
{
BizSP sp = new BizSP();
public DataTable GetFileByPrimarykey(File f)
{
List<Parameter> param = new List<Parameter>() {
new Parameter("@Afd_nbr", SqlDbType.Int,4,f.Afd_nbr)
};
sp.ConnectionString = DB.ConnectionString;
sp.Parameters = param;
sp.ProcedureName = "usp_ApiFileDemo_GetByPrimarykey";
return sp.ExecuteDataSet().Tables[0];
}
public void Insert(File f)
{
List<Parameter> param = new List<Parameter>() {
new Parameter("@Picture", SqlDbType.Image,-1,f.Picture),
new Parameter("@PictureType",SqlDbType.NVarChar,-1,f.PictureType),
new Parameter("@FileExtension",SqlDbType.NVarChar,-1,f.FileExtension)
};
sp.ConnectionString = DB.ConnectionString;
sp.Parameters = param;
sp.ProcedureName = "usp_ApiFileDemo_Insert";
sp.Execute();
}
public void Update(File f)
{
List<Parameter> param = new List<Parameter>() {
new Parameter("@Afd_nbr", SqlDbType.Int,4,f.Afd_nbr),
new Parameter("@Picture", SqlDbType.Image,-1,f.Picture),
new Parameter("@PictureType",SqlDbType.NVarChar,-1,f.PictureType),
new Parameter("@FileExtension",SqlDbType.NVarChar,-1,f.FileExtension)
};
sp.ConnectionString = DB.ConnectionString;
sp.Parameters = param;
sp.ProcedureName = "usp_ApiFileDemo_Update";
sp.Execute();
}
public void Delete(File f)
{
List<Parameter> param = new List<Parameter>() {
new Parameter("@Afd_nbr", SqlDbType.Int,4,f.Afd_nbr)
};
sp.ConnectionString = DB.ConnectionString;
sp.Parameters = param;
sp.ProcedureName = "usp_ApiFileDemo_Delte";
sp.Execute();
}
}
}
接下去，Insus.NET写了八个积攒进度，方法如下：
Source Code
上面包车型客车主宰器FileController，即是为客户端采访的接口，这么些类型，它是后续了ApiController。
DECLARE @r int = 1,@rs INT = 0
SELECT @rs = MAX([empid]) FROM #ttt
WHILE @r <= @rs
BEGIN
IF (@r = 1 )
UPDATE #ttt SET [eqty] = [qty] WHERE [id] = @r --处理第一笔记录
ELSE
BEGIN
DECLARE @eqty INT
SELECT @eqty = [eqty] FROM #ttt WHERE [id] = @r - 1 --获取前一笔的结余数
UPDATE #ttt SET [eqty] = [qty] + @eqty WHERE [id] = @r --计算后更新当前记录的eqty字段值。
END
SET @r = @r+ 1
END
GO
Source Code
using Insus.NET.Entities;
using Insus.NET.ExtendMethods;
using Insus.NET.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web.Http;
namespace Insus.NET.Controllers
{
public class FileController : ApiController
{
// GET: File
FileEntity fe = new FileEntity();
// GET: ApiFileDemo
[HttpGet]
public string Get(int id)
{
File f = new File();
f.Afd_nbr = id;
return fe.GetFileByPrimarykey(f).ToJson();
}
[HttpPost]
public void Post(File f)
{
fe.Insert(f);
}
[HttpPut]
public void Put(File f)
{
fe.Update(f);
}
[HttpDelete]
public void Delete(File f)
{
fe.Delete(f);
}
[HttpDelete]
public void Delete(int id)
{
File f = new File();
f.Afd_nbr = id;
fe.Delete(f);
}
}
}
管理结果：
Source Code
Web
API完结，大家要求把它揭发至IIS中去，怎么样发表，可以参照《创制与应用Web
API》……
Ok，接下去，大家开拓客户端的顺序，尝试上Web API上传一些文书。
No Comments, Be The First!