计算结余数

有一个网友问及,在SQL中,计算每一笔的结余数。他提供的截图说明:
图片 1

前段时间,一直有练习ASP.NET MVC与Web
API交互,接下来,Insus.NET再做一些相关的练习,Web
API与文件操作,如POST文件至Web API,更新或是删除等。

 

不管怎样,先在数据库创建一张表,用来存储上传的文件。本实例中是把文件存储过数据库的。

图片 2

图片 3

 

图片 4图片 5

实现这个功能,关键是获取前一笔记录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

创建一个临时表存储原数:
图片 6

写到这里,发现少了一个存储过程,就是获取某一张图片的:
图片 7

图片 4图片 9

图片 4图片 11

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:
图片 12

还需要把原始数据搬到此新建的临时表中:
图片 13

图片 4图片 15

 

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

图片 4图片 17

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写一个实体,这个对象只是让程序与数据库进行交互。获取与存储等操作:
图片 18

Source Code

图片 4图片 20

 

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写了一个存储过程,方法如下:
图片 21

Source Code

 

 

图片 4图片 23

下面的控制器FileController,即是为客户端访问的接口,这个类别,它是继承了ApiController。
图片 24

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

图片 4图片 26

 

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

处理结果:
图片 27

Source Code

 

 
Web
API完成,我们需要把它发布至IIS中去,如何发布,可以参考《创建与使用Web
API
http://www.cnblogs.com/insus/p/5019088.html……

Ok,接下来,我们开发客户端的程序,尝试上Web API上传一些文件。

在客户端的项目中,创建一个mode:
图片 28

图片 4图片 30

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

 

ASP.NET MVC的控制器中,创建2个Action:
图片 31

 

图片 4图片 33

 public ActionResult Upload()
        {
            return View();
        }

        [HttpPost]
        public ActionResult Upload(IEnumerable<HttpPostedFileBase> files)
        {
            foreach (var file in files)
            {
                if (file.ContentLength > 0)
                {
                    Insus.NET.Models.File f = new Insus.NET.Models.File();
                    f.PictureType = file.ContentType;
                    string fn = Path.GetFileName(file.FileName);
                    f.FileExtension = fn.Substring(fn.LastIndexOf('.'));
                    using (Stream inputStream = file.InputStream)
                    {
                        MemoryStream memoryStream = inputStream as MemoryStream;
                        if (memoryStream == null)
                        {
                            memoryStream = new MemoryStream();
                            inputStream.CopyTo(memoryStream);
                        }
                        f.Picture = memoryStream.ToArray();
                    }
                    HttpClient client = new HttpClient();
                    string ff = f.ToJson();
                    HttpContent httpcontent = new StringContent(ff, System.Text.Encoding.UTF8, "application/json");
                    client.PostAsync("http://localhost:9001/api/file", httpcontent)
                        .ContinueWith((postTask) =>
                        {
                            postTask.Result.EnsureSuccessStatusCode();
                        });
                }
            }
            return RedirectToAction("Upload");
        }

Source Code

 

在视图中,可以这样做:
图片 34

 

程序运行:
图片 35

 

 图片上传成功之后,现在我们需要把图片显示出来。
由于存储的是二进制的数据流,显示图片时,需要处理一下,需要写一个自定义的Result,如:PictureResult,它需要继承ContentResult:
图片 36

图片 4图片 38

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Web;
using System.Web.Mvc;

namespace Insus.NET.Results
{
    public class PictureResult : ContentResult
    {
        public byte[] _Picture { get; set; }
        public string _PictureType { get; set; }

        public PictureResult(byte[] sourceStream, String contentType)
        {
            _Picture = sourceStream;
            _PictureType = contentType;
        }

        public override void ExecuteResult(ControllerContext context)
        {
            var response = context.HttpContext.Response;
            response.Clear();
            response.Cache.SetCacheability(HttpCacheability.NoCache);
            response.ContentType = ContentType;

            if (_Picture != null)
            {
                var stream = new MemoryStream(_Picture);
                stream.WriteTo(response.OutputStream);
                stream.Dispose();
            }
        }
    }
}

Source Code

接下来,我们在控制器创建视图的Action:
图片 39

 

图片 4图片 41

 public ActionResult ShowPhoto()
        {
            return View();
        }

        public ActionResult ShowPicture(int id)
        {
            var files = ApiUtility.Get<Insus.NET.Models.File>("http://localhost:9001/api/file/" + id);
            var model = files.FirstOrDefault();

            PictureResult pictureResult = new PictureResult(model.Picture, model.PictureType);
            return pictureResult;
        }

Source Code

 
客户端程序运行,可以看到图片显示的效果:
图片 42

在Web
API的接口还有更新,删除的接口,看官们可以继续完成。方法在Insus.NET博客上也有相似或是相关的功能……

 

发表评论

电子邮件地址不会被公开。 必填项已用*标注