Click here to Skip to main content
14,975,416 members
Please Sign up or sign in to vote.
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
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

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 [^]
   
Comments
Richard Deeming 29-Jan-16 11:44am
   
Would the phantom down-voter care to explain their down-vote?

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900