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

[Simon_Whale]

it requires more reading from yourself but have a read of this All About TransactionScope[^]

Every day, thousands of innocent plants are killed by vegetarians. Help end the violence EAT BACON

[TarunKumarSusarapu]

Thank you Simon for your document shared.I will go through it and let you know the result.

[Afzaal Ahmad Zeeshan]

That happens. Well, for further explanations kindly see the links I provided. They have a good resource start up that you can use for your application.

The shit I complain about It's like there ain't a cloud in the sky and it's raining out - Eminem ~! Firewall !~

[Richard Deeming]

It looks like you're part-way through rewriting your code to avoid SQL Injection. :thumbsup: As a result, you can remove all of the code that's building up the sdata string, since it's no longer used. The lines which call cfs.get_data are still vulnerable, and need to be rewritten to use parameters. You need to remove the calls to cfs.singlequotconver. Based on the name, it's trying to avoid SQL Injection by "escaping" quote characters. Since you're now using parameters, you don't need to do that. You should also remove the call to cfs.sqldateconverion, which is probably converting the date to a string. Dates should be passed and stored as dates.

---

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

[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. :)