There are tables on SQL Server between which there is a link:

Table1: (ID Identity INT, FileName nvarchar(255)) Table2: (ID Identity INT, ParentId INT(Внешний ключ к Table1) ...)

On the client side, the program should generate 2 empty DataTables with new records and send them to the server => insert in these tables on the server.

How to do this so that there are no problems with connections, because in advance I do not know which IDENTITY will be generated by the server.

Also, other users can insert data into these tables (on the server). Will the logic of multi-use change, or can we abstract from it?

  • Insert in the database the data of the first table. Get back the generated ID. Insert in the database the data of the second table already with these IDs. - Alexander Petrov
  • You can solve the problem dramatically: go to GUIDs. - Alexander Petrov
  • @AlexanderPetrov, the structure of the table can not be touched. Can I have a sample code for your first comment? Never sent tables with relationships to server ... - iluxa1810
  • If the data sources in the application are created through the dataset designer in VS, then the update code could be generated automatically, taking into account foreign keys. And so, see here and here . - Alexander Petrov
  • one
    The technology ADO.NET has not changed since then. I think there are no newer books in Russian. Just now, few people use raw ADO.NET, more different ORM type EF. - Alexander Petrov

1 answer 1

How to do this so that there are no problems with connections, because in advance I don’t know what IDENTITY will be generated by the server.

This can be done as follows.

Suppose on the client side in the C # code we have tables

 var table1 = new DataTable("Table1"); table1.Columns.Add("ID", typeof(int)); table1.Columns.Add("FileName", typeof(string)); var table2 = new DataTable("Table2"); table2.Columns.Add("ID", typeof(int)); table2.Columns.Add("ParentID", typeof(int)); table2.Columns.Add(...);

which we fill with some data. If the data is always new and on the SqlServer side are subject to unconditional insertion, then we generate any IDs. This will be a "temporary" ID.

We send the data table1 and table2 some way to the server (via table parameters, using SqlBulkCopy into temporary tables, or in some other way). Suppose that you send data to temporary tables #table1 and #table2 .

On the SqlServer side, in order to insert data into the first table, MERGE use MERGE with the OUTPUT clause, which will allow us to compare the temporary IDs we assigned with the actual ones. Then we insert the data into the second table using this mapping:

 create table #map1 (TmpID int, ActualID int, primary key (TmpID)); merge into Table1 t using #table1 s on 1 = 0 when not matched then insert (FileName) values (s.FileName) output s.ID, inserted.ID into #map1 (TmpID, ActualID); insert into Table2 (ParentID, ...) select m1.ActualID, ... from #table2 t2 left join #map1 m1 on m1.TmpID = t2.ParentID; drop table #map1;

Also, other users can insert data into these tables (on the server). Will the logic of multi-use change, or can we abstract from it?

When multi-use no correction is required.

  • Hmm ... Something I completely forgot about INSERTED. Then, probably, you can do the same without Merge. And is it necessary to merge everything into temporary tables? Can't you push everything into Table1, then get the actual IDs for the DataTable, update them in DataTable2 and insert the entries in Table2? - iluxa1810
  • @ iluxa1810, "without Merge" - does it mean using insert ? No, with the same insert fails, insert will not allow to send s.ID to output. Only merge allows simultaneously with inserted. * To send data from a source to output. - i-one
  • @ iluxa1810, "Can't you push everything into Table1, then get the actual IDs for the DataTable, update them in DataTable2, and insert the entries in Table2?" probably you can. This is a slightly different architectural approach. Perhaps it may be more convenient in some cases. Here the client and the server agree on the data format (#table, @ table, xml or something else). The client sends data to the server, the server imports it (in a separate CP, for example). With a two-stage import, the client becomes more involved in the process. - i-one