Calling a DataSource

[Gerhard]

Hi,

I have an application that uses an aps:GridView. The GridView is populated
using a DataSource that calls a StoredProcedure. The DataSource passes
parameters to the StoredProcedure based on some text fields. The page has
been running slower than wanted, so I was doing some checking. I ran SQL
Profiler while loading the page, and saw the StoredProcedure was being called
twice. I stepped through the code and found out that in the Page Load
Complete event was where this was happening. In this event, I set the value
of a parameter for the StoredProcedure, then did a DataBind on the GridView
to pick up the data. As I was stepping through the code, I watched the SQL
Profiler. When I got to the step of setting the parameter, the
StoredProcedure had not yet been called. When I stepped through that and got
to the DataBind command (before runnning that step), the SQL Profiler showed
that the StoredProcedure had been called. Then I did the DataBind and the
StoredProcedure was called again. My question is, how do I get the
parameters set, and get the GridView data bound, and only call the
StoredProcedure once? If I comment out the DataBind step, the
StoredProcedure never does get called (which seems really strange to me...).
Can you help me on this? Thanks.

 

[Gregory A. Beamer]

Hi,

I have an application that uses an aps:GridView. The GridView is
populated using a DataSource that calls a StoredProcedure. The
DataSource passes parameters to the StoredProcedure based on some text
fields. The page has been running slower than wanted, so I was doing
some checking. I ran SQL Profiler while loading the page, and saw the
StoredProcedure was being called twice. I stepped through the code
and found out that in the Page Load Complete event was where this was
happening. In this event, I set the value of a parameter for the
StoredProcedure, then did a DataBind on the GridView to pick up the
data. As I was stepping through the code, I watched the SQL Profiler.
When I got to the step of setting the parameter, the StoredProcedure
had not yet been called. When I stepped through that and got to the
DataBind command (before runnning that step), the SQL Profiler showed
that the StoredProcedure had been called. Then I did the DataBind and
the StoredProcedure was called again. My question is, how do I get
the parameters set, and get the GridView data bound, and only call the
StoredProcedure once? If I comment out the DataBind step, the
StoredProcedure never does get called (which seems really strange to
me...). Can you help me on this? Thanks.

Call in code page or the tagged page, not both.

If you have bound using the drag and drop, you are already binding in
the tagged page, whether you realize it or not. If you then use code to
bind, you are calling twice.

If this is your issue (as I believe it is, but I have seen no code to
confirm my feelings on this), you have to decide which methodology you
are going to use and get rid of the other.

Peace and Grace,

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

My vacation and childhood cancer awareness site:
http://www.crazycancertour.com

*******************************************
| Think outside the box! |
*******************************************

 

[Gerhard]

Thanks.

I'm not quite clear on this, so please be patient.

I have the GridView linked to a DataSource on the page. The DataSource has
parameters linked to various controls. In the code, I assign values to the
controls.

In the Load Complete event, if I don't call the GridView data bind, the
GridView doesn't get populated. If I do call it, it does get populated, but
the StoredProcedure in the DataSource gets called twice.

Seems like the way to go is to do the data bind in the code, but how do I
set it up so it isn't called in what you call the tagged page (this is a term
I am afraid I am not familar with, I did a search and didn't come up with a
definition, just lots of places it was used without a definition)? Do I just
not associate the GridView with a DataSource originally? Please clarify.

I really appreciate your help on this.

 

[Gregory A. Beamer]

Thanks.

I'm not quite clear on this, so please be patient.

I have the GridView linked to a DataSource on the page. The
DataSource has parameters linked to various controls. In the code, I
assign values to the controls.

In the Load Complete event, if I don't call the GridView data bind,
the GridView doesn't get populated. If I do call it, it does get
populated, but the StoredProcedure in the DataSource gets called
twice.

Seems like the way to go is to do the data bind in the code, but how
do I set it up so it isn't called in what you call the tagged page
(this is a term I am afraid I am not familar with, I did a search and
didn't come up with a definition, just lots of places it was used
without a definition)? Do I just not associate the GridView with a
DataSource originally? Please clarify.

I really appreciate your help on this.

Since you have used drag and drop, the bind should be called. I am not
sure, without seeing code, why it is not being called.

One thing to possibly try is using the Page_Load and calling
Page.DataBind() and see if that does a double call to the stored
procedure.

If this does not work, you might be better to move away from the
DataSource control (SqlDataSource, I assume, as this is SQL Server?) and
manually bind.

A manual bind will look like this (with a dataset) - hope C# is your
language of choice:

string connString = ConfigurationManager.ConnectionStrings
["myConnString"].ConnectionString;
string sproc = "{stored proc name here}";

SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(sproc, conn);
cmd.CommandType = CommandType.StoredProcedure;

//if there are parameters
cmd.AddWithValue("@paramName", paramName1);

DataSet ds = new DataSet("{dataset name optional}");
SqlDataAdapter da = new SqlDataAdapter(cmd);

//Can add mappings to friendly name tables, if more than one
da.TableMappings.Add("Table", "{better name here}");

//Note that this try ... finally is equivalent to
//using(SqlConnection conn = new SqlConnection(connString))
//{
//}
try
{
conn.Open();
da.Fill(ds);
}
finally
{
conn.Dispose()
}

MyGridView.DataSource = ds;
MyGridView.DataBind();

The SqlDataSource control encapsulates this type of logic, so it does
the same thing, but it hides it from you.

Peace and Grace,


--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

My vacation and childhood cancer awareness site:
http://www.crazycancertour.com

*******************************************
| Think outside the box! |
*******************************************

 

[Gerhard]

Thanks.

It looks like it works the way I want if I set the GridView DataSource to ""
in the page load event (if not post back or call back), then after setting
the param variables in the load complete event, setting the DataSource back.
In the tests I have done, that seems to do the trick. Do you see any
possible pitfalls in this? The only one I could think of was paging, and
that looks like it works okay.

Thanks again for your assistance.

Thanks.

I'm not quite clear on this, so please be patient.

I have the GridView linked to a DataSource on the page. The
DataSource has parameters linked to various controls. In the code, I
assign values to the controls.

In the Load Complete event, if I don't call the GridView data bind,
the GridView doesn't get populated. If I do call it, it does get
populated, but the StoredProcedure in the DataSource gets called
twice.

Seems like the way to go is to do the data bind in the code, but how
do I set it up so it isn't called in what you call the tagged page
(this is a term I am afraid I am not familar with, I did a search and
didn't come up with a definition, just lots of places it was used
without a definition)? Do I just not associate the GridView with a
DataSource originally? Please clarify.

I really appreciate your help on this.

Since you have used drag and drop, the bind should be called. I am not
sure, without seeing code, why it is not being called.

One thing to possibly try is using the Page_Load and calling
Page.DataBind() and see if that does a double call to the stored
procedure.

If this does not work, you might be better to move away from the
DataSource control (SqlDataSource, I assume, as this is SQL Server?) and
manually bind.

A manual bind will look like this (with a dataset) - hope C# is your
language of choice:

string connString = ConfigurationManager.ConnectionStrings
["myConnString"].ConnectionString;
string sproc = "{stored proc name here}";

SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(sproc, conn);
cmd.CommandType = CommandType.StoredProcedure;

//if there are parameters
cmd.AddWithValue("@paramName", paramName1);

DataSet ds = new DataSet("{dataset name optional}");
SqlDataAdapter da = new SqlDataAdapter(cmd);

//Can add mappings to friendly name tables, if more than one
da.TableMappings.Add("Table", "{better name here}");

//Note that this try ... finally is equivalent to
//using(SqlConnection conn = new SqlConnection(connString))
//{
//}
try
{
conn.Open();
da.Fill(ds);
}
finally
{
conn.Dispose()
}

MyGridView.DataSource = ds;
MyGridView.DataBind();

The SqlDataSource control encapsulates this type of logic, so it does
the same thing, but it hides it from you.

Peace and Grace,


--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

My vacation and childhood cancer awareness site:
http://www.crazycancertour.com

*******************************************
| Think outside the box! |
*******************************************

 

[Gregory A. Beamer]

Thanks.

It looks like it works the way I want if I set the GridView DataSource
to "" in the page load event (if not post back or call back), then
after setting the param variables in the load complete event, setting
the DataSource back. In the tests I have done, that seems to do the
trick. Do you see any possible pitfalls in this? The only one I
could think of was paging, and that looks like it works okay.

The only "pitfall" I can see offhand is a bit less clarity in the code.
What you are doing is forcing the automagic bits to short circuit. You
may be burning a few cycles, but this is probably not a huge concern.

And, it might be better to distance yourself from it and get the
appplication logic solved. When you get a chance to experiment, you may
find the solution later.

This should not affect paging, as event handlers run after load. Same
with sorting, if you use it.

I would still search for an answer when you have time, but it might be
better to get your mind off of it for now. ;-)

Peace and Grace,


--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

My vacation and childhood cancer awareness site:
http://www.crazycancertour.com

*******************************************
| Think outside the box! |
*******************************************