全栈开发学习记录：一个简单的node.js服务器以及用到的表、视图、存储过程和配套测试的前端.

从认知全栈开发到底是什么后,又学习了很久,最终写出了这么一个简单的node.js服务器以及配套前端测试,由于学习的还不够深入,路由那块与fetch那块做的不好,只能勉强跑起来,希望在学习深入后重新完善,特此记录.

此node.js服务器实现如下功能:

1 用express快速建立服务器

2 在node.js中连接sql server

3 增(POST)删(DELETE)改(PUT)查(GET)例子,增删改查时皆调用sql server存储过程

以下为服务器代码:需要用npm安装需要的express等包

//引用

const cors = require("cors"); //引入cors,解决跨域

const fs = require("fs"); //引入fs,解决读取监听端口

const express = require("express"); //引入express

const server = express(); //server变量

const mssql = require("mssql"); //引入sql server

//

//中间件

server.use(express.json()); //用于post的json解析

server.use(cors()); //用于跨域

server.use(express.urlencoded({ extended: true }));

//连接sql server的参数配置

const config = {

  user: "sa", //定义连接SQL SERver的连接字符串

  password: "04768350806",

  server: "localhost",

  database: "grain_bss2023",

  port: 1433,

  options: {

    encrypt: false,

  },

  pool: {

    min: 0,

    max: 10,

    idleTimeoutMillis: 30000,

  },

};

//连接sql server

mssql.connect(config, function (err) {

  if (err) {

    console.log("数据库连接建立失败");

    return;

  } else {

    console.log("数据库连接建立成功");

  }

});

//post路由(生成新id)

server.post("/api/buyin/guard/makenewid", (req, res) => {

  const strday = req.body.strday;

  const request = new mssql.Request(); //新建请求

  request.input("strtoday", mssql.VarChar, strday);

//配置参数的数据类型,熟练后strday不需要定义可直接用req.body.strday,则const strday可去掉

//执行生成新id的存储过程,送入20221212,返回202212120001 202212120002 202212120003... 

request.execute("prBuyInMakeNewID", (err, recordsets, returnvalue) => {

    res.send({ id: recordsets.recordset[0][""] });

  });

});

//delete路由(根据表的名字 字段的名字 字段的值删除行)

server.delete("/api/comm/deleterow", (req, res) => {

  const strtablename = req.body.strtablename; //哪个表?

  const strconditionfield = req.body.strconditionfield; //哪个字段?

  const strConditiondata = req.body.strConditiondata; //值是多少  假设字段为id 值为202212120007

  const request = new mssql.Request(); //新建请求

  request.input("TableName", mssql.VarChar, strtablename); //配置参数的数据类型

  request.input("ConditionField", mssql.VarChar, strconditionfield);

  request.input("Conditiondata", mssql.VarChar, strConditiondata);

  request.execute(

    "prCommDeleteRowByTableName_ConditionField_ConditionData",

    (err, recordsets, returnvalue) => {

      //console.log(recordsets.recordset);

      res.send({ result: "sucess" }); //向客户端返回

      return;

    }

  );

});

//put路由(根据id更新此id所在行数据)

server.put("/api/buyin/guard/updatebyid", (req, res) => {

  const strintime = req.body.strintime;

  const strtranser = req.body.strtranser;

  const strtranserid = req.body.strtranserid;

  const strtranseraddress = req.body.strtranseraddress;

  const strtransertele = req.body.strtransertele;

  const strcartype = req.body.strcartype;

  const strcarnum = req.body.strcarnum;

  const strguardermemo = req.body.strguardermemo;

  const stringuarder = req.body.stringuarder;

  const strid = req.body.strid;

  const request = new mssql.Request(); //新建请求

  request.input("InTime", mssql.VarChar, strintime); //配置参数的数据类型

  request.input("Transer", mssql.VarChar, strtranser);

  request.input("TranserID", mssql.VarChar, strtranserid);

  request.input("TranserAddress", mssql.VarChar, strtranseraddress);

  request.input("TranserTele", mssql.VarChar, strtransertele);

  request.input("CarType", mssql.VarChar, strcartype);

  request.input("CarNum", mssql.VarChar, strcarnum);

  request.input("GuarderMemo", mssql.VarChar, strguardermemo);

  request.input("inguarder", mssql.VarChar, stringuarder);

  request.input("strID", mssql.VarChar, strid);

  request.execute("prBuyInUpdateGuardByID", (err, recordsets, returnvalue) => {

    res.send({ result: "success" });

  });

});

//get路由(根据参数查找等级):如果路由类似这样"/api/courses/:id" 则使用req.params.id

server.get("/api/buyin/assay/findgrade", (req, res) => {

  const breed = req.query.breed;

  const volweight = req.query.volweight;

  const producerough = req.query.producerough;

  const complete = req.query.complete;

  const oil = req.query.oil;

  const request = new mssql.Request(); //新建请求

  request.input("breed", mssql.VarChar, breed); //配置参数的数据类型

  request.input("volweight", mssql.Float, volweight);

  request.input("producerough", mssql.Float, producerough);

  request.input("complete", mssql.Float, complete);

  request.input("oil", mssql.Float, oil);

  request.execute("prBuyInAssayFindGrade", (err, recordsets, returnvalue) => {

    if (err) {

      console.log("查询失败:", err);

      return;

    } else {

      res.send({ grade: recordsets.recordset[0][""] }); //向客户端返回

     }

  });

});

//读取port.txt文件中的port

const port = fs.readFileSync("./port.txt", "utf-8");

server.listen(port, () => {

  console.log(`listening on port ${port}...`);

});

以下为前端html文件:

<!DOCTYPE html>

<html lang="en">

<head>

  <meta charset="UTF-8">

  <meta http-equiv="X-UA-Compatible" content="IE=edge">

  <meta name="viewport" content="width=device-width, initial-scale=1.0">

  <title>Document</title>

</head>

<body>

  <div>

    <label for="txtbreed">品名</label>

    <input type="text" id="txtbreed" value="玉米">

    <label for="txtvolweight">容重</label>

    <input type="text" id="txtvolweight" value="700">

    <label for="txtproducerough">出糙率</label>

    <input type="text" id="txtproducerough" value="0">

    <label for="txtcomplete">完整粒率</label>

    <input type="text" id="txtcomplete" value="0">

    <label for="txtoil">出油率</label>

    <input type="text" id="txtoil" value="0">

  </div>

  <div>

    <button id="cmdgetgrade">获取等级</button>

    <label for="txtgrade">等级</label>

    <input type="text" id="txtgrade">

  </div>

  <div>

    <label for="txtday">日期</label>

    <input type="text" id="txtday" value="20221212">

  </div>

  <div>

    <button id="cmdgetbuyinid">获取收购id</button>

    <label for="txtbuyinid">id</label>

    <input type="text" id="txtbuyinid">

  </div>

  <!-- put测试 -->

  <div>

    <label for="txtintime">入库时间</label>

    <input type="text" id="txtintime" value="2022-12-12 12:12:12">

    <label for="txttranser">承运人</label>

    <input type="text" id="txttranser" value="张三">

    <label for="txttranserid">承运人id</label>

    <input type="text" id="txttranserid" value="15040219740126471x">

    <label for="txttranseraddress">承运人住址</label>

    <input type="text" id="txttranseraddress" value="内蒙古赤峰市松山区玉龙街道富河社区">

    <label for="txttransertele">承运人电话</label>

    <input type="text" id="txttransertele" value="13604767585">

    <label for="txtcartype">车型</label>

    <input type="text" id="txtcartype" value="前四后八">

    <label for="txtcarnum">车号</label>

    <input type="text" id="txtcarnum" value="蒙DEF975">

    <label for="txtguardermemo">登记备注</label>

    <input type="text" id="txtguardermemo" value="暂无">

    <label for="txtinguarder">入库登记员</label>

    <input type="text" id="txtinguarder" value="李四">

    <label for="txtgurderid">id</label>

    <input type="text" id="txtguarderid" value="202212120001">

  </div>

  <div>

    <button id="cmdupdateguardbyid">更新</button>

    <label for="txtguarderresut">结果</label>

    <input type="text" id="txtguarderresut">

  </div>

  <div>

    <label for="txtdeleteid">id</label>

    <input type="text" id="txtdeleteid" value="202212120007">

  </div>

  <div>

    <button id="cmddeletebyid">删除</button>

    <label for="txtdeleteresut">结果</label>

    <input type="text" id="txtdeleteresut">

  </div>

 

  <script src="script.js"></script>

</body>

</html>

以下为html文件用的script.js文件:

//get 查询数据,不改变数据库数据

document.getElementById("cmdgetgrade").addEventListener("click", function () {

  const breed = document.getElementById("txtbreed").value;

  console.log(breed);

  const volweight = document.getElementById("txtvolweight").value;

  const producerough = document.getElementById("txtproducerough").value;

  const complete = document.getElementById("txtcomplete").value;

  const oil = document.getElementById("txtoil").value;

  fetch(

    `http://127.0.0.1:3000/api/buyin/assay/findgrade?breed=${breed}&&volweight=${volweight}&&producerough=${producerough}&&complete=${complete}&&oil=${oil}`

  )

    .then((res) => res.json())

    .then((json) => {

      console.log(json); //console.log(json[0].StartDateTime); *** */必须注意区分大小写

      document.getElementById("txtgrade").value = json.grade;

    })

    .catch((e) => {

      console.log("error:", e);

    });

});

//post 插入id,返回插入的id

document.getElementById("cmdgetbuyinid").addEventListener("click", function () {

  const strday = document.getElementById("txtday").value;

  const settings = {

    method: "POST",

    headers: { Accept: "application/json", "Content-Type": "application/json" },

    body: JSON.stringify({

      strday: `${strday}`,

    }),

  };

  fetch(`http://127.0.0.1:3000/api/buyin/guard/makenewid`, settings)

    .then((res) => res.json())

    .then((json) => {

      //console.log(json);

      document.getElementById("txtbuyinid").value = json.id;

    })

    .catch((e) => {

      console.log("error:", e);

    });

});

//put 根据条件(id)更新

document

  .getElementById("cmdupdateguardbyid")

  .addEventListener("click", function () {

    const strintime = document.getElementById("txtintime").value;

    const strtranser = document.getElementById("txttranser").value;

    const strtranserid = document.getElementById("txttranserid").value;

    const strtranseraddress =

      document.getElementById("txttranseraddress").value;

    const strtransertele = document.getElementById("txttransertele").value;

    const strcartype = document.getElementById("txtcartype").value;

    const strcarnum = document.getElementById("txtcarnum").value;

    const strguardermemo = document.getElementById("txtguardermemo").value;

    const stringuarder = document.getElementById("txtinguarder").value;

    const strid = document.getElementById("txtguarderid").value;

    const settings = {

      method: "PUT",

      headers: {

        Accept: "application/json",

        "Content-Type": "application/json",

      },

      body: JSON.stringify({

        strintime: `${strintime}`,

        strtranser: `${strtranser}`,

        strtranserid: `${strtranserid}`,

        strtranseraddress: `${strtranseraddress}`,

        strtransertele: `${strtransertele}`,

        strcartype: `${strcartype}`,

        strcarnum: `${strcarnum}`,

        strguardermemo: `${strguardermemo}`,

        stringuarder: `${stringuarder}`,

        strid: `${strid}`,

      }),

    };

    fetch(`http://127.0.0.1:3000/api/buyin/guard/updatebyid`, settings)

      .then((res) => res.json())

      .then((json) => {

        //console.log(json);

        document.getElementById("txtguarderresut").value = json.result;

      })

      .catch((e) => {

        console.log("error:", e);

      });

  });

//delete

document.getElementById("cmddeletebyid").addEventListener("click", function () {

  const strtablename = "vwbuyin";

  const strconditionfield = "id";

  const strConditiondata = document.getElementById("txtdeleteid").value;

  const settings = {

    method: "DELETE",

    headers: { Accept: "application/json", "Content-Type": "application/json" },

    body: JSON.stringify({

      strtablename: `${strtablename}`,

      strconditionfield: `${strconditionfield}`,

      strConditiondata: `${strConditiondata}`,

    }),

  };

  fetch(`http://127.0.0.1:3000/api/comm/deleterow`, settings)

    .then((res) => res.json())

    .then((json) => {

      //console.log(json);

      document.getElementById("txtdeleteresut").value = json.result;

    })

    .catch((e) => {

      console.log("error:", e);

    });

});

以下为用到的表:此表可以为空表

CREATE TABLE [dbo].[tbBuyIn](
    [ID] [varchar](100) NOT NULL,
    [InTime] [varchar](100) NULL,
    [Transer] [varchar](100) NULL,
    [TranserID] [varchar](100) NULL,
    [TranserAddress] [varchar](100) NULL,
    [TranserTele] [varchar](100) NULL,
    [CarType] [varchar](100) NULL,
    [CarNum] [varchar](100) NULL,
    [GuarderMemo] [varchar](100) NULL,
    [InGuarder] [varchar](100) NULL,
    [OutTime] [varchar](100) NULL,
    [OutGuarder] [varchar](100) NULL,
    [AssayWriteTime] [varchar](100) NULL,
    [PlanType] [varchar](100) NULL,
    [StoreProperty] [varchar](100) NULL,
    [ContractNum] [varchar](100) NULL,
    [InStorePlanNum] [varchar](100) NULL,
    [StoreNum] [varchar](100) NULL,
    [StoreNumSub] [varchar](100) NULL,
    [ProduceCounty] [varchar](100) NULL,
    [ProduceArea] [varchar](100) NULL,
    [InStoreYear] [varchar](100) NULL,
    [ProduceYear] [varchar](100) NULL,
    [Saler] [varchar](100) NULL,
    [SalerCode] [varchar](100) NULL,
    [SalerID] [varchar](100) NULL,
    [SalerAddress] [varchar](100) NULL,
    [SalerTele] [varchar](100) NULL,
    [AssayPaperNum] [varchar](100) NULL,
    [Breed] [varchar](100) NULL,
    [Grade] [varchar](100) NULL,
    [Water] [float] NULL,
    [WaterSubWeightBase] [float] NULL,
    [WaterSubWeightMore] [float] NULL,
    [WaterSubWeightPer] [float] NULL,
    [WaterAddWeightMore] [float] NULL,
    [WaterAddWeightPer] [float] NULL,
    [WaterSubPriceBase] [float] NULL,
    [WaterSubPriceMore] [float] NULL,
    [WaterSubPricePer] [float] NULL,
    [InnerWaterSubWeightBase] [float] NULL,
    [InnerWaterSubWeightMore] [float] NULL,
    [InnerWaterSubWeightPer] [float] NULL,
    [WaterAdditionalSubWeightBase] [float] NULL,
    [WaterAdditionalSubWeightMore] [float] NULL,
    [WaterAdditionalSubWeightPer] [float] NULL,
    [WaterThirdSubWeightBase] [float] NULL,
    [WaterThirdSubWeightMore] [float] NULL,
    [WaterThirdSubWeightPer] [float] NULL,
    [Impurity] [float] NULL,
    [ImpuritySubWeightBase] [float] NULL,
    [ImpuritySubWeightMore] [float] NULL,
    [ImpuritySubWeightPer] [float] NULL,
    [ImpurityAddWeightMore] [float] NULL,
    [ImpurityAddWeightPer] [float] NULL,
    [ImpuritySubPriceBase] [float] NULL,
    [ImpuritySubPriceMore] [float] NULL,
    [ImpuritySubPricePer] [float] NULL,
    [VolWeight] [float] NULL,
    [VolWeightSubWeightBase] [float] NULL,
    [VolWeightSubWeightMore] [float] NULL,
    [VolWeightSubWeightPer] [float] NULL,
    [NoPerfect] [float] NULL,
    [NoPerfectSubWeightBase] [float] NULL,
    [NoPerfectSubWeightMore] [float] NULL,
    [NoPerfectSubWeightPer] [float] NULL,
    [NoPerfectSubPriceBase] [float] NULL,
    [NoPerfectSubPriceMore] [float] NULL,
    [NoPerfectSubPricePer] [float] NULL,
    [Mildew] [float] NULL,
    [MildewSubWeightBase] [float] NULL,
    [MildewSubWeightMore] [float] NULL,
    [MildewSubWeightPer] [float] NULL,
    [MildewSubPriceBase] [float] NULL,
    [MildewSubPriceMore] [float] NULL,
    [MildewSubPricePer] [float] NULL,
    [HardE] [float] NULL,
    [ProduceRough] [float] NULL,
    [FullExtract] [float] NULL,
    [FullExtractSubWeightBase] [float] NULL,
    [FullExtractSubWeightMore] [float] NULL,
    [FullExtractSubWeightPer] [float] NULL,
    [FullExtractSubPriceBase] [float] NULL,
    [FullExtractSubPriceMore] [float] NULL,
    [FullExtractSubPricePer] [float] NULL,
    [EachMix] [float] NULL,
    [EachMixSubWeightBase] [float] NULL,
    [EachMixSubWeightMore] [float] NULL,
    [EachMixSubWeightPer] [float] NULL,
    [EachMixSubPriceBase] [float] NULL,
    [EachMixSubPriceMore] [float] NULL,
    [EachMixSubPricePer] [float] NULL,
    [YellowRice] [float] NULL,
    [YellowRiceSubWeightBase] [float] NULL,
    [YellowRiceSubWeightMore] [float] NULL,
    [YellowRiceSubWeightPer] [float] NULL,
    [YellowRiceSubPriceBase] [float] NULL,
    [YellowRiceSubPriceMore] [float] NULL,
    [YellowRiceSubPricePer] [float] NULL,
    [GrainOutRough] [float] NULL,
    [GrainOutRoughSubWeightBase] [float] NULL,
    [GrainOutRoughSubWeightMore] [float] NULL,
    [GrainOutRoughSubWeightPer] [float] NULL,
    [GrainOutRoughSubPriceBase] [float] NULL,
    [GrainOutRoughSubPriceMore] [float] NULL,
    [GrainOutRoughSubPriceper] [float] NULL,
    [Complete] [float] NULL,
    [Oil] [float] NULL,
    [BadHurt] [varchar](100) NULL,
    [HotHurt] [varchar](100) NULL,
    [ColorOdor] [varchar](100) NULL,
    [IsDisperse] [varchar](100) NULL,
    [EstablishPriceCondition] [varchar](100) NULL,
    [AssayMemo] [varchar](100) NULL,
    [GetSampler] [varchar](100) NULL,
    [Assayer] [varchar](100) NULL,
    [AssayWriter] [varchar](100) NULL,
    [TotalWeight] [float] NULL,
    [TotalWeightTime] [varchar](100) NULL,
    [TotalWeighter] [varchar](100) NULL,
    [CarWeight] [float] NULL,
    [CarWeightTime] [varchar](100) NULL,
    [CarWeighter] [varchar](100) NULL,
    [PureWeight] [float] NULL,
    [NowSubWeight] [float] NULL,
    [WatchUnloader] [varchar](100) NULL,
    [Keeper] [varchar](100) NULL,
    [PureWeightAfterSubPack] [float] NULL,
    [WaterSubWeight] [float] NULL,
    [WaterAdditionalSubWeight] [float] NULL,
    [WaterThirdSubWeight] [float] NULL,
    [ImpuritySubWeight] [float] NULL,
    [VolWeightSubWeight] [float] NULL,
    [NoPerfectSubWeight] [float] NULL,
    [MildewSubWeight] [float] NULL,
    [FullExtractSubWeight] [float] NULL,
    [EachMixSubWeight] [float] NULL,
    [YellowRiceSubWeight] [float] NULL,
    [GrainOutRoughSubWeight] [float] NULL,
    [PureWeightAfterSubWeight] [float] NULL,
    [InnerWaterSubWeight] [float] NULL,
    [InnerPureWeight] [float] NULL,
    [Price] [float] NULL,
    [UnloadPrice] [float] NULL,
    [TransPrice] [float] NULL,
    [AvgPrice] [float] NULL,
    [ShouldPayMoney] [float] NULL,
    [WaterSubPrice] [float] NULL,
    [ImpuritySubPrice] [float] NULL,
    [NoPerfectSubPrice] [float] NULL,
    [MildewSubPrice] [float] NULL,
    [FullExtractSubPrice] [float] NULL,
    [EachMixSubPrice] [float] NULL,
    [YellowRiceSubPrice] [float] NULL,
    [GrainOutRoughSubPrice] [float] NULL,
    [UnloadSubPrice] [float] NULL,
    [TransSubPrice] [float] NULL,
    [TruePayMoney] [float] NULL,
    [TruePayMoneyBigWrite] [varchar](100) NULL,
    [CalcFrom] [varchar](100) NULL,
    [SendForm] [varchar](100) NULL,
    [IsPrivate] [varchar](100) NULL,
    [BankCardName] [varchar](100) NULL,
    [BankCardNum] [varchar](100) NULL,
    [BankName] [varchar](100) NULL,
    [BankNum] [varchar](100) NULL,
    [BankProvince] [varchar](100) NULL,
    [BankCity] [varchar](100) NULL,
    [Calcer] [varchar](100) NULL,
    [CalcTime] [varchar](100) NULL,
    [Checker] [varchar](100) NULL,
    [Paymenter] [varchar](100) NULL,
    [CalcMemo] [varchar](100) NULL,
    [UseDirectSubPrice] [float] NULL,
    [IsReCheck] [float] NULL,
    [ReChecker] [varchar](100) NULL,
    [ReCheckDateTime] [varchar](100) NULL,
    [U8Mark] [varchar](100) NULL,
    [GBMark] [varchar](100) NULL,
    [OtherMark] [varchar](100) NULL,
    [Gps] [varchar](100) NULL,
    [OriPrice] [float] NULL,
    [GuardPrinted] [float] NULL,
    [AssayPrinted] [float] NULL,
    [WeightPrinted] [float] NULL,
    [CalcPrinted] [float] NULL,
    [Province] [varchar](100) NULL,
    [City] [varchar](100) NULL,
    [County] [varchar](100) NULL,
    [Company] [varchar](100) NULL,
    [IsUp] [varchar](100) NULL,
    [imgTotalWeight1] [image] NULL,
    [imgTotalWeight2] [image] NULL,
    [imgTotalWeight3] [image] NULL,
    [imgCarWeight1] [image] NULL,
    [imgCarWeight2] [image] NULL,
    [imgCarWeight3] [image] NULL,
    [ImgSaler] [image] NULL,
    [ImgWeightPaper] [image] NULL,
    [ImgCalcPaper] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
 

------------------------------------------------

此表需要填充数据

CREATE TABLE [dbo].[tbBuyInManagerPriceArg](
    [ID] [varchar](100) NOT NULL,
    [DateTime] [varchar](100) NULL,
    [Breed] [varchar](100) NULL,
    [GradeStartArg] [float] NULL,
    [GradeEndArg] [float] NULL,
    [Grade] [varchar](100) NULL,
    [EstablishPriceCondition] [varchar](100) NULL,
    [StartWater] [float] NULL,
    [EndWater] [float] NULL,
    [StartImpurity] [float] NULL,
    [EndImpurity] [float] NULL,
    [StartNoPerfect] [float] NULL,
    [EndNoPerfect] [float] NULL,
    [StartMildew] [float] NULL,
    [EndMildew] [float] NULL,
    [StartFullExtract] [float] NULL,
    [EndFullExtract] [float] NULL,
    [StartEachMix] [float] NULL,
    [EndEachMix] [float] NULL,
    [StartYellowRice] [float] NULL,
    [EndYellowRice] [float] NULL,
    [StartGrainOutRough] [float] NULL,
    [EndGrainOutRough] [float] NULL,
    [Price] [float] NULL,
    [WaterSubWeightBase] [float] NULL,
    [WaterSubWeightMore] [float] NULL,
    [WaterSubWeightPer] [float] NULL,
    [WaterAddWeightMore] [float] NULL,
    [WaterAddWeightPer] [float] NULL,
    [WaterSubPriceBase] [float] NULL,
    [WaterSubPriceMore] [float] NULL,
    [WaterSubPricePer] [float] NULL,
    [InnerWaterSubWeightBase] [float] NULL,
    [InnerWaterSubWeightMore] [float] NULL,
    [InnerWaterSubWeightPer] [float] NULL,
    [WaterAdditionalSubWeightBase] [float] NULL,
    [WaterAdditionalSubWeightMore] [float] NULL,
    [WaterAdditionalSubWeightPer] [float] NULL,
    [waterThirdSubWeightBase] [float] NULL,
    [waterThirdSubWeightMore] [float] NULL,
    [waterThirdSubWeightPer] [float] NULL,
    [ImpuritySubWeightBase] [float] NULL,
    [ImpuritySubWeightMore] [float] NULL,
    [ImpuritySubWeightPer] [float] NULL,
    [ImpurityAddWeightMore] [float] NULL,
    [ImpurityAddWeightPer] [float] NULL,
    [ImpuritySubPriceBase] [float] NULL,
    [ImpuritySubPriceMore] [float] NULL,
    [ImpuritySubPricePer] [float] NULL,
    [NoPerfectSubWeightBase] [float] NULL,
    [NoPerfectSubWeightMore] [float] NULL,
    [NoPerfectSubWeightPer] [float] NULL,
    [NoPerfectSubPriceBase] [float] NULL,
    [NoPerfectSubPriceMore] [float] NULL,
    [NoPerfectSubPricePer] [float] NULL,
    [MildewSubWeightBase] [float] NULL,
    [MildewSubWeightMore] [float] NULL,
    [MildewSubWeightPer] [float] NULL,
    [MildewSubPriceBase] [float] NULL,
    [MildewSubPriceMore] [float] NULL,
    [MildewSubPricePer] [float] NULL,
    [FullExtractSubWeightBase] [float] NULL,
    [FullExtractSubWeightMore] [float] NULL,
    [FullExtractSubWeightPer] [float] NULL,
    [FullExtractSubPriceBase] [float] NULL,
    [FullExtractSubPriceMore] [float] NULL,
    [FullExtractSubPricePer] [float] NULL,
    [EachMixSubWeightBase] [float] NULL,
    [EachMixSubWeightMore] [float] NULL,
    [EachMixSubWeightPer] [float] NULL,
    [EachMixSubPriceBase] [float] NULL,
    [EachMixSubPriceMore] [float] NULL,
    [EachMixSubPricePer] [float] NULL,
    [YellowRiceSubWeightBase] [float] NULL,
    [YellowRiceSubWeightMore] [float] NULL,
    [YellowRiceSubWeightPer] [float] NULL,
    [YellowRiceSubPriceBase] [float] NULL,
    [YellowRiceSubPriceMore] [float] NULL,
    [YellowRiceSubPricePer] [float] NULL,
    [GrainOutRoughSubWeightBase] [float] NULL,
    [GrainOutRoughSubWeightMore] [float] NULL,
    [GrainOutRoughSubWeightPer] [float] NULL,
    [GrainOutRoughSubPriceBase] [float] NULL,
    [GrainOutRoughSubPriceMore] [float] NULL,
    [GrainOutRoughSubPricePer] [float] NULL,
    [VolWeightSubWeightBase] [float] NULL,
    [VolWeightSubWeightMore] [float] NULL,
    [VolWeightSubWeightPer] [float] NULL,
    [CompanyName] [varchar](100) NULL,
    [UseDirectSubPrice] [float] NULL
) ON [PRIMARY]
 

初始填充数据:

2022110900001	2022-11-10 10:12:40	玉米	690	1000	2	测试	0	100	0	100	0	100	0	100	0	100	0	100	0	100	0	100	2.64	0	0	0	0	0	14	0.1	0.003	14	0.1	0.125	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	x	1
NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL

-------------------------------------------------------

以下为用到的vwbuyin视图:就是tbbuyin表去掉了几个字段.

SELECT     ID, InTime, Transer, TranserID, TranserAddress, TranserTele, CarType, CarNum, GuarderMemo, InGuarder, OutTime, OutGuarder, AssayWriteTime, PlanType, StoreProperty, ContractNum, 
                      InStorePlanNum, StoreNum, StoreNumSub, ProduceCounty, ProduceArea, InStoreYear, ProduceYear, Saler, SalerCode, SalerID, SalerAddress, SalerTele, AssayPaperNum, Breed, Grade, Water, 
                      WaterSubWeightBase, WaterSubWeightMore, WaterSubWeightPer, WaterAddWeightMore, WaterAddWeightPer, WaterSubPriceBase, WaterSubPriceMore, WaterSubPricePer, 
                      InnerWaterSubWeightBase, InnerWaterSubWeightMore, InnerWaterSubWeightPer, WaterAdditionalSubWeightBase, WaterAdditionalSubWeightMore, WaterAdditionalSubWeightPer, 
                      WaterThirdSubWeightBase, WaterThirdSubWeightMore, WaterThirdSubWeightPer, Impurity, ImpuritySubWeightBase, ImpuritySubWeightMore, ImpuritySubWeightPer, ImpurityAddWeightMore, 
                      ImpurityAddWeightPer, ImpuritySubPriceBase, ImpuritySubPriceMore, ImpuritySubPricePer, VolWeight, VolWeightSubWeightBase, VolWeightSubWeightMore, VolWeightSubWeightPer, NoPerfect, 
                      NoPerfectSubWeightBase, NoPerfectSubWeightMore, NoPerfectSubWeightPer, NoPerfectSubPriceBase, NoPerfectSubPriceMore, NoPerfectSubPricePer, Mildew, MildewSubWeightBase, 
                      MildewSubWeightMore, MildewSubWeightPer, MildewSubPriceBase, MildewSubPriceMore, MildewSubPricePer, HardE, ProduceRough, FullExtract, FullExtractSubWeightBase, 
                      FullExtractSubWeightMore, FullExtractSubWeightPer, FullExtractSubPriceBase, FullExtractSubPriceMore, FullExtractSubPricePer, EachMix, EachMixSubWeightBase, EachMixSubWeightMore, 
                      EachMixSubWeightPer, EachMixSubPriceBase, EachMixSubPriceMore, EachMixSubPricePer, YellowRice, YellowRiceSubWeightBase, YellowRiceSubWeightMore, YellowRiceSubWeightPer, 
                      YellowRiceSubPriceBase, YellowRiceSubPriceMore, YellowRiceSubPricePer, GrainOutRough, GrainOutRoughSubWeightBase, GrainOutRoughSubWeightMore, GrainOutRoughSubWeightPer, 
                      GrainOutRoughSubPriceBase, GrainOutRoughSubPriceMore, GrainOutRoughSubPriceper, Complete, Oil, BadHurt, HotHurt, ColorOdor, IsDisperse, EstablishPriceCondition, AssayMemo, GetSampler, 
                      Assayer, AssayWriter, TotalWeight, TotalWeightTime, TotalWeighter, CarWeight, CarWeightTime, CarWeighter, PureWeight, NowSubWeight, WatchUnloader, Keeper, PureWeightAfterSubPack, 
                      WaterSubWeight, WaterAdditionalSubWeight, WaterThirdSubWeight, ImpuritySubWeight, VolWeightSubWeight, NoPerfectSubWeight, MildewSubWeight, FullExtractSubWeight, EachMixSubWeight, 
                      YellowRiceSubWeight, GrainOutRoughSubWeight, PureWeightAfterSubWeight, InnerWaterSubWeight, InnerPureWeight, Price, UnloadPrice, TransPrice, AvgPrice, ShouldPayMoney, WaterSubPrice, 
                      ImpuritySubPrice, NoPerfectSubPrice, MildewSubPrice, FullExtractSubPrice, EachMixSubPrice, YellowRiceSubPrice, GrainOutRoughSubPrice, UnloadSubPrice, TransSubPrice, TruePayMoney, 
                      TruePayMoneyBigWrite, CalcFrom, SendForm, IsPrivate, BankCardName, BankCardNum, BankName, BankNum, BankProvince, BankCity, Calcer, CalcTime, Checker, Paymenter, CalcMemo, 
                      UseDirectSubPrice, IsReCheck, ReChecker, ReCheckDateTime, U8Mark, GBMark, OtherMark, Gps, OriPrice, GuardPrinted, AssayPrinted, WeightPrinted, CalcPrinted, Province, City, County, 
                      Company, IsUp
FROM         dbo.tbBuyIn

以下为用到的四个简单存储过程:需要表与视图都建立好才可以建立存储过程

Create PROCEDURE [dbo].[prBuyInMakeNewID]
@strToday varchar(100)
AS

    declare @tmpnum float
    select @tmpnum=convert(float,right(isnull(max(id), @strToday+'0000'),4)) from tbBuyIn where left(id,8)=@strToday
    set @tmpnum=@tmpnum+1

    declare @maxid varchar(100)
    set   @maxid =  @strToday  +  RIGHT('0000'+convert(varchar(100),@tmpnum),4)

     insert into tbBuyIn (id,isup) values(@maxid, '未上传')
     select @maxid

------------------------------------------------------------------------------

create PROCEDURE [dbo].[prCommDeleteRowByTableName_ConditionField_ConditionData]
@TableName varchar(100),
@ConditionField varchar(100)=null,
@Conditiondata varchar(100)=null

AS

declare @strSql nvarchar(4000)
set @strSql='delete  from ' +  @tablename  

if Len(@ConditionField)<>0 and Len(@Conditiondata)<>0
    set @strSql= @strSql + '  where '  +  @ConditionField + ' =  ' + char(39) + @Conditiondata + char(39)
exec sp_executesql @strSql

------------------------------------------------------------------------------------------------

create PROCEDURE [dbo].[prBuyInUpdateGuardByID] 
@InTime varchar(100),
@Transer varchar(100),
@TranserID varchar(100),
@TranserAddress varchar(100),
@TranserTele varchar(100),
@CarType varchar(100),
@CarNum varchar(100),
@GuarderMemo varchar(100),
@inguarder varchar(100),
@strID VarChar(100)

AS

update vwbuyin set
intime=@intime,
Transer =@Transer,
TranserID =@TranserID,
TranserAddress =@TranserAddress,
TranserTele =@TranserTele,
CarType =@CarType,
CarNum =@CarNum,
GuarderMemo =@guarderMemo,
inguarder =@inguarder,
isup='未上传'
where id=@strid

--------------------------------------------------------------------------------------------------------

create PROCEDURE [dbo].[prBuyInAssayFindGrade]
@breed varchar(50),
@volweight float,
@producerough float,
@complete float,
@oil float
AS

declare @grade varchar(100)

if @breed LIKE '%玉%'  or @breed LIKE '%粱%'
    select @grade=grade from tbBuyInManagerPriceArg where breed=@breed  and ( @volweight>=gradestartarg and  @volweight<=gradeendarg )
else if @breed like '%麦%' 
    select @grade=grade from tbBuyInManagerPriceArg where breed=@breed  and ( @volweight>=gradestartarg and  @volweight<=gradeendarg )
else if @breed like '%稻%' 
    select @grade=grade from tbBuyInManagerPriceArg where breed=@breed and ( @producerough>=gradestartarg and  @producerough<=gradeendarg )
else if @breed like '%豆%' 
    select @grade=grade from tbBuyInManagerPriceArg where breed=@breed  and ( @complete>=gradestartarg and  @Complete<=gradeendarg )
else if @breed like '%菜%' 
    select @grade=grade from tbBuyInManagerPriceArg where breed=@breed  and ( @oil>=gradestartarg and  @oil<=gradeendarg )
else
    select @grade=grade from tbBuyInManagerPriceArg where breed=@breed   
select @grade

好长啊,写的时候边学边写没什么感觉,总结起来居然如此费劲.

接下来的学习内容就是:  更好的路由,async/await.