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:

Expand|Select|Wrap|Line Numbers

 
private int GetNextRowID()

{

int NextRowID =0; 
 
try

{ 
 
using (SqlConnection ConnectionSql = new SqlConnection(ConnectionString))

{

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_CURRENT('ExampleTable'), 0) ; "); 
 
SqlCommand CommandSql = new SqlCommand(SqlQuery.ToString(), ConnectionSql);
 
ConnectionSql.Open();
 
SqlDataReader DataReaderSql = CommandSql.ExecuteReader();
 
while (DataReaderSql.Read())

{

NextRowID = int.Parse(DataReaderSql.GetValue(0).ToString());

}
 
ConnectionSql.Close();
 
MessageBox.Show("Add Row.");

}

}

catch (Exception ex)

{

MessageBox.Show(ex.ToString());

}

return NextRowID;

}

SQL:

Expand|Select|Wrap|Line Numbers

 
USE [master]

GO
 
IF EXISTS (SELECT name FROM sys.databases WHERE name = 'ExampleDatabase') 

DROP DATABASE [ExampleDatabase]; 

GO
 
CREATE DATABASE [ExampleDatabase]; 

GO
 
USE [ExampleDatabase]; 

GO
 
IF EXISTS (SELECT NAME FROM SYS.TABLES WHERE NAME = 'ExampleTable') 

DROP TABLE dbo.ExampleTable; 

GO
 
CREATE TABLE 

dbo.ExampleTable

(

ID INT IDENTITY(1,1) NOT NULL, 

Name NVARCHAR(50) NULL 

);

GO
 
SELECT * FROM dbo.ExampleTable;

GO
 
--SAME AS AFTER INSERTING:

SELECT ISNULL(IDENT_CURRENT('ExampleTable'), 0) ;

GO
 
INSERT INTO ExampleTable

VALUES

(

'Bill'

);

GO
 
SELECT * FROM dbo.ExampleTable;

GO
 
--SAME AS BEFORE INSERTING:

SELECT ISNULL(IDENT_CURRENT('ExampleTable'), 0) ;

GO

Plater

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

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

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

Similar C# / C Sharp bytes

/bytes/development

/bytes/it

/bytes/about