Max Value Retreive Code

Ramco Ramco

In below code i am getting Column Max Value from a table. I want that at same time 2 User should not be able to access same value

using (SqlConnection con0 = new SqlConnection(Common.CommonFunction.cnn_Live))
{
    SqlCommand cmd0 = new SqlCommand("sp_MaxDocEntry", con);
    cmd0.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.AddWithValue("@TableName", SqlDbType.VarChar).Value = "Department";
    SqlParameter successParam1 = cmd.Parameters.Add("@Success", SqlDbType.Bit);
    successParam1.Direction = ParameterDirection.Output;
    SqlParameter DocEntry = cmd.Parameters.Add("@DocEntry", SqlDbType.Int);
    DocEntry.Direction = ParameterDirection.Output;
    con.Open();

    cmd.ExecuteNonQuery();
    bool success1 = (bool)successParam1.Value;
    if (success1)
    {
        cmd.Parameters.AddWithValue("@DocEntry", SqlDbType.Int).Value = successParam0;
        int MaxDocEntry = Convert.ToInt32(DocEntry.Value);
    }
    else
    {
        ShowMessage("", success1.ToString(), "error");
    }
}

Thanks

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Naimish Makwana2y ago
Accepted answer
The table will be unlocked as soon as the transaction is completed. In the provided code, this happens when the transaction.Commit() method is called. If an error occurs, the transaction.Rollback() method will be called, which also releases the lock.

Here’s a brief overview of the process:
1. Transaction Begins: The table is locked when the transaction starts.
2. Stored Procedure Execution: The TABLOCKX hint ensures the table remains locked during the execution of the stored procedure.
3. Transaction Commit: The table is unlocked when transaction.Commit() is called, indicating the transaction is successfully completed.
4. Transaction Rollback: If an error occurs, transaction.Rollback() is called, which also releases the lock.
This ensures that the table is only locked for the duration of the transaction, preventing other users from accessing the same value simultaneously.

Thanks
Naimish Makwana2y ago
Please check answer here

https://www.c-sharpcorner.com/Forums/error-sharp160conversion-failed-when-converting-the-varchar-value
Ramco Ramco2y ago
Hi naimish

What will be the value if there is no record. I want if there is no record @Docentry value should be 1

Thanks
Ramco Ramco2y ago
Hi Naimish

When the table will get unlock

Thanks

Naimish Makwana2y ago

To ensure that two users do not access the same value simultaneously, you can use SQL Server’s locking mechanisms. One way to achieve this is by using the TABLOCKX hint, which locks the entire table during the transaction. This will prevent other users from accessing the table until the transaction is complete.

Here’s how you can modify your stored procedure to include the TABLOCKX hint:

ALTER PROCEDURE [dbo].[MaxDocEntry]
    @TableName VARCHAR(50),
    @DocEntry INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @SQL NVARCHAR(1000);
    DECLARE @Result INT;

    SET @SQL = 'SELECT @Result = MAX(docentry) FROM ' + @TableName + ' WITH (TABLOCKX)';

    EXEC sp_executesql @SQL, N'@Result INT OUTPUT', @Result OUTPUT;

    SET @DocEntry = @Result;
END

In your C# code, make sure to handle the transaction properly:

using (SqlConnection con = new SqlConnection(Common.CommonFunction.cnn_Live))
{
    con.Open();
    using (SqlTransaction transaction = con.BeginTransaction())
    {
        try
        {
            SqlCommand cmd = new SqlCommand("sp_MaxDocEntry", con, transaction);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.AddWithValue("@TableName", "Department");
            SqlParameter successParam1 = cmd.Parameters.Add("@Success", SqlDbType.Bit);
            successParam1.Direction = ParameterDirection.Output;
            SqlParameter DocEntry = cmd.Parameters.Add("@DocEntry", SqlDbType.Int);
            DocEntry.Direction = ParameterDirection.Output;

            cmd.ExecuteNonQuery();
            bool success1 = (bool)successParam1.Value;
            if (success1)
            {
                int MaxDocEntry = Convert.ToInt32(DocEntry.Value);
                // Use MaxDocEntry as needed
            }
            else
            {
                ShowMessage("", success1.ToString(), "error");
            }

            transaction.Commit();
        }
        catch (Exception ex)
        {
            transaction.Rollback();
            // Handle exception
            ShowMessage("", ex.Message, "error");
        }
    }
}

In this version:

The stored procedure uses the TABLOCKX hint to lock the entire table.
The C# code starts a transaction and commits it if everything is successful, or rolls it back in case of an error.

This approach ensures that only one user can access the maximum document entry at a time, preventing concurrent access issues. Thanks