|
Open new connections as and when you need them, if you have a single connection you can only run a single command at a time and as you have found if that command is a data reader it can be running for some time.
You could of course queue up all the requests but that would slow everything down, your threads would all be waiting in line to get their data.
There is one other option which is MARS(Multiple Active Result Sets), this was added in MSSQL2k5 but it does have some limitaitons.
MARS[^]
|
|
|
|
|
The thread who is pooling the database does this every 0,5 second. I could make a separate SQLConnection which only will be used by this thread.
On the other site there is the Socket connection. I have not a very good indepth knowledge on how this works, with AsyncCallBacks etc... Are these callbacks (when data is received) handled by the same Thread. Or does each of them has its own thread. When they share the same thread I would probably have only 2 different sql connections: 1 for the Thread who pools the database and the second one for the socket connection, data callbacks.
When each callback is handled by an other (new) Thread, I probably need a new connection for each callback.
Could you let me know if this is the right way? Or am I missing something?
Thanks in advance!
Michiel
|
|
|
|
|
Michiel wrote: The thread who is pooling the database
You mean polling? Pooling is something quite different.
As I said in the first post I personally go for creating a connection when I need one and disposing it as soon as I'm done with it. This isn't appropriate for databases whic have limitaitons on the number of connections but with MSSQL Express this shouldn't be a problem.
|
|
|
|
|
let me give you some example code:
while(true)
{
}
This is in a short way what the thread does. Just quering/pooling the database for changes.
|
|
|
|
|
If I remember correctly, the current SQL Native Client (preferred when talking to SQL Server 2005) doesn't support connection pooling. This means you get a performance hit if you solve your problems by creating, opening and closing a separate connection each time you touch the database (each time you will cause negotiations between the client and SQL Server before you start doing actual work). If connection pooling was supported, this wouldn't be the case, meaning it would be the simplest solution and wouldn't be a performance issue (connection pooling makes it extremely fast to open/close new connections as a number of "closed" connections are kept open in the background, ready to use by the next attempt to open a new connection).
As is, it isn't always workable (depending on how often you touch the database, of course - if it's just once an hour you should of course connect and close each time).
My solution when doing what you're doing (I have a couple of apps that have similar structure) is as follows:
- One thread for listening to the TCP socket and accessing the database accordingly: Has its own SQL Server connection, opened when the thread starts and kept as long as it runs.
- One thread (the main UI thread, usually) that reacts to user interaction and accesses the database accordingly: Has its own SQL Server connection, opened when the application starts and kept as long as it runs.
- One thread that loops with a Thread.Sleep with some appropriate interval and checks the database for stuff to do: Has its own SQL Server connection, opened when the thread starts and kept as long as it runs. NOTE: If the checks and actions aren't time-consuming you could do them from a Timer object in the UI, using the connection of the UI thread (as the Windows Timer object lives on the UI thread and won't fire when some other UI event handler is running).
If your application is meant to stay running for long times and don't need to touch the database frequently, you could wrap your database code in a class that opens the actual connection on demand and closes it after some time of inactivity (purpose: minimize the number of open connections to the SQL Server). But if I understand your scenario correctly (reading between the lines), this isn't the case.
Finally, a short warning: Beware of classes that retain a reference to the connection and use it unexpectedly. You need to make sure that each distinct action on the database doesn't leave any SqlCommand, SqlDataReader or similar objects laying around (using blocks are Good Things). For instance, don't create a reader in the TCP socket listener thread and store it for use by the UI thread - you're bound to run into threading issues when the UI thread starts reading from it (that particular example is actually even worse than that - the reader will block the connection until it is closed, unless you have MARS enabled). Always consume your data (read it into variables, a disconnected DataTable or whatever) immediately. If you can't due to large volumes of data, you have to create a brand new connection for each such action.
Note: Reading your error messages, I think you may have the SqlDataReader issue. As long as one of them is open and alive, you can't do anything else on the same connection (not even on the same thread). Unless (which has already been said by others) you enable multiple result sets (MARS) - but that won't solve any other threading issues.
Was this helpful?
--
Peter
|
|
|
|
|
PeterTheSwede wrote: If I remember correctly, the current SQL Native Client (preferred when talking to SQL Server 2005) doesn't support connection pooling. This means you get a performance hit if you solve your problems by creating, opening and closing a separate connection each time you touch the database (each time you will cause negotiations between the client and SQL Server before you start doing actual work). If connection pooling was supported, this wouldn't be the case, meaning it would be the simplest solution and wouldn't be a performance issue (connection pooling makes it extremely fast to open/close new connections as a number of "closed" connections are kept open in the background, ready to use by the next attempt to open a new connection).
If you have MDAC installed it supports Connection Pooling just fine, which is why you always include MDAC along with the Native Client installer Heh it'd be extreamly painful making larger systems if it wasn't supported :/
|
|
|
|
|
Hmmm... I was under the impression that this was the case only under IIS. But a few minutes on Google I'm now convinced you are right! How did I miss that? Thanks for the enlightenment!
--
Peter
|
|
|
|
|
No probs ... I had pretty much the opposite! I didn't realise there was circumstances under which Connection Pooling wouldn't be avalible, but after a few minutes on google double checking I found you were right
You learn something new everyday
|
|
|
|
|
Hi Peter,
Sorry for this late reply, I was out of the office because I was ill.
Just for my understanding:
I need three different (copies) of Sql Server connections
SqlConnection sqlConn1, sqlConn2, sqlConn3;
SqlDataReader sqlReader1, sqlReader2, slqReader3;
SqlCommand sqlComm1, sqlComm2, sqlComm3;
The Thread that loops will start as soon as the program runns and won't end till the programm stops. This thread will loop with an interval less then a second.
How about the part of 'there can only be one datareader active at the same time'? If I do understand you right there can only be one datareader active at the same time, also if this datareader is on a different thread. How about when using different (for each tread one) sqlconnections and datareaders?
The program I am building now is quite small and 'easy', but this database 'management' makes it a bit more complicated for me.
Hope you can help me out on this.
Thanks again!
Michiel
modified on Wednesday, May 21, 2008 7:10 AM
|
|
|
|
|
Hi,
(edited: added null checking of parms in GetCommand, added comment on why I don't let GetCommand create the connection)
(additional edit: somehow I forgot to set CommandText on the command...)
Can't say whether or not your code will work - that depends on what you do with the stuff. The thing to remember is that one single SQL connection will only handle ONE "firehose cursor" (such as a datareader) at any one time - so if you need parallell queries running simultaneously, they must be on separate connections.
Based on the discussion between me and originSH regarding connection pooling, I'd suggest you work as follows:
Common infrastructure
private string myConnectionString;
private SqlConnection GetConnection()
{
SqlConnection cn = new SqlConnection(myConnectionString);
try
{
cn.Open();
return cn;
}
catch
{
cn.Dispose();
throw;
}
}
private SqlCommand GetCommand(SqlConnection cn, string sqlquery, params object[] parms)
{
SqlCommand cmd = cn.CreateCommand();
try
{
cmd.CommandText = sqlquery;
if (parms != null)
{
for (int i = 0; i < parms.Length; i++)
{
cmd.Parameters.AddWithValue(
String.Format("@p{0}", i), parms[i] ?? DbNull.Value);
}
}
return cmd;
}
catch
{
cmd.Dispose();
throw;
}
}
Each time you read something from the database:
(each time you loop in your watchdog thread, or when UI needs to read, or whenever)
using (SqlConnection cn = GetConnection())
{
string query = "SELECT * FROM MyTable WHERE x = @p0 AND y = @p1";
using (SqlCommand cmd = GetCommand(cn, query, 1, 2))
{
using (SqlDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
DoSomethingWithTheseFields(rdr["field1"], rdr["field2"]);
}
}
}
}
When you need to do updates or fetch a scalar you just do that instead of getting a reader, using whatever methods you need on the SqlCommand object.
As per our discussion regarding connection pooling, the Sql client (which actually is NOT the "SQL Native Client" - that is only used for backwards ADO compatibility - you will be using the "Managed Provider" instead) will do connection pooling. As long as you don't forget to Dispose() the connections after use (which I do with the using keyword), it will take zero (in practice) time to get new connections when you need them. It looks like it would be slow but it won't (the provider does a quick lookup based on connection string and user credentials, and returns the first match - it is almost instantaneous).
This way, each connection is only used for one specific task and is very short-lived. No threading issues anywhere. You may run into locking issues, but that is another story altogether, and belongs in the SQL Server forum...
Added comment: A tempting optimization might be to let GetCommand create the connection. Don't. The connection won't be automatically disposed when the command is, so it won't be returned to the pool until the GC collects the SqlConnection object. In larger systems I solve this by creating (disposable) wrapper classes around the useful objects, ensuring that the entire hierarchy gets torn down (disposed) appropriately. Also, creating connections in exactly one place enables you to configure them (connection timeout or whatever) in exactly one place.
Hope this helped!
NOTE: Code written from memory but concept tested. Aside from spelling mistakes, it will work.
--
Peter
|
|
|
|
|
This is almost what I am trying to do now... and it seems to work.
With locking you mean database locking? Because of the posibility of to much open connections? This is indead an other story...
How about closing the connections? Need this to be done after finishing using the current connection or does the (automatic)disposing take care of this?
Thank you very much for helping me, I have learned a lot!
|
|
|
|
|
You're welcome!
On your followups:
- Yes, I mean database locks. Not related to too many connections but related to the possibility of conflicting reads and updates of the same rows. However - this is a database design and access issue and that is another forum...
- Yes, the connections will be closed by the Dispose() calls done by the using statements, unless you call Close() yourself. As per the idea of the disposable pattern: all held unmanaged resources (of which a database connection is an excellent example) are released.
Also note that I edited the reply - I added a null check for the parameter array and a comment on why I always create connections as a separate call (instead of doing it from the GetCommand method).
Later,
--
Peter
|
|
|
|
|
One final thought: If all your database actions are completed quickly (enough for what you're doing), you could just synchronize them (using a lock on something) and use a single database connection instead. If everything sits on a single CPU (you mentioned the Express edition of SQL Server, usually used like that), that should be the opimal solution. Having different things happen simultaneously in the database means additional overhead (thread switching, locking etc), and in that scenario this would just cause unnecessary performance degradation.
--
Peter
|
|
|
|
|
Why do I hit "reply" before I think? Sorry!
I reread your question and realized that a) you're talking to the socket from all three threads (counting 3, the UI, as one), b) you're talking to the database from two of them (1 and 2, using your numbering). I have an app that does exactly that in production (it controls a bunch of stacker cranes and keeps track of what's stored where). It works well and looks like this:
1. The socket communication is handled by an object that queues inbound and outbound messages. It has a worker thread and uses locking to ensure that any access to its queues is thread-safe (I don't expose my private Queue<message> objects).
2. There is a supervisor object with a watchdog thread that monitors database updates (from other sources) and the inbound queue of the communications handler, and responds accordingly. It also has methods used by the UI to enqueue commands to the socket (the socket itself and the communications handler aren't exposed outside of the supervisor class). The watchdog thread and the UI use locks to ensure that they don't mess with any queues simultaneously.
3. The UI calls methods on the supervisor object when needed. Out of lazyness I don't use events as I don't want to mess with Invoke() in this case. Any status displays and so on can easily be handled by UI timers.
The point here is that only the supervisor object (and specifically, its watchdog thread) ever touches the database. It has its own connection which isn't exposed, so if I ever get the idea to let the UI touch the database, it has to use a separate connection (no threading or other issues).
Sorry for the previous somewhat redundant answers. I think I need some sleep now (2:27 AM here)...
--
Peter
|
|
|
|
|
Can you let me know any articles about DrawEllipse and FillEllipse methods, which can give clear about the concept.
|
|
|
|
|
What was wrong with the MSDN ones that you got with your google search ?
Christian Graus
Please read this if you don't understand the answer I've given you
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
|
Hint: have a look at method names.
If the Lord God Almighty had consulted me before embarking upon the Creation, I would have recommended something simpler.
-- Alfonso the Wise, 13th Century King of Castile.
This is going on my arrogant assumptions. You may have a superb reason why I'm completely wrong.
-- Iain Clarke
|
|
|
|
|
I have a problem regarding the internal Protected.
I have a solution project that contains two projects.
1.Window application
2.class library.
namespace ClassLibrary1<br />
{<br />
public class Class1<br />
{<br />
internal protected int i = 0;<br />
}<br />
}
Now the following is the window application.I created a class file.There is am trying to access the proetected internal member of class library project.and it is accessibe there.
using ClassLibrary1;<br />
namespace WindowsApplication1<br />
{<br />
class Class2:Class1<br />
{<br />
public void bb()<br />
{<br />
<a href="http://zifiglio.blogspot.com/2007/03/c-accessibility-level-protected.html">http:
}<br />
}<br />
}
If you have an apple & I have an apple and we exchange our apples, then each of us will still have only one apple but if you have an idea & I have an idea and we exchange our ideas, then each of us will have two ideas!
|
|
|
|
|
You are deriving Class2 from Class1, that's why you are able to access the member.
protected internal does not mean protected and internal ; it means protected *or* internal .
To clear that up, a protected internal member will be visible to all other classes in the same assembly and derived classes everywhere - not just derived classes in the same assembly.
Cheers,
Vikram.
The hands that help are holier than the lips that pray.
|
|
|
|
|
Hello everyone,
Suppose Status is an int variable, which could be read and write by multiple threads.
When assign the value (write) of Status variable, I always use,
Interlocked.Exchange(ref Status, SomeValue);
My question is, in order to make write/read of this variable thread safe, could I access (read) the variable directly by accessing Status variable, for example
Console.write (Status);
if (Status > 0)
{
}
thanks in advance,
George
|
|
|
|
|
Reading and writing of an int are atomic operations, so the operations themselves are thread safe.
However, you may get unexpected results because the compiler can optimise the code because it relies on that a variable is only changed by the current thread, as you are supposed to use the volatile keyword if a variable needs special attention because it can be changed in multiple threads.
When you compile the code in your example, you might expect to get something like this (shown in pseudo code):
get Status into reg1
push reg1
call Console.Write(int)
get Status into reg1
compare reg1, 0
if lte jump .label
.label
Instead the compiler may optimise this code into:
get Status into reg1
push reg1
call Console.Write(int)
compare reg1, 0
if lte jump .label
.label
As you see, the code now doesn't get the value from the variable the second time, so it can enter the if statement even if the actual value of the variable has already changed.
Despite everything, the person most likely to be fooling you next is yourself.
|
|
|
|
|
Thanks Guffa,
1.
I doubt -- "Reading and writing of an int are atomic operations". Because if it is true, the system API Interlocked.Exchange overload for Int32 is useless. Any comments?
2.
It is great if you could provide some references to support the points -- "Reading and writing of an int are atomic operations". I am interested to learn new things.
regards,
George
|
|
|
|
|
George_George wrote: I doubt -- "Reading and writing of an int are atomic operations". Because if it is true, the system API Interlocked.Exchange overload for Int32 is useless. Any comments?
Either you don't know that the Interlocked.Exchange method does, or you misunderstood what I wrote. The documentation for the Interlocked.Exchange method says:
"Sets a 32-bit signed integer to a specified value and returns the original value, as an atomic operation."
So, it does two operations as an atomic operation. I wrote that reading and writing of an int are atomic operations, I didn't write that subsequent reading and writing of an int becomes a single atomic operation.
George_George wrote: It is great if you could provide some references to support the points -- "Reading and writing of an int are atomic operations".
"12.5 Atomicity of variable references
Reads and writes of the following data types shall be atomic: bool , char , byte , sbyte , short , ushort ,
uint , int , float , and reference types. In addition, reads and writes of enum types with an underlying type
in the previous list shall also be atomic. Reads and writes of other types, including long , ulong , double ,
and decimal , as well as user-defined types, need not be atomic. Aside from the library functions designed
for that purpose, there is no guarantee of atomic read-modify-write, such as in the case of increment or
decrement."
ECMA-334[^]
Despite everything, the person most likely to be fooling you next is yourself.
|
|
|
|
|
Thanks Guffa,
Your reply is great!
"reference types" -- means reference types of bool, char, byte, sbyte, short, ushort, uint, int, float (e.g. ref bool) or all reference types (including reference to a class)?
regards,
George
|
|
|
|
|