C# How to get the next value of identity(1,1) from database table before any insert?

[qwedster]

Hi!

Every time when I want to add a row, I call this method in the code snippet to get the next identity value.

Code Snippet:
[code=c#]
private int GetNextRowID()
{
int NextRowID =0;

try
{

using (SqlConnection ConnectionSql = new SqlConnection(C onnectionString ))
{
StringBuilder SqlQuery = new StringBuilder() ;

SqlQuery.Append ("USE [ExampleDatabase]; ");

// Returns 1 so works for first insert when the table is empty before any insert:
// Returns 1 again for second insert, so it FAILS:
SqlQuery.Append ("SELECT ISNULL(IDENT_CU RRENT('ExampleT able'), 0) ; ");

SqlCommand CommandSql = new SqlCommand(SqlQ uery.ToString() , ConnectionSql);

ConnectionSql.O pen();

SqlDataReader DataReaderSql = CommandSql.Exec uteReader();

while (DataReaderSql. Read())
{
NextRowID = int.Parse(DataR eaderSql.GetVal ue(0).ToString( ));
}

ConnectionSql.C lose();

MessageBox.Show ("Add Row.");
}
}
catch (Exception ex)
{
MessageBox.Show (ex.ToString()) ;
}
return NextRowID;
}
[/code]

SQL:
[code=SQL]
USE [master]
GO

IF EXISTS (SELECT name FROM sys.databases WHERE name = 'ExampleDatabas e')
DROP DATABASE [ExampleDatabase];
GO

CREATE DATABASE [ExampleDatabase];
GO

USE [ExampleDatabase];
GO

IF EXISTS (SELECT NAME FROM SYS.TABLES WHERE NAME = 'ExampleTable')
DROP TABLE dbo.ExampleTabl e;
GO

CREATE TABLE
dbo.ExampleTabl e
(
ID INT IDENTITY(1,1) NOT NULL,
Name NVARCHAR(50) NULL
);
GO

SELECT * FROM dbo.ExampleTabl e;
GO

--SAME AS AFTER INSERTING:
SELECT ISNULL(IDENT_CU RRENT('ExampleT able'), 0) ;
GO

INSERT INTO ExampleTable
VALUES
(
'Bill'
);
GO

SELECT * FROM dbo.ExampleTabl e;
GO

--SAME AS BEFORE INSERTING:
SELECT ISNULL(IDENT_CU RRENT('ExampleT able'), 0) ;
GO
[/code]

[Plater]

IDENT_CURRENT returns the LAST identity used, not the next.
Try adding 1