Database Push Method (Editing record MS SQL)

georgemcfly

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.

allan

Hi,

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

Allan

georgemcfly

Editor's .NET Libraries

allan

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

georgemcfly

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!

allan

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

georgemcfly

Thanks Allan

georgemcfly

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.

georgemcfly

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.

allan

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

georgemcfly

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

georgemcfly

Just curious to hear the final word on this?

allan

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

georgemcfly

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

georgemcfly

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

allan

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

georgemcfly

Awesome, thank you.