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
input type="file" grabs ENTIRE file path in IE?
Tabs in ASP.Net
No. of connections available for Per Seat License