call a stored procedure from C#+VS 2008 window application

Posted on 2011-10-21
Last Modified: 2012-05-12
hi all,
i have a lot experience in C# and i used the stored procedure a lot of time in my projects.
but i have a problem that when i call the stored procedure from my C# application takes a lot of time about 3 minutes although when i called from the sql server it self it is takes 2 or 3 seconds maximum.

NB: the sql server is installed locally and i run the executable file  also on the same computer.

Question by:MKItani
    LVL 26

    Expert Comment

    by:Anurag Thakur
    I have noticed lag in executing procedures through applications but not too much lag
    try to put some timestamps in the procedure
    one time stamp at the start and one at the end (first line and last line of the procedure) and see if its the execution thats talking time or getting hold of the connection is talking time
    LVL 40

    Expert Comment

    by:Jacques Bourgeois (James Burger)
    There might be a difference between calling the procedure from your code vs inside of SQL Server, but not that much.

    Could you show us the C# code that you use to call the procedure. Is suspect that there might be something wrong in there.
    LVL 30

    Expert Comment

    What is the stored procedure doing? How much data does it need to send back to the calling application? What is receiving this data in you code.. dataset/datareader/entity framework/etc/etc? Sometimes, the delay is actually in sending back the results to the calling application. Investigate and perhaps play around with the Packet Size in your connection string.
    LVL 17

    Accepted Solution

    Hello, please use the code bellow to see how your SP performs in two different scenarios:
    string myConnectionString = null;
    // Please uncomment to choose the correct connection string, and modify with the correct values.
    // Standard Security (user, password)
    //myConnectionString = "Data Source=myServerAddress; Initial Catalog=myDataBase; User Id=myUsername; Password=myPassword;";
    // Trusted Connection
    //myConnectionString = "Data Source=myServerAddress; Initial Catalog=myDataBase; Integrated Security=SSPI;";
    if (string.IsNullOrEmpty(myConnectionString))
        throw new Exception("Please choose a correct connection string for your environment.");
    using (System.Data.SqlClient.SqlConnection myConnection = new System.Data.SqlClient.SqlConnection(myConnectionString))
        System.Data.SqlClient.SqlCommand myCommand = new System.Data.SqlClient.SqlCommand();
        myCommand.Connection = myConnection;
        myCommand.CommandTimeout = 0;
        myCommand.CommandType = System.Data.CommandType.StoredProcedure;
        // Change it with your SP name.
        myCommand.CommandText = "MyStoreProcedureName";
        // Add parameters if required (examples).
        //myCommand.Parameters.Add("Param1", System.Data.SqlDbType.Int).Value = 123;
        //myCommand.Parameters.Add("Param2", System.Data.SqlDbType.VarChar, 50).Value = "123";
        System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();
        System.Diagnostics.Debug.WriteLine("Time required to complete ExecuteNonQuery: " + sw.Elapsed.ToString());
        using (System.Data.SqlClient.SqlDataReader dr = myCommand.ExecuteReader())
            while (dr.Read()) { }
        System.Diagnostics.Debug.WriteLine("Time required to complete ExecuteReader: " + sw.Elapsed.ToString());

    Open in new window

    After completed, copy and post here the values displayed on your VS Debug Output window.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Suggested Solutions

    Article by: Ivo
    Anonymous Types in C# by Ivo Stoykov Anonymous Types are useful when  we do not need to follow usual work-flow -- creating object of some type, assign some read-only values and then doing something with them. Instead we can encapsulate this read…
    Introduction Although it is an old technology, serial ports are still being used by many hardware manufacturers. If you develop applications in C#, Microsoft .NET framework has SerialPort class to communicate with the serial ports.  I needed to…
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now