How to handle SQL Query CommandTimeout in C# 2.0

Question

I have got below code in c#.

SqlConnection conn = new SqlConnection("Data Source=MANOJ-PC\\SQLEXPRESS;Initial Catalog=master;Integrated Security=False;User Id=sa;Password=Manoj;");
conn.Open();

if (conn != null)
{
    //create command
    SqlCommand cmd = new SqlCommand("dbo.GETTridionLinkData", conn);
    cmd.Parameters.AddWithValue("@PageID", "637518");
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandTimeout = 500;
    StringBuilder sbXML = new StringBuilder();
    //Adding Root node
    sbXML.Append("<TridionLinks>");

    //Reading all the values of Stored procedure return
    using (XmlReader reader = cmd.ExecuteXmlReader())
    {
        while (reader.Read())
        {
            sbXML.Append(reader.ReadOuterXml().Replace("//", "/"));
        }
    }

    //Closing the root node tag
    sbXML.Append("</TridionLinks>");
    XmlDocument xDoc = new XmlDocument();

    //Loading string xml in XML Document
    xDoc.LoadXml(sbXML.ToString());
}

In above code you can see that, I have set the cmd.CommandTimeout = 500;, now I want to give user an error message if the timeout is more than this or you can say database is down.

Please suggest!!

Community · Accepted Answer · 2017-05-23 12:03:13Z

1

Please refer to How to catch SQLServer timeout exceptions

The question has already been answered..

To improve coding, you can use

    try{

         using (sqlconnection Conn = new SqlConnection("Data Source=MANOJ-PC\\SQLEXPRESS;Initial Catalog=master;Integrated Security=False;User Id=sa;Password=Manoj;"){
            ...
        }
    }catch(sqlException ex){
       if (ex.Number == -2) {
        //return your message to the control or display the error
       }
    }

well just an example..

edited May 23, 2017 at 12:03

CommunityBot

11 silver badge

answered Mar 2, 2012 at 11:33

Parvez Bhatoo

262 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Manoj Singh Over a year ago

thanks, I saw your posted link, cmd.ExecuteNonQuery(); // This line will timeout. is used there, However I am using cmd.CommandTimeout = 500; as well as I am using cmd.ExecuteXmlReader() instead of cmd.ExecuteNonQuery(); so same exceptions handling will be done here also or something else needs to be written

Collectives™ on Stack Overflow

How to handle SQL Query CommandTimeout in C# 2.0

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related