I'm having an issue with returning a large amount of data into a dataset. 
When the query returns thousands of lines of data (in Query Analyzer it can
take 2 minutes) I receive the following error:
Message: System.Web.Services.Protocols.SoapException: Server was unable to
process request. ---> System.Data.SqlClient.SqlException: Timeout expired. 
The timeout period elapsed prior to completion of the operation or the server
is not responding.

My assumption is that I need to raise the command timeout to correct this. 
I cannot find a way while using SqlHelper.ExecuteDataSet to include a change
to the command timeout.  Here is an example of my webservice:


[WebMethod]
public System.Data.DataSet QuerySpecificTransaction(int transactionID)
{
    SqlConnection conn = new SqlConnection();
    DataSet ds = new DataSet();
    SqlParameter[] parms = new SqlParameter[1];

    try
    {
        conn.ConnectionString =
System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
        parms[0] = new SqlParameter("@transactionID", SqlDbType.Int);
        parms[0].Value = transactionID;
        ds = SqlHelper.ExecuteDataset(conn, CommandType.StoredProcedure,
"QuerySpecificTransaction", parms);
    }
    catch(Exception exc)
    {
        throw exc;   
    }
    finally
    {
                    if (conn.State != ConnectionState.Closed)
        {
            conn.Close();
        }
    }
    return ds;
}

you are correct, you need set the Timeout property of the sqlcommand object.
you will proably have to add code to your sqlhelper object to support this.

-- bruce (sqlwork.com)


Show quoteHide quote

Thanks, Bruce!  I've been digging through the SqlHelper object, but can't
find how to accomplish this.  Do you happen to know a good source?

Matt

Show quoteHide quote

Here's the fix in case anyone else has this issue.
********************************************************

[WebMethod]
public System.Data.DataSet QuerySpecificTransaction(int transactionID)
{
    SqlConnection conn = new SqlConnection();
    SqlParameter[] parms = new SqlParameter[1];

    try
    {
        conn.ConnectionString =
System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
        parms[0] = new SqlParameter("@transactionID", SqlDbType.Int);
        parms[0].Value = transactionID;

        SqlCommand com = new SqlCommand();
        bool mustCloseConnection = false;

        PrepareCommand(com, conn, null, CommandType.StoredProcedure,
"QuerySpecificTransaction", parms, out mustCloseConnection);
        using(SqlDataAdapter da = new SqlDataAdapter(com))
        {
            DataSet ds = new DataSet();
            da.Fill(ds);
            com.Parameters.Clear();
            if(mustCloseConnection)
                conn.Close();
            return ds;
        }               
    }
    catch(Exception exc)
    {
        throw exc;   
    }
    finally
    {
        if (conn.State != ConnectionState.Closed)
        {
            conn.Close();
        }
    }
}



private static void PrepareCommand(SqlCommand cmd, SqlConnection conn,
SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[]
cmdParms, out bool mustCloseConnection)
{
    if (conn.State != ConnectionState.Open)
    {
        mustCloseConnection = true;
        conn.Open();
    }
    else
    {
        mustCloseConnection = false;
    }

    cmd.Connection = conn;
    cmd.CommandText = cmdText;

    if (trans != null)
        cmd.Transaction = trans;

    cmd.CommandType = cmdType;
    cmd.CommandTimeout = 240;

    if (cmdParms != null)
    {
        foreach (SqlParameter parm in cmdParms)
            cmd.Parameters.Add(parm);
    }
    return;
}

********************************************************

Show quoteHide quote

Huge HTML output perfomance
New controls in ASP.NET 2.0
Layering Images in ASP.Net 1.1
My Threading Timer Doesn't Fire
How to install ISAPI filter for Url Rewriting?
javascript debugging
Dynamic Session State Mode inside of Web.Config
Pop-up Window
paging problem
Disable Cookieless Sessions on Single Page?