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
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