无法在Excel上传中进行回滚 [英] unable to give rollback in excel upload

问题描述

public ActionResult UploadQnExcelSheet(FormCollection form, HttpPostedFileBase file)
      {
          SqlHelper sh = new SqlHelper();
          var conn = sh.connection();
          DataSet ds = new DataSet();
          DateTime now = DateTime.Now;
          var skillid = form["SkillsList"];
          var LevelId = form["LevelList"];

         SqlTransaction transaction;

          try
          {

              string fileExtension =
                                   System.IO.Path.GetExtension(Request.Files["file"].FileName);

              if (fileExtension == ".xls" || fileExtension == ".xlsx")
              {
                  string fileLocation = Server.MapPath("~/ExcelUpload/") + Request.Files["file"].FileName;
                  if (System.IO.File.Exists(fileLocation))
                  {

                      System.IO.File.Delete(fileLocation);
                  }
                  Request.Files["file"].SaveAs(fileLocation);
                  string excelConnectionString = string.Empty;
                  excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                  fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
                  //connection String for xls file format.
                  if (fileExtension == ".xls")
                  {
                      excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
                      fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
                  }
                  //connection String for xlsx file format.
                  else if (fileExtension == ".xlsx")
                  {
                      excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                      fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
                  }
                  //Create Connection to Excel work book and add oledb namespace
                  OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
                  excelConnection.Open();
                  DataTable dt = new DataTable();

                  dt = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                  excelConnection.Close();
                  if (dt == null)
                  {
                      return null;
                  }

                  String[] excelSheets = new String[dt.Rows.Count];
                  int t = 0;
                  //excel data saves in temp file here.
                  foreach (DataRow row in dt.Rows)
                  {
                      excelSheets[t] = row["TABLE_NAME"].ToString();
                      t++;
                  }
                  OleDbConnection excelConnection1 = new OleDbConnection(excelConnectionString);


                  string query = string.Format("Select * from [{0}]", excelSheets[0]);
                  using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, excelConnection1))
                  {
                      dataAdapter.Fill(ds);
                  }
              }

              for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
              {

                  //string conn = ConfigurationManager.ConnectionStrings["dbconnection"].ConnectionString;
                  SqlConnection con = new SqlConnection(conn);
                  string query = "Insert into ALQuestion.QuestionMaster(Question,Option1,Option2,Option3,Option4,Answer,LevelID,SkillID,CreatedOn,Status) Values('" +
                  ds.Tables[0].Rows[i][00].ToString() + "','" + ds.Tables[0].Rows[i][01].ToString() +
                  "','" + ds.Tables[0].Rows[i][02].ToString() + "','" + ds.Tables[0].Rows[i][03].ToString() + "','" + ds.Tables[0].Rows[i][04].ToString() + "','" + ds.Tables[0].Rows[i][05].ToString() + "','" + LevelId.ToString() + "','" + skillid.ToString() + "','" + now.ToString(("yyyy/MM/dd")) + "','" + "1" + "')";

                  con.Open();
                  SqlCommand cmd = new SqlCommand(query, con);


                  transaction = con.BeginTransaction("SampleTransaction");
                  cmd.Transaction = transaction;
                  cmd.ExecuteNonQuery();

                  transaction.Commit();
                  con.Close();

              }


              @TempData["UploadExcelStatus"] = "Excel Uploaded Successfully";
              return RedirectToAction("UploadQnExcelSheet");

          }
          catch
          {
                  transaction.Rollback();

                @TempData["UploadExcelStatus"]= "Please Upload a Proper Excel File";

              return RedirectToAction("UploadQnExcelSheet");
          }



      }

在catch unaaign中显示错误transact

showing error in catch unaaign transact

推荐答案

首先修复 SQL注入 [ ^ ]漏洞。



然后，包装你的连接，命令和事务中的对象使用块。



不要尝试以<$ c $回滚交易c> catch block;如果你没有到达提交行，使用块会为你处理。



由于您可能希望插入所有行，或者不想插入所有行，因此您需要对所有行使用单个事务。



尝试这样的事情：

Start by fixing the SQL Injection[^] vulnerability in your code.

Then, wrap your connection, command and transaction objects in using blocks.

Don't try to roll-back the transaction in a catch block; the using block will take care of that for you if you haven't reached the Commit line.

Since you presumably want either all of the rows to be inserted, or none of them, you need to use a single transaction for all rows.

Try something like this:

using (SqlConnection con = new SqlConnection(conn))
using (SqlCommand command = new SqlCommand("INSERT INTO ALQuestion.QuestionMaster(Question, Option1, Option2, Option3, Option4, Answer, LevelID, SkillID, CreatedOn, Status) VALUES (@Question, @Option1, @Option2, @Option3, @Option4, @Answer, @LevelID, @SkillID, @CreatedOn, @Status)", con))
{
    con.Open();
    using (SqlTransaction transaction = con.BeginTransaction())
    {
        command.Transaction = transaction;
        
        foreach (DataRow row in ds.Tables[0].Rows)
        {
            command.Parameters.AddWithValue("@Question", row[0]);
            command.Parameters.AddWithValue("@Option1", row[1]);
            command.Parameters.AddWithValue("@Option2", row[2]);
            command.Parameters.AddWithValue("@Option3", row[3]);
            command.Parameters.AddWithValue("@Option4", row[4]);
            command.Parameters.AddWithValue("@Answer", row[5]);
            command.Parameters.AddWithValue("@LevelID", LevelId);
            command.Parameters.AddWithValue("@SkillID", skillid);
            command.Parameters.AddWithValue("@CreatedOn", now);
            command.Parameters.AddWithValue("@Status", 1);
            
            command.ExecuteNonQuery();
            command.Parameters.Clear();
        }
        
        transaction.Commit();
    }
}

---

你想知道关于SQL注入的一切（但不敢问）特洛伊亨特 [ ^ ]

如何在没有技术术语的情况下解释SQL注入？ |信息安全堆栈交换 [ ^ ]

查询参数化备忘单| OWASP [ ^ ]

SQL注入攻击机制Pluralsight [ ^ ]

---

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]
SQL injection attack mechanics | Pluralsight [^]

这篇关于无法在Excel上传中进行回滚的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！