/// <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)}");
}