public PagedResult<Party> GetParties(int page, int resultsPerPage) { PagedResult<Party> parties = new PagedResult<Party>(); parties.PageNumber = page; parties.ItemsPerPage = resultsPerPage; string sql = @"Select VintageRabbit.Parties.* From VintageRabbit.Parties Inner Join VintageRabbit.Orders On VintageRabbit.Parties.OrderGuid = VintageRabbit.Orders.Guid Where VintageRabbit.Orders.Status In ('Complete', 'AwaitingShipment') Order By VintageRabbit.Parties.DateCreated Desc OFFSET @Offset ROWS FETCH NEXT @ResultsPerPage ROWS ONLY; Select Count(*) From VintageRabbit.Parties;"; int offset = (page - 1) * resultsPerPage; using (SqlConnection connection = new SqlConnection(this._connectionString)) { using (var multi = connection.QueryMultiple(sql, new { Offset = offset, ResultsPerPage = resultsPerPage })) { parties.AddRange(multi.Read<Party>()); parties.TotalResults = multi.Read<int>().First(); } } return parties; }
public OverviewDataModel GetReleaseOverviewData(string includedReleasePathIDs) { var data = new OverviewDataModel { LastRefresh = DateTime.Now }; string[] pathIDs = includedReleasePathIDs != null ? includedReleasePathIDs.Split(new[] { ',' }) : null; DynamicParameters queryArguments = new DynamicParameters(); if (pathIDs != null) { for (int paramNo = 0; paramNo < pathIDs.Length; paramNo++) { queryArguments.Add(String.Format("p{0}", paramNo), pathIDs[paramNo]); } } using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ReleaseManagement"].ConnectionString)) { var sql = GenerateOverviewSQL(pathIDs != null ? pathIDs.Length : 0); using (var multi = connection.QueryMultiple(sql, queryArguments)) { data.ReleasePaths = multi.Read<ReleasePath>().ToList(); data.Stages = multi.Read<OverviewStage>().ToList(); data.StageDetails = multi.Read<OverviewStageDetails>().ToList(); data.ReleaseComponents = multi.Read<Component>().ToList(); } } return data; }
public static void Main() { using (var sqlConnection = new SqlConnection(Constant.DatabaseConnection)) { sqlConnection.Open(); var query = @" SELECT * FROM dbo.Suppliers WHERE Id = @Id SELECT * FROM dbo.Products WHERE SupplierID = @Id "; // return a GridReader using (var result = sqlConnection.QueryMultiple(query, new {Id = 1})) { var supplier = result.Read<Supplier>().Single(); var products = result.Read<Product>().ToList(); ObjectDumper.Write(supplier); Console.WriteLine(string.Format("Total Products {0}", products.Count)); ObjectDumper.Write(products); } } }
public List<Location> GetAll() { var query = FormQuery(); using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ABS-SQL"].ConnectionString)) { using (var multi = connection.QueryMultiple(query)) { return FindLocations(multi).ToList(); } } }
public Order Get(Guid id) { using (var connection = new SqlConnection(SqlConnectionLocator.LocalhostSqlExpress())) { const string query = SqlQueries.SelectOrdersByIdQuery + " " + SqlQueries.SelectOrderLinesByIdQuery; using (var multi = connection.QueryMultiple(query, new {id})) { var order = multi.Read<Order>().SingleOrDefault(); if (order != null) { order.Lines = multi.Read<OrderLine>().ToList(); } return order; } } }
// 2.8s public static void DapperReadMultiResultSet() { using (SqlConnection connection = new SqlConnection(connStr)) { using (var reader = connection.QueryMultiple("select * from Posts;select 1000 as Number;")) { var postList = reader.Read<Post>(); foreach (var item in postList) { Console.WriteLine("ID:{0},Title:{1}", item.Id, item.Title); } } } }
public Order Get(Guid id) { using (var connection = new SqlConnection(SqlConnectionLocator.LocalhostSqlExpress())) { const string query = SqlQueries.SelectOrdersByIdQuery + " " + SqlQueries.SelectOrderLinesByIdQuery; using (var multi = connection.QueryMultiple(query, new {id})) { var persistentModel = multi.Read<OrderPersistentModel>().SingleOrDefault(); if (persistentModel == null) { return null; } persistentModel.Lines = multi.Read<OrderLinePersistentModel>().ToList(); persistentModel.Lines.ForEach(x => x.OrderId = id); return _orderMapper.ToDomainModel(persistentModel); } } }
public Order Get(Guid id) { using (var connection = new SqlConnection(SqlConnectionLocator.LocalhostSqlExpress())) { const string query = SqlQueries.SelectOrdersByIdQuery + " " + SqlQueries.SelectOrderLinesByIdQuery; using (var multi = connection.QueryMultiple(query, new {id})) { var orderState = multi.Read<OrderState>().SingleOrDefault(); if (orderState == null) { return null; } orderState.Lines = multi.Read<OrderLineState>().ToList(); return new Order.FromState().Build(orderState); } } }
/// <summary> /// Get suggestions. /// In the request specify either RowsWithHeaders or Rows. /// </summary> /// <param name="roleId">The current role id</param> /// <param name="request"> /// <para>RowWithHeaders (List of sting[]): If not null, call ValidateInput. The first string[] is the headers. The rest are data to be imported.</para> /// <para>Rows (ImportRow[]): If not null, call SuggestEntites. The rows to be imported</para> /// <para>If both parameters are set or null an error will be thrown.</para></param> /// <returns>Entites with suggestions</returns> public Suggestions Put(Guid roleId, SuggestionsRequest request) { //If both parameters are set or null an error will be thrown if ((request.RowsWithHeaders == null && request.Rows == null) || (request.RowsWithHeaders != null && request.Rows != null)) throw Request.BadRequest(); var businessAccount = CoreEntitiesContainer.Owner(roleId, new[] { RoleType.Administrator }).FirstOrDefault(); if (businessAccount == null) throw Request.BadRequest(); //load all of the BusinessAccount's Clients, Locations, Regions and ContactInfoSets using (var conn = new SqlConnection(ServerConstants.SqlConnectionString)) { conn.Open(); using (var data = conn.QueryMultiple(Sql, new { id = businessAccount.Id })) { _locations = new ConcurrentDictionary<Guid, Location>(data.Read<Location>().ToDictionary(l => l.Id, l => l)); _clients = new ConcurrentDictionary<Guid, Client>(data.Read<Client>().ToDictionary(l => l.Id, l => l)); _regions = new ConcurrentDictionary<Guid, Core.Models.CoreEntities.Region>(data.Read<Core.Models.CoreEntities.Region>().ToDictionary(l => l.Id, l => l)); _contactInfoSets = new ConcurrentDictionary<Guid, Core.Models.CoreEntities.ContactInfo>(data.Read<Core.Models.CoreEntities.ContactInfo>().ToDictionary(l => l.Id, l => l)); } conn.Close(); } //var itemsProcessed = 0; //var updater = new Subject<Unit>(); //updater.Subscribe(u => // { // itemsProcessed += 1; // //Update the progress bars value here // //Call OnNext at the end of each loop // //Set Max on the progress bar to the number of rows if SuggestEntities is called // //Set Max on the progress bar to TWICE the number of rows if ValidateThenSuggestEntities is called (goes through both methods) // }); //Call the appropriate function and return the Suggestions return request.RowsWithHeaders != null ? this.ValidateThenSuggestEntities(request.RowsWithHeaders) : this.SuggestEntites(request.Rows); }
public IDictionary<Guid, int> CountMessages() { using (var sqlConnection = new SqlConnection(_connectionString)) { var commandBuilder = new StringBuilder(); commandBuilder.Append("exec CountMessages @categoryId;"); commandBuilder.Append("exec CountAllMessages;"); var command = new CommandDefinition(commandBuilder.ToString(), new { categoryId = Guid.Empty }); var resultReader = sqlConnection.QueryMultiple(command); var byCategoriesResult = resultReader.Read().ToDictionary(row => (Guid)row.CategoryId, row => (int)row.MessageCount); var totalResult = resultReader.Read().ToDictionary(row => (Guid)row.CategoryId, row => (int)row.MessageCount); return byCategoriesResult.Union(totalResult).ToDictionary(x => x.Key, x => x.Value); } }
private static IEnumerable<Supplier> QuerySupplier() { using (var sqlConnection = new SqlConnection(Constant.DatabaseConnection)) { sqlConnection.Open(); var query = @" SELECT * FROM dbo.Suppliers WHERE ContactName = 'Charlotte Cooper' SELECT * FROM dbo.Products WHERE SupplierID IN (SELECT Id FROM dbo.Suppliers WHERE ContactName = 'Charlotte Cooper') "; return sqlConnection .QueryMultiple(query).Map<Supplier, Product, int>(supplier => supplier.Id, product => product.SupplierID, (supplier, products) => { supplier.Products = products; }); } }
public DataModel GetReleaseData(string includedReleasePathIds, int releaseCount) { var data = new DataModel { LastRefresh = DateTime.Now }; using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ReleaseManagement"].ConnectionString)) { var sql = GenerateSQL(releaseCount, includedReleasePathIds); // query database using (var multi = connection.QueryMultiple(sql, new { numberOfReleasesToShow = releaseCount })) { data.Releases = multi.Read<Release>().ToList(); data.StageWorkflows = multi.Read<StageWorkflow>().ToList(); data.Stages = multi.Read<Stage>().ToList(); data.Environments = multi.Read<Models.Environment>().ToList(); data.ReleaseSteps = multi.Read<Step>().ToList(); data.ReleaseComponents = multi.Read<Component>().ToList(); } } return data; }
public IEnumerable<Customer> GetCustomers() { var getProductsSql = @" SELECT c.CustomerID, max(od.UnitPrice) as UnitPrice, sum(od.Quantity) as TotalQuantity, p.ProductName, ca.CategoryName FROM Customers c INNER JOIN Orders o ON c.CustomerId = o.CustomerId INNER JOIN [Order Details] od ON od.OrderID = o.OrderID INNER JOIN Products p ON p.ProductID = od.ProductID INNER JOIN Categories ca ON ca.CategoryID = p.CategoryID GROUP BY c.CustomerID, p.ProductName, ca.CategoryName ORDER BY c.CustomerID "; var getCustomersSql = @" SELECT CustomerID, CompanyName, ContactName, Address, City, Country FROM Customers "; var connectionString = Settings.NorthwndConnectionString; using (var connection = new SqlConnection(connectionString)) { try { var sets = connection.QueryMultiple(getCustomersSql + getProductsSql); var customers = sets.Read<Customer>().ToDictionary(y => y.CustomerID, y => y); var customerProdMapping = customers.ToDictionary(y => y.Key, y => new List<Product>()); var products = sets.Read((string cId, Product p) => { customerProdMapping[cId].Add(p); return p; }, splitOn: "UnitPrice"); customerProdMapping.ToList().ForEach(y => customers[y.Key].Products = y.Value.ToArray()); return customers.Values; } catch (Exception ex) { throw; } } }
private static void MultiResults() { using (var cn = new SqlConnection(CONNECTION_STRING)) { cn.Open(); var multiResults = cn.QueryMultiple( @" SELECT * FROM Contacts WHERE ID = 1 SELECT * FROM Orders WHERE ID = 1 ", new { id = 1 }); var contact = multiResults.Read<Contact>().First(); var order = multiResults.Read<Order>().First(); Console.WriteLine("Found contact id {0} (Multiple Results)", contact.Id); Console.WriteLine("Found order id {0}", order.Id); } Console.ReadKey(); }
public IQueryable<ServiceHolder> GetServiceHolders(Guid roleId, Guid? clientContext, Guid? recurringServiceContext, DateTime seedDate, int numberOfOccurrences, bool getPrevious, bool getNext) { Guid? recurringServiceContextId = null; Guid? clientContextId = null; Guid? serviceProviderContextId = null; if (recurringServiceContext.HasValue) { //Check the user has access to the RecurringService if (!RecurringServicesForServiceProviderOptimized(roleId).Any(rs => rs.Id == recurringServiceContext)) return null; recurringServiceContextId = recurringServiceContext; } else if (clientContext.HasValue) { //Check the user has access to the Client if (!GetClientsForRole(roleId).Any(c => c.Id == clientContext)) return null; clientContextId = clientContext; } else { var businessAccount = ObjectContext.Owner(roleId).First(); //Because there is no other context, assume the context should be the serviceProvider //The businessForRole returns null if the user does not have access to the ServiceProvider serviceProviderContextId = businessAccount.Id; } var user = ObjectContext.CurrentUserAccount(); if (user == null) throw new Exception("No User logged in"); using (var conn = new SqlConnection(ServerConstants.SqlConnectionString)) { conn.Open(); var parameters = new DynamicParameters(); parameters.Add("@serviceProviderIdContext", serviceProviderContextId); parameters.Add("@clientIdContext", clientContextId); parameters.Add("@recurringServiceIdContext", recurringServiceContextId); parameters.Add("@seedDate", user.Now().Date); parameters.Add("@frontBackMinimum", 50); parameters.Add("@getPrevious", 1); parameters.Add("@getNext", 1); parameters.Add("@serviceTypeContext", null); //Calls a stored procedure that will find any Services scheduled for today and create a routetask for them if one doesnt exist //Then it will return all RouteTasks that are not in a route joined with their Locations, Location.Regions and Clients //Dapper will then map the output table to RouteTasks, RouteTasks.Location, RouteTasks.Location.Region and RouteTasks.Client //While that is being mapped we also attach the Client, Location and Region to the objectContext var data = conn.QueryMultiple("GetDateRangeForServices", parameters, commandType: CommandType.StoredProcedure); var firstDate = data.Read<DateTime>().Single(); var lastDate = data.Read<DateTime>().Single(); //Reset parameters parameters = new DynamicParameters(); parameters.Add("@serviceProviderIdContext", serviceProviderContextId); parameters.Add("@clientIdContext", clientContextId); parameters.Add("@recurringServiceIdContext", recurringServiceContextId); parameters.Add("@firstDate", firstDate); parameters.Add("@lastDate", lastDate); parameters.Add("@serviceTypeContext", null); parameters.Add("@withFields", false); var serviceHolders = conn.Query<ServiceHolder>("GetServiceHolders", parameters, commandType: CommandType.StoredProcedure); conn.Close(); //Order by OccurDate and Service Name for UI //Order by ServiceId and RecurringServiceId to ensure they are always returned in the same order return serviceHolders.OrderBy(sh => sh.OccurDate).ThenBy(sh => sh.ServiceName).ThenBy(sh => sh.ServiceId).ThenBy(sh => sh.RecurringServiceId).AsQueryable(); } }
public PagedResult<Product> GetProductsByType(ProductType type, int page, int itemsPerPage) { PagedResult<Product> result = new PagedResult<Product>(); result.PageNumber = page; result.ItemsPerPage = itemsPerPage; using (SqlConnection connection = new SqlConnection(this._connectionString)) { string sql = @"Select * From VintageRabbit.Products Where [Type] = @Type Order By DateCreated Desc OFFSET @Offset ROWS FETCH NEXT @ResultsPerPage ROWS ONLY; Select Count(*) From VintageRabbit.Products Where [Type] = @Type;"; int offset = (page - 1) * itemsPerPage; using (var multi = connection.QueryMultiple(sql, new { Type = type.ToString(), Offset = offset, ResultsPerPage = itemsPerPage })) { var productResults = multi.Read<ProductDb>(); foreach (var product in productResults) { result.Add(this.ConvertToProduct(product)); } result.TotalResults = multi.Read<int>().First(); } } return result; }
public PagedResult<Product> GetProductsByCategory(ProductType type, Category category, int page, int itemsPerPage) { PagedResult<Product> result = new PagedResult<Product>(); result.PageNumber = page; result.ItemsPerPage = itemsPerPage; IList<int> categoryIds = new List<int>() { category.Id }; foreach(var child in category.Children) { categoryIds.Add(child.Id); } string sql = @"Select * From VintageRabbit.Products Where [Type] = @Type And VintageRabbit.Products.Id In (Select Distinct ProductId From VintageRabbit.ProductCategories Where CategoryId In @CategoryIds) Order By DateCreated Desc OFFSET @Offset ROWS FETCH NEXT @ResultsPerPage ROWS ONLY; Select Count(*) From VintageRabbit.Products Where [Type] = @Type And VintageRabbit.Products.Id In (Select Distinct ProductId From VintageRabbit.ProductCategories Where CategoryId In @CategoryIds);"; int offset = (page - 1) * itemsPerPage; using (SqlConnection connection = new SqlConnection(this._connectionString)) { using (var multi = connection.QueryMultiple(sql, new { CategoryIds = categoryIds, Type = type.ToString(), Offset = offset, ResultsPerPage = itemsPerPage })) { var productResults = multi.Read<ProductDb>(); foreach (var product in productResults) { result.Add(this.ConvertToProduct(product)); } result.TotalResults = multi.Read<int>().First(); } } return result; }
public void Example7_Dapper() { var sql = @" select * from Sales.Customer where CustomerKey = @id select * from Sales.[Order] where CustomerKey = @id select * from Sales.[Return] where CustomerKey = @id"; var selectedId = 1; using (var connection = new SqlConnection(s_ConnectionString)) { using (var multi = connection.QueryMultiple(sql, new { id = selectedId })) { var customer = multi.Read<Customer>().SingleOrDefault(); var orders = multi.Read<Order>().ToList(); var returns = multi.Read<Return>().ToList(); } } }
public int[] GetRecurringServiceCountForLocation(Guid locationId) { var date = DateTime.UtcNow.Date; const string sql = @"SELECT COUNT(*) FROM dbo.RecurringServices WHERE Id IN ( SELECT Id FROM dbo.ServiceTemplates WHERE Id IN ( SELECT ServiceTemplateId FROM dbo.Fields WHERE Id IN ( SELECT Id FROM dbo.Fields_LocationField WHERE LocationId = @Id ) ) ) SELECT COUNT(*) FROM dbo.Services WHERE Id IN ( SELECT Id FROM dbo.ServiceTemplates WHERE Id IN ( SELECT ServiceTemplateId FROM dbo.Fields WHERE Id IN ( SELECT Id FROM dbo.Fields_LocationField WHERE LocationId = @Id ) ) ) AND ServiceDate >= @Date AND RecurringServiceId IS NULL"; using (var conn = new SqlConnection(ServerConstants.SqlConnectionString)) { conn.Open(); var data = conn.QueryMultiple(sql, new { Id = locationId, Date = date }); var recurringServiceCount = data.Read<int>().Single(); var futureServiceCount = data.Read<int>().Single(); conn.Close(); var array = new int[] {recurringServiceCount, futureServiceCount}; return array; } }
private static void Main() { using (var conn = new SqlConnection("Server=localhost;Database=DapperSample2013;Trusted_Connection=True;")) { conn.Open(); using (var qm = conn .QueryMultiple(@" SELECT TOP 1 FirstName, LastName, DisplayName FROM Customer WITH(NOLOCK) ORDER BY NEWID() SELECT TOP 5 FirstName, LastName, DisplayName, CellPhoneNumber, WorkPhoneNumber, HomePhoneNumber, EmailAddress, Password, ModifiedDate, Created FROM Customer WITH(NOLOCK) ORDER BY NEWID() SELECT TOP 5 FirstName, LastName, DisplayName, CellPhoneNumber, WorkPhoneNumber, HomePhoneNumber, EmailAddress, Password, ModifiedDate, Created FROM Customer WITH(NOLOCK) ORDER BY NEWID() ")) { var salesPerson = qm.Read<SalesPerson>().FirstOrDefault(); if (salesPerson == null) { Console.WriteLine("No sales person found"); return; } salesPerson.Customers = qm.Read<Customer>().ToList(); salesPerson.Leads = qm.Read<Customer>().ToList(); Console.WriteLine( "Sales person: {0}\r\nCustomers:\r\n\t{1}\r\nLeads:\r\n\t{2}", salesPerson.DisplayName, string.Join( "\r\n\t", salesPerson.Customers.Select(customer => customer.DisplayName) ), string.Join( "\r\n\t", salesPerson.Leads.Select(lead => lead.DisplayName) ) ); } } }