/// <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>())) { }