savepath? saving excel to sql?

Question

I'm really confused on how to go about doing this. - I want to be able to upload an excel sheet from my web app using the fileupload control. - Next, I want to read each individual row under the first row. (so starting from row 2, row 1 will be the column title). - Finally, I want to pass the Strings I've read into another method that'll do what i want with it and then post to a gridview.

How I intend to do so... Since I have my web app published on the network, hosted on my local box... my usual savepath for the file uploaded (desktop) doesn't work. So i thought to save it onto a SQL Server that is also hosted on my local box.

Therefore, I guess I'm trying to: - Save the uploaded excel onto a SQL database. - Read each line from the excel and pass it through the intended method

Okay, that was super confusing. There must be an easier way to do this! (do i really need a SQL database?) Oh, and any good ideas for my savePath?

Answer 1

Haven't really solved this. But it's easier to read whatever's on the excel file using the OleDBConnection like this:

            try
            {
                using (OleDbConnection olcon = new OleDbConnection())
                {
                        olcon.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath(fileupFile.FileName) + ";Extended Properties=Excel 12.0";
                        OleDbCommand olcmd = new OleDbCommand("SELECT * FROM [Sheet1$]", olcon);
                        olcon.Open();
                        OleDbDataReader olread = olcmd.ExecuteReader();
                        while (olread.Read())
                        {
                            line = (String)(olread.GetString(0));
                            Verify(line); //calls the datatosql method
                        }
                        olcon.Close();
                        lblFiup.Text = "Data Inserted Sucessfully";   
                        lblFiup.ForeColor = System.Drawing.Color.Green; 
                }
            }

Then, pass into another method, which uses SqlConnection to write into SQL Server. Like this:

        protected void datatosql(String url, String stat)
        {
            try
            {
                using (SqlConnection sqlcon = new SqlConnection("Server=(local);Database=URLs;Trusted_Connection=True"))
                {
                    using (SqlCommand sqlcom = sqlcon.CreateCommand())
                    {
                        sqlcom.CommandText = "INSERT INTO WEVRecordsTest (URL, Status) VALUES ('" + url + "','" + stat + "')";
                        sqlcon.Open();
                        sqlcom.ExecuteNonQuery();
                        sqlcon.Close();
                    }
                }
            }
            catch (SqlException se)
            {
                lblHist.Text = se.Message;
                lblHist.ForeColor = System.Drawing.Color.Red;
            }
        }