Mantis#1736. Thank you kindly, StrawberryFride for a patch that:

Many issues with handling connections in MSSQL, have rearchitected to ensure that connections are always opened and closed in a timely fashion & disposed of cleanly, and removed unnecessary lock statements. SQL Server performance seems to have improved considerably as a result, and various timeout errors seem to have been fixed.
author: Charles Krinke 2008-07-17 13:43:31 +0000
committer: Charles Krinke 2008-07-17 13:43:31 +0000
commit: e8412dcd42f6478f70f587d534a92a948cd6dd93 (patch)
tree: e3f2d084da7f40bd453bf06fefa43241facc0f86 /OpenSim/Data/MSSQL/AutoClosingSqlCommand.cs
parent: morphing OSHttpHandler interface into an abstract base class. adding (diff)
download: opensim-SC-e8412dcd42f6478f70f587d534a92a948cd6dd93.zip
opensim-SC-e8412dcd42f6478f70f587d534a92a948cd6dd93.tar.gz
opensim-SC-e8412dcd42f6478f70f587d534a92a948cd6dd93.tar.bz2
opensim-SC-e8412dcd42f6478f70f587d534a92a948cd6dd93.tar.xz
1 files changed, 216 insertions, 0 deletions
diff --git a/OpenSim/Data/MSSQL/AutoClosingSqlCommand.cs b/OpenSim/Data/MSSQL/AutoClosingSqlCommand.cs
new file mode 100644
index 0000000..6a7d787
--- /dev/null
+++ b/OpenSim/Data/MSSQL/AutoClosingSqlCommand.cs
@@ -0,0 +1,216 @@
+/*
+ * Copyright (c) Contributors, http://opensimulator.org/
+ * See CONTRIBUTORS.TXT for a full list of copyright holders.
+ *
+ * Redistribution and use in source and binary forms, with or without
+ * modification, are permitted provided that the following conditions are met:
+ *     * Redistributions of source code must retain the above copyright
+ *       notice, this list of conditions and the following disclaimer.
+ *     * Redistributions in binary form must reproduce the above copyright
+ *       notice, this list of conditions and the following disclaimer in the
+ *       documentation and/or other materials provided with the distribution.
+ *     * Neither the name of the OpenSim Project nor the
+ *       names of its contributors may be used to endorse or promote products
+ *       derived from this software without specific prior written permission.
+ *
+ * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY
+ * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
+ * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
+ * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY
+ * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
+ * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
+ * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
+ * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
+ * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
+ * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
+ */
+using System;
+using System.Collections.Generic;
+using System.Text;
+using System.Data.SqlClient;
+using System.Data;
+namespace OpenSim.Data.MSSQL
+{
+    /// <summary>
+    /// Encapsulates a SqlCommand object but ensures that when it is disposed, its connection is closed and disposed also.
+    /// </summary>
+    internal class AutoClosingSqlCommand : IDbCommand
+    {
+        private SqlCommand realCommand;
+        public AutoClosingSqlCommand(SqlCommand cmd)
+        {
+            realCommand = cmd;
+        }
+        #region IDbCommand Members
+        public void Cancel()
+        {
+            realCommand.Cancel();
+        }
+        public string CommandText
+        {
+            get
+            {
+                return realCommand.CommandText;
+            }
+            set
+            {
+                realCommand.CommandText = value;
+            }
+        }
+        public int CommandTimeout
+        {
+            get
+            {
+                return realCommand.CommandTimeout;
+            }
+            set
+            {
+                realCommand.CommandTimeout = value;
+            }
+        }
+        public CommandType CommandType
+        {
+            get
+            {
+                return realCommand.CommandType;
+            }
+            set
+            {
+                realCommand.CommandType = value;
+            }
+        }
+        IDbConnection IDbCommand.Connection
+        {
+            get
+            {
+                return realCommand.Connection;
+            }
+            set
+            {
+                realCommand.Connection = (SqlConnection) value;
+            }
+        }
+        public SqlConnection Connection
+        {
+            get
+            {
+                return realCommand.Connection;
+            }
+        }
+        IDbDataParameter IDbCommand.CreateParameter()
+        {
+            return realCommand.CreateParameter();
+        }
+        public SqlParameter CreateParameter()
+        {
+            return realCommand.CreateParameter();
+        }
+        public int ExecuteNonQuery()
+        {
+            return realCommand.ExecuteNonQuery();
+        }
+        IDataReader IDbCommand.ExecuteReader(CommandBehavior behavior)
+        {
+            return realCommand.ExecuteReader(behavior);
+        }
+        public SqlDataReader ExecuteReader(CommandBehavior behavior)
+        {
+            return realCommand.ExecuteReader(behavior);
+        }
+        IDataReader IDbCommand.ExecuteReader()
+        {
+            return realCommand.ExecuteReader();
+        }
+        public SqlDataReader ExecuteReader()
+        {
+            return realCommand.ExecuteReader();
+        }
+        public object ExecuteScalar()
+        {
+            return realCommand.ExecuteScalar();
+        }
+        IDataParameterCollection IDbCommand.Parameters
+        {
+            get { return realCommand.Parameters; }
+        }
+        public SqlParameterCollection Parameters
+        {
+            get { return realCommand.Parameters; }
+        }
+        public void Prepare()
+        {
+            realCommand.Prepare();
+        }
+        IDbTransaction IDbCommand.Transaction
+        {
+            get
+            {
+                return realCommand.Transaction;
+            }
+            set
+            {
+                realCommand.Transaction = (SqlTransaction) value;
+            }
+        }
+        UpdateRowSource IDbCommand.UpdatedRowSource
+        {
+            get
+            {
+                return realCommand.UpdatedRowSource;
+            }
+            set
+            {
+                realCommand.UpdatedRowSource = value;
+            }
+        }
+        #endregion
+        #region IDisposable Members
+        public void Dispose()
+        {
+            SqlConnection conn = realCommand.Connection;
+            try
+            {
+                realCommand.Dispose();
+            }
+            finally
+            {
+                try
+                {
+                    conn.Close();
+                }
+                finally
+                {
+                    conn.Dispose();
+                }
+            }
+        }
+        #endregion
+    }
+}
author	Charles Krinke	2008-07-17 13:43:31 +0000
committer	Charles Krinke	2008-07-17 13:43:31 +0000
commit	e8412dcd42f6478f70f587d534a92a948cd6dd93 (patch)
tree	e3f2d084da7f40bd453bf06fefa43241facc0f86 /OpenSim/Data/MSSQL/AutoClosingSqlCommand.cs
parent	morphing OSHttpHandler interface into an abstract base class. adding (diff)
download	opensim-SC-e8412dcd42f6478f70f587d534a92a948cd6dd93.zip opensim-SC-e8412dcd42f6478f70f587d534a92a948cd6dd93.tar.gz opensim-SC-e8412dcd42f6478f70f587d534a92a948cd6dd93.tar.bz2 opensim-SC-e8412dcd42f6478f70f587d534a92a948cd6dd93.tar.xz

diff --git a/OpenSim/Data/MSSQL/AutoClosingSqlCommand.cs b/OpenSim/Data/MSSQL/AutoClosingSqlCommand.cs new file mode 100644 index 0000000..6a7d787 --- /dev/null +++ b/OpenSim/Data/MSSQL/AutoClosingSqlCommand.cs
@@ -0,0 +1,216 @@
	1	/*
	2	* Copyright (c) Contributors, http://opensimulator.org/
	3	* See CONTRIBUTORS.TXT for a full list of copyright holders.
	4	*
	5	* Redistribution and use in source and binary forms, with or without
	6	* modification, are permitted provided that the following conditions are met:
	7	* * Redistributions of source code must retain the above copyright
	8	* notice, this list of conditions and the following disclaimer.
	9	* * Redistributions in binary form must reproduce the above copyright
	10	* notice, this list of conditions and the following disclaimer in the
	11	* documentation and/or other materials provided with the distribution.
	12	* * Neither the name of the OpenSim Project nor the
	13	* names of its contributors may be used to endorse or promote products
	14	* derived from this software without specific prior written permission.
	15	*
	16	* THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY
	17	* EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
	18	* WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
	19	* DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY
	20	* DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
	21	* (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
	22	* LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
	23	* ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
	24	* (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
	25	* SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
	26	*/
	27
	28	using System;
	29	using System.Collections.Generic;
	30	using System.Text;
	31	using System.Data.SqlClient;
	32	using System.Data;
	33
	34	namespace OpenSim.Data.MSSQL
	35	{
	36	/// <summary>
	37	/// Encapsulates a SqlCommand object but ensures that when it is disposed, its connection is closed and disposed also.
	38	/// </summary>
	39	internal class AutoClosingSqlCommand : IDbCommand
	40	{
	41	private SqlCommand realCommand;
	42
	43	public AutoClosingSqlCommand(SqlCommand cmd)
	44	{
	45	realCommand = cmd;
	46	}
	47
	48	#region IDbCommand Members
	49
	50	public void Cancel()
	51	{
	52	realCommand.Cancel();
	53	}
	54
	55	public string CommandText
	56	{
	57	get
	58	{
	59	return realCommand.CommandText;
	60	}
	61	set
	62	{
	63	realCommand.CommandText = value;
	64	}
	65	}
	66
	67	public int CommandTimeout
	68	{
	69	get
	70	{
	71	return realCommand.CommandTimeout;
	72	}
	73	set
	74	{
	75	realCommand.CommandTimeout = value;
	76	}
	77	}
	78
	79	public CommandType CommandType
	80	{
	81	get
	82	{
	83	return realCommand.CommandType;
	84	}
	85	set
	86	{
	87	realCommand.CommandType = value;
	88	}
	89	}
	90
	91	IDbConnection IDbCommand.Connection
	92	{
	93	get
	94	{
	95	return realCommand.Connection;
	96	}
	97	set
	98	{
	99	realCommand.Connection = (SqlConnection) value;
	100	}
	101	}
	102
	103	public SqlConnection Connection
	104	{
	105	get
	106	{
	107	return realCommand.Connection;
	108	}
	109	}
	110
	111	IDbDataParameter IDbCommand.CreateParameter()
	112	{
	113	return realCommand.CreateParameter();
	114	}
	115
	116	public SqlParameter CreateParameter()
	117	{
	118	return realCommand.CreateParameter();
	119	}
	120
	121	public int ExecuteNonQuery()
	122	{
	123	return realCommand.ExecuteNonQuery();
	124	}
	125
	126	IDataReader IDbCommand.ExecuteReader(CommandBehavior behavior)
	127	{
	128	return realCommand.ExecuteReader(behavior);
	129	}
	130
	131	public SqlDataReader ExecuteReader(CommandBehavior behavior)
	132	{
	133	return realCommand.ExecuteReader(behavior);
	134	}
	135
	136	IDataReader IDbCommand.ExecuteReader()
	137	{
	138	return realCommand.ExecuteReader();
	139	}
	140
	141	public SqlDataReader ExecuteReader()
	142	{
	143	return realCommand.ExecuteReader();
	144	}
	145
	146	public object ExecuteScalar()
	147	{
	148	return realCommand.ExecuteScalar();
	149	}
	150
	151	IDataParameterCollection IDbCommand.Parameters
	152	{
	153	get { return realCommand.Parameters; }
	154	}
	155
	156	public SqlParameterCollection Parameters
	157	{
	158	get { return realCommand.Parameters; }
	159	}
	160
	161	public void Prepare()
	162	{
	163	realCommand.Prepare();
	164	}
	165
	166	IDbTransaction IDbCommand.Transaction
	167	{
	168	get
	169	{
	170	return realCommand.Transaction;
	171	}
	172	set
	173	{
	174	realCommand.Transaction = (SqlTransaction) value;
	175	}
	176	}
	177
	178	UpdateRowSource IDbCommand.UpdatedRowSource
	179	{
	180	get
	181	{
	182	return realCommand.UpdatedRowSource;
	183	}
	184	set
	185	{
	186	realCommand.UpdatedRowSource = value;
	187	}
	188	}
	189
	190	#endregion
	191
	192	#region IDisposable Members
	193
	194	public void Dispose()
	195	{
	196	SqlConnection conn = realCommand.Connection;
	197	try
	198	{
	199	realCommand.Dispose();
	200	}
	201	finally
	202	{
	203	try
	204	{
	205	conn.Close();
	206	}
	207	finally
	208	{
	209	conn.Dispose();
	210	}
	211	}
	212	}
	213
	214	#endregion
	215	}
	216	}