C# IQuery.OpenConnection方法代码示例

        public ProjectModule(IQuery queries)
            : base("/project")
        {
            Get["/{id:int}", true] = async (_, ctx) =>
            {
                var id = int.Parse(_.id);
                var model = this.Bind<UserDetailsRequest>();
                var response = new ProjectWithFeaturesResponse();

                var incompleteAttributes = string.IsNullOrEmpty(model.Token) || string.IsNullOrEmpty(model.Key);
                response.AllowEdits = !incompleteAttributes;

                var db = await queries.OpenConnection();
                using (var connection = db.Connection)
                {
                    if (!db.Open)
                    {
                        return Negotiate.WithReasonPhrase("Database Error")
                            .WithStatusCode(HttpStatusCode.InternalServerError)
                            .WithModel("Unable to connect to the database.");
                    }

                    var project = await queries.ProjectQueryAsync(connection, new {id});
                    response.Project = project;

                    var features = await queries.FeatureQueryAsync(connection, new {id});
                    response.Features = features;

                    var reason = "";
                    if (incompleteAttributes)
                    {
                        return Negotiate.WithModel(response)
                            .WithStatusCode(HttpStatusCode.OK)
                            .WithHeader("reason", "incomplete attributes");
                    }

                    var users = await queries.UserQueryAsync(connection, new {key = model.Key, token = model.Token});
                    var user = users.FirstOrDefault();

                    // make sure user is valid
                    if (user == null)
                    {
                        response.AllowEdits = false;
                        reason = "User is null. ";
                    }

                    else if (response.Project == null)
                    {
                        response.AllowEdits = false;
                        reason = "Project not found. ";
                    }

                    // anonymous and public users cannot create features
                    else if (new[] {"GROUP_ANONYMOUS", "GROUP_PUBLIC"}.Contains(user.Role))
                    {
                        response.AllowEdits = false;
                        reason += "Roles is anonymous or public. ";
                    }

                    // if project has features no or null, block feature creation
                    else if (response.Project.Features == "No" && user.Role != "GROUP_ADMIN")
                    {
                        response.AllowEdits = false;
                        reason += "Project is designated to have no features. ";
                    }

                    // cancelled and completed projects cannot be edited
                    else if (new[] {"Cancelled", "Completed"}.Contains(response.Project.Status) &&
                             user.Role != "GROUP_ADMIN")
                    {
                        response.AllowEdits = false;
                    }

                    // check if a user is a contributor
                    else if (response.Project.ProjectManagerId != user.Id && user.Role != "GROUP_ADMIN")
                    {
                        var counts = await queries.ContributorQueryAsync(connection, new {id, userId = user.Id});
                        var count = counts.FirstOrDefault();

                        if (count == 0)
                        {
                            response.AllowEdits = false;
                            reason += "User is not a contributor. ";
                        }
                    }

                    return Negotiate.WithModel(response)
                            .WithHeader("reason", reason);
                }
            };
        }

        public HistoricalModule(IQuery queries, ISoeService soeService)
            : base("/historical/project/{id:int}")
        {
            Put["/create-related-data", true] = async (_, ctx) =>
            {
                var id = int.Parse(_.id);

                var ten = TimeSpan.FromSeconds(600);

                var db = await queries.OpenConnection();
                using (var transaction = new TransactionScope(TransactionScopeOption.RequiresNew, ten, TransactionScopeAsyncFlowOption.Enabled))
                using (var connection = db.Connection)
                {
                    if (!db.Open)
                    {
                        return Negotiate.WithReasonPhrase("Database Error")
                            .WithStatusCode(HttpStatusCode.InternalServerError)
                            .WithModel("Unable to connect to the database.");
                    }

                    var projects = await queries.ProjectMinimalQueryAsync(connection, new {id = (int) id});
                    var project = projects.FirstOrDefault();

                    // make sure project id is valid
                    if (project == null)
                    {
                        return Negotiate.WithReasonPhrase("Project not found")
                            .WithStatusCode(HttpStatusCode.BadRequest)
                            .WithModel("Project not found.");
                    }

                    const string geometryQuery =
                        "SELECT [Shape] shape, [FeatureId], [TypeDescription] category FROM {0} WHERE [Project_ID] = @id";

                    foreach (var table in new[] {"POINT", "LINE", "POLY"})
                    {
                        var query = string.Format(geometryQuery, table);
                        var features = await connection.QueryAsync<SpatialFeatureSimple>(query, new
                        {
                            id = (int) id
                        });

                        foreach (var feature in features)
                        {
                            var soeAreaAndLengthResponse = await soeService.QueryAreasAndLengthsAsync(feature.Shape);

                            // handle error from soe
                            if (!soeAreaAndLengthResponse.IsSuccessful)
                            {
                                return Negotiate.WithReasonPhrase(soeAreaAndLengthResponse.Error.Message)
                                    .WithStatusCode(HttpStatusCode.InternalServerError)
                                    .WithModel(soeAreaAndLengthResponse.Error.Message);
                            }

                            var size = soeAreaAndLengthResponse.Result.Size;
                            if (table == "POLY")
                            {
                                await connection.ExecuteAsync("UPDATE [dbo].[POLY]" +
                                                              "SET [AreaSqMeters] = @size " +
                                                              "WHERE [FeatureID] = @featureId", new
                                                              {
                                                                  size,
                                                                  featureId = feature.FeatureId
                                                              });
                            }
                            else if (table == "LINE")
                            {
                                await connection.ExecuteAsync("UPDATE [dbo].[LINE]" +
                                                              "SET [LengthLnMeters] = @size " +
                                                              "WHERE [FeatureID] = @featureId", new
                                                              {
                                                                  size,
                                                                  featureId = feature.FeatureId
                                                              });
                            }

                            var soeResponse = await soeService.QueryIntersectionsAsync(feature.Shape, feature.Category);

                            // handle error from soe
                            if (!soeResponse.IsSuccessful)
                            {
                                return Negotiate.WithReasonPhrase(soeResponse.Error.Message)
                                    .WithStatusCode(HttpStatusCode.InternalServerError)
                                    .WithModel(soeResponse.Error.Message);
                            }

                            var attributes = soeResponse.Result.Attributes;

                            // insert related tables
                            var featureClass = table;
                            var primaryKey = feature.FeatureId;

                            await connection.ExecuteAsync("delete from [wri].[dbo].county WHERE [featureId] = @id;" +
                                                          "delete from [wri].[dbo].FOCUSAREA WHERE [featureId] = @id;" +
                                                          "delete from [wri].[dbo].sgma WHERE [featureId] = @id;" +
                                                          "delete from [wri].[dbo].LANDOWNER WHERE [featureId] = @id",
                                new
                                {
                                    id = primaryKey
                                });
//.........这里部分代码省略.........

        public FeatureModule(IQuery queries, IAttributeValidator validator, ISoeService soeService)
            : base("/project/{id:int}")
        {
            Get["/feature/{featureId:int}", true] = async (_, ctx) =>
            {
                var model = this.Bind<SpecificFeatureRequest>();

                // make sure feature type is valid
                if (model.FeatureCategory == null || !FeatureCategoryToTable.Contains(model.FeatureCategory.ToLower()))
                {
                    return Negotiate.WithReasonPhrase("Incomplete request")
                        .WithStatusCode(HttpStatusCode.BadRequest)
                        .WithModel("Category not found.");
                }

                IEnumerable<RelatedDetails> records;

                var db = await queries.OpenConnection();
                using (var connection = db.Connection)
                {
                    if (!db.Open)
                    {
                        return Negotiate.WithReasonPhrase("Database Error")
                            .WithStatusCode(HttpStatusCode.InternalServerError)
                            .WithModel("Unable to connect to the database.");
                    }

                    // get the database table to use
                    var table = FeatureCategoryToTable.GetTableFrom(model.FeatureCategory);

                    records = await queries.RelatedDataQueryAsync(connection, new
                    {
                        table,
                        model.FeatureId
                    });
                }

                var response = new SpatialFeatureResponse
                {
                    County = records.Where(x => x.Origin == "county"),
                    FocusArea = records.Where(x => x.Origin == "focus"),
                    SageGrouse = records.Where(x => x.Origin == "sgma"),
                    LandOwnership = records.Where(x => x.Origin == "owner"),
                    Nhd = records.Where(x => x.Origin == "nhd")
                };

                return response;
            };

            Post["/feature/create", true] = async (_, ctx) =>
            {
                var id = int.Parse(_.id);
                var model = this.Bind<SaveFeatureRequest>();

                // make sure we have all the user information.
                if (string.IsNullOrEmpty(model.Token) || string.IsNullOrEmpty(model.Key))
                {
                    return Negotiate.WithReasonPhrase("User not found")
                        .WithStatusCode(HttpStatusCode.BadRequest)
                        .WithModel("User not found.");
                }

                // make sure we have most of the attributes.
                if (new[] {model.Category, model.Geometry}.Any(string.IsNullOrEmpty))
                {
                    return Negotiate.WithReasonPhrase("Incomplete request")
                        .WithStatusCode(HttpStatusCode.BadRequest)
                        .WithModel("Missing required parameters. Category, geometry, or actions.");
                }

                // make sure feature type is valid
                if (!FeatureCategoryToTable.Contains(model.Category))
                {
                    return Negotiate.WithReasonPhrase("Incomplete request")
                        .WithStatusCode(HttpStatusCode.BadRequest)
                        .WithModel("Category not found.");
                }

                // get the database table to use
                var table = FeatureCategoryToTable.GetTableFrom(model.Category);

                var ten = TimeSpan.FromSeconds(600);

                var db = await queries.OpenConnection();
                using (
                    var transaction = new TransactionScope(TransactionScopeOption.RequiresNew, ten,
                        TransactionScopeAsyncFlowOption.Enabled))
                using (var connection = db.Connection)
                {
                    if (!db.Open)
                    {
                        return Negotiate.WithReasonPhrase("Database Error")
                            .WithStatusCode(HttpStatusCode.InternalServerError)
                            .WithModel("Unable to connect to the database.");
                    }

                    var projects = await queries.ProjectMinimalQueryAsync(connection, new {id});
                    var project = projects.FirstOrDefault();

                    // make sure project id is valid
//.........这里部分代码省略.........