/// <summary>
/// Loads the list.
/// <example>
/// <code>
/// User[] users = excelAccessor.LoadList<User>("Users Sheet", "A1:B10");
/// </code>
/// </example>
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sheetName">Name of the sheet.</param>
/// <param name="range">The range.</param>
/// <returns></returns>
public T[] LoadList <T>(string sheetName, string range) where T : new()
{
using (IDataReader reader = GetReader(sheetName, range))
{
return(DbGateway.LoadList <T>(reader));
}
}
internal override Dictionary <string, string> LoadFields(DbGateway gateway, string tableName)
{
try
{
object scalar = ExecuteScalar(string.Format("SELECT sql FROM sqlite_master WHERE type='table' and name='{0}'", tableName));
string sql = scalar.ToString();
int start = sql.IndexOf('(') + 1;
sql = sql.Substring(start, sql.IndexOf(')', start) - start);
string[] args = sql.Split(new[] { ',' });
Dictionary <string, string> d = new Dictionary <string, string>();
foreach (var item in args)
{
string[] split = item.Split(' ');
string key = split[0];
string value = split[1];
d.Add(key, value);
}
return(d);
}
catch (Exception ex)
{
throw new NdbException("Can't load fields information from the following table: " + tableName, ex);
}
}
private static DbRecords loadRecords(Type type, IDataReader reader)
{
DbRecordInfo info = DbAttributesManager.GetRecordInfo(type);
Array records = DbGateway.LoadRecords(type, reader, info);
return(new DbRecords(info, records));
}
internal override string[] LoadTables(DbGateway gateway)
{
return(gateway.LoadArray <string>(
// @"SELECT TABLE_SCHEMA, TABLE_NAME, OBJECTPROPERTY(object_id(TABLE_NAME), N'IsUserTable') AS type
@"SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE OBJECTPROPERTY(object_id(TABLE_NAME), N'IsUserTable')=1",
"TABLE_NAME"));
}
/// <summary>
/// Gets all of the Person registers on the Person table.
/// </summary>
public static IEnumerable <Person> GetPerson()
{
var sqlQuery = "select * from Person";
var db = new DbGateway();
var table = db.Read(sqlQuery);
return(table.Rows.Cast <DataRow>().Select(x => (Person)x));
}
/// <summary>
/// Gets the first transaction with a certain message.
/// </summary>
/// <param name="msg">Message to find</param>
public static Transaction GetTransactionByMessage(string msg)
{
var sqlQuery = $@"select * from [Transaction] where Message = '{msg}'";
var db = new DbGateway();
var table = db.Read(sqlQuery);
return((Transaction)table.Rows.Cast <DataRow>().FirstOrDefault());
}
/// <summary>
/// Gets all anti-fraud records associated to a given store.
/// </summary>
/// <param name="id">Store identifier</param>
public static AntiFraudInfo GetStoreAntiFraudInfo(int id)
{
var sqlQuery = $@"select * from AssociationStoreAntiFraud where IdStore = {id}";
var db = new DbGateway();
var table = db.Read(sqlQuery);
return((AntiFraudInfo)table.Rows.Cast <DataRow>().FirstOrDefault());
}
/// <summary>
/// Gets all of the Operator registers on the Operator table.
/// </summary>
public static IEnumerable <Operator> GetOperator()
{
var sqlQuery = "select * from Operator";
var db = new DbGateway();
var table = db.Read(sqlQuery);
return(table.Rows.Cast <DataRow>().Select(x => (Operator)x));
}
public static IDataReader ExecuteReader(this ISqlQuery sqlQuery, DbGateway dbGateway, int pageNumber, int pageSize)
{
string commandSql = sqlQuery.SqlBuilder.GetQuerySql();
object[] paramValues = sqlQuery.SqlBuilder.GetParameters().Values.ToArray();
return(dbGateway.ExecuteReader(commandSql, paramValues));
}
/// <summary>
/// Gets a specific Person register based on the id.
/// </summary>
/// <param name="id">Id of the person being searched</param>
public static Person GetPerson(string id)
{
var sqlQuery = $"select * from Person where Id = '{id}'";
var db = new DbGateway();
var table = db.Read(sqlQuery);
var person = (Person)table.Rows.Cast <DataRow>().FirstOrDefault();
return(person);
}
/// <summary>
/// Gets a specific Store register based on the id.
/// </summary>
/// <param name="id">Id of the store being searched</param>
public static Store GetStore(int id)
{
var sqlQuery = $"select * from Store where Id = {id}";
var db = new DbGateway();
var table = db.Read(sqlQuery);
var store = (Store)table.Rows.Cast <DataRow>().FirstOrDefault();
return(store);
}
/// <summary>
/// Gets a CreditCard register based on the number.
/// </summary>
/// <param name="id">Id of the person being searched</param>
public static CreditCard GetCard(string number)
{
var sqlQuery = $"select * from CreditCard where Number = '{number}'";
var db = new DbGateway();
var table = db.Read(sqlQuery);
var person = (CreditCard)table.Rows.Cast <DataRow>().FirstOrDefault();
return(person);
}
/// <summary>
/// Deletes transactions based on their message.
/// </summary>
public static void DeleteTransactionByMessage(string msg)
{
var pairs = new List <ExecValuePair>
{
new ExecValuePair("@msg", msg)
};
var sqlQuery = $@"delete from [Transaction] where Message = @msg";
var db = new DbGateway();
db.Exec(sqlQuery, pairs);
}
/// <summary>
/// Gets all operators associated to a given store.
/// </summary>
/// <param name="id">Store identifier</param>
public static IEnumerable <Operator> GetStoreOperators(int id)
{
var sqlQuery =
$@"select o.* from Operator o
inner join AssociationStoreOperator a on a.IdOperator = o.Id
inner join Store s on s.Id = a.IdStore
where s.Id = {id}";
var db = new DbGateway();
var table = db.Read(sqlQuery);
return(table.Rows.Cast <DataRow>().Select(x => (Operator)x));
}
/// <summary>
/// Loads the tables.
///
/// Used in code generation purposes
/// </summary>
/// <returns></returns>
internal override string[] LoadTables(DbGateway gateway)
{
using (OleDbConnection connection = (OleDbConnection)CreateConnection())
{
DataTable dataTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new Object[] { null, null, null, "TABLE" });
string[] tables = new string[dataTable.Rows.Count];
for (int i = 0; i < dataTable.Rows.Count; i++)
{
tables[i] = dataTable.Rows[i][0].ToString();
}
return(tables);
}
}
internal override Dictionary <string, string> LoadFields(DbGateway gateway, string tableName)
{
//TODO: load field length also
return(gateway.LoadKeyValue <string, string>(
string.Format(@"SELECT a.attname AS field, t.typname AS type
FROM pg_class c, pg_attribute a, pg_type t
WHERE c.relname = '{0}'
AND a.attnum > 0
AND a.attrelid = c.oid
AND a.atttypid = t.oid
AND attname NOT IN ('cmin', 'cmax', 'ctid', 'oid', 'tableoid', 'xmin', 'xmax')
ORDER BY a.attnum", tableName.ToLower()), "field", "type"));
}
/// <summary>
/// Initializes a new instance of the <see cref="DbQuery"/> class.
/// </summary>
/// <param name="gateway">The gateway.</param>
/// <param name="filterExpressions">The filter expressions.</param>
private DbQuery(DbGateway gateway, List <DbFilterNode> filterExpressions)
{
foreach (DbFilterExpression expression in filterExpressions)
{
if (!Utils.DbValidator.IsValidColumnName(expression.ColumnName))
{
throw new NdbInvalidColumnNameException(expression.ColumnName);
}
}
Gateway = gateway;
FilterGroup = new DbAndFilterGroup(filterExpressions);
}
public void GetFiltersSqlTest()
{
TestData TestData = new TestData(DbAccessor.Instance);
var expression = new DbColumnFilterExpression(
DbExpressionType.Equal, "Email", TestData.TestUser.Email);
var expression2 = new DbColumnFilterExpression(
DbExpressionType.Equal, "Email", "*****@*****.**");
DbFilterGroup or = new DbOrFilterGroup(expression, expression2);
var gateway = new DbGateway(DbAccessor.Instance);
//TODO: implement below approach
// DataTable table = DbQuery.Create(gateway, "SELECT * FROM Users", or).LoadDataTable();
}
internal override Dictionary <string, string> LoadFields(DbGateway gateway, string tableName)
{
// thanks to Kristof Neirynck - http://www.crydust.be/blog/2006/12/26/mssql-lacks-describe-statement/
return(gateway.LoadKeyValue <string, string>(
string.Format(
@"SELECT column_name as name, data_type +
COALESCE(
'(' + CAST(character_maximum_length AS VARCHAR) + ')'
, '(' + CAST(numeric_precision AS VARCHAR) + ')'
, ''
) as [type]
FROM
information_schema.columns
WHERE
table_name = N'{0}';", tableName),
"name", "type"));
}
public virtual void InitExchange()
{
var ex = DbGateway.GetExchangeInfo(GetExchangeName());
BTCUSDMinValue = ex.UsdMinValue;
LTCUSDMinValue = ex.UsdMinValue;
BCHUSDMinValue = ex.UsdMinValue;
ETHUSDMinValue = ex.UsdMinValue;
XRPUSDMinValue = ex.UsdMinValue;
DASHUSDMinValue = ex.UsdMinValue;
IOTAUSDMinValue = ex.UsdMinValue;
TradingState = TradingState.Ok;
TradeFee = ex.TradeFee;
_account = new AccountManagement(this, Log);
_account.UpdateBalances();
}
/// <summary>
/// Saves a transaction.
/// </summary>
/// <param name="transaction">Transaction to store</param>
public static void SaveTransaction(Transaction transaction)
{
var pairs = new List <ExecValuePair>
{
new ExecValuePair("@cardNumber", transaction.CreditCard.CreditCardNumber),
new ExecValuePair("@amount", transaction.AmountInCents),
new ExecValuePair("@installments", transaction.InstallmentCount),
new ExecValuePair("@authorized", transaction.Authorized),
new ExecValuePair("@msg", transaction.Message)
};
var sqlQuery =
$@"insert into [Transaction] (CardNumber, AmountInCents, Installments, Authorized, Message)
values (@cardNumber, @amount, @installments, @authorized, @msg)";
var db = new DbGateway();
db.Exec(sqlQuery, pairs);
}
public void UniqueTest()
{
TestData TestData = new TestData(gateway.Accessor);
DbGateway dbGateway = new DbGateway(gateway.Accessor);
gateway.CreateTable(typeof(User));
User user = TestData.TestUser;
dbGateway.Insert(user);
try
{
dbGateway.Insert(user);
Assert.Fail();
}
catch
{
}
}
public void ImportFromExcel(string [] args)
{
if (args.Length > 4)
{
string SourceFileConnectionString = args[3];
string assemblyName = args[4];
var assembly = Assembly.LoadFrom(assemblyName);
Type[] types = DbAttributesManager.LoadDbRecordTypes(assembly);
var Source = (ExcelAccessor)DbAccessor.Create(DbProvider.Excel, SourceFileConnectionString);
Accessor.ShareConnection = true;
var Target = new DbGateway(Accessor);
DbExcelExport export = new DbExcelExport(Source, Target);
export.Export(types, true);
ExitCode = ExitCode.Success;
}
else
{
ExitCode = ExitCode.Exception;
}
}
public TestData(DbAccessor accessor)
{
gateway = new DbGateway(accessor);
}
/// <summary>
/// Initializes a new instance of the <see cref="DbExcelExport"/> class.
/// </summary>
/// <param name="source">The source.</param>
/// <param name="target">The target.</param>
public DbExcelExport(ExcelAccessor source, DbGateway target)
{
Source = source;
Target = target;
}
/// <summary>
/// Initializes a new instance of the <see cref="DbExcelExport"/> class.
/// </summary>
/// <param name="sourceConnectionStringName">Name of the source connection string.</param>
/// <param name="targetConnectionStringName">Name of the target connection string.</param>
public DbExcelExport(string sourceConnectionStringName, string targetConnectionStringName)
{
Source = (ExcelAccessor)DbAccessor.Create(sourceConnectionStringName);
Target = new DbGateway(DbAccessor.Create(targetConnectionStringName));
}
/// <summary>
/// Creates this instance using specified filter expressions.
/// </summary>
/// <param name="gateway">The gateway</param>
/// <param name="filterExpressions">The filter expressions.</param>
/// <returns></returns>
public static DbQuery Create(DbGateway gateway, List <DbFilterNode> filterExpressions)
{
return(new DbQuery(gateway, filterExpressions));
}
/// <summary>
/// Creates this instance.
/// </summary>
/// <returns></returns>
public static DbQuery Create(DbGateway gateway)
{
return(new DbQuery(gateway));
}
/// <summary>
/// Initializes a new instance of the <see cref="DbQuery"/> class.
/// </summary>
/// <param name="gateway">The gateway.</param>
/// <param name="filterGroup">The filter group.</param>
private DbQuery(DbGateway gateway, DbFilterGroup filterGroup)
{
Gateway = gateway;
FilterGroup = filterGroup;
}
private DbQuery(DbGateway gateway) :
this(gateway, new DbAndFilterGroup(new List <DbFilterNode>()))
{
}
