We help IT Professionals succeed at work.

How to commit changes of a Typed Dataset to database at once

pradipkkoli
pradipkkoli asked
on
Hi,

        I am using Dotnet C# 2.0, also i am using a typed dataset. The scinario is not straight forward. Actually the typed dataset is filled with data from a xml file. While initially typed dataset is not connected to database and is also blank. After reading xml file now typed dataset is having data in parent and child tables.

       The problem is to update the data from typed dataset to sql server database again. I am not finding any method nor any reference to perform this operation. Please help.

Thanks and
Warm Regards

Pradip
Comment
Watch Question

Top Expert 2010

Commented:

Typed dataset includes all the funtionality of a dataset. Have you checked the typical way of updating DataSet using SqlDataAdapter.Update(DataSet ds) which is as following

DataSet dataSetFromXml = <data set loaded from xml>

using (SqlConnection connection = new SqlConnection(connectionString))
    {
        SqlDataAdapter adapter = new SqlDataAdapter();
        adapter.SelectCommand = new SqlCommand(selectQueryString, connection); // selectQueryString should include all the columns you want to update including the joins of multiple tables...
        SqlCommandBuilder builder = new SqlCommandBuilder(adapter);

        connection.Open();
       

        builder.GetUpdateCommand();
       
        adapter.Update(dataSetFromXml)        
    }

Note that this is untested and comes top off my head....

Author

Commented:
Hi Kris,

Thanks for reply. The query specified selectQueryString is the main problem. I think i was not clear in my question. The exact case is as follow:

1. I had created a typed dataset for a database, let say dataset1. Please note there is no database connection, neither datadapter and hence no commandset here. the dataset1 is empty when created.
2. I am having another dataset let say dataset2, which is having same structure as dataset1 but dataset2 is not a typed dataset. Hence we need to fill dataset1 (which is typed datset) manually from dataset2. This is done succesfully.
3. Now dataset1 (which is typed dataset) is filled with rows, either all tables or may be only one table, but this is sure that at least one table is filled (but which table is filled we are not aware).
4. Now the question is how to insert the new rows from dataset1 (typed) back to sql server offcourse using sql connection.

    Kris, as per your solution we need a query which you are using in string "selectQueryString". But as you can see in my case we do not know which table(s) need to be committed. That may be only one or all of table in typed dataset.

    I think now i am clear in my problem.

Thanks and
Warm Regards

Pradip
Top Expert 2010

Commented:

I hope you would know which tables and columns in database need to be updated. To simplify this lets take one table at a time. If dataSetFromXml.Tables[0] should go to the table named Table1 in db, then
selectQueryString can be = "SELECT col1, col2 FROM Table1"
and use
dataSetFromXml.Update(dataSetFromXml.Tables[0]) // use DataTable as param.

For this to work dataSet.Tables[0].TableName should = "Table1" and the ColumnName property of the columns in dataSet.Tables[0].Columns also should match the actual colulmn names...basically the schema..

Author

Commented:
Hi Kris,

Thanks again for reply. As per your suggestion we need to know the table. But this is the constraint we are not aware of the table, also as per your suggestion table by table iteration and insertion needs to make use of transaction which will rollback if any table insertion fails. This is not required neither feasible.

I am trying to update the data from typed dataset, Kris, just forgot about xml dataset, it work is over (as xml dataset datset2 in my example is just reading from xml and giving it's data to Typed dataset dataset1 in my example) and all data is there in typed dataset (dataset1 in my case in above example).

My problem is to updated this (typed) dataset to database, also no iteration on table, because this will involve use of transaction, first updating master tables and then child tables due to primary-foreign key relations, etc, etc. Hence i am looking for solution to update the entire Typed Dataset in Database, at once.

Please advice or suggest alternative.

Thanks and
Warm Regards

Pradip
Top Expert 2010

Commented:

I mentioned table iteration only to explain with simiplicity. I know it cant be a better or easy solution.

When a dataset is updated to the database, it updates the tables and columns as in the schema of the dataset which should match the tables/columns in the database. If table doesnt exist in database it will fail. In your case the dataset will have the schema as in the source xml and corresponding tables/columns should exist in the table. If you want the dataset into different tables/columns, you should change the dataset schema accordingly (one way is to transfer the data to a dataset whose schema matches with database).

When you say you dont know which table should it go, Do you mean that commiting the dataset should create the required tables and columns in database as per the schema in the dataset and add the rows? If so, dataset commit through sqladapter will not create tables/columns. You should create them separately (SMO-SQL Server Management Objects can be used for this using the existing dataset or schema) or through any other solution. Point is that it has to be done separately before dataset commit.

Alternatively i think there are options like exporting the xml data directly to database using Bulk Upload and Sqlxml. You can check if that would work for you.

Some links - might be helpful
=> http://bytes.com/topic/net/answers/176511-automagically-create-database-schema-xml-dataset
=> http://social.msdn.microsoft.com/Forums/en-US/sqlsmoanddmo/thread/591d50ae-fcc3-43ca-bf3e-e9c93335a37b

Author

Commented:
Hi Kris,

Thanks for reply. The links provided are very useful and totally new for me. When i implemented the code i found basically it generates script to create new tables in database. This is new for me really appreciated.

Kris, i think i am not clear about what my problem is! It seems me so. As from the previous example i have a typed dataset which already have database schema, we can be 100% assured that the schema in typed dataset is identical to the database. Hence only data needs to be committed in database, in case if any new table is present in typed dataset (which directly means that after creating typed dataset, a table in database was dropped!), hence while committing the data that table will be ignored. (This we will handle but that will be a rare case in our scenario).

As per your example or doubt! we do not required to create any table in database nor alter the schema of database. The simple is a typed database initially blank, then inserted new data (rows in one or all table) in type dataset table through code and now commit the data into database. The only condition is when typed dataset is initialized it is not connected to database, later we need to connect and commit the new data in database.

So Kris as per your example we are 1) not creating any new tables  and 2) no change in typed dataset schema.

To be simple i am putting easy steps what i did and what is the problem, be patience,

1) Let say today we created a typed dataset in Visual Studio, simply using wizard but not connected to database, hence no sqldatadapters or connection utilized here. So typed dataset is save as a class in our solution. (This is done)
2) We simply fill the type dataset table(s) from external xml file runtime, let say after 2 weeks. (Be assured all primary, foreign keys and required data is inserted in table(s) through code) [This is also done]
3) Now we have a typed dataset which have data in one or all tables. Now simple this new data needs to be inserted or committed to database, now the actual connection to database is required. [Kris This is the Problem, How to perform simple insert or commit in database!] (Note: Assume database schema is same as before 2 weeks when we created typed dataset schema, in any case only insert is required no update and no deletion, if any table is not found it will be exception or error, we do not need to create any new table in database neither alter the schema of database no matter schema of typed dataset has changed!)

Thanks Kris for holding your self, i think i am pretty now clear what my problem is. The problem is only
"Inserting or Committing typed dataset back to datatable!" simple to the point.

Please advice and yes thanks for valuable links i had never seen that codes and it will be very useful for further projects. As of my problem is still there.

Thanks and
Warm Regards

Pradip
Top Expert 2010
Commented:


I did some research over the web. Below are my understanding.

To update dataset to the database the typical method is to use SqlDataAdapter. But this requires to pass sql commands as well, at least SELECT statement with SqlCommandBuilder. Again SqlCommandBuilder is meant only for single-table updates. The example in this msdn link shows that => http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommandbuilder.aspx

To update multi-tables, using the same SqlDataAdapter/SqlCommandBuilder, it should be updated table-by-table one adapter for each table. This link shows that => http://msdn.microsoft.com/en-us/library/4esb49b4(v=VS.80).aspx

If you have only few tables to update, then I would say to follow the methods above. Since the typed dataset has been created from the database its schema would match the database.

Alternatively you can directly import the xml data into database using SQL Server's BULK INSERT or OPENROWSET(BULK…) or bcp utility. Again the exampless I have seen so far deals with one table at a time. => http://msdn.microsoft.com/en-us/library/ms175915.aspx

or if there is a third-party code library that scans the dataset and creates sqls and does the update, then that's what you are asking for. Basically 'update dataset to database without using sqldataadapter'. I will check and update if i find any such code.

or you can use other tools to export xml data to database like Altova DatabaseSpy, XMLSpy =>http://www.altova.com/databasespy/database-import-export.html

For the basic single table update below code works for me:

DataSet dataSet;
// dataset is filled here

using (SqlConnection connection = new SqlConnection(connectionString))
    {
        SqlDataAdapter adapter = new SqlDataAdapter();
        adapter.SelectCommand = new SqlCommand("SELECT col1, col2 FROM Table1", connection);
        SqlCommandBuilder builder = new SqlCommandBuilder(adapter);

        connection.Open();

        builder.GetUpdateCommand();
       
        adapter.Update(dataSet, tableName);
    }

Regarding your notes on dataset and connection, dataset doesnot have or store connection details. that is its special feature (dis-connected nature). It is the SqlDataAdapter that uses the connection. Also I think the problem is the same whether it is dataset or typed dataset. Typed dataset just helps you to use it like a business object. Underlying commit taks is the same for both.

Hope this helps to show you some light.








 

Author

Commented:
Excellent help, thanks!