Max Value Retreive Code

Question

Hi

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

Naimish Makwana · 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: Transaction Begins : The table is locked when the transaction starts. Stored Procedure Execution : The TABLOCKX hint ensures the table remains locked during the execution of the stored procedure. Transaction Commit : The table is unlocked when transaction.Commit() is called, indicating the transaction is successfully completed. 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 Makwana · Answer

Please check answer here https://www.c-sharpcorner.com/Forums/error-sharp160conversion-failed-when-converting-the-varchar-value

Naimish Makwana · Answer

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

Ramco Ramco · Answer

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 Ramco · Answer

Hi Naimish When the table will get unlock Thanks