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;
}