9

I've noticed that my code errors out on sqlWrite.ExecuteNonQuery(); after executing 200 Insert queries in couple of seconds. I always thought that using will make sure the resources are reused properly and there will be no need to do anything. This is the first time I get this error and I've been dealing with sql/c# for almost 3 years doing different things.

using (SqlConnection varConnection = Locale.sqlConnectOneTime(Locale.sqlDataConnectionDetails)) 
{
    using (var sqlWrite = new SqlCommand(preparedCommand, varConnection)) 
    {
        sqlWrite.Parameters.AddWithValue("@var_agr_fname", var_agr_fname == "" ? (object) DBNull.Value : var_agr_fname);
        sqlWrite.ExecuteNonQuery();
    }
}


public static SqlConnection sqlConnectOneTime(string varSqlConnectionDetails)
{
    var sqlConnection = new SqlConnection(varSqlConnectionDetails);
    try
    {
        sqlConnection.Open();
    }
    catch
    {
        DialogResult result = MessageBox.Show(new Form {TopMost = true},
                                              "Błąd połączenia z bazą danych. Czy chcesz spróbować nawiązac połączenie ponownie?",
                                              "Błąd połączenia (000001)",
                                              MessageBoxButtons.YesNo,
                                              MessageBoxIcon.Stop);
        if (result == DialogResult.No)
        {
            if (Application.MessageLoop)
            {
                Application.Exit(); // Use this since we are a WinForms app
            }
            else
            {
                Environment.Exit(1); // Use this since we are a console app
            }
        }
        else
        {
            sqlConnection = sqlConnectOneTime(varSqlConnectionDetails);
        }
    }
    return sqlConnection;
}

Error message: A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)

Considering advice for this error I should be using SqlConnection.ClearAllPools(); to make sure connections are reset or discarded properly. So I can use it but the question is where to use it and when? How to know if the limit is going to break? Where's the limit? at 50 / 150 / 200 ? or should I use it every single time in a loop?

Community
  • 1
  • 1
MadBoy
  • 10,824
  • 24
  • 95
  • 156
  • 3
    Why not spin up only one `SqlConnection` and use it exclusively? It does appear you are using any threads so this should work without any *hacky* `SqlConnection.ClearAllPools()`. – Erik Philips Apr 04 '12 at 21:54
  • I have a method that I reuse by using it either once or more times. It does it all from establishing a connection (or using the one that's already connected) to returning the connection to the pool. Those are advices I got. This way I always reuse the connection if it's open and if it's closed I always open it up. Never had a problem with it till now. I thought/was told that connection pool will take care of it for me when I use `using` :-) Seems not :-) – MadBoy Apr 04 '12 at 21:57
  • 2
    @Madboy: This sounds like you have reinvented the Connection-Pool. It always reuses connections if they are closed and cannot reuse them if they are open. Have you also seen the links in the other questions answer(not the accepted)? http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/9609559d-f7ce-4bd8-97d0-0003ff7c9c98/ and http://blogs.msdn.com/b/spike/archive/2009/04/16/a-transport-level-error-has-occurred-when-sending-the-request-to-the-server-provider-tcp-provider-error-0-an-existing-connection-was-forcibly-closed-by-the-remote-host.aspx – Tim Schmelter Apr 04 '12 at 21:59
  • @AustinSalonen You're right... ugh. – Erik Philips Apr 04 '12 at 22:11
  • @ErikPhilips http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx it's reused when used inside `using` and conditions apply – MadBoy Apr 04 '12 at 22:23

3 Answers3

5

First, let me say that this code is horrible. You're mixing UI with data connection creation. What's more, you show a dialog window inside a catch section and do a recursive call! This is very messy and in itself can lead to errors and unpredictable behaviour. And (original) formatting makes it hard to read. Sorry for the harsh comment but you really should redesign this code.

Apart from that your code should work fine but if you're getting No process is on the other end of the pipe. error that means there is something wrong with your database and/or SQL Server. It looks like it gets clogged up and just does not accept any more connections. If you run batch of inserts in a short time do them on one connection if possible. ClearAllPools is a way to recover when something wrong happens and it would be best to find out what it is instead of covering that up. It's like taking paracetamol when your tooth hurts and never going to a dentist.

One other thing is that using multiple SqlConnections create separate transaction for each connection. This adds load on SQL Server although it can surely do more than hundreds of transactions per sec.

Also, you can change transport to named pipe and TCP to see if it changes anything.

Maciej
  • 7,871
  • 1
  • 31
  • 36
  • It's generally happening on 3 different computers so it's not server related or at least not something I can see. ON Microsoft page http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx#Y1790 they suggest that if the connection string is the same the connection will stay open for next connections to come. So if I'm doing them all in one foreach loop it's probably one connection only. Of course the method is a bit short example since there's about 20 parameters instead of just 1. But that hasn't affected it earlier. And the problem starts at 180 insert. – MadBoy Apr 04 '12 at 22:46
  • It is true that real DB connection will stay open and be reused but creating SqlConnection hundreds of times per few seconds and then leaving it for garbage collector is an unnecessary strain on resources (GC mainly) – Maciej Apr 04 '12 at 22:51
  • Do some testing - introduce a delay between Inserts and see what happens. Also see if it changes to make all Inserts on one connection. – Maciej Apr 04 '12 at 22:56
  • I did try with 50ms sleep no go. Also I checked and there are only 3 connections made to the database (1-2 made in SQL Management Studio and 1 during the loop. So the connection is reused properly (it seems). – MadBoy Apr 04 '12 at 23:04
  • Another idea: change transport to named pipe and tcp to check if it helps – Maciej Apr 04 '12 at 23:18
  • `A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)` when changed to TCP only. – MadBoy Apr 04 '12 at 23:27
  • This may suggest network problems or SQL Server connection limit being reached. What version of SQL Server you're using? – Maciej Apr 04 '12 at 23:50
  • SQL Server 2008 R2 Developer Edition. But it also happens on R2 Sql Express and developer Edition on another computer. – MadBoy Apr 05 '12 at 06:34
  • WE have pinpointed the issue to certain records in Excel table we're using to insert into SQL. It always bumps out on certain records. If we use good records (like multiplied first 50 good records 10 times) it works fine.. it's very strange.. we're trying to find what's different in those records that causes this. – MadBoy Apr 05 '12 at 08:15
  • The issue is related to DateTime. There was a mistake in Excel where the date was 1200 year or something and SQL Min Value is set to 1900 year. Go figure why it was throwing out this weird error instead telling us the true reason :-) – MadBoy Apr 05 '12 at 08:20
4

"I have a method that I reuse by using it either once or more times. It does it all from establishing a connection (or using the one that's already connected) to returning the connection to the pool. Those are advices I got. This way I always reuse the connection if it's open and if it's closed I always open it up."

This sounds like you have reinvented the Connection-Pool. It always reuses connections if they are closed and cannot reuse them if they are open.

So close the connection in the catch block:

public static SqlConnection sqlConnectOneTime(string varSqlConnectionDetails) {
    var sqlConnection = new SqlConnection(varSqlConnectionDetails);
    try {
        sqlConnection.Open();
    } catch {
        //log and
        sqlConnection.Close();
        throw
    }
    return sqlConnection;
}

Edit: To be honest i wouldn't use such factory methods at all. They are just a source for unreproducable errors. What's so time consuming in creating and opening the connection where you're using it?

using(SqlConnection varConnection = new SqlConnection(Locale.sqlDataConnectionDetails)) {
    using (var sqlWrite = new SqlCommand(preparedCommand, varConnection)) {
        sqlWrite.Parameters.AddWithValue("@varSecus_agr_fname", varSecus_agr_fname == "" ? (object) DBNull.Value : varSecus_agr_fname);
        varConnection.Open();
        sqlWrite.ExecuteNonQuery();
    }
}

The first two links are from your linked question(not the accepted answer), they might also be helpful:

Community
  • 1
  • 1
Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
  • Wouldn't finally close the connection if the try is successful anyway? Returning me closed connection string anyways? Basically what your code does is open connection and close it in a second. While what my code was supposed to do is open connection so that it can be used and when done `using` is supposed to clean it up/return to the pool. – MadBoy Apr 04 '12 at 22:09
  • @Madboy: You're right, corrected. To be honest i wouldn't use such factory methods at all. They are just a source for unreproducable errors. What's so time consuming in creating and opening the connection where you're using it? – Tim Schmelter Apr 04 '12 at 22:12
  • Well I can look for it (it's somewhere in my profile questions) when I was searching for correct use of Open/Close connections it was suggested (and upvoted) that If I open Connection and not explicitly close it it will be returned to pool by `using` and this `open` connection in pool can be still reused by next queries without need to establish connection (hence being faster). If I explicitly tell it to Open and then always Close it, the next query will take as long as the first one. – MadBoy Apr 04 '12 at 22:15
  • http://stackoverflow.com/questions/2230062/usage-of-nested-using-in-c-sharp-and-sql-server where it says that .Close is called by .Dispose and Dispose is natural when using `using` and also my other question http://stackoverflow.com/questions/2150580/what-is-best-aproach-to-get-sql-data-from-c-sharp – MadBoy Apr 04 '12 at 22:18
  • I do understand that I could use new SqlConnection (as proposed) inside the Insert but having it in external method gives me ability to "reconnect" if it fails (warn user and give him "Reconnect Yes/No option" and it also gives me option to add some error logic. Otherwise I will have to duplicate lots of code. Also wouldn't your new SqlConnection inside the using method be exactly as my method does just in another method? After all, all I'm doing is calling another method inside using which should Dispose the connection anyways. – MadBoy Apr 04 '12 at 22:21
  • Also please check http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx which explicitly says using is reusing. And just to say I did as your last example says (defined new SqlConnection inside using and used varConnection.Open(); directly and still it fails with same error. – MadBoy Apr 04 '12 at 22:27
  • @MadBoy: I cannot tell you what exactly is going wrong. But as i've said i wouldn't fight symptoms but the cause. Maybe it is because you're opening dialogs in a static method which causes locks in a mutithreaded environment. – Tim Schmelter Apr 04 '12 at 22:28
  • This is all done in one thread. A foreach loop going thru Excel table and picking 30 columns and entering them into sql. No multithreading at this point at all. Even gui is freezed for 5 seconds. – MadBoy Apr 04 '12 at 22:30
  • @Madboy: Maybe it is already too late and you first need to clear the pools, restart SQL-Server or whatever so that my solution does not help longer. Yes, `using` does reuse connections since they are closed implicitely. But your method does not close the connection in case of an exception. – Tim Schmelter Apr 04 '12 at 22:31
  • That's actually not a problem. The errors doesn't happen on "connection". It happens `sqlWrite.ExecuteNonQuery();` so the method you're focusing on is not really to blame. And since connection should be reused (considering the database connectiong string is the same) there should be actually only 1 connection used for all this time. – MadBoy Apr 04 '12 at 22:38
  • Connection-errors always occur when you're using them(f.e. DataAdapter.Fill,SqlCommand.ExecuteReader or SqlCommand.ExecuteNonQuery). – Tim Schmelter Apr 04 '12 at 22:40
  • I checked and there are only 3 connections made to the database (1-2 made in SQL Management Studio and 1 during the loop). So the connection is reused properly (it seems) and there's no 180 connections made during the insert call. – MadBoy Apr 04 '12 at 23:04
  • Yes 4.0. I'll download it and see. But my computer is up to date / so is the server unless this is outside of the normal updates scope. – MadBoy Apr 04 '12 at 23:16
  • Didn't change anything. Applied, rebooted no go. – MadBoy Apr 04 '12 at 23:24
  • @MadBoy: Are you connecting to localhost? Have you seen the [first link](http://tinyurl.com/bqgtqex) above? "_For some reason the connection was made through Named Pipes instead of Shared Memory (the default for local connections), which suggests that SqlClient did not recognize the connection was local and most likely tunneled Named Pipes over TCP hence disconnecting the network has impact. How do you specify the server name - perhaps by its fully qualified domain name or IP address? If you specify it by the hostname or "." or "(local)" SqlClient should recognize the local connection._" – Tim Schmelter Apr 04 '12 at 23:34
  • WE have pinpointed the issue to certain records in Excel table we're using to insert into SQL. It always bumps out on certain records. If we use good records (like multiplied first 50 good records 10 times) it works fine.. it's very strange.. we're trying to find what's different in those records that causes this. – MadBoy Apr 05 '12 at 08:15
  • The issue is related to DateTime. There was a mistake in Excel where the date was 1200 year or something and SQL Min Value is set to 1900 year. Go figure why it was throwing out this weird error instead telling us the true reason :-) – MadBoy Apr 05 '12 at 08:22
0

Those 2 errors:

A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)

Were related to DateTime value being inserted into SQL with Date before 1900 year. The Microsoft rule here is .. don't store DateTime value less then 1900 year in DateTime value in SQL. Use string instead...

MadBoy
  • 10,824
  • 24
  • 95
  • 156