public static SqlQueryParameters WithParser(ISqlProc proc, Action <SqlTableMapper, DbDataReader> propertiesParser = null)
{
return(new SqlQueryParameters()
{
proc = proc, mapper = new SqlObjTableReader(propertiesParser)
});
}
public static Func <SqlDataReader> LazyReader(ISqlProc proc
, Action <SqlDataReader> readerGet
, Action <SqlCommand> setupCmd = null
, Action <Exception> onError = null)
{
return(SqlMultiDyn.LazyReader(proc, readerGet, setupCmd, onError));
}
public static Func <SqlDataReader> LazyReader(this ISqlProc proc
, Action <SqlDataReader> readerGet
, Action <SqlCommand> setupCmd = null
, Action <Exception> onError = null)
{
return(new Func <SqlDataReader>(() =>
{
SqlDataReader reader = null;
string errorStr = null;
try
{
reader = ExecMultiReader(proc, setupCmd, onError: null, progress: null);
}
catch (Exception ex)
{
errorStr = String.Format("error in sql {0}: {1}", proc.CmdText ?? "???", ex.Message);
System.Diagnostics.Trace.Write(errorStr);
if (onError != null)
{
onError(ex);
}
}
// System.AppDomain.CurrentDomain.UnhandledException
if (reader == null || reader.IsClosed || reader.Depth != 0)
{
return null;
}
readerGet(reader);
return reader;
}));
}
public static IEnumerable <object[]> ExecMulti(this ISqlProc proc, SqlDataReader dataReader,
IDataMapHelper <object[]> mapper,
Action <SqlDataReader, SqlField[]> onReadFields = null,
Action <double> progress = null)
{
var helper = mapper;
helper.GetProperties(dataReader);
if (onReadFields != null)
{
onReadFields(dataReader, helper.GetFields(dataReader));
}
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 static ResultDyn ExecProcResultDyn(this ISqlProc proc
, Action <SqlCommand> setupCmd = null
, Action <Exception> onError = null)
{
SqlDataReader reader = null;
return(DbEnumeratorData.GetResultDyn(SqlMultiDyn.LazyReader(proc, (r) => reader = r, setupCmd, onError)));
}
public MultiResult(ISqlProc proc = null)
{
Count = 1;
// mapper = null;
this.proc = proc;
reader = null;
mapHelper = null;
}
// public static IEnumerator<ExpandoObject>
public static DbEnumeratorData <ExpandoObject> ResultDyn(this ISqlProc proc
, Action <SqlDataReader> readerGet, Action <SqlCommand> setupCmd = null
, Action <Exception> onError = null)
{
Func <SqlDataReader> lazyReader = LazyReader(proc, readerGet, setupCmd, onError);
var dynNumerator = new DbEnumeratorData <ExpandoObject>(lazyReader);
return(dynNumerator); // as IEnumerator<ExpandoObject>;
}
// in T
public static bool ExecFill <T>(this ISqlProc proc, IList <T> list,
IDataMapHelper <T> mapper,
Action <double> progress = null) where T : class // , TMap : IDataMapHelper
{
Ai.Guard.Check(proc.Connection != null, "proc.Connection null error in ExecFill");
Ai.Guard.Check(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 bool Prepare(ISqlProc proc, Action <SqlTableMapper, DbDataReader> parser = null, int?commandTimeout = null)
{
if (state != StateExec.Init)
{
this.Dispose();
}
if (parser != null)
{
base.propertiesParser = parser;
}
LastRow = null;
var conn = proc.OpenConnection();
if (conn == null)
{
return(false);
}
cmd = proc.CreateCommand();
cmd.Connection = conn;
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
if (commandTimeout.HasValue)
{
cmd.CommandTimeout = commandTimeout.Value;
}
cmd.Prepare();
dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection | CommandBehavior.SingleResult);
state = StateExec.Prepared;
if (dataReader.Read())
{
// First record
(this as IDataMapHelper <object[]>).GetProperties(dataReader);
var helper = this;
object[] objVal = helper.DbRecordArray();
// first record array
dataReader.GetValues(objVal);
LastRow = helper.SetValues(objVal);
return(true);
}
return(false);
}
private bool ExecProc(ISqlProc proc, int iFrom = 0, int iTake = 0)
{
Ai.Guard.Check(proc.CmdText.Length > 0, "SqlTable proc error");
Ai.Guard.Check(Fields != null, "SqlTable Fields error");
var fld = this.Fields;
Action <SqlTableMapper, DbDataReader> parser = (map, dbReader) =>
{
fld = SqlFieldArray.GetArray(dbReader);
this.Fields = fld;
};
bool init = this.queryParam.Prepare(proc, parser, this.Timeout);
Guard.Check(fld.Length > 0, "fld.Length error");
return(init);
}
public static SqlDataReader ExecuteWithReconnect(this ISqlProc proc)
{
var lastErr = proc.LastError;
var cmd = proc.CreateCommand() as SqlCommand;
var reader = ExecuteReaderRetry(cmd, proc,
retry: () => GetSqlCommandReconnect(proc));
lastErr = proc.LastError;
if (lastErr is SqlException)
{
throw lastErr;
}
else if (reader == null)
{
proc.LastError = new Exception(String.Format("procedure error {0}", proc));
throw proc.LastError;
}
return(reader);
}
public static ISqlProc PrepareWithReconnect(this ISqlProc proc, Context db)
{
var conn = proc.Connection;
if (conn == null)
{
proc.Connection = proc.OpenConnection();
}
conn = proc.Connection;
try
{
var dbName = conn.Database;
var cmd = new SqlCommand {
CommandText = "SELECT DB_NAME()"
}; // neutral command
var result = cmd.ExecuteScalar() as string;
}
catch (Exception ex) { proc.LastError = ex; }
return(proc);
}
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 SqlConnection PreparedSqlConnection(this ISqlProc proc)
{
if (proc == null)
{
return(null);
}
if (proc.Connection.IsReady())
{
return(proc.Connection as SqlConnection);
}
proc.Connection = proc.OpenConnection();
proc.CloseOnDispose = true;
if (proc is ISqlProcContext && string.IsNullOrWhiteSpace(proc.Connection.ConnectionString))
{
var procDb = proc as ISqlProcContext;
proc.Connection = procDb.Context.SqlConnection;
}
return(proc.Connection as SqlConnection);
}
public static SqlCommand GetSqlCommandReconnect(this ISqlProc proc)
{
proc.Connection = null;
proc.CloseOnDispose = true;
if (proc is SqlProc)
{
var sqlProc = proc as SqlProc;
if (sqlProc.Context != null)
{
sqlProc.Context.AssureOpen(withCommand: true);
sqlProc.Connection = sqlProc.Context.SqlConnection;
}
else
{
sqlProc.OpenConnection();
}
}
else
{
proc.Connection = proc.OpenConnection();
}
return(proc.CreateCommand() as SqlCommand);
}
public static IEnumerable <object[]> ExecEnumerable(this ISqlProc proc,
IDataMapHelper <object[]> mapper,
Action <double> progress = null, Action <SqlField[]> onReadFields = null)
{
Ai.Guard.Check(proc.Connection != null, "proc.Connection null error in ExecFill");
using (SqlConnection connection = new SqlConnection(proc.ConnectionString()))
{
if (connection.State != ConnectionState.Open)
{
connection.Open();
if (connection.State != ConnectionState.Open)
{
yield break;
}
}
if (connection.Database != proc.DbName)
{
connection.ChangeDatabase(proc.DbName);
}
using (var command = proc.CreateCommand())
{
command.Connection = connection;
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);
if (onReadFields != null)
{
onReadFields(helper.GetFields(dataReader));
}
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);
}
}
}
}
}
// Unsafe
public static SqlDataReader ExecMultiReader(this ISqlProc proc,
Action <SqlCommand> setup = null,
Action <Exception> onError = null,
Action <double> progress = null)
{
var command = proc.CreateCommand();
//if (command.Connection == null || command.Connection.State != ConnectionState.Open)
var connStr = proc.ConnectionString();
Guard.Check(!string.IsNullOrWhiteSpace(connStr), "Exec MultiReader connection error");
SqlConnection connection = new SqlConnection(connStr);
if (connection.State != ConnectionState.Open)
{
connection.Open();
if (connection.State != ConnectionState.Open)
{
return(null);
}
}
if (connection.Database != proc.DbName)
{
connection.ChangeDatabase(proc.DbName);
}
command.Connection = connection;
if (setup != null)
{
setup(command);
}
if (progress != null)
{
progress(0.0);
}
SqlDataReader dataReader = null;
if (onError != null)
{
try
{
dataReader = command.ExecuteReader(behavior: CommandBehavior.CloseConnection);
}
catch (Exception ex) { onError(ex); }
}
else
{
dataReader = command.ExecuteReader(behavior: CommandBehavior.CloseConnection);
}
if (dataReader != null && dataReader.IsClosed) // !dataReader.Read())
{
if (progress != null)
{
progress(1.0);
}
return(null);
}
return(dataReader);
}
public bool Prepare(ISqlProc proc, Action <SqlTableMapper, DbDataReader> parser = null, int?commandTimeout = null)
{
this.proc = proc;
return(IsEmpty() || mapper.Prepare(proc, parser, commandTimeout));
}
