c# - Return ID if record exist, else Insert and return ID -

i have below c# code check if record not exist, insert , return id. need if record exists, return value. change should make c# , sql part happen? database sql server. still have use executescalar() this?

con.open();  // insert clinreffiletypemaster string command1 = string.format(     "if not exists (select * [clinreffiletypemaster] [clinreftypename] = '{0}') insert [clinreffiletypemaster] ([clinreftypename]) output inserted.[clinreftypeid] values('{0}')",       datatoparse[i][0]       ); sqlcommand clinreffiletypemaster = new sqlcommand(command1, con);  // check if there value  object checkvalue = clinreffiletypemaster.executescalar();   if (checkvalue != null)     clinreffiletypeid = (int)checkvalue; 

there many ways achieve this. 1) can in inline sql 2) can in stored proc. 3) can in code split code code frankly doing much. in general avoid insert/query in same method.

also try use sqlparameters instead of building query string concat.

i propose makes code bit more readable

    public int insertandretrieveclientrefid(string clientreftypename)     {         int id = getidifrecordexists(clientreftypename);          if (id == 0)         {              // insert logic here              id = getidifrecordexists(clientreftypename);         }          return id;     }      public int getidifrecordexists(string clientreftypename)     {         int id = 0;          string command = "select id clinreffiletypemaster clinreftypename = @clinreftypename";         sqlparameter nameparameter = new sqlparameter("@clinreftypename", system.data.sqldbtype.nvarchar, 10) { value = clientreftypename };          using (sqlconnection connection = new sqlconnection("connectionstring"))         {             using (sqlcommand cmd = new sqlcommand(command))             {                 cmd.parameters.add(newparameter);                 connection.open();                 cmd.connection = connection;                 int.tryparse(cmd.executescalar().tostring(), out id);             }         }          return id;     }