public int ExecuteWithoutFetch(ICRUDQueryExecutionContext context, Query query) { var ctx = (MySQLCRUDQueryExecutionContext)context; using (var cmd = ctx.Connection.CreateCommand()) { cmd.CommandText = m_Source.StatementSource; PopulateParameters(cmd, query); cmd.Transaction = ctx.Transaction; try { var affected = cmd.ExecuteNonQuery(); GeneratorUtils.LogCommand(ctx.DataStore.LogLevel, "queryhandler-ok", cmd, null); return(affected); } catch (Exception error) { GeneratorUtils.LogCommand(ctx.DataStore.LogLevel, "queryhandler-error", cmd, error); throw; } } //using command }
public RowsetBase Execute(ICRUDQueryExecutionContext context, Query query, bool oneRow = false) { var ctx = (MySQLCRUDQueryExecutionContext)context; var target = ctx.DataStore.TargetName; using (var cmd = ctx.Connection.CreateCommand()) { cmd.CommandText = m_Source.StatementSource; PopulateParameters(cmd, query); cmd.Transaction = ctx.Transaction; MySqlDataReader reader = null; try { reader = oneRow ? cmd.ExecuteReader(CommandBehavior.SingleRow) : cmd.ExecuteReader(); GeneratorUtils.LogCommand(ctx.DataStore.LogLevel, "queryhandler-ok", cmd, null); } catch (Exception error) { GeneratorUtils.LogCommand(ctx.DataStore.LogLevel, "queryhandler-error", cmd, error); throw; } using (reader) return(PopulateRowset(ctx, reader, target, query, m_Source, oneRow)); } //using command }
public Cursor OpenCursor(ICRUDQueryExecutionContext context, Query query) { var ctx = (MySQLCRUDQueryExecutionContext)context; var target = ctx.DataStore.TargetName; Schema.FieldDef[] toLoad; Schema schema = null; MySqlDataReader reader = null; var cmd = ctx.Connection.CreateCommand(); try { cmd.CommandText = m_Source.StatementSource; PopulateParameters(cmd, query); cmd.Transaction = ctx.Transaction; try { reader = cmd.ExecuteReader(); GeneratorUtils.LogCommand(ctx.DataStore.LogLevel, "queryhandler-ok", cmd, null); } catch (Exception error) { GeneratorUtils.LogCommand(ctx.DataStore.LogLevel, "queryhandler-error", cmd, error); throw; } schema = GetSchemaForQuery(target, query, reader, m_Source, out toLoad); } catch { if (reader != null) { reader.Dispose(); } cmd.Dispose(); throw; } var enumerable = execEnumerable(ctx, cmd, reader, schema, toLoad, query); return(new MySQLCursor(ctx, cmd, reader, enumerable)); }
public static void Delete(MySQLDataStoreBase store, MySqlConnection cnn, ModelBase instance, IDataStoreKey key, object[] extra) { var record = GeneratorUtils.AsSuitableRecordInstance(instance, true); using (var cmd = cnn.CreateCommand()) { var pk = key ?? record.DataStoreKey; if (pk == null) { throw new MySQLDataAccessException(StringConsts.KEY_UNAVAILABLE_ERROR); } var where = GeneratorUtils.KeyToWhere(pk, cmd.Parameters); if (!string.IsNullOrEmpty(where)) { cmd.CommandText = string.Format("DELETE T1 FROM `{0}` T1 WHERE {1}", record.TableName, where); } else { cmd.CommandText = string.Format("DELETE T1 FROM `{0}` T1", record.TableName); } var affected = 0; try { affected = cmd.ExecuteNonQuery(); GeneratorUtils.LogCommand(store.LogLevel, "rmdelete-ok", cmd, null); } catch (Exception error) { GeneratorUtils.LogCommand(store.LogLevel, "rmdelete-error", cmd, error); throw; } if (affected == 0) { throw new MySQLDataAccessException(string.Format(StringConsts.NO_ROWS_AFFECTED_ERROR, "Delete")); } }//using command }
private static int crudDelete(MySQLDataStoreBase store, MySqlConnection cnn, MySqlTransaction trans, Row row, IDataStoreKey key) { var target = store.TargetName; string tableName = getTableName(row.Schema, target); using (var cmd = cnn.CreateCommand()) { var pk = key ?? row.GetDataStoreKey(target); if (pk == null) { throw new MySQLDataAccessException(StringConsts.KEY_UNAVAILABLE_ERROR); } var where = GeneratorUtils.KeyToWhere(pk, cmd.Parameters); cmd.Transaction = trans; if (!string.IsNullOrEmpty(where)) { cmd.CommandText = string.Format("DELETE T1 FROM `{0}` T1 WHERE {1}", tableName, where); } else { cmd.CommandText = string.Format("DELETE T1 FROM `{0}` T1", tableName); } ConvertParameters(store, cmd.Parameters); try { var affected = cmd.ExecuteNonQuery(); GeneratorUtils.LogCommand(store.LogLevel, "delete-ok", cmd, null); return(affected); } catch (Exception error) { GeneratorUtils.LogCommand(store.LogLevel, "delete-error", cmd, error); throw; } }//using command }
public Schema GetSchema(ICRUDQueryExecutionContext context, Query query) { var ctx = (MySQLCRUDQueryExecutionContext)context; var target = ctx.DataStore.TargetName; using (var cmd = ctx.Connection.CreateCommand()) { cmd.CommandText = m_Source.StatementSource; PopulateParameters(cmd, query); cmd.Transaction = ctx.Transaction; MySqlDataReader reader = null; try { reader = cmd.ExecuteReader(CommandBehavior.SchemaOnly); GeneratorUtils.LogCommand(ctx.DataStore.LogLevel, "queryhandler-ok", cmd, null); } catch (Exception error) { GeneratorUtils.LogCommand(ctx.DataStore.LogLevel, "queryhandler-error", cmd, error); throw; } using (reader) { Schema.FieldDef[] toLoad; return(GetSchemaForQuery(target, query, reader, m_Source, out toLoad)); } //using reader } //using command }
private static int crudUpsert(MySQLDataStoreBase store, MySqlConnection cnn, MySqlTransaction trans, Row row, FieldFilterFunc filter) { var target = store.TargetName; var cnames = new StringBuilder(); var values = new StringBuilder(); var upserts = new StringBuilder(); var vparams = new List <MySqlParameter>(); var vpidx = 0; foreach (var fld in row.Schema.FieldDefs) { var fattr = fld[target]; if (fattr == null) { continue; } if (fattr.StoreFlag != StoreFlag.LoadAndStore && fattr.StoreFlag != StoreFlag.OnlyStore) { continue; } if (filter != null)//20160210 Dkh+SPol { if (!filter(row, null, fld)) { continue; } } var fname = fld.GetBackendNameForTarget(target); var fvalue = getFieldValue(row, fld.Order, store); cnames.AppendFormat(" `{0}`,", fname); if (fvalue != null) { var pname = string.Format("?VAL{0}", vpidx); values.AppendFormat(" {0},", pname); if (!fattr.Key) { upserts.AppendFormat(" `{0}` = {1},", fname, pname); } var par = new MySqlParameter(); par.ParameterName = pname; par.Value = fvalue; 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 = getTableName(row.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 = cmd.ExecuteNonQuery(); GeneratorUtils.LogCommand(store.LogLevel, "upsert-ok", cmd, null); return(affected); } catch (Exception error) { GeneratorUtils.LogCommand(store.LogLevel, "upsert-error", cmd, error); throw; } }//using command }
private static int crudUpdate(MySQLDataStoreBase store, MySqlConnection cnn, MySqlTransaction trans, Row row, IDataStoreKey key, FieldFilterFunc filter) { var target = store.TargetName; var values = new StringBuilder(); var vparams = new List <MySqlParameter>(); var vpidx = 0; foreach (var fld in row.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)//20160210 Dkh+SPol { if (!filter(row, key, fld)) { continue; } } var fvalue = getFieldValue(row, fld.Order, store); if (fvalue != null) { var pname = string.Format("?VAL{0}", vpidx); values.AppendFormat(" `{0}` = {1},", fname, pname); var par = new MySqlParameter(); 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(row.Schema, target); using (var cmd = cnn.CreateCommand()) { var sql = string.Empty; var pk = key ?? row.GetDataStoreKey(target); if (pk == null) { throw new MySQLDataAccessException(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 MySQLDataAccessException(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.LogLevel, "update-ok", cmd, null); return(affected); } catch (Exception error) { GeneratorUtils.LogCommand(store.LogLevel, "update-error", cmd, error); throw; } }//using command }
public RowsetBase Execute(ICRUDQueryExecutionContext context, Query query, bool oneRow = false) { var ctx = (MySQLCRUDQueryExecutionContext)context; var target = ctx.DataStore.TargetName; Rowset result = null; using (var cmd = ctx.Connection.CreateCommand()) { cmd.CommandText = m_Source.StatementSource; PopulateParameters(cmd, query); cmd.Transaction = ctx.Transaction; MySqlDataReader reader = null; try { reader = oneRow ? cmd.ExecuteReader(CommandBehavior.SingleRow) : cmd.ExecuteReader(); GeneratorUtils.LogCommand(ctx.DataStore.LogLevel, "queryhandler-ok", cmd, null); } catch (Exception error) { GeneratorUtils.LogCommand(ctx.DataStore.LogLevel, "queryhandler-error", cmd, error); throw; } using (reader) { Schema.FieldDef[] toLoad; Schema schema = getSchema(target, query, reader, out toLoad); result = new Rowset(schema); while (reader.Read()) { var row = Row.MakeRow(schema, query.ResultRowType); for (int i = 0; i < reader.FieldCount; i++) { var fdef = toLoad[i]; if (fdef == null) { continue; } var val = reader.GetValue(i); if (fdef.NonNullableType == typeof(bool)) { row[fdef.Order] = val.AsNullableBool(); } else { row[fdef.Order] = val; } } result.Add(row); if (oneRow) { break; } } } //using reader } //using command return(result); }
/// <summary> /// Auto generates select sql and params. If sqlStatement!=null then params are added to that statement /// </summary> public static void Load(MySQLDataStoreBase store, MySqlConnection cnn, string sqlStatement, ModelBase instance, IDataStoreKey key, object[] extra) { var autoSql = string.IsNullOrEmpty(sqlStatement); var record = GeneratorUtils.AsSuitableRecordInstance(instance, autoSql); var select = new StringBuilder(); if (autoSql) { foreach (var fld in record.Fields) { if (fld.StoreFlag == StoreFlag.LoadAndStore || fld.StoreFlag == StoreFlag.OnlyLoad) { select.AppendFormat(" T1.`{0}`,", fld.FieldName); } } if (select.Length > 0) { select.Remove(select.Length - 1, 1);// remove "," } else { throw new MySQLDataAccessException(StringConsts.LOAD_NO_SELECT_COLUMNS_ERROR); } } var pk = key ?? record.DataStoreKey; if (pk == null) { throw new MySQLDataAccessException(StringConsts.KEY_UNAVAILABLE_ERROR); } using (var cmd = cnn.CreateCommand()) { var where = GeneratorUtils.KeyToWhere(pk, cmd.Parameters); if (autoSql) { cmd.CommandText = string.Format("SELECT {0} FROM `{1}` T1 WHERE {2}", select, record.TableName, where); } else { cmd.CommandText = string.Format(sqlStatement, where); } MySqlDataReader reader = null; try { reader = cmd.ExecuteReader(); GeneratorUtils.LogCommand(store.LogLevel, "rmload-ok", cmd, null); } catch (Exception error) { GeneratorUtils.LogCommand(store.LogLevel, "rmload-error", cmd, error); throw; } using (reader) { if (reader.Read()) { reader.CopyFieldsToRecordFields(record); } else { throw new MySQLDataAccessException(string.Format(StringConsts.LOADING_ENTITY_NOT_FOUND_ERROR, pk)); } } //using reader } //using command }
public static void Save(MySQLDataStoreBase store, MySqlConnection cnn, ModelBase instance, IDataStoreKey key, object[] extra) { var record = GeneratorUtils.AsSuitableRecordInstance(instance, true); if (record.LastPostedChange != ChangeType.Created && record.LastPostedChange != ChangeType.Edited) { throw new MySQLDataAccessException(string.Format(StringConsts.MODEL_INVALID_STATE_ERROR, "Created || Edited", instance.LastPostedChange)); } bool insert = instance.LastPostedChange == ChangeType.Created; var cnames = new StringBuilder(); var values = new StringBuilder(); var vparams = new List <MySqlParameter>(); var vpidx = 0; foreach (var fld in record.Fields) { if (fld.StoreFlag == StoreFlag.LoadAndStore || fld.StoreFlag == StoreFlag.OnlyStore) { if ( insert || fld.Modified ) { cnames.AppendFormat(" `{0}`,", fld.FieldName); if (fld.HasValue) { var pname = string.Format("?VAL{0}", vpidx); if (insert) { values.AppendFormat(" {0},", pname); } else { values.AppendFormat(" `{0}` = {1},", fld.FieldName, pname); } var par = new MySqlParameter(); par.ParameterName = pname; par.Value = fld.ValueAsObject; vparams.Add(par); vpidx++; } else { if (insert) { values.Append(" NULL,"); } else { values.AppendFormat(" `{0}` = NULL,", fld.FieldName); } } } } } if (cnames.Length > 0) { cnames.Remove(cnames.Length - 1, 1);// remove "," if (values.Length > 0) { values.Remove(values.Length - 1, 1); // remove "," } } else { return;//nothing has been modified } using (var cmd = cnn.CreateCommand()) { var sql = string.Empty; if (insert) //INSERT { sql = string.Format("INSERT INTO `{0}` ({1}) VALUES ({2})", record.TableName, cnames, values); } else //UPDATE { var pk = key ?? record.DataStoreKey; if (pk == null) { throw new MySQLDataAccessException(StringConsts.KEY_UNAVAILABLE_ERROR); } var where = GeneratorUtils.KeyToWhere(pk, cmd.Parameters); if (!string.IsNullOrEmpty(where)) { sql = string.Format("UPDATE `{0}` T1 SET {1} WHERE {2}", record.TableName, values, where); } else { sql = string.Format("UPDATE `{0}` T1 SET {1}", record.TableName, values); } } cmd.CommandText = sql; cmd.Parameters.AddRange(vparams.ToArray()); var affected = 0; try { affected = cmd.ExecuteNonQuery(); GeneratorUtils.LogCommand(store.LogLevel, "rmsave-ok", cmd, null); } catch (Exception error) { GeneratorUtils.LogCommand(store.LogLevel, "rmsave-error", cmd, error); throw; } if (affected == 0) { throw new MySQLDataAccessException(string.Format(StringConsts.NO_ROWS_AFFECTED_ERROR, instance.LastPostedChange == ChangeType.Created ? "Insert" : "Update")); } }//using command }