SQL batch add, UPDATE, delete

This is about the bulk operation data for SQL.

Need to prepare:

Three-layer frame: Using the dynamic Soft code generator (can quickly generate additions and deletions) to generate three-layer Model,bll,dal

① DbHelpeSQL.cs------->sql helper Classes for operations

② CommandInfo.cs--------> A command class

Method of the transaction:

   /// <summary>        ///executes multiple SQL statements to implement database transactions. /// </summary>        /// <param name= "Sqlstringlist" >hash table for the SQL statement (key is the SQL statement, value is the statement's sqlparameter[])</param>         Public Static intExecutesqltran (system.collections.generic.list<commandinfo>cmdlist) {            using(SqlConnection conn =NewSqlConnection (connectionString)) {Conn.                Open (); using(SqlTransaction trans =Conn. BeginTransaction ()) {SqlCommand cmd=NewSqlCommand (); Try                    { intCount =0; //Loops                        foreach(CommandInfo Mydeinchcmdlist) {                            stringCmdtext =Myde.commandtext; Sqlparameter[] Cmdparms=(sqlparameter[]) myde.parameters;                                                       PrepareCommand (CMD, conn, trans, Cmdtext, cmdparms); if(Myde.effentnexttype = = Effentnexttype.whenhavecontine | | myde.effentnexttype = =effentnexttype.whennohavecontine) {if(MyDE.CommandText.ToLower (). IndexOf ("Count (") == -1) {trans.                                    Rollback (); return 0; }                                Objectobj =cmd.                                ExecuteScalar (); BOOLIshave =false; if(obj = =NULL&& obj = =dbnull.value) {Ishave=false; } Ishave= Convert.ToInt32 (obj) >0; if(Myde.effentnexttype = = Effentnexttype.whenhavecontine &&!)Ishave) {trans.                                    Rollback (); return 0; }                                if(Myde.effentnexttype = = Effentnexttype.whennohavecontine &&Ishave) {trans.                                    Rollback (); return 0; }                                Continue; }                            intval =cmd.                            ExecuteNonQuery (); Count+=Val; if(Myde.effentnexttype = = Effentnexttype.excuteeffectrows && val = =0) {trans.                                Rollback (); return 0; } cmd.                        Parameters.clear ();                        } trans.commit (); returncount; }                    Catch{trans.                        Rollback (); Throw; }                }            }        }

① Bulk Delete

This can be done with a single SQL statement, so there is no problem with bulk batches

Delete TableName where ID in ( conditional ) conditional format: 1,2,3,4

Example: A simple User table column: Id,name.age,sex

② Batch Add we use the time, their own mobile phone changes can be, in line with their own table

        /// <summary>        ///add more than one piece of data/// </summary>        /// <param name= "model" ></param>        /// <returns></returns>         Public intADD (list<user> userlist) {List<CommandInfo> strSQL =NewList<commandinfo>(); foreach(User Iteminchuserlist) {CommandInfo Info=NewCommandInfo (); StringBuilder strSQL=NewStringBuilder (); Strsql.append ("INSERT INTO User ("); Strsql.append ("name,age,sex)"); Strsql.append ("VALUES ("); Strsql.append ("@Name, @Age, @Sex)"); Info.commandtext=strsql.tostring (); //sql Statements
sqlparameter[] Parameters= {                    NewSqlParameter ("@Name", sqldbtype.nvarchar,500),                    NewSqlParameter ("@Age", sqldbtype.int,4),                    NewSqlParameter ("@Sex", sqldbtype.int,4)} ;parameters[0]. Value =item.                 Name; parameters[1]. Value =item.                Age; parameters[2]. Value =item.            Sex; info. Parameters=parameters; //Parameters

strSQL.   ADD (info);            //Add multiple SQL statements and execute them together } intnum =Dbhelpersql.executesqltran (strSQL); //execution transactions must be performed in a transaction, when multiple data are either all successful or fail if(Num >0)            {                returnnum; }            Else            {                return 0; }        }

③ batch update Similar updates, the SQL statements replaced by the update is possible

  /// <summary>        ///update more than one data/// </summary>         Public BOOLUpdate (list<user> userlist) {List<CommandInfo> strSQL =NewList<commandinfo>(); CommandInfo Info=NewCommandInfo (); StringBuilder strSQL=NewStringBuilder (); foreach(User Iteminchuserlist) {Strsql.append ("Update User Set"); Strsql.append ("[email protected],"); Strsql.append ("[email protected],"); Strsql.append ("[email protected],"); Strsql.append ("where [email protected]"); Info.commandtext=strsql.tostring ();
sqlparameter[] Parameters= {                    NewSqlParameter ("@Name", sqldbtype.nvarchar,500),                    NewSqlParameter ("@Age", SqlDbType.Int,4),                    NewSqlParameter ("@Sex", sqldbtype.int,4),NewSqlParameter ("@ID", SqlDbType.Int,4)}; parameters[0]. Value =item.                Name; parameters[1]. Value =item.                Age; parameters[2]. Value =item.                Sex; parameters[3]. Value =item.id; info. Parameters=parameters; strSQL.             ADD (info);             }            introws =Dbhelpersql.executesqltran (strSQL); if(Rows >0)            {                return true; }            Else            {                return false; }        }

Bulk data, this format: (Real-world requirements may be more than just doing an operation, may add, delete, update together)

list<commandinfo> strSQL = new list<commandinfo> (); //Head

First command

CommandInfo info1 = new CommandInfo ();
StringBuilder STRSQL1 = new StringBuilder ();

Strsql1.append ("SQL statements, parameters with @xx substitution");

Info1.commandtext=strsql1.tosting ();

sqlparameter[] Parameters = {
declaring a collection of type and size SqlParameter
};

Parameters[0]. Value = Assign a value to each parameter
Info1.  Parameters = Parameters; //Parameters of the command

Second command

The third command continues to be added.

strSQL. ADD (info);

int rows = Dbhelpersql.executesqltran (strSQL); //Tail

SQL batch add, UPDATE, delete