How do I obtain the ServerName associated with the DBCommand object?

Topics: Data Access Application Block
Jul 24, 2010 at 6:56 PM
Edited Jul 24, 2010 at 6:58 PM

This is a repost from http://forums.asp.net/t/1582174.aspx per advice from one of the community members. 

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:

 

<add name="conn_Heartbeat" connectionString="Connection Timeout=60;Initial Catalog=heartbeat;
         Data Source=premiercedb0b.dell.com;Failover Partner=premiercedb0c.dell.com;
         Trusted_Connection=False;User ID=ol_sqlmaint_tools;PASSWORD=P@ssw0rd;"
      />

 

<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.

I know how to implement logging but am facing an issue getting access to the ServerName. 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.

Regards,
Sreejath S. Warrier

 

Jul 26, 2010 at 3:11 AM

Yes you can override the ExecuteDataset and put a logic that would enable you to obtain a reference to the connection that is being used.  Another way around it would be to instantiate a connection prior to calling ExecuteDataset, something like this:

SqlDatabase db = new SqlDatabase(connection);
DbConnection connection = db.CreateConnection();
connection.Open();
using(connection)
{
         DbCommand dbCommand = db.GetStoredProcCommand(procName);
         ds = db.ExecuteDataSet(dbCommand);
}
return ds;

The ExecuteDataSet method, prior to actually exeucting the command, gets the currently open connection (and creates a new one if there isn't any) and assign it to the DbCommand object you passed to it.  Thus, creating and opening a connection outside will cause the ExecuteDataSet to use that connection. 

Hope this helps.

Sarah Urmeneta
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com

Jul 26, 2010 at 5:08 PM

Sarah,

That worked!

Thanks a lot!

Sreejath