CODEWITHSUNDEEP
c#sql
LesterNotTheMolestor
LesterNotTheMolestor

Reputation: 141

Deleting Several Rows with a condition

Hello trying to delete several rows of the table according to a condition, however i need some help with it, basically i want to delete all rows, while the condition is true and then stop and leave the rest untouched.

EDIT: Regarding the comments i apologize, im fairly new to programming, sorry if not doing things correctly im new to this website as well.

private void button1_Click(object sender, EventArgs e)
{
   string varsql2check = ""; 
   do{
        SqlConnection conn = new SqlConnection(@"Data Source=.\wintouch;Initial Catalog=bbl;User ID=sa;Password=Pa$$w0rd");
        conn.Open();
        string varsql = "DELETE FROM wgcdoccab WHERE 'tipodoc' ='FSS' and 'FP' "; //sql query

        SqlCommand cmd = new SqlCommand(varsql, conn);

        SqlDataReader dr = cmd.ExecuteReader();

     } while(varsql2check = "SELECT * from wgcdoccab where 'tipodoc' !='FSS' and !='FP'  and contribuinte !='999999990' and  datadoc != CONVERT(varchar(10),(dateadd(dd, -1, getdate())),120);");

        dr.Close();
        conn.Close();
 }

Upvotes: 0

Views: 143

Answers (1)

Gnqz
Gnqz

Reputation: 3382

What you need to do is:

private void button1_Click(object sender, EventArgs e)
{
     bool check = true;
     do
     {
          string connectionString = @"Data Source=.\wintouch;Initial Catalog=bbl;User ID=sa;Password=Pa$$w0rd";
          string queryString = string.Empty;
          using (SqlConnection connection = new SqlConnection(connectionString))
          {
               connection.Open();
               queryString = "DELETE FROM wgcdoccab WHERE 'tipodoc' ='FSS' and 'FP' ";
               SqlCommand command = new SqlCommand(queryString, connection);
               command.Connection.Open();
               command.ExecuteNonQuery();
           }
           using (SqlConnection connection = new SqlConnection(connectionString))
           {
                connection.Open();
                queryString = "SELECT * from wgcdoccab where 'tipodoc' !='FSS' and !='FP'  and contribuinte !='999999990' and  datadoc != CONVERT(varchar(10),(dateadd(dd, -1, getdate())),120)";

                using (SqlCommand command = new SqlCommand(queryString, connection))
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        check = true;
                    }
                    else
                    {
                        check = false;
                    }
                }
           }          
      } 
      while (check);
}

Generally what I have done by editing your code is:

  1. Add the using statement in order to release the resources from the established connections.

  2. You should be using the returned types from the exeqution of the queries. The ExecuteNonQuery() will return the numbers of rows affected, in our case we are particularly interested in the rows returned from the select statement after the delete query. We create a reader and depending of the number of rows, in our case we are only interested if there are rows or no, branch accoringly. If we get no rows from the select (everything is deleted) we just continue, if we get nothing (reader.HasRows returns false) we repeat the delete query and check again. Simple as that.

Upvotes: 1

Related Questions