/// <summary>
        /// Populates OracleCommand with parameters from CRUD Query object
        /// Note: this code was purposely made provider specific because other providers may treat some nuances differently
        /// </summary>
        public void PopulateParameters(OracleCommand cmd, Query query)
        {
            foreach (var par in query.Where(p => p.HasValue))
            {
                cmd.Parameters.Add(par.Name, par.Value);
            }

            if (query.StoreKey != null)
            {
                var where        = GeneratorUtils.KeyToWhere(query.StoreKey, cmd.Parameters);
                cmd.CommandText += "\n WHERE \n {0}".Args(where);
            }

            CRUDGenerator.ConvertParameters(Store, cmd.Parameters);
        }
        public override Cursor OpenCursor(ICRUDQueryExecutionContext context, Query query)
        {
            var ctx    = (OracleCRUDQueryExecutionContext)context;
            var target = ctx.DataStore.TargetName;

            Schema.FieldDef[] toLoad;
            Schema            schema = null;
            OracleDataReader  reader = null;
            var cmd = ctx.Connection.CreateCommand();

            try
            {
                cmd.CommandText = Source.StatementSource;

                PopulateParameters(cmd, query);

                cmd.Transaction = ctx.Transaction;

                try
                {
                    reader = cmd.ExecuteReader();
                    GeneratorUtils.LogCommand(ctx.DataStore, "queryhandler-ok", cmd, null);
                }
                catch (Exception error)
                {
                    GeneratorUtils.LogCommand(ctx.DataStore, "queryhandler-error", cmd, error);
                    throw;
                }


                schema = GetSchemaForQuery(target, query, reader, Source, out toLoad);
            }
            catch
            {
                if (reader != null)
                {
                    reader.Dispose();
                }
                cmd.Dispose();
                throw;
            }

            var enumerable = execEnumerable(ctx, cmd, reader, schema, toLoad, query);

            return(new OracleCursor(ctx, cmd, reader, enumerable));
        }
示例#3
0
        private static async Task <int> crudDelete(OracleDataStoreBase store, OracleConnection cnn, OracleTransaction trans, Doc doc, IDataStoreKey key)
        {
            var    target    = store.TargetName;
            string tableName = store.AdjustObjectNameCasing(getTableName(doc.Schema, target));

            using (var cmd = cnn.CreateCommand())
            {
                var pk = key ?? doc.GetDataStoreKey(target);

                if (pk == null)
                {
                    throw new OracleDataAccessException(StringConsts.KEY_UNAVAILABLE_ERROR);
                }

                var where = GeneratorUtils.KeyToWhere(pk, cmd.Parameters);

                cmd.Transaction = trans;
                if (!string.IsNullOrEmpty(where))
                {
                    cmd.CommandText = string.Format("DELETE FROM \"{0}\" T1 WHERE {1}", tableName, where);
                }
                else
                {
                    cmd.CommandText = string.Format("DELETE FROM \"{0}\" T1", tableName);
                }

                ConvertParameters(store, cmd.Parameters);

//dbg(cmd);

                try
                {
                    var affected = await cmd.ExecuteNonQueryAsync().ConfigureAwait(false);

                    GeneratorUtils.LogCommand(store, "delete-ok", cmd, null);
                    return(affected);
                }
                catch (Exception error)
                {
                    GeneratorUtils.LogCommand(store, "delete-error", cmd, error);
                    throw;
                }
            }//using command
        }
        public override Schema GetSchema(ICRUDQueryExecutionContext context, Query query)
        {
            var ctx    = (OracleCRUDQueryExecutionContext)context;
            var target = ctx.DataStore.TargetName;

            using (var cmd = ctx.Connection.CreateCommand())
            {
                cmd.CommandText = Source.StatementSource;


                PopulateParameters(cmd, query);



                cmd.Transaction = ctx.Transaction;

                OracleDataReader reader = null;

                try
                {
                    reader = cmd.ExecuteReader(CommandBehavior.SchemaOnly);
                    GeneratorUtils.LogCommand(ctx.DataStore, "queryhandler-ok", cmd, null);
                }
                catch (Exception error)
                {
                    GeneratorUtils.LogCommand(ctx.DataStore, "queryhandler-error", cmd, error);
                    throw;
                }


                using (reader)
                {
                    Schema.FieldDef[] toLoad;
                    return(GetSchemaForQuery(target, query, reader, Source, out toLoad));
                } //using reader
            }     //using command
        }
示例#5
0
        private static async Task <int> crudUpsert(OracleDataStoreBase store, OracleConnection cnn, OracleTransaction trans, Doc doc, FieldFilterFunc filter)
        {
            var target  = store.TargetName;
            var cnames  = new StringBuilder();
            var values  = new StringBuilder();
            var upserts = new StringBuilder();
            var vparams = new List <OracleParameter>();
            var vpidx   = 0;

            foreach (var fld in doc.Schema.FieldDefs)
            {
                var fattr = fld[target];
                if (fattr == null)
                {
                    continue;
                }

                if (fattr.StoreFlag != StoreFlag.LoadAndStore && fattr.StoreFlag != StoreFlag.OnlyStore)
                {
                    continue;
                }


                if (filter != null)
                {
                    if (!filter(doc, null, fld))
                    {
                        continue;
                    }
                }

                var fname = fld.GetBackendNameForTarget(target);

                fname = store.AdjustObjectNameCasing(fname);

                var converted = getDbFieldValue(doc, fld, fattr, store);


                cnames.AppendFormat(" \"{0}\",", fname);

                if (converted.value != null)
                {
                    var pname = string.Format(":VAL{0}", vpidx);

                    values.AppendFormat(" {0},", pname);

                    if (!fattr.Key)
                    {
                        upserts.AppendFormat(" \"{0}\" = {1},", fname, pname);
                    }

                    var par = new OracleParameter();
                    par.ParameterName = pname;
                    par.Value         = converted.value;
                    if (converted.dbType.HasValue)
                    {
                        par.OracleDbType = converted.dbType.Value;
                    }
                    vparams.Add(par);

                    vpidx++;
                }
                else
                {
                    values.Append(" NULL,");
                    upserts.AppendFormat(" \"{0}\" = NULL,", fname);
                }
            }//foreach

            if (cnames.Length > 0 && upserts.Length > 0)
            {
                cnames.Remove(cnames.Length - 1, 1);   // remove ","
                upserts.Remove(upserts.Length - 1, 1); // remove ","
                values.Remove(values.Length - 1, 1);   // remove ","
            }
            else
            {
                return(0);//nothing to do
            }
            string tableName = store.AdjustObjectNameCasing(getTableName(doc.Schema, target));

            using (var cmd = cnn.CreateCommand())
            {
                var sql =
                    @"INSERT INTO ""{0}"" ({1}) VALUES ({2}) ON DUPLICATE KEY UPDATE {3}".Args(tableName, cnames, values, upserts);

                cmd.Transaction = trans;
                cmd.CommandText = sql;
                cmd.Parameters.AddRange(vparams.ToArray());
                //   ConvertParameters(store, cmd.Parameters);

                try
                {
                    var affected = await cmd.ExecuteNonQueryAsync().ConfigureAwait(false);

                    GeneratorUtils.LogCommand(store, "upsert-ok", cmd, null);
                    return(affected);
                }
                catch (Exception error)
                {
                    GeneratorUtils.LogCommand(store, "upsert-error", cmd, error);
                    throw;
                }
            }//using command
        }
示例#6
0
        private static async Task <int> crudUpdate(OracleDataStoreBase store, OracleConnection cnn, OracleTransaction trans, Doc doc, IDataStoreKey key, FieldFilterFunc filter)
        {
            var target  = store.TargetName;
            var values  = new StringBuilder();
            var vparams = new List <OracleParameter>();
            var vpidx   = 0;

            foreach (var fld in doc.Schema.FieldDefs)
            {
                var fattr = fld[target];
                if (fattr == null)
                {
                    continue;
                }

                var fname = fld.GetBackendNameForTarget(target);

                //20141008 DKh Skip update of key fields
                //20160124 DKh add update of keys if IDataStoreKey is present
                if (fattr.Key && !GeneratorUtils.HasFieldInNamedKey(fname, key))
                {
                    continue;
                }

                fname = store.AdjustObjectNameCasing(fname);

                if (fattr.StoreFlag != StoreFlag.LoadAndStore && fattr.StoreFlag != StoreFlag.OnlyStore)
                {
                    continue;
                }

                if (filter != null)
                {
                    if (!filter(doc, key, fld))
                    {
                        continue;
                    }
                }


                var converted = getDbFieldValue(doc, fld, fattr, store);


                if (converted.value != null)
                {
                    var pname = string.Format(":VAL{0}", vpidx);

                    values.AppendFormat(" \"{0}\" = {1},", fname, pname);

                    var par = new OracleParameter();
                    par.ParameterName = pname;
                    par.Value         = converted.value;
                    if (converted.dbType.HasValue)
                    {
                        par.OracleDbType = converted.dbType.Value;
                    }
                    vparams.Add(par);

                    vpidx++;
                }
                else
                {
                    values.AppendFormat(" \"{0}\" = NULL,", fname);
                }
            }//foreach

            if (values.Length > 0)
            {
                values.Remove(values.Length - 1, 1);// remove ","
            }
            else
            {
                return(0);//nothing to do
            }
            string tableName = store.AdjustObjectNameCasing(getTableName(doc.Schema, target));

            using (var cmd = cnn.CreateCommand())
            {
                var sql = string.Empty;

                var pk = key ?? doc.GetDataStoreKey(target);

                if (pk == null)
                {
                    throw new OracleDataAccessException(StringConsts.KEY_UNAVAILABLE_ERROR);
                }

                var where = GeneratorUtils.KeyToWhere(pk, cmd.Parameters);

                if (!string.IsNullOrEmpty(where))
                {
                    sql = "UPDATE \"{0}\" T1  SET {1} WHERE {2}".Args(tableName, values, where);
                }
                else
                {
                    throw new OracleDataAccessException(StringConsts.BROAD_UPDATE_ERROR);//20141008 DKh BROAD update
                }
                cmd.Transaction = trans;
                cmd.CommandText = sql;
                cmd.Parameters.AddRange(vparams.ToArray());
                //  ConvertParameters(store, cmd.Parameters);
                //https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9539482000346124162

//dbg(cmd);

                try
                {
                    var affected = await cmd.ExecuteNonQueryAsync().ConfigureAwait(false);

                    GeneratorUtils.LogCommand(store, "update-ok", cmd, null);
                    return(affected);
                }
                catch (Exception error)
                {
                    GeneratorUtils.LogCommand(store, "update-error", cmd, error);
                    throw;
                }
            }//using command
        }
示例#7
0
        private static int crudUpdate(OracleDataStoreBase store, OracleConnection cnn, OracleTransaction trans, Doc doc, IDataStoreKey key, FieldFilterFunc filter)
        {
            var target  = store.TargetName;
            var values  = new StringBuilder();
            var vparams = new List <OracleParameter>();
            var vpidx   = 0;

            foreach (var fld in doc.Schema.FieldDefs)
            {
                var fattr = fld[target];
                if (fattr == null)
                {
                    continue;
                }

                var fname = fld.GetBackendNameForTarget(target);

                //20141008 DKh Skip update of key fields
                //20160124 DKh add update of keys if IDataStoreKey is present
                if (fattr.Key && !GeneratorUtils.HasFieldInNamedKey(fname, key))
                {
                    continue;
                }

                if (fattr.StoreFlag != StoreFlag.LoadAndStore && fattr.StoreFlag != StoreFlag.OnlyStore)
                {
                    continue;
                }

                if (filter != null)
                {
                    if (!filter(doc, key, fld))
                    {
                        continue;
                    }
                }


                var fvalue = getFieldValue(doc, fld.Order, store);


                if (fvalue != null)
                {
                    var pname = string.Format("?VAL{0}", vpidx);

                    values.AppendFormat(" `{0}` = {1},", fname, pname);

                    var par = new OracleParameter();
                    par.ParameterName = pname;
                    par.Value         = fvalue;
                    vparams.Add(par);

                    vpidx++;
                }
                else
                {
                    values.AppendFormat(" `{0}` = NULL,", fname);
                }
            }//foreach

            if (values.Length > 0)
            {
                values.Remove(values.Length - 1, 1);// remove ","
            }
            else
            {
                return(0);//nothing to do
            }
            string tableName = getTableName(doc.Schema, target);

            using (var cmd = cnn.CreateCommand())
            {
                var sql = string.Empty;

                var pk = key ?? doc.GetDataStoreKey(target);

                if (pk == null)
                {
                    throw new OracleDataAccessException(StringConsts.KEY_UNAVAILABLE_ERROR);
                }

                var where = GeneratorUtils.KeyToWhere(pk, cmd.Parameters);

                if (!string.IsNullOrEmpty(where))
                {
                    sql = "UPDATE `{0}` T1  SET {1} WHERE {2}".Args(tableName, values, where);
                }
                else
                {
                    throw new OracleDataAccessException(StringConsts.BROAD_UPDATE_ERROR);//20141008 DKh BROAD update
                }
                cmd.Transaction = trans;
                cmd.CommandText = sql;
                cmd.Parameters.AddRange(vparams.ToArray());
                ConvertParameters(store, cmd.Parameters);

                try
                {
                    var affected = cmd.ExecuteNonQuery();
                    GeneratorUtils.LogCommand(store, "update-ok", cmd, null);
                    return(affected);
                }
                catch (Exception error)
                {
                    GeneratorUtils.LogCommand(store, "update-error", cmd, error);
                    throw;
                }
            }//using command
        }
示例#8
0
        private static int crudInsert(OracleDataStoreBase store, OracleConnection cnn, OracleTransaction trans, Doc doc, FieldFilterFunc filter)
        {
            var target  = store.TargetName;
            var cnames  = new StringBuilder();
            var values  = new StringBuilder();
            var vparams = new List <OracleParameter>();
            var vpidx   = 0;

            foreach (var fld in doc.Schema.FieldDefs)
            {
                var fattr = fld[target];
                if (fattr == null)
                {
                    continue;
                }

                if (fattr.StoreFlag != StoreFlag.LoadAndStore && fattr.StoreFlag != StoreFlag.OnlyStore)
                {
                    continue;
                }

                if (filter != null)
                {
                    if (!filter(doc, null, fld))
                    {
                        continue;
                    }
                }

                var fname = fld.GetBackendNameForTarget(target);

                var fvalue = getFieldValue(doc, fld.Order, store);


                cnames.AppendFormat(" `{0}`,", fname);

                if (fvalue != null)
                {
                    var pname = string.Format("?VAL{0}", vpidx);

                    values.AppendFormat(" {0},", pname);

                    var par = new OracleParameter();
                    par.ParameterName = pname;
                    par.Value         = fvalue;
                    vparams.Add(par);

                    vpidx++;
                }
                else
                {
                    values.Append(" NULL,");
                }
            }//foreach

            if (cnames.Length > 0)
            {
                cnames.Remove(cnames.Length - 1, 1); // remove ","
                values.Remove(values.Length - 1, 1); // remove ","
            }
            else
            {
                return(0);//nothing to do
            }
            string tableName = getTableName(doc.Schema, target);

            using (var cmd = cnn.CreateCommand())
            {
                var sql = "INSERT INTO `{0}` ({1}) VALUES ({2})".Args(tableName, cnames, values);

                cmd.Transaction = trans;
                cmd.CommandText = sql;
                cmd.Parameters.AddRange(vparams.ToArray());
                ConvertParameters(store, cmd.Parameters);
                try
                {
                    var affected = cmd.ExecuteNonQuery();
                    GeneratorUtils.LogCommand(store, "insert-ok", cmd, null);
                    return(affected);
                }
                catch (Exception error)
                {
                    GeneratorUtils.LogCommand(store, "insert-error", cmd, error);
                    throw;
                }
            }//using command
        }