using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Text;
using Microsoft.SqlServer.Server;
using Neo.Afx.ComponentModel;

namespace Neo.Afx.Sys
{
    /// <summary>
    /// Helper function to assist with sql dataset formatting based on a predefined template
    /// </summary>
    public class DatasetFormatter
    {
        /// <summary>
        /// Selects data from a provided SQL string and inserts it into the format string.
        /// </summary>
        /// <param name="sql">The sql string to execute</param>
        /// <param name="formatString">The format string.</param>
        /// <returns>The formatted string.</returns>
        [SqlFunction(
            IsDeterministic = true,
            IsPrecise = true,
            DataAccess = DataAccessKind.Read,
            SystemDataAccess = SystemDataAccessKind.Read
            )]
        public static SqlString GetFormattedDataSet(SqlString sql, SqlString formatString)
        {
            var result = new StringBuilder();

            using(var connection = new SqlConnection(SqlUtilities.ClrConnectionString))
            {
                using(var cmd = connection.CreateCommand())
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = sql.ToString();
                    cmd.Connection.Open();

                    using(var reader = cmd.ExecuteReader(CommandBehavior.Default))
                    {
                        while(reader.Read())
                        {
                            var s = formatString.Value.Replace(EntityFormatType.Replacement, reader);
                            result.Append(s);
                        }
                    }
                }
            }

            return new SqlString(result.ToString());
        }
    }
}