从存储过程中的一个Datamanager方法中读取两个不同表中的两个选择查询。 [英] read two select query from two different table in one Datamanager method from Stored Procedure.

问题描述

HI Everyone，



我需要你的帮助。我在Datamanager中的代码如下：

 public IEntityCollectionBase GetZeroErlangBBHCellCount（IEntityBase entityBase）
 {
 IDataReader dataReader = null; 
 IEntityCollectionBase nqi2GList = null; 
 try 
 {
 Logger.Write（Inside Kaizen2GDataManager.GetZeroErlangBBHCellCount（），LogType.Information）; 
 IGraphFilter filter = entityBase as IGraphFilter; 
 
 CommonDataAccess.CreateStoredProcCommandWrapper（KaizenConstants.SPK2ZEROERLANGBBHCELLCOUNT）; 
 CommonDataAccess.AddInParameter（KaizenConstants.STARTDATE，DbType.DateTime，filter.FromDate.ObjectToDBDateTime（））; 
 CommonDataAccess.AddInParameter（KaizenConstants.ENDDATE，DbType.DateTime，filter.ToDate.ObjectToDBDateTime（））; 
 
 dataReader = CommonDataAccess.ExecuteReader（）; 
 nqi2GList = Kaizen2GEntityCreator.GetNqi2GEntityCollection（）; 
 INqi2G nqi2GEntity = null; 
 int count = 0; 
 while（dataReader.Read（））
 {
 nqi2GEntity = Kaizen2GEntityCreator.GetNqi2GEntity（）; 
 nqi2GEntity.Id = count ++; 
 nqi2GEntity.Circle = dataReader [KaizenConstants.CIRCLE] .ObjectToString（）; 
 nqi2GEntity.Date = dataReader [KaizenConstants.DATE] .ObjectToDateTime（）; 
 nqi2GEntity.TotalCeelCount = dataReader [KaizenConstants.TOTALCEELCOUNT] .ObjectToInt32（）; 
 nqi2GEntity.ZeroErlangsCellCount = dataReader [KaizenConstants.ZEROERLANGSCELLCOUNT] .ObjectToInt32（）; 
 nqi2GEntity.CellId = dataReader [KaizenConstants.CELLID] .ObjectToInt32（）; 
 
 nqi2GList.Add（nqi2GEntity）; 
} 
 
 Logger.Write（退出Kaizen2GDataManager.GetZeroErlangBBHCellCount（），LogType.Information）; 
} 
 catch（异常异常）
 {
抛出异常; 
} 
最后
 {
 dataReader.CloseDataReader（）; 
} 
 
返回nqi2GList; 
} 
 

我有一个存储过程如下：

 DECLARE @STARTDATE DATETIME ，@ ENDDATE DATETIME; 
 SET @STARTDATE ='2012-08-13 00:00:00'
 set @ENDDATE ='2014-08-13 00:00:00'
 
 DECLARE @ ZEROERLANGS TABLE 
（
 DATE DATETIME NULL，
 CIRCLE VARCHAR（MAX）NULL，
 TOTALCEELCOUNT INT NULL，
 ZEROERLANGSCELLCOUNT INT NULL 
）
 
 DECLARE @BBHCELL TABLE 
（
 DATE DATETIME NULL，
 CIRCLE VARCHAR（MAX）NULL，
 CELLID INT NULL 
）
 
 --ZTE 
 INSERT INTO @ZEROERLANGS 
 SELECT FROMDATE AS DATE，K2C.CIRCLE，COUNT（D.ID）ASTOTALCEELCOUNT，0 AS ZEROERLANGSCELLCOUNT 
来自K2ZTEBBHDAILY D（NOLOCK）
 INNER JOIN K2COMBINEDMASTER K2C 
 ON D.COMBINEDMASTERID = K2C.ID 
来自@STARTDATE和@ENDDATE之间的日期
 GROUP BY K2C.CIRCLEID，FROMDATE，K2C.CIRCLE 
 
 UPDATE Z 
 SET Z.ZEROERLANGSCELLCOUNT = T.CELLCOUNT 
 FROM @ZEROERLANGS Z INNER JOIN 
（
 SELECT K2C.CIRCLE，COUNT（D.ID）AS CELLCOUNT 
来自K2ZTEBBHDAILY D（NOLOCK）
 INNER JOIN K2COMBINEDMASTER K2C（NOLOCK） 
 ON D.COMBINEDMASTERID = K2C.ID 
在@STARTDATE和@ENDDATE以及VOICEERLANGS = 0 
 GROUP BY K2C.CIRCLEID，FROMDATE，K2C.CIRCLE 
）AS T之间
 ON Z.CIRCLE = T.CIRCLE 
 
  -  HUAWEI 
 INSERT INTO @ZEROERLANGS 
 SELECT FROMDATE AS DATE，K2C.CIRCLE，COUNT（D.ID） AS TOTALCEELCOUNT，0 AS ZEROERLANGSCELLCOUNT 
来自K2HUAWEIBBHDAILY D（NOLOCK）
 INNER JOIN K2COMBINEDMASTER K2C（NOLOCK）
 ON D.COMBINEDMASTERID = K2C.ID 
来自@STARTDATE和@之间的日期ENDDATE 
 GROUP BY K2C.CIRCLEID，FROMDATE，K2C.CIRCLE 
由K2C.CIRCLE订购
 
更新Z 
 SET Z.ZEROERLANGSCELLCOUNT = T.CELLCOUNT 
 FROM @ZEROERLANGS Z INNER JOIN 
（
 SELECT K2C.CIRCLE，COUNT（D.ID）AS CELLCOUNT 
来自K2HUAWEIBBHDAILY D（NOLOCK）
 INNER JOIN K2COMBINEDMASTER K2C（NOLOCK） 
 ON D.COMBINEDMASTERID = K2C.ID 
在@STARTDATE和@ENDDATE以及VOICEERLANGS = 0 
 GROUP BY K2C.CIRCLEID，FROMDATE，K2C.CI之间RCLE 
）AS T 
 ON Z.CIRCLE = T.CIRCLE 
 
 
 
 
 --NSN 
 INSERT INTO @ ZEROERLANGS 
 SELECT FROMDATE AS DATE，K2C.CIRCLE，COUNT（D.ID）ASTOTALCEELCOUNT，0 AS ZEROERLANGSCELLCOUNT 
来自K2NSNBBHDAILY D（NOLOCK）
 INNER JOIN K2COMBINEDMASTER K2C（NOLOCK）
 ON D.COMBINEDMASTERID = K2C.ID 
在@STARTDATE和@ENDDATE之间的日期
 GROUP BY K2C.CIRCLEID，FROMDATE，K2C.CIRCLE 
由K2C.CIRCLE订购
 
 UPDATE Z 
 SET Z.ZEROERLANGSCELLCOUNT = T.CELLCOUNT 
 FROM @ZEROERLANGS Z INNER JOIN 
（
 SELECT K2C.CIRCLE，COUNT（D.ID）AS CELLCOUNT 
来自K2NSNBBHDAILY D（NOLOCK）
 INNER JOIN K2COMBINEDMASTER K2C（NOLOCK）
 ON D.COMBINEDMASTERID = K2C.ID 
来自@STARTDATE和@ENDDATE之间的日期和VOICEERLANGS = 0 
 GROUP BY K2C.CIRCLEID，FROMDATE，K2C.CIRCLE 
）AS T 
 ON Z.CIRCLE = T.CIRCLE 
 
 SELECT * FROM @ZEROERLANGS ORDER BY CIRCLE; 
 
 INSERT INTO @BBHCELL 
 SELECT FROMDATE AS DATE，K2C.CIRCLE，K2C.CELLID FROM K2ZTEBBHDAILY D（NOLOCK）
 INNER JOIN K2COMBINEDMASTER K2C 
 ON D.COMBINEDMASTERID = K2C.ID 
 @STARTDATE与@ENDDATE和VOICEERLANGS之间的时间间隔= 0 
 
 INSERT INTO @BBHCELL 
 SELECT FROMDATE AS DATE，K2C.CIRCLE，K2C.CELLID from K2HUAWEIBBHDAILY D（NOLOCK）
 INNER JOIN K2COMBINEDMASTER K2C（NOLOCK）
 ON D.COMBINEDMASTERID = K2C.ID 
 @STARTDATE与@ENDDATE和VOICEERLANGS之间的日期= 0 
 
 INSERT INTO @BBHCELL 
 SELECT FROMDATE AS DATE，K2C.CIRCLE，K2C.CELLID FROM K2NSNBBHDAILY D（NOLOCK）
 INNER JOIN K2COMBINEDMASTER K2C（NOLOCK）
 ON D.COMBINEDMASTERID = K2C.ID 
 @STARTDATE与@ENDDATE和VOICEERLANGS之间的日期= 0 
由K2C.CIRCLE订购
 
 SELECT * FROM @BBHCELL订购日期，CIRCLE; 

我想读取两个不同表的数据，即选择查询两个不同的表在一个datamanager中。在我的代码中，当datareader.read（）时，我正在读取两个不同表的列，这是错误的。有没有办法读取两个不同的表列。 。任何想法..

请让我知道。



我试试这个：

  while （dataReader.Read（））
 {
 nqi2GEntity = Kaizen2GEntityCreator.GetNqi2GEntity（）; 
 nqi2GEntity.Id = count ++; 
 nqi2GEntity.Circle = dataReader [KaizenConstants.CIRCLE] .ObjectToString（）; 
 nqi2GEntity.Date = dataReader [KaizenConstants.DATE] .ObjectToDateTime（）; 
 nqi2GEntity.TotalCeelCount = dataReader [KaizenConstants.TOTALCEELCOUNT] .ObjectToInt32（）; 
 nqi2GEntity.ZeroErlangsCellCount = dataReader [KaizenConstants.ZEROERLANGSCELLCOUNT] .ObjectToInt32（）; 
  ///   / nqi2GList.Add（nqi2GEntity）; 
 } 
 
  if （dataReader.NextResult（））
 {
  while （dataReader.Read（））
 {
 nqi2GEntity = Kaizen2GEntityCreator.GetNqi2GEntity（）; 
 nqi2GEntity.Id = count ++; 
 nqi2GEntity.Circle = dataReader [KaizenConstants.CIRCLE] .ObjectToString（）; 
 nqi2GEntity.Date = dataReader [KaizenConstants.DATE] .ObjectToDateTime（）; 
 nqi2GEntity.CellId = dataReader [KaizenConstants.CELLID] .ObjectToInt32（）; 
  ///   / nqi2GList.Add（nqi2GEntity）; 
 } 
} 
 
 nqi2GList.Add（nqi2GEntity）; 

它无法读取第二张表代码中的列。为什么会这样?? ??请告诉我。



提前致谢

Harshal

解决方案

您可以在SqlDataReader上调用NextResult来获取从存储过程返回的下一个表。



http://msdn.microsoft.com/en- us / library / system.data.sqlclient.sqldatareader.nextresult（v = vs.110）.aspx [ ^ ]



在dataReader.Read（）上进行while循环后，你可以检查是否（dataReader.NextResult（））然后再做另一个dataReader.Read（）为第二个ta BLE。

HI Everyone,

I need a help from you.I have code in Datamanager as follows:

public IEntityCollectionBase GetZeroErlangBBHCellCount(IEntityBase entityBase)
       {
           IDataReader dataReader = null;
           IEntityCollectionBase nqi2GList = null;
           try
           {
               Logger.Write("Inside Kaizen2GDataManager.GetZeroErlangBBHCellCount()", LogType.Information);
               IGraphFilter filter = entityBase as IGraphFilter;

               CommonDataAccess.CreateStoredProcCommandWrapper(KaizenConstants.SPK2ZEROERLANGBBHCELLCOUNT);
               CommonDataAccess.AddInParameter(KaizenConstants.STARTDATE, DbType.DateTime, filter.FromDate.ObjectToDBDateTime());
               CommonDataAccess.AddInParameter(KaizenConstants.ENDDATE, DbType.DateTime, filter.ToDate.ObjectToDBDateTime());

               dataReader = CommonDataAccess.ExecuteReader();
               nqi2GList = Kaizen2GEntityCreator.GetNqi2GEntityCollection();
               INqi2G nqi2GEntity = null;
               int count = 0;
               while (dataReader.Read())
               {
                   nqi2GEntity = Kaizen2GEntityCreator.GetNqi2GEntity();
                   nqi2GEntity.Id = count++;
                   nqi2GEntity.Circle = dataReader[KaizenConstants.CIRCLE].ObjectToString();
                   nqi2GEntity.Date = dataReader[KaizenConstants.DATE].ObjectToDateTime();
                   nqi2GEntity.TotalCeelCount = dataReader[KaizenConstants.TOTALCEELCOUNT].ObjectToInt32();
                   nqi2GEntity.ZeroErlangsCellCount = dataReader[KaizenConstants.ZEROERLANGSCELLCOUNT].ObjectToInt32();
                   nqi2GEntity.CellId = dataReader[KaizenConstants.CELLID].ObjectToInt32();

                   nqi2GList.Add(nqi2GEntity);
               }

               Logger.Write("Exiting Kaizen2GDataManager.GetZeroErlangBBHCellCount()", LogType.Information);
           }
           catch (Exception exception)
           {
               throw exception;
           }
           finally
           {
               dataReader.CloseDataReader();
           }

           return nqi2GList;
       }

I have a stored Procedure as follows:

DECLARE @STARTDATE DATETIME,@ENDDATE DATETIME;
SET @STARTDATE ='2012-08-13 00:00:00'
set @ENDDATE ='2014-08-13 00:00:00'

DECLARE @ZEROERLANGS TABLE
(
	DATE DATETIME NULL,
	CIRCLE VARCHAR(MAX) NULL,
	TOTALCEELCOUNT INT NULL,
	ZEROERLANGSCELLCOUNT INT NULL
)

DECLARE @BBHCELL TABLE
(
	DATE DATETIME NULL,
	CIRCLE VARCHAR(MAX) NULL,
	CELLID INT NULL
)

--ZTE
INSERT INTO @ZEROERLANGS
	SELECT FROMDATE AS DATE,K2C.CIRCLE,COUNT(D.ID) AS TOTALCEELCOUNT,0 AS ZEROERLANGSCELLCOUNT 
FROM K2ZTEBBHDAILY D (NOLOCK)
	INNER JOIN K2COMBINEDMASTER K2C 
	ON D.COMBINEDMASTERID=K2C.ID 
	WHERE FROMDATE BETWEEN @STARTDATE AND @ENDDATE
	GROUP BY K2C.CIRCLEID,FROMDATE,K2C.CIRCLE

UPDATE Z
	SET Z.ZEROERLANGSCELLCOUNT = T.CELLCOUNT
FROM @ZEROERLANGS Z INNER JOIN
	(
		SELECT K2C.CIRCLE, COUNT(D.ID) AS CELLCOUNT
		FROM K2ZTEBBHDAILY D (NOLOCK)
		INNER JOIN K2COMBINEDMASTER K2C (NOLOCK)
		ON D.COMBINEDMASTERID=K2C.ID 
		WHERE FROMDATE BETWEEN @STARTDATE AND @ENDDATE and VOICEERLANGS = 0
		GROUP BY K2C.CIRCLEID,FROMDATE,K2C.CIRCLE
	) AS T
		ON Z.CIRCLE = T.CIRCLE

--HUAWEI
INSERT INTO @ZEROERLANGS
SELECT FROMDATE AS DATE,K2C.CIRCLE,COUNT(D.ID) AS TOTALCEELCOUNT,0 AS ZEROERLANGSCELLCOUNT 
FROM K2HUAWEIBBHDAILY D (NOLOCK)
INNER JOIN K2COMBINEDMASTER K2C (NOLOCK)
ON D.COMBINEDMASTERID=K2C.ID 
WHERE FROMDATE BETWEEN @STARTDATE AND @ENDDATE
GROUP BY K2C.CIRCLEID,FROMDATE,K2C.CIRCLE
order by K2C.CIRCLE

UPDATE Z
	SET Z.ZEROERLANGSCELLCOUNT = T.CELLCOUNT
FROM @ZEROERLANGS Z INNER JOIN
	(
		SELECT K2C.CIRCLE, COUNT(D.ID) AS CELLCOUNT
		FROM K2HUAWEIBBHDAILY D (NOLOCK)
		INNER JOIN K2COMBINEDMASTER K2C (NOLOCK)
		ON D.COMBINEDMASTERID=K2C.ID 
		WHERE FROMDATE BETWEEN @STARTDATE AND @ENDDATE and VOICEERLANGS = 0
		GROUP BY K2C.CIRCLEID,FROMDATE,K2C.CIRCLE
	) AS T
		ON Z.CIRCLE = T.CIRCLE




--NSN
INSERT INTO @ZEROERLANGS
SELECT FROMDATE AS DATE,K2C.CIRCLE,COUNT(D.ID) AS TOTALCEELCOUNT,0 AS ZEROERLANGSCELLCOUNT 
FROM K2NSNBBHDAILY D (NOLOCK)
INNER JOIN K2COMBINEDMASTER K2C (NOLOCK)
ON D.COMBINEDMASTERID=K2C.ID 
WHERE FROMDATE BETWEEN @STARTDATE AND @ENDDATE
GROUP BY K2C.CIRCLEID,FROMDATE,K2C.CIRCLE
order by K2C.CIRCLE

UPDATE Z
	SET Z.ZEROERLANGSCELLCOUNT = T.CELLCOUNT
FROM @ZEROERLANGS Z INNER JOIN
	(
		SELECT K2C.CIRCLE, COUNT(D.ID) AS CELLCOUNT
		FROM K2NSNBBHDAILY D (NOLOCK)
		INNER JOIN K2COMBINEDMASTER K2C (NOLOCK)
		ON D.COMBINEDMASTERID=K2C.ID 
		WHERE FROMDATE BETWEEN @STARTDATE AND @ENDDATE and VOICEERLANGS = 0
		GROUP BY K2C.CIRCLEID,FROMDATE,K2C.CIRCLE
	) AS T
		ON Z.CIRCLE = T.CIRCLE

SELECT * FROM @ZEROERLANGS ORDER BY CIRCLE;

INSERT INTO @BBHCELL
SELECT FROMDATE AS DATE,K2C.CIRCLE,K2C.CELLID FROM K2ZTEBBHDAILY D (NOLOCK)
INNER JOIN K2COMBINEDMASTER K2C 
ON D.COMBINEDMASTERID=K2C.ID 
WHERE FROMDATE BETWEEN @STARTDATE AND @ENDDATE AND VOICEERLANGS = 0

INSERT INTO @BBHCELL
SELECT FROMDATE AS DATE,K2C.CIRCLE,K2C.CELLID FROM K2HUAWEIBBHDAILY D (NOLOCK)
INNER JOIN K2COMBINEDMASTER K2C (NOLOCK)
ON D.COMBINEDMASTERID=K2C.ID 
WHERE FROMDATE BETWEEN @STARTDATE AND @ENDDATE AND VOICEERLANGS = 0

INSERT INTO @BBHCELL
SELECT FROMDATE AS DATE,K2C.CIRCLE,K2C.CELLID FROM K2NSNBBHDAILY D (NOLOCK)
INNER JOIN K2COMBINEDMASTER K2C (NOLOCK)
ON D.COMBINEDMASTERID=K2C.ID 
WHERE FROMDATE BETWEEN @STARTDATE AND @ENDDATE AND VOICEERLANGS = 0
order by K2C.CIRCLE

SELECT * FROM @BBHCELL ORDER BY DATE, CIRCLE;

I want to read the data of two different table that is select query of two different table in one datamanager .In my code while datareader.read(),i am reading the column of two different table which is wrong. is there any way to read the two different table column . .Any Idea ..
Please let me know.

I have try this :

while (dataReader.Read())
               {
                   nqi2GEntity = Kaizen2GEntityCreator.GetNqi2GEntity();
                   nqi2GEntity.Id = count++;
                   nqi2GEntity.Circle = dataReader[KaizenConstants.CIRCLE].ObjectToString();
                   nqi2GEntity.Date = dataReader[KaizenConstants.DATE].ObjectToDateTime();
                   nqi2GEntity.TotalCeelCount = dataReader[KaizenConstants.TOTALCEELCOUNT].ObjectToInt32();
                   nqi2GEntity.ZeroErlangsCellCount = dataReader[KaizenConstants.ZEROERLANGSCELLCOUNT].ObjectToInt32();
                   //// nqi2GList.Add(nqi2GEntity);
               }

               if (dataReader.NextResult())
               {
                   while (dataReader.Read())
                   {
                       nqi2GEntity = Kaizen2GEntityCreator.GetNqi2GEntity();
                       nqi2GEntity.Id = count++;
                       nqi2GEntity.Circle = dataReader[KaizenConstants.CIRCLE].ObjectToString();
                       nqi2GEntity.Date = dataReader[KaizenConstants.DATE].ObjectToDateTime();
                       nqi2GEntity.CellId = dataReader[KaizenConstants.CELLID].ObjectToInt32();
                       //// nqi2GList.Add(nqi2GEntity);
                   }
               }

               nqi2GList.Add(nqi2GEntity);

it cannot read the second table column in code .why is it so ??.please let me know.

Thanks in advance
Harshal

解决方案

You can call NextResult on your SqlDataReader to get the next table that was returned from the stored procedure.

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.nextresult(v=vs.110).aspx[^]

After your while loop on dataReader.Read() you can check if (dataReader.NextResult()) and then do another while dataReader.Read() for the second table.

这篇关于从存储过程中的一个Datamanager方法中读取两个不同表中的两个选择查询。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！