Member Avatar for itslucky

Dear Friends,

When i enter a new record in SQL Database from my C# application, the record stores in the database at random locations, but i want to make sure that every record must be enter at the end of the Table.


Actually i have to get the Last Balance of Customer, and it must be at the last record, but whenever i put a new record it stores at the middle or above, i need it to be store at the last so i get the last balance...


Plz help me out Dear Friends...

  • 4 Contributors
  • 3 Replies
  • 219 Views
  • 6 Days Discussion Span
  • Latest Post Latest Post by jpattison

Dani AI

Generated

Short version: don't try to force a row to live "at the end" of the table. As and pointed out, physical placement is not a reliable ordering. For the specific goal of "get the last balance for a customer" the most robust approaches are (a) maintain the customer's current balance and update it together with inserting the transaction, or (b) give each transaction a deterministic sequence/timestamp and always query for the most recent one.

A practical, safe pattern is to perform the balance update and transaction insert inside a single transaction on the server so you always return the up-to-date balance. Example stored-procedure pattern:

CREATE PROCEDURE AddCustomerTransaction
  @CustomerId INT,
  @Amount DECIMAL(18,2)
AS
BEGIN
  SET NOCOUNT ON;
  BEGIN TRAN;

  UPDATE Customers
    SET Balance = Balance + @Amount
    WHERE CustomerId = @CustomerId;

  INSERT INTO Transactions (CustomerId, Amount, BalanceAfter, OccurredAt)
    VALUES (@CustomerId, @Amount,
            (SELECT Balance FROM Customers WHERE CustomerId = @CustomerId),
            SYSUTCDATETIME());

  SELECT SCOPE_IDENTITY() AS TransactionId,
         (SELECT Balance FROM Customers WHERE CustomerId = @CustomerId) AS NewBalance;

  COMMIT TRAN;
END

On the client side, call this procedure and read the returned transaction id/new balance (so your app never has to guess which row is "last"):

using(var cmd = new SqlCommand("AddCustomerTransaction", conn))
{
  cmd.CommandType = CommandType.StoredProcedure;
  cmd.Parameters.AddWithValue("@CustomerId", id);
  cmd.Parameters.AddWithValue("@Amount", delta);
  using(var rdr = cmd.ExecuteReader())
  {
    if (rdr.Read()) { var txId = rdr.GetInt32(0); var newBal = rdr.GetDecimal(1); }
  }
}

Notes and caveats: wrap update+insert in one transaction to avoid races; gaps in identity values can occur on rollback (so identity is not a perfect human-visible sequence); if you only read the most recent transaction, index the transactions table on (CustomerId, OccurredAt DESC) to make that lookup fast. This keeps the logic deterministic and avoids relying on how rows happen to be laid out on disk.

Recommended Answers

All 3 Replies

Member Avatar for kvprajapati

>How to put New record at the end of Database Table.
A table is an unordered recordset. The position of a record in the table is not relevant unless you impose an order on it.

If your table has transaction date column then use SELECT statement with ORDER BY clause.

str="select * from tableName order by transactiondate"
Member Avatar for sknake

The physical order of records written to an SQL table are determined by the clustered index if one is present. If one isn't present then they are just added in the order they are inserted and likewise they are returned in the order they are selected unless you use an order by clause.

You should add a "CreateDate" column or an auto incrementing record id column to determine which record is the last record. By default an autoinc primary key column creates the clustered index unless you specify to override it.

IE default value and clustered:

CREATE TABLE Test
(
  RecordId int identity(1000, 1) PRIMARY KEY
)

A nonclustered version:

CREATE TABLE Test
(
  RecordId int identity(1000, 1) PRIMARY KEY NONCLUSTERED
)

[edit]
Also if someone changes a clustered index that you are relying on to determine order than you cannot figure out by a column value then you run the risk of ruining your database by changing an index. This is a bad idea.... add another way to determine record sequence.
[/edit]

Edited by sknake because: n/a
Member Avatar for jpattison

...You should add a "CreateDate" column or an auto incrementing record id column to determine which record is the last record. By default an autoinc primary key column creates the clustered index unless you specify to override it.

[/edit]

After creating your table with the Create Date - or with the RecordID from Sknake above... then use Max(<fieldname>) to bring back the last entry.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.