C#通过事务批量插入数据库

通过C#通过事务来批量插入数据库

public int SubmitResult(DBEntityResultInfo entityResultInfo, List<DBEntityResultDetailInfo> listResultDetails, IDbTransaction trans)
        {

            List<SqlParameter> paramList = new List<SqlParameter>();
            string sqlstr = @"INSERT INTO Exam_ResultInfo
                           ([ExamID]
                           ,[UserAccount]
                           ,[ExamTime]
                           ,[IsCertificate]
                           ,[CurrentLanguage])
                     VALUES
                           (@ExamID
                           ,@UserAccount
                           ,@ExamTime
                           ,@IsCertificate
                           ,@CurrentLanguage)";
            sqlstr += "declare @resultID int;select @resultID=@@identity;";
            paramList.Add(new SqlParameter("@ExamID", entityResultInfo.ExamID));
            paramList.Add(new SqlParameter("@UserAccount", entityResultInfo.UserAccount));
            paramList.Add(new SqlParameter("@ExamTime", entityResultInfo.ExamTime));
            paramList.Add(new SqlParameter("@IsCertificate", entityResultInfo.IsCertificate));
            paramList.Add(new SqlParameter("@CurrentLanguage", entityResultInfo.CurrentLanguage));
            for (int i = 0; i < listResultDetails.Count; i++)
            {
                sqlstr += @"INSERT INTO [MCD_WeiXinDB].[dbo].[Exam_ResultDetailInfo]
                       ([ResultID]
                       ,[QuestionID]
                       ,[AnswerID])
                 VALUES(@resultID,@QuestionID" + i + ",@AnswerID" + i + ")";
                paramList.Add(new SqlParameter("@QuestionID" + i, listResultDetails[i].QuestionID));
                paramList.Add(new SqlParameter("@AnswerID" + i, listResultDetails[i].AnswerID));
            }

            return DbHelper.ExecuteNonQuery(trans, CommandType.Text, sqlstr, paramList.ToArray());


        }

底层代码1

public static int ExecuteNonQuery(IDbTransaction transaction,
            CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {

            if (transaction is SqlTransaction)
            {
                return SqlHelper.ExecuteNonQuery(transaction as SqlTransaction, commandType, commandText, commandParameters);
            }
            else
            {
                throw new Exception("not implenment");
            }
        }

底层代码2

  public static int ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            if (transaction == null) throw new ArgumentNullException("transaction");
            if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");

            // Create a command and prepare it for execution
            SqlCommand cmd = new SqlCommand();
            bool mustCloseConnection = false;
            PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);

            // Finally, execute the command
            int retval = cmd.ExecuteNonQuery();

            // Detach the SqlParameters from the command object, so they can be used again
            cmd.Parameters.Clear();
            return retval;
        }