Common Table Expression in SQL (with multiple joined CTEs) 

CTE ( common table expression ) can be taken simply as temporary dataset within the query.
It is similar to temporary table but it is not stored as an object like temporary table and is alive
until the query execution.

You can use CTE like a normal table. Complicated joins can be simplified using CTE. Below you can find examples of simple CTE and CTE with joins. These techniques can be used as per your need to get the result data.

Simple CTE

;with cte1 as (

select

       field1,

       field2,

       field3

from dbo.table1

)

select * from cte1

Multiple CTEs with joins 

;with cte1 as (

select 

field1,

field2,

field3

from dbo.table1

), cte2 as (

select 

field4,

field5

from dbo.table2

)

select 

_cte1.*,

_cte2.* 

from cte1 _cte1

inner join cte2 _cte2 on _cte2.field4 = _cte1.field1


For detailed information click the link below :
 https://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx

Reporting Services (SSRS Expression) : Get number of rows in dataset

The following ssrs expression will return you the total number of rows in the defined dataset.

=CountRows("MyDataSetName")

For detailed info checkout the linke below :

https://technet.microsoft.com/en-US/library/ms156330(v=sql.100).aspx

Get end of the month in mssql (2008 R2 / 2012 )

This is a simple workout to find the end day of the month for selected date in SQL server. For SQL server 2012, we have a built-in function to perform this task but for 2008, we need to write the logic manually. Here are the sql query samples to find out the end day of the selected month in mssql.

let's assume @dt is your declared DateTime

DECLARE @dt DATETIME = GETDATE()

In SQL 2012, we have an inbuilt function which will give directly the end of the month.

SELECT DAY(

EOMONTH(

@dt   -------- date to get end of the month

,0   -------- months to add ( optional )

)

)

-- In Short :

SELECT DAY(EOMONTH(@dt,0))

But in sql 2008 R2, we do not have this function, hence we need a workout. 

SELECT DAY(   --------------------------------------- returns the number of days of that month

DATEADD(d,-1, --------------------------- subtracts one day from the diff date

DATEADD( ------------------------ adds 1 month to the difference month: return type is DateTime                    

m

,DATEDIFF(M,0,@dt)+1  --- gets difference in months from the very beginning 

,0

)

)

   ) 

           

-- In Short :

SELECT DAY(DATEADD(d,-1,DATEADD(m,DATEDIFF(M,0,@dt)+1,0)))

For end of month in javascript check the link: http://iamfixed.blogspot.de/2017/12/get-end-day-of-month-jquery-javascript.html

Exit application programmatically (c#/.NET) 

// Terminate process and return the exit code to the operating system. 

//0  indicates that process has completed successfully

                    Environment.Exit(0);

Run code as different user in c#

In our application, some tasks might need to be run as different user or as administrator. Below you can find a simple and effective solution for this purpose.

Simple Impersonation Library is a library which lets you to impersonate any user with its login credentials. From NuGet package, you can get this library.

Then you can implement your code inside a section as below:

using (SimpleImpersonation.Impersonation.LogonUser("domainname", "Username", "password", SimpleImpersonation.LogonType))

{

// Your running code as this user

}

Create/Save/Write/Append text in a txt file (c# / .NET)

Many requirements will come in application development where we have to create or update a text file for certain purpose. For example that may be a log file or any accumulative text files.

Here is a function which implements the code to create/save/write/append text in a text file. You can further modify the function or create a new one as per your need. You can pass texts and filepath as arguments and modify internal code based on your requirement.

       void MyFunction()

        {

            // path to your txt file

            string filePath = @"D:\myFile.txt";

            // if file does not exist, it will create a new file and dispose the resource used by stream

            if (!File.Exists(filePath))

            {

                File.Create(filePath).Dispose();

            }

            // instanciate a stream writer for the txt file.

            using (StreamWriter writer = File.AppendText(filePath))

            {

                // writes the line to the file and inserts a line break

                writer.WriteLine("I will insert a line break.");

                // writes the string to the stream

                writer.Write("I am written in a stream.");

                writer.Write("I am continued.");

                // NOTE : you can also write it asynchronously. check the functions in writer.

            }

        }

XML to Object and vice versa in c# using DataContractSerializer

From conversion of XML to object and vice versa, we already have helper classes in .net. However, on this page, I have tried to implement a generic helper class for these operations. There are two functions in the helper which converts an xml string to class object and another function for converting class object to XML string.
To use DataContractSerializer class, you need to add reference System.Runtime.Serialization in your project.

Helper class:

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Runtime.Serialization;
using System.Text;
using System.Threading.Tasks;
using System.Xml;

namespace ConsoleApp2
{
    /// <summary>
    /// Helper class for xml parsing
    /// </summary>
    /// <typeparam name="T">class object to/to be parsed</typeparam>
    public class DataContractSerializationHelper<T>
    {
        /// <summary>
        /// Converts xml string to Object
        /// </summary>
        /// <param name="xmlText"></param>
        /// <returns></returns>
        public  T Xml_To_Object(string xmlText)
        {

            T returnObject;
            XmlDocument doc = new XmlDocument();
            doc.LoadXml(xmlText);

            using (StringReader stringReader = new StringReader(doc.OuterXml))
            {
                using (XmlTextReader xmlReader = new XmlTextReader(stringReader))
                {
                    DataContractSerializer serializer = new DataContractSerializer(typeof(T));
                    returnObject = (T)serializer.ReadObject(xmlReader);
                }

            }

            return returnObject;
        }

        /// <summary>
        /// Converts object to xmldocument
        /// </summary>
        /// <param name="myObject"></param>
        /// <returns></returns>
        public XmlDocument Object_To_XML(T myObject)
        {

            XmlDocument xmlDocument = null;

            using (StringWriter stringWriter = new StringWriter())
            {
                using (XmlTextWriter xmlTextWriter = new XmlTextWriter(stringWriter))
                {
                    DataContractSerializer serializer = new DataContractSerializer(typeof(T));
                    serializer.WriteObject(xmlTextWriter, myObject);
                    xmlDocument = new XmlDocument();
                    xmlDocument.InnerXml = stringWriter.GetStringBuilder().ToString();
                }
            }

            return xmlDocument;
        }
    }   
}

Sample class ( to be passed as generic):

    public class testclass
    {
        public int id { get; set; }
        public string name { get; set; }
    }

Implementation of above helper class ( assuming the implementation done in console app) :

using System;
using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

namespace ConsoleApp2

{

    class Program

    {

        static void Main(string[] args)

        {

            DataContractSerializationHelper<testclass> helper = new                           DataContractSerializationHelper<testclass>();

            // conversion from object to xmldocument

            testclass obj = new testclass ();

            obj.id = 1;

            obj.name = "hello world";

            var xmldoc = helper.Object_To_XML(obj);

            // conversion from xmlstring to object
//note : in case you get namespace error replace 'ConsoleApp2' of testxml with the namespace of the class

            string testxml = "<testclass xmlns:i=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns=\"http://schemas.datacontract.org/2004/07/ConsoleApp2\"><id>1</id><name>hello world</name></testclass>";

            var testclassObject = helper.Xml_To_Object(testxml);

        }

    }

}

Ref:
https://docs.microsoft.com/en-us/dotnet/api/system.runtime.serialization.datacontractserializer?view=netframework-4.7.1

Note: If you need to have a function which will directly use the XML file URL or you need to export the class object to an XML file and save it in a location, you can give it a try it. Or leave a comment and I will update the post with other functions as well.