public void AddCmdParams(DbCommand dbCmd, SqlCommandData data)
{
var oraCmd = (OracleCommand)dbCmd;
if (data.ArrayBindCount > 0)
{
oraCmd.ArrayBindCount = data.ArrayBindCount;
}
oraCmd.BindByName = data.BindByName;
foreach (var prm in data.Params)
{
var opa = oraCmd.Parameters.Add(prm.name, ToOraDbType(prm.type));
opa.Value = prm.value;
var lstF = prm.value as float[];
if (lstF != null)
{
var sts = new OracleParameterStatus[lstF.Length];
for (int i = lstF.Length - 1; i >= 0; i--)
{
sts[i] = float.IsNaN(lstF[i]) ? OracleParameterStatus.NullInsert : OracleParameterStatus.Success;
}
opa.ArrayBindStatus = sts;
}
else if (Common.Utils.IsEmpty(prm.value))
{
opa.Status = OracleParameterStatus.NullInsert;
}
}
}
private static Fn FuncRawIntervalQuery(QueryTemplate qt)
{
return((IList args) =>
{
return (LazyAsync)(async ctx =>
{
var begTime = OPs.FromExcelDate(Convert.ToDouble(args[1]));
var endTime = OPs.FromExcelDate(Convert.ToDouble(args[2]));
var conn = (IDbConn)await ctx.GetValue(qt.connName);
var mq = qt.GetQuery(new object[] { args[0] }
, conn.dbms.TimeToSqlText(begTime)
, conn.dbms.TimeToSqlText(endTime)
);
if (mq == null)
{
return ValuesDictionary.Empties;
}
var cmd = new SqlCommandData()
{
Kind = CommandKind.Query,
ConvertMultiResultsToLists = qt.arrayResults
};
using (mq)
{
cmd.SqlText = mq.QueryText;
var res = await conn.ExecCmd(cmd, ctx.Cancellation);
if (((IList)res).Count == 0)
{
res = ValuesDictionary.Empties;
}
return res;
}
});
});
}
async Task <object> ExecCmdImplLogged(OneConn oc, SqlCommandData data, CancellationToken ct)
{
var sbSql = new System.Text.StringBuilder(data.SqlText);
sbSql.Replace('\r', ' ').Replace('\n', ' ');
int L;
do
{
L = sbSql.Length;
sbSql.Replace(" ", " ");
} while (L != sbSql.Length);
var sw = new System.Diagnostics.Stopwatch();
sw.Start();
try
{
var res = await ExecCmdImpl(oc, data, ct);
var lst = res as IList;
sbSql.Insert(0, string.Format("{0}\t{1}\t", sw.ElapsedMilliseconds, (lst == null) ? -1 : lst.Count));
Diagnostics.Logger.TraceEvent(System.Diagnostics.TraceEventType.Information, 1, sbSql.ToString());
//System.Diagnostics.Trace.WriteLine(sbSql.ToString());
return(res);
}
catch (DbException ex)
{
sbSql.Insert(0, string.Format("{0}\tERR\t", ex.Message));
Diagnostics.Logger.TraceEvent(System.Diagnostics.TraceEventType.Error, 1, sbSql.ToString());
throw;
}
}
public IEnumerable <DbCommand> GetSpecificCommands(DbConnection dbConn, SqlCommandData data)
{
var oraCmd = ((OracleConnection)dbConn).CreateCommand();
oraCmd.CommandText = data.SqlText;
if (data.ArrayBindCount > 0)
{
oraCmd.ArrayBindCount = data.ArrayBindCount;
}
oraCmd.BindByName = data.BindByName;
foreach (var prm in data.Params)
{
var opa = oraCmd.Parameters.Add(prm.name, ToOraDbType(prm.type));
opa.Value = prm.value;
if (prm.value is float[] lstF)
{
var sts = new OracleParameterStatus[lstF.Length];
for (int i = lstF.Length - 1; i >= 0; i--)
{
sts[i] = float.IsNaN(lstF[i]) ? OracleParameterStatus.NullInsert : OracleParameterStatus.Success;
}
opa.ArrayBindStatus = sts;
}
else if (prm.value is double[] lstD)
{
var sts = new OracleParameterStatus[lstD.Length];
for (int i = lstD.Length - 1; i >= 0; i--)
{
sts[i] = double.IsNaN(lstD[i]) ? OracleParameterStatus.NullInsert : OracleParameterStatus.Success;
}
opa.ArrayBindStatus = sts;
}
else if (prm.value is IList lst)
{
var sts = new OracleParameterStatus[lst.Count];
for (int i = lst.Count - 1; i >= 0; i--)
{
sts[i] = W.Common.Utils.IsEmpty(lst[i]) ? OracleParameterStatus.NullInsert : OracleParameterStatus.Success;
}
opa.ArrayBindStatus = sts;
}
else if (Common.Utils.IsEmpty(prm.value))
{
opa.Status = OracleParameterStatus.NullInsert;
}
}
yield return(oraCmd);
}
public async Task <object> ExecCmd(SqlCommandData data, CancellationToken ct)
{
await gate.WaitAsync(ct);
try
{
if (disposed != 0)
{
throw new ObjectDisposedException(this.GetType().Name);
}
#if TRACE
return(await conn.ExecCmdImplLogged(conn.Pool(i), data, ct));
#else
return(await conn.ExecCmdImpl(conn.Pool(i), data, ct));
#endif
}
finally { gate.Release(); }
}
public async Task <object> ExecCmd(SqlCommandData data, CancellationToken ct)
{
await gate.WaitAsync(ct);
try
{
int i = W.Common.Utils.InterlockedCaptureIndex(ref poolUsageBits, _pool.Length - 1);
if (i < UnusedConnections)
{
UnusedConnections = i;
}
try
#if TRACE
{ return(await ExecCmdImplLogged(Pool(i), data, ct)); }
#else
{ return(await ExecCmdImpl(Pool(i), data, ct)); }
#endif
finally
{ W.Common.Utils.InterlockedReleaseIndex(ref poolUsageBits, i); }
}
async Task <object> ExecCmdImpl(OneConn oc, SqlCommandData data, CancellationToken ct)
{
int sum = 0;
foreach (var dbCmd in dbms.GetSpecificCommands(oc.conn, data))
{
using (dbCmd)
try
{
dbCmd.Transaction = oc.GetTransaction(autoCommitPeriod);
try
{
dbCmd.CommandText = data.SqlText;
if (data.Kind == CommandKind.NonQuery)
{ // For MS SQL can be multiple INSERT commands due rows count limitation in one INSERT
sum += dbCmd.ExecuteNonQuery();
continue;
}
}
catch (Exception ex) { throw ex; }
if (data.Kind == CommandKind.GetSchema)
{
using (var rdr = dbCmd.ExecuteReader(System.Data.CommandBehavior.SchemaOnly))
return(rdr.GetSchemaTable());
}
using (var rdr = dbCmd.ExecuteReader())// (await SafeGetRdr(oraCmd, ct))
{
if (data.ConvertMultiResultsToLists)
{
return(ReadGroupedRows(rdr));
}
return(ReadRows(rdr));
}
}
finally { oc.Commit(); }
}
return(sum); // return from NonQuery
//throw new InvalidOperationException("You re not supposed to be here)");
}
public void AddCmdParams(DbCommand dbCmd, SqlCommandData data)
{
var sqlCmd = (SqlCommand)dbCmd;
if (data.ArrayBindCount > 0)
{
throw new NotImplementedException();
}
if (data.Params.Count > 0 && !data.BindByName)
{
throw new NotSupportedException("DbmsSpecificMsSql: only BindByName parameters binding supported");
}
foreach (var prm in data.Params)
{
var spa = sqlCmd.Parameters.Add(prm.name, ToMsSqlDbType(prm.type));
spa.Value = prm.value;
if (Common.Utils.IsEmpty(prm.value))
{
spa.SqlValue = DBNull.Value;// Status = OracleParameterStatus.NullInsert;
}
}
}
private static Fn FuncTimedSliceQuery(TimeSpan actuality, QueryTemplate qt)
{
return((IList args) =>
{
return (LazyAsync)(async ctx =>
{
var lst = args[0] as IList;
if (lst != null && lst.Count == 0)
{
return lst;
}
bool range = args.Count > 2;
var begTime = OPs.FromExcelDate(Convert.ToDouble(range ? args[2] : args[1]));
var minTime = range ? OPs.FromExcelDate(Convert.ToDouble(args[1])) : begTime - actuality;
var conn = (IDbConn)await ctx.GetValue(qt.connName);
var mq = qt.GetQuery(new object[] { args[0] }, conn.dbms.TimeToSqlText(minTime), conn.dbms.TimeToSqlText(begTime));
if (mq == null)
{
return ValuesDictionary.Empties;
}
var cmd = new SqlCommandData()
{
Kind = CommandKind.Query,
ConvertMultiResultsToLists = qt.arrayResults
};
using (mq)
{
cmd.SqlText = mq.QueryText;
var res = await conn.ExecCmd(cmd, ctx.Cancellation);
if (((IList)res).Count == 0)
{
res = ValuesDictionary.Empties;
}
return res;
}
});
});
}
public IEnumerable <DbCommand> GetSpecificCommands(DbConnection dbConn, SqlCommandData data)
{
int n = data.ArrayBindCount;
SqlCommand sqlCmd;
if (n == 0)
{
sqlCmd = ((SqlConnection)dbConn).CreateCommand();
sqlCmd.CommandText = data.SqlText;
yield return(sqlCmd);
yield break;
}
if (!data.SqlText.StartsWith("INSERT", StringComparison.OrdinalIgnoreCase))
{
throw new NotSupportedException("Only INSERT is supported by GetSpecificCommands for ArrayBindCount>0");
}
var prms = data.Params;
var sb = new System.Text.StringBuilder();
var lsts = prms.Select(p => p.value as IList).ToList();
int j = 0;
int k = 0;
sqlCmd = ((SqlConnection)dbConn).CreateCommand();
while (n > 0)
{
if (sb.Length == 0)
{
sb.Append(data.SqlText);
}
else
{
sb.AppendLine(",");
}
sb.Append('(');
for (int i = 0; i < prms.Count; i++)
{
if (i > 0)
{
sb.Append(',');
}
var v = lsts[i][j];
var ic = v as IConvertible;
if (ic != null)
{
var s = ic.ToString(System.Globalization.CultureInfo.InvariantCulture).Replace("'", "''");
switch (ic.GetTypeCode())
{
case TypeCode.String:
sb.Append('\''); sb.Append(s); sb.Append('\'');
break;
case TypeCode.Empty:
case TypeCode.DBNull:
sb.Append("NULL");
break;
default:
sb.Append(s);
break;
}
}
else if (v == null || v == DBNull.Value)
{
sb.Append("NULL");
}
else
{
sb.Append(v.ToString().Replace("'", "''"));
}
}
sb.Append(")");
k++;
j++;
if (k == 10000 || sb.Length >= 32768)
{
sqlCmd.CommandText = sb.ToString();
k = 0;
yield return(sqlCmd);
}
}
if (k > 0)
{
sqlCmd.CommandText = sb.ToString();
sb.Clear();
yield return(sqlCmd);
}
}