Hi Kenj,
Seems the problem is data access component related or page specific. I've
justed performed some tests through a sample data access object( use
Northwind database's employee table), and I found that eveytime, the page
load (no matter initial request all sequential postback...), the
ObjectDataSource's SelectMethod and SelectCount method(if enabe paging)
will be called only once .... I print out the statement in page's output
trace ....
So will the duplicated method calls caused by some other controls or code
on the page?
Here is my test data access object class (I only use a single additional
select parameter....):
=============
/// <summary>
/// Summary description for objDS
/// </summary>
namespace Samples.AspNet.ObjectDataSource
{
//
// Northwind Employee Data Factory
//
public class NorthwindData
{
private string _connectionString;
public NorthwindData()
{
Initialize();
}
public void Initialize()
{
// Initialize data source. Use "Northwind" connection string
from configuration.
if (ConfigurationManager.ConnectionStrings["Northwind"] == null
||
ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString.Trim()
== "")
{
throw new Exception("A connection string named 'Northwind'
with a valid connection string " +
"must exist in the <connectionStrings>
configuration section for the application.");
}
_connectionString =
ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
}
// Select all employees.
public DataTable GetAllEmployees(string sortColumns, int
startRecord, int maxRecords, int eid)
{
VerifySortColumns(sortColumns);
string sqlCmd = "SELECT EmployeeID, LastName, FirstName,
Address, City, Region, PostalCode FROM Employees ";
//test code
HttpContext.Current.Trace.Warn("<br>GetAllEmployees, eid: " +
eid);
//eid = -1;
if(eid > 0)
{
sqlCmd += " WHERE EmployeeID > @eid ";
}
if (sortColumns.Trim() == "")
sqlCmd += "ORDER BY EmployeeID";
else
sqlCmd += "ORDER BY " + sortColumns;
SqlConnection conn = new SqlConnection(_connectionString);
SqlDataAdapter da = new SqlDataAdapter(sqlCmd, conn);
if(eid > 0)
{
da.SelectCommand.Parameters.Add("@eid",SqlDbType.Int);
da.SelectCommand.Parameters["@eid"].Value = eid;
}
DataSet ds = new DataSet();
try
{
conn.Open();
da.Fill(ds, startRecord, maxRecords, "Employees");
}
catch (SqlException e)
{
// Handle exception.
}
finally
{
conn.Close();
}
return ds.Tables["Employees"];
}
public int SelectCount(int eid )
{
string sqlCmd = "SELECT COUNT(*) FROM Employees";
//test code
HttpContext.Current.Trace.Warn("<br>SelectCount, eid: " + eid);
//eid = 0;
if (eid > 0)
{
sqlCmd += " WHERE EmployeeID > @eid ";
}
SqlConnection conn = new SqlConnection(_connectionString);
SqlCommand cmd = new SqlCommand(sqlCmd, conn);
if (eid > 0)
{
cmd.Parameters.Add("@eid", SqlDbType.Int);
cmd.Parameters["@eid"].Value = eid;
}
int result = 0;
try
{
conn.Open();
result = (int)cmd.ExecuteScalar();
}
catch (SqlException e)
{
// Handle exception.
}
finally
{
conn.Close();
}
return result;
}
//////////
// Verify that only valid columns are specified in the sort
expression to avoid a SQL Injection attack.
private void VerifySortColumns(string sortColumns)
{
if (sortColumns.ToLowerInvariant().EndsWith(" desc"))
sortColumns = sortColumns.Substring(0, sortColumns.Length -
5);
string[] columnNames = sortColumns.Split(',');
foreach (string columnName in columnNames)
{
switch (columnName.Trim().ToLowerInvariant())
{
case "employeeid":
break;
case "lastname":
break;
case "firstname":
break;
case "":
break;
default:
throw new ArgumentException("SortColumns contains
an invalid column name.");
break;
}
}
}
// Select an employee.
public DataTable GetEmployee(int EmployeeID)
{
SqlConnection conn = new SqlConnection(_connectionString);
SqlDataAdapter da =
new SqlDataAdapter("SELECT EmployeeID, LastName, FirstName,
Address, City, Region, PostalCode " +
" FROM Employees WHERE EmployeeID =
@EmployeeID", conn);
da.SelectCommand.Parameters.Add("@EmployeeID",
SqlDbType.Int).Value = EmployeeID;
DataSet ds = new DataSet();
try
{
conn.Open();
da.Fill(ds, "Employees");
}
catch (SqlException e)
{
// Handle exception.
}
finally
{
conn.Close();
}
return ds.Tables["Employees"];
}
// Delete the Employee by ID.
public int DeleteEmployee(int EmployeeID)
{
SqlConnection conn = new SqlConnection(_connectionString);
SqlCommand cmd = new SqlCommand("DELETE FROM Employees WHERE
EmployeeID = @EmployeeID", conn);
cmd.Parameters.Add("@EmployeeID", SqlDbType.Int).Value =
EmployeeID;
int result = 0;
try
{
conn.Open();
result = cmd.ExecuteNonQuery();
}
catch (SqlException e)
{
// Handle exception.
}
finally
{
conn.Close();
}
return result;
}
// Update the Employee by original ID.
public int UpdateEmployee(int EmployeeID, string LastName, string
FirstName,
string Address, string City, string
Region, string PostalCode)
{
//test code
HttpContext.Current.Trace.Warn("<br>UpdateEmployee");
if (String.IsNullOrEmpty(FirstName))
throw new ArgumentException("FirstName cannot be null or an
empty string.");
if (String.IsNullOrEmpty(LastName))
throw new ArgumentException("LastName cannot be null or an
empty string.");
if (Address == null) { Address = String.Empty; }
if (City == null) { City = String.Empty; }
if (Region == null) { Region = String.Empty; }
if (PostalCode == null) { PostalCode = String.Empty; }
SqlConnection conn = new SqlConnection(_connectionString);
SqlCommand cmd = new SqlCommand("UPDATE Employees " +
" SET
FirstName=@FirstName, LastName=@LastName, " +
" Address=@Address,
City=@City, Region=@Region, " +
" PostalCode=@PostalCode "
+
" WHERE
EmployeeID=@EmployeeID", conn);
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 10).Value =
FirstName;
cmd.Parameters.Add("@LastName", SqlDbType.VarChar, 20).Value =
LastName;
cmd.Parameters.Add("@Address", SqlDbType.VarChar, 60).Value =
Address;
cmd.Parameters.Add("@City", SqlDbType.VarChar, 15).Value = City;
cmd.Parameters.Add("@Region", SqlDbType.VarChar, 15).Value =
Region;
cmd.Parameters.Add("@PostalCode", SqlDbType.VarChar, 10).Value
= PostalCode;
cmd.Parameters.Add("@EmployeeID", SqlDbType.Int).Value =
EmployeeID;
int result = 0;
try
{
conn.Open();
result = cmd.ExecuteNonQuery();
}
catch (SqlException e)
{
// Handle exception.
}
finally
{
conn.Close();
}
return result;
}
// Insert an Employee.
public int InsertEmployee(string LastName, string FirstName,
string Address, string City, string
Region, string PostalCode)
{
if (String.IsNullOrEmpty(FirstName))
throw new ArgumentException("FirstName cannot be null or an
empty string.");
if (String.IsNullOrEmpty(LastName))
throw new ArgumentException("LastName cannot be null or an
empty string.");
if (Address == null) { Address = String.Empty; }
if (City == null) { City = String.Empty; }
if (Region == null) { Region = String.Empty; }
if (PostalCode == null) { PostalCode = String.Empty; }
SqlConnection conn = new SqlConnection(_connectionString);
SqlCommand cmd = new SqlCommand("INSERT INTO Employees " +
" (FirstName, LastName,
Address, City, Region, PostalCode) " +
" Values(@FirstName,
@LastName, @Address, @City, @Region, @PostalCode); " +
"SELECT @EmployeeID =
SCOPE_IDENTITY()", conn);
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 10).Value =
FirstName;
cmd.Parameters.Add("@LastName", SqlDbType.VarChar, 20).Value =
LastName;
cmd.Parameters.Add("@Address", SqlDbType.VarChar, 60).Value =
Address;
cmd.Parameters.Add("@City", SqlDbType.VarChar, 15).Value = City;
cmd.Parameters.Add("@Region", SqlDbType.VarChar, 15).Value =
Region;
cmd.Parameters.Add("@PostalCode", SqlDbType.VarChar, 10).Value
= PostalCode;
SqlParameter p = cmd.Parameters.Add("@EmployeeID",
SqlDbType.Int);
p.Direction = ParameterDirection.Output;
int newEmployeeID = 0;
try
{
conn.Open();
cmd.ExecuteNonQuery();
newEmployeeID = (int)p.Value;
}
catch (SqlException e)
{
// Handle exception.
}
finally
{
conn.Close();
}
return newEmployeeID;
}
//
// Methods that support Optimistic Concurrency checks.
//
// Delete the Employee by ID.
public int DeleteEmployee(int original_EmployeeID, string
original_LastName,
string original_FirstName, string
original_Address,
string original_City, string
original_Region,
string original_PostalCode)
{
if (String.IsNullOrEmpty(original_FirstName))
throw new ArgumentException("FirstName cannot be null or an
empty string.");
if (String.IsNullOrEmpty(original_LastName))
throw new ArgumentException("LastName cannot be null or an
empty string.");
if (original_Address == null) { original_Address =
String.Empty; }
if (original_City == null) { original_City = String.Empty; }
if (original_Region == null) { original_Region = String.Empty; }
if (original_PostalCode == null) { original_PostalCode =
String.Empty; }
string sqlCmd = "DELETE FROM Employees WHERE EmployeeID =
@original_EmployeeID " +
" AND LastName = @original_LastName AND
FirstName = @original_FirstName " +
" AND Address = @original_Address AND City =
@original_City " +
" AND Region = @original_Region AND PostalCode
= @original_PostalCode";
SqlConnection conn = new SqlConnection(_connectionString);
SqlCommand cmd = new SqlCommand(sqlCmd, conn);
cmd.Parameters.Add("@original_EmployeeID", SqlDbType.Int).Value
= original_EmployeeID;
cmd.Parameters.Add("@original_FirstName", SqlDbType.VarChar,
10).Value = original_FirstName;
cmd.Parameters.Add("@original_LastName", SqlDbType.VarChar,
20).Value = original_LastName;
cmd.Parameters.Add("@original_Address", SqlDbType.VarChar,
60).Value = original_Address;
cmd.Parameters.Add("@original_City", SqlDbType.VarChar,
15).Value = original_City;
cmd.Parameters.Add("@original_Region", SqlDbType.VarChar,
15).Value = original_Region;
cmd.Parameters.Add("@original_PostalCode", SqlDbType.VarChar,
10).Value = original_PostalCode;
int result = 0;
try
{
conn.Open();
result = cmd.ExecuteNonQuery();
}
catch (SqlException e)
{
// Handle exception.
}
finally
{
conn.Close();
}
return result;
}
// Update the Employee by original ID.
public int UpdateEmployee(int EmployeeID, string LastName, string
FirstName,
string Address, string City, string
Region, string PostalCode,
int original_EmployeeID, string
original_LastName,
string original_FirstName, string
original_Address,
string original_City, string
original_Region,
string original_PostalCode)
{
if (String.IsNullOrEmpty(FirstName))
throw new ArgumentException("FirstName cannot be null or an
empty string.");
if (String.IsNullOrEmpty(LastName))
throw new ArgumentException("LastName cannot be null or an
empty string.");
if (Address == null) { Address = String.Empty; }
if (City == null) { City = String.Empty; }
if (Region == null) { Region = String.Empty; }
if (PostalCode == null) { PostalCode = String.Empty; }
if (original_Address == null) { original_Address =
String.Empty; }
if (original_City == null) { original_City = String.Empty; }
if (original_Region == null) { original_Region = String.Empty; }
if (original_PostalCode == null) { original_PostalCode =
String.Empty; }
string sqlCmd = "UPDATE Employees " +
" SET FirstName = @FirstName, LastName =
@LastName, " +
" Address = @Address, City = @City, Region =
@Region, " +
" PostalCode = @PostalCode " +
" WHERE EmployeeID = @original_EmployeeID " +
" AND LastName = @original_LastName AND
FirstName = @original_FirstName " +
" AND Address = @original_Address AND City =
@original_City " +
" AND Region = @original_Region AND PostalCode
= @original_PostalCode";
SqlConnection conn = new SqlConnection(_connectionString);
SqlCommand cmd = new SqlCommand(sqlCmd, conn);
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 10).Value =
FirstName;
cmd.Parameters.Add("@LastName", SqlDbType.VarChar, 20).Value =
LastName;
cmd.Parameters.Add("@Address", SqlDbType.VarChar, 60).Value =
Address;
cmd.Parameters.Add("@City", SqlDbType.VarChar, 15).Value = City;
cmd.Parameters.Add("@Region", SqlDbType.VarChar, 15).Value =
Region;
cmd.Parameters.Add("@PostalCode", SqlDbType.VarChar, 10).Value
= PostalCode;
cmd.Parameters.Add("@original_EmployeeID", SqlDbType.Int).Value
= original_EmployeeID;
cmd.Parameters.Add("@original_FirstName", SqlDbType.VarChar,
10).Value = original_FirstName;
cmd.Parameters.Add("@original_LastName", SqlDbType.VarChar,
20).Value = original_LastName;
cmd.Parameters.Add("@original_Address", SqlDbType.VarChar,
60).Value = original_Address;
cmd.Parameters.Add("@original_City", SqlDbType.VarChar,
15).Value = original_City;
cmd.Parameters.Add("@original_Region", SqlDbType.VarChar,
15).Value = original_Region;
cmd.Parameters.Add("@original_PostalCode", SqlDbType.VarChar,
10).Value = original_PostalCode;
int result = 0;
try
{
conn.Open();
result = cmd.ExecuteNonQuery();
}
catch (SqlException e)
{
// Handle exception.
}
finally
{
conn.Close();
}
return result;
}
}
}
====================================
Thanks,
Steven Cheng
Microsoft Online Support
Get Secure!
www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
--------------------
| Thread-Topic: Looping through a filtered GridView
| thread-index: AcYQgP02l4TIi5weR3GXKRzUwu9GnQ==
| X-WBNR-Posting-Host: 130.76.32.145
| From: =?Utf-8?B?S2Vu?= <
[email protected]>
| References: <
[email protected]>
<
[email protected]>
<
[email protected]>
<
[email protected]>
<NSVL#
[email protected]>
| Subject: Re: Looping through a filtered GridView
| Date: Tue, 3 Jan 2006 08:16:01 -0800
| Lines: 82
| Message-ID: <
[email protected]>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.dotnet.framework.aspnet
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl
microsoft.public.dotnet.framework.aspnet:368290
| X-Tomcat-NG: microsoft.public.dotnet.framework.aspnet
|
| Thanks for replying Steven. Sorry about the taking so long to respond.
| Christmas vacation
|
| I have provided the code you requested below. The events on the page
appear
| to be firing in the following patterns:
| SEQUENCE OF EVENTS WHEN PAGE IS INITIALLY LOADED AFTER MENU SELECTION
| Page_Load
| GetContactList is called
| GetListCount is called
|
| SEQUENCE OF EVENTS WHEN SELECT PARAMETERS ARE APPLIED
| GetContactList is called (regionFilter parameter is not set)
| GetListCount is called (regionFilter parameter is not set)
| Page_Load
| GetContactList is called (regionFilter parameter has a value)
| GetListCount is called (regionFilter parameter has a value)
|
| SEQUENCE OF EVENTS WHEN DELETE BUTTON IS PRESSED
| GetContactList is called (regionFilter parameter is not set)
| GetListCount is called (regionFilter parameter is not set)
| Page_Load
| deleteContests_ServerClick (GridView contains unfiltered results)
| GetContactList is called (regionFilter parameter has a value)
| GetListCount is called (regionFilter parameter has a value)
|
| public static DataSet GetDiscountList(
| int pageSize
| , int currentRow
| , string sortExpression
| , bool dateFilter
| , int regionFilter
| )
| {
| DiscountDatabase db = new DiscountDatabase();
| SqlParameter[] parms = new SqlParameter[(dateFilter ? 7 : 5)];
|
| parms[0] = new SqlParameter("@pageSize", pageSize);
| if (regionFilter == 0)
| {
| parms[1] = new SqlParameter("@regionFilter", null);
| }
| else
| {
| parms[1] = new SqlParameter("@regionFilter", regionFilter);
| }
| //... set other parameters
|
|
| return db.GetDataSet("getDiscountList", parms);
| }
| public static int GetListCount(
| bool dateFilter
| , int regionFilter
| )
| {
| DiscountDatabase db = new DiscountDatabase();
| SqlParameter[] parms = new SqlParameter[(dateFilter ? 6 : 4)];
|
| parms[0] = new SqlParameter("@pageSize", int.MaxValue);
| if (regionFilter == 0)
| {
| parms[1] = new SqlParameter("@regionFilter", null);
| }
| else
| {
| parms[1] = new SqlParameter("@regionFilter", regionFilter);
| }
| //... set other parameters
|
| return (int)db.GetScaler("getDiscountList", parms);
| }
| public static void DeleteDiscounts(string discountList)
| {
| DiscountDatabase db = new DiscountDatabase();
| SqlParameter[] parms = new SqlParameter[1];
|
| parms[0] = new SqlParameter("@discountList", discountList);
|
| db.ExecuteProcedure("DeleteDiscounts", parms);
| }
|
|
|
