Hi, I have an SQL Server 2005 database set up and I have quite a few stored procedures that I am using to query the database. Basically what I am doing is gathering data from a user on a web form and then processing this data through stored procedures and c# code to upload and retrieve data from the database.

At the beginning my project was running rather efficiently :) however now that it has become slightly larger it has slowed down a great deal. I have been reading threads that say to put the connection string into the web.config file. However, I am not sure how I should go about coding (in terms of opening and closing the connection)

For example, in my web.config file I have the following:

        <add name="AMIS_DBconnection"
             connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Assignment_Submissions.mdf;Integrated Security=True;User Instance=True"/>

and just to illustrate how I am using my code in the code behind pages (with lots more stored procedures):

public void getModule()
            SqlConnection myConnection = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\Assignment_Submissions.mdf;Integrated Security=True;User Instance=True"); 

            command = new SqlCommand("Modules", myConnection);
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Add(new SqlParameter("@CourseCode", SqlDbType.NVarChar, 50, "CourseCode"));
            command.Parameters["@CourseCode"].Value = DD_Course_ViewBrief.SelectedValue;

            reader = command.ExecuteReader();   //CommandBehavior.CloseConnection

            while (reader.Read())
                DD_Module_ViewBrief.Items.Add(new ListItem(reader.GetString(0)));

I am creating a new connection string each time I need to connect to the database to get data but I am also closing and disposing of it too. (Should this not mean that my website should run just as well as it did in the beginning)

Please help :)

Personally, I like to load my results into a table, then from the table do whatever (for instance, add a list item.)

I don't know if it's faster or better, but my stuff always runs pretty quick!

Also, it could just be your SQL server....