Use dynamic build statements in ASP. NET to efficiently delete resolution records in batches

Batch delete in the gridview. The general practice is to cycle to the checked items and delete the call process directly. In this case, I personally think the efficiency is not very high. If you want to delete hundreds of thousands of data, you have to call the trial for hundreds of times. In fact, we can use dynamic build deletion statements for efficient batch deletion. No matter how much data you choose to delete, you only need to call the trial once.

The stored procedure deletion statement written below has nothing to do with the preceding figure. The preceding figure shows a common batch deletion statement in the gridview. Use Stored procedures:

Code

Bytes ---------------------------------------------------------------------------------------
/*
* Author: lin sen
* Function description: Delete a dynamically constructed SQL statement
* Prepared on: July 15, September 27, 2010
 *
*/
Bytes ---------------------------------------------------------------------------------------
Drop procedure proc_deletemessage
Go
Create procedure proc_deletemessage
(
@ Condition varchar (500) -- delete conditions (multiple)
)
As
Begin
Declare @ SQL varchar (200)
-- Dynamically build deletion statements
Select @ SQL = 'delete from messageinfo where '+ @ condition
-- Trial statement
Exec (@ SQL)
End
Go

Call this process on the SQL query Analyzer: (the input condition is that the column name is uniquely identified and the selected value)

Exec proc_deletemessage 'messageid = 240 or messageid = 241 or messageid = 242 ...'

The SQL call is not clear. Let's look at the front-end call and trial in the web.

Code

// Delete button click event
Protected void lbtn_del_click (object sender, eventargs e)
        {
Stringbuilder sb = new stringbuilder ();
For (int I = 0; I <gv_class.rows.count; I ++)
            {
Checkbox = (checkbox) gv_class.rows [I]. findcontrol ("checkbox ");
If (checkbox. checked = false)
                {
Lab_note.text = "select the information to delete ";
Lab_note.style.add ("color", "red ");
                }
Else
                {
Messagemodel. c_id = int32.parse (gv_class.rows [I]. cells [3]. text. trim (); // The selected unique identifier column value
Sb. append ("messageid = ");
Sb. append (messagemodel. c_id );
Sb. append ("or ");
                }
            }
Sb. append ("messageid = null ");
Messagemodel. sqlstr = sb. tostring (); // The dynamic condition statement is passed to the object.
Int j = deleteclass (messagemodel );
//.....
        }
/// <Summary>
/// Delete information
/// </Summary>
/// <Param name = "memodel"> </param>
/// <Returns> </returns>
Public int deleteclass (messagemodel memodel)
        {
Int rowsaffected;
Sqlparameter [] parameter = {new sqlparameter ("@ sqlstr", sqldbtype.int )};
Parameter [0]. value = memodel. sqlstr;
Dbhelpersql. runintprocname ("proc_deletemessage", out rowsaffected, parameter );
Return rowsaffected;
        }

Pay attention to the following points when dynamically constructing and calling the web front-end:

1. The selected columns are generally unique primary key identifiers that can be deleted based on them.

2. If your primary key is a string that is not an integer, modify sb. append ("messageid = '"); sb. append (messagemodel. c_id );

Sb. append ("'or"); only multiple single quotes.

3. Add sb. append ("messageid = null"); after the loop ends. I personally think that the primary key cannot be empty, so this condition is excluded. Multiple Dynamic statements that are not added or;

However, do not add or 1 = 1 in the condition. The entire data is deleted no matter how many values are set.

4. Input the constructed statement with parameters as the deletion condition.