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

namespace Neo.Afx.ComponentModel
{
    ///////////////////////////////////////////////////////////////////////
    // DO NOT USE Neo.Afx.Core version as it closes connections!!!
    ///////////////////////////////////////////////////////////////////////


    /// <summary>
    /// Groups some SQL Server utility methods.
    /// </summary>  
    public static class SqlUtilities
    {
        /// <summary>
        /// The CLR connection string: <code>context connection=true</code>
        /// </summary>
        public const string ClrConnectionString = "context connection=true";

        #region FetchDataTable

        /// <summary>
        /// Executes the given <code>SqlCommand</code> and returns a 
        /// <code>DataTable</code> for the result set returned by the 
        /// <code>SqlCommand</code>. 
        /// </summary>
        /// <returns>The result of the command in a <code>DataTable</code>.</returns>
        public static DataTable FetchDataTable(this SqlCommand command)
        {
            using(var reader = command.ExecuteReader((CommandBehavior.SchemaOnly & CommandBehavior.KeyInfo)))
            {
                var table = new DataTable();
                table.Load(reader, LoadOption.OverwriteChanges);
                return table;
            }
        }

        #endregion

        #region Replace

        /// <summary>
        /// Replaces values in the format string with values from the given source.
        /// </summary>
        /// <param name="formatString">The format string to be parsed.</param>
        /// <param name="formatType">The type of the formatting to be performed.</param>
        /// <param name="source">The source to be queried for values - must be positioned before call.</param>
        /// <returns>The string with the relevant values replaced.</returns> 
        public static string Replace(this string formatString, EntityFormatType formatType, SqlDataReader source)
        {
            return ReplaceInternal(formatString, formatType,
                source.FieldCount, source.GetName, index => source[index].ToStringSafe());
        }

        /// <summary>
        /// Replaces values in the format string with values from the given source.
        /// </summary>
        /// <param name="formatString">The format string to be parsed.</param>
        /// <param name="formatType">The type of the formatting to be performed.</param>
        /// <param name="source">The source to be queried for values - must be positioned before call.</param>
        /// <returns>The string with the relevant values replaced.</returns> 
        public static string Replace(this string formatString, EntityFormatType formatType, DataRow source)
        {
            return ReplaceInternal(formatString, formatType,
                source.Table.Columns.Count,
                index => source.Table.Columns[index].ColumnName,
                index => source[index].ToStringSafe());
        }

        #endregion

        #region ReplaceInternal

        static string ReplaceInternal(string formatString, EntityFormatType formatType, int colCount, Func<int, string> getName, Func<int, string> getValue)
        {
            var result = new StringBuilder(formatString);

            for(var colIndex = 0; colIndex < colCount; colIndex++)
            {
                var name = getName(colIndex);
                var value = getValue(colIndex);

                switch(formatType)
                {
                    case EntityFormatType.Replacement:
                        result.Replace("[[" + name.ToLower() + "]]", value);
                        break;

                    case EntityFormatType.Xml:
                        result.Append(new XElement(name, value));
                        break;
                }
            }
            return result.ToString();
        }

        #endregion
    }
}