One requirement that the business has requested is to identify whenever a switchover occurs. They know that this can be done in SQL Server using alerts. But what they need is for this to be implemented in the DAL code so that the code will write an entry into the log whenever a connection switchover (from primary to failover or vice versa) occurs with the method name, time when the connection was attempted and the time when the switchover occured. This requirement and the need to implement this in DAL are non-negotiable.
The only way I know of to figure this out is to check the dbCommand.Connection.DataSource property.
However, in the method that I posted above, DBCommand.Connection object is null prior to:
ds = db.ExecuteDataSet(dbCommand);
and the connectionstate of the dbCommand.Connection object is closed (and the DataSource is null) after the above method is executed.
This is expected behavior, but I am wondering if there is any way to obtain access to the Connection object while it is open and check the DataSource property.
I suspect I can do this by overriding the ExecuteDataset method, but wanted to check if there is something simpler that I am missing.
I think that it should be feasible to grab the current connection being used before return ds(DataSet).
you can use Logging application block to log this information to desired location (Database, email or flat file or custom) location as explained in my blog article at
The only object in that scope that had a Connection property was the dbCommand and the ConnectionState for this connection was closed and for this Connection, the DataSource was null and the connectionstring was empty.
I will follow your advice and post this query in the relevant codeplex forum.
None
0 Points
13 Posts
MS Ent Lib:How do I obtain the ServerName associated with the DBCommand object?
Jul 23, 2010 01:31 PM|sreejath|LINK
All,
The application I am currently working on uses Microsoft Enterprise Library Data Access block for Data Access.
I have pasted the following method to provide context:
public static DataSet ExecuteDataSet(string connection, string procName) { DataSet ds = null; try { SqlDatabase db = new SqlDatabase(connection); DbCommand dbCommand = db.GetStoredProcCommand(procName); ds = db.ExecuteDataSet(dbCommand); return ds; } catch (Exception ex) { //Exception handling code } }In the next release we are planning to use SQL Server 2008 R2 as our database and we will be implementing mirroring to provide automatic failover.
I have been tasked with rewriting the DAL to take advantage of this.
A sample connection string used by the application today is as below:
<div style="position: absolute; left: -10000px; top: 183px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste"><add name="conn_Heartbeat" connectionString="Connection Timeout=60;Initial Catalog=heartbeat;</div> <div style="position: absolute; left: -10000px; top: 183px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste"> Data Source=premiercedb0b.dell.com;Failover Partner=premiercedb0c.dell.com;</div> <div style="position: absolute; left: -10000px; top: 183px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste"> Trusted_Connection=False;User ID=ol_sqlmaint_tools;PASSWORD=P@ssw0rd;"</div> <div style="position: absolute; left: -10000px; top: 183px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste"> /></div><add name="cn_Name" connectionString="Connection Timeout=60;Initial Catalog=dbName; Data Source=dbservername; Trusted_Connection=False;User ID=uid;PASSWORD=pwd;" />The same connection string in the "to be" application will be as below:
<add name="cn_Name" connectionString="Connection Timeout=60;Initial Catalog=dbName; Data Source=dbservername; Failover Partner = failoverdbservername; Trusted_Connection=False;User ID=uid;PASSWORD=pwd;" />One requirement that the business has requested is to identify whenever a switchover occurs. They know that this can be done in SQL Server using alerts. But what they need is for this to be implemented in the DAL code so that the code will write an entry into the log whenever a connection switchover (from primary to failover or vice versa) occurs with the method name, time when the connection was attempted and the time when the switchover occured. This requirement and the need to implement this in DAL are non-negotiable.
The only way I know of to figure this out is to check the dbCommand.Connection.DataSource property.
However, in the method that I posted above, DBCommand.Connection object is null prior to:
and the connectionstate of the dbCommand.Connection object is closed (and the DataSource is null) after the above method is executed.
This is expected behavior, but I am wondering if there is any way to obtain access to the Connection object while it is open and check the DataSource property.
I suspect I can do this by overriding the ExecuteDataset method, but wanted to check if there is something simpler that I am missing.
</div>DBCommand Mirroring Failover Enterprise Library Connection
Sreejath S. Warrier
Star
13042 Points
3174 Posts
Re: MS Ent Lib:How do I obtain the ServerName associated with the DBCommand object?
Jul 24, 2010 01:31 PM|sukumarraju|LINK
I think that it should be feasible to grab the current connection being used before return ds(DataSet).
you can use Logging application block to log this information to desired location (Database, email or flat file or custom) location as explained in my blog article at
http://weblogs.asp.net/sukumarraju/archive/2009/10/04/microsoft-enterprise-library-4-1-exception-handling-block.aspx
Let you know that here is the dedicated forum on Enterprise Library at http://entlib.codeplex.com/Thread/List.aspx
If the above solution is not helpful, Post your query in codePlex forums(above).
Application Architecture Guide 2.0
My Blog
Twitter
None
0 Points
13 Posts
Re: MS Ent Lib:How do I obtain the ServerName associated with the DBCommand object?
Jul 24, 2010 01:51 PM|sreejath|LINK
Sukumar,
Thanks for replying.
The only object in that scope that had a Connection property was the dbCommand and the ConnectionState for this connection was closed and for this Connection, the DataSource was null and the connectionstring was empty.
I will follow your advice and post this query in the relevant codeplex forum.
Thanks a lot
~S
Sreejath S. Warrier
None
0 Points
13 Posts
Re: MS Ent Lib:How do I obtain the ServerName associated with the DBCommand object?
Jul 26, 2010 12:03 PM|sreejath|LINK
For those that are interested, the query has been answered here:
http://entlib.codeplex.com/Thread/View.aspx?ThreadId=221290
Sreejath S. Warrier