Thursday, 22 January 2009

Insert data to database and get generated key back

Sample table with identity column.

CREATE TABLE [dbo].[Table1](
  [Id] int IDENTITY(1,1) NOT NULL,
  [Value] [varchar](10) NOT NULL,
  CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED 
  (
    [Id] ASC
  )
)

C# code than inserts data to database and gets generated id back.

class TestInsertDao : AdoDaoSupport
{
  private string insertSql = @"
INSERT INTO Table1(Value)
VALUES(@Value)
SET @Id = SCOPE_IDENTITY()
";
  public TestInsertDao(IDbProvider dbProvider)
  {
    this.DbProvider = dbProvider;
  }
  public int Insert(string value)
  {
    IDbParameters parameters = CreateDbParameters();
    parameters.Add("Value", DbType.String).Value = value;
    parameters.AddOut("Id", DbType.Int32);
    AdoTemplate.ExecuteNonQuery(CommandType.Text, deleteSql, parameters);
    return (int)parameters["@Id"].Value;
  }
} 

To get more info on C# spring setup refer to this post Using Spring.NET to load data to DataTable

No comments:

Post a Comment