C# How to read ,validate multi tab excel sheet data and import in sql server tables

architecture c#-4.0 dapper sql-server-2008-r2

Question

I need to upload ecommerce catalog data by reading data from multiple tabs And insert those data in SQL server tables.

Whats best way to implement the same using c# and dapper .net and SQL server.

Thanks Rakesh

Popular Answer

Here is sample code i used

using Excel;
using System.IO;
using System.Data;
using System.Data.OleDb;
using System.Text;

 public partial class UserDashboard : BasePage
 {
    protected void lnkImportExcel_Click(object sender, EventArgs e)
    {
        try
        {
            if (!FpdUnConLoanUpload.HasFile)
            {
                this.DisplayAlert(ref AlertctrlMessage, "Please select an excel (.xlsx) or CSV file.", AlertNotification.Error);
                return;
            }
            if (!FpdUnConLoanUpload.FileName.Trim().ToLower().Split('.')[1].Equals("xlsx") && !FpdUnConLoanUpload.FileName.Trim().ToLower().Split('.')[1].Equals("csv"))
            {
                this.DisplayAlert(ref AlertctrlMessage, "Please select a valid excel (.xlsx) or CSV file.", AlertNotification.Error);
                return;
            }

            Guid nimgGUID = Guid.NewGuid();
            if (!FpdUnConLoanUpload.FileName.Trim().ToLower().Equals(""))
            {
                if (FpdUnConLoanUpload.FileName.Trim().ToLower().Split('.')[1].Equals("xlsx"))
                {
                    IExcelDataReader iExcelDataReader = null;

                    string TargetPathFileSave = Server.MapPath(ConfigurationManager.AppSettings.Get("ImportFiles"));

                    TargetPathFileSave = TargetPathFileSave + "\\";

                    if (!System.IO.Directory.Exists(TargetPathFileSave))
                    {
                        System.IO.Directory.CreateDirectory(TargetPathFileSave);
                    }

                    TargetPathFileSave = TargetPathFileSave + "\\" + nimgGUID.ToString().Trim() + FpdUnConLoanUpload.FileName.Substring(FpdUnConLoanUpload.FileName.LastIndexOf('.')).ToLower();
                    FpdUnConLoanUpload.PostedFile.SaveAs(TargetPathFileSave);

                    FileStream oStream = File.Open(TargetPathFileSave, FileMode.Open, FileAccess.Read);
                    DataSet dsUnUpdated = new DataSet();
                    if (FpdUnConLoanUpload.FileName.Trim().ToLower().Split('.')[1].Equals("xlsx"))
                    {
                        iExcelDataReader = ExcelReaderFactory.CreateOpenXmlReader(oStream);
                        iExcelDataReader.IsFirstRowAsColumnNames = true;
                        dsUnUpdated = iExcelDataReader.AsDataSet();
                        iExcelDataReader.Close();
                    }

                    if (dsUnUpdated != null)
                    {
                        Session["dsUnUpdated"] = dsUnUpdated;
                        LinkProcess.Visible = true;
                        grdSearchData.DataSource = dsUnUpdated;
                        grdSearchData.DataBind();
                    }
                    else
                    {
                        this.DisplayAlert(ref AlertctrlMessage, "No Data Found In File!", AlertNotification.Error);
                    }
   }
        catch (Exception exc)
        {
            lblcount.Text = "";
            this.DisplayAlert(ref AlertctrlMessage, exc.Message, AlertNotification.Error);
        }
    }
    }
}

now in filled dataset ds you will have all data as per sheets as data table

traverse data tables in ds and save them to db



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why