So, we've got this set of code that, for some reason, keeps timing out. It's not the stored procedure that it's running, because that runs fine. Also, if we remove the parameter from the c# code, the code runs. The parameter keeps breaking (causing it to time out) and we can't figure out why.
c#:
public static PTWViewList GetList(int studynumber)
{
PTWViewList tempList = new PTWViewList();
using (SqlConnection myConnection = new SqlConnection(AppConfiguration.cnARDB))
{
string spName = "ardb.PTWViewSelect";
SqlCommand myCommand = new SqlCommand(spName, myConnection);
myCommand.CommandType = CommandType.StoredProcedure;
myCommand.Parameters.AddWithValue("@study", studynumber);
myConnection.Open();
using (NullableDataReader myReader = new NullableDataReader(myCommand.ExecuteReader())) /*this is where the code times out*/
{
tempList = new PTWViewList();
while (myReader.Read())
{
tempList.Add(FillDataRecord(myReader));
}
myReader.Close();
}
}
tempList.ListCount = tempList.Count;
return tempList;
}
stored procedure:
CREATE PROCEDURE [ardb].[PTWViewSelect]
@studynumber int = NULL,
@quoteid uniqueidentifier = NULL,
@lineitemid uniqueidentifier = NULL
AS
BEGIN
SET NOCOUNT ON;
SELECT
[Study]
,[LineItemID]
,[QuoteID]
,[Total]
,[COOP]
,[VendorCost]
,[CustCost]
,[LineItemNumber]
,[StudyTypeCode]
,[GroupLeader]
,[PTWDate]
,[PONumber]
,[POStatus]
,[StudyDirector]
,[SL_DESC_L]
,[SL_Code]
,ProjectDescription
,CreatedBy
,chARProcess
,CODate
FROM
[ARDB].[dbo].[PTWView]
WHERE
(@studynumber is null or StudyNumber=@studynumber)
AND (@quoteid is null or QuoteID=@quoteid)
AND (@lineitemid is null or LineItemID = @lineitemid)
END
