Last post Sep 25, 2007 06:57 AM by Nai-Dong Jin - MSFT
Sep 22, 2007 04:07 AM|cshoji|LINK
Scenario: I have created a dataset from an excel file to display it on a screen. Now I would like to save this same dataset in a SQL Server table.
Any ideas would be greatly appreciated.
Sep 22, 2007 04:24 AM|CS4Ever|LINK
Data Set = One or More table(s).
Create a table in SQL Server that has same headers (same number of columns and same data types).
Iterate through your dataset table(s): MyDataSet.Tables[i]
Read each column from that table, and save its value in SQL Server.
Why not to store the table in SQL Server then show it to user (e.g. by using DTS or SSIS).
Sep 22, 2007 04:46 AM|cshoji|LINK
Is there any other way than iteracting row by row using the Tables collection of the DataSet?
Note: I am using .NET 1.1
Sep 22, 2007 05:00 AM|kaushalparik27|LINK
i agree with the previous post ... it is better to save / import the date from excel to sql server then to show the data to user... here is how you can import data from spreadsheet to sql... using SqlBulkCopy
[if you are using ADO.NET 2.0]
Import / Export Excel Spreadsheet Data into SQL Server Database Table Using SqlBulkCopy
hope it helps./.
Sep 22, 2007 05:11 AM|cshoji|LINK
Unfortunately, I am using ADO.NET 1.1
Any similar way of doing it? Or other ideas?
Sep 22, 2007 05:40 AM|kaushalparik27|LINK
refer to link below...
How to import data from Excel to SQL Server
Sep 22, 2007 10:05 PM|cshoji|LINK
Is there any reference to How to import data from excel to sql server database in ADO.NET, instead of microsoft how to ADO and VB6?
Thanks in advance.
Sep 25, 2007 06:57 AM|Nai-Dong Jin - MSFT|LINK
Actually the solution provided by CS4Ever has already answered the problem. But I don’t know what does your “other way than iteracting row by row using the Tables collection of the DataSet” mean?
You have mentioned that you want to use ADO.Net, you have created a DataSet, and you want to save it in the back-end database.
The question has been quite clear, the DataSet is in-memory data container, if you want to save the schema and data in your dataset to your database, you have to build the same table schemas in your database first, and then, calling the update method of
dataadapter to update the back-end (In other words, import the data in dataset to back-end). Note, you should keep the status of all rows in dataset as “modified” which indicates that don’t call AcceptChanges() before calling Update method.
If you are insist on finding a better way, please show us more details, your needs, or something like “the way in my mind”, and reply.