CODEWITHSUNDEEP
c#.netentity-framework
xwrs
xwrs

Reputation: 1297

Unique number identifier generation

I have to create logic for generation unique number identifier for records in database. id, generated in database is a separate column.

At this moment, when user calls "create record" action, I save new record, get its database id, generate record number using this id, then put it to the edit form. Using this way means that all entity fields should be nullable to save record to database.

I don't like this way. I know that should be better way.

Is there a better practice to generate unique number identifier? What is possibility of generating non-unique random numbers?

Thank you

Upvotes: 3

Views: 2582

Answers (5)

RameshAyi
RameshAyi

Reputation: 11

Code for Table..

CREATE TABLE TblTransactions(
    TId varchar(8),
    TName varchar(50)
)

C# Code Behind…

protected void Page_Load(object sender, EventArgs e)
{
    string id = GenerateId("TblTransactions", "TId", 8, "TRN");
// insert the id along with data in the table
    Response.Write(id);
}

public string GenerateId(string TableName, string ColumnName, int ColumnLength, string Prefix)
{
    SqlConnection con = new SqlConnection("server=.;integrated security=true;database=EBissCard");
    string Query, Id;
    int PrefixLength, PadLength;
    PrefixLength = Convert.ToInt32(Prefix.Length);
    PadLength = ColumnLength - PrefixLength;
    Query = "SELECT '" + Prefix + "' + REPLACE(STR(MAX(CAST(SUBSTRING(" + ColumnName + "," + Convert.ToString(PrefixLength + 1) + "," + PadLength + ") AS INTEGER))+1," + PadLength + "),' ',0) FROM " + TableName;

    SqlCommand com = new SqlCommand(Query, con);
    con.Open();
    if (com.ExecuteScalar().ToString() == "")
    {
        Id = Prefix;
        for (int i = 1; i <= PadLength - 1; i++)
        {
            Id += "0";
        }
        Id += "1";
    }
    else
    {
        Id = Convert.ToString(com.ExecuteScalar());
    }
    con.Close();
    return Id;
}

Upvotes: 1

rekire
rekire

Reputation: 47945

Try the Random class from .net itself.

Upvotes: 0

Steve Morgan
Steve Morgan

Reputation: 13091

The pattern that you're using, of saving an empty record simply to get the ID, is not a good one.

The standard approach, and the one that I'd recommend, is for Create Record to simply display an empty form (the ID at this point will typically be 0). The user fills in the form and the data is only committed to the database when the user clicks Save. The ID should be an IDENTITY column.

A problem with your approach is that if users do not complete the form, you end up with lots of incomplete records in your database. And, of course, it makes it much more difficult to handle data validation and integrity.

An alternative approach, if you really must display the ID to the user, is to have a separate table containing a row with a "Next Record ID" column. This column can be incremented and returned as an atomic operation and used to populate the ID of your new record. You still don't create the real record, just increment this "Next Record ID" in your Create Record action. Using this approach, you can use the same approach for multiple entities by having separate rows for each in this "Record IDs" table. Bear in mind that if the user does not ultimately save the record to the database, an ID will still have been 'used up'. The numbers will still be unique and will be chronological but won't necessarily be contiguous.

Upvotes: 7

Gopal Nair
Gopal Nair

Reputation: 840

An idea to generate a unique number for a record is to use the time() in milliseconds (since a reference point of time, say, 01/01/2010).

However, if there are 2 records that are simultaneously getting updated, this may cause an issue. To solve this problem, if each of the user can be assigned a number (when creating the userID), a combination (concatenation) of that "user number" and time in milliseconds will give you the unique number you need.

Upvotes: 0

balexandre
balexandre

Reputation: 75073

I don't get it, but, if you are using the uniqueidentifier data type in your database, that translates to Guid in C#, so you can do:

public Guid CreateRecord(MyObject model) {

    Guid newId = Guid.NewGuid();

    MyTable tbl = new MyTable();
    tbl.guid = newId;

    // ... other columns

    db.MyTable.AddObject(tbl);
    db.SaveChanges();

    return newId;
}

though what I normally do, is having the PrimaryKey as int and add a uniqueidentifier field named guid (that I use it publically instead the column_id) and remember to index that column.

Upvotes: 1

Related Questions