Problem with wrong data in dataset using SQLDataAdapter.fill

microsoft.public.dotnet.framework.adonet

18 Nov 2005 5:53 PM

loralea

We are experiencing an intermittent problem with our ASP.NET
application. When using the SQLDataAdapter to fill a dataset, the
dataset is sometimes being filled with the wrong data. It appears to
be data from different threads. For example, when retrieving a product
information for the product list page, the dataset that was returned
was a customer address that would have been requested from a different
page in the site. In this example, table[0] in the dataset should have
contained 4 columns: R.to_oid, P.IsActive, P.ProductID, R.SortOrder
Instead, table[0] in the dataset returned from GetDataSet contained 27
columns of customer address data. (I have included the code for the
given example below.) These errors are thrown from many different
pages on the site, and the wrong data that is returned is also from
many different pages of the site. When the problem occurs it will occur
on only one of the server, and will last anywhere from 10 minutes to an
hour or so. When it occurs, not every dataset is affected, but by
clicking a few links through the site, you can see the error. You can
click on a product and get an error, go back and click on the same
link, and not get an error. It's very sporadic. It does eventually go
away by itself, or we can get the behavior to stop by doing an IISreset
on the affected server. Does anyone have any insight?

Thanks,
Loralea Seale
INS, Consultant

Environment:
2 load balanced web servers, Win2003, .NET Framework 1.1, no SP
2 SQL Servers, active-passive cluster

Page code behind:

DataSvc.clsDataAccess oData = new
DataSvc.clsDataAccess(g.Const.cAPPLICATION_DatabaseKey);
string sSqlStmt =
string.Format(g.Const.cSQL_RetrieveProductRelationships, sProduct,
"Design");

DataSet oDs = oData.GetDataSet(sSqlStmt, g.Const.cTABLE_Default);

foreach (DataRow oRelationship in oDs.Tables[0].Rows)
{
    sDesign = oRelationship["ProductID"].ToString();     //<<<throws error
here, System.Data.DataRowView does not contain a property with the name
ProductID.

    ...

}

g.Const.cSQL_RetrieveProductRelationships value:

"SELECT R.to_oid, P.IsActive, P.ProductID, R.SortOrder " +
"FROM Pens_CatalogRelationships R, Pens_CatalogProducts P " +
"WHERE R.from_oid = (SELECT oid From Pens_CatalogProducts WHERE
ProductID ='{0}') " +
"AND R.Name = '{1}' AND R.to_oid = P.oid AND P.IsActive = 1 ORDER BY
R.SortOrder";

DataSvc class code:

using System;
using System.Data;
using System.Web;
using System.Data.SqlClient;
using System.Collections.Specialized;

namespace DataSvc
{
    /// <summary>
    /// Class clsDataAccess - Contains code for database access and
processing.
    /// </summary>
    public class clsDataAccess
    {
        // Private variables._
        string              _connstring;
        bool                _connected = false;
        NameValueCollection _configs    = (NameValueCollection)
HttpContext.GetAppConfig("appSettings");
        SqlConnection       _connection = new SqlConnection();

        /// <summary>
        /// clsDataAccess - Initializes class.
        /// </summary>
        public clsDataAccess()
        {
            _connected = false;
        }

        /// <summary>
        /// clsDataAccess - Sets up database connection based on given
connection string.
        /// </summary>
        /// <param name="ConnectionString">Database connection
string.</param>
        public clsDataAccess(string ConnectionString)
        {
            //configuration key exists, open database connection
            if (_configs[ConnectionString] != null)
            {
                _connstring = (string) _configs[ConnectionString];
                _connection = new SqlConnection(_connstring);
                _connected = true;
            }
            else
            {
                //invalid configuration key name, throw error
                ArgumentOutOfRangeException e = new ArgumentOutOfRangeException();
                throw(e);
            }
        }

        /// <summary>
        /// ~clsDataAccess - Closes database connection.
        /// </summary>
        ~clsDataAccess()
        {
            try
            {
                _connection.Close();
            }
            catch {}
        }

        /// <summary>
        /// ExecuteSql - Executes a SQL statement.
        /// </summary>
        /// <param name="Sql">SQL statement to be processed.</param>
        /// <returns>Number of rows affected.</returns>
        public int ExecuteSql(string Sql)
        {
            int rc = 0;
            if (_connected)
            {
                SqlCommand sc = new SqlCommand(Sql,_connection);
                sc.Connection.Open();
                rc = sc.ExecuteNonQuery();
                sc.Connection.Close();
            }
            return rc;
        }

        /// <summary>
        /// GetDataSet - Executes a given SQL statement and returns the
results.
        /// </summary>
        /// <param name="sqlStmt">SQL statement to be processed.</param>
        /// <param name="tableName">Name of table to be labelled for return
results set.</param>
        /// <returns></returns>
        public DataSet GetDataSet(string sqlStmt, string tableName)
        {
            if (_connected == false)
                return null;

            DataSet ds = new DataSet();

            try
            {
                _connection.Open();
                SqlDataAdapter sda = new SqlDataAdapter(sqlStmt, _connection);

                sda.Fill(ds, tableName);
                _connection.Close();
            }
            catch (Exception ex)
            {
                Log.additionalInfo.Clear();
                Log.additionalInfo.Add("clsDataAccess.cs", "GetDataSet");
                Log.Write(ex);
            }
            finally {
                try {
                    if (_connection != null && _connection.State !=
ConnectionState.Closed)
                        _connection.Close();
                }catch {}
            }

            return ds;
        }
    }

}