在页面刷新时，我的存储过程将视图中显示的项加倍

本文关键字：显示 视图 存储过程 刷新 我的 | 更新日期: 2023-09-27 18:10:31

Sql Server 2014, MVC 4 Web Service方法，Json数据，调用存储过程，在页面刷新时，视图中的数据计数加倍。重复。不断

查询:

        ALTER PROCEDURE spGetPricing
AS
BEGIN
INSERT INTO Pricing_Compare (ProductID,SKU,ScansourcePrice,BluestarPrice,BarcodePrice,Manufacturer,Product)    
SELECT Product.ProductID,Product.SKU,Scansource.COST, Bluestar.PRICE, ProductVariant.Price, Manufacturer.Name,Product.Name
FROM Product 
INNER JOIN Scansource ON Product.SKU =Scansource.SKU
INNER JOIN Bluestar ON Product.SKU = Bluestar.MFG_PART_NUMBER
INNER JOIN ProductVariant ON Product.ProductID = ProductVariant.ProductID 
INNER JOIN ProductManufacturer ON Product.ProductID = ProductManufacturer.ProductID 
INNER JOIN Manufacturer ON ProductManufacturer.ManufacturerID = Manufacturer.ManufacturerID
SELECT * FROM Pricing_Compare
END
c#代码:

using CSVTool.Models;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Script.Serialization;
using System.Web.Services;
namespace CSVTool
{
    /// <summary>
    /// Summary description for PricingService
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
     [System.Web.Script.Services.ScriptService]
    public class PricingService : System.Web.Services.WebService
    {
        [WebMethod]
        public string HelloWorld()
        {
            return "Hello World";
        }
        [WebMethod]
        public void GetPricing()
        {
            string cs = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            List<PricingModel> pricingModel = new List<PricingModel>();
            using (SqlConnection con = new SqlConnection(cs))
            {
                SqlCommand cmd = new SqlCommand("spGetPricing", con);
                cmd.CommandType = CommandType.StoredProcedure;
                con.Open();
                SqlDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    PricingModel pricinglist = new PricingModel();
                    pricinglist.Manufacturer = rdr["Manufacturer"].ToString();
                    pricinglist.Product = rdr["Product"].ToString();
                    pricinglist.SKU = rdr["SKU"].ToString();
                    pricinglist.BarcodePrice = rdr["BarcodePrice"].ToString();
                    pricinglist.ScansourcePrice = rdr["ScansourcePrice"].ToString();
                    pricinglist.BluestarPrice = rdr["BluestarPrice"].ToString();
                    pricingModel.Add(pricinglist);
                }
            }
            JavaScriptSerializer js = new JavaScriptSerializer(); js.MaxJsonLength = 2147483644;
            Context.Response.Write(js.Serialize(pricingModel));
        }
    }
}

我有另外两个视图，它们的行为不像那样，但存储过程只是一个选择，而不是伴随的插入和连接。是否有一种方法可以更改存储过程，以便每次加载页面时不会加载一组新的重复项?在查询中还是在客户端(在我的web服务方法中)?

在页面刷新时，我的存储过程将视图中显示的项加倍

问题是每次执行存储过程时，都要先将数据插入Pricing_Compare，然后从中进行选择。

这意味着，对于存储过程的每次执行，您都在向Pricing_Compare中添加(可能的)重复记录。

根本不需要填充这个表—只返回这样的数据:

ALTER PROCEDURE spGetPricing
AS
BEGIN
SELECT 
    Product.ProductID,
    Product.SKU,
    Scansource.COST, 
    Bluestar.PRICE, 
    ProductVariant.Price, 
    Manufacturer.Name,
    Product.Name AS ProductName
FROM 
    Product 
INNER JOIN 
    Scansource 
ON 
    Product.SKU =Scansource.SKU
INNER JOIN 
    Bluestar 
ON 
    Product.SKU = Bluestar.MFG_PART_NUMBER
INNER JOIN 
    ProductVariant 
ON 
    Product.ProductID = ProductVariant.ProductID 
INNER JOIN 
    ProductManufacturer 
ON 
    Product.ProductID = ProductManufacturer.ProductID 
INNER JOIN 
    Manufacturer 
ON 
    ProductManufacturer.ManufacturerID = Manufacturer.ManufacturerID
END
相关文章：