15 May 2014

Uploading Excel File to SQL Server Database

protected void btnSave_Click(object sender, EventArgs e)
    {
        ImportlToDb();
     }

void ImportToDb()
    {

        try
        {

            if (FileUpload1.HasFile)
            {
                string saveFolder = @"C:\MyFolder";

                string filePath = Path.Combine(saveFolder, FileUpload1.FileName);

                FlUploadcsv.SaveAs(filePath);
                String strConnection = "Data Source=hack3r;Initial Catalog=eLogisticsDb;Integrated Security=True";

                String excelConnString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0\"", filePath);
                //Create Connection to Excel work book
                using (OleDbConnection excelConnection = new OleDbConnection(excelConnString))
                {
                    //Create OleDbCommand to fetch data from Excel
                    using (OleDbCommand cmd = new OleDbCommand("Select [Id],[Name] from [Sheet1$]", excelConnection))
                    {
                        excelConnection.Open();
                        using (OleDbDataReader dReader = cmd.ExecuteReader())
                        {
                            using (SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection))
                            {
                                //Give your Destination table name
                                sqlBulk.DestinationTableName = "Employees";
                                sqlBulk.WriteToServer(dReader);
                            }
                        }
                        excelConnection.Close();
                    }
                }

            }

        }

        catch (Exception ex)
        {

        }

    }

No comments:

Post a Comment