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.AppSett ings["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;
}
************************************************** ******
"Matt" wrote:
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.AppSett ings["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;
}
