using System;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;

namespace Westwind.Tools
{
    /// <summary>
    /// Utility library for common data operations.
    /// </summary>
    public class wwDataUtils
    {

        public const BindingFlags MemberAccess =
            BindingFlags.Public | BindingFlags.NonPublic |
            BindingFlags.Static | BindingFlags.Instance | BindingFlags.IgnoreCase;


        /// <summary>
        /// Copies the content of a data row to another. Runs through the target's fields
        /// and looks for fields of the same name in the source row. Structure must mathc
        /// or fields are skipped.
        /// </summary>
        /// <param name="Source"></param>
        /// <param name="Target"></param>
        /// <returns></returns>
        public static bool CopyDataRow(DataRow Source, DataRow Target)
        {
            DataColumnCollection loColumns = Target.Table.Columns;

            for (int x = 0; x < loColumns.Count; x++)
            {
                string lcFieldname = loColumns[x].ColumnName;

                try
                {
                    Target[x] = Source[lcFieldname];
                }
                catch { ;}  // skip any errors
            }

            return true;
        }

        public static void CopyObjectFromDataRow(DataRow Row, object TargetObject)
        {
            MemberInfo[] miT = TargetObject.GetType().FindMembers(MemberTypes.Field | MemberTypes.Property, MemberAccess, null, null);
            foreach (MemberInfo Field in miT)
            {
                string Name = Field.Name;
                if (!Row.Table.Columns.Contains(Name))
                    continue;

                if (Field.MemberType == MemberTypes.Field)
                {
                    ((FieldInfo)Field).SetValue(TargetObject, Row[Name]);
                }
                else if (Field.MemberType == MemberTypes.Property)
                {
                    ((PropertyInfo)Field).SetValue(TargetObject, Row[Name], null);
                }
            }
        }

        /// <summary>
        /// Copies the content of an object to a DataRow with matching field names.
        /// Both properties and fields are copied. If a field copy fails due to a
        /// type mismatch copying continues but the method returns false
        /// </summary>
        /// <param name="Row"></param>
        /// <param name="Target"></param>
        /// <returns></returns>
        public static bool CopyObjectToDataRow(DataRow Row, object Target)
        {
            bool Result = true;

            MemberInfo[] miT = Target.GetType().FindMembers(MemberTypes.Field | MemberTypes.Property, MemberAccess, null, null);
            foreach (MemberInfo Field in miT)
            {
                string Name = Field.Name;
                if (!Row.Table.Columns.Contains(Name))
                    continue;

                try
                {
                    if (Field.MemberType == MemberTypes.Field)
                    {
                        Row[Name] = ((FieldInfo)Field).GetValue(Target);
                    }
                    else if (Field.MemberType == MemberTypes.Property)
                    {
                        Row[Name] = ((PropertyInfo)Field).GetValue(Target, null);
                    }
                }
                catch { Result = false; } 
            }

            return Result;
        }

        /// <summary>
        /// Copies the content of one object to another. The target object 'pulls' properties of the first. 
        /// </summary>
        /// <param name="Source"></param>
        /// <param name="Target"></param>
        public static void CopyObjectData(object Source, Object Target)
        {
            CopyObjectData(Source, Target, MemberAccess);
        }


        /// <summary>
        /// Copies the content of one object to another. The target object 'pulls' properties of the first. 
        /// </summary>
        /// <param name="Source"></param>
        /// <param name="Target"></param>
        public static void CopyObjectData(object Source, Object Target, BindingFlags MemberAccess)
        {
            MemberInfo[] miT = Target.GetType().GetMembers(MemberAccess);
            foreach (MemberInfo Field in miT)
            {
                string Name = Field.Name;
                if (Field.MemberType == MemberTypes.Field)
                {
                    FieldInfo SourceField = Source.GetType().GetField(Name);
                    if (SourceField == null)
                        continue;
                    object SourceValue = SourceField.GetValue(Source);
                    ((FieldInfo)Field).SetValue(Target, SourceValue);
                }
                else if (Field.MemberType == MemberTypes.Property)
                {
                    PropertyInfo SourceField = Source.GetType().GetProperty(Name, MemberAccess);
                    if (SourceField == null)
                        continue;
                    object SourceValue = SourceField.GetValue(Source, null);
                    ((PropertyInfo)Field).SetValue(Target, SourceValue, null);
                }
            }
        }


        /// <summary>
        /// The default SQL date used by InitializeDataRowWithBlanks. Considered a blank date instead of null.
        /// </summary>
        public static DateTime MinimumSqlDate = DateTime.Parse("01/01/1900");

        /// <summary>
        /// Initializes a Datarow containing NULL values with 'empty' values instead.
        /// Empty values are:
        /// String - ""
        /// all number types - 0 or 0.00
        /// DateTime - Value of MinimumSqlData (1/1/1900 by default);
        /// Boolean - false
        /// Binary values and timestamps are left alone
        /// </summary>
        /// <param name="Row">DataRow to be initialized</param>
        public static void InitializeDataRowWithBlanks(DataRow Row)
        {
            DataColumnCollection loColumns = Row.Table.Columns;

            for (int x = 0; x < loColumns.Count; x++)
            {
                if (!Row.IsNull(x))
                    continue;

                string lcRowType = loColumns[x].DataType.Name;

                if (lcRowType == "String")
                    Row[x] = "";
                else if (lcRowType.StartsWith("Int"))
                    Row[x] = 0;
                else if (lcRowType == "Byte")
                    Row[x] = 0;
                else if (lcRowType == "Decimal")
                    Row[x] = 0.00M;
                else if (lcRowType == "Double")
                    Row[x] = 0.00;
                else if (lcRowType == "Boolean")
                    Row[x] = false;
                else if (lcRowType == "DateTime")
                    Row[x] = wwDataUtils.MinimumSqlDate;

                // *** Everything else isn't handled explicitly and left alone
                // Byte[] most specifically

            }
        }

#region Minimal Sql Data Access Function

        /// <summary>
        /// Creates a Command object and opens a connection
        /// </summary>
        /// <param name="ConnectionString"></param>
        /// <param name="Sql"></param>
        /// <returns></returns>
        public static SqlCommand GetSqlCommand(string ConnectionString, string Sql, params SqlParameter[] Parameters)
        {
            SqlCommand Command = new SqlCommand();
            Command.CommandText = Sql;
            
            try 
            {
                Command.Connection = new SqlConnection(ConnectionString);
                Command.Connection.Open();
            }
            catch
            {
                return null;
            }


            if (Parameters != null)
            {
                foreach(SqlParameter Parm in Parameters)
                {
                    Command.Parameters.Add(Parm);
                }
            }

            return Command;
        }

        /// <summary>
        /// Returns a SqlDataReader object from a SQL string.
        /// 
        /// Please ensure you close the Reader object
        /// </summary>
        /// <param name="ConnectionString"></param>
        /// <param name="Sql"></param>
        /// <param name="Parameters"></param>
        /// <returns></returns>
        public static SqlDataReader GetSqlDataReader(string ConnectionString, string Sql, params SqlParameter[] Parameters)
        {
            SqlCommand Command = GetSqlCommand(ConnectionString, Sql, Parameters);
            if (Command == null)
                return null;

            SqlDataReader Reader = null;
            try
            {
                Reader = Command.ExecuteReader();
            }
            catch
            {
                CloseConnection(Command);
                return null;
            }

            return Reader;
        }

        /// <summary>
        /// Returns a DataTable from a Sql Command string passed in.
        /// </summary>
        /// <param name="Tablename"></param>
        /// <param name="ConnectionString"></param>
        /// <param name="Sql"></param>
        /// <param name="Parameters"></param>
        /// <returns></returns>
        public static DataTable GetDataTable(string Tablename, string ConnectionString, string Sql, params SqlParameter[] Parameters)
        {
            SqlCommand Command = GetSqlCommand(ConnectionString,Sql,Parameters);
            if (Command == null)
                return null;

            SqlDataAdapter Adapter = new SqlDataAdapter(Command);
                        
            DataTable dt = new DataTable(Tablename);

            try
            {
                Adapter.Fill(dt);
            }
            catch
            {
                return null;
            }
            finally
            {
                CloseConnection(Command);
            }

            return dt;
        }

        
        /// <summary>
        /// Closes a connection
        /// </summary>
        /// <param name="Command"></param>
        public static void CloseConnection(SqlCommand Command)
        {
            if (Command.Connection != null &&
                Command.Connection.State == ConnectionState.Open)
                Command.Connection.Close();
        }
#endregion

    }
}
