Click here to Skip to main content
15,868,080 members
Home / Discussions / C#
   

C#

 
GeneralRe: Decoding GBK, .NET 6 edition Pin
harold aptroot9-Aug-22 4:44
harold aptroot9-Aug-22 4:44 
QuestionHow to close FileStream without causing an error in PdfViewer Pin
Code4Ever7-Aug-22 20:02
Code4Ever7-Aug-22 20:02 
AnswerRe: How to close FileStream without causing an error in PdfViewer Pin
OriginalGriff7-Aug-22 20:25
mveOriginalGriff7-Aug-22 20:25 
GeneralRe: How to close FileStream without causing an error in PdfViewer Pin
Code4Ever7-Aug-22 20:38
Code4Ever7-Aug-22 20:38 
GeneralRe: How to close FileStream without causing an error in PdfViewer Pin
OriginalGriff7-Aug-22 21:16
mveOriginalGriff7-Aug-22 21:16 
GeneralRe: How to close FileStream without causing an error in PdfViewer Pin
OriginalGriff7-Aug-22 21:59
mveOriginalGriff7-Aug-22 21:59 
AnswerRe: How to close FileStream without causing an error in PdfViewer Pin
Richard Deeming8-Aug-22 0:08
mveRichard Deeming8-Aug-22 0:08 
QuestionFacing problem when calling store procedure from c# code Pin
Mou_kol2-Aug-22 7:52
Mou_kol2-Aug-22 7:52 
I have store procedure which insert and update data and at the end it return multiple data.
i am using SQL helper utility class to call store procedure. i use

C#
ds = await SqlHelper.ExecuteDatasetAsync(ConnectionManager.GetConnectionString(), CommandType.StoredProcedure, "USP_InsertBMAsLineItem", spParameter);


execution stuck at this line. i am not able to figure out where i made the mistake. when i execute SP from SSMS then everything goes fine. please see my store procedure and c# code. tell me where i made the mistake.

SQL
ALTER PROC USP_InsertBMAsLineItem
 (
     @TickerID VARCHAR(20),
     @CommaSeparatedItems VARCHAR(MAX),
     @UserID VARCHAR(20)
 )
 AS
 BEGIN
     Declare @Start INT, @Count INT,@MaxOrder INT
     SET @Start=1
     SET @Count=1
     SET @MaxOrder=0
    
     BEGIN TRY
     BEGIN TRAN
     DROP TABLE IF EXISTS #Tmpdata
    
     CREATE TABLE #Tmpdata
     (
         ID  INT Identity,
         LineItem VARCHAR(MAX)
     )
    
     INSERT INTO #Tmpdata(LineItem) 
     (
         SELECT value as LineItem
         FROM STRING_SPLIT(@CommaSeparatedItems, ',')
     ) 
    
        
    
     MERGE INTO TblLineItemTemplate Trg
     USING
     (
         SELECT value as LineItem
         FROM STRING_SPLIT(@CommaSeparatedItems, ',')
     ) AS Src
     ON UPPER(TRIM(Trg.LineItem)) = UPPER(TRIM(Src.LineItem))
        AND Trg.TickerID = @TickerID
     WHEN MATCHED THEN
         UPDATE SET Trg.Action = 'U', ModifiedBy=@UserID
     WHEN NOT MATCHED THEN
         INSERT
         (
             TickerID,
             LineItem,
             Action,
             InsertedOn,
             InsertedBy
         )
         VALUES
         (TRIM(@TickerID), TRIM(Src.LineItem), 'I', GETDATE(),@UserID);
    
        
     SELECT @Start=MIN(ID) FROM #Tmpdata
     SELECT @Count=MAX(ID) FROM #Tmpdata
    
     WHILE (@Start<=@Count)
     BEGIN
         IF NOT EXISTS(SELECT * FROM tblSectionLineItemTemplate WHERE TickerID=@TickerID
             AND SectionID IN (SELECT SectionID FROM tblSectionTemplate WHERE TickerID=@TickerID AND Section='Model Output' AND Action<>'D')
             AND LineItemID IN (SELECT LineItemId FROM TblLineItemTemplate WHERE TickerID=@TickerID 
             AND LineItem IN (
                     SELECT LineItem FROM #Tmpdata WHERE ID=@Start           
             )))
         BEGIN
             SELECT @MaxOrder=MAX(ISNULL(OrderID,0))+1 FROM tblSectionLineItemTemplate WHERE TickerID=@TickerID
    
             INSERT INTO tblSectionLineItemTemplate
             (
                 TickerID,
                 SectionID,
                 LineItemID,
                 OrderID,
                 InsertedOn,
                 UserID
             )
             VALUES
             (
                 @TickerID,
                 (SELECT SectionID FROM tblSectionTemplate WHERE TickerID=@TickerID AND Section='Model Output'  AND Action<>'D'),
                 (SELECT LineItemId FROM TblLineItemTemplate WHERE TickerID=@TickerID AND LineItem IN (SELECT LineItem FROM #Tmpdata WHERE ID=@Start)),
                 @MaxOrder,
                 GETDate(),
                 @UserID
             )
         END
         SET @Start=@Start+1
    
     END
        
    
     SELECT L.LineItemId,L.LineItem from TblLineItemTemplate L INNER JOIN 
     (
         SELECT value as LineItem
         FROM STRING_SPLIT(@CommaSeparatedItems, ',')
     ) V
     ON UPPER(TRIM(L.LineItem))=UPPER(TRIM(V.LineItem)) AND L.TickerID=@TickerID
    
    
     DROP TABLE IF EXISTS #Tmpdata
     COMMIT TRAN
 END TRY
 BEGIN CATCH  
     ROLLBACK TRAN  
      
     DECLARE @ErrorMessage NVARCHAR(4000);  
     DECLARE @ErrorSeverity INT;  
     DECLARE @ErrorState INT;  
      
     SELECT @ErrorMessage = ERROR_MESSAGE(),  
            @ErrorSeverity = ERROR_SEVERITY(),  
            @ErrorState = ERROR_STATE();  
      
     --SET @Message = @ErrorMessage  
      
     RAISERROR(   @ErrorMessage,  -- Message text.                        
                  @ErrorSeverity, -- Severity.                        
                  @ErrorState     -- State.                        
              );  
      
 END CATCH  
 END


My C# code which would call SP

C#
public async Task<ResponseContextDto> ConvertToPercentLineItem(string tickerid, string items)
             {
                 DataSet ds = new DataSet();
                 DataTable dt=new DataTable();
                 string userid = GlobalVariable.strLoginID;
                 ResponseContextDto oResponseContext = new ResponseContextDto();
                 string? message = "";
        
                 try
                 {
                     #region db operation start
                     SqlParameter[] spParameter = new SqlParameter[3];
        
                     spParameter[0] = new SqlParameter("@TickerID", SqlDbType.VarChar);
                     spParameter[0].Direction = ParameterDirection.Input;
                     spParameter[0].Value = tickerid;
        
                     spParameter[1] = new SqlParameter("@CommaSeparatedItems", SqlDbType.VarChar);
                     spParameter[1].Direction = ParameterDirection.Input;
                     spParameter[1].Value = items;
        
                     spParameter[2] = new SqlParameter("@UserID", SqlDbType.VarChar);
                     spParameter[2].Direction = ParameterDirection.Input;
                     spParameter[2].Value = userid;
        
                     ds = await SqlHelper.ExecuteDatasetAsync(ConnectionManager.GetConnectionString(), CommandType.StoredProcedure, "USP_InsertBMAsLineItem", spParameter);
        
                     oResponseContext.IsSuccess = Status.Success;
                     oResponseContext.Message = Status.Message(Status.Success);
                     if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                     {
                         dt = ds.Tables[0];
                         oResponseContext.ResponseData = dt;
                     }
                     else
                     {
                         oResponseContext.ResponseData = null;
                     }
        
                     #endregion
                 }
                 catch (Exception ex)
                 {
                     oResponseContext.IsSuccess = Status.Fail;
                     oResponseContext.Message = "Error " + ex.Message;
                     oResponseContext.ResponseData = null;
                 }
        
                 return oResponseContext;
             }


I also tried this way to capture return tabular data from SP into c# datatable but still no luck.

C#
string userid = GlobalVariable.strLoginID;
             ResponseContextDto oResponseContext = new ResponseContextDto();
             DataTable table = new DataTable();
             using (var con = new SqlConnection(ConnectionManager.GetConnectionString()))
             using (var cmd = new SqlCommand("USP_InsertBMAsLineItem", con))
             using (var da = new SqlDataAdapter(cmd))
             {
                 cmd.CommandType = CommandType.StoredProcedure;
                 cmd.Parameters.Add(new SqlParameter("@TickerID", tickerid));
                 cmd.Parameters.Add(new SqlParameter("@CommaSeparatedItems", items));
                 cmd.Parameters.Add(new SqlParameter("@UserID", userid));
                 da.Fill(table);
             }


please guide me what to change in c# code or in SP code.

Thanks
AnswerRe: Facing problem when calling store procedure from c# code Pin
Richard Deeming2-Aug-22 21:21
mveRichard Deeming2-Aug-22 21:21 
GeneralRe: Facing problem when calling store procedure from c# code Pin
Mou_kol3-Aug-22 3:27
Mou_kol3-Aug-22 3:27 
GeneralRe: Facing problem when calling store procedure from c# code Pin
Victor Nijegorodov3-Aug-22 5:33
Victor Nijegorodov3-Aug-22 5:33 
QuestionRe: Facing problem when calling store procedure from c# code Pin
thatraja2-Aug-22 21:36
professionalthatraja2-Aug-22 21:36 
AnswerRe: Facing problem when calling store procedure from c# code Pin
Gerry Schmitz3-Aug-22 5:58
mveGerry Schmitz3-Aug-22 5:58 
QuestionHow to dispose Interaction.Behaviors in WPF Pin
Code4Ever2-Aug-22 5:45
Code4Ever2-Aug-22 5:45 
AnswerRe: How to dispose Interaction.Behaviors in WPF Pin
Gerry Schmitz2-Aug-22 6:21
mveGerry Schmitz2-Aug-22 6:21 
QuestionHow to set null value to a FOREIGN KEY using EF Core Pin
Code4Ever31-Jul-22 20:38
Code4Ever31-Jul-22 20:38 
AnswerRe: How to set null value to a FOREIGN KEY using EF Core Pin
Richard MacCutchan31-Jul-22 21:37
mveRichard MacCutchan31-Jul-22 21:37 
GeneralRe: How to set null value to a FOREIGN KEY using EF Core Pin
Code4Ever31-Jul-22 21:49
Code4Ever31-Jul-22 21:49 
GeneralRe: How to set null value to a FOREIGN KEY using EF Core Pin
Richard MacCutchan31-Jul-22 22:06
mveRichard MacCutchan31-Jul-22 22:06 
GeneralRe: How to set null value to a FOREIGN KEY using EF Core Pin
Code4Ever31-Jul-22 22:17
Code4Ever31-Jul-22 22:17 
GeneralRe: How to set null value to a FOREIGN KEY using EF Core Pin
Dave Kreskowiak1-Aug-22 4:23
mveDave Kreskowiak1-Aug-22 4:23 
AnswerRe: How to set null value to a FOREIGN KEY using EF Core Pin
Richard Deeming1-Aug-22 0:25
mveRichard Deeming1-Aug-22 0:25 
AnswerRe: How to set null value to a FOREIGN KEY using EF Core Pin
Gerry Schmitz1-Aug-22 7:02
mveGerry Schmitz1-Aug-22 7:02 
QuestionMocking HttpContext Pin
SreeRama Govind31-Jul-22 17:26
SreeRama Govind31-Jul-22 17:26 
AnswerRe: Mocking HttpContext Pin
Pete O'Hanlon1-Aug-22 23:26
subeditorPete O'Hanlon1-Aug-22 23:26 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.