Connection Pooling?

fordc03fordc03 Posts: 49 Bronze 2
edited June 15, 2006 4:12PM in SQL Toolkit Previous Versions
Is it possible to pass an object to the Register Method for a connection that is already open to a Database?

For example:

SqlConnection sqlconn = new SqlConnection();
sqlconn.ConnectionString("DATA SOURCE=<server>;DATABASE=<dbname>;INTEGRATED SECURITY=SSPI;");

....Do Some Stuff to the Database....

db1.Register(ConnectionObject)


I would like to use connection pooling and not open any more connections than I have to.

The end result is this...Connect to a server that has 1,000 databases, make snapshots of them all.

I'm running into performance issues with opening many connections to the server (creating 40 threads and attaching to 40 databases to do a snapshot) My problem is that it's creating a brand new connection every time...I want to re-use my connection by connecting to the Master database on the server, then switching to the next database in my array therefore using connection pooling because my connection is already open and in use.

Let me know if I make sense. :)

Thanks,

Comments

  • Hi there,
    I am sorry to say that with the current API this is not possible.
    However here is an idea that you could possibly make work for yourself. However this idea is not supported and probably will not be possible in future releases. (ConectionProperties may become a sealed class.)
    The basis is taken from the code snippits.
    		class &#91;b&#93;MyConnectionProperties&#91;/b&#93; : ConnectionProperties
    		&#123;
    			public MyConnectionProperties &#40;string serverName, string databaseName&#41; : base&#40;serverName, databaseName&#41;
    			&#123;
    				
    			&#125;
    
    			public MyConnectionProperties&#40;string serverName, string databaseName, string userName, string password&#41; : base &#40;serverName, databaseName, userName, password&#41;
    			&#123;				
    			&#125;
    			public override string ToString&#40;&#41;
    			&#123;
    				StringBuilder sb = new StringBuilder&#40;&#41;;
    				sb.Append&#40;"Data Source="&#41;;
    				sb.Append&#40;this.ServerName&#41;;
    				sb.Append&#40;";initial catalog="&#41;;
    				sb.Append&#40;this.DatabaseName&#41;;
    				sb.Append&#40;";"&#41;;
    				sb.Append&#40;"Integrated security=SSPI;"&#41;;
    				sb.Append&#40;";&#91;b&#93;pooling=true&#91;/b&#93;"&#41;;
    				return sb.ToString&#40;&#41;;
    			&#125;			
    		&#125;
    		private static void LoadAndSaveASnapshot&#40;&#41;
    		&#123;
    			//register a database using integrated security
    			Database db1=new Database&#40;&#41;;
    			db1.Register&#40;new &#91;b&#93;MyConnectionProperties&#91;/b&#93;&#40;@"davidc\sql2005", "database1"&#41;, Options.Default&#41;;
    
    			//save the database to disk
    			db1.SaveToDisk&#40;@"c:\WidgetStaging.snp"&#41;;
    
    			Database db2=new Database&#40;&#41;;
    			db2.LoadFromDisk&#40;@"c:\WidgetStaging.snp"&#41;;
    
    			//dispose of the objects
    			db1.Dispose&#40;&#41;;
    			db2.Dispose&#40;&#41;;
    		&#125;
    
    

    From here you could modify the connection string further.
    Note I have not supported non integrated security in this code. I have verified that this will work with SQL Bundle 5.1, but no further.
    I hope that helps...
  • fordc03fordc03 Posts: 49 Bronze 2
    Hey that's pretty cool...I might be able to do something with that.
Sign In or Register to comment.