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 :
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:
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:
void GetLastransactionNumber()
{
var _db = new DatabaseContext();
var lastTransactionNumber = _db.TransactionNumberGenerators
.Select(p => p.TransactionNumber + 1).DefaultIfEmpty(1).Max();
txttransactionNumber.Text = lastTransactionNumber.ToString();
}