title: ADO.net insert a new row into a table that has a IDENTITY column, how to do that correctly?
body: <p>I have a table in a SQL Server instance and it looks like:</p>
  
  <pre><code>IF OBJECT_ID(N'dbo.employees', N'U') IS NOT NULL
      DROP TABLE dbo.Employees;
  GO
  
  CREATE TABLE dbo.employees
  (
   id INT IDENTITY
          PRIMARY KEY,
   name NVARCHAR(20),
   age TINYINT,
   birthday DATETIME
  );
  </code></pre>
  
  <p>I'm learning ADO.net and I want to use insert a new row into the table. My code is as follows:</p>
  
  <pre><code>string connectionString = @"Server=.\sql2014;Database=Test;Trusted_connection=True;";
  SqlConnection connection = new SqlConnection(connectionString);
  
  SqlDataAdapter adapter = new SqlDataAdapter("select * from dbo.employees", connection);
  new SqlCommandBuilder(adapter);
  
  DataSet dataSet = new DataSet();
  adapter.Fill(dataSet, "employees");
  
  dataSet.Tables[0].Constraints.Add("pk_employees", dataSet.Tables[0].Columns[0], true);
  
  DataRow row = dataSet.Tables[0].NewRow();
  row["id"] = 1;
  row["name"] = "Hi";
  row["age"] = 33;
  row["birthday"] = "1982-10-09";
  
  dataSet.Tables[0].Rows.Add(row);
  adapter.Update(dataSet.Tables[0]);
  
  MessageBox.Show("Inserted!");
  </code></pre>
  
  <p>The <code>id</code> column of the table is of IDENTITY property, which mean its value will automatically be generated. However, I have to set <code>row["id"]</code> to some integer value, otherwise I will get an error "the id column doesn't all null". When I use SQL Server profiler to capture the executed SQL, I found that the executed SQL looks like:</p>
  
  <pre><code>exec sp_executesql N'INSERT INTO [dbo].[employees] ([name], [age], [birthday])
  VALUES (@p1, @p2, @p3)',
  N'@p1 nvarchar(4),@p2 tinyint,@p3 datetime',@p1=N'Hi',@p2=33,@p3='1982-10-09 00:00:00'
  </code></pre>
  
  <p>I want to know, what's the correct way to insert data in such a table? If the table already has some rows and its max id is 10. When I set row["id"] to some values that are smaller than 10, I'll get another error "the id column doesn't allow duplicated value". Does that means I need to get the max id before I insert new rows into the table each time?</p>