How to save values in table in SQL without crashing when it is used by multiple users

[Lost User]

1. You're probably updating way more fields than the "business rule" requires; possibly fouling up foreign keys in the process. 2) Maybe a "Delete and insert" makes more sense than an "update" here. 3) It seems you're arbitrarily overwriting everything. You will probably have better success if you incorporate "versioning"; which will also make your "WHERE" clauses more intelligent instead of blindly accessing by line# only. That, and a transaction.

"(I) am amazed to see myself here rather than there ... now rather than then". ― Blaise Pascal

[TarunKumarSusarapu]

Thank you Mr.Ricard.These may be one of the reasons of data crashing.I will try to do modifications at my side and let you know the concerns about this.

[TarunKumarSusarapu]

Thank you Mr.Gerry.These may be one of the reasons of data crashing.Can u please explain the point 3 briefly and give me ways to write short code for that.

[TarunKumarSusarapu]

Here Passing a query as a parameter to get_data method.How can we use parameters here?

public string get_data(string sqlstr)
{
string strTemp = "";
try
{
double sum = 0;
if (cn.State != ConnectionState.Open) cn.Open();
TRcmd2.Connection = cn;
TRcmd2.CommandText = sqlstr;
TRdr = TRcmd2.ExecuteReader();
if (TRdr.Read())
strTemp = TRdr.GetValue(0).ToString();
TRdr.Close();
}
catch
{
try
{
if (!TRdr.IsClosed == true) TRdr.Close();
}
catch (Exception EX) { }
}
return (strTemp);
}

[Lost User]

[rowversion (Transact-SQL)](https://msdn.microsoft.com/en-us/library/ms182776.aspx)

"(I) am amazed to see myself here rather than there ... now rather than then". ― Blaise Pascal

[TarunKumarSusarapu]

I tried all the points as you mentioned here.But still, invoice number clash happens when the multiple users save at a time.Here the invoice number is unique.But in details part we can save multiple products for a particular invoice.But here clash happens when multiple users enter data at once. for example

users-->invoice no Products
Raj 1 J,K
Tarun 2 O,M

But at the time of saving it swapped like this

users-->invoice no Products
Raj 1 J,K,M-------->Clashing taken place
Tarun 2 O

Please give me suggestions to solve this.

[TarunKumarSusarapu]

I tried all the points as you mentioned here.But still, invoice number clash happens when the multiple users save at a time.Here the invoice number is unique.But in details part we can save multiple products for a particular invoice.But here clash happens when multiple users enter data at once. for example

users-->invoice no Products
Raj 1 J,K
Tarun 2 O,M

But at the time of saving it swapped like this

users-->invoice no Products
Raj 1 J,K,M-------->Clashing taken place
Tarun 2 O

Please give me suggestions to solve this.

[Richard Deeming]

For a start, don't store connection and command objects in fields. Instead, create them when you need them, and wrap them in a using block to ensure that they're always disposed of properly. You don't need to call ExecuteReader to get the value of the first column of the first row; use ExecuteScalar[^] instead. And you need to add a params parameter[^] to your method to pass parameters:

private static void PrepareCommand(SqlCommand command, string commandText, object[] parameters)
{
if (parameters != null && parameters.Length != 0)
{
string[] parameterNames = new string[parameters.Length];
for (int index = 0; index < parameters.Length; index++)
{
string name = "@p" + index;
parameterNames[index] = name;
command.Parameters.AddWithValue(name, parameters[index]);
}

    commandText = string.Format(commandText, parameterNames);
}

command.CommandText = commandText;

}

public string get_data(string commandText, params object[] parameters)
{
using (var connection = new SqlConnection(ConnectionString))
using (var command = new SqlCommand(string.Empty, connection))
{
PrepareCommand(command, commandText, parameters);

    connection.Open();
    object result = command.ExecuteScalar();
    return Convert.ToString(result);
}

}

You can then pass parameters to the command using the auto-generated names:

cfs.get_data("select top 1 AVERAGE_COST from PRODUCT where PRODUCT_NO = @p0", strpcode)

or by position:

cfs.get_data("select top 1 AVERAGE_COST from PRODUCT where PRODUCT_NO = {0}", strpcode)

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

[Lost User]

Where's the version number? I don't see any evidence of you "trying all the points".

"(I) am amazed to see myself here rather than there ... now rather than then". ― Blaise Pascal

[palikhelsanjeeb]

Hi Tarun, may b helpful to use the required isolation levels while locking the transactions. may b u could watch the video tutorial in mva site course:

Developing Microsoft SQL Server Databases

chapter 4: managing transaction if you prefer watching rather than reading link: Developing Microsoft SQL Server Databases – Microsoft Virtual Academy[^]

[TarunKumarSusarapu]

Thank you Mr.palikhelsanjeeb.I will go through this link and let you know the result if it is useful. :)