This is a fun one.
I have written a custom search page that provides faster, more user friendly searches than the default Contacts view and also allows searching of Leads and Contacts simultaneously. It uses GridViews bound to SqlDataSources that query filtered views. I'm sure someone will complain that I'm not using the web services for this, but this is just the design decision we made.
These GridViews live in UpdatePanels to enable very slick AJAX updates upon search.
It's all working great. Nearly ready to be deployed, except for one thing: Some long running searches are triggering an uncatchable SQL timeout exception.
[SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.]
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)
at System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments)
at System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback)
at System.Web.UI.WebControls.DataBoundControl.PerformSelect()
at System.Web.UI.WebControls.BaseDataBoundControl.DataBind()
at System.Web.UI.WebControls.GridView.DataBind()
at System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound()
at System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls()
at System.Web.UI.Control.EnsureChildControls()
at System.Web.UI.Control.PreRenderRecursiveInternal()
at System.Web.UI.Control.PreRenderRecursiveInternal()
at System.Web.UI.Control.PreRenderRecursiveInternal()
at System.Web.UI.Control.PreRenderRecursiveInternal()
at System.Web.UI.Control.PreRenderRecursiveInternal()
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
I found that CRM is doing a server.transfer to capture this error because my UpdatePanels started throwing JavaSript errors when this error would occur. I was only able to get the full error message by using the JavaScript debugger in IE.
Having found this error, I thought the solution would be simple. I just needed to wrap my databind calls in try/catch blocks to capture any errors.
Unfortunately it seems CRM's IIS configuration has the magic ability to capture this error before it ever gets back to my code. Using the debugger I never see it. It never gets to my catch blocks, but it's clearly happening in the SQL Data Source which is clearly (by the stack trace) being triggered by my GridView bind.
Any ideas on this? It's driving me crazy.
Code Behind (with some irrelevant functions omitted):
protected void Page_Load(object sender, EventArgs e)
{
//Initialize some stuff
this.bannerOracle = new OdbcConnection(ConfigurationManager.ConnectionStrings["OracleConnectionString"].ConnectionString);
//Prospect default
HideProspects();
HideProspectAddressColumn();
//Contacts default
HideContactAddressColumn();
//Default error messages
gvContacts.EmptyDataText = "Sad day. Your search returned no contacts.";
gvProspects.EmptyDataText = "Sad day. Your search returned no prospects.";
//New search
try
{
SearchContact(null, -1);
}
catch
{
gvContacts.EmptyDataText = "Oops! An error occured. This may have been a timeout. Please try your search again.";
gvContacts.DataSource = null;
gvContacts.DataBind();
}
}
protected void txtSearchString_TextChanged(object sender, EventArgs e)
{
if(!String.IsNullOrEmpty(txtSearchString.Text))
{
try
{
SearchContact(txtSearchString.Text, Convert.ToInt16(lstSearchType.SelectedValue));
}
catch
{
gvContacts.EmptyDataText = "Oops! An error occured. This may have been a timeout. Please try your search again.";
gvContacts.DataSource = null;
gvContacts.DataBind();
}
if (chkProspects.Checked == true)
{
try
{
SearchProspect(txtSearchString.Text, Convert.ToInt16(lstSearchType.SelectedValue));
}
catch
{
gvProspects.EmptyDataText = "Oops! An error occured. This may have been a timeout. Please try your search again.";
gvProspects.DataSource = null;
gvProspects.DataBind();
}
finally
{
ShowProspects();
}
}
else
{
HideProspects();
}
}
}
protected void SearchContact(string search, int type)
{
SqlCRM_Contact.ConnectionString = ConfigurationManager.ConnectionStrings["MSSQLConnectionString"].ConnectionString;
gvContacts.DataSourceID = "SqlCRM_Contact";
string strQuery = "";
string baseQuery = @"SELECT filteredcontact.contactid,
filteredcontact.new_libertyid,
filteredcontact.fullname,
'none' AS line1,
filteredcontact.emailaddress1,
filteredcontact.telephone1,
filteredcontact.birthdateutc AS birthdate,
filteredcontact.gendercodename
FROM filteredcontact ";
switch(type)
{
case LASTFIRST:
strQuery = baseQuery + "WHERE fullname LIKE @value AND filteredcontact.statecode = 0";
SqlCRM_Contact.SelectCommand = strQuery;
SqlCRM_Contact.SelectParameters.Add("value", DbType.String, search.Trim() + "%");
break;
case LAST:
strQuery = baseQuery + "WHERE lastname LIKE @value AND filteredcontact.statecode = 0";
SqlCRM_Contact.SelectCommand = strQuery;
SqlCRM_Contact.SelectParameters.Add("value", DbType.String, search.Trim() + "%");
break;
case FIRST:
strQuery = baseQuery + "WHERE firstname LIKE @value AND filteredcontact.statecode = 0";
SqlCRM_Contact.SelectCommand = strQuery;
SqlCRM_Contact.SelectParameters.Add("value", DbType.String, search.Trim() + "%");
break;
case LIBERTYID:
strQuery = baseQuery + "WHERE new_libertyid LIKE @value AND filteredcontact.statecode = 0";
SqlCRM_Contact.SelectCommand = strQuery;
SqlCRM_Contact.SelectParameters.Add("value", DbType.String, search.Trim() + "%");
break;
case EMAIL:
strQuery = baseQuery + "WHERE emailaddress1 LIKE @value AND filteredcontact.statecode = 0";
SqlCRM_Contact.SelectCommand = strQuery;
SqlCRM_Contact.SelectParameters.Add("value", DbType.String, search.Trim() + "%");
break;
case TELEPHONE:
strQuery = baseQuery + "WHERE telephone1 LIKE @value AND filteredcontact.statecode = 0";
SqlCRM_Contact.SelectCommand = strQuery;
SqlCRM_Contact.SelectParameters.Add("value", DbType.String, search.Trim() + "%");
break;
case BIRTHDAY:
strQuery = baseQuery + "WHERE filteredcontact.birthdateutc BETWEEN @dateStart AND @dateEnd AND filteredcontact.statecode = 0";
try
{
DateTime temp = DateTime.Parse(search);
if (temp.Year < 1753 || temp.Year > 9999)
{
search = string.Empty;
}
else
{
search = temp.ToString("yyyy-MM-dd");
}
}
catch
{
search = string.Empty;
}
SqlCRM_Contact.SelectCommand = strQuery;
SqlCRM_Contact.SelectParameters.Add("dateStart", DbType.String, search.Trim() + " 00:00:00.000");
SqlCRM_Contact.SelectParameters.Add("dateEnd", DbType.String, search.Trim() + " 23:59:59.999");
break;
case SSN:
//Do something
break;
case ADDRESS:
strQuery = @"SELECT contactid,
new_libertyid,
fullname,
line1,
emailaddress1,
telephone1,
birthdate,
gendercodename
FROM (SELECT FC.contactid,
FC.new_libertyid,
FC.fullname,
FA.line1,
FC.emailaddress1,
FC.telephone1,
FC.birthdateutc AS birthdate,
FC.gendercodename,
ROW_NUMBER() OVER(PARTITION BY FC.contactid ORDER BY FC.contactid DESC) AS rn
FROM filteredcontact FC
INNER JOIN FilteredCustomerAddress FA
ON FC.contactid = FA.parentid
WHERE FA.line1 LIKE @value AND FA.addressnumber <> 1 AND FC.statecode = 0 ) AS RESULTS
WHERE rn = 1";
SqlCRM_Contact.SelectCommand = strQuery;
SqlCRM_Contact.SelectParameters.Add("value", DbType.String, search.Trim() + "%");
ShowContactAddressColumn();
break;
default:
strQuery = @"SELECT TOP 500 filteredcontact.contactid,
filteredcontact.new_libertyid,
filteredcontact.fullname,
'none' AS line1,
filteredcontact.emailaddress1,
filteredcontact.telephone1,
filteredcontact.birthdateutc AS birthdate,
filteredcontact.gendercodename
FROM filteredcontact
WHERE filteredcontact.statecode = 0";
SqlCRM_Contact.SelectCommand = strQuery;
break;
}
if (type != ADDRESS)
{
HideContactAddressColumn();
}
gvContacts.PageIndex = 0;
//try
//{
// SqlCRM_Contact.DataBind();
//}
//catch
//{
// SqlCRM_Contact.DataBind();
//}
gvContacts.DataBind();
}
protected void SearchProspect(string search, int type)
{
SqlCRM_Prospect.ConnectionString = ConfigurationManager.ConnectionStrings["MSSQLConnectionString"].ConnectionString;
gvProspects.DataSourceID = "SqlCRM_Prospect";
string strQuery = "";
string baseQuery = @"SELECT filteredlead.leadid,
filteredlead.fullname,
'none' AS address1_line1,
filteredlead.emailaddress1,
filteredlead.telephone1,
filteredlead.lu_dateofbirthutc AS lu_dateofbirth,
filteredlead.lu_gendername
FROM filteredlead ";
switch (type)
{
case LASTFIRST:
strQuery = baseQuery + "WHERE fullname LIKE @value AND filteredlead.statecode = 0";
SqlCRM_Prospect.SelectCommand = strQuery;
SqlCRM_Prospect.SelectParameters.Add("value", DbType.String, search.Trim() + "%");
break;
case LAST:
strQuery = baseQuery + "WHERE lastname LIKE @value AND filteredlead.statecode = 0";
SqlCRM_Prospect.SelectCommand = strQuery;
SqlCRM_Prospect.SelectParameters.Add("value", DbType.String, search.Trim() + "%");
break;
case FIRST:
strQuery = baseQuery + "WHERE firstname LIKE @value AND filteredlead.statecode = 0";
SqlCRM_Prospect.SelectCommand = strQuery;
SqlCRM_Prospect.SelectParameters.Add("value", DbType.String, search.Trim() + "%");
break;
case LIBERTYID:
strQuery = baseQuery + "WHERE new_libertyid LIKE @value AND filteredlead.statecode = 0";
SqlCRM_Prospect.SelectCommand = strQuery;
SqlCRM_Prospect.SelectParameters.Add("value", DbType.String, search.Trim() + "%");
break;
case EMAIL:
strQuery = baseQuery + "WHERE emailaddress1 LIKE @value AND filteredlead.statecode = 0";
SqlCRM_Prospect.SelectCommand = strQuery;
SqlCRM_Prospect.SelectParameters.Add("value", DbType.String, search.Trim() + "%");
break;
case TELEPHONE:
strQuery = baseQuery + "WHERE telephone1 LIKE @value AND filteredlead.statecode = 0";
SqlCRM_Prospect.SelectCommand = strQuery;
SqlCRM_Prospect.SelectParameters.Add("value", DbType.String, search.Trim() + "%");
break;
case BIRTHDAY:
strQuery = baseQuery + "WHERE filteredlead.lu_dateofbirth BETWEEN @dateStart AND @dateEnd AND filteredlead.statecode = 0";
try
{
DateTime temp = DateTime.Parse(search);
if (temp.Year < 1753 || temp.Year > 9999)
{
search = string.Empty;
}
else
{
search = temp.ToString("yyyy-MM-dd");
}
}
catch
{
search = string.Empty;
}
SqlCRM_Prospect.SelectCommand = strQuery;
SqlCRM_Prospect.SelectParameters.Add("dateStart", DbType.String, search.Trim() + " 00:00:00.000");
SqlCRM_Prospect.SelectParameters.Add("dateEnd", DbType.String, search.Trim() + " 23:59:59.999");
break;
case SSN:
//Do nothing
break;
case ADDRESS:
strQuery = @"SELECT filteredlead.leadid,
filteredlead.fullname,
filteredlead.address1_line1,
filteredlead.emailaddress1,
filteredlead.telephone1,
filteredlead.lu_dateofbirthutc AS lu_dateofbirth,
filteredlead.lu_gendername
FROM filteredlead WHERE address1_line1 LIKE @value AND filteredlead.statecode = 0";
SqlCRM_Prospect.SelectCommand = strQuery;
SqlCRM_Prospect.SelectParameters.Add("value", DbType.String, search.Trim() + "%");
ShowProspectAddressColumn();
break;
default:
strQuery = @"SELECT TOP 500 filteredlead.leadid,
filteredlead.fullname,
'none' AS address1_line1
filteredlead.emailaddress1,
filteredlead.telephone1,
filteredlead.lu_dateofbirthutc AS lu_dateofbirth,
filteredlead.lu_gendername
FROM filteredlead WHERE filteredlead.statecode = 0";
SqlCRM_Prospect.SelectCommand = strQuery;
break;
}
if (type != ADDRESS)
{
HideProspectAddressColumn();
}
gvProspects.PageIndex = 0;
//try
//{
// SqlCRM_Prospect.DataBind();
//}
//catch (Exception ex)
//{
// SqlCRM_Prospect.DataBind();
//}
gvProspects.DataBind();
}
