unable to give rollback in excel upload

Question

0.00/5 (No votes)

See more:

C#

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");
          }



      }

showing error in catch unaaign transact

Posted 29-Jan-16 0:21am

Member 11970398

Add a Solution

Comments

F-ES Sitecore 29-Jan-16 6:30am

Only your "Insert into ALQuestion.QuestionMaster" statements are in the transaction, nothing else is so nothing but those statements can be rolled back.

If you are getting an error then say what line and what the error is.

Member 11970398 29-Jan-16 7:44am

transaction.Rollback();
error in transaction
unassign

Member 11970398 29-Jan-16 7:00am

if some lines give error it should roll back

ZurdoDev 29-Jan-16 8:06am

Roll back what? You have code to roll the transaction back. So what exactly is your question?

Richard Deeming 29-Jan-16 10:31am

Your code is vulnerable to SQL Injection[^].

NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Answer 1 · 2016-01-29T04:41:00

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:

C#

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();
    }
}

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 [^]

unable to give rollback in excel upload

1 solution

Solution 1

Add your solution here

Preview 0

unable to give rollback in excel upload

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us