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); } }