locked
SqlDataReader inside a loop in vb.NET RRS feed

  • Question

  • User-1305530094 posted

    I m trying to insert/update multi-records and return the records after change in a table and I m using the following code,

    Using cmd As New SqlCommand("TblRegJoinDynHdlr", con)
        cmd.CommandType = CommandType.StoredProcedure
        Dim Data = PtRegData(0).PartnerData
        For i As Integer = 0 To Data.Count - 1
            cmd.Parameters.Clear()
            If (String.IsNullOrEmpty(Data(i).RecID)) Then
                cmd.Parameters.Add("@RecID", SqlDbType.Int).Value = DBNull.Value
            Else
                cmd.Parameters.Add("@RecID", SqlDbType.Int).Value = Convert.ToInt32(Data(i).RecID)
            End If
            If (String.IsNullOrEmpty(Data(i).PtFilenum)) Then
                cmd.Parameters.Add("@ptfilenum", SqlDbType.Int).Value = DBNull.Value
            Else
                cmd.Parameters.Add("@ptfilenum", SqlDbType.Int).Value = Convert.ToInt32(Data(i).PtFilenum)
            End If
            If (String.IsNullOrEmpty(Data(i).PrtFilenum)) Then
                cmd.Parameters.Add("@prfilenum", SqlDbType.Int).Value = DBNull.Value
            Else
                cmd.Parameters.Add("@prfilenum", SqlDbType.Int).Value = Convert.ToInt32(Data(i).PrtFilenum)
            End If
            cmd.Parameters.Add("@PrtStatus", SqlDbType.Int).Value = (Data(i).PrtStatus)
            con.Open()
            Using sdr As SqlDataReader = cmd.ExecuteReader()
                While sdr.Read()
                    Prtdata.Add(New PartnerData() With { _
                     .RecID = sdr("RecID").ToString(), _
                     .PrtFilenum = sdr("PrtFilenum").ToString(), _
                     .PtFilenum = sdr("PtFilenum").ToString(), _
                     .FullName = sdr("FullName").ToString(), _
                     .PrtStatus = sdr("PrtStatus").ToString(), _
                    .DOB = sdr("DOB").ToString() _
                    })
                End While
            End Using
            con.Close()
        Next
    
        con.Dispose()
    End Using

    My Problem If I leave the SqlDataReader inside the loop, all records are updated properly but I get the data as n x 2 and if I leave it outside the loop I get my data but records are not updated/inserted properly Any help please?

    Friday, June 2, 2017 3:48 AM

Answers

  • User-707554951 posted

    Hi Embryologist

    For your problem, I suggest you could  separate your operation to two steps.

    1. Do insert or update 

    2. Then select record from a table

     public object TblRegJoinUpd3(List<TblRegJoindata> Data)
            {
                List<PartnerData> Prtdata = new List<PartnerData>();
                string constr = ConfigurationManager.ConnectionStrings("ARTSQLConStrng").ConnectionString;
              
                    using (SqlConnection con = new SqlConnection(constr))
                    {
                    using (SqlCommand cmd = new SqlCommand("TblRegJoinDynHdlr", con))
                    {
                        for (int i = 0; i <= Data.Count - 1; i++)
                        {
                            cmd.CommandType = CommandType.StoredProcedure;
                            cmd.Parameters.Clear();
                            if ((string.IsNullOrEmpty(Data(i).RecID)))
                            {
                                cmd.Parameters.Add("@RecID", SqlDbType.Int).Value = DBNull.Value;
                            }
                            else
                            {
                                cmd.Parameters.Add("@RecID", SqlDbType.Int).Value = Convert.ToInt32(Data(i).RecID);
                            }
                            if ((string.IsNullOrEmpty(Data(i).ptfilenum)))
                            {
                                cmd.Parameters.Add("@ptfilenum", SqlDbType.Int).Value = DBNull.Value;
                            }
                            else
                            {
                                cmd.Parameters.Add("@ptfilenum", SqlDbType.Int).Value = Convert.ToInt32(Data(i).ptfilenum);
                            }
                            if ((string.IsNullOrEmpty(Data(i).prfilenum)))
                            {
                                cmd.Parameters.Add("@prfilenum", SqlDbType.Int).Value = DBNull.Value;
                            }
                            else
                            {
                                cmd.Parameters.Add("@prfilenum", SqlDbType.Int).Value = Convert.ToInt32(Data(i).prfilenum);
                            }
                            cmd.Parameters.Add("@PrtStatus", SqlDbType.Int).Value = (Data(i).PrtStatus);
                            con.Open();
                            // 1.add the following code
                             int roweffected = cmd.ExecuteNonQuery();
                        }
                    //2.Then do select from specified tabel
                        cmd.CommandText = "select * from TblRegJoin";
                        cmd.CommandType = CommandType.Text;
                        using (SqlDataReader sdr = cmd.ExecuteReader())
                        {
                            while (sdr.Read())
                            {
                                Prtdata.Add(new PartnerData
                                {
                                    RecID = sdr("RecID").ToString(),
                                    PrtFilenum = sdr("PrtFilenum").ToString(),
                                    PtFilenum = sdr("PtFilenum").ToString(),
                                    FullName = sdr("FullName").ToString(),
                                    PrtStatus = sdr("PrtStatus").ToString(),
                                    DOB = sdr("DOB").ToString()
                                });
                            }
                        }
                    }                                   
                            con.Close();
                        }
                return JsonConvert.SerializeObject(Prtdata);
                //Return Nothing                
            }

    Best regard

    Cathy

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, June 5, 2017 6:51 AM
  • User-707554951 posted

    Hi Embryologist

    I did it in this way, i was just trying to minimize the code line,

    The number of code line didn't make difference on the performance.

    It is related to Improve ADO.NET Performance:

    For how to do that, please refer to the following links:

    https://msdn.microsoft.com/en-us/library/ff647768.aspx

    https://msdn.microsoft.com/en-us/library/ms971481.aspx

    Best regard

    Cathy

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, June 5, 2017 9:27 AM

All replies

  • User753101303 posted

    Hi,

    Are you sure the reader always return exactly one row ? Make sure also that the same row is not found twice in your source data.

    Unrelated but rather than removing and adding again parameters you could add parameters before the loop and assign a value to each parameter inside the loop.

    Friday, June 2, 2017 3:18 PM
  • User-1716253493 posted

    Recheck your sp

    Friday, June 2, 2017 3:31 PM
  • User-1305530094 posted
    if I m not mistaken, the loop is operating on single row level. My logic, which is not the case here, says that with every loop a record is inserted/updated and returned using select statement which also returns values per record and hence the list is populated! But I m mistaken
    How can I assign the values before the loop as you are suggesting?
    Friday, June 2, 2017 3:33 PM
  • User-1305530094 posted
    I m away from my pc now, I will be posting the sp later today
    Thanks
    Friday, June 2, 2017 3:35 PM
  • User-1716253493 posted

    Try move Using cmd inside

        Dim Data = PtRegData(0).PartnerData
        For i As Integer = 0 To Data.Count - 1
          'Using cmd 
    
          'End Using
        Next

    Friday, June 2, 2017 3:52 PM
  • User-1305530094 posted

    oned_gk

    Try move Using cmd inside

        Dim Data = PtRegData(0).PartnerData
        For i As Integer = 0 To Data.Count - 1
          'Using cmd 
    
          'End Using
        Next

    I moved my cmd inside and it did not work

        Public Class PartnerData
            Public Property RecID As Integer
            Public Property PrtStatus As Integer
            Public Property PtFilenum As Integer
            Public Property PrtFilenum As Integer
            Public Property FullName As String
            Public Property DOB As String
        End Class
    
       <WebMethod> _
        Public Function TblRegJoinUpd3(ByVal Data As List(Of TblRegJoindata))
            Dim Prtdata As New List(Of PartnerData)()
            Dim constr As String = ConfigurationManager.ConnectionStrings("ARTSQLConStrng").ConnectionString
            For i As Integer = 0 To Data.Count - 1
                Using con As New SqlConnection(constr)
                    Using cmd As New SqlCommand("TblRegJoinDynHdlr", con)
                        cmd.CommandType = CommandType.StoredProcedure
                        cmd.Parameters.Clear()
                        If (String.IsNullOrEmpty(Data(i).RecID)) Then
                            cmd.Parameters.Add("@RecID", SqlDbType.Int).Value = DBNull.Value
                        Else
                            cmd.Parameters.Add("@RecID", SqlDbType.Int).Value = Convert.ToInt32(Data(i).RecID)
                        End If
                        If (String.IsNullOrEmpty(Data(i).ptfilenum)) Then
                            cmd.Parameters.Add("@ptfilenum", SqlDbType.Int).Value = DBNull.Value
                        Else
                            cmd.Parameters.Add("@ptfilenum", SqlDbType.Int).Value = Convert.ToInt32(Data(i).ptfilenum)
                        End If
                        If (String.IsNullOrEmpty(Data(i).prfilenum)) Then
                            cmd.Parameters.Add("@prfilenum", SqlDbType.Int).Value = DBNull.Value
                        Else
                            cmd.Parameters.Add("@prfilenum", SqlDbType.Int).Value = Convert.ToInt32(Data(i).prfilenum)
                        End If
                        cmd.Parameters.Add("@PrtStatus", SqlDbType.Int).Value = (Data(i).PrtStatus)
                        con.Open()
                        Using sdr As SqlDataReader = cmd.ExecuteReader()
                            While sdr.Read()
                                Prtdata.Add(New PartnerData() With { _
                                 .RecID = sdr("RecID").ToString(), _
                                 .PrtFilenum = sdr("PrtFilenum").ToString(), _
                                 .PtFilenum = sdr("PtFilenum").ToString(), _
                                 .FullName = sdr("FullName").ToString(), _
                                 .PrtStatus = sdr("PrtStatus").ToString(), _
                                .DOB = sdr("DOB").ToString() _
                                })
                            End While
                        End Using
                        con.Close()
                    End Using
                End Using
            Next
            Return JsonConvert.SerializeObject(Prtdata)
            'Return Nothing
        End Function
    
    

    MY SP is 

    ALTER PROCEDURE [dbo].[TblRegJoinDynHdlr]
    	-- Add the parameters for the stored procedure here
    	(
    	@RecID int,
    	@PrtStatus int,
    	@ptfilenum int,
    	@prfilenum int
    	)
    AS
    BEGIN
    DECLARE @validRecID int
    SET @validRecID = (SELECT COUNT(*) as numRows FROM TblRegjoin WHERE RecID=@RecID)
    	SET NOCOUNT ON;
    	IF (@validRecID = 0)
    		BEGIN
    		SET NOCOUNT ON;
    		 INSERT INTO TblRegjoin(PtFilenum, PrtFilenum) Values (@ptfilenum, @prfilenum)
    			SELECT TblRegjoin.RecID, TblRegjoin.PtFilenum, TblRegjoin.PrtFilenum, CAST(TblRegjoin.PrtStatus As int) AS PrtStatus , TblReg.FullName, CONVERT(VARCHAR(24), TblReg.DOB, 113) AS DOB
    FROM            TblRegjoin INNER JOIN TblReg ON TblRegjoin.PrtFilenum = TblReg.Filenum WHERE (TblRegjoin.PtFilenum = @ptfilenum)
    		END
    	ELSE IF (@validRecID>0)
    		BEGIN
    		SET NOCOUNT ON;
    		UPDATE TblRegjoin 
    		SET  PrtStatus=@PrtStatus
    		WHERE RecID= @RecID 
    					SELECT TblRegjoin.RecID, TblRegjoin.PtFilenum, TblRegjoin.PrtFilenum, CAST(TblRegjoin.PrtStatus As int) AS PrtStatus , TblReg.FullName, CONVERT(VARCHAR(24), TblReg.DOB, 113) AS DOB
    FROM            TblRegjoin INNER JOIN TblReg ON TblRegjoin.PrtFilenum = TblReg.Filenum WHERE (TblRegjoin.PtFilenum = @ptfilenum)
    		END
    
    END

    PS. Without the loop (for single record operation) the code works fine, I update/insert and get the corresponding values back

    Saturday, June 3, 2017 1:15 AM
  • User753101303 posted

    Have you tired to see if the same item is found twice in your source list ? Also that your SP always return exactly one row ?

    IMO before trying to change your code, it would be best to understand first what is the exact problem. For now my understanding is that you find the same item twice in your final Prtdata list.

    Saturday, June 3, 2017 2:41 PM
  • User-1716253493 posted

    Can you recheck Data.Count?

    Saturday, June 3, 2017 3:49 PM
  • User-1305530094 posted

    I believe both of you are right,

    I m trying to make my code to dynamic, for insert/update/delete. I am working with datatables. after each process I clear the table and repopulate it. 

    I checked my SP and I changed my query select to get per updated record and its working. I m still having problem with insert and delete. removing and inserting records will not play as nice with the list. 

    My resolution,

    I have a read function already. So I will not be returning records anymore using "reader",  

    cmd.ExecuteNonQuery()

    and then returning data using the reading function to populate the table again.

    I have no idea how this can impact the performance and I would appreciate it if someone can assist me to find a way in evaluating the whole procedure performance

    Sunday, June 4, 2017 4:22 AM
  • User-707554951 posted

    Hi Embryologist

    For your problem, I suggest you could  separate your operation to two steps.

    1. Do insert or update 

    2. Then select record from a table

     public object TblRegJoinUpd3(List<TblRegJoindata> Data)
            {
                List<PartnerData> Prtdata = new List<PartnerData>();
                string constr = ConfigurationManager.ConnectionStrings("ARTSQLConStrng").ConnectionString;
              
                    using (SqlConnection con = new SqlConnection(constr))
                    {
                    using (SqlCommand cmd = new SqlCommand("TblRegJoinDynHdlr", con))
                    {
                        for (int i = 0; i <= Data.Count - 1; i++)
                        {
                            cmd.CommandType = CommandType.StoredProcedure;
                            cmd.Parameters.Clear();
                            if ((string.IsNullOrEmpty(Data(i).RecID)))
                            {
                                cmd.Parameters.Add("@RecID", SqlDbType.Int).Value = DBNull.Value;
                            }
                            else
                            {
                                cmd.Parameters.Add("@RecID", SqlDbType.Int).Value = Convert.ToInt32(Data(i).RecID);
                            }
                            if ((string.IsNullOrEmpty(Data(i).ptfilenum)))
                            {
                                cmd.Parameters.Add("@ptfilenum", SqlDbType.Int).Value = DBNull.Value;
                            }
                            else
                            {
                                cmd.Parameters.Add("@ptfilenum", SqlDbType.Int).Value = Convert.ToInt32(Data(i).ptfilenum);
                            }
                            if ((string.IsNullOrEmpty(Data(i).prfilenum)))
                            {
                                cmd.Parameters.Add("@prfilenum", SqlDbType.Int).Value = DBNull.Value;
                            }
                            else
                            {
                                cmd.Parameters.Add("@prfilenum", SqlDbType.Int).Value = Convert.ToInt32(Data(i).prfilenum);
                            }
                            cmd.Parameters.Add("@PrtStatus", SqlDbType.Int).Value = (Data(i).PrtStatus);
                            con.Open();
                            // 1.add the following code
                             int roweffected = cmd.ExecuteNonQuery();
                        }
                    //2.Then do select from specified tabel
                        cmd.CommandText = "select * from TblRegJoin";
                        cmd.CommandType = CommandType.Text;
                        using (SqlDataReader sdr = cmd.ExecuteReader())
                        {
                            while (sdr.Read())
                            {
                                Prtdata.Add(new PartnerData
                                {
                                    RecID = sdr("RecID").ToString(),
                                    PrtFilenum = sdr("PrtFilenum").ToString(),
                                    PtFilenum = sdr("PtFilenum").ToString(),
                                    FullName = sdr("FullName").ToString(),
                                    PrtStatus = sdr("PrtStatus").ToString(),
                                    DOB = sdr("DOB").ToString()
                                });
                            }
                        }
                    }                                   
                            con.Close();
                        }
                return JsonConvert.SerializeObject(Prtdata);
                //Return Nothing                
            }

    Best regard

    Cathy

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, June 5, 2017 6:51 AM
  • User-1305530094 posted

    I did it in this way, i was just trying to minimize the code line,

    is there a way to measure the code performance?

    Monday, June 5, 2017 6:54 AM
  • User-707554951 posted

    Hi Embryologist

    I did it in this way, i was just trying to minimize the code line,

    The number of code line didn't make difference on the performance.

    It is related to Improve ADO.NET Performance:

    For how to do that, please refer to the following links:

    https://msdn.microsoft.com/en-us/library/ff647768.aspx

    https://msdn.microsoft.com/en-us/library/ms971481.aspx

    Best regard

    Cathy

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, June 5, 2017 9:27 AM