How can I do that by using single query in C#?

I want to make passive them if a date is earlier than today from the recorded dates within database with update command.How can I do that by using single query in C#?

Colums: Tarih(nvarchar) , Durum(nvarchar)

For example;

if tarih<Datetime.Today.Date is set Durum='PASİF' 

Code:

string sorgu = "select BitTarihi from Abonelikler";
        SqlConnection gg = new SqlConnection(constr);
        SqlCommand gg2 = new SqlCommand(sorgu,gg);
        SqlDataReader gg3;
        gg.Open();
       gg3= gg2.ExecuteReader();
       while (gg3.Read())
       {
           DateTime b1 = new DateTime();
           DateTime b2 = new DateTime();
           b1 = Convert.ToDateTime(gg3.GetString(0));
           b2 = DateTime.Today.Date;
           TimeSpan fark = new TimeSpan();
           fark = b2 - b1;
           if (fark.TotalDays > 0)
           {
               SqlCon开发者_如何学Gonection vv = new SqlConnection(constr);
               SqlCommand vv2 = new SqlCommand("update Abonelikler set Durum='PASİF' where BitTarihi='" + b1.ToShortDateString() + "'", vv);
               vv.Open();
               vv2.ExecuteNonQuery();
               vv.Close();
               vv2.Dispose();

           }


       }
        gg.Close();
       gg2.Dispose();

---

Look at DATEDIFF function.

UPDATE Abonelikler SET Durum='PASIF' 
    WHERE DATEDIFF(DD, CONVERT(datetime, BitTarihi), GETDATE())>0

Also, please do not use concatenation of an SQL statement, use parameters. It's more safe and also sometimes concatenation of statement with parameters hampers server-side statement optimization.

---

Instead of updating the records why don't you decide the state at query level? Since you check this everyday there will be many unneeded updates.

SELECT BitTarihi, CASE WHEN DATEDIFF(DAY, BitTarihi, GetDate()) = 0 THEN 'AKTIF' ELSE 'PASIF' END DURUM FROM Abonelikler 

This way you won't have to update your records.