Asp. NET uses dynamic construction statement to realize efficient batch deletion

Bulk deletion in the Web site is common, especially for bulk deletions in the GridView. Our general practice is to loop to the tick to delete the call procedure directly. In this way, individuals feel that the efficiency is not very high, if hundreds of thousands of data to be deleted, it will have to invoke hundreds of trials. In fact, we can use dynamic build DELETE statements for efficient bulk deletion, no matter how much you choose to delete the data, just call the trial once.

The stored procedure deletion statement below has nothing to do with the above diagram, which is common in the GridView for bulk deletion. Using Stored procedures:

 
 

  
  

  
---------------------------------------------------------------------------------------
  
  

  
* Author: Lin Sen * Function Description: Dynamic construction of the deletion of SQL statements * written Date: September 27, 2010 **/
  
  

  
---------------------------------------------------------------------------------------
  
  

  
drop procedure Proc_deletemessagegocreate Procedure Proc_deletemessage (
  
  

  
@condition
  
  

  
VARCHAR (500)
  
  

  
--Delete criteria (multiple)
  
  

  
) Asbegin
  
  

  
DECLARE @sql varchar (200)
  
  

  
--Dynamically building DELETE statements
  
  

  
Select @sql = "Delete from Messageinfo where" + @condition
  
  

  
--Trial Statement exec (@sql) Endgo
 
 

This procedure is called on SQL Query Analyzer: (the criteria passed in is uniquely identifying the column name and the selected value)

 
  
  

  
   
   
EXEC proc_deletemessage "messageid=240 or messageid=241 or messageid=242 ..."
 
  
  

In the SQL call is not very clear, we look at the web in front of the call and trial.

 
 

  
  

  
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 = (checkbox) Gv_class. Rows[i]. FindControl ("checkbox");
  
  

  
if (checkbox. Checked = = False)
  
  

  
{
  
  

  
Lab_note.text = "Please select to delete Information";
  
  

  
LAB_NOTE.STYLE.ADD ("Color", "red");
  
  

  
}
  
  

  
Else
  
  

  
{
  
  

  
messagemodel.c_id = Int32.Parse (gv_class. Rows[i]. CELLS[3]. Text.trim ());//Selected unique identity column value
  
  

  
Sb. Append ("messageid=");
  
  

  
Sb. Append (messagemodel.c_id);
  
  

  
Sb. Append ("or");
  
  

  
}
  
  

  
}
  
  

  
Sb. Append ("Messageid=null");
  
  

  
Messagemodel.sqlstr = sb. ToString ();//Dynamic Conditional statement passed to entity
  
  

  
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;
  
  

  
}
 
 

When the Web foreground dynamically builds and invokes procedures, we need to note a few things:

1, we check the column is generally a primary key unique identity column, according to it to delete.

2, if your primary key is a string is not plastic, then the above need to change sb. Append ("messageid="); sb. Append (messagemodel.c_id);

Sb. Append ("" or "), just multiple single quotes.

3, after the cycle after the attention plus SB. Append ("Messageid=null"); The individual believes that the primary key cannot be empty, so this condition is excluded. A dynamic statement that is not added is followed by a lot of or;

But do not add or 1=1 in the condition, so no matter how many are set up, will be the entire data deleted.

4, the construction of the statement with parameters passed in, as a deletion condition.

If there are other methods of efficient batch data deletion, I hope to post it and discuss and share with you.

Original link: http://www.cnblogs.com/lsyfg/archive/2010/12/28/1919186.html

"Edit Recommendation"