SQL SERVER TO XML USING C#

In SSIS we dont have XML DESTINATION. The following c# code  is used to populate data from sql server to xml file.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using System.Data;
using System.Data.SqlClient;
using System.Collections;

namespace XmlConsole
{
    class Program
    {
        static void Main(string[] args)
        {
            string XmlPath=@"d:\article.xml";  \\ xml path
            string conn = @"Data source=SERVER;database=PublishingCompany;uid=sa;pwd=password"; \* sql connection string */
            SqlConnection SqlConn = new SqlConnection(conn);
            string query = "Select * from Article";
            SqlDataAdapter sda = new SqlDataAdapter(query, SqlConn);
            DataSet ds = new DataSet();
            sda.Fill(ds);

            DataTable dt = ds.Tables[0];
            ArrayList columns = new ArrayList();

            foreach (DataColumn dc in dt.Columns )
            {
                columns.Add (dc.ToString());
            }

            XmlTextWriter xr = new XmlTextWriter(XmlPath,Encoding .UTF8);
            xr.WriteStartDocument();
            xr.WriteStartElement("Article");

            for(int i=0; i<dt.Rows .Count ;i++)
            {
                xr.WriteStartElement("Article");
                for (int j = 0; j < dt.Columns.Count; j++)
                {                  
                    xr.WriteElementString(columns[j].ToString (), dt.Rows[i][j].ToString());                   
                }
                xr.WriteEndElement();
            }
            xr.WriteEndElement();
            xr.WriteEndDocument();
            xr.Close();
        }
    }
}

C# CODE TO INSERT DATA TO REMOTE SERVER MONGODB FROM CSV FILE

using System;
using System.Collections.Generic;
using System.Text;
using MongoDB.Driver;
using MongoDB.Bson;
using System.IO;
using System.Text.RegularExpressions;


namespace csharpMongo
{
    class Program
    {
        static void Main(string[] args)
        {
            string sourceFile=@"F:\FILE.csv";
            StreamReader sr = new StreamReader(sourceFile);
            string line = sr.ReadLine();
            var columnNames = Regex.Split(line, ",");
            string connectionString = "mongodb://xxx.xxx.x.xxx"; //TARGET IP ADDRESS
            MongoServer mongo = MongoServer.Create(connectionString);
            mongo.Connect();
            var db = mongo.GetDatabase("database_name");  // TARGET DATABASE NAME
       
            using (mongo.RequestStart(db))
            {
                var collection = db.GetCollection<BsonDocument >("Collection_name");
                while ((line = sr.ReadLine()) != null)
                {
                    string[] cols=Regex .Split (line ,",");
                    BsonDocument book = new BsonDocument();
                    for (int i = 0; i < columnNames.Length; i++)
                    {
                        book.Add(columnNames[i], cols[i]);
                    }
                    collection.Insert(book);
                }
            }

         
            mongo.Disconnect();
        }
    }
}

C# CODE TO COPY FILE FROM WINDOWS TO UBUNTU

using System;
using System.Collections.Generic;
using Tamir.SharpSsh;
//using Tamir.Streams;


namespace CopyFileToUbuntu
{
    class Program
    {
        static void Main(string[] args)
        {

            string host = "ipaddress";
            string username = "username";
            string password = "password";
            string sourcePath = @"F:\sample.json";
            //string sourcePath = @"F:\";
            string destPath = "/home/myfolder/Archive/";

            Sftp sftpClient = new Sftp(host, username, password);

            sftpClient.Connect();
            sftpClient.Put(sourcePath, destPath);
         
            //sftpClient.Get( destPath,sourcePath );
            sftpClient.Close();
        }
    }
}

C# CODE TO CONVERT CSV FILE JSON FILE FORMAT

using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using Newtonsoft.Json;
using System.Data;
using System.Text.RegularExpressions;

namespace jsonPractice
{
 
    class Program
    {
        static void Main(string[] args)
        {
            string[] cols;
            string[] rows;
         
            StreamReader sr = new StreamReader(@"F:\Sample.csv");  //SOURCE FILE
            StreamWriter sw = new StreamWriter(@"F:\sample.json");  // DESTINATION FILE
         
            string line = sr.ReadLine();
         

            cols = Regex.Split(line, ",");

            DataTable table = new DataTable();
            for (int i = 0; i < cols.Length; i++)
            {
                table.Columns.Add(cols[i], typeof(string));
            }
            while ((line = sr.ReadLine()) != null)
            {
                 table.Rows.Clear();
             
                int i;
                string row = string.Empty;
                rows = Regex.Split(line, ",");
                DataRow dr = table.NewRow();
             
                for (i = 0; i < rows.Length ; i++)
                {
                    dr[i] = rows[i];
                                   
                }
                table.Rows.Add(dr);
             
                string json = JsonConvert.SerializeObject(table, Formatting.Indented);
                sw.Write(json);
            }

       
            sw.Close();
            sr.Close();

        }
    }
}

C# CODE TO CONVERT XML FILE TO JSON FILE FORMAT

xmltoJson console code
-------------------------

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Xml;
using Newtonsoft.Json;
using System.IO;
using System.Collections;

namespace xml2json
{
    class Program
    {
        static void Main(string[] args)
        {
            XmlDocument doc = new XmlDocument();
            try
            {
                //string path = Server.MapPath(".");
                doc.Load(@"c:\abc.xml");
            }
            catch (Exception ex)
            {
                //lblError.Text = ex.ToString();
                return;
            }
            StringBuilder JSON = new StringBuilder();
            JSON.Append(XmlToJSON(doc));
            // Convert XML to a JSON string
            //String  JSON = XmlToJSON(doc);

            // Replace \ with \\ because string is being decoded twice
            JSON = JSON.Replace(@"\", @"\\");

            StreamWriter sw = new StreamWriter(@"c:\abc.json");
            sw.WriteLine(JSON);
            sw.Close();
        }


        private static string XmlToJSON(XmlDocument xmlDoc)
        {
            StringBuilder sbJSON = new StringBuilder();
            sbJSON.Append("{ ");
            XmlToJSONnode(sbJSON, xmlDoc.DocumentElement, true);
            sbJSON.Append("}");
            return sbJSON.ToString();
        }

        //  XmlToJSONnode:  Output an XmlElement, possibly as part of a higher array
        private static void XmlToJSONnode(StringBuilder sbJSON, XmlElement node, bool showNodeName)
        {
            if (showNodeName)
                sbJSON.Append("\"" + SafeJSON(node.Name) + "\": ");
            sbJSON.Append("{");
            // Build a sorted list of key-value pairs
            //  where   key is case-sensitive nodeName
            //          value is an ArrayList of string or XmlElement
            //  so that we know whether the nodeName is an array or not.
            SortedList childNodeNames = new SortedList();

            //  Add in all node attributes
            if (node.Attributes != null)
                foreach (XmlAttribute attr in node.Attributes)
                    StoreChildNode(childNodeNames, attr.Name, attr.InnerText);

            //  Add in all nodes
            foreach (XmlNode cnode in node.ChildNodes)
            {
                if (cnode is XmlText)
                    StoreChildNode(childNodeNames, "value", cnode.InnerText);
                else if (cnode is XmlElement)
                    StoreChildNode(childNodeNames, cnode.Name, cnode);
            }

            // Now output all stored info
            foreach (string childname in childNodeNames.Keys)
            {
                ArrayList alChild = (ArrayList)childNodeNames[childname];
                if (alChild.Count == 1)
                    OutputNode(childname, alChild[0], sbJSON, true);
                else
                {
                    sbJSON.Append(" \"" + SafeJSON(childname) + "\": [ ");
                    foreach (object Child in alChild)
                        OutputNode(childname, Child, sbJSON, false);
                    sbJSON.Remove(sbJSON.Length - 2, 2);
                    sbJSON.Append(" ], ");
                }
            }
            sbJSON.Remove(sbJSON.Length - 2, 2);
            sbJSON.Append(" }");
        }

        //  StoreChildNode: Store data associated with each nodeName
        //                  so that we know whether the nodeName is an array or not.
        private static void StoreChildNode(SortedList childNodeNames, string nodeName, object nodeValue)
        {
            // Pre-process contraction of XmlElement-s
            if (nodeValue is XmlElement)
            {
                // Convert  <aa></aa> into "aa":null
                //          <aa>xx</aa> into "aa":"xx"
                XmlNode cnode = (XmlNode)nodeValue;
                if (cnode.Attributes.Count == 0)
                {
                    XmlNodeList children = cnode.ChildNodes;
                    if (children.Count == 0)
                        nodeValue = null;
                    else if (children.Count == 1 && (children[0] is XmlText))
                        nodeValue = ((XmlText)(children[0])).InnerText;
                }
            }
            // Add nodeValue to ArrayList associated with each nodeName
            // If nodeName doesn't exist then add it
            object oValuesAL = childNodeNames[nodeName];
            ArrayList ValuesAL;
            if (oValuesAL == null)
            {
                ValuesAL = new ArrayList();
                childNodeNames[nodeName] = ValuesAL;
            }
            else
                ValuesAL = (ArrayList)oValuesAL;
            ValuesAL.Add(nodeValue);
        }

        private static void OutputNode(string childname, object alChild, StringBuilder sbJSON, bool showNodeName)
        {
            if (alChild == null)
            {
                if (showNodeName)
                    sbJSON.Append("\"" + SafeJSON(childname) + "\": ");
                sbJSON.Append("null");
            }
            else if (alChild is string)
            {
                if (showNodeName)
                    sbJSON.Append("\"" + SafeJSON(childname) + "\": ");
                string sChild = (string)alChild;
                sChild = sChild.Trim();
                sbJSON.Append("\"" + SafeJSON(sChild) + "\"");
            }
            else
                XmlToJSONnode(sbJSON, (XmlElement)alChild, showNodeName);
            sbJSON.Append(", ");
        }

        // Make a string safe for JSON
        private static string SafeJSON(string sIn)
        {
            StringBuilder sbOut = new StringBuilder(sIn.Length);
            foreach (char ch in sIn)
            {
                if (Char.IsControl(ch) || ch == '\'')
                {
                    int ich = (int)ch;
                    sbOut.Append(@"\u" + ich.ToString("x4"));
                    continue;
                }
                else if (ch == '\"' || ch == '\\' || ch == '/')
                {
                    sbOut.Append('\\');
                }
                sbOut.Append(ch);
            }
            return sbOut.ToString();
        }
    }
}

C# CODE TO CONVERT CSV FILE TO XML FILE FORMAT

using System;
using System.Collections.Generic;
using System.Text;
using System.IO;


namespace textToXml
{
    class Program
    {
        static void Main(string[] args)
        {
            StreamReader sr = new StreamReader(@"C:\Documents and Settings\Shalu\Desktop\mywork\Dictionary");
            StreamWriter sw = new StreamWriter(@"d:\Dictionary1.xml");
              
            try
            {
                string table = "Table";
                string TableName = "TableName";
                string FirstLine;
                string line;
                string[] col;
                string[] values;
                FirstLine = sr.ReadLine();
                col = FirstLine.Split('\t');

                sw.Write(OpenTag(table));


                while ((line = sr.ReadLine()) != null)
                {
                    sw.Write(OpenTag(TableName));
                    values = line.Split(',');
                    for (int i = 0; i < values.Length; i++)
                    {
                        col[i] = col[i].Replace(" ", "_x0020_");
                        if (values[i] == "")
                        {
                            string t = Tag(col[i], "NULL");
                            sw.Write(t);
                        }
                        if (values[i] == "&")
                        {
                            string t = Tag(col[i], "&amp;");
                            sw.Write(t);
                        }
                        if (values[i] == "<")
                        {
                            string t = Tag(col[i], "&lt;");
                            sw.Write(t);
                        }
                        if (values[i] == ">")
                        {
                            string t = Tag(col[i], "&gt;");
                            sw.Write(t);
                        }
                        if (values[i] == "\"")
                        {
                            string t = Tag(col[i], "&quot;");
                            sw.Write(t);
                        }
                        if (values[i] == "\'")
                        {
                            string t = Tag(col[i], "&apos;");
                            sw.Write(t);
                        }
                        else
                        {
                            string t = Tag(col[i], values[i]);
                            sw.Write(t);
                        }
                       
                    }
                    sw.Write(CloseTag(TableName));
                }

               
                sw.Write(CloseTag(table));
            }
            finally
            {
                sr.Close();
                sw.Close();
            }
           
        }
        public static string OpenTag(string col)
        {
            return "<"+col+">";
        }
        public static string CloseTag(string col)
        {
            return (@"</") + col + ">";
        }
        public static string Tag(string columnName, string columnValue)
        {
            return OpenTag(columnName) + columnValue + CloseTag(columnName);
        }
    }
}

C# CODE TO CONVERT SQL SERVER CODE TO XML FORMAT

EXECUTE THE FOLLOWING CODE IN SSIS SCRIPT TASK
________________________________________________

/*
   Microsoft SQL Server Integration Services Script Task
   Write scripts using Microsoft Visual C# 2008.
   The ScriptMain is the entry point class of the script.
*/

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Data;
using System.Xml;

namespace ST_bb7b89d299dd436baa727dc552e2636f.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

        /*
        The execution engine calls this method when the task executes.
        To access the object model, use the Dts property. Connections, variables, events,
        and logging features are available as members of the Dts property as shown in the following examples.

        To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;
        To post a log entry, call Dts.Log("This is my log text", 999, null);
        To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true);

        To use the connections collection use something like the following:
        ConnectionManager cm = Dts.Connections.Add("OLEDB");
        cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";

        Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
       
        To open Help, press F1.
    */

        public void Main()
        {
            XmlDocument doc = new XmlDocument();
            string conn = @"Data source=nws1008\Admin123;database=testdb;uid=sa;pwd=!password01";
            string query = "select * from aut";
            SqlConnection con = new SqlConnection(conn);
            SqlDataAdapter sda = new SqlDataAdapter(query, con);
            DataSet ds = new DataSet();
            using (con)
            using (sda)
            {
                sda.Fill(ds,"aut");
            }

            //StringBuilder sb = new StringBuilder();
            XmlTextWriter xw = new XmlTextWriter(@"c:\aut.xml", null);
            ds.WriteXml(xw);
            // TODO: Add your code here
            Dts.TaskResult = (int)ScriptResults.Success;
        }
    }
}