public static SqlObjQueryParameters WithParser(SqlProc proc, Action <SqlTableMapper, DbDataReader> propertiesParser = null)
{
return(new SqlObjQueryParameters()
{
proc = proc, mapper = new SqlObjectReader(propertiesParser)
});
}
public static DataTable Execute(this Context context, SqlProc procedure, bool parseError)
{
if (parseError)
{
try
{
context.LastError = null;
if (procedure.Connection == null)
{
procedure.Connection = context.SqlConnection;
if (procedure.Connection == null)
{
return(null);
}
}
Trace.WriteLine("SqlProc: " + procedure.CmdText);
return(procedure.Result());
}
catch (Exception ex)
{
Trace.Write("Error in " + procedure.CmdText + " : " + ex.Message);
context.LastError = ex;
}
}
return(null);
}
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);
}
}
private bool ExecProc(SqlProc proc, int iFrom = 0, int iTake = 0)
{
var fld = this.Fields;
// && Rows.Count == 0
Ai.Assert.IsTrue(Rows != null, "SqlTable rows error");
Action <SqlTableMapper, DbDataReader> parser = (map, dbReader) =>
{
int len = dbReader.FieldCount;
Array.Resize <SqlField>(ref fld, len);
for (int i = 0; i < len; i++)
{
fld[i] = new SqlField()
{
Ordinal = i,
Name = dbReader.GetName(i),
Type = dbReader.GetFieldType(i),
Caption = dbReader.GetName(i),
Hide = false
};
}
this.Fields = fld;
};
bool init = this.queryParam.Prepare(proc, parser);
Ai.Assert.IsTrue(fld.Length > 0);
return(init);
// return enumerable.Any<object[]>();
}
public static Task <IList <T> > ExecAsync <T>(this Context context, SqlProc procedure, IList <T> list,
TaskScheduler scheduler, Action <double> progress) where T : class
{
Func <IList <T> > func = () =>
{
var conn = new SqlConnection();
conn.ConnectionString = context.ConnectionString();
if (context.OnBeforeOpen != null)
{
context.OnBeforeOpen(context, new SqlConnEventArgs(conn));
}
conn.Open();
var procClone = new SqlProc()
{
CmdText = procedure.CmdText, Connection = conn, Param = procedure.Param
};
Trace.WriteLine("SqlProc: " + procClone.CmdText);
var res = procClone.Exec <T>(list, progress); // .Result;
return(res);
};
var task = Task.Factory.StartNew <IList <T> >(func, CancellationToken.None, TaskCreationOptions.LongRunning, scheduler);
return(task);
}
public static MultiResult <T> MultiExec <T>(this SqlProc proc) where T : class
{
MultiResult <T> result = new MultiResult <T>();
result.Prepare(proc);
return(result);
}
public static SqlProc ProcNamed(object namedParam, Context db = null)
{
var properties = new NameProperties(namedParam);
if (properties.List.Count == 0)
{
return(null);
}
string cmdText = properties.GetValue(namedParam, properties.FirstName()) as string;
Ai.Guard.Check(cmdText.Length > 4);
var proc = SqlProcExt.CmdText(cmdText);
var listParam = new List <SqlParameter>();
foreach (string itemName in properties.Names(1))
{
var val = Ai.Reflection.Utils.GetPropertyValue(namedParam, itemName);
if (val != null)
{
listParam.Add(SqlProc.AddWithValue("@" + itemName, val));
}
}
proc.Param = listParam;
proc.Context = db;
return(proc);
}
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 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);
}
public static MultiResult <object[]> MultiObj(this Context db, string proc, Action <SqlCommand> setup = null)
{
var procMap = new SqlProc {
Context = db, Connection = db.SqlConnection, CmdText = proc, Param = null
};
var result = new MultiResult <object[]>();
return(result.Prepare(procMap, setup));
}
public static SqlProc WithParam(this SqlProc proc, object namedParam)
{
if (namedParam != null)
{
proc.Param = NameProperties.Parse(namedParam);
}
return(proc);
}
public static DbObject Exec(SqlProc proc, Action <double> progress = null)
{
var mapper = new DbObject();
mapper.Worker = DbGetHelper.ExecEnumerable(proc, mapper, progress).GetEnumerator();
if (mapper.Worker == null)
{
return(null);
}
return(mapper);
}
public static MultiResult <T> MultiExec <T>(this Context db, SqlCommand cmd) where T : class
{
var proc = new SqlProc
{
Connection = new SqlConnection(db.ConnectionString()),
CmdText = cmd.CommandText,
Param = SqlProc.CloneParam(cmd.Parameters)
};
return(MultiExec <T>(proc));
}
// 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 async Task <DataTable> ExecuteAsync(this Context context
, SqlProc procedure, bool parseError = true)
{
if (procedure.Connection == null)
{
procedure.Connection = context.SqlConnection;
if (procedure.Connection == null)
{
return(null);
}
}
Trace.WriteLine("SqlProc: " + procedure.CmdText);
if (procedure.Connection.State != ConnectionState.Open)
{
Task <object> task = OpenAsync(context, procedure.Connection); // , CancellationToken.None);
await task;
if (task.Exception != null)
{
var tcs = new TaskCompletionSource <DataTable>();
tcs.SetException(task.Exception);
context.LastError = task.Exception;
if (!parseError)
{
throw task.Exception;
}
return(await tcs.Task);
}
}
if (!parseError)
{
return(await Task.Factory.StartNew(() =>
{
return procedure.Result();
}));
}
try
{
return(await Task.Factory.StartNew(() =>
{
return procedure.Result();
}));
}
catch (Exception ex)
{ context.LastError = ex; }
return(null);
}
public static DbObject Exec(SqlProc proc, Action <double> progress = null, bool withFirst = true)
{
var mapper = new DbObject();
mapper.Worker = DbGetHelper.ExecEnumerable(proc, mapper, progress).GetEnumerator();
if (mapper.Worker == null)
{
return(null);
}
if (withFirst && mapper.Worker.Current == null)
{
mapper.Worker.MoveNext();
}
return(mapper);
}
public static DataTable Execute(this Context context, SqlProc procedure)
{
if (procedure.Connection == null)
{
procedure.Connection = context.SqlConnection;
if (procedure.Connection == null ||
string.IsNullOrEmpty(procedure.Connection.ConnectionString))
{
return(null);
}
}
Trace.WriteLine("SqlProc: " + procedure.CmdText);
return(procedure.Result());
}
public static SqlProc CmdText(string cmdText, Context context = null, Action <SqlProc> setup = null)
{
var proc = new SqlProc()
{
CmdText = cmdText,
Context = context,
Connection = context == null ? null : context.SqlConnection,
Param = null
};
if (setup != null)
{
setup(proc);
}
return(proc);
}
public static SqlProc WithParam(this SqlProc proc, string name, object value)
{
if (proc.Param == null)
{
proc.Param = new List <SqlParameter>()
{
SqlProc.AddWithValue(name, value)
}
}
;
else
{
proc.Param.Add(SqlProc.AddWithValue(name, value));
}
return(proc);
}
public IList <T> Exec <T>(SqlProc procedure) where T : class
{
if (procedure.Connection == null)
{
procedure.Connection = SqlConnection;
if (procedure.Connection == null)
{
throw new ArgumentNullException("procedure.Connection");
}
}
Trace.WriteLine("SqlProc: " + procedure.CmdText);
var list = new Collection <T>();
return(procedure.Exec <T>(list, null)); // .Result;
}
public bool ExecNamed(DbContext context, object paramNamed, int iFrom = 0, int iTake = 0)
{
var prop = new NameProperties(paramNamed);
string execName = prop.GetValue(paramNamed, prop.FirstName()) as string;
var proc = SqlProcExt.CmdText(execName, context);
var paramList = new List <SqlParameter>();
foreach (string item in prop.Names(1))
{
paramList.Add(SqlProc.AddWithValue("@" + item, prop.GetValue(paramNamed, item)));
}
if (paramList.Count > 0)
{
proc.Param = paramList;
}
return(ExecProc(proc, iFrom, iTake));
}
public MultiResult <T> Prepare(SqlProc proc
, Action <SqlCommand> setup = null
, Action <SqlField[]> onReadFields = null
, bool noMoveFirst = false
)
{
this.proc = proc;
var mapper = new DbObject();
numerator = DbEnumeratorData.GetEnumerator(() =>
{
this.reader = SqlMultiDyn.ExecMultiReader(proc, setup, progress: null);
return(reader);
});
if (numerator == null)
{
return(null);
}
if (reader.IsClosed || reader.Depth != 0)
{
return(null);
}
if (noMoveFirst)
{
return(this);
}
numerator.MoveNext();
var rec = numerator.Current as object[]; // DbDataRecord;
mapHelper = mapper.GetProperties(reader);
Fields = reader.GetFields(); // SqlFieldArray
FirstRecord = rec;
// mapHelper.SetValues(mapHelper.DbRecordArray());
return(this);
}
public static List <SqlParameter> Parse(object namedParam, int skipFrom = 0)
{
var properties = new NameProperties(namedParam);
if (properties.List.Count == 0)
{
return(null);
}
var listParam = new List <SqlParameter>();
foreach (string itemName in properties.Names(skipFrom))
{
var val = Utils.GetPropertyValue(namedParam, itemName);
if (val != null)
{
listParam.Add(SqlProc.AddWithValue("@" + itemName, val));
}
}
return(listParam);
}
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);
}
// Sql data set to multiple XML
public static MultiResult <XElement> MultiXElem(this SqlProc proc)
{
var result = new MultiResult <XElement>();
return(result.Prepare(proc));
}
public static MultiResult <object[]> MultiObj(this SqlProc proc)
{
var result = new MultiResult <object[]>();
return(result.Prepare(proc));
}
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);
}
}
}
}
}
public bool Prepare(SqlProc proc, Action <SqlTableMapper, DbDataReader> parser = null)
{
this.proc = proc;
return(IsEmpty() || mapper.Prepare(proc, parser));
}
