Pavels Sergijenko

Copy two identical object with different namespaces.

Sometimes you need to copy all object properties to a new object, which is created from identical class with the same properties, but from different namespace. You have several option, how to do it:

Of course you can use Reflection. This method has several disadvantages. One of them is performance. The second disadvantage is, when you have properties, which actually are array of another class, or List, where T is again another class. Remember, our goal to get a new object, which is initiated using classes from different namespace.
Another method is to use third party libraries like AutoMapper. It helps to do this job.

We are going to use serialization.

Complete Source Code

Download

What types of serialization we have ?

The options are BinaryFormatter and DataContractSeralilzer. The problem is that, that these serializers remember from what type of class the instance was created. But JSON format do not remember from what type of class the instance was created. JSON serializer is implemented by Microsoft in DataContractJosnSerializer.

Let’s write an extension method.

public static T CloneIntoNewType<T>(this object obj) where T : class
{
return (T)obj.CloneIntoNewType(typeof(T));
}

public static object CloneIntoNewType(this object obj, Type newType)
{
if (newType == null)
      return null;

Type currentType = obj.GetType();

DataContractJsonSerializer serializer = new DataContractJsonSerializer(currentType);
DataContractJsonSerializer deserializer = new DataContractJsonSerializer(newType);
using (MemoryStream ms = new MemoryStream())
   {
       serializer.WriteObject(ms, obj);
       ms.Position = 0;
       return deserializer.ReadObject(ms);
   }
}

I am creating two methods: one generic method and another simple method that accept new type of class as input parameter. Sometimes, when you work with reflection you will have only a class type – not the referrence to a class. Also enums are not reference classes so we can use simple method instead.

So what next?

Let’s created two class libraries – one with namespace Library1 and the second with namespace Library2. And put two classes in Library1 and two identical classes to Library2.

public class DemoClass
{
public DemoClass() {}

public string SimpleStringType { get; set; }
public InnerClass SimpleInnerClass { get; set; }
public List<InnerClass> InnerClassList { get; set; }
}

and

public class InnerClass
{
public InnerClass() { }

public int SimpleType { get; set; }
public int[] SimpleArray { get; set; }
}

As you can see, DemoClass have a property InnerClassList which is List of InnerClass. Also, Inner class contains array property SimpleArray. So finally let’s create an instance of class and use extension method

DemoClass demoClass = new DemoClass();
demoClass.SimpleStringType = "Simple Data";

demoClass.SimpleInnerClass = new InnerClass() { SimpleType = 234, SimpleArray = new int[] { 53, 82 } };

InnerClass innerClass = new InnerClass() { SimpleType = 45, SimpleArray = new int[] { 58, 89 } };
demoClass.InnerClassList = new List<InnerClass>();
demoClass.InnerClassList.Add(innerClass);

Library2.DemoClass demoClassNew = demoClass.CloneIntoNewType<Library2.DemoClass>();

Here is debug result view:

As you can see a new instance of class were created and all properties has the same values from the source object.

Last thing

You can use this method to copy all properties to the similar class. Try to comment out property in DemoClass in Library2 and see what happens.

Posted in C#

Creating DAL for accessing Analysis Services

Aug 29

Posted by umnik03

This article describes how to create Data Access Layer (DAL) for accessing Analysis Services. What is good:

it will be generated automatically from stored procedures using C#.
output can be a class, that contains methods for accessing data from Analysis Services.
output can be an assembly.

Code is written using C# – CodeDom. Generated output only depends on selected CodeDom provider. (CSharp, VB.NET)

Complete Source Code

Download

Problem description

There are several option to display reports from data cubes. One of them, of course, is MS Reporting Services. But sometimes business requirement is to design and show reports in UI for clients without using Reporting Services. In such cases developer can use local reports, but still he need to get data from data cube in order to provide it to client.

Existing Solutions

Accessing data using OPENROWSET, which use MOLAP provider and MDX Query as parameters.
Write a custom CLR table-valued function, which takes MDX Query as parameter.

OPENROWSET

OPENROWSET have limitation for query – 8KB, which, unfortunately, not documented. But you can find this limitation in OPENQUERY documentation. The documentation say’s

‘query‘ Is the query string executed in the linked server. The maximum length of the string is 8 KB.

So, if you MDX Query is too long, than you will get a syntax error.

Example of using OPENROWSET

SELECT	*
FROM 	OPENROWSET
	(
		'MSOLAP',
		'Data Source=localhost;Initial Catalog=Adventure Works DW 2008R2;Integrated Security=sspi;',
		'SELECT
			NON EMPTY
			{
				[Measures].[Internet Sales Amount]
			} ON COLUMNS,
			NON EMPTY
			{
				[Product].[Category].&[4]
				*
				[Product].[Product].[Product]
			} ON ROWS
			FROM [Adventure Works]'
	)

CLR table-valued function

Of course, we can write a custom CLR table-valued function, which will access Analysis Services using Microsoft.AnalysisServices.AdomdClient. The main problem, that this assembly is unsafe and it will cause you to set access level to Unsafe for your custom CLR function.

When you try to register Microsoft.AnalysisServices.AdomdClient assembly you will get warning message from MS SQL.

ALTER DATABASE [DALDemo] SET TRUSTWORTHY ON
GO

CREATE ASSEMBLY 	[Microsoft.AnalysisServices.AdomdClient]
FROM 		'C:\Program Files\Microsoft.NET\ADOMD.NET\100\Microsoft.AnalysisServices.AdomdClient.dll'
WITH 		PERMISSION_SET = UNSAFE;
GO

Warning: The SQL Server client assembly ‘microsoft.analysisservices.adomdclient, version=10.0.0.0, culture=neutral, publickeytoken=89845dcd8080cc91, processorarchitecture=msil.’ you are registering is not fully tested in SQL Server hosted environment.

Creating custom DAL

So, what we need:

Database which will have Stored procedures with MDX Query for Analysis Services.
Reference to Microsoft.AnalysisServices.AdomdClient assembly in C# project.
XML configuration file to be able to configure DAL.
CodeDom namespace for creating CodeCompileUnit.
TypeConverter class to convert SqlDbType to C# types. (Official mapping can be found here)
Adventure Works DW 2008R2 Analysis Service database. (Download here)

Database

Complete Install script for database can be found in source files.

Let’s create two stored procedures. First is simple MDX Query to a cube.

IF OBJECT_ID('[report].[GetDemoData]') IS NOT NULL
DROP PROCEDURE [report].[GetDemoData]
GO

CREATE PROCEDURE [report].[GetDemoData]
AS
BEGIN

SELECT 	'SELECT
		NON EMPTY
		{
			[Measures].[Internet Sales Amount]
		} ON COLUMNS,
		NON EMPTY
		{
			[Product].[Product].[Product]
			*
			[Sales Reason].[Sales Reason].[Sales Reason]
		} ON ROWS
FROM 	[Adventure Works]'

END
GO
PRINT 'Created stored procedure [report].[GetDemoData]'
GO

And the second is stored procedure with parameter

IF OBJECT_ID('[report].[SimpleRequest]') IS NOT NULL
DROP PROCEDURE [report].[SimpleRequest]
GO

CREATE PROCEDURE [report].[SimpleRequest]
@CategoryId INT
AS
BEGIN

SELECT 	'SELECT
		NON EMPTY
		{
			[Measures].[Internet Sales Amount]
		} ON COLUMNS,
		NON EMPTY
		{
			[Product].[Category].&[{0}]
			*
			[Product].[Product].[Product]
		} ON ROWS
FROM 	[Adventure Works]'

END
GO
PRINT 'Created stored procedure [report].[SimpleRequest]'
GO

As we can see, in second stored procedure I used string formatting expression like in C#, and parameter is defined in MDX as {0}. Notice that “report” schema is used to create stored procedures. Now, what we need is to query all stored procedures from schema “report”. Stored procedure will use system views sys.schemas, sysy.procedures and sys.parameters.

IF OBJECT_ID('[dalgenerator].[GetReportProcedures]') IS NOT NULL
DROP PROCEDURE [dalgenerator].[GetReportProcedures]
GO
CREATE PROCEDURE [dalgenerator].[GetReportProcedures]
@SchemaName NVARCHAR(50)
AS
BEGIN
SELECT           p.[name] AS ProcedureName,
s.[name] AS SchemaName,
                 pr.[name] AS Parameter_ParamterName,
                 CAST(pr.[user_type_id] AS INT) AS Paramter_TypeId
FROM             sys.schemas s
INNER JOIN       sys.procedures p
ON               p.[schema_id] = s.[schema_id]
LEFT JOIN        sys.parameters pr
ON               pr.[object_id] = p.[object_id]
WHERE            s.[name] = @SchemaName
ORDER BY         p.[object_id] ASC, pr.[parameter_id] ASC

END
GO
PRINT 'Created stored procedure [dalgenerator].[GetReportProcedures]'
GO

This stored procedure will select all stored procedures with parameters.

Project Solution

To be able to use AdomdClient namespace, we need to add reference to the project. AdomdClient.dll can be found either in

C:\Program Files\Microsoft.NET\ADOMD.NET\100\Microsoft.AnalysisServices.AdomdClient.dll

C:\Windows\assembly\GAC_MSIL\Microsoft.AnalysisServices.AdomdClient\
10.0.0.0__89845dcd8080cc91\Microsoft.AnalysisServices.AdomdClient.dll

If You can’t find the assembly, then You need to install Microsoft® SQL Server® 2008 R2 ADOMD.NET from Microsoft® SQL Server® 2008 R2 Feature Pack Also we need to create configuration file. Which will contain useful information like:

Path to where to store class file and assembly
Code language : CSharp, Visaul Basic or JScript. (Default code language is C#)
Two connection strings. One for MS SQL database (In my case database name is DALDemo) and another one for Analysis Service. (In my case database name is Adventure Works DW 2008 R2)
Schema name for selecting DAL store procedures. (In my case schema name is “report”)

Configuration file example:

<?xml version="1.0" encoding="utf-8"?>
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<ClassPath>C:\DropBox\My Dropbox\DemoProjects\DALDemo\DALGenerator\DALGenerator
  <SchemaName>report</SchemaName>
<AssemblyPath>C:\DropBox\My Dropbox\DemoProjects\DALDemo\DALGenerator\DALGenerator
  <CodeLanguage>CSharp</CodeLanguage>
Data Source=localhost;Initial Catalog=Adventure Works DW 2008R2;Integrated Security=sspi;
  <SQLConnectionStringName>Data Source=localhost;Initial Catalog=DALDemo;Integrated Security=sspi;</SQLConnectionStringName>
</configuration>

To create a code generator we need CodeDom namespace. We are going to create two static methods:

Generate
CompileCode

Static method Generate will generate DAL class with 8 metohd for each procedure from database. Half of methods will return AdomdDataReader and another one will return DataSet. Methods name is formed using simple pattern <schema_ProcedureNameReader> and <schema_ProcedureNameDataSet>. Static method CompileCode will compiles assembly as a result , which also will have 8 methods for each procedure in database. The main method GenerateCodeCompileUnit generates CodeCompileUnit. CodeCompileUnit will have defined class with the name of the schema, 8 methods. You can see the complete code in the source files.

Let’s go and see how to generate a class

public static void Generate(string fileNamespace, string className)
        {
string classPath = ConfigSettings("ClassPath");
if (string.IsNullOrWhiteSpace(classPath))
            {
classPath = Directory.GetParent(Environment.CurrentDirectory).Parent.FullName;
Console.WriteLine(string.Format("Warning:ClassPath in config not found. Default class path will be {0}", classPath));
            }
else if(!Directory.Exists(classPath))
            {
classPath = Directory.GetParent(Environment.CurrentDirectory).Parent.FullName;
Console.WriteLine(string.Format("Warning:ClassPath do not exists. Default class path will be {0}", classPath));
            }

CodeDomProvider provider = GetCurrentProvider();
string sourceFile;
            if (provider.FileExtension[0] == '.')
            {
sourceFile = classPath + "\\" + className + provider.FileExtension;
            }
            else
            {
sourceFile = classPath + "\\" + className + "." + provider.FileExtension;
            }

            //Defining Code Generator Options
CodeGeneratorOptions options = new CodeGeneratorOptions();
            options.BlankLinesBetweenMembers = true;
            options.BracingStyle = "C";

//Defining Text Writer with Tab Seperator definition
using (IndentedTextWriter textWriter = new IndentedTextWriter(new StreamWriter(sourceFile, false, Encoding.UTF8), "    "))
            {
                //Generating code in output file
provider.GenerateCodeFromCompileUnit(GenerateCodeCompileUnit(fileNamespace, className), textWriter, options);
                textWriter.Flush();
            }
        }

So, first of all, we check for class path existence and defining code generation options and indented text writer. Using code provider we passing CodeCompileUnit. As a result class is created. To generate an assembly we need create compiler parameters and add all referenced assemblies. CompileCode method will returnCompilerResults and copy assembly to location defined in config file. Code example:

public static CompilerResults CompileCode(string fileNamespace, string className, string assemblyName)
        {
if (!assemblyName.EndsWith(".dll"))
            {
assemblyName = assemblyName + ".dll";
            }

string assemblyPath = ConfigSettings("AssemblyPath");
if (string.IsNullOrWhiteSpace(assemblyPath))
            {
assemblyPath = Directory.GetParent(Environment.CurrentDirectory).Parent.FullName;
Console.WriteLine(string.Format("Warning:AssemblyPath in config not found. Default assembly path will be {0}", assemblyPath));
            }
else if (!Directory.Exists(assemblyPath))
            {
assemblyPath = Directory.GetParent(Environment.CurrentDirectory).Parent.FullName;
Console.WriteLine(string.Format("Warning:AssemblyPath do not exists. Default assembly path will be {0}", assemblyPath));
            }

CodeCompileUnit codeCompileUnit = GenerateCodeCompileUnit(fileNamespace, className);

CodeDomProvider provider = GetCurrentProvider();
CompilerParameters cp = new CompilerParameters();
            cp.ReferencedAssemblies.Add("System.dll");
            cp.ReferencedAssemblies.Add("System.Configuration.dll");
            cp.ReferencedAssemblies.Add("System.Data.dll");
            cp.ReferencedAssemblies.Add("System.Xml.dll");
cp.ReferencedAssemblies.Add(typeof(AdomdConnection).Assembly.Location);
            cp.OutputAssembly = assemblyName;
            cp.IncludeDebugInformation = false;
            cp.GenerateExecutable = false;
            cp.CompilerOptions = "/target:library /optimize";
            cp.GenerateInMemory = false;

CompilerResults cr = provider.CompileAssemblyFromDom(cp, codeCompileUnit);

            if (cr.Errors.HasErrors)
            {
StringBuilder sb = new StringBuilder();
foreach (CompilerError error in cr.Errors)
                {
                    sb.AppendLine(error.ErrorText);
                }
                throw new Exception(sb.ToString());
            }

FileInfo fileInfo = new FileInfo(cr.PathToAssembly);
            if (fileInfo.Exists)
            {
File.Copy(cr.PathToAssembly, assemblyPath + "//" + assemblyName, true);
                fileInfo.Delete();
            }

            return cr;
        }

Like in previous method, first, we checking for assembly path existence. Defining all assemblies. One tricky thing is that compiler can’t find assembly Microsoft.AnalysisServices.AdomdClient, so we have to tell compiler directly where Assembly is located.

Samples

Let’s create some simple samples to understand how to generate and then use generated class. First of all, we are going to generate a DAL class. We are going to use generate method and passing to it namespace and class name.

Generator.Generate("DALGenerator", "Report");

The result of executing this method is class with name Report in namespace DALGenerator. I am including only a part of it.

namespace DALGenerator
{
    using System.Configuration;
    using System.Data;
    using Microsoft.AnalysisServices.AdomdClient;
    using System.Data.SqlClient;

    public partial class Report
    {

private string GetConnectionString()
        {
return "Data Source=localhost;Initial Catalog=Adventure Works DW 2008R2;Integrated Securi" +
                "ty=sspi;";
        }

public Microsoft.AnalysisServices.AdomdClient.AdomdDataReader report_SimpleRequestReader(int CategoryId)
        {
string mdxQuery = string.Format("SELECT      NON EMPTY       {{       [Measures].[Internet Sales Amount]      }} O" +
                    "N COLUMNS,      NON EMPTY      {{            [Product].[Category].&[{0}]       *" +
"       [Product].[Product].[Product]      }} ON ROWS    FROM [Adventure Works]", CategoryId);
Microsoft.AnalysisServices.AdomdClient.AdomdConnection conn = new Microsoft.AnalysisServices.AdomdClient.AdomdConnection(GetConnectionString());
Microsoft.AnalysisServices.AdomdClient.AdomdCommand comm = new Microsoft.AnalysisServices.AdomdClient.AdomdCommand(mdxQuery, conn);
            try
            {
                conn.Open();
                return comm.ExecuteReader(CommandBehavior.CloseConnection);
            }
            finally
            {
                comm.Dispose();
            }
        }
    }
}

Finally, we can use this class to get data from Analysis services using simple code snippet:

            AdomdDataReader reader2 = new Report().report_SimpleRequestReader(4, connectionString, 30);
            while (reader2.Read())
            {
                Console.WriteLine(string.Format("{0} \t {1} \t {2}", reader2.GetValue(0), reader2.GetValue(1), reader2.GetValue(2)));
            }

Using of this snippet in console application will cause a result:

Now, let us try to execute static method CompileCode.

Generator.CompileCode("DALGenerator", "Report");

Again, we are passing namespace and class name. The result of this method execution is assembly – DALGenarator.dll. We can add reference to this assembly in Visual Studio and view assembly metadata in Object Browser

Limitation

In order to be able generate this on C# and VB we can’t use “using” statement and create our class static. Because some of the language do not have such language semantics. So “using” statement is replaced with try – finally structure. And final DAL class not a static. You can find some comments about it here.

Posted in C#, SSAS

Pavels Sergijenko

Copy two identical object with different namespaces.

Complete Source Code

What types of serialization we have ?

Let’s write an extension method.

So what next?

Last thing

Creating DAL for accessing Analysis Services

Complete Source Code

Problem description

Existing Solutions

OPENROWSET

CLR table-valued function

Creating custom DAL

Database

Project Solution

Samples

Limitation

Visitors Map

Tags

Follow “Pavels Sergijenko”