C# SqlConnection.QueryMultiple方法代码示例

        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();
            }
        }