c# - TSQL - select inserted rows -

is there way select inserted row? trying run following query using sqlcommand (it used reserve given id in database):

insert tbl (id) select count(*) + 1 id tbl 

is there way return inserted id column can use in app? or maybe there easier way can achieve this? have absolutely sure reserve free id, if multiple users use app @ same time.

also, there way can change query pick first free id can avoid gaps?

you can use output clause output e.g. newly created id's result set, , can read c# app use standard sqldatareader:

insert tbl (id) output inserted.id    select count(*) + 1 id tbl 

update: data returned output clause can captured c# side if regular select statement:

string insertstmt = "insert tbl (id) " +                      " output inserted.id " +                      " select count(*) + 1 id tbl";  using (sqlconnection conn = new sqlconnection(-your-connection-string-here-)) using (sqlcommand cmd = new sqlcommand(insertstmt, conn)) {     conn.open();      // execute insert statement sqldatareader     using(sqldatareader reader = cmd.executereader())     {         // read values returned output clause         while(reader.read())         {             int insertedid = reader.getint32(0);             // values....                         }     }      conn.close(); }