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:

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

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"); 

            DD_Module_ViewBrief.Items.Clear();
            command = new SqlCommand("Modules", myConnection);
            command.CommandType = CommandType.StoredProcedure;
            myConnection.Open();

            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)));
            }
            reader.Close();
            command.Dispose();
            myConnection.Close();
            myConnection.Dispose();
        }

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....

This article has been dead for over six months. Start a new discussion instead.