Select and Update together

  • Thread starter Thread starter ALI-R
  • Start date Start date
A

ALI-R

I am going to select buch of rows from database and update a field in them
,is there a good way of doing that ?

thanks for your help.
Ali-R
 
Ali:

I'm not sure I understand the utlimate objective, but it sounds like you can
use an Update statement with a Select in a Subquery for instance or a join.
If the values are constant and don't require user intervention - I'd lean
toward doing it on the server exclusively. If you let me knwo a little more
about the scenario - I'll be glad to do what I can.
 
I have a table of alerts which has a column called "Viewed"(default is 0
which means ,row hasn't been viewed) ,I want to show alerts for an specific
user and then update "viewed" column to 1 (which means that alert has been
viewed).

thanks for your help.
Ali-R
 
U¿ytkownik "ALI-R said:
I have a table of alerts which has a column called "Viewed"(default is 0
which means ,row hasn't been viewed) ,I want to show alerts for an
specific
user and then update "viewed" column to 1 (which means that alert has been
viewed).

thanks for your help.
Ali-R
Click to expand...

If you use SQL Server you can do it, for example:

class Class1
{
[STAThread]
static void Main(string[] args)
{
Test t = new Test();
t.MakeTable();
t.ReadAndUpdate();
System.Console.ReadLine();
}
class Test
{
SqlConnection con = new SqlConnection(
"Persist Security Info=False;Integrated Security=SSPI;" +
"database=Testy;server=(local");
public void MakeTable()
{
con.Open();

string sql =
"Create Table myTest(" +
"Id Int Identity(1,1)," +
"Name VarChar(30)," +
"LastRead SmallDateTime);" +
//insert one record to the table
"Insert Into myTest (Name, LastRead) Values('Ben', GetDate())";

SqlCommand myC = new SqlCommand(sql, con);
try
{
myC.ExecuteNonQuery();
}
catch(SqlException ex)
{
System.Console.WriteLine(ex.Message);
}
con.Close();
}
public void ReadAndUpdate()
{
con.Open();
string sql =
"Select * From myTest;" +
//update records in table
"Update myTest Set LastRead = GetDate()";

SqlCommand myC = new SqlCommand(sql, con);
SqlDataReader myRead = myC.ExecuteReader();
myRead.Read();

System.Console.WriteLine("Id={0}, Name={1}, " +
"Last read date time={2}",
myRead.GetInt32(0).ToString(),
myRead.GetString(1),
myRead.GetDateTime(2).ToLongTimeString());

myRead.Close();
con.Close();
}

}
}

In Access db you must use additional update command.

Regards,
Grzegorz
 
Is it possibel to read the data into the datset ,itereate through it
,showing data ,then changing the datset and updating adapeter to update the
datasource??

Thansk for your help

Grzegorz Danowski said:
U¿ytkownik "ALI-R said:
I have a table of alerts which has a column called "Viewed"(default is 0
which means ,row hasn't been viewed) ,I want to show alerts for an
specific
user and then update "viewed" column to 1 (which means that alert has been
viewed).

thanks for your help.
Ali-R
Click to expand...

If you use SQL Server you can do it, for example:

class Class1
{
[STAThread]
static void Main(string[] args)
{
Test t = new Test();
t.MakeTable();
t.ReadAndUpdate();
System.Console.ReadLine();
}
class Test
{
SqlConnection con = new SqlConnection(
"Persist Security Info=False;Integrated Security=SSPI;" +
"database=Testy;server=(local");
public void MakeTable()
{
con.Open();

string sql =
"Create Table myTest(" +
"Id Int Identity(1,1)," +
"Name VarChar(30)," +
"LastRead SmallDateTime);" +
//insert one record to the table
"Insert Into myTest (Name, LastRead) Values('Ben', GetDate())";

SqlCommand myC = new SqlCommand(sql, con);
try
{
myC.ExecuteNonQuery();
}
catch(SqlException ex)
{
System.Console.WriteLine(ex.Message);
}
con.Close();
}
public void ReadAndUpdate()
{
con.Open();
string sql =
"Select * From myTest;" +
//update records in table
"Update myTest Set LastRead = GetDate()";

SqlCommand myC = new SqlCommand(sql, con);
SqlDataReader myRead = myC.ExecuteReader();
myRead.Read();

System.Console.WriteLine("Id={0}, Name={1}, " +
"Last read date time={2}",
myRead.GetInt32(0).ToString(),
myRead.GetString(1),
myRead.GetDateTime(2).ToLongTimeString());

myRead.Close();
con.Close();
}

}
}

In Access db you must use additional update command.

Regards,
Grzegorz
Click to expand...
 
U¿ytkownik "ALI-R said:
Is it possibel to read the data into the datset ,itereate through it
,showing data ,then changing the datset and updating adapeter to update
the
datasource??
Click to expand...
(...)

Yes, you can also use DataSet. Personally, I would prefer to use stored
procedure to retrieve (and update in the same moment) data from server
because:
- using dataadapter in so situation may cause high network traffic,
- if the sp is only one way to retrieve data from the database(user has no
permission to read table in direct way) you will have gurantee that table
contains true information whether or not row was viewed (in any way).
Of course not every database has stored procedures.

Regards,
Grzegorz
 
Back
Top