/// <summary>
/// Returns a list of tables from the data store
/// </summary>
/// <param name="dstore"></param>
/// <returns></returns>
public IEnumerable <DBObject> GetSchemaTables(IDataStore dstore)
{
SQLiteCommand getTables = new SQLiteCommand();
getTables.CommandText = "select name from sqlite_master where type = 'table'";
using (IQueryData tables = dstore.ExecuteCommands.ExecuteCommandQuery(getTables, dstore.Connection))
{
foreach (IQueryRow tab in tables)
{
using (SQLiteCommand getColumns = new SQLiteCommand())
{
DBObject t = new DBObject();
t.Name = tab.GetDataForRowField(0).ToString();
t.Columns = new List <Column>();
getColumns.CommandText = string.Concat("pragma table_info(", t.Name, ");");
using (IQueryData columns = dstore.ExecuteCommands.ExecuteCommandQuery(getColumns, dstore.Connection))
{
foreach (IQueryRow col in columns)
{
t.Columns.Add(new Column()
{
Name = col.GetDataForRowField("name").ToString(),
DataType = col.GetDataForRowField("type").ToString(),
IsPrimaryKey = (bool)dstore.Connection.CLRConverter.ConvertToType(col.GetDataForRowField("pk"), typeof(bool)),
DefaultValue = col.GetDataForRowField("dflt_value").ToString()
});
}
yield return(t);
}
}
}
}
}
/// <summary>
/// Executes a db command and fills in a list of objects with the result data
/// </summary>
/// <typeparam name="ReturnType">The type of object to return</typeparam>
/// <param name="objectType">The type of object to return.</param>
/// <param name="command">The command to execute</param>
/// <returns></returns>
protected virtual IEnumerable <ReturnType> ExecuteCommandLoadList <ReturnType>(Type objectType, IDbCommand command)
{
DatabaseTypeInfo ti = Connection.CommandGenerator.TypeParser.GetTypeInfo(objectType);
using (IQueryData dt = ExecuteCommands.ExecuteCommandQuery(command, Connection))
{
if (dt.QuerySuccessful)
{
foreach (IQueryRow row in dt)
{
ReturnType toAdd;
if (objectType.IsSystemType())
{
toAdd = (ReturnType)Connection.CLRConverter.ConvertToType(row.GetDataForRowField(0), typeof(ReturnType));
}
else
{
toAdd = (ReturnType)BuildObject(row, ti);
}
yield return(toAdd);
}
}
}
}
/// <summary>
/// Returns a list of tables from the data store
/// </summary>
/// <param name="dstore"></param>
/// <returns></returns>
public IEnumerable <DBObject> GetSchemaTables(IDataStore dstore)
{
MySqlCommand getColumns = new MySqlCommand();
MySqlCommand getTables = new MySqlCommand();
getTables.CommandText = "show full tables where table_type != 'view'";
using (IQueryData tables = dstore.ExecuteCommands.ExecuteCommandQuery(getTables, dstore.Connection))
{
foreach (IQueryRow row in tables)
{
DBObject t = new DBObject();
t.Name = row.GetDataForRowField(0).ToString();
t.Columns = new List <Column>();
getColumns.CommandText = string.Concat("DESCRIBE ", t.Name);
using (IQueryData columns = dstore.ExecuteCommands.ExecuteCommandQuery(getColumns, dstore.Connection))
{
foreach (QueryRow c in columns)
{
t.Columns.Add(new Column()
{
Name = c.GetDataForRowField("Field").ToString(),
DataType = c.GetDataForRowField("Type").ToString(),
IsPrimaryKey = c.GetDataForRowField("Key").ToString().Equals("PRI", StringComparison.InvariantCultureIgnoreCase),
DefaultValue = c.GetDataForRowField("Default").ToString()
});
}
yield return(t);
}
}
}
}
private async void OnCallbackQuery(object sender, Telegram.Bot.Args.CallbackQueryEventArgs e)
{
try
{
IQueryData queryData = _queryDataSerializer.DecodeBase64(e.CallbackQuery.Data);
Group group = await _groupService.GetByChatTokenAsync(queryData.ChatToken);
Customer[] customers = await _customerService.GetListAsync(group.Id);
// Собираем всю информацию о пользователе и сообщении.
UserMessageInfo userMessageInfo = new UserMessageInfo
{
Group = group,
Customers = customers,
UserName = e.CallbackQuery.From.Username,
UserToken = e.CallbackQuery.From.Id.ToString(),
MessageType = Models.MessageType.Query,
Message = new MessageInfo
{
Token = e.CallbackQuery.Message.MessageId.ToString(),
Text = e.CallbackQuery.Message.Text,
QueryData = queryData
}
};
OnMessage?.Invoke(this, userMessageInfo);
}
catch (Exception ex)
{
_logger.Error($"{GetType().Name}: Произошла ошибка во время обработки query запроса ", ex);
}
}
public string EncodeBase64(IQueryData data, MenuType type)
{
return(UrlBase64Serializer.Base64Encode(new QueryDataRaw
{
Type = type,
Data = ProtoSerializerHelper.Serialize(data, RuntimeTypeModel.Default)
}));
}
public IQueryable <T> Query <T>(IQueryData queryData)
{
Contract.Assert(queryData != null);
var command = this.ResolveCommand <T>(queryData);
var result = command.Execute(queryData);
return(result);
}
private IQueryRepositoryCommand <T> ResolveCommand <T>(IQueryData commandData)
{
Contract.Assert(commandData != null);
var type = typeof(IQueryRepositoryCommand <,>).MakeGenericType(commandData.GetType(), typeof(T));
var command = (IQueryRepositoryCommand <T>) this.ServiceLocator.GetInstance(type);
return(command);
}
public static IEnumerable <T> FindAll <T>(this IRepository repository, IQueryData queryData)
{
Contract.Assert(queryData != null);
var query = repository.Query <T>(queryData);
var result = query.ToList();
return(result);
}
public int Count <T>(IQueryData queryData)
{
Contract.Assert(queryData != null);
var command = this.ResolveCommand <T>(queryData);
var count = command.RowCount(queryData);
return(count);
}
public static T FindOne <T>(this IRepository repository, IQueryData queryData)
{
Contract.Assert(queryData != null);
var query = repository.Query <T>(queryData);
var result = query.FirstOrDefault();
return(result);
}
public Sucursal(Entities.Table.Sucursal entity,
IRepositoryTable <Entities.Table.Sucursal, Persistence.Table.Sucursal> repository,
IQueryData <Entities.Table.Sucursal, Persistence.Table.Sucursal> query)
: base(entity,
repository,
query,
typeof(Entities.Table.Sucursal).GetAttributeFromType <TableAttribute>()?.Name ?? "sucursal", "Sucursal")
{
}
public Empresa(Entities.Table.Empresa entity,
IRepositoryTable <Entities.Table.Empresa, Persistence.Table.Empresa> repository,
IQueryData <Entities.Table.Empresa, Persistence.Table.Empresa> query)
: base(entity,
repository,
query,
typeof(Entities.Table.Empresa).GetAttributeFromType <TableAttribute>()?.Name ?? "empresa", "Empresa")
{
}
SelectSingle
(IQueryData <T, U> query,
int maxdepth = 1, U data = default(U))
{
var select = Select(query, maxdepth, 1, (data != null ? new ListData <T, U>()
{
data
} : null));
return(select.result, select.datas != null ? select.datas.FirstOrDefault() : default(U));
}
/// <summary>
/// Determines if an object already exists in the data store, based on the primary key
/// </summary>
/// <param name="item">The object to check</param>
/// <returns></returns>
public virtual bool IsNew(object item)
{
IDbCommand cmd = Connection.CommandGenerator.GetSelectCommand(item);
using (IQueryData qd = ExecuteCommands.ExecuteCommandQuery(cmd, Connection))
{
var enumerator = qd.GetEnumerator();
enumerator.MoveNext();
return(enumerator.Current == null);
}
}
public async Task <bool> IsNew(object item)
{
using (IQueryData qd = await Task.Run(() => {
IDbCommand cmd = Connection.CommandGenerator.GetSelectCommand(item, false);
return(ExecuteCommands.ExecuteCommandQuery(cmd, Connection));
}))
{
var enumerator = qd.GetEnumerator();
enumerator.MoveNext();
return(enumerator.Current == null);
}
}
public IEnumerable <DBObject> GetSchemaTables(IDataStore dstore)
{
AdsConnection connection = (AdsConnection)GetConnection();
connection.Open();
using (IQueryData columns = dstore.ExecuteCommands.ExecuteCommandQuery(GetCommand("EXECUTE PROCEDURE sp_GetColumns(null, null, null, null)"), this))
{
foreach (string table in connection.GetTableNames())
{
yield return(LoadObjectInfo(dstore, table, columns));
}
}
}
public IMenu GetMenu(UserMessageInfo userMessageInfo, IQueryData data)
{
// Todo Проверка №2. Надо перенести в одно место.
switch (data.MenuType)
{
case MenuType.NewReceiptSelectCustomer: return(GetNewReceiptSelectCustomerMenu(userMessageInfo, (AddReceiptQueryData)data));
case MenuType.NewReceiptSelectConsumers: return(GetNewReceiptSelectConsumersMenu(userMessageInfo, (AddReceiptQueryData)data));
case MenuType.MoneyTransferSelectFrom: return(GetMoneyTransferSelectFromMenu(userMessageInfo, (MoneyTransferQueryData)data));
case MenuType.MoneyTransferSelectTo: return(GetMoneyTransferSelectToMenu(userMessageInfo, (MoneyTransferQueryData)data));
}
return(null);
}
protected virtual bool ProcessCommand(Func <IQueryData, bool> OnDone, IDbCommand command, bool FailIfNoRecords)
{
using (IQueryData dt = ExecuteCommands.ExecuteCommandQuery(command, Connection))
{
if (dt.QuerySuccessful)
{
bool records = OnDone(dt);
if (FailIfNoRecords)
{
return(records);
}
}
return(true);
}
}
Select
(IQueryData <T, U> query,
int maxdepth = 1, int top = 0, IListData <T, U> datas = null)
{
var parameters = new List <SqlParameter>();
var querycolumns = _sqlbuilder.GetQueryColumns(query, null, null, maxdepth, 0);
var queryjoins = _sqlbuilder.GetQueryJoins(query, new List <string>()
{
query.Description.Name
}, maxdepth, 0);
var selectcommandtext = _sqlcommandbuilder.Select(_sqlbuilder.GetSelectColumns(querycolumns),
_sqlbuilder.GetFrom(queryjoins, query.Description.Name),
_sqlbuilder.GetWhere(querycolumns, parameters), top);
return(Select(selectcommandtext, CommandType.Text, parameters, maxdepth, datas));
}
Delete
(IQueryData <T, U> query,
int maxdepth = 1)
{
var parameters = new List <SqlParameter>();
var querycolumns = _sqlbuilder.GetQueryColumns(query, null, null, maxdepth, 0);
var queryjoins = _sqlbuilder.GetQueryJoins(query, new List <string>()
{
query.Description.Name
}, maxdepth, 0);
var deletecommandtext = _sqlcommandbuilder.Delete($"{query.Description.Name}",
_sqlbuilder.GetFrom(queryjoins, query.Description.Name),
_sqlbuilder.GetWhere(querycolumns, parameters));
return(Delete(deletecommandtext, CommandType.Text, parameters));
}
protected async Task <bool> ProcessCommandAsync(Func <IQueryData, bool> OnDone, IDbCommand command, bool FailIfNoRecords)
{
using (IQueryData dt = await Task.Run(() => _dstore.ExecuteCommands.ExecuteCommandQuery(command, _dstore.Connection)))
{
return(await Task.Run(() =>
{
if (dt.QuerySuccessful)
{
bool records = OnDone(dt);
if (FailIfNoRecords)
{
return records;
}
}
return true;
}));
}
}
public static T LoadOne <T>(this IRepository repository, IQueryData <T> queryData)
where T : class
{
Contract.Assert(repository != null);
Contract.Assert(queryData != null);
var instance = repository.FindOne(queryData);
if (instance == null)
{
var message = string.Format(
CultureInfo.InvariantCulture,
"Cannot load [{0}] using criteria [{1}].",
typeof(T),
queryData.GetType());
throw new ObjectNotFoundException(message);
}
return(instance);
}
/// <summary>
/// Gets the tables for a datastore
/// </summary>
/// <param name="dstore">the datastore to fetch from</param>
/// <returns></returns>
public IEnumerable <DBObject> GetSchemaTables(IDataStore dstore)
{
IDbCommand tblCmd = GetCommand();
tblCmd.CommandText = _GetTables;
IDbCommand clmCmd = GetCommand();
clmCmd.CommandText = _GetTableColumns;
using (IQueryData objects = dstore.ExecuteCommands.ExecuteCommandQuery(tblCmd, dstore.Connection))
using (IQueryData columns = dstore.ExecuteCommands.ExecuteCommandQuery(clmCmd, dstore.Connection))
{
List <IQueryRow> rows = columns.GetQueryEnumerator().ToList();
foreach (IQueryRow o in objects)
{
yield return(Helpers.LoadObjectInfo(dstore, o, rows));
}
}
}
Update
(IQueryData <T, U> query,
IList <IColumnTable> columns,
int maxdepth = 1)
{
var parameters = new List <SqlParameter>();
var querycolumns = _sqlbuilder.GetQueryColumns(query, null, null, maxdepth, 0);
var queryjoins = _sqlbuilder.GetQueryJoins(query, new List <string>()
{
query.Description.Name
}, maxdepth, 0);
var updatecommandtext = _sqlcommandbuilder.Update($"{query.Description.Name}",
_sqlbuilder.GetFrom(queryjoins, query.Description.Name),
_sqlbuilder.GetUpdateSet(columns.Where(c => !c.IsIdentity && c.Value != c.DbValue).Select(x => (x.Table.Description, x.Description, x.Type, x.Value)).ToList(), parameters),
_sqlbuilder.GetWhere(querycolumns, parameters));
return(Update(updatecommandtext, CommandType.Text, parameters));
}
/// <summary>
/// Returns a list of views from the datastore
/// </summary>
/// <param name="dstore"></param>
/// <returns></returns>
public IEnumerable <DBObject> GetSchemaViews(IDataStore dstore)
{
MySqlCommand getColumns = new MySqlCommand();
MySqlCommand getTables = new MySqlCommand();
getTables.CommandText = "show full tables where table_type = 'view'";
using (IQueryData tables = dstore.ExecuteCommands.ExecuteCommandQuery(getTables, dstore.Connection))
{
foreach (IQueryRow tab in tables)
{
DBObject t = new DBObject();
t.Name = tab.GetDataForRowField(0).ToString();
t.Columns = new List <Column>();
try
{
getColumns.CommandText = string.Concat("DESCRIBE ", t.Name);
using (IQueryData columns = dstore.ExecuteCommands.ExecuteCommandQuery(getColumns, dstore.Connection))
{
foreach (IQueryRow col in columns)
{
t.Columns.Add(new Column()
{
Name = col.GetDataForRowField("Field").ToString(),
DataType = col.GetDataForRowField("Type").ToString(),
IsPrimaryKey = col.GetDataForRowField("Key").ToString().Equals("PRI", StringComparison.InvariantCultureIgnoreCase),
DefaultValue = col.GetDataForRowField("Default").ToString()
});
}
}
}
catch //this is intentional, mysql will bomb out when a view is messed up if you try to retrieve the columns
{
}
yield return(t);
}
}
}
public DBObject LoadObjectInfo(IDataStore dstore, string table, IQueryData columns)
{
DBObject t = new DBObject();
t.Name = table;
t.Columns = new List <Column>();
foreach (IQueryRow o in columns) //all of the columns for all of the tables were returned, so we need to only get the one I'm working on...
{
if (o.FieldHasMapping("Table_Name")) //make sure the table name is present
{
if (((string)o.GetDataForRowField("TABLE_NAME")).Trim().Equals(table, StringComparison.InvariantCultureIgnoreCase) && o.FieldHasMapping("Column_Name"))
{
AddColumn(dstore, o, t);
}
else
{
o.ResetUsed();
}
}
}
return(t);
}
public async Task <IEnumerable <ReturnType> > ExecuteCommandLoadList <ReturnType>(Type objectType, IDbCommand command)
{
List <ReturnType> toReturn = new List <ReturnType>();
using (IQueryData dt = await Task.Run(() => ExecuteCommands.ExecuteCommandQuery(command, Connection)))
{
if (dt.QuerySuccessful)
{
DatabaseTypeInfo ti = TypeInformationParser.GetTypeInfo(objectType);
foreach (IQueryRow row in dt)
{
if (objectType.IsSystemType())
{
toReturn.Add((ReturnType)Connection.CLRConverter.ConvertToType(row.GetDataForRowField(0), typeof(ReturnType)));
}
else
{
toReturn.Add((ReturnType)await BuildObjectAsync(row, ti));
}
}
}
return(toReturn);
}
}
public static IQueryable <T> Query <T>(this IRepository repository, IQueryData <T> queryData)
{
Contract.Assert(repository != null);
return(repository.Query <T>(queryData));
}
public static T FindOne <T>(this IRepository repository, IQueryData <T> queryData)
{
Contract.Assert(repository != null);
return(repository.FindOne <T>((IQueryData)queryData));
}
public static IEnumerable <T> FindAll <T>(this IRepository repository, IQueryData <T> queryData)
{
Contract.Assert(repository != null);
return(repository.FindAll <T>((IQueryData)queryData));
}
public DbContextPage()
{
InitializeComponent();
data = (IQueryData)WizardDataStore.Data;
}
