How to write the IFEXISTS condition and update the record from the codebehind?

I want to write a IFEXISTS condition and update the record from the code behind I don't know whether its possible or not...

if its possible then someone please tell me the syntax for that..

How to update the 开发者_运维问答record..

I want to write something like this.. but what is correct?

public void UpdateModalitiesId(int? CaseId, int ModalitiesId)

    {
        string query = "if exists (select count(*) from ImageModality where ImageModality.Id='"+ ModalitiesId +"')
                               UPDATE ImageGroup set ImageModalityId='" + ModalitiesId + "' where BaseCaseId='" + CaseId +"' ;
        //string query = "UPDATE ImageGroup set ImageModalityId='" + ModalitiesId + "' where BaseCaseId='" + CaseId + "'";
        SqlHelper.ExecuteNonQuery(strConnectionString, CommandType.Text, query);
    }

---

First, the statement select count(*) will always return at least one record, so your condition will always return TRUE. So you need condition like

IF (Select count(*) FROM ...) > 0 

Next, if you need to update the record using your condition, you can use the IF .. BEGIN .. END statement. It is not necessarily to use BEGIN .. END, You can use your UPDATE immediatelly after IF condition, but in future, if you will modify your code, you can avoid the logic errors.

So, your code will be like this:

public void UpdateModalitiesId(int? CaseId, int ModalitiesId)

    {
        string query = "if (select count(*) from ImageModality where ImageModality.Id='"+ ModalitiesId +"') > 0 BEGIN
                               UPDATE ImageGroup set ImageModalityId='" + ModalitiesId + "' where BaseCaseId='" + CaseId +"; END' ;
        SqlHelper.ExecuteNonQuery(strConnectionString, CommandType.Text, query);
    }

EDIT: And the correct code is

public void UpdateModalitiesId(int? CaseId, int ModalitiesId)

    {
        string query = "if EXISTS(select * from ImageModality where ImageModality.Id='"+ ModalitiesId +"') 0 BEGIN
                               UPDATE ImageGroup set ImageModalityId='" + ModalitiesId + "' where BaseCaseId='" + CaseId +"; END' ;
        SqlHelper.ExecuteNonQuery(strConnectionString, CommandType.Text, query);
    }

---

Personally, I'd consider not testing first and just doing an UPDATE

Doing both requires 2 table touches for basically the same SELECT..WHERE...

So an UPDATE only will mean zero rows get updated if there are no rows. Aand the table is tiuched once

It's also possible that the row is INSERTED after EXISTS but before UPDATE under heavy load.

---

You can check out something on these lines

IF EXISTS(SELECT count(*) from ImageModality where ImageModality.Id= ID > x)
BEGIN     
     UPDATE ImageGroup set ImageModalityId ....
END