How to import the excel sheet with images and save it in database using asp.net c#

Last Reply 5 days ago By dharmendr

Posted 7 days ago

Hello Sir,

I'm trying to import the excel sheets with images and wanted to save the same in database.Below is the code which im using to import ,but here images are not getting imported/saved into the database and if the same row exists into the database then a duplicate of the same is saved.

 

 //Upload and save the file
            string excelPath = Server.MapPath("~") + Path.GetFileName(FileUpload1.PostedFile.FileName);
            FileUpload1.SaveAs(excelPath);

            string conString = string.Empty;
            string extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
            switch (extension)
            {
                case ".xls": //Excel 97-03
                    conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
                    break;
                case ".xlsx": //Excel 07 or higher
                    conString = ConfigurationManager.ConnectionStrings["Excel07+ConString"].ConnectionString;
                    break;

            }
            conString = string.Format(conString, excelPath);
            using (OleDbConnection excel_con = new OleDbConnection(conString))
            {
                excel_con.Open();
                string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
                DataTable dtExcelData = new DataTable();

                //[OPTIONAL]: It is recommended as otherwise the data will be considered as String by default.
                dtExcelData.Columns.AddRange(new DataColumn[17] { 
                       new DataColumn("userId", typeof(int)),
                    new DataColumn("staff_RefId",typeof(string)),
                  new DataColumn("name", typeof(string)),
                    new DataColumn("residentialaddress",typeof(string)),
                    new DataColumn("respostalcode",typeof(string)),
                    new DataColumn("email",typeof(string)),
                    new DataColumn("contact",typeof(string)),
                    new DataColumn("phone",typeof(string)),
                    new DataColumn("qualification",typeof(string)),
                    new DataColumn("languages",typeof(string)),
                    new DataColumn("licencenum",typeof(string)),
                    new DataColumn("date",typeof(DateTime)),
                    new DataColumn("postaddress",typeof(string)),
                    new DataColumn("postalcode",typeof(string)),
                    new DataColumn("work_history",typeof(string)),
                     new DataColumn("Image", typeof(string)),
                    
               });
                using (OleDbDataAdapter oda = new OleDbDataAdapter("select * from [" + sheet1 + "]", excel_con))
                {
                    oda.Fill(dtExcelData);
                }
                excel_con.Close();

                string consString = ConfigurationManager.ConnectionStrings["crm_db"].ConnectionString;
                SqlConnection con = new SqlConnection(consString);

                SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con);
                    {
                        //Set the database table name
                        sqlBulkCopy.DestinationTableName = "dbo.tbl_details";

                        ////[OPTIONAL]: Map the Excel columns with that of the database table
                        sqlBulkCopy.ColumnMappings.Add("userId", "userId");
                     sqlBulkCopy.ColumnMappings.Add("staff_RefId","staff_RefId");
                        sqlBulkCopy.ColumnMappings.Add("name","name");
                        sqlBulkCopy.ColumnMappings.Add("residentialaddress","residentialaddress");
                        sqlBulkCopy.ColumnMappings.Add("respostalcode","respostalcode");
                        sqlBulkCopy.ColumnMappings.Add("email","email");
                        sqlBulkCopy.ColumnMappings.Add("contact","contact");
                        sqlBulkCopy.ColumnMappings.Add("phone","phone");
                       sqlBulkCopy.ColumnMappings.Add("qualification","qualification");
                        sqlBulkCopy.ColumnMappings.Add("languages","languages");
                        sqlBulkCopy.ColumnMappings.Add("licencenum","licencenum");
                        sqlBulkCopy.ColumnMappings.Add("date","date");
                        sqlBulkCopy.ColumnMappings.Add("postaddress","postaddress");
                        sqlBulkCopy.ColumnMappings.Add("postalcode","postalcode");
                        sqlBulkCopy.ColumnMappings.Add("work_history","work_history");
                    sqlBulkCopy.ColumnMappings.Add("Image", "Image");
                    con.Open();
                        sqlBulkCopy.WriteToServer(dtExcelData);
                        ScriptManager.RegisterStartupScript(this, this.GetType(), "SweetAlert", "swal('Success!', 'Data has been been imported successfully to the database', 'success');", true);
                        con.Close();
           

Please help me

Thanks

You are viewing reply posted by: pandeyism 7 days ago.