Вопрос

the SQL Server fields for primary keys are set as autoincrement fields.

If I want to insert a new row in the Customers table, everything works fine. In this case, setting the ID field is useless, because it is changed by the server when the CustomerTableAdapter.Update method is called.

But,if I want to add a new row in the Orders table, I can omit the ID field as before, but which IDCust do I have to use??????

Here is my question:do I have to make it a two step process? First updating the Customer row, then getting the Customer ID, then create the Orders row, then again updating the Order row.If this were the case, it would be a big limit for my work: I cannot restore the previous situation if the user decide to abort the changes instead of committing, like all other ONE TABLE CHANGES I experienced before.I mean: I would like to make all the changes to the dataset and then decide to commit or abort in one step, but if I need to update in order to build data in relation-connected tables, this is not possible.

Is there a way to get the next autoincrement number by the server and book it somehow?

I'm a newbie here, and would like to understand the common how to in this situation.

Help me if you can.Thanks.-------
Life is what happens while doing other projects
-------

Ответы

The DataSet is able to take care of this for you. It's just a little non-obvious.

First, for your autoincrement fields, in the DataSet designer I would highly recommend that you set the AutoIncrementSeed and AutoIncrementStep both to -1 (negative 1). This will keep it clear which IDs are temporary and which ones are actually in the database. You might be doing this already.

Make sure that you have defined both a DataRelation with ForeignKeyConstraint line between parent Customers (IDCust) and Orders (IDCust) in the designer. Set the UpdateRule on this object to Rule.Cascade. This is critical as the first Update on the Customers table will, in the process, obtain the ID from the database into the Customers DataRow and cascade it into the child Orders DataRow(s) in the DataSet automatically.

In order for this to work correctly, your INSERT routine must actually do a SELECT that reports back the inserted ID. In other words, it must be written like the statements or stored procedures that the TableAdapter wizards in the Visual Studio GUI generate.

For example, INSERT is actually an INSERT, a semicolon, and then a SELECT. Here is an example of wizard generated SQL for INSERT:

> I would like to make all the changes to the dataset and then decide to commit or abort in one step

This always requires you to perform the commands within a transaction, such as an SqlTransaction or a TransactionScope. Otherwise the granularity will be one row at a time. This is how SQL Server works.

Все ответы

The DataSet is able to take care of this for you. It's just a little non-obvious.

First, for your autoincrement fields, in the DataSet designer I would highly recommend that you set the AutoIncrementSeed and AutoIncrementStep both to -1 (negative 1). This will keep it clear which IDs are temporary and which ones are actually in the database. You might be doing this already.

Make sure that you have defined both a DataRelation with ForeignKeyConstraint line between parent Customers (IDCust) and Orders (IDCust) in the designer. Set the UpdateRule on this object to Rule.Cascade. This is critical as the first Update on the Customers table will, in the process, obtain the ID from the database into the Customers DataRow and cascade it into the child Orders DataRow(s) in the DataSet automatically.

In order for this to work correctly, your INSERT routine must actually do a SELECT that reports back the inserted ID. In other words, it must be written like the statements or stored procedures that the TableAdapter wizards in the Visual Studio GUI generate.

For example, INSERT is actually an INSERT, a semicolon, and then a SELECT. Here is an example of wizard generated SQL for INSERT:

> I would like to make all the changes to the dataset and then decide to commit or abort in one step

This always requires you to perform the commands within a transaction, such as an SqlTransaction or a TransactionScope. Otherwise the granularity will be one row at a time. This is how SQL Server works.

OK. This method you proposed is straight. But, is it possible to work without SQL code , without connecting to the underlying database ? Can I achieve my goal by using instead ADO.NET objects like DataRow, DataTable and DataSet?

Is it possible to insert two brand new rows in two tables, connected by a DataRelation, and after that, have them correctly connected (primary key vs. foreign key) when the update method is called? This would be more elegant, IMHO.

For instance, I noticed the method SetParentRow....... may it be useful?

Thanks.-------
Life is what happens while doing other projects
-------

I go and explain: your response has gone beyond my limits when you say:

In order for this to work correctly, your INSERT routine must actually do a SELECT that reports back the inserted ID. In other words, it must be written like the statements or stored procedures that the TableAdapter wizards in the Visual Studio GUI generate.

For example, INSERT is actually an INSERT, a semicolon, and then a SELECT. Here is an example of wizard generated SQL for INSERT:

I just want to use ADO.NET object, not any SQL command or stored procedure or istant connection to the db. I want that all changes are only in the local datatables and if I want (only if) they can be committed.

I'm working on it.

See you.-------
Life is what happens while doing other projects
-------

What you are asking will have one problem like data inconsistency in Customer & Order Data (Only problem is in Getting Correct ID of each tables).

I mean when u work with dataset , you can add record in datatable (customer) but what about CustomerID that u will not have correct id when u work in disconnected mode(dataset). Now if u do not have CustomerID , you can not assign it to Order table.

so if you want to have customer id for your order table (foreign key) than you have to insert record in customer table (i.e database interaction needed). once you have customer id , you will be able to add order table with CustomerID.

I have a peopleTable (PK is peopleID) and i have a stakesTables (PK is stakeID) then i have a judgestable which only contains the peopleID, StakeID.

I can update with no problem. But if i .Addnew to the judges table i need to the the peopleID. I tested using

intretval = MainDbDataSet.Tables(

"lastID").Rows(0)("ID")
+ 1

just to get the max value and add 1 but this does not work if the last record has been deleted. (ie if the last peopleid was 7 and it was deleted,
then the next one will be 8 and the formula above returns 7)

and it does not work if the table is empty for the same reason as above.

Its a bussiness level Problem.In your case you have to add customer first.Then you have to update order.

Example:-

Whenever you are shoping in any online shoping store like ebay and Amazon they wont alloy you to buy till you register.Its a good practise as companies are keeping track of their customer,they have their information in case any fraud happen they can track
him.There are other benefit too once user is signed in you can send latest product detail.Then Signing user can book order so in your case you will have customer id.

But in your scenario bussiness logic will be wrong if you are putting dummy value in customer id as this field has to be taken from customer table.But if you want to do it anyhow.

then write a single store procedure which can insert in two table.Its mentioned below.

>> I just want to use ADO.NET object, not any SQL command or stored procedure or istant connection to the db. <<

The sample SQL statement that was provided by BC is similar to one constructed automatically by the wizards and placed in the .InsertCommand property of the Table Adaptor object. So ... no need to learn SQL, but I highly encourage you to learn the
basics so you know what the wizards are doing on your behalf.

>> Thanks it helped me to clear some basic consepts about dataset. But it is not working in MS-Access, Any Alternative? <<

Access is a different animal that SQL Server or other server based systems. One such difference is that you can not send two statements in one command to the Access (actually ACE or JET) database engine.

With an ACE/Jet back end, IIRC, you will need to create a RowUpdated event handler on the DataAdapter which handles the inserts into the parent table.

Set up the event handler with something like this after the dataset is created ...