insert values into two different tables

Posted by shashianireddy-29351 under C# on 1/3/2015 | Points: 10 | Views : 1368 | 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..

table_a
sno name fname
1 raju ravi

table_b

empid empsal
123 40000

plz write qurie




Responses

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

Up
0
Down
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 https://www.nuget.org/packages/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.
excelReader.Dispose();
}


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

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

Up
0
Down
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)));
productupload.PostedFile.SaveAs(path);

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;\";");
comman.Open();
OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", comman);
conn.Open();
DbDataReader dr = cmd.ExecuteReader();


dt.Load(dr);


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();
else
{
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();
else
{
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();
else
{
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