insert values into two different tables

Posted by shashianireddy-29351 under C# on 1/3/2015 | Points: 10 | Views : 418 | Status : [Member] | Replies : 2
i have excel file like below
sno name fname empid epsal
1 raju ravi 123 40000

upload Import Excel Sheet data into SQL Server using ASP . in different tables like..

sno name fname
1 raju ravi


empid empsal
123 40000

plz write qurie


Posted by: Ndebata on: 1/6/2015 [Member] Starter | Points: 25

I hope you are using Aspx in c# , as this question is tagged under c#.
There are so many ways you can do this

You can use nuget package ExcelDataReader

 using(FileStream stream = File.Open(excelPath, FileMode.Open, FileAccess.Read))

IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
excelReader.IsFirstRowAsColumnNames = true;
DataSet result = excelReader.AsDataSet();

//Here split the datatable to two different sets and save it your database in two different tables.

shashianireddy-29351, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Joginder on: 1/6/2015 [Member] Starter | Points: 25

hi Shashianireddy
if you want really upload both table first of all you get the data like that.

  bool checkempty = false;
string holdcolumnname = "";
//Array.ForEach(Directory.GetFiles((Server.MapPath("~/admin/Import/"))), File.Delete); // Delete for all file in the Import Directory.

//Array.ForEach(Directory.GetFiles((Server.MapPath("~/admin/Import/"))), ); // Delete for all file in the Import Directory.
string path = string.Concat((Server.MapPath("~/admin/Import/" + productupload.FileName)));

string temp = Path.GetExtension(path);
comman = new OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source=" + path + ";Extended Properties=Excel 8.0;");

//comman = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=YES;\";");
OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", comman);
DbDataReader dr = cmd.ExecuteReader();


after get the all data on data table get the value and one by one with the help of for loop like that..
  if (dt.Rows.Count > 0)
for (int i = 0; i < dt.Rows.Count; i++)
holdValueInspectbyOrg1 = null;
DataRow newRow = showResult.NewRow();

if (dt.Rows[i]["your column name paste here"].ToString().Trim() != null && dt.Rows[i]["Therapeutic Category Name"].ToString().Trim() != "")
import.fk_TherapeuticCategoryID = dt.Rows[i]["Therapeutic Category Name"].ToString().Trim();
holdcolumnname = "Therapeutic Category name";
goto outer;
if (dt.Rows[i]["Company Name"].ToString() != null && dt.Rows[i]["Company Name"].ToString() != "")
import.fk_ManufacturerIDs = dt.Rows[i]["Company Name"].ToString();
holdcolumnname = "Company Name";
goto outer;
if (dt.Rows[i]["Drug Generic Name"].ToString() != null && dt.Rows[i]["Drug Generic Name"].ToString() != "")
import.INN = dt.Rows[i]["Drug Generic Name"].ToString();
holdcolumnname = "Drug Generic Name";
goto outer;

I hope your query will be solve after see this solution and if have any problem regrading that touch with query.

shashianireddy-29351, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response