How to save a dataset to a sql server table?

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.

Thanks

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.

Idea:

Why not to store the table in SQL Server then show it to user (e.g. by using DTS or SSIS).

Good luck.

|||

Is there any other way than iteracting row by row using the Tables collection of the DataSet?

Note: I am using .NET 1.1

thanks.

|||

hi there.,

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

|||

Unfortunately, I am using ADO.NET 1.1

Any similar way of doing it? Or other ideas?

Thanks

|||

refer to link below...

How to import data from Excel to SQL Server

|||

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.

|||

Hi,

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.

Thanks.