How to Use Multiple Databases in Editor

JohnRibar

I am trying to use an Editor, but my data is in 2 databases. In the meantime, I am using a View that connected the databases, but this does not allow Editor to do either the Edit or Delete. Does anyone know how to setup the Editor to allow linking tables from two databases?
Yes, I am only trying to edit items in the local database, and same for the delete (i.e., the table in the definition, ChemRound in this example:

                var response = new Editor(db, "ChemRound", "Id") ...

I am using C#/MVC if it matters.
Thanks for your assistance!
LJ

allan

When you say different databases, does your db connection have access to both of them? If so, you should be able to do db1.ChemRound, and db2.tableName, etc to quality each field / table.

Allan

JohnRibar

Thanks, Allan. I didn't realize you could put multiple catalogs in the connection string. So, how do I access the fields then? Is it 'database1.table.field' format? And is 'database1' the name of the catalog in this case? And what do you need to name things in the EditorModel class so that the records come from the individual databases?

Here is my class:

        public class ChemRound_Join : EditorModel
        {
            public partial class ChemRound : EditorModel { }
            public partial class RoundName : EditorModel { }
            public partial class FieldPivot : EditorModel { }
        }

FieldPivot comes from the second catalog (FarmDB), and the other two come from the first (CoreDB).

In my current code, I use things like this:

                    .Field(new DataTables.Field("RoundName.ChemProductName"))
                    .Field(new DataTables.Field("FieldPivot.Acres"))
                    .Field(new DataTables.Field("ChemRound.Rate"))
                    .LeftJoin("FieldPivot", "FieldPivot.Id", "=", "ChemRound.PivotId")

So I can see using the catalog name in the items that are in quotes:

                    .Field(new DataTables.Field("FarmDB.FieldPivot.Acres"))
                    .Field(new DataTables.Field("CoreDB.ChemRound.Rate"))

but how does that work with the class? Do I need to separate it out for each of the catalogs?

Thanks so much for your help!

allan

Hi,

I'd probably skip using the modal class at all here. Just define them using the new DataTables.Field() parameter. It might make the code a little more dense, but it doesn't effect performance or operation in any way.

I should not that this isn't an area I have extensively tested in the .NET libraries! It is possible you might come across a bug or two. If you do, let me know. What database are you using?

Allan

JohnRibar

We are using SQLServer. I'll share whatever I find with you. Thanks!