public static DataSet DataSet(this SqlBuilder Builder, string ConnectionString = null, int TimeoutSeconds = 60, params object[] Format)
{
ConnectionString = ConnectionString ?? Builder.ConnectionString ?? SqlBuilder.DefaultConnection;
if (ConnectionString == null)
{
throw new InvalidOperationException("The ConnectionString must be set on the Execute Method or on the SqlBuilder");
}
DataSet ds = new DataSet();
using (TransactionScope trans = new TransactionScope(TransactionScopeOption.RequiresNew, new TransactionOptions()
{
IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted,
Timeout = TimeSpan.FromSeconds(TimeoutSeconds)
}))
{
try
{
using (SqlConnection context = new SqlConnection(ConnectionString))
{
context.Open();
SqlCommand cmd = new SqlCommand(Builder.ToSql(Format), context);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.AcceptChangesDuringFill = false;
adapter.Fill(ds);
context.Close();
}
}
catch (TransactionException exTrans)
{
trans.Dispose();
throw exTrans;
}
trans.Complete();
}
if (Builder.StatementType == SqlBuilder.StatementTypes.Procedure)
{
FillBuilderFromProcedureOutput(Builder, ds.Tables[0]);
}
return(ds);
}
private static ResultTable ExecuteRelatedInternal(SqlBuilder builder, Dictionary <string, RowData> results)
{
if (results.Count > 0)
{
MetadataTable mt = builder.BaseTable().WithMetadata().Model;
foreach (string key in results.Keys)
{
foreach (MetadataForeignKey fk in mt.ForeignKeys.Values.Where(x => (x.ReferencedSchema + "." + x.ReferencedTable).Equals(key, StringComparison.OrdinalIgnoreCase)))
{
RowData row = results[key];
foreach (MetadataColumnReference mcr in fk.ColumnReferences)
{
if (row.Columns.Contains(mcr.Column.Name))
{
Field f = builder.BaseTable().FindField(mcr.Column.Name);
if (f != null)
{
f.Value = row.Column(mcr.Column.Name);
}
else
{
(builder.BaseTable() as InsertIntoTable).Value(mcr.Column.Name, row.Column(mcr.Column.Name), SqlDbType.VarChar);
}
}
}
}
}
}
DataTable dt = new DataTable();
ResultTable table = new ResultTable();
using (SqlConnection context = new SqlConnection(builder.ConnectionString))
{
context.Open();
SqlCommand cmd = new SqlCommand(builder.ToSql(), context);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.AcceptChangesDuringFill = false;
adapter.Fill(dt);
context.Close();
}
if (builder.SubQueries.Count > 0)
{
Dictionary <string, RowData> subresults = new System.Collections.Generic.Dictionary <string, RowData>(results);
if (dt.Rows.Count > 0)
{
MetadataTable mt = builder.BaseTable().WithMetadata().Model;
if (!subresults.ContainsKey(mt.Fullname))
{
ResultTable rt = new ResultTable(dt, ResultTable.DateHandlingEnum.None);
RowData row = rt.First();
table.Add(row);
subresults.Add(mt.Fullname, row);
}
}
foreach (SqlBuilder Builder in builder.SubQueries.Values)
{
ResultTable sub = ExecuteRelatedInternal(Builder, subresults);
foreach (RowData row in sub)
{
table.Add(row);
}
}
}
return(table);
}