One thing that I have often found I have needed to do when programming is to change the connection string properties of an dataset table adapter or queries table adapter. Allowing changes to the connection to be made programmatically can be very beneficial. For example time-outs can be controlled and the database or SQL server instance can be changed on the fly.
The usual way of accessing the connection is the make a partial class on the adapter that exposes the properties that you want to have access to. While this method does work it is not very generic and requires partial classes to be made and maintained. This is OK if you have a small set but can become an unnecessary overhead if you have a large collection of adapters. As such having a generic method of solving this issue was required.
The first thing we have to do is make connection and connection modifier properties on the adapter public because by default they are set to internal.
Next we need to make use of the .Net reflection library. This will allow us to make a generic method that will work over all of the XSDs strongly typed datasets. Due to the way the table adapters and query table adapters are generated I created two methods, however if required I’m sure they could be combined.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 |
/// <summary> /// Changes the database a table adapter connects to /// </summary> /// <typeparam name="TA">The type of table adater</typeparam> /// <param name="TableAdapter">The instance of the table adapter</param> /// <param name="serverName">The name of the database server</param> /// <param name="sqlDatabaseName">The name of the database the table adapter should connect to</param> /// <param name="userName">The user name of the user used to connect to the database</param> /// <param name="timeout">The new timeout (In seconds)</param> /// <param name="password">The users password</param> public static void ChangeTableAdapterConnection<TA>(ref TA TableAdapter, string serverName, string sqlDatabaseName, string userName, string password, int timeout) { try { PropertyInfo adapterCon = TableAdapter.GetType().GetProperty("Connection"); if (adapterCon != null) { SqlConnection newSQLConnection = (SqlConnection)adapterCon.GetValue(TableAdapter, null); SqlConnectionStringBuilder csBulider = new SqlConnectionStringBuilder(newSQLConnection.ConnectionString); csBulider.DataSource = serverName; csBulider.InitialCatalog = sqlDatabaseName; csBulider.UserID = userName; csBulider.Password = password; newSQLConnection.ConnectionString = csBulider.ConnectionString; adapterCon.SetValue(TableAdapter, newSQLConnection, null); } else { throw new Exception("Connection property not found."); } FieldInfo timeoutField = TableAdapter.GetType().GetField("_commandCollection", BindingFlags.GetProperty | BindingFlags.Instance | BindingFlags.NonPublic); MethodInfo initCC = TableAdapter.GetType().GetMethod("InitCommandCollection", BindingFlags.GetProperty | BindingFlags.Instance | BindingFlags.NonPublic); if (timeoutField != null && initCC != null) { initCC.Invoke(TableAdapter, null); SqlCommand[] commandCollection = (SqlCommand[])timeoutField.GetValue(TableAdapter); foreach (SqlCommand singleCommand in commandCollection) { singleCommand.CommandTimeout = timeout; } timeoutField.SetValue(TableAdapter, commandCollection); } else { throw new Exception("Timout property not found."); } } catch (Exception _exception) { throw new Exception(_exception.ToString()); } } /// <summary> /// Changes the database connection to all the queries in a queries table adapter /// </summary> /// <typeparam name="QTA">The type of the queries table adapter</typeparam> /// <param name="QueriesTableAdapter">The instance of the queries table adapter</param> /// <param name="serverName">The name of the database server</param> /// <param name="sqlDatabaseName">The name of the database the table adapter should connect to</param> /// <param name="userName">The user name of the user used to connect to the database</param> /// <param name="timeout">The new timeout (In seconds)</param> /// <param name="password">The users password</param> public static void ChangeQueriesTableAdapterConnection<QTA>(ref QTA QueriesTableAdapter, string serverName, string sqlDatabaseName, string userName, string password, int timeout) { try { FieldInfo qAdapterCommandCollection = QueriesTableAdapter.GetType().GetField("_commandCollection", BindingFlags.GetProperty | BindingFlags.Instance | BindingFlags.NonPublic); MethodInfo initCC = QueriesTableAdapter.GetType().GetMethod("InitCommandCollection", BindingFlags.GetProperty | BindingFlags.Instance | BindingFlags.NonPublic); if (qAdapterCommandCollection != null && initCC != null) { initCC.Invoke(QueriesTableAdapter, null); IDbCommand[] qaCC = (IDbCommand[])qAdapterCommandCollection.GetValue(QueriesTableAdapter); foreach (SqlCommand singleCommand in qaCC) { SqlConnection newSQLConnection = singleCommand.Connection; SqlConnectionStringBuilder csBulider = new SqlConnectionStringBuilder(newSQLConnection.ConnectionString); csBulider.DataSource = serverName; csBulider.InitialCatalog = sqlDatabaseName; csBulider.UserID = userName; csBulider.Password = password; newSQLConnection.ConnectionString = csBulider.ConnectionString; singleCommand.Connection = newSQLConnection; singleCommand.CommandTimeout = timeout; } qAdapterCommandCollection.SetValue(QueriesTableAdapter, qaCC); } else { throw new Exception("Could not find command collection."); } } catch (Exception _exception) { throw new Exception(_exception.ToString()); } } |
Hopefully this code will be helpful to some people as I could find very little online on how to do this.