Return values to c# via stored procedure

Question

I want to return few columns and rows (Temp table) to C# code. This is my stored procedure.

CREATE PROC [dbo].[GetInvoice]    
(              
 @ClientId as INT ,
 @MatterId as INT,
 @DateFrom datetime,
 @DateTo datetime
)              
AS              
BEGIN              
  -- SET NOCOUNT ON added to prevent extra result sets from             
  -- interfering with SELECT statements.              
  SET NOCOUNT ON;              

  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED  

  select *,'Expense' as TblName 
  into #tempExpense 
  from
      (select e.ClientId
              , CAST(e.DateAdded AS DATE) as [Date]
              , e.MatterId
              , e.TotalAmount
              , e.UserId
              , e.Id As SourceRowID 
       from Expense e) AS E

  select *, 'Times' as TblName  
  into #tempTimes 
  from
      (select t.clientid
              , CAST(t.[Date] AS DATE) AS [Date]
              , t.MatterId
              , t.TotalAmount
              , t.UserId
              , t.TimeID as SourceRowID
       from Times t) AS T

  select A.* 
  into #tempResult 
  from
      (select * from #tempExpense 
       UNION ALL
       select * from #tempTimes) AS A

  SELECT DENSE_Rank() OVER (ORDER BY ClientID, Date, MatterID) As Rank,*  
  INTO #tempResult_Final
  FROM #tempResult

  UPDATE Expense
  SET Expense_Rank = R.[Rank]
  FROM Expense E
  INNER JOIN #tempResult_Final R ON E.ID = R.SourceRowID AND TblName = 'Expense'

  UPDATE Times
  SET Times_Rank = R.[Rank]
  FROM Times E
  INNER JOIN #tempResult_Final R ON E.TimeID = R.SourceRowID AND TblName = 'Times'

  select 
     tr.ClientId, m.MatterName, c.Name as ClientName, 
     tr.[Date] as DateInvoice, tr.MatterId, tr.UserId,
     u.FirstName + ' ' + u.LastName as UserFullName, 
     Sum(tr.TotalAmount) as Total, 
     MAX(tr.Rank) AS Rank
  from 
     #tempResult_Final tr
  inner join 
     Matters m ON m.MatterID = tr.MatterId
  inner join 
     Client c ON c.ClientId = tr.ClientId
  inner join 
     [User] u ON u.UserId = tr.UserId 
  where 
      tr.ClientId = (Case when @ClientId = 0 then tr.ClientId else @ClientId end )
      and tr.MatterId = (Case when @MatterId = 0 then tr.MatterId else @MatterId end )
      and tr.[Date] between isnull(@DateFrom,cast('1900/01/01' as date))  
      and isnull(@Dateto,cast('2999/01/01' as date))
  group by 
      tr.clientid, m.MatterName, c.Name, tr.[Date], tr.MatterId, 
      tr.UserId , u.FirstName, u.LastName
  order by 
      [Date] 
END 
GO

Please help.

Second hit on google search of "Return values to c# via stored procedure": http://stackoverflow.com/questions/6210027/c-sharp-calling-sql-server-stored-procedure-with-return-value — Steven Wexler, Jul 13 '14 at 13:43

score 0 · Answer 1 · answered Jul 13 '14 at 13:50

Use Reference guide and google. This code may help for your problem:

SqlConnection sqlConnection1 = new SqlConnection("Your Connection String");
SqlCommand cmd = new SqlCommand();
SqlDataReader reader;

cmd.CommandText = "StoredProcedureName";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = sqlConnection1;


//ADD parameters and return value

  cmd.Parameters.AddWithValue("par1Name", "par1Value");

    var returnParameterVariable = cmd.Parameters.Add("@ReturnVal", SqlDbType.NVarChar);
    returnParameterVariable .Direction = ParameterDirection.ReturnValue;

sqlConnection1.Open();

reader = cmd.ExecuteReader();

sqlConnection1.Close();

score 0 · Answer 2 · answered Jul 13 '14 at 13:51

From How to: Execute a Stored Procedure that Returns Rows:

Add the following code to a method that you want to execute the code from. You return rows by calling the ExecuteReader method of the command (for example, ExecuteReader). The data is returned in a DataReader. For more information on accessing the data in a DataReader, see Retrieving Data Using a DataReader.

SqlConnection sqlConnection1 = new SqlConnection("Your Connection String");
SqlCommand cmd = new SqlCommand();
SqlDataReader reader;

cmd.CommandText = "GetInvoice";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = sqlConnection1;

sqlConnection1.Open();

reader = cmd.ExecuteReader();
// Data is accessible through the DataReader object here.

sqlConnection1.Close();

I'm able to receive values at C#. But my SP doesn't return any value. — Laxmi Parab, Jul 13 '14 at 14:11
After executing my SP @ sql gives following output but doen't returns any values at c#. ClientID Matter Name UserName Invoice date matterID UserID Total Rank 79 Matter chetana.. 2014-07-12 36 18 42.00 2 79 Matter 467 chetana.. 2014-07-12 38 18 225.00 3 Return Value 0 — Laxmi Parab, Jul 13 '14 at 14:30

Return values to c# via stored procedure

2 Answers2