Click here to Skip to main content
11,432,427 members (70,938 online)
Rate this: bad
good
Please Sign up or sign in to vote.
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
Posted 20-Aug-14 4:01am
Edited 20-Aug-14 4:56am
v2

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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.
  Permalink  
Comments
R Harshal at 20-Aug-14 9:58am
   
Hi Ryan,
according to your suggestion i have modify the code but it cant work .it cannot read the second table column .Please Team let me know.

Thanks in Advance
Harshal
RyanDev at 20-Aug-14 9:59am
   
Sorry, I think amongst all the code I missed that you had tried that. Why can't it read the column? Do you get an error?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Advertise | Privacy | Mobile
Web01 | 2.8.150428.2 | Last Updated 20 Aug 2014
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100