ASMX Service Doesn't Always Return Data

I am working with an ASMX service in ASP.NET/C#. My service returns proper data for some of my WebMethods, but not all. The interesting part is all of the WebMethods are very similar.

Here's one that always returns data:

[WebMethod]
public AccountItem[] GetAllAccounts()
{
    AccountItem[] AccountItems = HttpContext.Current.Cache[AccountItemsCacheKey] as AccountItem[];
    if (AccountItems == null)
    {
        List<AccountItem> items = new List<AccountItem>();
        using (SqlManager sql = new SqlManager(SqlManager.GetSqlDbiConnectionString()))
        {
            using (SqlDataReader reader = sql.ExecuteReader("SELECT A.Account_Id, A.Customer_Id, C.Last_Name + ', ' + C.First_Name AS CustomerName, A.[Status], AT.Name AS AcctType, A.Employee_Id, A.Initial_Balance, A.Interest_Rate, '$'+CONVERT(varchar(50), A.Balance, 1) AS Balance FROM Account A JOIN Account_Type AT ON A.Account_Type_Id=AT.Account_Type_Id JOIN Customer C ON A.Customer_Id=C.Customer_Id WHERE [Status]=1"))
            {
                while (reader.Read())
                {
                    AccountItem item = new AccountItem();
                    item.AccountId = (int)reader["Account_Id"];
                    item.CustomerId = (int)reader["Customer_Id"];
                    item.CustomerName = (string)reader["CustomerName"];
                    item.AccountStatus = (bool)reader["Status"];
                    item.AccountType = (string)reader["AcctType"];
                    item.InitialBalance = (decimal)reader["Initial_Balance"];
                    item.InterestRate = (decimal)reader["Interest_Rate"];
                    item.Balance = (string)reader["Balance"];

                    items.Add(item);
                }
                reader.Close();
            }
        }
        HttpContext.Current.Cache.Add(AccountItemsCacheKey, items.ToArray(), null, DateTime.Now.AddMinutes(CacheDuration), Cache.NoSlidingExpiration, CacheItemPriority.Default, null);
        return items.ToArray();
    }
    else
    {
        return AccountItems;
    }
}

And here's one that never returns data:

[WebMethod]
public TransactionItem[] GetAllTransactions()
{
    TransactionItem[] tranItems = HttpContext.Current.Cache[TransactionItemsCacheKey] as TransactionItem[];

    if (tranItems == null)
    {
        List<TransactionItem> items = new List<TransactionItem>();
        using (SqlManager sql = new SqlManager(SqlManager.GetSqlDbiConnectionString()))
        {
            using (SqlDataReader reader = sql.ExecuteReader("SELECT [Transaction_Id],[Account_Id],[Amount],[DateTime],[Comment],TT.[Name] AS [TransType],[Flagged],[Employee_Id],[Status] FROM [Transaction] T JOIN [Trans_Type] TT ON T.Trans_Type_Id=TT.Trans_Type_Id"))
            {
                while (reader.Read())
                {
                    TransactionItem item = new TransactionItem();
                    item.TransactionId = (int)reader["Transaction_Id"];
                    item.AccountId = (int)reader["Account_Id"];
                    item.Amount = (decimal)reader["Amount"];
                    item.Timestamp = (DateTime)reader["DateTime"];
                    item.Comment = (string)reader["Comment"];
                    item.TransType = (string)reader["TransType"];
                    item.Flagged = (bool)reader["Flagged"];
                    item.EmployeeId = (int)reader["Employee_Id"];
                    item.Status = (bool)reader["Status"];

                    items.Add(item);
                }
                reader.Close();
            }
        }
        HttpContext.Current.Cache.Add(TransactionItemsCacheKey, items.ToArray(), null, DateTime.Now.AddMinutes(CacheDuration), Cache.NoSlidingExpiration, CacheItemPriority.Default, null);
        return items.ToArray();
    }
    else
    {
        return tranItems;
    }
}

As you can see, they're almost identical. The SQL queries for both return a ton of records, but only the GetAllAccounts() WebMethod actually returns that data back.

This is how I'm displaying the data passed back from GetAllAccounts(), which works fine:

@{
    Layout = "~/Shared/_Layout.cshtml";
    Page.Title = "Accounts";
    Page.Header = "BankSite Mobile - Accounts";
    var svc = IntranetService.GetAllAccounts();
}
 <div data-role="content">
        <ul data-role="listview" data-inset="true" data-theme="c">
            @foreach(var item in svc){
                <li>
                    <h3><a href="Product.cshtml?acctid=@item.AccountId">Account #@item.AccountId.ToString() (@item.AccountType)</a></h3>
                    <p>Customer: @item.Cust开发者_高级运维omerName</p>
                    <p>Account Balance: @item.Balance</p>
                </li>
            }
       </ul>
  </div>

Yet, this doesn't work fine, though it's the almost the exact same code:

@{
    Layout = "~/Shared/_Layout.cshtml";
    Page.Title = "Customers";
    Page.Header = "BankSite Mobile - Customers";
    var svc = IntranetService.GetAllCustomers();
}
 <div data-role="content">
        <ul data-role="listview" data-inset="true" data-theme="c">
            @foreach(var item in svc){
                <li>
                    <h3><a href="Product.cshtml?acctid=@item.CustomerId">Account #@item.CustomerId.ToString() (@item.CustomerId)</a></h3>
                    <p>Customer: @item.CustomerId</p>
                    <p>Account Balance: @item.CustomerId</p>
                </li>
            }
       </ul>
  </div>

...So basically I'm baffled. I don't understand why the data isn't being returned as expected from the non-working WebMethod (GetAllCustomers()). What am I missing?

If you disable loading stuff from the cache, would both methods always succeed to return expected result set? I would try that first, my gut feeling is that something funky with the cache (i.e expires before your method returns). Then go from there.

Try to isolate the problem to the web service by accessing the web service directly in a web browser. Also, if possible, use SQL Server Profiler to make sure the web method is querying the database.

If it is not querying the database, then I would guess that it has already cached an empty array.

Therefore, the inital "if (tranItems == null)" check returns false, but it then returns an empty array as the results.

I found the issue to be that some fields retrieved by the reader were null, and you can't create a null string. The solution was essentially to use something like this for every item property:

item.Amount = (reader["Amount"] != DBNull.value) ? (decimal)reader["Amount"] : 0;