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(); GeneratorUtils.LogCommand(store, "delete-ok", cmd, null); return(affected); } catch (Exception error) { GeneratorUtils.LogCommand(store, "delete-error", cmd, error); throw; } }//using command }
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 }
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 }
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 = store.AdjustObjectNameCasing(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 = store.AdjustObjectNameCasing(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 }