public XDocument ExecXml(SqlProc procedure)
{
if (procedure.Connection == null)
{
procedure.Connection = SqlConnection;
}
AssureOpen();
var cmd = procedure.CreateCommand();
Trace.WriteLine("ExecXml " + cmd.CommandText);
XDocument doc = null;
using (var reader = cmd.ExecuteXmlReader())
{
XDocument docNext = null;
// list nodes : reader.NameTable;
while (reader.Read())
{
if (doc == null)
{
doc = XDocument.Load(reader.ReadSubtree());
}
else
{
docNext = XDocument.Load(reader.ReadSubtree());
doc.Document.Root.Add(docNext.Root);
docNext = null;
}
}
}
return(doc);
}
public static IEnumerable <T> MultiExec <T>(this SqlProc proc,
Action <SqlDataReader, SqlField[]> onReadFields = null,
Action <Exception> onError = null)
where T : class
{
var cmd = proc.CreateCommand();
var mapper = new DbObject();
SqlDataReader reader = SqlMultiDyn.ExecMultiReader(proc, onError: onError);
if (reader == null)
{
yield break;
}
IEnumerable <object[]> worker = ExecMulti(proc, reader, mapper, onReadFields, progress: null);
var numerator = worker.GetEnumerator();
Guard.Check(numerator.Current == null);
while (numerator.MoveNext())
{
object[] record = numerator.Current;
T obj = (T)Activator.CreateInstance <T>();
yield return(obj);
}
}
public bool Prepare(SqlProc proc, Action <SqlTableMapper, DbDataReader> parser = null)
{
if (state != StateExec.Init)
{
this.Dispose();
}
Context db = proc.Context;
conn = new SqlConnection(db.ConnectionString());
conn.Open();
if (conn.State != ConnectionState.Open)
{
return(false);
}
conn.ChangeDatabase(db.DbName);
cmd = proc.CreateCommand();
cmd.Connection = conn;
dataReader = cmd.ExecuteReader(CommandBehavior.SchemaOnly | CommandBehavior.KeyInfo);
if (parser != null)
{
base.propertiesParser = parser;
}
(this as IDataMapHelper <object[]>).GetProperties(dataReader);
// CommandBehavior.
// SingleResult = 1, The query returns a single result set.
// SchemaOnly = 2,
// The query returns column information only. When using System.Data.CommandBehavior.SchemaOnly,
// the .NET Framework Data Provider for SQL Server precedes the statement being
// executed with SET FMTONLY ON.
// KeyInfo = 4,
// The query returns column and primary key information.
// SingleRow = 8,
// The query is expected to return a single row of the first result set. Execution
// of the query may affect the database state. Some .NET Framework data providers
// may, but are not required to, use this information to optimize the performance
// of the command. When you specify System.Data.CommandBehavior.SingleRow with
// the System.Data.OleDb.OleDbCommand.ExecuteReader() method of the System.Data.OleDb.OleDbCommand
// object, the .NET Framework Data Provider for OLE DB performs binding using
// the OLE DB IRow interface if it is available. Otherwise, it uses the IRowset
// interface. If your SQL statement is expected to return only a single row,
// specifying System.Data.CommandBehavior.SingleRow can also improve application
// performance. It is possible to specify SingleRow when executing queries that
// are expected to return multiple result sets. In that case, where both a multi-result
// set SQL query and single row are specified, the result returned will contain
// only the first row of the first result set. The other result sets of the
// query will not be returned.
dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection | CommandBehavior.SingleResult);
state = StateExec.Prepared;
return(true);
}
public static IEnumerable <object[]> ResultObj(this SqlProc proc
, Action <SqlField[]> onReadFields = null)
{
var cmd = proc.CreateCommand();
var helper = new DbDataMapHelper <object[]>();
return(DbGetHelper.ExecEnumerable(proc, helper, onReadFields: onReadFields));
}
public object[] FirstRecord(SqlProc proc, Action <SqlTableMapper, DbDataReader> parser = null, int?commandTimeout = null)
{
if (parser != null)
{
base.propertiesParser = parser;
}
LastRow = null;
var conn = OpenConnection(proc.Context);
if (conn == null)
{
return(LastRow);
}
using (var cmd = proc.CreateCommand())
{
cmd.Connection = conn;
if (commandTimeout.HasValue)
{
cmd.CommandTimeout = commandTimeout.Value;
}
cmd.Prepare();
var dataReader = cmd.ExecuteReader(CommandBehavior.SingleRow); // CommandBehavior.SchemaOnly | CommandBehavior.KeyInfo);
(this as IDataMapHelper <object[]>).GetProperties(dataReader);
// CommandBehavior.
// SingleResult = 1, The query returns a single result set.
// SchemaOnly = 2,
// The query returns column information only. When using System.Data.CommandBehavior.SchemaOnly,
// the .NET Framework Data Provider for SQL Server precedes the statement being
// executed with SET FMTONLY ON.
// KeyInfo = 4,
// The query returns column and primary key information.
// SingleRow = 8,
// The query is expected to return a single row of the first result set. Execution
// may, but are not required to, use this information to optimize the performance
// of the command. When you specify System.Data.CommandBehavior.SingleRow with
// the System.Data.OleDb.OleDbCommand.ExecuteReader() method of the System.Data.OleDb.OleDbCommand
// object, the .NET Framework Data Provider for OLE DB performs binding using
// the OLE DB IRow interface if it is available. Otherwise, it uses the IRowset
// interface. If your SQL statement is expected to return only a single row, also improve performance.
var helper = this;
object[] objVal = helper.DbRecordArray();
dataReader.GetValues(objVal);
LastRow = helper.SetValues(objVal);
cmd.Cancel();
// The name/value pair "Asynchronous Processing=true" was not included within
dataReader.Dispose();
}
return(LastRow);
}
// in T
public static bool ExecFill <T>(this SqlProc proc, IList <T> list,
IDataMapHelper <T> mapper,
Action <double> progress = null) where T : class // , TMap : IDataMapHelper
{
Ai.Assert.IsTrue(proc.Connection != null, "proc.Connection null error in ExecFill");
Ai.Assert.IsTrue(list != null, "list null error in ExecFill");
if (proc.Connection.State != ConnectionState.Open)
{
proc.Connection.Open();
}
using (var command = proc.CreateCommand())
{
if (progress != null)
{
progress(0.0);
}
using (SqlDataReader dataReader = command.ExecuteReader())
{
var results = list as IList <T>;
if (!dataReader.Read())
{
if (progress != null)
{
progress(1.0);
}
return(false);
}
// Task<T> GetFieldValueAsync<T>(int i, CancellationToken cancellationToken);
var helper = mapper; // new DbDataMapHelper<T>();
helper.GetProperties(dataReader);
do
{
object[] objVal = helper.DbRecordArray();
// int ret =
dataReader.GetValues(objVal);
T val = helper.SetValues(objVal);
results.Add(val);
}while (dataReader.Read());
if (progress != null)
{
progress(1.0);
}
return(true);
}
}
}
public static DataTable Result(this SqlProc proc)
{
var cmd = proc.CreateCommand();
var table = new DataTable();
if (cmd.Connection.State != ConnectionState.Open)
{
Assert.IsTrue(cmd.Connection.ConnectionString.Length > 0);
cmd.Connection.Open();
if (cmd.Connection.State != ConnectionState.Open)
{
return(null);
}
}
var reader = cmd.ExecuteReader();
// Load(IDataReader reader, LoadOption loadOption);
table.Load(reader, LoadOption.OverwriteChanges);
return(table);
}
public static IEnumerable <object[]> ExecEnumerable(this SqlProc proc,
IDataMapHelper <object[]> mapper,
Action <double> progress = null)
{
Ai.Assert.IsTrue(proc.Connection != null, "proc.Connection null error in ExecFill");
// Ai.Assert.IsTrue(list != null, "list null error in ExecFill");
using (SqlConnection connection = new SqlConnection(proc.Context.ConnectionString()))
{
proc.Connection = connection;
if (proc.Connection.State != ConnectionState.Open)
{
proc.Connection.Open();
if (proc.Connection.State != ConnectionState.Open)
{
yield break;
}
}
if (proc.Connection.Database != proc.Context.DbName)
{
proc.Connection.ChangeDatabase(proc.Context.DbName);
}
using (var command = proc.CreateCommand())
{
if (progress != null)
{
progress(0.0);
}
using (SqlDataReader dataReader = command.ExecuteReader())
{
if (!dataReader.Read())
{
if (progress != null)
{
progress(1.0);
}
yield break;
}
// Task<T> GetFieldValueAsync<T>(int i, CancellationToken cancellationToken);
var helper = mapper; // new DbDataMapHelper<T>();
helper.GetProperties(dataReader);
// int records = dataReader.RecordsAffected;
do
{
object[] objVal = helper.DbRecordArray();
dataReader.GetValues(objVal);
object[] val = helper.SetValues(objVal);
yield return(val);
}while (dataReader.Read());
if (progress != null)
{
progress(1.0);
}
}
}
}
}