时间戳

首页 > 解决方案 > 在存储过程中组合两个视图

c# - 在存储过程中组合两个视图

问题描述

我不知道这样做的最佳做法是什么，但如果我需要一个存储过程来从多个视图中获取数据。

我有两个视图，第一个视图将参数传递CreationDate给它，第二个视图使用附加语句执行第一个视图。

我想做的是从 C# 代码中从这两个视图中选择数据；我正在使用 ASP.NET Webforms、VS 2010 从第一个视图更改后从第二个视图获取结果CreationDate。

这是从第一个视图中选择的称为VwInprogressSub1

select 
    CurrentAgentFK, 
    Count(*) as TotalLeads,
    Sum(CASE WHEN LeadTicket.leadticketstatusfk=4 THEN 1 ELSE 0 END) AS AssignedCount,
    Sum(CASE WHEN LeadTicket.leadticketstatusfk=6 THEN 1 ELSE 0 END) AS InProgressCount,
    Sum(CASE WHEN DaysToGetInProgress=0 THEN 1 ELSE 0 END) AS RespondedSameDay,
    Sum(CASE WHEN DaysToGetInProgress=1 THEN 1 ELSE 0 END) AS RespondedSecondDay,
    Sum(CASE WHEN DaysToGetInProgress=2 THEN 1 ELSE 0 END) AS RespondedThirdDay,
    Sum(CASE WHEN DaysToGetInProgress>2 THEN 1 ELSE 0 END) AS RespondedMorethanThreeDay
 from 
     leadticket
 where 
     isfullleadticket = 1 and isold = 0
     and leadticket.CreationDateLG >= 20200201
     and leadticket.CreationDateLG <= 20200229
 group by 
     CurrentAgentFK

这是第二个视图中的选择，称为VwInprogress：

SELECT
    dbo.VW_User.BranchFranchiseeName AS Branch,
    dbo.VW_User.UserName,
    dbo.VwInprogressSub1.TotalLeads,
    dbo.VwInprogressSub1.AssignedCount,
    dbo.VwInprogressSub1.InProgressCount,
    dbo.VwInprogressSub1.RespondedSameDay,
    dbo.VwInprogressSub1.RespondedSecondDay,
    dbo.VwInprogressSub1.RespondedThirdDay,
    dbo.VwInprogressSub1.RespondedMorethanThreeDay,
    (((((dbo.VwInprogressSub1.RespondedSameDay) * 5) +
       ((dbo.VwInprogressSub1.RespondedSecondDay) * 3) +
       ((dbo.VwInprogressSub1.RespondedThirdDay) * 2))) -
      ((dbo.VwInprogressSub1.RespondedMorethanThreeDay) * 2)) AS Score,
    dbo.Vw_AgentsInAllTeams.TeamManagerUsername
FROM
    dbo.VwInprogressSub1
INNER JOIN 
    dbo.VW_User ON dbo.VW_User.UserIdLG = dbo.VwInprogressSub1.CurrentAgentFK
LEFT JOIN 
    dbo.Vw_AgentsInAllTeams ON dbo.VW_User.UserIdLG = dbo.Vw_AgentsInAllTeams.UserPK
WHERE
    dbo.VW_User.IsApproved = 1

标签： c#asp.netsql-serverstored-procedures

解决方案

我会将每个视图包装在一个单独的存储过程中，获取结果并在 C# 代码中执行进一步的逻辑。下面介绍如何创建存储过程并打开连接、创建命令和执行存储过程。您没有指定 ORM 框架，所以我假设您想使用 System.Data.Sqlclient 中的东西。

CREATE PROCEDURE [dbo].[StoredProcedure]
  @CreationDate INT
AS
BEGIN
   -- ... view select
END

using (var connection = new SqlConnection(connectionString))
using (var command = new SqlCommand("StoredProcedure", connection) { 
                           CommandType = CommandType.StoredProcedure }) {
   connection.Open();
   command.Parameters.Add(new SqlParameter("@CreationDate", creationDate));
   var reader = command.ExecuteReader();

   while (reader.Read())
   {
      // Logic depends on how are you going to store view results. DataTable? DTO?
   }
}

--EDIT 进一步对话 SqlDataAdapter 方法：

using (var connection = new SqlConnection(connectionString))
using (var command = new SqlCommand("StoredProcedure", connection) { 
                           CommandType = CommandType.StoredProcedure }) {
   command.Parameters.Add(new SqlParameter("@CreationDate", creationDate));

   using (var dataAdapter = new SqlDataAdapter(command)) {
      var dataTable = new DataTable();
      dataAdapter.Fill(dataTable);
   }

}

推荐阅读