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
ObjectDataSourc e'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. ObjectDataSourc e
{
//
// Northwind Employee Data Factory
//
public class NorthwindData
{
private string _connectionStri ng;
public NorthwindData()
{
Initialize();
}
public void Initialize()
{
// Initialize data source. Use "Northwind" connection string
from configuration.
if (ConfigurationM anager.Connecti onStrings["Northwind"] == null
||
ConfigurationMa nager.Connectio nStrings["Northwind"].ConnectionStri ng.Trim()
== "")
{
throw new Exception("A connection string named 'Northwind'
with a valid connection string " +
"must exist in the <connectionStri ngs>
configuration section for the application.");
}
_connectionStri ng =
ConfigurationMa nager.Connectio nStrings["Northwind"].ConnectionStri ng;
}
// Select all employees.
public DataTable GetAllEmployees (string sortColumns, int
startRecord, int maxRecords, int eid)
{
VerifySortColum ns(sortColumns) ;
string sqlCmd = "SELECT EmployeeID, LastName, FirstName,
Address, City, Region, PostalCode FROM Employees ";
//test code
HttpContext.Cur rent.Trace.Warn ("<br>GetAllEmp loyees, eid: " +
eid);
//eid = -1;
if(eid > 0)
{
sqlCmd += " WHERE EmployeeID > @eid ";
}
if (sortColumns.Tr im() == "")
sqlCmd += "ORDER BY EmployeeID";
else
sqlCmd += "ORDER BY " + sortColumns;
SqlConnection conn = new SqlConnection(_ connectionStrin g);
SqlDataAdapter da = new SqlDataAdapter( sqlCmd, conn);
if(eid > 0)
{
da.SelectComman d.Parameters.Ad d("@eid",SqlDbT ype.Int);
da.SelectComman d.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.Cur rent.Trace.Warn ("<br>SelectCou nt, eid: " + eid);
//eid = 0;
if (eid > 0)
{
sqlCmd += " WHERE EmployeeID > @eid ";
}
SqlConnection conn = new SqlConnection(_ connectionStrin g);
SqlCommand cmd = new SqlCommand(sqlC md, conn);
if (eid > 0)
{
cmd.Parameters. Add("@eid", SqlDbType.Int);
cmd.Parameters["@eid"].Value = eid;
}
int result = 0;
try
{
conn.Open();
result = (int)cmd.Execut eScalar();
}
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 VerifySortColum ns(string sortColumns)
{
if (sortColumns.To LowerInvariant( ).EndsWith(" desc"))
sortColumns = sortColumns.Sub string(0, sortColumns.Len gth -
5);
string[] columnNames = sortColumns.Spl it(',');
foreach (string columnName in columnNames)
{
switch (columnName.Tri m().ToLowerInva riant())
{
case "employeeid ":
break;
case "lastname":
break;
case "firstname" :
break;
case "":
break;
default:
throw new ArgumentExcepti on("SortColumn s contains
an invalid column name.");
break;
}
}
}
// Select an employee.
public DataTable GetEmployee(int EmployeeID)
{
SqlConnection conn = new SqlConnection(_ connectionStrin g);
SqlDataAdapter da =
new SqlDataAdapter( "SELECT EmployeeID, LastName, FirstName,
Address, City, Region, PostalCode " +
" FROM Employees WHERE EmployeeID =
@EmployeeID", conn);
da.SelectComman d.Parameters.Ad d("@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(_ connectionStrin g);
SqlCommand cmd = new SqlCommand("DEL ETE FROM Employees WHERE
EmployeeID = @EmployeeID", conn);
cmd.Parameters. Add("@EmployeeI D", SqlDbType.Int). Value =
EmployeeID;
int result = 0;
try
{
conn.Open();
result = cmd.ExecuteNonQ uery();
}
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.Cur rent.Trace.Warn ("<br>UpdateEmp loyee");
if (String.IsNullO rEmpty(FirstNam e))
throw new ArgumentExcepti on("FirstName cannot be null or an
empty string.");
if (String.IsNullO rEmpty(LastName ))
throw new ArgumentExcepti on("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(_ connectionStrin g);
SqlCommand cmd = new SqlCommand("UPD ATE Employees " +
" SET
FirstName=@Firs tName, LastName=@LastN ame, " +
" Address=@Addres s,
City=@City, Region=@Region, " +
" PostalCode=@Pos talCode "
+
" WHERE
EmployeeID=@Emp loyeeID", conn);
cmd.Parameters. Add("@FirstName ", SqlDbType.VarCh ar, 10).Value =
FirstName;
cmd.Parameters. Add("@LastName" , SqlDbType.VarCh ar, 20).Value =
LastName;
cmd.Parameters. Add("@Address", SqlDbType.VarCh ar, 60).Value =
Address;
cmd.Parameters. Add("@City", SqlDbType.VarCh ar, 15).Value = City;
cmd.Parameters. Add("@Region", SqlDbType.VarCh ar, 15).Value =
Region;
cmd.Parameters. Add("@PostalCod e", SqlDbType.VarCh ar, 10).Value
= PostalCode;
cmd.Parameters. Add("@EmployeeI D", SqlDbType.Int). Value =
EmployeeID;
int result = 0;
try
{
conn.Open();
result = cmd.ExecuteNonQ uery();
}
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.IsNullO rEmpty(FirstNam e))
throw new ArgumentExcepti on("FirstName cannot be null or an
empty string.");
if (String.IsNullO rEmpty(LastName ))
throw new ArgumentExcepti on("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(_ connectionStrin g);
SqlCommand cmd = new SqlCommand("INS ERT INTO Employees " +
" (FirstName, LastName,
Address, City, Region, PostalCode) " +
" Values(@FirstNa me,
@LastName, @Address, @City, @Region, @PostalCode); " +
"SELECT @EmployeeID =
SCOPE_IDENTITY( )", conn);
cmd.Parameters. Add("@FirstName ", SqlDbType.VarCh ar, 10).Value =
FirstName;
cmd.Parameters. Add("@LastName" , SqlDbType.VarCh ar, 20).Value =
LastName;
cmd.Parameters. Add("@Address", SqlDbType.VarCh ar, 60).Value =
Address;
cmd.Parameters. Add("@City", SqlDbType.VarCh ar, 15).Value = City;
cmd.Parameters. Add("@Region", SqlDbType.VarCh ar, 15).Value =
Region;
cmd.Parameters. Add("@PostalCod e", SqlDbType.VarCh ar, 10).Value
= PostalCode;
SqlParameter p = cmd.Parameters. Add("@EmployeeI D",
SqlDbType.Int);
p.Direction = ParameterDirect ion.Output;
int newEmployeeID = 0;
try
{
conn.Open();
cmd.ExecuteNonQ uery();
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_Employ eeID, string
original_LastNa me,
string original_FirstN ame, string
original_Addres s,
string original_City, string
original_Region ,
string original_Postal Code)
{
if (String.IsNullO rEmpty(original _FirstName))
throw new ArgumentExcepti on("FirstName cannot be null or an
empty string.");
if (String.IsNullO rEmpty(original _LastName))
throw new ArgumentExcepti on("LastName cannot be null or an
empty string.");
if (original_Addre ss == null) { original_Addres s =
String.Empty; }
if (original_City == null) { original_City = String.Empty; }
if (original_Regio n == null) { original_Region = String.Empty; }
if (original_Posta lCode == null) { original_Postal Code =
String.Empty; }
string sqlCmd = "DELETE FROM Employees WHERE EmployeeID =
@original_Emplo yeeID " +
" AND LastName = @original_LastN ame AND
FirstName = @original_First Name " +
" AND Address = @original_Addre ss AND City =
@original_City " +
" AND Region = @original_Regio n AND PostalCode
= @original_Posta lCode";
SqlConnection conn = new SqlConnection(_ connectionStrin g);
SqlCommand cmd = new SqlCommand(sqlC md, conn);
cmd.Parameters. Add("@original_ EmployeeID", SqlDbType.Int). Value
= original_Employ eeID;
cmd.Parameters. Add("@original_ FirstName", SqlDbType.VarCh ar,
10).Value = original_FirstN ame;
cmd.Parameters. Add("@original_ LastName", SqlDbType.VarCh ar,
20).Value = original_LastNa me;
cmd.Parameters. Add("@original_ Address", SqlDbType.VarCh ar,
60).Value = original_Addres s;
cmd.Parameters. Add("@original_ City", SqlDbType.VarCh ar,
15).Value = original_City;
cmd.Parameters. Add("@original_ Region", SqlDbType.VarCh ar,
15).Value = original_Region ;
cmd.Parameters. Add("@original_ PostalCode", SqlDbType.VarCh ar,
10).Value = original_Postal Code;
int result = 0;
try
{
conn.Open();
result = cmd.ExecuteNonQ uery();
}
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_Employ eeID, string
original_LastNa me,
string original_FirstN ame, string
original_Addres s,
string original_City, string
original_Region ,
string original_Postal Code)
{
if (String.IsNullO rEmpty(FirstNam e))
throw new ArgumentExcepti on("FirstName cannot be null or an
empty string.");
if (String.IsNullO rEmpty(LastName ))
throw new ArgumentExcepti on("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_Addre ss == null) { original_Addres s =
String.Empty; }
if (original_City == null) { original_City = String.Empty; }
if (original_Regio n == null) { original_Region = String.Empty; }
if (original_Posta lCode == null) { original_Postal Code =
String.Empty; }
string sqlCmd = "UPDATE Employees " +
" SET FirstName = @FirstName, LastName =
@LastName, " +
" Address = @Address, City = @City, Region =
@Region, " +
" PostalCode = @PostalCode " +
" WHERE EmployeeID = @original_Emplo yeeID " +
" AND LastName = @original_LastN ame AND
FirstName = @original_First Name " +
" AND Address = @original_Addre ss AND City =
@original_City " +
" AND Region = @original_Regio n AND PostalCode
= @original_Posta lCode";
SqlConnection conn = new SqlConnection(_ connectionStrin g);
SqlCommand cmd = new SqlCommand(sqlC md, conn);
cmd.Parameters. Add("@FirstName ", SqlDbType.VarCh ar, 10).Value =
FirstName;
cmd.Parameters. Add("@LastName" , SqlDbType.VarCh ar, 20).Value =
LastName;
cmd.Parameters. Add("@Address", SqlDbType.VarCh ar, 60).Value =
Address;
cmd.Parameters. Add("@City", SqlDbType.VarCh ar, 15).Value = City;
cmd.Parameters. Add("@Region", SqlDbType.VarCh ar, 15).Value =
Region;
cmd.Parameters. Add("@PostalCod e", SqlDbType.VarCh ar, 10).Value
= PostalCode;
cmd.Parameters. Add("@original_ EmployeeID", SqlDbType.Int). Value
= original_Employ eeID;
cmd.Parameters. Add("@original_ FirstName", SqlDbType.VarCh ar,
10).Value = original_FirstN ame;
cmd.Parameters. Add("@original_ LastName", SqlDbType.VarCh ar,
20).Value = original_LastNa me;
cmd.Parameters. Add("@original_ Address", SqlDbType.VarCh ar,
60).Value = original_Addres s;
cmd.Parameters. Add("@original_ City", SqlDbType.VarCh ar,
15).Value = original_City;
cmd.Parameters. Add("@original_ Region", SqlDbType.VarCh ar,
15).Value = original_Region ;
cmd.Parameters. Add("@original_ PostalCode", SqlDbType.VarCh ar,
10).Value = original_Postal Code;
int result = 0;
try
{
conn.Open();
result = cmd.ExecuteNonQ uery();
}
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: AcYQgP02l4TIi5w eR3GXKRzUwu9GnQ ==
| X-WBNR-Posting-Host: 130.76.32.145
| From: =?Utf-8?B?S2Vu?= <ke**@newsgroup s.nospam>
| References: <E9************ *************** *******@microso ft.com>
<11************ **********@g14g 2000cwa.googleg roups.com>
<$8************ **@TK2MSFTNGXA0 2.phx.gbl>
<0B************ *************** *******@microso ft.com>
<NS************ **@TK2MSFTNGXA0 2.phx.gbl>
| Subject: Re: Looping through a filtered GridView
| Date: Tue, 3 Jan 2006 08:16:01 -0800
| Lines: 82
| Message-ID: <6F************ *************** *******@microso ft.com>
| 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.publi c.dotnet.framew ork.aspnet
| NNTP-Posting-Host: TK2MSFTNGXA03.p hx.gbl 10.40.2.250
| Path: TK2MSFTNGXA02.p hx.gbl!TK2MSFTN GP08.phx.gbl!TK 2MSFTNGXA03.phx .gbl
| Xref: TK2MSFTNGXA02.p hx.gbl
microsoft.publi c.dotnet.framew ork.aspnet:3682 90
| X-Tomcat-NG: microsoft.publi c.dotnet.framew ork.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
| )
| {
| DiscountDatabas e db = new DiscountDatabas e();
| 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
| )
| {
| DiscountDatabas e db = new DiscountDatabas e();
| 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.GetScal er("getDiscount List", parms);
| }
| public static void DeleteDiscounts (string discountList)
| {
| DiscountDatabas e db = new DiscountDatabas e();
| SqlParameter[] parms = new SqlParameter[1];
|
| parms[0] = new SqlParameter("@ discountList", discountList);
|
| db.ExecuteProce dure("DeleteDis counts", parms);
| }
|
|
|
