public void InputOutputParameter() { DB.Execute(@" CREATE PROCEDURE uspInputOutputParameter @Input int, @InputOutput int OUTPUT, @Output int OUTPUT AS BEGIN SET @InputOutput = @Input + 1 SET @Output = @InputOutput + 2 END"); var parameters = new { Input = 5, InputOutput = Col.Int(3, ParameterDirection.InputOutput), Output = Col.Int(null, ParameterDirection.Output) }; var affectedRows = DB.Execute("uspInputOutputParameter", parameters, new QueryOptions { CommandType = CommandType.StoredProcedure }); Assert.AreEqual(-1, affectedRows); Assert.AreEqual(6, parameters.InputOutput.Value); Assert.AreEqual(8, parameters.Output.Value); }
public void ToDataTable() { // Type var @this = new[] { new TestObject { IntColumn = 1, StringColumn = "2" }, new TestObject { IntColumn = 3, StringColumn = "4" } }; // Exemples DataTable dt = @this.ToDataTable(); // Transorm entities to DataTable // Unit Test Assert.AreEqual(2, dt.Rows.Count); Assert.AreEqual(1, dt.Rows[0]["IntColumn"]); Assert.AreEqual("2", dt.Rows[0]["StringColumn"]); Assert.AreEqual(3, dt.Rows[1]["IntColumn"]); Assert.AreEqual("4", dt.Rows[1]["StringColumn"]); }
public void AllKindsOfParametersAndResults() { DB.Execute(@" CREATE PROCEDURE uspAllKindsOfParametersAndResults @Input int, @InputOutput int OUTPUT, @Output int OUTPUT AS BEGIN SET @InputOutput = @InputOutput + 1 SET @Output = @Input + 1 SELECT 'Hello world' AS A RETURN 5 END"); var parameters = new { Input = 5, InputOutput = Col.Int(6, ParameterDirection.InputOutput), Output = Col.Int(null, ParameterDirection.Output), ReturnValue = Col.Int(null, ParameterDirection.ReturnValue) }; var row = DB.GetRow("uspAllKindsOfParametersAndResults", parameters, new QueryOptions { CommandType = CommandType.StoredProcedure }); Assert.AreEqual("Hello world", row.A); Assert.AreEqual(7, parameters.InputOutput.Value); Assert.AreEqual(6, parameters.Output.Value); Assert.AreEqual(5, parameters.ReturnValue.Value); }
// Pull emails from specified Architype Accounts public DataTable pull_emls(string acnt1) { var sprcPrms = new[] { _dalBase.pram_make_strn(acnt1, "@acnt1") }; var dtbl = _dalBase.sprc_exec("sp_mail_chmp_mail_Select", sprcPrms); return dtbl; }
public void ArgumentsArrayReturnsInnerArgumentArray() { var args = new[] { new SqlArgument() }; var sqlQuery = new SqlQuery(string.Empty, args); Assert.ReferenceEquals(args, sqlQuery.ArgumentsArray); }
private void Handle(BookReceived evnt) { var key = new { LibraryId = evnt.LibraryId, BookId = evnt.BookId }; var count = _connection.GetValue<int>(key, "EventSourcing_Sample_BookStoreItem", "Count", _transaction); _connection.Update( new { Count = count + evnt.Count }, key, "EventSourcing_Sample_BookStoreItem", _transaction); }
public void Convert150GBP_To_EUR() { var currencyConverter = new Converter.CurrencyConversion(; var result = currencyConverter.DoConversion(150,"EUR"); Assert.AreEqual(150, result.Result); Assert.AreEqual(150, result.Fee); Assert.AreEqual(150, result.Total); }
public void Format_CreatesCsv() { var dataTable = new[] { new Person {Id = 1, FirstName = "John", LastName = "Doe"} }.ToDataTable(); var formatter = new CsvFormatter(); var result = formatter.Format(dataTable).ToList(); CollectionAssert.AreEqual(new[] { "\"Id\";\"FirstName\";\"LastName\"", "\"1\";\"John\";\"Doe\"" }, result); }
private void Handle(BookBorrowed evnt) { var key = new { LibraryId = evnt.LibraryId, AccountId = evnt.AccountId, BookId = evnt.BookId }; var borrowedBooks = _connection.Query(key, "EventSourcing_Sample_BorrowedBook", _transaction); if (borrowedBooks.Count() == 0) { _connection.Insert(evnt, "EventSourcing_Sample_BorrowedBook", _transaction); } else { var originalCount = (int)borrowedBooks.First().Count; _connection.Update(new { Count = originalCount + evnt.Count }, key, "EventSourcing_Sample_BorrowedBook", _transaction); } }
public override void Up() { var columns = new[] { new Column("EwayProcessedDataId", DbType.Int32, ColumnProperty.PrimaryKeyWithIdentity), new Column("EwayTransactionReference", DbType.Int64, ColumnProperty.Unique | ColumnProperty.NotNull), new Column("CardHolder", DbType.String, 50, ColumnProperty.NotNull), new Column("Amount", DbType.Decimal, ColumnProperty.NotNull), new Column("ResponseCode", DbType.String, 2, ColumnProperty.NotNull), new Column("ResponseText", DbType.String, 50, ColumnProperty.NotNull), new Column("InvoiceNumber", DbType.String, 50), new Column("EwayInvoiceReference", DbType.String, 50) }; Database.AddTable("EwayProcessedData", columns); }
public User GetUserByLogin(ViewModels.UserLoginViewModel userLogin, Enums.AccountType accountType) { using (IDbConnection conn = OpenConnection()) { const string query = "SELECT u.Id, u.Email,u.Password, u.Firstname,u.Lastname,u.CreatedDate,u.LastLogin," + " u.AccountType, u.Roles, u.Displayname, u.Avatar, u.ThirdPartyId, u.Status, u.Ip, u.IsAdmin" + " FROM dbo.[User] AS u" + " WHERE u.Email = @email AND u.Password = @password AND u.AccountType = accountType"; var parameters = new { email = userLogin.Email, password = userLogin.Password }; return conn.Query<User>(query, parameters).SingleOrDefault(); } }
public override bool Execute() { _log.InfoFormat("Creating DCS database schema"); var entityTypes = new[] { typeof(UserEntity), typeof(RepositoryEntity), typeof(CommitEntity), typeof(UserChallengeEntity), typeof(ContactEntity) }; foreach (var type in entityTypes) { _db.CreateTableIfNotExists(type); } return true; }
public override void Up() { Database.AddTable("Circuit", new[] { new Column("CircuitId", DbType.Int32, ColumnProperty.PrimaryKeyWithIdentity), new Column("Name", DbType.String, 50, ColumnProperty.NotNull | ColumnProperty.Unique | ColumnProperty.Indexed), new Column("Location", DbType.String, 50), new Column("Country", DbType.String, 50), new Column("CircuitMapUri", DbType.String, 255) }); var circuitNames = new[] { new[] {"Bahrain", "Sakhir", "Bahrain"}, new[] {"Australian", "Melbourne", "Australia"}, new[] {"Malaysian", "Kuala Lumpur", "Malaysia"}, new[] {"Chinese", "Shanghai", "China"}, new[] {"Spainish", "Catalunya", "Spain"}, new[] {"Monaco", "Monte Carlo", "Monaco"}, new[] {"Turkish", "Istanbul", "Turkey"}, new[] {"Canadian", "Montreal", "Canada"}, new[] {"European", "Valencia", "Spain"}, new[] {"British", "Silverstone", "Britian"}, new[] {"German", "Hockenheim", "Germany"}, new[] {"Hungarian", "Budapest", "Hungary"}, new[] {"Belgian", "Spa-Francorchamps", "Belgium"}, new[] {"Italian", "Monza", "Italy"}, new[] {"Singapore", "Singapore", "Singapore"}, new[] {"Japanese", "Suzuka", "Japan"}, new[] {"Korean", "Yeongam", "Korea"}, new[] {"Brazilian", "Interlagos", "Brazil"}, new[] {"Abu Dhabi", "Yas Marina", "Abu Dhabi"} }; foreach (var circuit in circuitNames) { Database.Insert("Circuit", new[] {"Name", "Location", "Country"}, circuit); } }
public override void Up() { Database.AddTable("Team", new[] { new Column("TeamId", DbType.Int32, ColumnProperty.PrimaryKeyWithIdentity), new Column("Name", DbType.String, 50, ColumnProperty.Unique | ColumnProperty.Indexed) }); var teamNames = new[] { "McLaren", "Red Bull", "Ferrari", "Mercedes GP", "Renault", "Williams", "Force India", "Toro Rosso", "Lotus", "Sauber", "HRT", "Virgin" }; foreach (string team in teamNames) { Database.Insert("Team", new[] {"Name"}, new[] {team}); } }
private void button2_Click(object sender, System.EventArgs e) { var s = new[] { "Insert Into [SampleData] ([Name],[Role],[JoinDate],[Enabled]) Values ('angel',1,'2004-2-27 15:10:21',true)", "Insert Into [SampleData] ([Name],[Role],[JoinDate],[Enabled]) Values ('demon',2,'2005-10-20 9:22:10',false)" }; DbEntry.UsingTransaction(delegate() { DbEntry.Provider.ExecuteNonQuery(s[0]); DbEntry.Provider.ExecuteNonQuery(s[1]); if (MessageBox.Show("Do you want commit this transaction ?", "llf.hanzify.org", MessageBoxButtons.OKCancel) == DialogResult.Cancel) { Scope<ConnectionContext>.Current.Rollback(); } }); MessageBox.Show("OK"); }
public void Should_not_throw_any_exception() { // Fix for issue #12 https://github.com/rdingwall/protobuf-net-data/issues/12 var matrix = new[] { new object[] {"A", "B"}, new object[] {1, 2}, new object[] {10, 20}, }; using (var table = TestData.FromMatrix(matrix)) using (var reader = table.CreateDataReader()) using (var schemaTable = reader.GetSchemaTable()) { var originalReader = MockRepository.GenerateMock<IDataReader>(); schemaTable.Columns.Remove("Expression"); originalReader.Stub(r => r.GetSchemaTable()).Return(schemaTable); using (var stream = Stream.Null) new ProtoDataWriter().Serialize(stream, originalReader); } }
// GET: api/Recipes public async Task<IQueryable<Recipe>> GetRecipeAsync(ODataQueryOptions options) { var appUser = await AuthRepo.FindUserByName(User.Identity.Name); IQueryable results = options.ApplyTo(MatsMatRepo.GetAllRecipes()); //IQueryable results = MatsMatRepo.GetAllRecipes(); var result = results as IQueryable<Recipe>; var paginationHeader = new { TotalCount = Request.ODataProperties().TotalCount, Skip = options?.Skip?.Value ?? 0, Top = options?.Top?.Value ?? 0, Url = "" }; //Add pagination info to response header System.Web.HttpContext.Current.Response.AddHeader("Access-Control-Expose-Headers", "X-Pagination"); System.Web.HttpContext.Current.Response.Headers.Add("X-Pagination", Newtonsoft.Json.JsonConvert.SerializeObject(paginationHeader)); return result; }
private void AddCustomers() { var customers = new[] { new Customer { Name = "Customer 1", PrimaryAddress = new CustomerAddress { AddressLine1 = "1 Australia Street", Country = "Australia" }, }, new Customer { Name = "Customer 2", PrimaryAddress = new CustomerAddress { AddressLine1 = "2 America Street", Country = "USA" }, }, new Customer { Name = "Customer 3", PrimaryAddress = new CustomerAddress { AddressLine1 = "3 Canada Street", Country = "Canada" }, }, }; customers.Each(c => db.Save(c, references: true)); }
public int Save(User item) { using (IDbConnection conn = OpenConnection()) { const string query = "INSERT INTO dbo.[User]" + " ( Email ,Password ,Firstname ,Lastname ,CreatedDate ,LastLogin , AccountType ," + " Roles ,Displayname ,Avatar ,ThirdPartyId ,Status ,Ip, IsAdmin" + " )" + "VALUES ( @Email, @Password , @Firstname , @Lastname , @CreatedDate , @LastLogin ," + " @AccountType , @Roles , @Displayname , @Avatar , @ThirdPartyId," + " @Status , @Ip, @IsAdmin" + " )"; var parameters = new { Email = item.Email, Password = item.Password, Firstname = item.Firstname, Lastname = item.Lastname, CreatedDate = item.CreatedDate, LastLogin = item.LastLogin, AccountType = item.AccountType, Roles = item.Roles, Displayname = item.DisplayName, Avatar = item.Avatar, ThirdPartyId = item.ThirdPartyId, Status = item.Status, Ip = item.Ip, IsAdmin = item.IsAdmin }; conn.Execute(query, parameters); SetIdentity<int>(conn, id=> item.Id = id); return item.Id; } }
public virtual DataTable GetForeignKeys(string catalog, string schema, string table) { var restrictions = new[] {catalog, schema, table, null}; return connection.GetSchema(ForeignKeysSchemaName, restrictions); }
public virtual DataTable GetIndexColumns(string catalog, string schemaPattern, string tableName, string indexName) { var restrictions = new[] {catalog, schemaPattern, tableName, indexName, null}; return connection.GetSchema("IndexColumns", restrictions); }
public virtual DataTable GetColumns(string catalog, string schemaPattern, string tableNamePattern, string columnNamePattern) { var restrictions = new[] {catalog, schemaPattern, tableNamePattern, columnNamePattern}; return connection.GetSchema("Columns", restrictions); }
public virtual DataTable GetTables(string catalog, string schemaPattern, string tableNamePattern, string[] types) { var restrictions = new[] {catalog, schemaPattern, tableNamePattern, null}; return connection.GetSchema("Tables", restrictions); }
public void Can_do_joins_with_complex_wheres_using_SqlExpression() { var customers = AddCustomersWithOrders(); db.Insert( new Country { CountryName = "Australia", CountryCode = "AU" }, new Country { CountryName = "USA", CountryCode = "US" }); var results = db.Select<FullCustomerInfo, Customer>(q => q .Join<CustomerAddress>() //implicit .Join<Customer, Order>() //explicit .Where(c => c.Name == "Customer 1") .And<Order>(o => o.Cost < 2) .Or<Order>(o => o.LineItem == "Australia Flag")); var costs = results.ConvertAll(x => x.Cost); Assert.That(costs, Is.EquivalentTo(new[] { 1.99m, 1.49m, 9.99m })); var orderIds = results.ConvertAll(x => x.OrderId); var expectedOrderIds = new []{customers[0].Orders[0].Id, customers[0].Orders[2].Id, customers[0].Orders[4].Id}; Assert.That(orderIds, Is.EquivalentTo(expectedOrderIds)); //Same as above using using db.From<Customer>() results = db.Select<FullCustomerInfo>(db.From<Customer>() .Join<CustomerAddress>() //implicit .Join<Customer, Order>() //explicit .Where(c => c.Name == "Customer 1") .And<Order>(o => o.Cost < 2) .Or<Order>(o => o.LineItem == "Australia Flag")); costs = results.ConvertAll(x => x.Cost); Assert.That(costs, Is.EquivalentTo(new[] { 1.99m, 1.49m, 9.99m })); results = db.Select<FullCustomerInfo, Customer>(q => q .Join<Customer, CustomerAddress>() .Join<Customer, Order>() .Where(c => c.Name == "Customer 2") .And<CustomerAddress, Order>((a, o) => a.Country == o.LineItem)); costs = results.ConvertAll(x => x.Cost); Assert.That(costs, Is.EquivalentTo(new[] { 20m })); var countryResults = db.Select<FullCustomerInfo>(db.From<Customer>() .Join<CustomerAddress>() //implicit join with Customer .Join<Order>((c, o) => c.Id == o.CustomerId) //explicit join condition .Join<CustomerAddress, Country>((ca, c) => ca.Country == c.CountryName) .Where(c => c.Name == "Customer 2") //implicit condition with Customer .And<CustomerAddress, Order>((a, o) => a.Country == o.LineItem)); costs = countryResults.ConvertAll(x => x.Cost); Assert.That(costs, Is.EquivalentTo(new[] { 20m })); Assert.That(countryResults.ConvertAll(x => x.CountryCode), Is.EquivalentTo(new[] { "US" })); }
public void NaiveBayesConstructorTest5() { const int classes = 2; const int inputCount = 3; double[] classPriors = {0.4, 0.6}; var inputPriors = new [,] { {new UniformDiscreteDistribution(0,10), new UniformDiscreteDistribution(0,10), new UniformDiscreteDistribution(0,10)}, {new UniformDiscreteDistribution(0,10), new UniformDiscreteDistribution(0,10), new UniformDiscreteDistribution(0,10)} }; var target = new NaiveBayes<UniformDiscreteDistribution>(classes, inputCount, inputPriors, classPriors); Assert.AreEqual(classes, target.ClassCount); Assert.AreEqual(inputCount, target.InputCount); Assert.AreEqual(classPriors.Length, target.Priors.Length); Assert.AreEqual(0.4, target.Priors[0]); Assert.AreEqual(0.6, target.Priors[1]); Assert.AreEqual(2, target.Distributions.GetLength(0)); Assert.AreEqual(3, target.Distributions.GetLength(1)); }
private Customer[] AddCustomersWithOrders() { var customers = new[] { new Customer { Name = "Customer 1", PrimaryAddress = new CustomerAddress { AddressLine1 = "1 Australia Street", Country = "Australia" }, Orders = new[] { new Order {LineItem = "Line 1", Qty = 1, Cost = 1.99m}, new Order {LineItem = "Line 1", Qty = 2, Cost = 3.98m}, new Order {LineItem = "Line 2", Qty = 1, Cost = 1.49m}, new Order {LineItem = "Line 2", Qty = 2, Cost = 2.98m}, new Order {LineItem = "Australia Flag", Qty = 1, Cost = 9.99m}, }.ToList(), }, new Customer { Name = "Customer 2", PrimaryAddress = new CustomerAddress { AddressLine1 = "2 Prospect Park", Country = "USA" }, Orders = new[] { new Order {LineItem = "USA", Qty = 1, Cost = 20m}, }.ToList(), }, }; customers.Each(c => db.Save(c, references: true)); return customers; }
private static Person[] LagPersoner() { var personer = new[] { new Person { Name="Patric Bateman", Age=27, HairColor="Brun", Height=184, Gender="M"}, new Person { Name="Mystique", Age=127, HairColor="Rød", Height=177, Gender="K"}, new Person { Name="Two Face", Age=58, HairColor="Brun", Height=183, Gender="M"}, new Person { Name="Cruella De Vil", Age=65, HairColor="Svart og hvitt", Height=168, Gender="K"}, new Person { Name="Orochimaru", Age=100, HairColor="Svart", Height=180, Gender="M"}, new Person { Name="Harvey Dent", Age=56, HairColor="Brun", Height=183, Gender="M"}, new Person { Name="KongenDin", Age=75, HairColor="Ukjent", Height=150, Gender="M"} }; return personer; }
public void Does_populate_custom_columns_based_on_property_convention() { // Reset auto ids db.DropAndCreateTable<Order>(); db.DropAndCreateTable<CustomerAddress>(); db.DropAndCreateTable<Customer>(); var customer = AddCustomerWithOrders(); var results = db.Select<FullCustomerInfo, Customer>(q => q .Join<Customer, CustomerAddress>() .Join<Customer, Order>()); var addressIds = results.ConvertAll(x => x.CustomerAddressId); var expectedAddressIds = new[] { customer.PrimaryAddress.Id, customer.PrimaryAddress.Id }; Assert.That(addressIds, Is.EquivalentTo(expectedAddressIds)); var orderIds = results.ConvertAll(x => x.OrderId); var expectedOrderIds = new[] {customer.Orders[0].Id, customer.Orders[1].Id}; Assert.That(orderIds, Is.EquivalentTo(expectedOrderIds)); var customerNames = results.ConvertAll(x => x.CustomerName); Assert.That(customerNames, Is.EquivalentTo(new[] { "Customer 1", "Customer 1" })); var orderCosts = results.ConvertAll(x => x.OrderCost); Assert.That(orderCosts, Is.EquivalentTo(new[] { 1.99m, 2.99m })); var expr = db.From<Customer>() .Join<Customer, CustomerAddress>() .Join<Customer, Order>() .Where<Order>(o => o.Cost > 2); results = db.Select<FullCustomerInfo>(expr); addressIds = results.ConvertAll(x => x.CustomerAddressId); Assert.That(addressIds, Is.EquivalentTo(new[] { customer.PrimaryAddress.Id })); orderIds = results.ConvertAll(x => x.OrderId); Assert.That(orderIds, Is.EquivalentTo(new[] { customer.Orders[1].Id })); customerNames = results.ConvertAll(x => x.CustomerName); Assert.That(customerNames, Is.EquivalentTo(new[] { "Customer 1" })); orderCosts = results.ConvertAll(x => x.OrderCost); Assert.That(orderCosts, Is.EquivalentTo(new[] { 2.99m })); }
public static DataTable GetReservedWords() { DataTable table = new DataTable("ReservedWords"); table.Locale = CultureInfo.InvariantCulture; table.Columns.Add("ReservedWord", typeof(string)); // List of keywords taken from PostgreSQL 9.0 reserved words documentation. string[] keywords = new[] { "ALL", "ANALYSE", "ANALYZE", "AND", "ANY", "ARRAY", "AS", "ASC", "ASYMMETRIC", "AUTHORIZATION", "BINARY", "BOTH", "CASE", "CAST", "CHECK", "COLLATE", "COLUMN", "CONCURRENTLY", "CONSTRAINT", "CREATE", "CROSS", "CURRENT_CATALOG", "CURRENT_DATE", "CURRENT_ROLE", "CURRENT_SCHEMA", "CURRENT_TIME", "CURRENT_TIMESTAMP", "CURRENT_USER", "DEFAULT", "DEFERRABLE", "DESC", "DISTINCT", "DO", "ELSE", "END", "EXCEPT", "FALSE", "FETCH", "FOR", "FOREIGN", "FREEZE", "FROM", "FULL", "GRANT", "GROUP", "HAVING", "ILIKE", "IN", "INITIALLY", "INNER", "INTERSECT", "INTO", "IS", "ISNULL", "JOIN", "LEADING", "LEFT", "LIKE", "LIMIT", "LOCALTIME", "LOCALTIMESTAMP", "NATURAL", "NOT", "NOTNULL", "NULL", "OFFSET", "ON", "ONLY", "OR", "ORDER", "OUTER", "OVER", "OVERLAPS", "PLACING", "PRIMARY", "REFERENCES", "RETURNING", "RIGHT", "SELECT", "SESSION_USER", "SIMILAR", "SOME", "SYMMETRIC", "TABLE", "THEN", "TO", "TRAILING", "TRUE", "UNION", "UNIQUE", "USER", "USING", "VARIADIC", "VERBOSE", "WHEN", "WHERE", "WINDOW", "WITH" }; foreach (string keyword in keywords) { table.Rows.Add(keyword); } return table; }
private AliasedCustomer[] AddAliasedCustomers(out Country[] countries) { db.DropAndCreateTable<AliasedCustomer>(); db.DropAndCreateTable<AliasedCustomerAddress>(); db.DropAndCreateTable<Country>(); var customers = new[] { new AliasedCustomer { Name = "Customer 1", PrimaryAddress = new AliasedCustomerAddress { AddressLine1 = "1 Australia Street", Country = "Australia" }, }, new AliasedCustomer { Name = "Customer 2", PrimaryAddress = new AliasedCustomerAddress { AddressLine1 = "2 America Street", Country = "USA" }, }, new AliasedCustomer { Name = "Customer 3", PrimaryAddress = new AliasedCustomerAddress { AddressLine1 = "3 Canada Street", Country = "Canada" }, }, }; customers.Each(c => db.Save(c, references: true)); countries = new[] { new Country {CountryName = "Australia", CountryCode = "AU"}, new Country {CountryName = "USA", CountryCode = "US"}, new Country {CountryName = "Italy", CountryCode = "IT"}, new Country {CountryName = "Spain", CountryCode = "ED"} }; db.Save(countries); return customers; }