SqlCommand. ExecuteScalar

The following example creates a SqlCommand and runs it using ExecuteScalar. Two strings are passed to this example. One String indicates the new value to be inserted into the table, and the other string is used to connect to the data source. If a new row has been inserted, this function returns the value of the new "Identity" column. If it fails, 0 is returned.

01 static public int AddProductCategory( string newName, string connString) 02 { 03      Int32 newProdID = 0; 04      string sql = 05          "INSERT INTO Production.ProductCategory (Name) VALUES (@Name); " 06          + "SELECT CAST(scope_identity() AS int)" ; 07      using (SqlConnection conn = new SqlConnection(connString)) 08      { 09          SqlCommand cmd = new SqlCommand(sql, conn); 10          cmd.Parameters.Add( "@Name" , SqlDbType.VarChar); 11          cmd.Parameters[ "@name" ].Value = newName; 12          try 13          { 14              conn.Open(); 15              newProdID = (Int32)cmd.ExecuteScalar(); 16          } 17          catch (Exception ex) 18          { 19              Console.WriteLine(ex.Message); 20          } 21      } 22      return ( int )newProdID; 23 }