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.

How to retrieve large string from stored procedure?

Hello! This is my scenario...

Development - Visual Studio 2005
Database - MS SQL 2005

I have written a stored procedure that has a output parameter whose type is NVARCHAR(MAX). Then I use C# to wrap this stored procedure in OLEDB way. That means the OleDbType for that parameter is VarWChar.

This works fine if the string size is less than 4000. If more than 4000, the remaining part will be chopped off. The situation gets worst if I replace VarWChar with LongVarWChar. There is no error nor warning at compiling time. But there is an exception at run time.

So... Does anyone here can help me out? Thanks in advance.
Have you tried using a C# string datatype?|||Hello! Thanks for the reply. But for do you mean C# string data type here? Hmm... Let me show some codes here to clarify the question. :-)

OleDbParameter prm_Xml = new OleDbParameter("@.Xml", OleDbType.VarWChar, -1);

prm_Xml.Direction = ParameterDirection.Output;

OleDbParameter's constructor needs a OleDbType enumerator for second parameter. VarWChar has a limitation for 4K characters. If I change that to LongVarWchar, then will have a runtime error...
|||

Take a look at this link - http://msdn2.microsoft.com/en-us/library/a1904w6t(VS.80).aspx

Hope this helps

|||Raj, thanks for your prompt reply. Sorry for the late reply.

The link you sent does give me a clear picture of how to store and retrieve data that larger than 8K. But I still wonder whether there is a way to get the data as a output parameter of a stored procedure in OLEDB way.
|||

I think there is an example in the link that uses stored procedure with output parameter and gives VB/C# sample to retrieve it - Was that not helpful?

Take a look at - http://www.dotnet247.com/247reference/a.aspx?u=http://support.microsoft.com/?kbid=317016 and

http://www.dotnet247.com/247reference/a.aspx?u=http://www.kbalertz.com/Feedback_308049.aspx

Hope this helps

How to retrieve large string from stored procedure?

Hello! This is my scenario...

Development - Visual Studio 2005
Database - MS SQL 2005

I have written a stored procedure that has a output parameter whose type is NVARCHAR(MAX). Then I use C# to wrap this stored procedure in OLEDB way. That means the OleDbType for that parameter is VarWChar.

This works fine if the string size is less than 4000. If more than 4000, the remaining part will be chopped off. The situation gets worst if I replace VarWChar with LongVarWChar. There is no error nor warning at compiling time. But there is an exception at run time.

So... Does anyone here can help me out? Thanks in advance.
Have you tried using a C# string datatype?|||Hello! Thanks for the reply. But for do you mean C# string data type here? Hmm... Let me show some codes here to clarify the question. :-)

OleDbParameter prm_Xml = new OleDbParameter("@.Xml", OleDbType.VarWChar, -1);

prm_Xml.Direction = ParameterDirection.Output;

OleDbParameter's constructor needs a OleDbType enumerator for second parameter. VarWChar has a limitation for 4K characters. If I change that to LongVarWchar, then will have a runtime error...
|||

Take a look at this link - http://msdn2.microsoft.com/en-us/library/a1904w6t(VS.80).aspx

Hope this helps

|||Raj, thanks for your prompt reply. Sorry for the late reply.

The link you sent does give me a clear picture of how to store and retrieve data that larger than 8K. But I still wonder whether there is a way to get the data as a output parameter of a stored procedure in OLEDB way.
|||

I think there is an example in the link that uses stored procedure with output parameter and gives VB/C# sample to retrieve it - Was that not helpful?

Take a look at - http://www.dotnet247.com/247reference/a.aspx?u=http://support.microsoft.com/?kbid=317016 and

http://www.dotnet247.com/247reference/a.aspx?u=http://www.kbalertz.com/Feedback_308049.aspx

Hope this helps

how to retrieve inserted rows in sql server 2000

Hi,

I have this scenario:

items should be inserted into a item table before they are converted into assets. The database server is sql server 2000.

so item-> itemTable ->assetTable

When I insert items into the itemTable, how can I retrieve the itemID (an identity column) that will be created during the insert?

I know I can use scope_identity(), but if two or three person are inserting items to the same table, which scope_identity() correctly return the identity value? Should I lock the table first?

Also, I am using openxml to insert more than one items in one tsql statement, is there any chance to get all the newly created identity values? To make things worse, some items do not need to be converted into assetTables (according to itemType column), how can I loop through the whole xml and only insert certain items (continue using openxml if possible).?

many thanks

jerry

To answer your first question, Scope_Identity() is designed for just this situation. No need to lock the table.

I've not used openxml so can't help you with that. You could do this through DTS easily.

On the bulk insert, I suspect you'll have to re-query after the insert to get the values.

|||

How can I use DTS to do this? Can you give me an example in c# (or vb)?

Cheers

|||

DTS is a feature of SS2000 Enterprise Manager.

How you do this depends upon exactly what you want to do.

For the absolute basics, you could use the DTS Wizard to create a simple transformation / import from the XML file to a SQL Server table.

Not knowing your situation, I'd suggest one of two approaches:

1) Import the data into a staging table, then insert into your two tables with a SQL statement based upon your desired criteria.

2) Import the data into the first table with a timestamp (this would require adding a column to this table). Insert into the second table with a SQL statement based upon your desired criteria and the timestamp.

|||

Unfortunately there is no easy way to get the generated identity values for multiple rows other than querying the table or using a trigger to persist the results. SCOPE_IDENTITY or @.@.IDENTITY will only give the last generated value and single row only. In SQL Server 2005, there is a new OUTPUT clause that can be used in DML statements to get the identity values into a temporary table for example. See post below for more details on this feature or Books Online:

http://blogs.msdn.com/sqltips/archive/2005/06/13/OUTPUT_clause.aspx

So getting back to SQL Server 2000, you can do the following:

-- in your SP:

create table #ident ( i int not null )

insert ...

select ...

from openxml()

-- in your trigger

if object_id('tempdb...#ident') is not null

insert into #ident (i)

select identity_col from table

Now you can query the temporary table to get the generated values. You will probably need other key column(s) to match the rows with the ones returned by OPENXML.

|||

Fantastic... I have been prowling through my new O'Reilly textbook looking for this type of feature (as I need it - adding new stock items to an e-commerce database) and I discover that Microsoft have JUST invented it and implemented it into TSQL and SQL Server 05?!
Microsoft has new-found respect from me...