Reading Excel file to DataSet and writing it to SQL table in the look with verification.

Hi Expert,

I am trying to create a web page in C# that will perform the following:
1. Connects to Excel file and read all the information into DataSet - DONE
2. Display the DataGrid with the DataSet - DONE
3. Insert DataSet into the defined table in the SQL database except of the first header row
4. There will be additional fields in the SQL table that will need to be filled with the Variables read from another table in the SQL database (like the Batch No. - which will be different each time the data is inserted) before writing it to the SQL table.

I am able to read the Excel file and bind it to the datagrid to display on the webpage. I need to insert this rows in a LOOP into the desired SQL table. There is about 200 rows on average to insert every day.

In the loop described before inserting the row to SQL table, I would like to check the value of the one filed in dataset with the field in the different SQL table (like Account number)

I am struggling with points 3 and 4. Could you help me there please.

So far my code looks like that:
-------------------------------------------------------------------------------------------------------------------------------
<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data.OleDb" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System" %>

<script language="C#" runat="server">
protected void Page_Load(Object Src, EventArgs E)
{
string myQuery;
string ExcelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\dataset\\exceltest.xls;Extended Properties=""Excel 8.0;HDR=YES;""";
string SQLConnectionString = "Data Source=.;Initial Catalog=intranet_panda;Integrated Security=True";

OleDbDataAdapter myCommandExcel = new OleDbDataAdapter("SELECT * FROM [Dane$]",ExcelConnectionString);

DataSet myDataSet = new DataSet();
myCommandExcel.Fill(myDataSet, "ExcelInfo");

DataGrid1.DataSource = myDataSet.Tables["ExcelInfo"].DefaultView;
DataGrid1.DataBind();
}
</script>

<html>
  <head></head>
  <body>
    <p><asp:Label id=Label1 runat="server">SpreadSheetContents:</asp:Label></p>
    <asp:DataGrid id=DataGrid1 runat="server"/>
  </body>
</html>
----------------------------------------------------------------------------------------------------------------

Thank you for the response,
Piotr Szablowski
piotr-szablowskiAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David ToddSenior DBACommented:
Hi,

Just a thought, but given that you have cross posted into SQL, this would be how a DBA would do it - Bulk insert of BCP into a staging table, then join to the second table, then finally insert into the destination table. Now the app or web page can display the data ...

Just a thought.

And the catch is that the path to the excel file needs to be from the SQL servers perspective ...

HTH
  David
0
piotr-szablowskiAuthor Commented:
Hi David,

Looks like an idea to me. Could you expand your thought a little bit more. Your answer is a little to sophisicated to me.

Could you explain in more detail the following part?:
"Bulk insert of BCP into a staging table, then join to the second table, then finally insert into the destination table. Now the app or web page can display the data ..."

SQL server will manage to deal with the file as it will always have the same name and can be saved on the network drive that is physically located on the SQL server. That solves the problem. I can schedule a task to move the file from that folder to the archive folder, or even delete it on the nightly basis to prepare emptly folder for the new file to save there.

Thank you for your answer.
0
David ToddSenior DBACommented:
Hi,

"... explain the staging table thing ... "
Often the external data contains duplicates and all that.

BCP and Bulk insert aren't particularly intelligent, and will totally fail for the most minor of things.

For instance, importing orders.
There might be a stuff up on the export and an order exported that has been transfered previously. Often can happen when attempting to recover from a crash.

So an insert into the destination orders table will fail.

If we have an intermediate table, with much the same structure as the orders table, but few constraints, then the insert will happen, and 'consume' the transfer file.

Now we can at our leisure copy the data from intermediate or staging table to the destination table. As this query allows us a lot more control, we can do things like this
insert destination_Orders
select *
from staging_orders so
left outer join destination_orders do
  on so.orderID = do.orderID -- you may or may not be bringing an ID across
where
  do.orderID is null -- that is, no row exists in destination.

HTH
  David
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Computer101Commented:
Forced accept.

Computer101
EE Admin
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.