Showing posts with label .NET. Show all posts
Showing posts with label .NET. Show all posts

NLog for Flexible Application Logging in .NET

As much as I have enjoyed working with log4net in the psat NLog works much more seamlessly for .NET apps (it has good, solid abstractions)

From Nuget Package Manager, you can find and reference NLog and the accompanying NLog.Config which simplifies setup. To configure NLog logging, simply point the required properties (you need at least a logFile variable, a target and a rule to get started) to your desired values in the config file that NLog.Config creates in your project root  (NLog.config):

Configuration:

 <?xml version="1.0" encoding="utf-8" ?>  
 <nlog xmlns="http://www.nlog-project.org/schemas/NLog.xsd"  
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  
    xsi:schemaLocation="http://www.nlog-project.org/schemas/NLog.xsd NLog.xsd"  
    autoReload="true"  
    throwExceptions="false"  
    internalLogLevel="Off" internalLogFile="c:\temp\nlog-internal.log">  
  <variable name="logFilePath" value="C:\NLog\IDG.log" />  
  <targets>  
   <target name="logfile"  
     xsi:type="File"  
     fileName="${logFilePath}"  
     layout="${longdate}  LEVEL=${level:upperCase=true}: ${message}"  
     keepFileOpen="true" />  
  </targets>  
  <rules>  
   <logger name="*" minlevel="Info" writeTo="logfile" />  
   <logger name="*" minlevel="Warn" writeTo="logFile"/>  
  </rules>  
 </nlog>  
This example uses a log file target in C:\NLog directory; you can utilize a wide variety of logging targets to broadcast app errors

Source Code:

 using Microsoft.VisualStudio.TestTools.UnitTesting;  
 using NLog;  
 namespace ExtRSTests  
 {  
   [TestClass]  
   public class NLogTests  
   {  
     private static Logger logger = LogManager.GetCurrentClassLogger();  
     [TestMethod]  
     public void TestLoggerToFile()  
     {  
       logger.Warn("Something in the app happened that may indicate trouble ahead....");  
       logger.Error("Uh-oh. Something broke.");  
     }  
   }  
 }  
With the logging levels, log formatting (timestamps) and abundant integration options, NLog is a complete logging solution

With NLogger you can implement logging for an array of targets including file, email, database and 3rd party integrations (ie. send message to Slack channel if logger generates any "Error" or "Fatal" level log message).


Reference: https://www.infoworld.com/article/3144535/how-to-work-with-nlog-in-net.html

.NET XML Serialization with System.Xml.Serialization

From SOAP, to app configuration, model templating, EDI/ETL, and beyond, XML is a mainstay in software and will continue to be (see- HTML)


Source Code:
 using System;  
 using Microsoft.VisualStudio.TestTools.UnitTesting;  
 using System.Xml.Serialization;  
 using System.IO;  
 using System.Xml.Linq;  
 using System.Xml;  
 namespace DemoTests  
 {  
   [TestClass]  
   public class TextXmlSerialization  
   {  
     public const string xml =  
       "<XMLEntityCollection>" +  
         "<XMLEntities>" +  
           "<XMLEntity>" +   
             "<ID>1</ID>" +   
             "<Name>TestName1</Name>" +   
           "</XMLEntity>" +  
           "<XMLEntity>" +  
             "<ID>2</ID>" +  
             "<Name>TestName2</Name>" +  
           "</XMLEntity>" +  
         "</XMLEntities>" +  
       "</XMLEntityCollection>";  
     [TestMethod]  
     public void TestDeserialize()  
     {  
       XmlSerializer serializer = new XmlSerializer(typeof(XMLEntityCollection));  
       using (StringReader reader = new StringReader(xml))  
       {  
         XMLEntityCollection ents = (XMLEntityCollection)serializer.Deserialize(reader);  
         TestSerialize(ents);  
       }  
     }  
     [TestMethod]  
     public void TestSerialize(XMLEntityCollection ents)  
     {  
       XmlSerializer xmlSerializer = new XmlSerializer(ents.GetType());  
       using (StringWriter stringWriter = new StringWriter())  
       {  
         xmlSerializer.Serialize(stringWriter, ents);  
         Assert.IsNotNull(stringWriter);  
       }  
     }  
   }  
   [Serializable()]  
   public class XMLEntity  
   {  
     [XmlElement("ID")]  
     public string Id { get; set; }  
     [XmlElement("Name")]  
     public string Name { get; set; }  
   }  
   [Serializable()]  
   [XmlRoot("XMLEntityCollection")]  
   public class XMLEntityCollection  
   {  
     [XmlArray("XMLEntities")]  
     [XmlArrayItem("XMLEntity", typeof(XMLEntity))]  
     public XMLEntity[] Ents { get; set; }  
   }  
 }  



.NET XML Deserialization with System.Xml.Serialization: 


Instantiated (ents) object containing XML from the deserialized XML string



.NET XML Serialization with System.Xml.Serialization:


XML genertaed  by the stringWriter when serializing ents object into XML using the NET XMLSerializer type



Reference: https://stackoverflow.com/questions/364253/how-to-deserialize-xml-document

Newtonsoft JSON .NET Nuget Nugget

Most API payloads are in XML or JSON; it is best to know both of these data structures, and how to serialize/deserialize them

The JSON parsing utilities found in Newtonsoft.Json are very. very useful and should be common knowledge for any .NET developer who works with API data or anything producing or derived from JSON (JavaScript Object Notation).

In general, to use Newtonsoft.Json you simply need to create a .NET class hierarchy that mimics the structure and hierarchy of the target JSON. Once that is setup, serializing in-memory objects to JSON and deserializing the JSON back to in-memory objects is a breeze.

You achieve this by normal class hierarchy and making List<> of child objects, array properties, etc. Newtonsoft's 'JsonProperty' class property decorator maps JSON properties and the builtin serialization and deserialization methods facilitate working between JSON strings and the in-memory objects they represent.

The C# source code below demonstrates serialization from a SQL Server 2017 SSRS API v2 JSON response and then serializing that object back into JSON.

Source Code:

 using System;  
 using Microsoft.VisualStudio.TestTools.UnitTesting;  
 using Newtonsoft.Json;  
 using System.Collections.Generic;  
 using System.Net.Http;  
 using System.Threading.Tasks;  
 namespace DemoTests  
 {  
   [TestClass]  
   public class DemoTestJSON  
   {  
     [TestMethod]  
     public async Task TestDeserializeJSON()  
     {  
       HttpClient client = new HttpClient(new HttpClientHandler() { UseDefaultCredentials = true });  
       client.BaseAddress = new Uri("http://localhost/reports/api/v2.0/reports");  
       var response = await client.GetAsync(client.BaseAddress);  
       var deserial = JsonConvert.DeserializeObject<APIGenericItemsResponse>(await response.Content.ReadAsStringAsync());       
       TestSerializeJSON(deserial);  
       Assert.IsNotNull(deserial);  
     }  
     [TestMethod]  
     public void TestSerializeJSON(APIGenericItemsResponse genericObject)  
     {      
       string serial = JsonConvert.SerializeObject(genericObject);  
       Assert.IsNotNull(null);  
     }  
   }  
   public class APIGenericItemsResponse  
   {  
     [JsonProperty("@odata.context")]  
     public string Context { get; set; }  
     [JsonProperty("value")]  
     public List<GenericItem> GenericItem { get; set; }  
   }  
   public class GenericItem  
   {  
     [JsonProperty("Id")]  
     public string Id { get; set; }  
     [JsonProperty("Name")]  
     public string Name { get; set; }  
     [JsonProperty("Path")]  
     public string Path { get; set; }  
   }  
 }  


SSRS API v2 /Reports JSON Response:




JSON Deserialization with Newtonsoft.Json:
The deserial variable holds an in-memory .NET object of type APIGenericResponse, derived (deserialized) from the SSRS API JSON response




JSON Serialization with Newtonsoft.Json:

The serial variable is simply the serialization APIGenericItemsResponse object serialized into a JSON string


Reference: https://www.newtonsoft.com/json/help/html/Introduction.htm

base in C#

The base keyword in C# allows a subclass to access base (superclass) members.

All credit to Suresh Dasari of Tutlane (reference below) on explaining this so effectively in just a few steps of code.

What is shown here is the Details subclass overriding the Users base class' "GetInfo()" method and including the base behavior (Console.WriteLine("Name: {0}", name); ... Console.WriteLine("Location: {0}", location); - along with- some new behavior (Console.WriteLine("Age: {0}", base.age);). In this way members can be shared between subtypes and the type they inherit from- in constructors as well as elsewhere in the subclass.

 using System;  
 namespace Tutlane  
 {  
   // Base Class  
   public class Users  
   {  
     public string name = "Suresh Dasari";  
     public string location = "Hyderabad";  
     public int age = 32;  
     public virtual void GetInfo()  
     {  
       Console.WriteLine("Name: {0}", name);  
       Console.WriteLine("Location: {0}", location);  
     }  
   }  
   // Derived Class  
   public class Details : Users  
   {  
     public override void GetInfo()  
     {  
       base.GetInfo();  
       Console.WriteLine("Age: {0}", base.age);  
     }  
   }  
   class Program  
   {  
     static void Main(string[] args)  
     {  
       Details d = new Details();  
       d.GetInfo();  
       Console.WriteLine("\nPress Enter Key to Exit..");  
       Console.ReadLine();  
     }  
   }  
 }  


Result

Reference: https://www.tutlane.com/tutorial/csharp/csharp-base-keyword

ETL and EDI Using SSIS

ETL is the process by which you can take (Extract) data from various (usually related) data sources, Transform that data to meet your destination system's needs, and finally Load that transformed data into the destination system data store.

Your table structure will be something along the lines of this basic template:

In a real-world db environment Staging, OLAP, OLTP and other data repos may be on different database servers, this is same db server for demonstration

We will use SQL Server Integration Services (SSIS) and develop the SSIS package within the Visual Studio 2017 IDE.


The first step of the SSIS package load (INSERT) the data into a STAGING area database. This allows us to:
  • Store off the intermediate data from all sources into analysis-friendly OLAP datastores
  • Perform data integrity checks
  • Keep extraction and transformation as two strictly separated steps

We load the data from the various source files (.csv, .xls, .xlsx) into SQL Server database Staging table(s) using SSIS Source and Destination Data Flow Tasks connected with the movable data flow arrows. Once you have connected a source and destination you can go into the Destination Data Flow Task and edit the mappings of which source columns should be written to which destination columns.

Next we perform some transformations. This can be anything from a simple ranking or status/flagging/business prioritization algorithm to data cleansing to data partitioning based on certain criteria; the key is that this Transform step is where we apply T-SQL UPDATEs to transform the data once it has all been aggregated in Staging.

Then we refresh the OLAP destination tables using the same kind of Source and Destination Data Flow Tasks and mappings as used for Staging. The OLAP data is used for data analysis.

Finally, we load the cleansed Staging data into our destination system's OLTP database and email or text message the system owner upon successful completion of the SSIS ETL job (or deliver an error if anything fails). The OLTP data stores live transactions.

Bear in mind that most ETL data-flow step mappings are not a 1:1 match; this is just an e2e demo of SSIS ETL in most basic form


Happy ETL'ing, and be sure to watch out for cases of mysterious symbols/characters from miscellaneous data copied from other programs or from other system environments that were using a Language setting (codepage) which is incompatible with your ETL software. Bad data happens more than you think and as we say, GIGO.

Your end result looks like this (all Green Checkmarks indicates all was successful; I recommend using PaperCut for SMTP testing- super cool and useful product

I would attach or GitHub the source code (and will do so upon request) but SSIS project code has a lot of dependencies and can get quite messy for another to re-use project on even just a 'slightly different' machine.

Having used SSIS' now-deprecated predecessor "DTS" (SQL Server Data Transformation Services) and SSIS for many years I can attest to the fact that the best way to learn this product is by diving right in on your own and begin the creation of sources and destinations and source/destination connection managers and control flow events, and .NET integration, and exception event handlers, etc.

You will likely run into some ambiguous and not well-documented errors when developing in SSIS; but persist in your efforts and you can create a very powerful EDI system with the many capabilities of SSIS and the robust Scheduled ETL jobs that it can create.

Java from .NET and .NET from Java

Java or .NET? Why not both (when it is the only viable path)?

jni4net is a proven interop library for Java and .NET. Two brief examples developed by jni4net below merely require that you to specify the jni4net dependency in the (Visual Studio or Eclipse) project.

Calling Java from .NET
 using java.io;  
 using java.lang;  
 using java.util;  
 using net.sf.jni4net;  
 using net.sf.jni4net.adaptors;  
 namespace helloWorldFromCLR  
 {  
   public class Program  
   {  
     private static void Main()  
     {  
       // create bridge, with default setup  
       // it will lookup jni4net.j.jar next to jni4net.n.dll  
       Bridge.CreateJVM(new BridgeSetup(){Verbose=true});  
       // here you go!  
       java.lang.System.@out.println("Hello Java world!");  
       // OK, simple hello is boring, let's play with Java properties  
       // they are Hashtable realy  
       Properties javaSystemProperties = java.lang.System.getProperties();  
       // let's enumerate all keys.   
       // We use Adapt helper to convert enumeration from java o .NET  
       foreach (java.lang.String key in Adapt.Enumeration(javaSystemProperties.keys()))  
       {  
         java.lang.System.@out.print(key);  
         // this is automatic conversion of CLR string to java.lang.String  
         java.lang.System.@out.print(" : ");  
         // we use the hashtable  
         Object value = javaSystemProperties.get(key);  
         // and this is CLR ToString() redirected to Java toString() method  
         string valueToString = value.ToString();  
         java.lang.System.@out.println(valueToString);  
       }  
       // Java output is really Stream  
       PrintStream stream = java.lang.System.@out;  
       // it implements java.io.Flushable interface  
       Flushable flushable = stream;  
       flushable.flush();  
     }  
   }  
 }  


Calling .NET from Java
 import net.sf.jni4net.Bridge;  
 import java.io.IOException;  
 import java.lang.String;  
 import system.*;  
 import system.Object;  
 import system.io.TextWriter;  
 import system.collections.IDictionary;  
 import system.collections.IEnumerator;  

 public class Program {  
      public static void main(String[] args) throws IOException {  
           // create bridge, with default setup  
           // it will lookup jni4net.n.dll next to jni4net.j.jar   
           Bridge.setVerbose(true);  
           Bridge.init();  
           // here you go!  
           Console.WriteLine("Hello .NET world!\n");  
           // OK, simple hello is boring, let's play with System.Environment  
           // they are Hashtable realy  
           final IDictionary variables = system.Environment.GetEnvironmentVariables();  
           // let's enumerate all keys  
           final IEnumerator keys = variables.getKeys().GetEnumerator();  
           while (keys.MoveNext()) {  
                // there hash table is not generic and returns system.Object  
                // but we know is should be system.String, so we could cast  
                final system.String key = (system.String) keys.getCurrent();  
                Console.Write(key);  
                // this is automatic conversion of JVM string to system.String  
                Console.Write(" : ");  
                // we use the hashtable  
                Object value = variables.getItem(key);  
                // and this is JVM toString() redirected to CLR ToString() method  
                String valueToString = value.toString();  
                Console.WriteLine(valueToString);  
           }  
           // Console output is really TextWriter on stream  
           final TextWriter writer = Console.getOut();  
           writer.Flush();  
      }  
 }  
(verbose commenting by Pavel Savara, a jni4net contributor)

References:

http://zamboch.blogspot.com/2009/10/how-calling-from-net-to-java-works.html

http://zamboch.blogspot.com/2009/11/how-calling-from-java-to-net-works-in.html

https://github.com/jni4net/jni4net/tree/master/content/samples

SQL CLR for .NET in SQL Server

You may find yourself with the need to integrate a .NET method within SQL Server to be called as a function. This usually happens when some relatively complex looping and modifying logic is a requirement of a SQL operation.

SQL is a great data language but it is not the right language for some tasks. Creating a SQL CLR from a .NET assembly may be the best approach to some unique situations (and there is a bonus in that, in many cases you can reuse existing .NET code).

Before creating the CLR object we need a .NET .dll; so first we create a basic .NET assembly compile in Release and copy the path the the compiled .dll:

This is our simple .NET CLR method with which we want to run within the SQL Server query execution engine


SQL CLR provides a way for you to integrate complex .NET methods within SQL Server


Import into SQL Server instance via SSMS*: 


Select New Assembly... 




...and then enter the path to your Release .dll


Create T-SQL function or stored procedure to serve as caller for the function and run it:

From here we can see all of the T-SQL code involved; the 3 SQL Server configuration conditions (shown in the 3 EXEC statements) are required

And that is all there is to it. Only use CLR functions when absolutely necessary as RDBMS's like SQL Server are designed to processes relational data in sets, and not to apply complex business logic on individual rows.

But if there is no other way- SQL CLRs could provide you a solution to your code/logic integration problems.


*Warning and Reference: https://blog.netspi.com/attacking-sql-server-clr-assemblies/

Calling Win32 API from .NET C# Application

As quoted in the useful reference below:
"Anybody doing any serious Windows development in C# will probably end up calling many Win32 functions. The .NET framework just doesn't cover 100% of the Win32 API." Mike Thompson

This illustrative example here is simply to show what the integrated code looks like; however identifying available drive space is a common app requirement

The interop of .NET and Win32API works by way of referencing the InteropServices .NET namespace and using normal Win32 API functions with the [DllImport()] attribute denoting the Win32 API assembly being used and the corresponding function being modified as "static extern" which informs the compiler that the function is calling unmanaged (non-.NET) code.

 using System;  
 using System.Runtime.InteropServices;  
 namespace ConsoleApp1  
 {  
   internal static class Win32  
   {  
     [DllImport("kernel32.dll", CharSet = CharSet.Auto, SetLastError = true)]  
     internal static extern bool GetDiskFreeSpaceEx(string drive, out long freeBytesForUser, out long totalBytes, out long freeBytes);  
   }  
   class Program  
   {  
     static void Main(string[] args)  
     {  
       long freeBytesForUser;  
       long totalBytes;  
       long freeBytes;  
       Console.WriteLine("Free space in this directory:");  
       if (Win32.GetDiskFreeSpaceEx(@"C:\", out freeBytesForUser, out totalBytes, out freeBytes))  
       {  
         Console.WriteLine("Free user bytes: " + freeBytesForUser.ToString());  
         Console.WriteLine("Free total bytes: " + totalBytes.ToString());  
         Console.WriteLine("Free bytes: " + freeBytes.ToString());  
       }  
       Console.ReadLine();  
     }  
   }  
 }  

Reference: https://stackoverflow.com/questions/137255/how-can-i-determine-if-a-remote-drive-has-enough-space-to-write-a-file-using-c

TwickrTape

Real-time scrolling Tweets related to financial news alerts and updates. Just enter the Twitter handle you want to see a marquee of live streaming tweets from.

TL;DR - the working app is here: https://twickrtape.azurewebsites.net
..znd here on Google Play Store: https://play.google.com/store/apps/details?id=io.cordova.twicktape

Use button in lower-left to toggle different Twitter handles

The code is a hodgepodge of various references and vestiges of past personal projects. My initial aim was to get something working end-to-end as I envisioned, and I was able to achieve that pretty easily through the Twitter API and .NET.

The process is standard API stuff- first you authenticate and receive a token which you can then subsequently pass to prove that your requests are valid. Then, using simple (REST API) HTTP GET, we get the content (Twitter Timeline information) that we are interested in.

The gist of the code is .NET C# (an ASP.NET Controller method) and listed below:

  public ActionResult GetTwickr(string handle = "business")  
     {  
       // Set your own keys and screen name  
       var oAuthConsumerKey = "XXXXXXXXXXXXX"; // "API key";  
       var oAuthConsumerSecret = "XXXXXXXXXXXXXXXXXXXXX"; // "API secret key";  
       var oAuthUrl = "https://api.twitter.com/oauth2/token";  
       var screenname = "@" + handle; // default Twitter display current status  
       // Authenticate  
       var authHeaderFormat = "Basic {0}";  
       var authHeader = string.Format(authHeaderFormat,  
         Convert.ToBase64String(Encoding.UTF8.GetBytes(Uri.EscapeDataString(oAuthConsumerKey) + ":" +  
         Uri.EscapeDataString((oAuthConsumerSecret)))  
       ));  
       var postBody = "grant_type=client_credentials";  
       HttpWebRequest authRequest = (HttpWebRequest)WebRequest.Create(oAuthUrl);  
       authRequest.Headers.Add("Authorization", authHeader);  
       authRequest.Method = "POST";  
       authRequest.ContentType = "application/x-www-form-urlencoded;charset=UTF-8";  
       authRequest.AutomaticDecompression = DecompressionMethods.GZip | DecompressionMethods.Deflate;  
       using (Stream stream = authRequest.GetRequestStream())  
       {  
         byte[] content = ASCIIEncoding.ASCII.GetBytes(postBody);  
         stream.Write(content, 0, content.Length);  
       }  
       authRequest.Headers.Add("Accept-Encoding", "gzip");  
       WebResponse authResponse = authRequest.GetResponse();  
       // deserialize into an object  
       TwitAuthenticateResponse twitAuthResponse;  
       using (authResponse)  
       {  
         using (var reader = new StreamReader(authResponse.GetResponseStream()))  
         {  
           System.Web.Script.Serialization.JavaScriptSerializer js = new System.Web.Script.Serialization.JavaScriptSerializer();  
           var objectText = reader.ReadToEnd();  
           twitAuthResponse = JsonConvert.DeserializeObject<TwitAuthenticateResponse>(objectText);  
         }  
       }  
       try  
       {  
         // Get timeline info  
         var timelineFormat = "https://api.twitter.com/1.1/statuses/user_timeline.json?screen_name={0}&include_rts=1&exclude_replies=1&count=5";  
         var timelineUrl = string.Format(timelineFormat, screenname);  
         HttpWebRequest timeLineRequest = (HttpWebRequest)WebRequest.Create(timelineUrl);  
         var timelineHeaderFormat = "{0} {1}";  
         timeLineRequest.Headers.Add("Authorization", string.Format(timelineHeaderFormat, twitAuthResponse.token_type, twitAuthResponse.access_token));  
         timeLineRequest.Method = "GET";  
         WebResponse timeLineResponse = timeLineRequest.GetResponse();  
         var timeLineJson = string.Empty;  
         string scrolltxt = string.Empty;  
         using (timeLineResponse)  
         {  
           using (var reader = new StreamReader(timeLineResponse.GetResponseStream()))  
           {  
             timeLineJson = reader.ReadToEnd();  
           }  
         }  
         // deserialize into an object  
         dynamic obj = JsonConvert.DeserializeObject(timeLineJson);  
         foreach (var r in obj.Root)  
         {  
           scrolltxt += " ***** " + r.text;  
         }  
         var model = new LoggedInUserTimelineViewModel { TimelineContent = scrolltxt, Handle = handle };  
         return View("TwickrMain", model);  
       }  
       catch (Exception e) { }  
       return View("TwickrMain");  
     }  

Next, using Newtonsoft,Json JsonConvert() function, we deserialize the JSON response and zero in on the 'text' property of each entity (tweet) in the array of JSON results (tweets).

And finally, using some basic Bootstrap HTML, JavaScript and CSS we are able to wire up a very simple, but effective app that interacts with the Twitter API in real-time.

Reference: https://developer.twitter.com/en/docs/tweets/timelines/api-reference/get-statuses-user_timeline.html

SSRS Extension Methods - The 3 Levels

When developing SSRS reports, if you are unable to achieve what you want through SQL and RDL alone, there are essentially 3 ways you can add custom functions/functionality to SSRS.

1). Inline SSRS "Expressions". (VBScript code)

2). Report.Code references (VB code)


3). Custom Assembly references (C# or VB .NET .dll)


The following is a brief example of each.

1). Inline SSRS Expressions
In SSRS, virtually every report property can be dynamic. The way to set the dynamic behavior of any report item is through SSRS Expressions which are simply VBScript code blocks that evaluate some logic against the report data and return the property value at runtime.

For instance, here we are setting the Font >> FontWeight property of the Order Date column for an Orders report (AdventureWorks sample data). If the Date is more than 1 year in the past, this inline expression sets the FontWeight of the OrderDate field to bold:

Method 1 Code

Method 1 Result

You can use IIF(), Choose(), Switch() or any valid VBScript syntax. The report field values are referenced as Fields!OrderDate.Value. As in the example above, you will oftentimes need to cast the report dataset data to the data type which you are evaluating (a cast to CDate in this example as we are evaluating a Date).

2). Report Code references (VB code)
This method is similar to Method #1, but it allows you to define a VB Code space where you can define functions in a central place and reference them any number of times throughout your report.

For this example, let's say we need to have a report function that takes a string as a parameter and then evaluates the length of that string. If the string value is over 10 characters long, it sets the Color to Red, if not, the color is the default Black. Your Report Code methods are defined in: Report >> Report Properties >> Code:

Method 2 Code

Method 2 Reference

Method 2 Result


3). Custom Assembly references (C# or VB .NET .dll)
This is the most comprehensive of the 3 methods and allows you to do anything that you could normally do with a .NET assembly.

For illustrative purposes, let's say we have a need to interface with a backend system and get a URI which will fill a report image item with the appropriate image from an external server that is not connected to the Report Server. You can implement the necessary .NET logic and API calls, etc. in your custom assembly and then reference that .dll in your report:

 namespace SSRSCustomAssembly  
 {  
   public class ErpReferenceLib  
   {  
     public static string GetImageForCustomerAccount(int customerID)  
     {  
       //this example is static; in real-world implementation the customerID might fetch value from db or API  
       bool isActiveAndValid = CheckCustomerIDIsValidAndActive(customerID);  
       if (isActiveAndValid)  
       {  
         return "https://abccorp.imagesrv?customerID=" + customerID + ".svg";  
       }  
       else  
       {  
         return "https://abccorp.imagesrv?inactive.svg";  
       }  
     }  
     public static bool CheckCustomerIDIsValidAndActive(int customerID)  
     {  
       return customerID >= 100 ? true : false;  
     }  
   }  
 }  
Method 3 Code

Method 3 Report Reference


Method 3 Report Item Reference- NOTE: You cannot use namespaces separated with ".", ie: MyNamespace.Subnamespace


Method 3 Visual Studio Report Design View

Method 3 Result in VS2017 Preview Mode

Method 3 Result on local Report Server

For more information (esp. how to do the necessary custom assembly security permission config changes, for creating object instances vs. static methods, etc.), see the relevant references below.

Note(!): your custom assembly must have permission to run within Visual Studio in order to Preview a report that calls a custom assembly method and it must also have permission within Report Server. You can grant it FullTrust or whatever CAS group you deem is appropriate. The paths to deploy the .dll are as follows (may vary by SQL Server and Visual Studio version- this is for SQL 2017 and VS2017):

Visual Studio Report Designing/Preview assemblies*: C:\Program Files (x86)\Microsoft Visual Studio\2017\Community\Common7\IDE\CommonExtensions\Microsoft\SSRS

Report Server assemblies*: C:\Program Files\Microsoft SQL Server Reporting Services\SSRS\ReportServer\bin

GitHub for SSRS Project: https://github.com/Radagast27/CustomSSRS

GitHub for SSRS Custom Assembly: https://github.com/Radagast27/CustomAssembly


*https://www.mssqltips.com/sqlservertip/3224/sql-server-reporting-services-custom-code-assemblies/

Serialization and Deserialization in .NET

Definition: "Serialization is the process of going from in-memory representation of an object to a disk-based format which can be in any form like binary, JSON, BSON, XML, etc. Deserialization is the reverse process, in which you recreate an object from disk storage and store it in RAM"

Purpose: "This is mainly used in document store, ORM style databases, storing config files etc. The main benefit is the speed in populating an object as opposed to querying multiple tables in the case of database storage."

Serialization saves object state so that the object can be persisted and recreated without the need for inserting/updating one or more records across several tables in an RDBMS.

.NET Example: The following is an example of serialization of a simple C# object to XML, and deserialization from the XML back into the same C# object:

 using System;  
 using System.IO;  
 using System.Xml;  
 using System.Xml.Serialization;  
 namespace SerializationDeserializationSimpl  
 {  
   class Program  
   {  
     static void Main(string[] args)  
     {  
       Album album = new Album(){ AlbumId = 1, AlbumName = "James Vincent McMorrow", ArtistName="Post Tropical", CurrentBillboardRank=1045, HighestBillboardRank=102, SoundScanUnits=207000};  
       Console.WriteLine("Original object in memory:");  
       album.WriteSummary();  
       Console.WriteLine("Press Enter to Serialize this object to XML");  
       Console.ReadLine();  
       //Serialize it to XML file (disk) form  
       var serializer = new XmlSerializer(album.GetType());  
       using (var writer = XmlWriter.Create("album.xml"))  
       {  
         serializer.Serialize(writer, album); //serializes to XmlWriter for later deserialization back into Album obj  
         var sWriter = new StringWriter();  
         serializer.Serialize(sWriter, album); //serializes to StringWriter for display in the Console via WriteLine  
         Console.WriteLine(sWriter.ToString());  
         Console.WriteLine("------------------------------------");  
         Console.WriteLine("------------------------------------");  
         Console.WriteLine("------------------------------------");  
       }  
       Console.WriteLine("Serialization to XML sucessfull!");  
       Console.WriteLine("------------------------------------");  
       album.WriteSummary();  
       Console.WriteLine("------------------------------------");  
       Console.WriteLine("Press Enter to Deserialize from the XML");  
       Console.ReadLine();  
       //Deserialize from that XML back into object (RAM) form  
       using (var reader = XmlReader.Create("album.xml"))  
       {  
         var albumDeserialized = (Album)serializer.Deserialize(reader);  
         Console.WriteLine("Deserialization from XML sucessfull!");  
         Console.WriteLine("------------------------------------");  
         albumDeserialized.WriteSummary();  
         Console.WriteLine("------------------------------------");  
         Console.WriteLine("Press any key to exit");  
         Console.ReadLine();  
       }  
     }  
   }  
   public class Album  
   {  
     public int AlbumId;  
     public string AlbumName;  
     public string ArtistName;  
     public int SoundScanUnits;  
     public int CurrentBillboardRank;  
     public int HighestBillboardRank;  
     public void WriteSummary()  
     {  
       Console.WriteLine("AlbumId: " + AlbumId);  
       Console.WriteLine("Album: " + AlbumName);  
       Console.WriteLine("Artist: " + ArtistName);  
       Console.WriteLine("SoundScanUnits: " + SoundScanUnits);  
       Console.WriteLine("CurrentBillboardRank: " + CurrentBillboardRank);  
       Console.WriteLine("HighestBillboardRank: " + HighestBillboardRank);  
     }  
   }  
 }  
Program.cs

Console Output

GitHub: https://github.com/Radagast27/SerializationDeserializationSimpl

Quote Attribution: Mehdi Gholam

References:

https://stackoverflow.com/questions/11447529/convert-an-object-to-an-xml-string?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa

https://www.codeproject.com/Questions/277995/What-is-serialization-and-deserialization-in-cshar

https://stackoverflow.com/questions/3356976/how-to-serialize-deserialize-simple-classes-to-xml-and-back

T-SQL and .NET for XSLT

Steps to perform transformation from SQL to XML to XSLT Output data (csv, htm, tab-delim, fixed-width, etc.):

1). Get your SQL data (assuming you are using SQL Server) into XML format via:

 SELECT [OrderId], [PersonId], [OrderDateTime] FROM [Order] FOR XML AUTO, ELEMENTS, ROOT ('Orders');  


XML Result of "FOR XML AUTO, ELEMENTS, ROOT"..

2). Load XSL transform file (OrderXform.xsl) into a new XslCompiledTransform object (transform):

       XslCompiledTransform transform = new XslCompiledTransform();  
       transform.Load("C:\\Xform\\OrderXform.xsl");  


XSL file used to transform the XML to the desired file output (EDI 834 standard, etc)


3). Using a StringWriter (writer), StringReader (inputReader) and XmlReader (inputXmlReader), read the inputReader XML string into inputXmlReader and apply the XSL transformation to the transform object via: transform.Transform(inputXmlReader, args, writer); //(or another override of Transform())

  using (StringWriter writer = new StringWriter())   
     {   
      // Make an XML reader (inputReader) out of the string (input)  
      XmlReader inputXmlReader;   
      using (var inputReader = new StringReader(input))   
      {   
       inputXmlReader = XmlReader.Create(inputReader);   
       // Apply the transformation to XmlReader (inputXmlReader) and write it to StringWriter (writer)   
       transform.Transform(inputXmlReader, null, writer);   
      }      
      // Retrieve the output as string from writer object  
      return writer.GetStringBuilder().ToString();   
     }   

4). Return the result and write the bits (to memory as Label Text for a demo like this, to file, to domain network location, to non-domain customer ftp, etc.).

The result normally gets written to remote SFTP location or posted to API endpoint for processing

Nothing incredibly complex about this, but it seems to frustrate a lot of people in .NET land so I have documented a quick and easy example.

GitHub Source: https://github.com/Radagast27/Simpl_XSLT_dot_net

Tool for XSLT validation: https://www.freeformatter.com/xsl-transformer.html

References:

https://www.red-gate.com/simple-talk/sql/learn-sql-server/using-the-for-xml-clause-to-return-query-results-as-xml/

https://www.w3schools.com/xml/xsl_examples.asp

https://www.c-sharpcorner.com/article/transform-xml-output-of-sql-query-using-for-xml-auto-statement-to-html-using-xsl/

SQL, XML, and XSLT with SSIS

A common need in any business is the exchange of data through various file formats. EDI (Electronic Data Interchange) is typically defined in a document that outlines the positions, symbols, and range of acceptable values for a given file standard (834, 872, etc.).

Example of an 834 EDI formatted file

In this exercise, we are going to very quickly turn a SQL dataset result into an XML file and apply an XSL transformation to it- all through SSIS. Using the techniques described below you will be able to create a process to transform data and write data files for any EDI standard with relative ease.

One can add .NET and other customizable code via SSIS "Execute Process" and "Script Task", however in this exercise we are going to stick to the core problem of fetching data from a SQL Server, transforming that data and then writing the result to an output/destination file.

The SSIS package design

To start, create an Integration Services project in Visual Studio 2017 (install SQL Server Data Tools if your machine does not have the Integration Services Visual Studio project template).

Our project will consist of a Data Flow Task to gather our data (SQL Server to a text file containing the results as XML), and an XML Task which will apply an XSL transformation (XSLT) and write the file to disk.

Data Flow Step #1 -  Source


Data Flow Step #1 - Destination

In my example here, we are using ADO.NET to connect to SQL Server for source data as XML and then writing that data to result.txt.

result.txt (formatted to more clearly see the Customer XML elements

Next, the SSIS XML Task takes result.txt and applies an XSL stylesheet transformation to it, thereby changing the data from XML to HTML with dynamically added CustomerID values (the integer after the static text, "***CUST**ID**").

The key to the particular (CustomerID) transformation we are doing here is the following code which selects each <Customer> in the XML and writes the <CustomerID> value after "CUST**ID**".

<xsl:for-each select="Root/Customer">
 <tr>
  <td>NM1*IL*1***CUST**ID**<xsl:value-of select="CustomerID"/>****MI*YYX123456789!</td>
 </tr>
</xsl:for-each>

XML Task

XSLT is the operation, result.txt is our source data, finally.htm our destination and the XSL is Direct Input shown in the highlighted text above.

You can easily send mail on success or failure to keep the appropriate personnel informed of ETL job/service status. You can use a connected SMTP server to send mail via the SSIS Send Mail Task or (as I have in this example) you can use this T-SQL for sending mail as an Execute SQL Task:

T-SQL for sending email in Execute SQL Task

If you need to create a SQL Server Database Mail account/profile for Gmail, etc, follow this walkthrough.

And with a little luck and no missed steps, you will execute the SSIS package successfully and can go on to really leverage the powerful capabilities of SSIS to build simple and effective solutions for data interchange.

The completed SSIS package and the .htm result of our ETL exercise




Patterns Simpl: Facade

Simplified interface to the overall functionality of a complex subsystem.

For example, a mortgage application is the facade to the subsystem of more complex entities: credit, bank and loan. The .NET Framework is an interface to the more complicated subsystems that lie beneath System.IO, System.Data, etc.



Reference: http://www.dofactory.com/net/facade-design-pattern

Patterns Simpl: Singleton

It is imperative to ensure atomicity (indivisible-ness) for certain "master" objects in your applications.

Singleton ensures the existence of 1 and only 1 object instance (or version/representation) of an application entity- at all times.


Some examples of Singleton:

If your program somehow winds up with 2 globally-scoped objects of a class that handles bank deposits, let's hope our account gets updated by the one with a larger dollar amount!

In Java, there is a single instance of java.lang.Runtime. If there were more than 1 language instance to interface with the Java application environment, you can see how problems would unfold.

In .NET applications, an application .config file serves as a single source of configuration values.

Log Managers should have 1 and only 1 instance, lest our log files become far too complex to sift through when we are tracking down an application issue.




Reference: https://stackoverflow.com/questions/3192095/where-exactly-the-singleton-pattern-is-used-in-real-application

C# switch case, for/foreach loops and enum

Often in the earlier years of my software development career (esp. after working solely on SQL code for a long stretch of time), I would forget the exact syntax of some commonly used C# logic routines.

I've long since memorized the exact syntax for all of these, but I'd like to help someone else out there who may need a reminder in the future.

So here is a one-stop handy reference for these 3 commonly used C# constructs:

using System;
// enum
public enum Color { Red, Green, Blue }

public class Example
{   // switch
   public static void Main()
   {
      Color c = (Color) (new Random()).Next(0, 3);
      switch (c)
      {
         case Color.Red:
            Console.WriteLine("The color is red");
            break;
         case Color.Green:
            Console.WriteLine("The color is green");
            break;
         case Color.Blue:
            Console.WriteLine("The color is blue");   
            break;
         default:
            Console.WriteLine("The color is unknown.");
            break;   
      }
   }
}

// for loop
for (int i = 1; i <= 5; i++)
{
 Console.WriteLine(i);
}

 // foreach loop
int[] fibarray = new int[] { 0, 1, 1, 2, 3, 5, 8, 13 };
foreach (int element in fibarray)
{
    System.Console.WriteLine(element);
}

Sql Connection String for .NET


Standard Security:

"Server=myServerAddress;Database=myDataBase;User Id=myUsername;
Password=myPassword;"


References:

(on unit vs. integration tests) So maybe this is the distinction I find useful. There are tests where a failure points directly to the problem.
These tests are tests either of well-understood abstractions or using only well-understood abstractions.
Then there are tests where a failure indicates a genuine problem, but where oh where? - Kent Beck