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 run multiple store procedures in SQL Reporting Service

Can we run two store procedures in the Query String under the Dataset section
of SQL Reporting Service 2005? It seems like the Query String only allow one
store procedure. Ww want to use a separate store procedure to open a
symmetric key before we run the second store procedure to retrieve the
sensitive data.What about writing a thrid procedure calling the two others ?
HTH, Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--sql

How to return all records when date filter parameter is missing

I'm using an objectDataSource connected to a strongly typed dataset to populate a GridView. I want to be able to show all the records, or let the user to select only those records that expire in a certain month. The expire field is of type date

I'm used to all records being returned when a parameter is missing. If I have Select * from table where last=@.last, only the records where the last name is 'Smith' will be returned if @.last = 'Smith', but all records are returned is @.last = "". But that's not how it's working with the date.

I'm passing an integer from 1 to 12 in a querystring. I have the equivalent of

select * from table where (MONTH([AD ENDS]) = @.month)

MONTH(datefield) always returns an integer from 1 to 12. If @.month is empty, I want all the records to be displayed, but nothing is. If @.month is an int form 1 to 12, it works fine. How can I get all the records if no month is selected? Can I have two objectdatasources and programmatically select which one populates the gridview depending on if I want to filter the data or not?

Diane

HI Diane,

It think there are two options that might be better than having two objectdatasources.

1:change the select statement programmatically
strSQL = "select * from table ";
if (strMyMonth == string.empty)
{ strSQL+= "where (MONTH([AD ENDS]) = @.month)"}
else
{srSQL += "where (MONTH([AD ENDS]) > Date('01/01/0001')}

2:use the databases own If Then Else capabilities (this is dependent on your DB.. I know oracle, sqlserver and even access allows this)

Access Example: If [targetgoal] <> 0 then ([Total Of $FeeBilled]/6)*12)/[TargetGoal] else [targetgoal]= 0

|||I don't know how you can get all records without send anything in with Select * from table wherelast=@.last

But I would try to use Select * from table where last=ISNULL(@.last,last).

For the month query,please try something like this:

WHEREMONTH(tDate)=ISNULL(@.month,MONTH(tDate))

|||

Yup, those are much better options. I forgot about changing the select statement programmatically. Been at this too many hours I guess. I like the idea of using the database's capabilities though. If my current select statement is select * from table where (MONTH([AD ENDS]) = @.month) and I'm using SQL Server 2005, what would the new select statement be?

select * from table where ( If @.month <> 0 then (MONTH([AD ENDS]) = @.month) )?

or

select * from table ( If @.month <> 0 then (whereMONTH([AD ENDS]) = @.month) )?

Diane

|||SELECT * FROM Table WHERE MONTH([AD ENDS]) = ISNULL(@.month, MONTH([AD ENDS])|||

Thank you Dinakar! That worked!

Linmo, in a strongly typed dataset (which are pretty much all I know in .NET so far), if an empty parameter is returned, the filter for that parameter isn't set. This behavior is a life saver IMHO. It kust had me stumped this time around.

Diane

How to retrieve the actions using MDSCHEMA_ACTIONS?

Hello,

When I run the following code, I get an empty schema.

Dim drAction As DataRow

Dim dsActions As DataSet

dsActions = MyConnection.GetSchemaDataSet(AdomdSchemaGuid.Actions, _

New Object() {szDatabase, Nothing, szCubeName, Nothing, Nothing, szCubeName, 1})

The result:

dsActions.Tables.Count = 1 but dsActions.Tables(0).Rows.Count = 0

Even though my cube contains two actions as it is shown in the following:

<Actions>

<Action xsi:type="DrillThroughAction">

<ID>Drillthrough Action</ID>

<Name>Drillthrough Action</Name>

<TargetType>Cells</TargetType>

<Target>MeasureGroupMeasures("Values Measure")</Target>

<Type>DrillThrough</Type>

<Default>true</Default>

<MaximumRows>3</MaximumRows>

</Action>

<Action xsi:type="DrillThroughAction">

<ID>Drillthrough Action 1</ID>

<Name>Drillthrough Action 1</Name>

<TargetType>Cells</TargetType>

<Target>MeasureGroupMeasures("Values Measure")</Target>

<Type>DrillThrough</Type>

<Default>true</Default>

<MaximumRows>2</MaximumRows>

</Action>

</Actions>

Thanks,

yones

hello Yones,

i think the problem is with the restrictions you specify. I think coordinate and coordinate type in this case should be: Coordinate type should be Cell (6) and Coordinate should be a tuple defining your cell. (right now it looks like they are for cube object). So for example the query could look like (for Adventure Works)

dsActions = MyConnection.GetSchemaDataSet(AdomdSchemaGuid.Actions, _

New Object() {

"Adventure Works DW", // CATALOG_NAME

Nothing, // SCHEMA_NAME

"Adventure Works", // CUBE_NAME

Nothing, // ACTION_NAME

Nothing, // ACTION_TYPE

"([Customer].[Customer Geography].[Country].&[Canada],[Measures].[Internet Extended Amount])", // COORDINATE

6}) // COORDINATE_TYPE : MDACTION_COORDINATE_CELL (6)

hope this helps,

|||

Hello Mary,

First thank you very much for your help.

I tried Cell actions instead of Cube actions as it is shown in the following code:

dsActions = MyConnection.GetSchemaDataSet(AdomdSchemaGuid.Actions, _

New Object() {"AmoAdventureWorks", Nothing, "Adventure Works", Nothing, Nothing, "[Date].[Calendar Month Name].[All Periods],[Customer].[City].[All Customers],[Measures].[Reseller Sales Amount]", 6})

But the result was the same:

dsActions.Tables.Count = 1

dsActions.Tables(0).Rows.Count = 0

And my cube contains the following action:

<Actions>

<Action xsi:type="DrillThroughAction">

<ID>Drillthrough Action 1</ID>

<Name>Drillthrough Action</Name>

<TargetType>Cells</TargetType>

<Target>MeasureGroupMeasures("Reseller Sales")</Target>

<Type>DrillThrough</Type>

<Default>true</Default>

<MaximumRows>4</MaximumRows>

</Action>

</Actions>

Please let me know if you notice any wrong in my code.

Again thank you

Yones|||

hello Yones,

i think you might be missing () inside a coordinate. I.e. i think it should be "([Date].[Calendar Month Name].[All Periods],[Customer].[City].[All Customers],[Measures].[Reseller Sales Amount])".

hope this helps,

How to retrieve the actions using MDSCHEMA_ACTIONS?

Hello,

When I run the following code, I get an empty schema.

Dim drAction As DataRow

Dim dsActions As DataSet

dsActions = MyConnection.GetSchemaDataSet(AdomdSchemaGuid.Actions, _

New Object() {szDatabase, Nothing, szCubeName, Nothing, Nothing, szCubeName, 1})

The result:

dsActions.Tables.Count = 1 but dsActions.Tables(0).Rows.Count = 0

Even though my cube contains two actions as it is shown in the following:

<Actions>

<Action xsi:type="DrillThroughAction">

<ID>Drillthrough Action</ID>

<Name>Drillthrough Action</Name>

<TargetType>Cells</TargetType>

<Target>MeasureGroupMeasures("Values Measure")</Target>

<Type>DrillThrough</Type>

<Default>true</Default>

<MaximumRows>3</MaximumRows>

</Action>

<Action xsi:type="DrillThroughAction">

<ID>Drillthrough Action 1</ID>

<Name>Drillthrough Action 1</Name>

<TargetType>Cells</TargetType>

<Target>MeasureGroupMeasures("Values Measure")</Target>

<Type>DrillThrough</Type>

<Default>true</Default>

<MaximumRows>2</MaximumRows>

</Action>

</Actions>

Thanks,

yones

hello Yones,

i think the problem is with the restrictions you specify. I think coordinate and coordinate type in this case should be: Coordinate type should be Cell (6) and Coordinate should be a tuple defining your cell. (right now it looks like they are for cube object). So for example the query could look like (for Adventure Works)

dsActions = MyConnection.GetSchemaDataSet(AdomdSchemaGuid.Actions, _

New Object() {

"Adventure Works DW", // CATALOG_NAME

Nothing, // SCHEMA_NAME

"Adventure Works", // CUBE_NAME

Nothing, // ACTION_NAME

Nothing, // ACTION_TYPE

"([Customer].[Customer Geography].[Country].&[Canada],[Measures].[Internet Extended Amount])", // COORDINATE

6}) // COORDINATE_TYPE : MDACTION_COORDINATE_CELL (6)

hope this helps,

|||

Hello Mary,

First thank you very much for your help.

I tried Cell actions instead of Cube actions as it is shown in the following code:

dsActions = MyConnection.GetSchemaDataSet(AdomdSchemaGuid.Actions, _

New Object() {"AmoAdventureWorks", Nothing, "Adventure Works", Nothing, Nothing, "[Date].[Calendar Month Name].[All Periods],[Customer].[City].[All Customers],[Measures].[Reseller Sales Amount]", 6})

But the result was the same:

dsActions.Tables.Count = 1

dsActions.Tables(0).Rows.Count = 0

And my cube contains the following action:

<Actions>

<Action xsi:type="DrillThroughAction">

<ID>Drillthrough Action 1</ID>

<Name>Drillthrough Action</Name>

<TargetType>Cells</TargetType>

<Target>MeasureGroupMeasures("Reseller Sales")</Target>

<Type>DrillThrough</Type>

<Default>true</Default>

<MaximumRows>4</MaximumRows>

</Action>

</Actions>

Please let me know if you notice any wrong in my code.

Again thank you

Yones|||

hello Yones,

i think you might be missing () inside a coordinate. I.e. i think it should be "([Date].[Calendar Month Name].[All Periods],[Customer].[City].[All Customers],[Measures].[Reseller Sales Amount])".

hope this helps,

How to Re-Structure Form After adding an Identity

I'd created a dataform from a table stored in a SQL database, after that I changed the primary key field to be an identity, refreshed the dataset but when I execute the program, it raises an exception when I try to save the record after adding a new one asking me for the value of this field. What can I do to be sure it will be created automatically?

Move the thread here in order to get more helpful answers.

|||

Hi Juan,

I'm not sure why this got moved here, it seems like a problem with the DataSet definition...

It would help to have the actual error you're getting, but my guess is that the field you added didn't acutally get the Identity proprty set correctly, so it is not generating new values automatically. When you try to add a new record, there is no value specified for your PK so you get the error. Verify that the field works correctly outside of your DataSet, if it doesn't, then you have a problem with the table, if it does, then there is something wrong with your DataSet.

Mike

|||Ok. I've got the answer. After change the datatype in the table as IDENTITY, and refresh the dataset, the identity atribute in the dataset field does not change automatically. Thanks Mike