Database Push Method (Editing record MS SQL)

Database Push Method (Editing record MS SQL)

georgemcflygeorgemcfly Posts: 18Questions: 4Answers: 0

When working with a table in SQL Azure, the Geometry and Geography datatypes are causing DataReader.GetFieldTypes errors. Looking at this method, I would also think it would be more performant to use the set collection to name the fields that are being updated? (Maybe you have this for Inserts). Either way I am unable to edit tables with this datatypes. Add and delete work fine.

This question has accepted answers - jump to:

Answers

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Hi,

    Is this using Editor's .NET libraries or are you using something else on the server-side?

    Allan

  • georgemcflygeorgemcfly Posts: 18Questions: 4Answers: 0

    Editor's .NET Libraries

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Thanks! Are you using a model for this? The model currently needs to contain types which are directly representable in JSON - e.g. string, int, etc. More complex types can't be represented in JSON.

    Perhaps you can show me some of the code you are using?

    Thanks,
    Allan

  • georgemcflygeorgemcfly Posts: 18Questions: 4Answers: 0

    The model is basic, there are two fields on the underlying table that have datatypes of geography and geometry that is causing the problems. Here is the model
    public class AddressModelDT
    {

        [JsonProperty("Name")]
        public string Name { get; set; }
        public string Address1 { get; set; }
        public string Address2 { get; set; }
        public string Address3 { get; set; }
        public string City { get; set; }
        public string State { get; set; }
        public string Zip { get; set; }
        public string Lat { get; set; }
        public string Long { get; set; }
        public int IsBilling { get; set; }
        public int IsPrimary { get; set; }
        public int AddressTypeId { get; set; }
        public decimal? TaxTotalRate { get; set; }
        public decimal? TaxCityRate { get; set; }
        public decimal? TaxCountyRate { get; set; }
        public decimal? TaxStateRate { get; set; }
    }
    

    and the code that executes it

         private DtResponse ProcessAddress(HttpRequest formData)
        {
            using (var db = new Database("sqlserver", _db.Database.Connection))
            {
                var response = new Editor(db, "Address", "Id")
                    .Model<AddressModelDT>()
                    .Debug(true)
                    .Field(new DataTables.Field("Name")
                        .Validator(Validation.NotEmpty())
                    )
                    .Field(new DataTables.Field("Address1")
                        .Validator(Validation.NotEmpty())
                    )
                    .Field(new DataTables.Field("AddressTypeId")
                        .Options(() => new List<Dictionary<string, object>>{
                            new Dictionary<string, object>{ {"value", "1"}, {"label", "Primary"} },
                            new Dictionary<string, object>{ {"value", "2"}, {"label", "Billing"} },
                            new Dictionary<string, object>{ {"value", "3"}, {"label", "Shipping"} },
                            new Dictionary<string, object>{ {"value", "4"}, {"label", "Location"} }
                        })
                    )
                    .Field(new DataTables.Field("City")
                        .Validator(Validation.NotEmpty())                        
                    )
                    .Field(new DataTables.Field("State")
                        .Validator(Validation.NotEmpty())
                        .Validator(Validation.MaxLen(2, new ValidationOpts { Message = "State must be in 2 Digit format i.e. GA" }))
                    )
                    .Field(new DataTables.Field("Zip")
                        .Validator(Validation.NotEmpty())
                    )
                    .Process(formData)
                    .Data();
                return response;
            }
        }
    

    Thanks!

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin
    Answer ✓

    Super - thanks!

    I was able to reproduce this locally as well simply by using a Geography data type in the db and making a "string" in the model.

    This thread helped to resolve it locally for me. This one is also relevant.

    Looking at this method, I would also think it would be more performant to use the set collection to name the fields that are being updated?

    Could you clarify what you mean by that?

    Thanks,
    Allan

  • georgemcflygeorgemcfly Posts: 18Questions: 4Answers: 0

    Thanks Allan

  • georgemcflygeorgemcfly Posts: 18Questions: 4Answers: 0

    Oh on the last part, I very really do Select * from a database table because it in the past has always played a heavier load on the DB and returns more data than I need. Not sure if that is still the case with current providers.

  • georgemcflygeorgemcfly Posts: 18Questions: 4Answers: 0

    Well I clicked the answer too quickly. It is still happening. I'll investigate a little deeper but the API layer is in an Azure App Service and I used the Cloud Explorer to verify the Microsoft.SqlServer.Types.dll is in the bin.

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    I very really do Select * from a database table because it in the past has always played a heavier load on the DB

    I don't think Editor does a SELECT * anywhere - it should always list the fields that it wants to get from the db. Is that not the case for you?

    Thanks,
    Allan

  • georgemcflygeorgemcfly Posts: 18Questions: 4Answers: 0

    I see it in the Push method of the Database class.

  • georgemcflygeorgemcfly Posts: 18Questions: 4Answers: 0

    Just curious to hear the final word on this?

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Yes I see what you mean. Perhaps it would be more performant to get on of the column names from the set object and use that rather than *. Is that what you were referring to in your other thread?

    Allan

  • georgemcflygeorgemcfly Posts: 18Questions: 4Answers: 0

    yes and based on my experience, it usually is. It seems safer for scenarios where you have funky datatypes or computed columns. Thanks!

  • georgemcflygeorgemcfly Posts: 18Questions: 4Answers: 0

    Is this something that you will fix (it causes problems when editing records with unsupported datatypes in MS SQL) ? Its causing a problem in our application and I don't like modifying 3rd party libraries unless we have to.

    Thanks

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin
    Answer ✓

    Yes, I think picking a column name out of the set object would be the right thing to do here. Ideally the column would be indexed, but without making the function a lot more complicated, that wouldn't be easy to do.

    I've got this logged as something to be included in Editor 1.8.

    Allan

  • georgemcflygeorgemcfly Posts: 18Questions: 4Answers: 0

    Awesome, thank you.

This discussion has been closed.