What used to be simple is now simply confusing

[James R. Davis]

Yes, a newbie here.

Though I am making progress, slowly, I am also getting more and more
confused.

With ASP, when I wanted to do something as trivial as updating a visitor
counter, I connected to a database, executed a SQL command to read the
current value of a field into a recordset, updated the value by adding 1 and
writing the field back to the table, closed and got rid of the connection
and recordset. I had no concerns about how to get or use the information
read, or manipulate it.

Now I am confronted with issues such as whether or not to use a connected or
disconnected database access. Do I really need to build a dataadapter and
in-memory table for such a trivial function? Do I really need to be
concerned about data binding? Do I really need to use a databound control
at all?

Will someone please post the minimum ASP.NET instructions to perform this
trivial function for me? I suspect that under ASP.NET it is just as trivial
as it was under ASP, but as I said earlier, what used to be simple is now
simply confusing.

Thank you.

 

[Scott Roberts]

Yes, a newbie here.

Though I am making progress, slowly, I am also getting more and more
confused.

With ASP, when I wanted to do something as trivial as updating a visitor
counter, I connected to a database, executed a SQL command to read the
current value of a field into a recordset, updated the value by adding 1
and
writing the field back to the table, closed and got rid of the connection
and recordset. I had no concerns about how to get or use the information
read, or manipulate it.

Now I am confronted with issues such as whether or not to use a connected
or
disconnected database access. Do I really need to build a dataadapter and
in-memory table for such a trivial function? Do I really need to be
concerned about data binding? Do I really need to use a databound control
at all?

Will someone please post the minimum ASP.NET instructions to perform this
trivial function for me? I suspect that under ASP.NET it is just as
trivial
as it was under ASP, but as I said earlier, what used to be simple is now
simply confusing.

Thank you.

SqlConnection conn = new SqlConnection("YourConnectionString");
SqlCommand cmd = new SqlCommand("update MyTable set UserCounter =
UserCounter + 1", conn);
using (conn)
{
conn.Open();
cmd.ExecuteNonQuery();
}

 

[Scott M.]

The process doesn't need to be that much different than what you are doing
now albeit that some of the object have changed.

You still need a connection to the database and you can then just execute an
update statement to modify the db value.

This is not a job for DataAdapters and DataSets (disconnected data).

 

[James R. Davis]

Amazingly simple when you step away from the details. That accomplished 90%
of what I need. Thank you!

I assume you still must close or dispose of the connection. All that I need
now is to know how to gain access to the new value to set the text value of
a label. Can I get that value without having to re-read the field?

 

[Scott Roberts]

The "using" statement automatically disposes of the connection (which also
closes it). You can explicitly call "conn.Close();" if it makes you feel
better.

You'll need another command and a DataReader to get the new value.

// Goes right after "ExecuteNonQuery".
SqlCommand scmd = new SqlCommand("select UserCounter from MyTable", conn);
SqlDataReader dr = scmd.ExecuteReader();
if (dr.Read())
Label2.Text = dr.GetString(0);

 

[Scott Roberts]

Note also that the "old" way you were also executing 2 statements - 1 to
read then 1 to update. Now we're still executing 2 statements, but we're
doing the update first then the read. This is "safer" because the DB handles
concurrency issues for us.

 

[James R. Davis]

Scott,

You are amazingly generous with your help. Again, thank you!

 

[Coskun SUNALI [MVP]]

Hi,

To make some additions...

If you still insist on using only one command, you can execute both queries
at once and you can get the returning value using "ExecuteScalar" method of
the SqlCommand class. Just simple changes to what Scott wrote. I am just now
sure about if you need "+1" in second SQL statement, I haven't executed the
code but you still may need to save or remove it.

int currentCounter = 0;
SqlConnection conn = new SqlConnection("YourConnectionString");
SqlCommand cmd = new SqlCommand("update MyTable set UserCounter =
UserCounter + 1; Select UserCounter + 1 from MyTable;", conn);
using (conn)
{
conn.Open();
currentCounter = cmd.ExecuteScalar();
}

 

[LVP]

I am just wondering:
How can you guarantee that no one else updates the UserCounter in the table
before you read the UserCounter

LVP

 

[LVP]

What I mean is between your update and your read

Update ctr = ctr + 1 in table
<<<some one else updates it again>>>
you read it.

 

[Coskun SUNALI [MVP]]

Hi,

I am sorry but I don't understand the problem that I will have if someone
else updates the table before my read.

Current Value = 2
I visited web site
{
Counter incremented by 1;
Someone else visited web site
{
Counter incremented by 1;
}
}
I got the response: 4;
Someone else also got the response 4;

So actually the result is even better because I would always like to be sure
that I have the most correct value which is 4 here in this case, instead of
3 because someone else also visited the page at the given time.

And just because I want to remind you; what is being discused within this
topic is not the best practice to have an user counter but having an user
counter as much as simple.

--
All the best,
Coskun SUNALI
MVP ASP/ASP.NET
http://sunali.com

 

[LVP]

Okay,

I miss-understood the requirements.

LVP: