title: Soap WebService with Database access
body: <p>So basically I have a Soap webservice that does inserts and retrieves some data from a SqlServer database.</p>
  
  <p>The webservice uses a Singleton that is responsible for the DB stuff.</p>
  
  <pre><code>public class Service : System.Web.Services.WebService
  {
      private DBAccess dbaccess;
      public Service()
      {
          dbaccess = DBAccessLocalhost.GetInstance();
      }
  
      [WebMethod]
      public List&lt;Profile&gt; XXX(Guid a, uint b, DateTime c)
      {
          return dbaccess.XXX(a, b, c);
      }
      ...
  }
  </code></pre>
  
  <p>The singleton that access the database. It has allot of methods that basically do this.</p>
  
  <pre><code>public class DBAccessLocalhost : DBAccess
  {
      private static DBAccess instance = null;
      private string connectionString;
  
      public static DBAccess GetInstance()
      {
          if (instance == null)
              instance = new DBAccessLocalhost();
  
          return instance;
      }
  
      private DBAccessLocalhost()
      {
          connectionString = "Data Source=localhost;Initial Catalog=DBName;Integrated Security=True;Max Pool Size=2000;Pooling=false";
      }
  
      public override void XXX(Guid a, uint b, DateTime c)
      {
          SqlCommand cmd;
          SqlDataReader dr;
          string strSql = "SP_Name";
          SqlConnection conn;
          conn = new SqlConnection(connectionString);
  
          try
          {
              conn.Open();
  
              cmd = new SqlCommand(strSql, conn);
              cmd.CommandType = System.Data.CommandType.StoredProcedure;
  
              cmd.Parameters.AddWithValue("@a", a.ToString());
              cmd.Parameters.AddWithValue("@b", (int)b);
              cmd.Parameters.AddWithValue("@c", c);
              dr = cmd.ExecuteReader();
  
              while (dr.Read() &amp;&amp; dr.HasRows)
              {
                  //Do stuff...
              }
  
              dr.Close();
  
          }catch (Exception ex)
          {
              throw new DBError("Errors: " + ex.Message);
          }
          finally
          {
              conn.Close();
          }
       }
  </code></pre>
  
  <p>Second version:</p>
  
  <pre><code>public class DBAccessLocalhost : DBAccess
  {
      private static DBAccess instance = null;
      private string connectionString;
  
      public static DBAccess GetInstance()
      {
          if (instance == null)
              instance = new DBAccessLocalhost();
  
          return instance;
      }
  
      private DBAccessLocalhost()
      {
          connectionString = "Data Source=localhost;Initial Catalog=DBName;Integrated Security=True;Max Pool Size=2000;Pooling=true";
      }
  
      public override void XXX(Guid a, uint b, DateTime c)
      {
          string strSql = "SP_Name";
  
          using (SqlConnection conn = new SqlConnection(connectionString))
          {
              using (SqlCommand cmd = new SqlCommand(strSql, conn))
              {
                  cmd.CommandType = System.Data.CommandType.StoredProcedure;
  
                  cmd.Parameters.AddWithValue("@a", a.ToString());
                  cmd.Parameters.AddWithValue("@b", (int)b);
                  cmd.Parameters.AddWithValue("@c", c);
  
                  try
                  {
                      conn.Open();
  
                      using (SqlDataReader dr = cmd.ExecuteReader())
                      {
                          while (dr.Read())
                          {
                              //....
                          }
                      }
                  }
                  catch (Exception ex)
                  {
                      throw new DBError("Error: " + ex.Message);
                  }
                  finally
                  {
                      conn.Close();
                  }
              }
          }
  </code></pre>
  
  <p>The problem is that sometimes I get this exception:</p>
  
  <pre><code> DBAccessWebSerice,System.ServiceModel.FaultException: Server was unable to process 
   request. ---&gt; Errors: ExecuteNonQuery requires an open and available    
   Connection. The connection's current state is closed (Sometimes says connecting).
  
   Server stack trace: 
   at System.ServiceModel.Channels.ServiceChannel.HandleReply(ProxyOperationRuntime 
   operation, ProxyRpc&amp; rpc)
   at System.ServiceModel.Channels.ServiceChannel.Call(String action, Boolean oneway, 
   ProxyOperationRuntime operation, Object[] ins, Object[] outs, TimeSpan timeout)
   at System.ServiceModel.Channels.ServiceChannelProxy.InvokeService(IMethodCallMessage  
   methodCall, ProxyOperationRuntime operation)
   at System.ServiceModel.Channels.ServiceChannelProxy.Invoke(IMessage message)
  </code></pre>
  
  <p>The problem may be because there are to many connections at the same time to the database?</p>
  
  <p>Probably this isn't the best way do to this. But if you have a better way of doing this or have a solution for this problem, please say.</p>