Here's the fix in case anyone else has this issue.
*************** *************** *************** ***********
[WebMethod]
public System.Data.Dat aSet QuerySpecificTr ansaction(int transactionID)
{
SqlConnection conn = new SqlConnection() ;
SqlParameter[] parms = new SqlParameter[1];
try
{
conn.Connection String =
System.Configur ation.Configura tionSettings.Ap pSettings["ConnectionStri ng"];
parms[0] = new SqlParameter("@ transactionID", SqlDbType.Int);
parms[0].Value = transactionID;
SqlCommand com = new SqlCommand();
bool mustCloseConnec tion = false;
PrepareCommand( com, conn, null, CommandType.Sto redProcedure,
"QuerySpecificT ransaction", parms, out mustCloseConnec tion);
using(SqlDataAd apter da = new SqlDataAdapter( com))
{
DataSet ds = new DataSet();
da.Fill(ds);
com.Parameters. Clear();
if(mustCloseCon nection)
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 mustCloseConnec tion)
{
if (conn.State != ConnectionState .Open)
{
mustCloseConnec tion = true;
conn.Open();
}
else
{
mustCloseConnec tion = false;
}
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (trans != null)
cmd.Transaction = trans;
cmd.CommandType = cmdType;
cmd.CommandTime out = 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.Serv ices.Protocols. SoapException: Server was unable to
process request. ---> System.Data.Sql Client.SqlExcep tion: 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.Execu teDataSet to include a change
to the command timeout. Here is an example of my webservice:
[WebMethod]
public System.Data.Dat aSet QuerySpecificTr ansaction(int transactionID)
{
SqlConnection conn = new SqlConnection() ;
DataSet ds = new DataSet();
SqlParameter[] parms = new SqlParameter[1];
try
{
conn.Connection String =
System.Configur ation.Configura tionSettings.Ap pSettings["ConnectionStri ng"];
parms[0] = new SqlParameter("@ transactionID", SqlDbType.Int);
parms[0].Value = transactionID;
ds = SqlHelper.Execu teDataset(conn, CommandType.Sto redProcedure,
"QuerySpecificT ransaction", parms);
}
catch(Exception exc)
{
throw exc;
}
finally
{
if (conn.State != ConnectionState .Closed)
{
conn.Close();
}
}
return ds;
}
