Execute Multiple SQL statements and execute database transactions (SQL parameters can be passed in)

In the previous blog, the instance introduced a transaction to execute multiple SQL statement functions (intExecuteSqlTran (ListStringSQLStringList) and click the link to open the link. In addition, it found its defects-the SQL statement parameters cannot be passed in, it is very troublesome to complete SQL statements by concatenating strings! Humans are 'Greedy 'drops (* ^__ ^ ......,

In the previous blog, the instance introduced a transaction to execute multiple SQL statement functions (int ExecuteSqlTran (ListString SQLStringList). Click to open the link, it is easy to find its defect-you cannot input SQL statement parameters. You can only concatenate strings to complete SQL statements, which is very troublesome! Humans are 'Greedy 'drops (* ^__ ^ ......,

In the previous blog, the instance introduced a transaction to execute multiple SQL statement functions (int ExecuteSqlTran (List SQLStringList) Click to open the link, so that you can easily find its defects-you cannot input SQL statement parameters and can only concatenate strings to complete SQL statements, which is very troublesome! Humans are 'Greedy 'drops (* ^__ ^ ......, At the same time, I am also brave in exploring and not passing parameters, so I think there must be passing parameters. This blog will share it-execute multiple SQL statements, implement Database Transaction functions (SQL parameters can be passed)

(Note: This blog does not describe hash tables, so I will know about it once. When my sister is happy, I will try to analyze the hash table O (partition _ partition) O Haha ~)

D-layer SqlTaskAssignInfo class:

////// Assign tasks to the group leader (update task info table T_TaskInfo and task allocation table T_TaskAssignInfo )//////Task Allocation Information Entity class (task ID, group ID, member ID, member name, group leader allocation date, group leader allocation time)///
 
  
Bool (true: Allocation successful; false: Allocation failed)
 Public bool AssignTask2 (Entity. taskAssignInfoEntity enTaskAssignInfo) {// 1, sql1 statement: Based on the task ID and allocation level, update the Task Assignment Status field taskDistributeState in T_TaskAssignInfo to 'allocated 'string sql1 = "update T_TaskAssignInfo set taskDistributeState = 'allocated 'where taskID = @ taskID and distributeLevel =' 1 '"; // 2, sql2 statement: Insert a task Information allocated by the group lead to the task assignment information table, including (task ID, group ID, member ID, member name, allocation date, allocation time, allocation level, submission status) string sql2 = "insert into T_TaskAssignInfo (taskID, groupID, memberID, memberName, distributeDate, distributeTime, distributeLevel, submitState)

Values (@ taskID, @ groupID, @ memberID, @ memberName, @ distributeDate, @ distributeTime, '2', 'uncommitted ') "; // 3, set parameters (task ID, group ID, member ID, member name, allocation date, and allocation time) SqlParameter [] parameters = {new SqlParameter ("@ taskID", enTaskAssignInfo. taskID), new SqlParameter ("@ groupID", enTaskAssignInfo. groupID), new SqlParameter ("@ memberID", enTaskAssignInfo. memberID), new SqlParameter ("@ memberName", enTaskAssignInfo. memberName), new SqlParameter ("@ distributeDate", enTaskAssignInfo. distributeDate), new SqlParameter ("@ distributeTime", enTaskAssignInfo. distributeTime)}; // 4, create a hash table Hashtable SQLStringList = new Hashtable (); SQLStringList. add (sql1, parameters); // 4.1 Add the sql1 statement and parameters to the SQLStringList hash table. add (sql2, parameters); // 4.2 Add sql2 statements and parameters to the hash table try {// 5, call the transaction functions of the DbHelperSQL class (execute multiple SQL statements, complete database transactions) DbHelperSQL. executeSqlTran (SQLStringList); return true;} catch (SystemException) {return false ;}}

(Note: The ConnectionString in the DbHelperSQL code is the database connection string written in the configuration file)

DbHelperSQL class:

After using System. Configuration is added here, you must also add reference public static string connectionString = ConfigurationManager. etettings ["ConnectionString"] in the Manager; ///// Execute multiple SQL statements to implement database transactions. //////Hash table of an SQL statement (the key is an SQL statement, and the value is the SqlParameter [] of the statement)Public static void ExecuteSqlTran (Hashtable SQLStringList) {using (SqlConnection conn = new SqlConnection (connectionString) {conn. open (); // Open the database connection using (SqlTransaction trans = conn. beginTransaction () // start Database Transaction {SqlCommand cmd = new SqlCommand (); // create SqlCommand command try {// loop foreach (DictionaryEntry myDE in SQLStringList) // circular hash table (in this example, the SQL statement {string plain text = myDE. key. toString (); // obtain the key value (in this example, the SQL statement) SqlParameter [] partition parms = (SqlParameter []) myDE. value; // obtain the key Value (in this example, the parameter corresponding to the SQL statement) PrepareCommand (cmd, conn, trans, plain text, plain parms); // call the PrepareCommand () function, add int val = cmd. executeNonQuery (); // call the add, delete, modify, and delete function ExcuteNoQuery () to execute the SQL statement cmd added to the hash table. parameters. clear (); // Clear parameter} trans. commit (); // Commit transaction} catch // catch exception {trans. rollback (); // transaction Rollback throw; // throw exception }}}}

// Add the private static void PrepareCommand (SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string parameter text, SqlParameter [] parameter parms) {if (conn. State! = ConnectionState. open) // if the database connection is closed, conn. open (); // Open the database connection cmd. connection = conn; // set the command to connect to cmd. commandText = plain text; // sets the SQL statement if (trans! = Null) // if the transaction is not empty cmd. transaction = trans; // set the command execution Transaction cmd. commandType = CommandType. text; // set the description that the SQL statement type is "Text" (that is, this function is not applicable to Stored Procedures) if (explain parms! = Null) // if the parameter array is not empty {foreach (SqlParameter parameter in parameter parms) // The parameter array passed in cyclically {if (parameter. direction = ParameterDirection. inputOutput | parameter. direction = ParameterDirection. input) & (parameter. value = null) {parameter. value = DBNull. value; // obtain the parameter Value} cmd. parameters. add (parameter); // Add parameter }}}

Configuration file:

Compare the "execute multiple SQL statements to implement database transactions (SQL parameters are not allowed to be passed in) functions" described in the previous blog and click the open link to see the advantages and disadvantages of the two functions, learn how two functions are called in layer-D functions and how to use them!