Click here to Skip to main content
14,828,731 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
i created desktop application in accounting Field using c# and SQL Server 2014 . i have more than 36 users using my program at the same time . i already hosted my database at smarterAsp.net . my program Consists of 7 modules(Fixed Assets- Inventory - Purchasing
-Sales - Treasury - Bank - General Ledger) every module has its own tables inside database
so my database consists of more than 120 tables .

I created table named(Tbl_TransactionNumberGenerator) this table will generate number for any action that affects the database like (Add- Update - Delete) . i did that because
i needed every action must has unique number so if i the user want to open any form and wanted to edit any action it will be easy to the program searching inside database.
The general idea of the table is as follows :
C#
void GetLastransactionNumber()
{

var _db = new DatabaseContext();
            var lastTransactionNumber = _db.TransactionNumberGenerators
                .Select(p => p.TransactionNumber + 1).DefaultIfEmpty(1).Max();
            txttransactionNumber.Text = lastTransactionNumber.ToString(); 

}

and this is my table:

C#
public class TransactionNumberGenerator
    {
        public int Id { get; set; }

        public string ModuleName { get; set; }

        public int TransactionNumber { get; set; }
       
    }

and then i insert generated number inside this table to TransactionNumber column
so every transaction taken by user the table will generate last number plus one
(p.TransactionNumber + 1)

so every user who uses the program will generate more than 40 transaction per day
-------------
the problem is that when number of rows inside my table becomes more than 1000 rows
my program stopped working for any screen uses this table but other screens works well.
and the program gives this error(execution timeout expired . the timeout period elapsed prior to completion of the operation or the server is not responding).

is the is is any solution

What I have tried:

C#
void GetLastransactionNumber()
{

var _db = new DatabaseContext();
            var lastTransactionNumber = _db.TransactionNumberGenerators
                .Select(p => p.TransactionNumber + 1).DefaultIfEmpty(1).Max();
            txttransactionNumber.Text = lastTransactionNumber.ToString(); 

}
Posted
Updated 1-Mar-21 6:10am
v2

1 solution

That is not a good way to generate transactions numbers - what if there were two users trying to generate a transaction at the same time - it is possible that both will get the same transaction number!

You could try using uniqueidentifier (Transact-SQL) - SQL Server | Microsoft Docs[^] and NEWID (Transact-SQL) - SQL Server | Microsoft Docs[^]. Although be aware that using GUIDs can cause maintenance issues and fragmentation (I can't find my source for this at the moment, will revisit when I do).
You could generate this within the C# code Guid.NewGuid Method (System) | Microsoft Docs[^]

A better route might be to have a transaction table that uses an Identity column to automatically generate the unique value - see IDENTITY (Property) (Transact-SQL) - SQL Server | Microsoft Docs[^].

Use a stored procedure to insert an arbitrary value into the transaction table and return the identity generated by SQL Server - use one of the four methods provided Use the right tool to get identity values back after an insert - DEV Community[^]

Edit: I can't find the original source I referred to above, but this discussion might help GUIDs as clustered index – SQLServerCentral[^]
   
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900