none
Problem with SqlDataReader [Connection Open and Close ISSUE] RRS feed

  • Question

  •  

    Hi all,

            I am using a SqlDataReader to read Database Values. I have been doing this as follows

    SqlConnection cn=new SqlConnection("connectionString") 

    for(int i=0; i<myList.Count;i++)       //myList is a list of integers  

    {
         foreach(int ID in IDs)        //IDs is a list of Ids

    {

         cn.Open();

    //Here i am using a SqlDataReader ro read database values  and i am appending these values to a string variable.

         cn.Close()

    myString=myString +””READER VALUE”;



    }

     

    The above code work fine has no errors or any display problems but the processing is getting really slow if the number of elements in "myList" and "IDs" increase.

    For example : say there are 100 integers in myList

                      And 10 IDs in "IDS" list

    then i am opening and closing the SqlConnection 100x10 times which is making this process really slow.

    CAN ANY ONE HELP ME OUT IN THIS ASPECT.

    MNK.
    Saturday, February 21, 2009 4:21 PM

All replies

  • Hi Dear Ali.
    Generally you must open your connection once. Change your code as following:


    SqlConnection cn=new SqlConnection("connectionString") 

    cn.Open();


    for(int i=0; i<myList.Count;i++)       //myList is a list of integers  

    {
         foreach(int ID in IDs)        //IDs is a list of Ids

    {


    //Here i am using a SqlDataReader ro read database values  and i am appending these values to a string variable.

    myString=myString +””READER VALUE”;

         cn.Close()

    }





    Software Developer on .NET
    Sunday, February 22, 2009 12:01 PM