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