public virtual ResultSet GetResultSet()
{
var ds = GetDataSet();
if (ds.Tables.Count > 1) throw new InvalidOperationException("Multiple datatables returned");
var schema = GetSchema();
int count = ds.Tables[0].Columns.Count;
ResultSet rs = new ResultSet();
rs.Schema = schema;
foreach (DataRow r in ds.Tables[0].Rows)
{
Row row = new Row();
foreach (DataColumn c in ds.Tables[0].Columns)
{
var cs = schema.Columns[c.ColumnName];
Column col = (Column)Activator.CreateInstance(cs.ColumnType);
col.Schema = cs;
col.Value = r[c.ColumnName];
row.Columns.Add(cs.Name, col);
}
rs.Rows.Add(row);
}
return rs;
}
protected string GetHeader(ResultSet rs)
{
string batchHeader = BatchHeaderTemplate;
batchHeader = batchHeader.Replace("{Table}", rs.Schema.TableName);
batchHeader = batchHeader.Replace("{Query}", Query);
batchHeader = batchHeader.Replace("{Time}", DateTime.Now.ToString("yyyy-MM-dd HH:mm"));
batchHeader = batchHeader.Replace("{BlockSize}", BlockSize.ToString());
batchHeader = batchHeader.Replace("{IsInsertIdentity}", IsInsertIdentity ? "Yes" : "No");
batchHeader = batchHeader.Replace("{IsUpdateByPrimaryKey}", IsUpdateByPrimaryKey ? "Yes" : "No");
batchHeader = batchHeader.Replace("{PrimaryKey}", String.Join(", ", rs.Schema.Columns.Values.Where(n => n.IsPrimaryKey).OrderBy(n => n.Ordinal).Select(n => n.Name)));
batchHeader = batchHeader.Replace("{UpdateByColumns}", String.Join(", ", UpdateByColumnNames));
return batchHeader;
}
protected string GetUpdateStatementTemplate(ResultSet rs)
{
string sql = UpdateTemplate;
List<string> updatebycolumns = GetUpdateByColumns(rs);
if (updatebycolumns.Count == 0) throw new InvalidOperationException("Cannot build WHERE clause. No PRIMARY KEY defined on table or returned in query. Please provide update columns.");
sql = sql.Replace("{Table}", rs.Schema.TableName);
string assign = "";
int assignCount = rs.Rows.First().Columns.Values.Where(n => !updatebycolumns.Contains(n.Schema.Name) && !n.Schema.IsIdentity).Count();
int i = 0;
foreach (var c in rs.Rows.First().Columns.Values.OrderBy(n => n.Schema.Ordinal))
{
if (updatebycolumns.Contains(c.Schema.Name)) continue;
if (c.Schema.IsIdentity) continue;
string assignvalue = UpdateValueAssignmentTemplate;
assignvalue = assignvalue.Replace("{Column}", c.Schema.Name);
assignvalue = assignvalue.Replace("{Value}", "{" + i.ToString() + "}");
if (i < assignCount - 1)
assignvalue = assignvalue.Replace("{Comma}", ",");
else
assignvalue = assignvalue.Replace("{Comma}", "");
assign = assign + assignvalue;
i++;
}
sql = sql.Replace("{Assign}", assign);
string whereclause = "";
int j = 0;
foreach (string key in updatebycolumns)
{
if (j > 0) whereclause = whereclause + " AND ";
whereclause = key + " = {" + i.ToString() + "}";
i++;
j++;
}
sql = sql.Replace("{Where}", whereclause);
return sql;
}
protected string GetInsertStatementTemplate(ResultSet rs)
{
string sql = InsertTemplate;
string columns = "";
foreach (var c in rs.Rows.First().Columns.Values.OrderBy(n => n.Schema.Ordinal))
{
if (!IsInsertIdentity && c.Schema.IsIdentity) continue;
if (!String.IsNullOrEmpty(columns)) columns = columns + ", ";
columns = columns + c.Schema.Name;
}
sql = sql.Replace("{Table}", rs.Schema.TableName);
sql = sql.Replace("{Columns}", columns);
sql = sql.Replace("{Values}", "{0}");
return sql;
}
protected List<string> GetUpdateByColumns(ResultSet rs)
{
if (!IsUpdateByPrimaryKey && UpdateByColumnNames.Count > 0) return UpdateByColumnNames;
List<String> PrimaryKeysInTable = rs.Schema.Columns.Values.Where(n => n.IsPrimaryKey).OrderBy(n => n.Ordinal).Select(n => n.Name).ToList();
List<String> PrimaryKeysInQuery = rs.Rows.First().Columns.Values.Where(n => n.Schema.IsPrimaryKey).OrderBy(n => n.Schema.Ordinal).Select(n => n.Schema.Name).ToList();
if (PrimaryKeysInQuery.Intersect(PrimaryKeysInTable).Count() != PrimaryKeysInQuery.Count) return new List<string>();
return PrimaryKeysInQuery;
}