Advanced Auto-Update Code to get Next Unique ID

Tips submitted by ASP.NET Maker users

Advanced Auto-Update Code to get Next Unique ID

Postby xgis » Sat Nov 09, 2013 8:34 am

This code is implemented "globally" so it can be used by code throughout the system.
It simply gets the last row ID created in the database and adds 1 to it to get the next unique row ID.
It was developed for use with Custom Folder Image Upload using the Row_Inserting event.
Its purpose is to create a Custom Folder with the value that is calculated. This code will be presented in a subsequent post.

It is important that you add this "function" to your "Advanced | Auto-Update values" after the existing fixed functions like so... ",ew_CurrentDateTime(),SiteIDMax()"

Here is the code for this tip;

public static int SiteIDMax()
{
int SiteID;
SiteID = Convert.ToInt32(ew_ExecuteScalar("SELECT max(SiteID) FROM <TABLE>"))+ 1;
//HttpContext.Current.Response.Write(Convert.ToInt32(SiteID)); //This is only used to display the value in development to confirm it is correct.
return SiteID;
}

Just replace "SiteID" with your Unique ID column, and replace <TABLE> with your schema.table name to implement.

This method may not be suitable for concurrently accessed databases but is fine for low traffic systems.
xgis
 
Posts: 87
Joined: Thu Jan 17, 2013 10:40 pm

Re: Advanced Auto-Update Code to get Next Unique ID

Postby xgis » Sat Nov 09, 2013 12:09 pm

Here is a code replacement which includes error handling when there are NO existing records in your table.
In the dependent code that consumes this function it ensures that if no records exist then it does not run the function and simply assigns 1,
which becomes the number associated with the 1st folder that is created that then corresponds with the actual row record 1.

public static int SiteIDMax()
{
int SiteID = 0;
try
{
int count=Convert.ToInt32(ew_ExecuteScalar("SELECT max(SiteID) FROM DMPDB.DMP_Sites"));
if(count==0)
{
}
}
catch(Exception ex)
{
return 1;
}
SiteID = Convert.ToInt32(ew_ExecuteScalar("SELECT max(SiteID) FROM DMPDB.DMP_Sites"))+1;
return SiteID;
}
xgis
 
Posts: 87
Joined: Thu Jan 17, 2013 10:40 pm

Re: Advanced Auto-Update Code to get Next Unique ID

Postby xgis » Mon Nov 18, 2013 9:19 am

Another approach to consider for dealing with the fresh table issue is letting the database do the work.
For a SQL Server database, you can modify the query so it never returns NULL, like this:
SELECT ISNULL(max(<IDField>),1) FROM <Table>
xgis
 
Posts: 87
Joined: Thu Jan 17, 2013 10:40 pm

Re: IMPROVED Auto-Update Code to get Next IDENTITY (MSSQL)

Postby xgis » Thu Dec 12, 2013 11:53 am

This code replaces previous code that had 1 fundamental issue;
1. If the last ID was deleted e.g. #10 and you were loading #11, then it would count to #9 and Add 1, making a new folder 10 in the related "Row_Inserting" event.

The new code uses the IDENTITY (1,1) column in MSSQL and identifies the NEXT IDENTITY as well as Substitutes an IDENTITY if one does not exist (eg empty table).
There is also some error message handling which I have not seen work as yet, because I have had no exceptions.
This code has been tested against row inserts for new and existing tables by deleting key rows including row 1 and the last rows of any table.

The resource has been RENAMED from SiteMaxID to NextRowID to make it more Generic and better define the new type of calculation taking place. Here are the refined and commented codes;

public static int NextRowID()
{
// Static Function for ANM10 Tools | Extensions to obtain the next IDENTITY Increment in the database
//This public static int function is entered like so; CurrentUserName(),..ew_CurrentDateTime(),NextRowID()

// The following code also resolves table upload issues where no existing data exists
// The following code also calculates the next IDENTITY for ADDing records using Row_Inserting

// Customisation1 - Change "SiteID" to your Identity Column Field Name e.g. "YourID"
// Customisation2 - Change "DMPDB" to your Schema eg "dbo"
// Customisation3 - Change "DMP_Sites" to your Table Name eg "YourTableName"

int NextRowID = 0;
try
{
//The following code checks if the table is empty. If so it returns a value of 1 for the 1st custom folder
int count=Convert.ToInt32(ew_ExecuteScalar("SELECT IDENT_CURRENT('DMPDB.DMP_Sites') AS SiteID FROM DMPDB.DMP_Sites"));
if(count==0)
{
return 1;
}
}
catch(SystemException ex)
{
HttpContext.Current.Response.Write(ex.Message);
}

try
{
//The next line of code is for the Row_Inserting event. It returns the NEXT IDENTITY Value for the Table
NextRowID = Convert.ToInt32(ew_ExecuteScalar("SELECT IDENT_CURRENT('DMPDB.DMP_Sites')+ IDENT_INCR('DMPDB.DMP_Sites')"));
{
return NextRowID;
}
}
catch(SystemException ex)
{
HttpContext.Current.Response.Write(ex.Message);
}
return 0;
}
xgis
 
Posts: 87
Joined: Thu Jan 17, 2013 10:40 pm


Return to User Submited Tips (ASP.NET Maker)