Visual Web part for read data from Excel Sheet and Insert into Data base

Step of reading data from excel sheet and insert into database.

     Step 1: Open .Net and Select New Project=> and Select SharePoint 2010=>Visual Web Part.

     Step 2: Open the .cs  file and put this code.

This code is for generate Xml for insert in databse:-

        private string GetXMLFromExcel(System.Web.HttpPostedFile file)
        {
            string savePath = string.Empty;
            string xmlData = string.Empty;
            string dirPath = string.Empty;
            dirPath = Path.Combine(Request.PhysicalApplicationPath, "BAUBUG");
            savePath = Path.Combine(dirPath, file.FileName);
            if (!Directory.Exists(dirPath))
            {
                 Directory.CreateDirectory(dirPath);
            }

            FUExcelSheet.PostedFile.SaveAs(savePath);
            xmlData = ReadDataFromExcel(savePath, file.FileName);
            System.IO.File.Delete(savePath);
            return xmlData;
        }

 This code return xml String code of Excel:-

        public string ReadDataFromExcel(string filePath, string fileName)
        {
            DataSet dataSet = null;
            OleDbConnection oledbConn = null;
            OleDbCommand command = null;
            OleDbDataAdapter dataAdapter = null;
            string strConnectionString = null;
            try
            {

                strConnectionString = ExcelProvider(fileName).ToString() + "Data Source=" + filePath + ExtendedProperties(fileName);
                oledbConn = new OleDbConnection(strConnectionString);
                // Opens OleDb Connection
                oledbConn.Open();
                string excelSheetName = oledbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null)
                    .Rows[0]["TABLE_NAME"].ToString().Replace("'", "");
                // Create OleDbCommand object and select data from worksheet Sheet1
                command = new OleDbCommand("SELECT * FROM [" + excelSheetName + "]", oledbConn);
                // Create new OleDbDataAdapter
                dataAdapter = new OleDbDataAdapter(command);
                // Create a DataSet which will hold the data extracted from the worksheet.
                dataSet = new DataSet("Root");
                // Fill the DataSet from the data extracted from the worksheet.
                dataAdapter.Fill(dataSet, "Detail");
                //foreach (DataRow dr in dataSet.Tables[0].Rows)
                //{

                //    string path = dr[13].ToString();
               //    dataSet.Tables[0].AcceptChanges();
              //}
           }
           catch (Exception)
            {
                // Throws the exception
                throw;
            }
            finally
            {
                oledbConn.Close();
                command.Dispose();
                dataAdapter.Dispose();
            }
            //return the value
            return dataSet.GetXml();
        }
        /// <summary>
        /// Excel provider
        /// </summary>
        /// <param name="FileName"></param>
        /// <returns></returns>
        private string ExcelProvider(string FileName)
        {
            if (null != IsOpenXMLFormat(FileName))
            {
                if (Convert.ToBoolean(IsOpenXMLFormat(FileName)))
                {
                    return "Provider=Microsoft.ACE.OLEDB.12.0;";
                }
                else
                {
                    return "Provider=Microsoft.Jet.OLEDB.4.0;";
                }
            }
            else
            {
                return null;
            }
          }
         /// <summary>
        ///
        /// </summary>
        /// <param name="FileName">Check Excel file Type</param>
        /// <returns></returns>
        private bool? IsOpenXMLFormat(string FileName)
        {
            string[] splitByDots = FileName.Split(new char[1] { '.' });
            //Excel 97-2003 file
            if (splitByDots[splitByDots.Length - 1] == "xls")
                return false;
            //Excel 2007 file
            if (splitByDots[splitByDots.Length - 1] == "xlsx")
                return true;
            //Not an Excel Sheet
            return null;
        }
        private string ExtendedProperties(string FileName)
        {
            if (null != IsOpenXMLFormat(FileName))
            {
                if (Convert.ToBoolean(IsOpenXMLFormat(FileName)))
                {
                    return ";Extended Properties=\"Excel 8.0;HDR=YES;\"";
                }
                else
                {
                    return ";Extended Properties=Excel 8.0;";
                }
            }
            else
            {
                return null;
            }
        }
        /// <summary>
        /// Bind Bug Details Grid
        /// </summary>
        public void DsGetBugDetails()
        {
            try
            {
                gvBugDetails.DataSource = ExecuteDataSet("CusTableBAUIssue", "SpGetBugDetails");
                gvBugDetails.DataBind();
            }
            catch (Exception Ex)
            {
                throw;
            }
        }
     #region DataBaseOperation
        /// <summary>
        /// Open Sql conncetion
        /// </summary>
        private void OpenConnection()
        {
            try
            {
                connectionString = "Data Source=P2010;Initial Catalog=DataBaseName;integrated security=true;";
                connection = new SqlConnection(connectionString);
                if (connection.State != ConnectionState.Open) connection.Open();
            }
            catch (Exception ex)
            {
                throw new ApplicationException(ex.Message + "OpenDBConnection");
            }
        }
        /// <summary>
        /// Close the connection
        /// </summary>
        public void ClosedbConnection()
        {
            try
            {
                connection.Dispose();
            }
            catch (Exception ex)
            {
                throw new ApplicationException(ex.Message + " CloseDBConnection");
            }
        }
        /// <summary>
        /// Return DataSet
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="procedureName"></param>
        /// <returns></returns>
        public DataSet ExecuteDataSet(string tableName, string procedureName)
        {
            DataSet dsReturn;
            try
            {
                OpenConnection();
                sqlComm = new SqlCommand();
                sqlComm.Parameters.Clear();
                sqlComm.CommandType = CommandType.StoredProcedure;
                sqlComm.CommandText = procedureName;
                dsReturn = new DataSet();
                sqlComm.Connection = connection;
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlComm);
                sqlDataAdapter.Fill(dsReturn, tableName);
            }
            catch (Exception ex)
            {
                throw new ApplicationException(ex.Message);
            }
            finally
            {
                ClosedbConnection();
            }
            return dsReturn;
        }
 #endregion