/*
    *** DO NOT USE IN PRODUCTION -- TESTING / DEMONSTRATION CODE ONLY ***

    This script relates to the following SQL Server Central Forum topic:
    Processing strings ( https://www.sqlservercentral.com/forums/topic/processing-strings )

    *** DO NOT USE IN PRODUCTION -- TESTING / DEMONSTRATION CODE ONLY ***

    This script provides the .NET / C# source code for the [ParseCSV] SQLCLR Stored Procedure.

    *** DO NOT USE IN PRODUCTION -- TESTING / DEMONSTRATION CODE ONLY ***

    A T-SQL installation script (no external DLL) containing only two Stored Procedures is located at:
    https://pastebin.com/aqsWiX1e

    *** DO NOT USE IN PRODUCTION -- TESTING / DEMONSTRATION CODE ONLY ***

    A T-SQL script containing tests for the [ParseCSV] SQLCLR Stored Procedure is located at:
    https://pastebin.com/WGDkQTxt

    *** DO NOT USE IN PRODUCTION -- TESTING / DEMONSTRATION CODE ONLY ***

    Date: 2016-04-11
    Version: 1.0.0

    For more functions like this, please visit: https://SQLsharp.com

    Stairway to SQLCLR series: https://www.sqlservercentral.com/stairways/stairway-to-sqlclr

    Copyright (c) 2016 Sql Quantum Leap. All rights reserved.
    https://SqlQuantumLeap.com
*/

using System;
using System.Data.SqlTypes;
using System.IO;
using Microsoft.SqlServer.Server;
using Microsoft.VisualBasic.FileIO;


public class CSVUtils
{
    [SqlProcedure()]
    public static SqlInt32 ParseCSV(
        [SqlFacet(MaxSize = 1)] SqlString Delimiter,
        [SqlFacet(MaxSize = -1)] SqlString InputString,
        [SqlFacet(MaxSize = 500)] SqlString FilePath)
    {
        if (Delimiter.IsNull)
        {
            return 1;
        }

        if (!FilePath.IsNull && !FilePath.Value.Trim().Equals(string.Empty))
        {
            using (TextFieldParser _CSV = new TextFieldParser(FilePath.Value))
            {
                ParseStuff(_CSV, Delimiter.Value);
            }
        }
        else
        {
            if (InputString.IsNull || InputString.Value.Trim().Equals(string.Empty))
            {
                return 0;
            }

            using (StringReader _Reader = new StringReader(InputString.Value))
            {
                using (TextFieldParser _CSV = new TextFieldParser(_Reader))
                {
                    ParseStuff(_CSV, Delimiter.Value);
                }
            }
        }
        return 0;
    }

    private static void ParseStuff(TextFieldParser CSV, string Delimiter)
    {
        bool _IsResultSchemaSet = false;
        SqlDataRecord _ResultSet = null;
        int _FieldCount = 0;

        CSV.SetDelimiters(Delimiter); // this also sets TextFieldType = FieldType.Delimited;
        CSV.HasFieldsEnclosedInQuotes = true;
        string[] _Fields;

        while (!CSV.EndOfData)
        {
            _Fields = CSV.ReadFields();

            if (!_IsResultSchemaSet)
            {
                _FieldCount = _Fields.Length;
                SqlMetaData[] _TempStructure = new SqlMetaData[_FieldCount];

                for (int _Index = 0; _Index < _FieldCount; _Index++)
                {
                    _TempStructure[_Index] = new SqlMetaData(string.Format("Field{0:D3}", _Index + 1),
                        System.Data.SqlDbType.NVarChar, SqlMetaData.Max);
                }
                _ResultSet = new SqlDataRecord(_TempStructure);

                SqlContext.Pipe.SendResultsStart(_ResultSet);

                _IsResultSchemaSet = true;
            }

            _ResultSet.SetValues(_Fields);
            if (_Fields.Length < _FieldCount)
            {
                for (int _Index = _Fields.Length; _Index < _FieldCount; _Index++)
                {
                    _ResultSet.SetDBNull(_Index);
                }
            }
            SqlContext.Pipe.SendResultsRow(_ResultSet);
        }

        SqlContext.Pipe.SendResultsEnd();

        return;
    }

    [SqlProcedure]
    public static void GarbageCollect()
    {
        GC.Collect();

        return;
    }

}