SQl server data collection feature

SQL SERver 2008 has one more nice feature: data collection feature. You can create trace profile and collect data about those collections. It manages caching ,collection cycle and all others. They created most of the options inside the management studio. You don’t need to know detailed scripts. It got some detailed steps to start. First, you need to create data warehouse db for that. It is a very nice feature to track servers.  You can create your own collections and custom reports. Creating a  collection is not in that menu. You need to use script or use sql profiler to create a collection script. See more here http://msdn.microsoft.com/en-us/library/bb677356.aspx

Here is one script to create your own data collection after going through some setup. Before running this script, read about how to setup data collection.http://msdn.microsoft.com/en-us/library/bb677179(v=sql.105).aspx:

USE msdb;

DECLARE @collection_set_id int;
DECLARE @collection_set_uid uniqueidentifier

EXEC dbo.sp_syscollector_create_collection_set
    @name = N'Custom query test1',
    @collection_mode = 0,
    @description = N'This is a test collection set',
    @logging_level=0,
    @days_until_expiration = 14,
    @schedule_name=N'CollectorSchedule_Every_15min',
    @collection_set_id = @collection_set_id OUTPUT,
    @collection_set_uid = @collection_set_uid OUTPUT
SELECT @collection_set_id,@collection_set_uid

DECLARE @collector_type_uid uniqueidentifier
SELECT @collector_type_uid = collector_type_uid FROM syscollector_collector_types 
WHERE name = N'Generic T-SQL Query Collector Type';

DECLARE @collection_item_id int
EXEC sp_syscollector_create_collection_item
@name= N'Custom query test1-item',
@parameters=N'
<ns:TSQLQueryCollector xmlns:ns="DataCollectorType">
<Query>
  <Value>select * from sys.dm_exec_query_stats</Value>
  <OutputTable>dm_exec_query_stats</OutputTable>
</Query>
 </ns:TSQLQueryCollector>',
    @collection_item_id = @collection_item_id OUTPUT,
    @frequency = 5, -- This parameter is ignored in cached mode
    @collection_set_id = @collection_set_id,
    @collector_type_uid = @collector_type_uid
SELECT @collection_item_id
   
GO

You can connect data sources to your reports inside BI studio. Their BI studio has also very nice gadgets for reports.

Test with database call and rollback your changes

This example is for MSTest. You can do the same thing for Nunit. We will have test init and test cleanup calls. Those will be called before and after each test. Whenever you define transaction scope, it will wrap your calls inside. Default scope option is Required.

We will rollback our changes at test cleanup. Of course, it will lock your records until your test completes. You could not do select statement from test table for all rows. You can read my previous post about transactions and locks in SQL.

        [TestInitialize()]
        public void MyTestInitialize()
        {
            //without any scope option. Default is Required scope option and Serializable trans type
             testTransScope = new TransactionScope();
        }
 
        [TestCleanup()]
        public void MyTestCleanup()
        {
             Console.WriteLine("Cleanup trans rollback");
             Transaction.Current.Rollback();
             testTransScope.Dispose();
        }

Our test method to add object and get that. This object will not be there after test is complete. However, your next identity value on that table will not be same.:

 [TestMethod()]
        public void CreateTest()
        {
            MailJobController target = new MailJobController();
            string name = "status for Omer" + Guid.NewGuid().ToString().Substring(0, 5);
            int id = target.AddStatus(name);
 
            Assert.IsTrue(id > 0);
 
             
        }

Our Controller method that we are testing:

 public int AddStatus(string dessert)
       {
            
               try
               {
                   // ...
                   StatusDefinition statusDefinition = new StatusDefinition() {Name = dessert};
                   db.StatusDefinitions.AddObject(statusDefinition);
                   db.SaveChanges();
                   Console.WriteLine("object id:"+statusDefinition.StatusDefinitionId);
                   
                   return statusDefinition.StatusDefinitionId;
               }
               catch (Exception ex)
               {
                   Console.WriteLine(ex.ToString());
               }
            
 
           return -1;
       }
 
        public string GetStatus(int id)
        {
            var obj = db.StatusDefinitions.Where(a => a.StatusDefinitionId == id).FirstOrDefault();
            if (obj != null)
                return obj.Name;
 
            return null;
        }

Create a Comma Delimited List Using SELECT Clause From Table Column

I should keep reference to this:


DECLARE @listIntoString VARCHAR(MAX)

    SELECT   @listIntoString = COALESCE(@listIntoString+',' ,'') +  COALESCE( color,'')
    FROM production.product
    SELECT @listIntoString, len(@listIntoString)

if column is null, we are just adding empty string.

If you want distinct colors in delimited list:

 DECLARE @listIntoString VARCHAR(MAX)
with cte as
(    select distinct color from production.product where color is not null
)
SELECT    @listIntoString = COALESCE(@listIntoString+',' ,'') +  COALESCE( color,'')
FROM cte
SELECT @listIntoString, len(@listIntoString)

MetadataException: Unable to load the specified metadata resource

This means that the application is unable to load the EDMX. If you see this error in your test run, it means you forgot to add your connection strings for your data context. 



Error should say something about connection string instead of that message.



Other reasons:

• MetadataArtifactProcessing property of the model may be wrong
• The connection string could be wrong. eDMX connection str syntax may be wrong. Copy the default and change inside.
• You might be using a post-compile task to embed the EDMX in the assembly, which may not working for renaming.
• May be you renamed some assembly name

For other reasons related to edmx file in your project, you may need to delete that and create that again. It will use correct Assembly names. Reset sometimes is the best way to fix things.

Get model attribute value from Controller using Reflection

C# is a nice language with those attribute decorations. You can create your own attributes and check their values for your test or for other purposes.

In MVC models, .NET framework provides data model validation attributes which inherit from Attribute class. You can get those attributes for your properties. Here is an example i just answered at stackoverflow.

We have Name property with DisplayName attribute which has public get property with "DisplayName" attribute.

public class MailJobView
    {
      

        public int MailJobId { get; set; }
 
        [DisplayName("Job Name")]
        [StringLength(50)]
        public string Name { get; set; }
}

public void TestAttribute()
    {
        MailJobView view = new MailJobView();
        string displayname = view.Attributes<DisplayNameAttribute>("Name") ;


    }

If you have one simple extension method, you can see the display name easily. Here, i am passing attribute type and property name for this object. You can do in a different way if you want, but reflection calls will be similar. This extension method will follow convention and lookup property in that attribute class. Property name will be class name with "Attribute" removed. If you want, you can remove constraint for inheriting from attribute class.

public static class AttributeSniff
{
    public static string Attributes<T>(this object inputobject, string propertyname) where T : Attribute
    {
        //each attribute can have different internal properties
        //DisplayNameAttribute has  public virtual string DisplayName{get;}
        Type objtype = inputobject.GetType();
        PropertyInfo propertyInfo = objtype.GetProperty(propertyname);
        if (propertyInfo != null)
        {
            object[] customAttributes = propertyInfo.GetCustomAttributes(typeof(T), true);

            // take only publics and return first attribute
            if (propertyInfo.CanRead && customAttributes.Count() > 0)
            {
                //get that first one for now

                Type ourFirstAttribute = customAttributes[0].GetType();
                //Assuming your attribute will have public field with its name
                //DisplayNameAttribute will have DisplayName property
                PropertyInfo defaultAttributeProperty = ourFirstAttribute.GetProperty(ourFirstAttribute.Name.Replace("Attribute",""));
                if (defaultAttributeProperty != null)
                {
                    object obj1Value = defaultAttributeProperty.GetValue(customAttributes[0], null);
                    if (obj1Value != null)
                    {
                        return obj1Value.ToString();
                    }
                }

            }

        }

        return null;
    }
}