c# How to handle multiple MSSQL attach

socom1985
socom1985 used Ask the Experts™
on
two part question

I have a WCF webservice which attaches MSQL DB files (templates) to the server.

first question:
Does anybody know why this could be very slow on the server? when I run the service on my client machine  the attach is like 10 times faster.

second question:
I have problems with the timeout as when my service tries to handle multiple requests I get a timeout because apparently the sql server is busy with handling my previous request. What is the correct way to handle this?


 
//Creating connection
                    SqlConnection conn = new SqlConnection("Data Source=" + Properties.Settings.Default.MSSQLServerName + ";Integrated Security=SSPI");
                    SqlCommand cmd = new SqlCommand("", conn);

                    //Creating SQL command
                    cmd.CommandText = "exec sys.sp_attach_db " + name + " , '" + MDFpath + "','" + LDFpath + "'";


                    
                    //Open connection
                    conn.Open();

                    //Execute query
                    cmd.ExecuteNonQuery();

                    //Dispose of connection
                    cmd.Dispose();
                    conn.Dispose();

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
.NET Expert
Commented:
Use "Max Pool Size" in the connection string

Please Refer

http://www.connectionstrings.com/articles/show/all-sql-server-connection-string-keywords

Author

Commented:
I set Max Pool Size=200;Connect Timeout=30 but I still get timeouts.. when I attach the database with management studio it happens rather quickly..

Author

Commented:
I solved it with:
using (SqlConnection conn = new SqlConnection("Data Source=" + Properties.Settings.Default.MSSQLServerName + ";Integrated Security=SSPI;Max Pool Size=200;Connect Timeout=30;Async=true"))

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial