public void GetSqlStatementsTest()
        {
            DataTable _table = SampleTable();
            _table.Rows.Add(new object[] { null, "jsph", 1, null, null, DateTime.Now, DateTime.Now });

            QueryGenerator _generator = new QueryGenerator(_table);
            _generator.ExcludedFields.Add("LastModified");
            _generator.ForeignKey.Field = "HeaderId";
            _generator.ForeignKey.Value = Database.Database.LastInsertIdCall;
            string[] _queries = _generator.GetSqlStatements();

            if (_queries.Length <= 0) Assert.Fail();
            else
            {
                for (int i = 0; i < _queries.Length; i++) Console.WriteLine(_queries[i]);
            }

            _table.AcceptChanges(); _generator = null;

            DataRow _row = _table.Rows[0];
            _row["Active"] = 0;
            _generator = new QueryGenerator(_table);
            _generator.ExcludedFields.Add("LastModified");
            _queries = null; _queries = _generator.GetSqlStatements();

            if (_queries.Length <= 0) Assert.Fail();
            else Console.WriteLine(_queries[0]);

            _table.AcceptChanges(); _generator = null;
            _row.Delete();

            _generator = new QueryGenerator(_table);
            _generator.ExcludedFields.Add("LastModified");
            _queries = null; _queries = _generator.GetSqlStatements();

            if (_queries.Length <= 0) Assert.Fail();
            else Console.WriteLine(_queries[0]);

            _table.Dispose(); _table = null;
        }
 /// <summary>
 /// Creates a new instance of PrimaryKeyInfo.
 /// </summary>
 /// <param name="generator">Development.Materia.Database.QueryGenerator that owns the current class.</param>
 /// <param name="field">Table's field name.</param>
 /// <param name="value">Field's assigned value.</param>
 public PrimaryKeyInfo(QueryGenerator generator, string field, string value)
 { _generator = generator; _field = field; _value = value; }
 /// <summary>
 /// Creates a new instance of PrimaryKeyInfo.
 /// </summary>
 /// <param name="generator">Development.Materia.Database.QueryGenerator that owns the current class.</param>
 public PrimaryKeyInfo(QueryGenerator generator) : this(generator, "", "")
 { }
 /// <summary>
 /// Creates a new instance of ForeignKeyInfo.
 /// </summary>
 /// <param name="generator">Development.Materia.Database.QueryGenerator that owns the current class.</param>
 /// <param name="field">Table's field name.</param>
 /// <param name="value">Field's assigned value.</param>
 public ForeignKeyInfo(QueryGenerator generator, string field, string value) : base(generator, field, value)
 { }
 /// <summary>
 /// Creates a new instance of ForeignKeyInfo.
 /// </summary>
 /// <param name="generator">Development.Materia.Database.QueryGenerator that owns the current class.</param>
 public ForeignKeyInfo(QueryGenerator generator)  : base(generator)
 { }
 /// <summary>
 /// Creates a new instance of ExcludedDataSourceFieldCollection.
 /// </summary>
 /// <param name="generator">Development.Materia.Database.QueryGenerator that owne the class.</param>
 public ExcludedDataSourceFieldCollection(QueryGenerator generator)
 { _generator = generator; }
        /// <summary>
        /// Returns the sql statement associated with the updates applied into the current DataBinding.
        /// </summary>
        /// <returns>SQL statement generated from the changes applied to the current data bindings.</returns>
        public string GetSqlStatement()
        {
            QueryGenerator _sqlgenerator = new QueryGenerator(_basetable);
            StringBuilder _sql = new StringBuilder();
            _sqlgenerator.ExcludedFields.Add(_pkfieldname);

            if (_basetable != null)
            {
                if (_basetable.Columns.Contains(_pkfieldname))
                {
                    if (_basetable.Rows.Count > 0) _sqlgenerator.PrimaryKey.Value = "{" + _basetable.Columns[_pkfieldname].Ordinal + "}";
                }
            }

            if (_header == null) _sql.Append(_sqlgenerator.ToString());
            else
            {
                string _pk = _header.PrimaryKey;

                if (_header.Table != null)
                {
                    if (String.IsNullOrEmpty(_pk.RLTrim()))
                    {
                        foreach (DataColumn _column in _header.Table.Columns)
                        {
                            if (_column.Unique)
                            {
                                _pk = _column.ColumnName; break;
                            }
                        }
                    }
                    else
                    {
                        if (!_header.Table.Columns.Contains(_pk))
                        {
                            foreach (DataColumn _column in _header.Table.Columns)
                            {
                                if (_column.Unique)
                                {
                                    _pk = _column.ColumnName; break;
                                }
                            }
                        }
                    }
                }

                _sqlgenerator.ForeignKey.HeaderPrimaryKey = _pk;
                _sqlgenerator.ForeignKey.HeaderTable = _header.Table;


                if (!String.IsNullOrEmpty(_pk.RLTrim()))
                {
                    string _fk = ForeignKey; string _fkvalue = "";

                    if (String.IsNullOrEmpty(_fk.RLTrim()))
                    {
                        if (_basetable.Columns.Contains(_pk)) _fk = _pk;
                    }
                    else
                    {
                        if (!_basetable.Columns.Contains(_fk))
                        {
                            if (_basetable.Columns.Contains(_pk)) _fk = _pk;
                            else _fk = "";
                        }
                    }

                    if (!String.IsNullOrEmpty(_fk.RLTrim()))
                    {
                        DataTable _htable = _header.GetBaseTable();
                        if (_htable != null)
                        {
                            if (_htable.Columns.Contains(_pk))
                            {
                                if (_htable.Rows.Count > 0)
                                {
                                    if (_htable.Columns[_pk].AutoIncrement)
                                    {
                                        DataRow rw = _htable.Rows[0];
                                        if (rw.RowState == DataRowState.Added) _fkvalue = Database.LastInsertIdCall;
                                        else
                                        {
                                            _fkvalue = "NULL";

                                            if (!Materia.IsNullOrNothing(rw[_pk]))
                                            {
                                                DataColumn _fkcolumn = _htable.Columns[_pk];

                                                if (_fkcolumn.DataType.Name == typeof(string).Name ||
                                                    _fkcolumn.DataType.Name == typeof(String).Name) _fkvalue = "'" + rw[_pk].ToString().ToSqlValidString() + "'";
                                                else if (_fkcolumn.DataType.Name == typeof(DateTime).Name)
                                                {
                                                    if (VisualBasic.IsDate(rw[_pk])) _fkvalue = "'" + VisualBasic.CDate(rw[_pk]).ToSqlValidString(true) + "'";
                                                }
                                                else if (_fkcolumn.DataType.Name == typeof(byte).Name ||
                                                         _fkcolumn.DataType.Name == typeof(Byte).Name ||
                                                         _fkcolumn.DataType.Name == typeof(decimal).Name ||
                                                         _fkcolumn.DataType.Name == typeof(Decimal).Name ||
                                                         _fkcolumn.DataType.Name == typeof(double).Name ||
                                                         _fkcolumn.DataType.Name == typeof(Double).Name ||
                                                         _fkcolumn.DataType.Name == typeof(float).Name ||
                                                         _fkcolumn.DataType.Name == typeof(int).Name ||
                                                         _fkcolumn.DataType.Name == typeof(Int16).Name ||
                                                         _fkcolumn.DataType.Name == typeof(Int32).Name ||
                                                         _fkcolumn.DataType.Name == typeof(Int64).Name ||
                                                         _fkcolumn.DataType.Name == typeof(long).Name ||
                                                         _fkcolumn.DataType.Name == typeof(sbyte).Name ||
                                                         _fkcolumn.DataType.Name == typeof(SByte).Name ||
                                                         _fkcolumn.DataType.Name == typeof(short).Name ||
                                                         _fkcolumn.DataType.Name == typeof(Single).Name)
                                                {
                                                    if (VisualBasic.IsNumeric(rw[_pk])) _fkvalue = rw[_pk].ToString();
                                                }
                                                else if (_fkcolumn.DataType.Name == typeof(bool).Name ||
                                                         _fkcolumn.DataType.Name == typeof(Boolean).Name)
                                                {
                                                    if (VisualBasic.CBool(rw[_pk])) _fkvalue = "1";
                                                    else _fkvalue = "0";
                                                }
                                                else
                                                { }
                                            }
                                        }
                                    }
                                    else
                                    {
                                        DataRow rw = _htable.Rows[0]; _fkvalue = "NULL";

                                        if (!Materia.IsNullOrNothing(rw[_pk]))
                                        {
                                            DataColumn _fkcolumn = _htable.Columns[_pk];

                                            if (_fkcolumn.DataType.Name == typeof(string).Name ||
                                                _fkcolumn.DataType.Name == typeof(String).Name) _fkvalue = "'" + rw[_pk].ToString().ToSqlValidString() + "'";
                                            else if (_fkcolumn.DataType.Name == typeof(DateTime).Name)
                                            {
                                                if (VisualBasic.IsDate(rw[_pk])) _fkvalue = "'" + VisualBasic.CDate(rw[_pk]).ToSqlValidString(true) + "'";
                                            }
                                            else if (_fkcolumn.DataType.Name == typeof(byte).Name ||
                                                     _fkcolumn.DataType.Name == typeof(Byte).Name ||
                                                     _fkcolumn.DataType.Name == typeof(decimal).Name ||
                                                     _fkcolumn.DataType.Name == typeof(Decimal).Name ||
                                                     _fkcolumn.DataType.Name == typeof(double).Name ||
                                                     _fkcolumn.DataType.Name == typeof(Double).Name ||
                                                     _fkcolumn.DataType.Name == typeof(float).Name ||
                                                     _fkcolumn.DataType.Name == typeof(int).Name ||
                                                     _fkcolumn.DataType.Name == typeof(Int16).Name ||
                                                     _fkcolumn.DataType.Name == typeof(Int32).Name ||
                                                     _fkcolumn.DataType.Name == typeof(Int64).Name ||
                                                     _fkcolumn.DataType.Name == typeof(long).Name ||
                                                     _fkcolumn.DataType.Name == typeof(sbyte).Name ||
                                                     _fkcolumn.DataType.Name == typeof(SByte).Name ||
                                                     _fkcolumn.DataType.Name == typeof(short).Name ||
                                                     _fkcolumn.DataType.Name == typeof(Single).Name)
                                            {
                                                if (VisualBasic.IsNumeric(rw[_pk])) _fkvalue = rw[_pk].ToString();
                                            }
                                            else if (_fkcolumn.DataType.Name == typeof(bool).Name ||
                                                     _fkcolumn.DataType.Name == typeof(Boolean).Name)
                                            {
                                                if (VisualBasic.CBool(rw[_pk])) _fkvalue = "1";
                                                else _fkvalue = "0";
                                            }
                                            else
                                            { }
                                        }
                                    }
                                }
                                else
                                {
                                    if (_htable.Columns[_pk].AutoIncrement) _fkvalue = Database.LastInsertIdCall; 
                                }
                            }
                        }

                        _sqlgenerator.ForeignKey.Field = _fk; _sqlgenerator.ForeignKey.Value = _fkvalue;
                    }
                }

                _sql.Append(_sqlgenerator.ToString());
            }

            return _sql.ToString();
        }
        public void ToStringTest()
        {
            DataTable _table = SampleTable();
            _table.Rows.Add(new object[] { null, "jsph", 1, null, null, DateTime.Now, DateTime.Now });
            _table.Rows.Add(new object[] { null, "dqadmin", 1, null, null, DateTime.Now, DateTime.Now });
            _table.AcceptChanges();

            _table.Rows.Add(new object[] { null, "admin", 1, null, null, DateTime.Now, DateTime.Now });

            DataRow[] _jsphRows = _table.Select("[Username] = 'jsph'");
            if (_jsphRows.Length > 0)
            {
                _jsphRows[0]["Active"] = 0;
                _jsphRows[0]["Username"] = "******";
            }

            DataRow[] _dqRows = _table.Select("[Username] = 'dqadmin'");
            if (_dqRows.Length > 0) _dqRows[0].Delete();

            QueryGenerator _generator = new QueryGenerator(_table);
            _generator.ExcludedFields.Add("LastModified");
            string _sql = _generator.ToString();
            if (_sql.Trim() == "") Assert.Fail();
            else Console.WriteLine(_sql);

            _table.AcceptChanges(); _table.Dispose(); _table = null;
        }
        /// <summary>
        /// Generates sql statement based on the current DataObjectMap's table value updates.
        /// </summary>
        /// <param name="excludedfields">List of table fields to be excluded from the sql statement generation.</param>
        /// <returns>SQL statement based on the current Development.Materia.Database.DataObjectMap's updates.</returns>
        public string GenerateSqlStatement(string[] excludedfields)
        {
            string _sql = "";

            if (_table != null)
            {
                QueryGenerator _generator = new QueryGenerator(_table);
                if (excludedfields != null)
                {
                    foreach (string _exfield in excludedfields)
                    {
                        if (!String.IsNullOrEmpty(_exfield.RLTrim()))
                        {
                            if (_table.Columns.Contains(_exfield)) _generator.ExcludedFields.Add(_exfield);
                        }
                    }
                }
                _sql = _generator.ToString();
            }

            return _sql;
        }