c# - Right Approach to Insert in Table then copy it to another table -

in application user insert book. example somebook insert 3 copies. table1.bookid = 1, table1.copy = 3, in table 3 books have primary key table2.accessionid = 1,2,3 table2.bookid = 1, 1, 1.

this current doing bad practice aaron bertrand said.

int booktitlesid; public void addbooktitle() {   int copy = int.parse(textbox2.text);   try   {     using (sqlconnection mydatabaseconnection = new sqlconnection(myconnectionstring.connectionstring))     {       mydatabaseconnection.open();        using (sqlcommand mysqlcommand1 = new sqlcommand("insert booktitles(booktitle, copies) values(@booktitle,  @copies)", mydatabaseconnection))       {         mysqlcommand1.parameters.addwithvalue("@booktitle", booktitletextbox.text);         mysqlcommand1.parameters.addwithvalue("@copies", copy);         mysqlcommand1.executenonquery();       }     }   }   catch (exception ex)   {     messagebox.show(ex.message, "exception");   } }  public void addbook() {   int copy = int.parse(textbox2.text);   try   {     (int x = 0; x < copy; x++)     {       using (sqlconnection mydatabaseconnection = new sqlconnection(myconnectionstring.connectionstring))       {         mydatabaseconnection.open();         using (sqlcommand mysqlcommand1 = new sqlcommand("insert book(booktitleid) values(@booktitleid)", mydatabaseconnection))         {           mysqlcommand1.parameters.addwithvalue("@booktitleid", booktitlesid);           mysqlcommand1.executenonquery();         }       }     }   }   catch (exception ex)   {     messagebox.show(ex.message, "exception");   } }  private void form_load(object sender, eventargs e) {   using (sqlconnection mydatabaseconnection = new sqlconnection(myconnectionstring.connectionstring))   {     mydatabaseconnection.open();     using (sqlcommand mysqlcommand1 = new sqlcommand("select top 1 booktitleid + 1 booktitles order booktitleid desc", mydatabaseconnection))     {       string x = mysqlcommand1.executescalar().tostring();       booktitlesid = convert.toint32(x);     }   } }  private void button1_click(object sender, eventargs e) {   addbooktitle();   addbook(); } 

simplify. notably, as discussed in other, related question, there absolutely no safety in going out , seeing current max is, adding 1, , assuming next identity value generated. can rely on number if retrieve after have inserted, , reliable way use scope_identity() (or, multiple-row insert, output clause).

create procedure dbo.insertbook   @booktitle nvarchar(256),   @copies    int begin   set nocount on;    declare @booktitleid int;    insert dbo.booktitles(booktitle, copies) select @booktitle, @copies;    select @booktitleid = scope_identity();    insert dbo.books(booktitleid) select @booktitleid   (select top (@copies) rn = row_number() on (order [object_id])         sys.all_objects order [object_id]) y; end go 

now can simplify c# code (i'm not c# guy, whether best approach or if compile beyond me, can sort out on own).

public void addbook() {   try   {     int copies    = int.parse(textbox2.text);     string btitle = booktitletextbox.text     using (sqlconnection conn = new sqlconnection ...blah blah...)     {       conn.open();        using (sqlcommand cmd = new sqlcommand("exec dbo.insertbook", conn))       {         cmd.commandtype = commandtype.storedprocedure;         cmd.parameters.addwithvalue("@booktitle", btitle);         cmd.parameters.addwithvalue("@copies",    copies);         cmd.executenonquery();       }     }   }   catch (exception ex)   {     messagebox.show(ex.message, "exception");   } }  private void button1_click(object sender, eventargs e) {   addbook(); }