/// <summary> /// fill by selecting from the specified table /// </summary> /// <param name="tbl"></param> public void Fill(DataTable tbl) { // get the schema from the table: if (SchemaInfo == null) { SchemaInfo = tbl.GetTableInfo(); } int rowNum = 0; var where = this.WhereClause; if (string.IsNullOrEmpty(this.WhereClause)) { where = "1=1"; } foreach (var tblRow in tbl.Select(where)) { var scriptRow = new DbScriptRow(this) { RowID = rowNum++ }; foreach (var info in SchemaInfo) { scriptRow.Columns.Add(new DbScriptColumn(scriptRow) { ColumnInfo = info, Value = tblRow[info.ColumnName] }); } Rows.Add(scriptRow); } }
/// <summary> /// fill by selecting from the specified table /// </summary> /// <param name="tbl"></param> public void Fill(DataTable tbl) { // get the schema from the table: if (SchemaInfo == null) { SchemaInfo = tbl.GetTableInfo(); } int rowNum = 0; var where = this.WhereClause; if (string.IsNullOrEmpty(this.WhereClause)) { where = "1=1"; } foreach (var tblRow in tbl.Select(where)) { var scriptRow = new DbScriptRow(this) { RowID = rowNum++ }; foreach (var info in SchemaInfo) { var tblCol = tbl.Columns[info.ColumnName]; DbRelationship rl = null; if (tblCol.ExtendedProperties.ContainsKey("relationship")) { rl = DbRelationship.Parse(tblCol.ExtendedProperties["relationship"] as string); } scriptRow.Columns.Add(new DbScriptColumn(scriptRow) { ColumnInfo = info, Value = tblRow[tblCol], Relationship = rl }); } Rows.Add(scriptRow); } }
/// <summary> /// populate from an SQL server database connection /// </summary> /// <param name="connect"></param> public void Fill(SqlConnection connect) { if (connect.State != ConnectionState.Open) { connect.Open(); } if (SchemaInfo == null) { SchemaInfo = connect.GetTableInfo(this.TableName); } int rowNum = 0; using (var cmd = connect.CreateCommand()) { cmd.CommandText = $"SELECT * FROM [{TableName}] WHERE {WhereClause}"; using (var rdr = cmd.ExecuteReader(CommandBehavior.KeyInfo)) { while (rdr.Read()) { var row = new DbScriptRow(this) { RowID = rowNum++ }; foreach (var info in SchemaInfo) { row.Columns.Add(new DbScriptColumn(row) { ColumnInfo = info, Value = rdr.GetValue(info.ColumnOrdinal) }); } Rows.Add(row); } } } }
/// <summary> /// construct with parent reference /// </summary> /// <param name="owner"></param> public DbScriptColumn(DbScriptRow owner) { this.Row = owner; this.Table = owner.Table; }
/// <summary> /// creates the select for the relationship /// </summary> /// <param name="row"></param> /// <returns></returns> public string CreateSelect(DbScriptRow row) { return($"select [{ColumnName}] from [{TableName}] where {row.GetForeignKeyWhere(this)}"); }