read two select query from two different table in one Datamanager method from Stored Procedure.

See more:

C#

SQL

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