Edit:
My problem is not a problem anymore: I have redo my performances tests and I have do a fatal stupid error: I had forget a x1000 to get seconds from milliseconds :/
Sorry for that guys.
For info:
- I do some 1900 updates per second from my PC to the DataBase server on local network.
- 3.200 updates per second if the programs is on same machine than DB.
- 3.500 updates per second from my PC on the DataBase server I do not re-create and re-open a new SQLConnection.
- 5.800 updates per second with a batch text.
For my 10.000 rows, if it take 5 seconds, it is ok for my programs. Sorry to have worry you.
Actually, I use a SQL stored prodedure to create a row in my database to avoid SQL-injection. In C# I have the following method:
public void InsertUser(string userCode)
{
using (SqlConnection sqlConnection = new SqlConnection(this.connectionString))
{
SqlCommand sqlCommand = new SqlCommand("InsertUser", sqlConnection);
sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
sqlCommand.Parameters.Add(new SqlParameter("@UserCode", userCode));
sqlConnection.Open();
sqlCommand.ExecuteNonQuery();///0.2 seconds !ERROR HERE! 0.2ms here,NOT 0.2sec!!!
}
}
It woks great when i have one or two rows to insert. But if i need to create 1.000 users and 10.000 products and 5000 pets, it is not the best solution: I will loose a huge time in netwok transport.
I believe, without checkin it, that I can use just a limited amount of callback. So I do not want to call 10.000 times:
sqlCommand.BeginExecuteNonQuery()
Another way will be to create a batch text, but there is a SQL-Injection risk (and it is ugly).
Does there is a 'SqlCommandList' object that manage that in .Net? How do I do large writing in database? What the good patern for that?