SqlDataAdapter optimize foreach

Question

This C# code is tested as working, but looks inefficient to me. How can I avoid all of the overhead in each foreach interation for say a 1,000 rows?

SqlConnection con = new SqlConnection(strConn);  // strConn is set elsewhere in the code
string strSQL = "select pkCustomer from Customer where YearEndDeactivateInd = 1";
SqlDataAdapter adap = new SqlDataAdapter(strSQL, con);
DataTable dt = new DataTable();
con.Open();
adap.Fill(dt);

foreach (DataRow row in dt.Rows)
{
    string strSQL1 = "update Ticket set activeInd = 0 where fkCustomer = " + row["pkCustomer"].ToString();
    SqlDataAdapter adap1 = new SqlDataAdapter(strSQL1, con);
    DataTable dt1 = new DataTable();
    adap1.Fill(dt1);
}

con.Close();

I am updating one table based on the results of another. Should I pursue using batch update instead?

Answer 1

Use query like this

update Ticket
set activeInd = 0
where fkCustomer in (
    select pkCustomer
    from Customer
    where YearEndDeactivateInd = 1
)

Do it without adapter

using (var con = new SqlConnection(strConn))
{
    string sql = "here query above";
    con.Open();

    using (var cmd = new SqlCommand(sql, con))
    {
        int rowsAffected = cmd.ExecuteNonQuery();
    }
}