API与文件操作,计算结余数

有3个网络好友问及,在SQL中,总计每一笔的剩下数。他提供的截图注明:
必发365 1

前段时间,向来有演习ASP.NET MVC与Web
API交互,接下去,Insus.NET再做壹些生死相依的勤学苦练,Web
API与公事操作,如POST文件至Web API,更新或是删除等。

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

 

不管如何,先在数据库创设一张表,用来囤积上传的文件。本实例中是把文件存款和储蓄过数据库的。

不顾,先在数据库创设一张表,用来储存上传的文书。本实例中是把公文存款和储蓄过数据库的。

必发365 2

必发365 3

必发365 3

 

必发365 5必发365 6

必发365 7必发365 8

达成这些功用,关键是收获前一笔记录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
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

Source Code

开创3个临时表存储原数:
必发365 9

写到那里,发现少了三个储存进程,正是获取某一张图片的:
必发365 10

写到那里,发现少了五个储存进度,正是得到某一张图片的:
必发365 10

必发365 12必发365 13

必发365 14必发365 15

必发365 16必发365 17

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

Source Code

 

 
接下去,大家得以布署Web
API接口,待达成了,公布至网上,别的客户端就足以操作了。

 
接下去,大家能够安排Web
API接口,待实现了,宣布至网上,其余客户端就能够操作了。

大家开始拍卖,创设别的3个权且表,原始表相似,可是供给添加三个字段,id和eqty。当中id是理所当然增加的identity(1,1)。

基于数据库表,能够在API项目中,创造Model:
必发365 18

根据数量库表,能够在API项目中,创造Model:
必发365 19

还亟需把原来数据搬到此新建的一时表中:
必发365 20

必发365 21必发365 22

必发365 23必发365 24

 

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

必发365 25必发365 26

Source Code

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写一个实体,这几个目的只是让程序与数据库进行互动。获取与存款和储蓄等操作:
必发365 27

写好model之后,还需求为API写3个实体,那一个指标只是让程序与数据库进行交互。获取与存款和储蓄等操作:
必发365 28

Source Code

必发365 29必发365 30

必发365 31必发365 32

 

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();
        }
    }
}
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写了三个存款和储蓄进程,方法如下:
必发365 33

Source Code

Source Code

 

 

 

必发365 34必发365 35

下边包车型客车控制器FileController,便是为客户端访问的接口,这一个种类,它是继承了ApiController。
必发365 36

上边包车型地铁主宰器FileController,正是为客户端访问的接口,这些类型,它是接二连三了ApiController。
必发365 37

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

必发365 38必发365 39

必发365 40必发365 41

 

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

处理结果:
必发365 42

Source Code

Source Code

 

 
Web
API完结,大家必要把它公布至IIS中去,如何发表,能够参见《创造与使用Web
API》……

 
Web
API完成,我们须求把它揭露至IIS中去,怎么样公布,能够参见《始建与应用Web
API》……

Ok,接下去,我们付出客户端的主次,尝试上Web API上传一些文本。

Ok,接下去,大家付出客户端的顺序,尝试上Web API上传一些文件。

在客户端的档次中,创立3个mode:
必发365 43

在客户端的品类中,创建八个mode:
必发365 44

必发365 45必发365 46

必发365 47必发365 48

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

Source Code

 

 

ASP.NET MVC的控制器中,成立一个Action:
必发365 49

ASP.NET MVC的控制器中,创建一个Action:
必发365 50

 

 

必发365 51必发365 52

必发365 53必发365 54

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

Source Code

 

 

在视图中,能够这么做:
必发365 55

在视图中,能够那样做:
必发365 56

 

 

程序运维:
必发365 57

程序运转:
必发365 58

 

 

 图片上传成功之后,今后我们供给把图片展现出来。
是因为存款和储蓄的是二进制的数据流,呈现图片时,须求处理一下,要求写三个自定义的Result,如:PictureResult,它须要一而再ContentResult:
必发365 59

 图片上传成功以往,今后大家须要把图纸呈现出来。
由于存款和储蓄的是贰进制的数据流,呈现图片时,要求处理一下,供给写三个自定义的Result,如:PictureResult,它必要后续ContentResult:
必发365 60

必发365 61必发365 62

必发365 63必发365 64

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

Source Code

接下去,大家在控制器成立视图的Action:
必发365 65

必发365,接下去,大家在控制器创造视图的Action:
必发365 66

 

 

必发365 67必发365 68

必发365 69必发365 70

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

Source Code

 
客户端程序运营,能够见到图片体现的功力:
必发365 71

 
客户端程序运维,能够看到图片突显的功效:
必发365 72

在Web
API的接口还有革新,删除的接口,看官们可以持续完结。方法在Insus.NET博客上也有1般大概相关的机能……

在Web
API的接口还有立异,删除的接口,看官们方可持续完毕。方法在Insus.NET博客上也有相似或然相关的效率……

 

 

相关文章

发表评论

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

*
*
Website