MySQL: Split comma separated list into multiple rows

    In MySQL this can be achieved as below  
 
    SELECT id, length FROM vehicles WHERE id IN ( 117, 148, 126)

    +---------------+
    | id  | length  |
    +---------------+
    | 117 | 25        |
    | 126 | 8        |
    | 148 | 10        |
    +---------------+

    SELECT id,vehicle_ids FROM load_plan_configs WHERE load_plan_configs.id =42

    +---------------------+
    | id  | vehicle_ids   |
    +---------------------+
    | 42  | 117, 148, 126 |
    +---------------------+

    Now to get the length of comma separated vehicle_ids use below query

    Output

    SELECT length FROM vehicles, load_plan_configs   WHERE load_plan_configs.id = 42 AND FIND_IN_SET(vehicles.id, load_plan_configs.vehicle_ids)
   
    +---------+
    | length  |
    +---------+
    | 25        |
    | 8          |
    | 10       |
    +---------+

Macro to unprotect excel sheet

sub MyMacro()
                        Dim i As Integer, j As Integer, k As Integer
                Dim l As Integer, m As Integer, n As Integer
                Dim i1 As Integer, i2 As Integer, i3 As Integer
               Dim i4 As Integer, i5 As Integer, i6 As Integer
               On Error Resume Next
                For i = 65 To 66 : For j = 65 To 66 : For k = 65 To 66
             For l = 65 To 66 : For m = 65 To 66 : For i1 = 65 To 66
             For i2 = 65 To 66 : For i3 = 65 To 66 : For i4 = 65 To 66
             For i5 = 65 To 66 : For i6 = 65 To 66 : For n = 32 To 126
             ActiveSheet.Unprotect(Chr(i) & Chr(j) & Chr(k) & _
                Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _
                 Chr(i4) & Chr(i5) & Chr(i6) & Chr(n))
             If ActiveSheet.ProtectContents = False Then
                Exit Sub
             End If
                                Next : Next : Next : Next : Next : Next
                                 Next : Next : Next : Next : Next : Next
                end sub

----------------------

Private Sub UnprotectSheet()
        'Breaks worksheet password protection.
        Dim strPassword As String = Configuration.ConfigurationManager.AppSettings("ProtectPassword").ToString()
        Dim intCtr As Integer = 0
        Dim ObjExcel As New Excel.Application
        Dim objWbook As Excel.Workbook
        For intCtr = 0 To lstFiles.Items.Count - 1
            ObjExcel.Workbooks.Add(txtInputFolder.Text & "\" & lstFiles.Items(intCtr))
        Next
        Dim wsHeet As Excel.Worksheet
        ObjExcel.DisplayAlerts = False
        intCtr = 0
        For Each objWbook In ObjExcel.Workbooks
            Dim xlmodule As Object 'VBComponent
            xlmodule = objWbook.VBProject.VBComponents.Add(1) 'vbext_ct_StdModule
            Dim strCode As String
            strCode = _
               "sub MyMacro()" & vbCr & _
               "         Dim i As Integer, j As Integer, k As Integer " & vbNewLine & _
               " Dim l As Integer, m As Integer, n As Integer " & vbNewLine & _
                "Dim i1 As Integer, i2 As Integer, i3 As Integer " & vbNewLine & _
              " Dim i4 As Integer, i5 As Integer, i6 As Integer " & vbNewLine & _
              " On Error Resume Next " & vbNewLine & _
                "For i = 65 To 66 : For j = 65 To 66 : For k = 65 To 66 " & vbNewLine & _
            " For l = 65 To 66 : For m = 65 To 66 : For i1 = 65 To 66 " & vbNewLine & _
            " For i2 = 65 To 66 : For i3 = 65 To 66 : For i4 = 65 To 66 " & vbNewLine & _
            " For i5 = 65 To 66 : For i6 = 65 To 66 : For n = 32 To 126 " & vbNewLine & _
            " ActiveSheet.Unprotect(Chr(i) & Chr(j) & Chr(k) & _" & vbNewLine & _
            "    Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _ " & vbNewLine & _
                " Chr(i4) & Chr(i5) & Chr(i6) & Chr(n))" & vbNewLine & _
            " If ActiveSheet.ProtectContents = False Then " & vbNewLine & _
            "    Exit Sub " & vbNewLine & _
            " End If " & vbNewLine & _
                          "      Next : Next : Next : Next : Next : Next " & vbNewLine & _
                                " Next : Next : Next : Next : Next : Next " & vbNewLine & _
                "end sub"

            strCode = _
               "sub MyMacro()" & vbCr & _
               " If ActiveSheet.ProtectContents = true Then" & vbNewLine & _
             "   ActiveSheet.Unprotect """ & strPassword & """ " & vbNewLine & _
            " End If " & vbNewLine & _
             "end sub"

            xlmodule.CodeModule.AddFromString(strCode)

            For Each wsHeet In objWbook.Worksheets
                wsHeet.Activate()
                If objWbook.ActiveSheet.ProtectContents = True Then
                    ObjExcel.Run("MyMacro")
                End If
            Next
            objWbook.Close(True, txtInputFolder.Text & "\" & lstFiles.Items(intCtr))
            intCtr = intCtr + 1

           
        Next
        ObjExcel.Quit()

    End Sub

Convert XmlNodeList to DataTable

XmlDocument xmlDoc = new XmlDocument();
XmlNodeList nodes;
string xmlResp;
// convert string to stream            
byte[] byteArray = Encoding.ASCII.GetBytes(xmlResp);
MemoryStream stream = new MemoryStream(byteArray);
xmlDoc = ConvertStreamToXMLDocument(stream);
nodes = xmlDoc.GetElementsByTagName("Items");
DataTable dt = Utility.ConvertXmlNodeListToDataTable(nodes);

public static DataTable ConvertXmlNodeListToDataTable(XmlNodeList xnl)
{
    DataTable dt = new DataTable();
    int TempColumn = 0;

    foreach (XmlNode node in xnl.Item(0).ChildNodes)
    {
        TempColumn++;
        DataColumn dc = new DataColumn(node.Name, System.Type.GetType("System.String"));
        if (dt.Columns.Contains(node.Name))
        {
            dt.Columns.Add(dc.ColumnName = dc.ColumnName + TempColumn.ToString());
        }
        else
        {
            dt.Columns.Add(dc);
        }
    }

    int ColumnsCount = dt.Columns.Count;
    for (int i = 0; i < xnl.Count; i++)
    {
        DataRow dr = dt.NewRow();
        for (int j = 0; j < ColumnsCount; j++)
        {
            //dr[j] = xnl.Item(i).ChildNodes[j].InnerText;
            if (xnl.Item(i).ChildNodes[j] != null)
                dr[j] = xnl.Item(i).ChildNodes[j].InnerText;
            else
                dr[j] = null;
        }
        dt.Rows.Add(dr);
    }
    return dt;
}

Read values from cookies

const string COOKIE_NAME = "xyz";
string subject = "";
if (HttpContext.Current.Request.Cookies[COOKIE_NAME] != null)
{
    allClaims = new Dictionary<String, String>();

    HttpCookie authCookie = HttpContext.Current.Request.Cookies[COOKIE_NAME];

    NameValueCollection col = new NameValueCollection(authCookie.Values);
    String[] keyNames = col.AllKeys;
    foreach (string key in keyNames)
    {
        allClaims.Add(key, col.GetValues(key)[0]);
    }

    subject = authCookie["subject"].ToString();
}   

Write DataTable to Excel

public void WriteToExcel(Excel.Worksheet worksheet, DataTable dataTable)
{
    var columns = dataTable.Columns.Count;
    var rows = dataTable.Rows.Count;
    var startCell = (Excel.Range)worksheet.Cells[1, 1];
    var endCell = (Excel.Range)worksheet.Cells[rows + 1, columns];
    var writeRange = (Excel.Range)worksheet.get_Range(startCell, endCell);
    var data = new object[rows + 1, columns];
    //Header                   
    for (var column = 0; column < columns; column++)
    {
        data[0, column] = dataTable.Columns[column].ColumnName;
    }
    for (var row = 1; row <= rows; row++)
    {
        for (var column = 0; column < columns; column++)
        {
            data[row, column] = dataTable.Rows[row - 1][column].ToString();
        }
    }
    worksheet.Rows.Clear();
    writeRange.Value = data;
    worksheet.Name = "test";   
}

Convert JSON to XML

Run below command in Visual Studio for the project
PM> Install-Package Newtonsoft.Json

XmlDocument xmlDoc = new XmlDocument();
xmlDoc = JsonConvert.DeserializeXmlNode("{records:{items:" + xmlResp + "}}");

Convert string to stream to XML document

// convert string to stream       
XmlDocument xmlDoc = new XmlDocument();    
byte[] byteArray = Encoding.ASCII.GetBytes(xmlResp);
MemoryStream stream = new MemoryStream(byteArray);
xmlDoc = ConvertStreamToXMLDocument(stream);
private XmlDocument ConvertStreamToXMLDocument(Stream stream)
{
    XmlDocument xmlDocument = new XmlDocument();
    xmlDocument = new XmlDocument();
    //load into XDocument
    XDocument xDocument = XDocument.Load(stream);

    //scrub the namespaces out!
    xDocument.Descendants().Attributes().Where(x => x.IsNamespaceDeclaration).Remove();
    // convert to xml document
    using (var xmlReader = xDocument.CreateReader())
    {
        xmlDocument.Load(xmlReader);
    }

    // remove default namespace
    xmlDocument.LoadXml(xmlDocument.OuterXml.Replace(xmlDocument.DocumentElement.NamespaceURI, ""));
    xmlDocument.DocumentElement.RemoveAllAttributes();

    return xmlDocument;
}