MVC Capturing Excel Filename and importing it into database

Question

Here is the sample layout for visualization: I already manage to make a program where a user will input filename, and choose a file that will reflect on the table. What I need to do is when the EXCEL FILE is selected and the user click submit the excel file will be imported into the database.

"Home/Index"

---------------------------------------------------

Here Is another view that will be getting the excel files when the excel is imported into the database. My problem is to implement that ActionResult method into 1 submit button.

"Student"

Here is my Home Controller consisting of the method that gets the file name and store it into table (Index) and the InsertExcel method that is working but I have no Idea how to merge it into one. I need to implement that when a user click the submit button both of the methods will execute.

[HttpPost]
public ActionResult Index(Student obj)
{

        string strDateTime = System.DateTime.Now.ToString("ddMMyyyyHHMMss");  
        string finalPath = "\\excelfolder\\" + strDateTime + obj.UploadFile.FileName;  


       

         obj.UploadFile.SaveAs(Server.MapPath("~") + finalPath);  
         obj.FilePath = strDateTime + obj.UploadFile.FileName;  
         ViewBag.Message = SaveToDB(obj);  
         Student products = GetProducts();  
        return View(products);  
    }  

    public string SaveToDB(Student obj) //Save to database tblFiles  
    {  
        try  
        {  
            con = new SqlConnection(connectionString);  
            cmd = new SqlCommand();  
            con.Open();  
            cmd.Connection = con;  
            cmd.CommandType = System.Data.CommandType.StoredProcedure;  
            cmd.CommandText = "sp_AddFiles";  
            cmd.Parameters.AddWithValue("@FileN", obj.FileN);  
            cmd.Parameters.AddWithValue("@FilePath", obj.FilePath);  
            cmd.ExecuteNonQuery();  

            cmd.Dispose();  
            con.Dispose();  
            con.Close();  

            return "Saved Successfully";  
        }  
        catch (Exception ex)  
        {  
            return ex.Message.ToString();  
        }  
    }  
    // GET: Products  
    public Student GetProducts() //method to select * from database put on Data table first  
    {  
        Student products = new Student();  
        try  
        {  
            con = new SqlConnection(connectionString);  
            cmd = new SqlCommand("Select * from tblFiles", con);  
            con.Open();  
            adapter = new SqlDataAdapter(cmd);  
            DataTable dt = new DataTable();  
            adapter.Fill(dt);  

            adapter.Dispose();  
            cmd.Dispose();  
            con.Close();  

            products.lstProducts = new List<Student>();  

            foreach (DataRow dr in dt.Rows)  
            {  
                products.lstProducts.Add(new Student  
                {  
                    FileN = dr["FileN"].ToString(),  
                    FilePath = dr["FilePath"].ToString()  
                });  
            }  

             
        }  
        catch (Exception ex)  
        {  
            adapter.Dispose();  
            cmd.Dispose();  
            con.Close();  
        }  

         

        return products;  
    }  

    private void ExcelConn(string filepath)  

    {  

        string constr = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;""", filepath);  

        Econ = new OleDbConnection(constr);  

    }  

    private void InsertExceldata(string fileepath, string filename)  

    {  

        string fullpath = Server.MapPath("/excelfolder/") + filename;  

        ExcelConn(fullpath);  

        string query = string.Format("Select * from [{0}]", "Sheet1$");  



        OleDbCommand Ecom = new OleDbCommand(query, Econ);  

        Econ.Open();  

        DataSet ds = new DataSet();  

        OleDbDataAdapter oda = new OleDbDataAdapter(query, Econ);  

        Econ.Close();  

        oda.Fill(ds);  

        DataTable dt = ds.Tables[0];  

        SqlBulkCopy objbulk = new SqlBulkCopy(con);  

        objbulk.DestinationTableName = "dbo.Student";  
        objbulk.ColumnMappings.Add("StudentID", "StudentID");  

        objbulk.ColumnMappings.Add("FirstName", "FirstName");  

        objbulk.ColumnMappings.Add("LastName", "LastName");  

        objbulk.ColumnMappings.Add("Program", "Program");  

        objbulk.ColumnMappings.Add("YearGraduate", "YearGraduate");  

        objbulk.ColumnMappings.Add("BoardScore", "BoardScore");  

        con.Open();  

        objbulk.WriteToServer(dt);  

        con.Close();  

    }  
      
  //Import excel Student Datas  
    public ActionResult Index2()  
    {  



        var studentList = _studentDAL.GetallStudents();  

        if (studentList.Count == 0)  
        {  
            TempData["InfoMessage"] = "Student data unavailable in the Database.";  

        }  

        return View(studentList);  

    }  


    [HttpPost]  
    public ActionResult Index2(HttpPostedFileBase file)  
    {  
        string filename = Guid.NewGuid() + Path.GetExtension(file.FileName);  

        string filepath = "/excelfolder/" + filename;  

        file.SaveAs(Path.Combine(Server.MapPath("/excelfolder"), filename));  

        InsertExceldata(filepath, filename);  

        var studentList = _studentDAL.GetallStudents();  

        return View(studentList);  
    }  

Answer 1

Hi @JUAN MIGUEL C. NIETO ,
According to your code, you create a new file name, select an excel file, upload its data to the database, and finally upload the database data to the page through the SQL Select statement.
We usually use an overloaded method when using [HttpPost] on the Controller page. Such as:

 public class HomeController : Controller  
    {  
        // GET: Home  
        public ActionResult Index()  
        {  
            return View();  
        }  
       
        [HttpPost]  
        public ActionResult Index(Student obj)  
        {  
            // your code  
       
            return View();  
        }  
    }  

Also so that the button can call the POST method that overloaded method requested, you can use the following:

<body>  
    @using (Html.BeginForm("Index", "Home", FormMethod.Post))  
......  
</body>  

You want to combine two methods into one, but your view page is two, maybe you can combine these operations on a single page through front-end controls.

Best Regards
Qi You

Answer 2

Please disregard this Import Excel Function at this view ---- I just put it to say that the import excel function is working but I need it to work on the "Home/Index"