You must Sign In to post a response.
  • Category: ASP.NET

    Import excel data with images to sql in asp.net

    Hi,
    This is Sadiq.i'm try'ng to import excel data with images to sql.but the bulk data will be saved successfully.but not images.how i will do this. please help me.
    Thanks and regards
    Sadiq.M
  • #769281
    There is no direct way to import data in EXCEL with images, but you can import EXCEL data without image, you can use SQLBulkcopy class of C# that will help you to import it.
    checkout below code

    protected void Upload(object sender, EventArgs e)
    {
    //Upload and save the file
    string excelPath = Server.MapPath("~/Files/") + 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[3] { new DataColumn("Id", typeof(int)),
    new DataColumn("Name", typeof(string)),
    new DataColumn("Salary",typeof(decimal)) });

    using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + sheet1 + "]", excel_con))
    {
    oda.Fill(dtExcelData);
    }
    excel_con.Close();

    string consString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(consString))
    {
    using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
    {
    //Set the database table name
    sqlBulkCopy.DestinationTableName = "dbo.tblPersons";

    //[OPTIONAL]: Map the Excel columns with that of the database table
    sqlBulkCopy.ColumnMappings.Add("Id", "PersonId");
    sqlBulkCopy.ColumnMappings.Add("Name", "Name");
    sqlBulkCopy.ColumnMappings.Add("Salary", "Salary");
    con.Open();
    sqlBulkCopy.WriteToServer(dtExcelData);
    con.Close();
    }
    }
    }
    }

    Thanks
    Koolprasd2003
    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]


Sign In to post your comments