How to Use Sql Server Extended properties using visual studio Asp.Net C#

Posted by james on June 15, 2011 Leave a comment (0) Go to comments

Being application developers that use Visual Studio, we need a way to access and work with these extended database properties from our applications. In order to demonstrate how we can access these extended properties, the following code examples use a class library project that hosts a test fixture.

If you need to engage in this sort of work, you will likely want to make your application configurable with the current database property values. For example, if your application is configured to work against version 2.0 and the database is stamped as version 1.0, you will likely want to throw an exception. The last thing you will want to do is hard code the database version into your application. Fortunately, we have the System.Configuration class that can be used to configure your application. In this case, the application is a simple test fixture that contains a few tests. In order to make the application configurable, we need the following:

app.config file with necessary configuration section
configuration class
The app.config file handles two things:

Defines the configuration section (in this case, the section is named SQLExtendedProperties)
Creates the relationship between the configuration section and the configuration class
If you are new to system configurations, check out ASP MVC Team Member Phil Haack’s blog post on the topic: http://haacked.com/archive/2007/03/12/custom-configuration-sections-in-3-easy-steps.aspx.

In this example, the application is configured to work with version 1.0.0. Also note that in the configuration, OLE-DB connection string information is associated with the version property. If you are thinking that we could have different configuration sections for test, debug and production, you are 100% correct and on the right track!

The SQLExtendedProperties class provides a structured class around the SQLExtendedProperties configuration section. List1 illustrates four helper methods that are used to add, update, query and remove an extended property.

The code outlined in List 1 is nothing more than wrapping around ADO.NET and our application configuration. The application configuration is used to drive which database server is used. In addition, the application configuration specifies which database version is appropriate to interact with our application. List2 puts it all together in a series of unit and integration tests that verifies the SQL Server Extended Property Functionality.

List 1:

private static void ExtendedPropertTest(SqlConnection local_conn,
 string PropertyName)
 {
using 
(var _command = 
  new SqlCommand("sys.sp_ExtendedPropertTest",local_conn))
 {
 _command.CommandType = CommandType.StoredProcedure;
 SqlCommandBuilder.DeriveParameters(_command);
 _command.Parameters["@name"].Value = PropertyName;
 _command.ExecuteNonQuery();
}
}
 
 
private static string GetExtendedPropertyValue(SqlConnection
 local_conn, string PropertyName)
{
 string _version;
using 
(var _command =
 new SqlCommand(String.Format("select value from
      sys.extended_properties where name = '{0}'", PropertyName),
   _  connection))
{
 var _reader = _command.ExecuteReader();
 _reader.Read();
 _version = _reader.GetString(0);
 _reader.Close();
 return _version;
}
}
 
 
private static void AddExtendedProperty(SqlConnection local_conn,
 string PropertyName)
{
var _command = new SqlCommand("sys.sp_addextendedproperty",
 local_conn);
 _command.CommandType = CommandType.StoredProcedure;
 
SqlCommandBuilder.DeriveParameters(_command);
 
 _command.Parameters["@name"].Value = PropertyName;
 _command.Parameters["@value"].Value =
 SQLExtendedProperties.LoadBySectionName().version;
 
 _command.ExecuteNonQuery();
 
}
 
 
private static void UpdateExtendedProperty(SqlConnection
 connection, string PropertyName, string PropertyValue)
 {
 using (var _command = new
 SqlCommand("sys.sp_updateextendedproperty", local_conn))
{
 _command.CommandType = CommandType.StoredProcedure;
 SqlCommandBuilder.DeriveParameters(_command);
 _command.Parameters["@name"].Value = PropertyName;
 _command.Parameters["@value"].Value = PropertyValue;
 _command.ExecuteNonQuery();
}
 
} 
 
private static SqlConnection GetConnection()
 {
var local_conn = new
SqlConnection(SQLExtendedProperties.LoadBySectionName().connection);
 local_conn.Open();
 return local_conn;
}
 
List 2:
 
[Test]
public void can_retrieve_version_value_from_config_file()
{
  var _version = SQLExtendedProperties.LoadBySectionName().version;
  Assert.AreEqual("1.0.0", _version);
}
 
 [Test]
public void can_open_and_close_dblocal_conn()
{
  var local_conn = GetConnection();
  Assert.AreEqual(ConnectionState.Open, local_conn.State);
 
  local_conn.Close();
  Assert.AreEqual(ConnectionState.Closed, local_conn.State);
 }
 
 [Test]
public void can_create_extended_property()
{
  var local_conn = GetConnection();
  AddExtendedProperty(local_conn, "Version");
 
  Assert.AreEqual(SQLExtendedProperties.LoadBySectionName().version,
   GetExtendedPropertyValue(local_conn,"Version"));
 
  ExtendedPropertTest(local_conn,"Version");
 
  local_conn.Close();
 
}
 
 [Test]
public void can_update_extended_property()
{
 var local_conn = GetConnection();
 
 AddExtendedProperty(local_conn, "Version");
 
 UpdateExtendedProperty(local_conn, "Version","2.0.0");
 
 Assert.AreEqual("2.0.0", GetExtendedPropertyValue(local_conn, 
  "Version"));
 
ExtendedPropertTest(local_conn, "Version");
 
local_conn.Close();
 
}