Editor - Upload

dpanscik

I'm back having fun working with Editor.

I'm getting my head wrapped around what upload requires to function. Have two initial questions. Referencing the example found here.

https://editor.datatables.net/examples/advanced/upload-many.html

Does the parent database table require a field named "users.site"?
What is stored here? An string array of table ID numbers of the "files" table?

Thanks as always for your help.

David

allan

Hi David,

If you click the "Server script" tab just below the table, it will show the controller code used, which will hopefully explain a few things.

It uses an Mjoin to create the one-to-many relationship. Each file that is assigned to a user is assigned as a record in the users_files table, which contains a reference to a user row and also to a file, providing the link between them.

It doesn't use arrays in the database, although that is how the JSON data that is loaded on the client-side represents the data.

Allan

dpanscik

Thanks Allan. I see the strategy. users_files reference the parent table. I was thinking the parent table referenced the users_files table. Makes sense.

Next question. referring to the example found here;
https://editor.datatables.net/manual/net/upload

How does this code;

public IHttpActionResult Staff()
{
    var request = HttpContext.Current.Request;
    var settings = Properties.Settings.Default;
 
    using (var db = new Database(settings.DbType, settings.DbConnection))
    {
        var response = new Editor(db, "staff")
            .Model<StaffModel>()
            .Process(request)
            .Data();
 
        return Json(response);
    }
}

combine with this code?

new Field( "image" )
    .Upload(
        new Upload( request.PhysicalApplicationPath + @"uploads\__ID____EXTN__" )
            .Db("image", "id", new Dictionary<string, object>
            {
                {"fileName", Upload.DbType.FileName},
                {"fileSize", Upload.DbType.FileSize}
            })
    )
    .SetFormatter( Format.NullEmpty() );

seems like there is some "glue" missing here. But perhaps not...

allan

Ah - you are using C#. Sorry I didn't realise that. This is the C# equivalent of the PHP controller from the link I gave above:

    public class UploadManyController : Controller
    {
        [HttpGet, HttpPost, Route("api/upload-many")]
        public ActionResult Staff()
        {
            var dbType = Environment.GetEnvironmentVariable("DBTYPE");
            var dbConnection = Environment.GetEnvironmentVariable("DBCONNECTION");

            using (var db = new Database(dbType, dbConnection))
            {
                var response = new Editor(db, "users")
                    .Model<UploadManyModel>()
                    .Field(new Field("users.site")
                        .Options(new Options()
                            .Table("sites")
                            .Value("id")
                            .Label("name")
                        )
                    )
                    .LeftJoin("sites", "sites.id", "=", "users.site")
                    .MJoin(new MJoin("files")
                        .Link("users.id", "users_files.user_id")
                        .Link("files.id", "users_files.file_id")
                        .Field(
                            new Field("id")
                                .Upload(new Upload(Path.Combine(Directory.GetCurrentDirectory(), "wwwroot", "uploads", "__ID____EXTN__"))
                                    .Db("files", "id", new Dictionary<string, object>
                                    {
                                        {"web_path", Path.DirectorySeparatorChar+Path.Combine("uploads", "__ID____EXTN__")},
                                        {"system_path", Upload.DbType.SystemPath},
                                        {"filename", Upload.DbType.FileName},
                                        {"filesize", Upload.DbType.FileSize}
                                    })
                                    .Validator(Validation.FileSize(500000, "Max file size is 500K."))
                                    .Validator(Validation.FileExtensions(new[] { "jpg", "png", "gif" }, "Please upload an image."))
                                )
                        )
                    )
                    .Process(Request)
                    .Data();

                return Json(response);
            }
        }
    }

The upload information needs to be in the Mjoin.

This is the documentation for Mjoin in .NET.

Allan

dpanscik

perfect. thank you Allan. I'm going to dive in and ramble around and see what happens.

dpanscik

I have a 4 more questions to help wrap my head around how this is supposed to work.

(1) Does this code make a (for lack of a better term) virtual table of "users" called "sites" ?

                .Field(new Field("users.site")
                    .Options(new Options()
                        .Table("sites")
                        .Value("id")
                        .Label("name")

(2) what database tables do I need to run this example?

Table ("users")
id
site

Table ("files")
id
web_path
system_path
filename
filesize

(3) what is this linking?

 .Link("users.id", "users_files.user_id")

I understand what users.id is but I don't understand what users_files.user_id is for, its the first time its referenced in this example. Not sure what this is supposed to be...

(4) same question for this one. what is this linking?

.Link("files.id", "users_files.file_id")

I understand what files.id is but I don't understand the purpose of users_files.user_id

allan

Does this code make a (for lack of a better term) virtual table of "users" called "sites" ?

No, it makes use of an actual take called sites. That isn't actually part of the file upload though. It is a left joined field with a list of options for the user to pick from. See the let him documentation for more information about that.

what database tables do I need to run this example?

That code would use user's, sites, files and users_files.

The SQL for our demos is available in the manual here.

what is this linking?

It uses a link / junction table as described in the Mjoin documentation. If you consider the image in the docs there, users_files is the link table (also known as a junction table), and the two link statements define how it connects to the two host tables.

Allan

dpanscik

Seeing the table creation here is helping to fill out some of the questions in my mind in figuring out how upload works. multiple questions are answered by having a look at this;
https://editor.datatables.net/examples/sql/sqlserver.sql

so by my reckoning sites seems to be related to a data function that can be eliminated for purpose of making a simpler multi upload test. One less table to worry about getting correct.

What do you think about this controller?

public class UploadManyController : Controller
{
    [HttpGet, HttpPost, Route("api/upload-many")]
    public ActionResult Staff()
    {
        var dbType = Environment.GetEnvironmentVariable("DBTYPE");
        var dbConnection = Environment.GetEnvironmentVariable("DBCONNECTION");
 
        using (var db = new Database(dbType, dbConnection))
        {
            var response = new Editor(db, "users")
                .Model<UploadManyModel>()
                .MJoin(new MJoin("files")
                    .Link("users.id", "users_files.user_id")
                    .Link("files.id", "users_files.file_id")
                    .Field(
                        new Field("id")
                            .Upload(new Upload(Path.Combine(Directory.GetCurrentDirectory(), "wwwroot", "uploads", "__ID____EXTN__"))
                                .Db("files", "id", new Dictionary<string, object>
                                {
                                    {"web_path", Path.DirectorySeparatorChar+Path.Combine("uploads", "__ID____EXTN__")},
                                    {"system_path", Upload.DbType.SystemPath},
                                    {"filename", Upload.DbType.FileName},
                                    {"filesize", Upload.DbType.FileSize}
                                })
                                .Validator(Validation.FileSize(500000, "Max file size is 500K."))
                                .Validator(Validation.FileExtensions(new[] { "jpg", "png", "gif" }, "Please upload an image."))
                            )
                    )
                )
                .Process(Request)
                .Data();
 
            return Json(response);
        }
    }
}

allan

Looks good on a first pass scan. Have you tried running it? What happens?

Allan

dpanscik

my first attempt at adding upload to my existing code was unsuccessful. On drawing the table i get an error in visual studio "{An item with the same key has already been added.}"

Sooo... for my learning purposes i need to back this up a step and build off a known working example.

I started with a stock controller from editor generator.

                public class Flex_OnCall_3Controller : ApiController
                {
                    [Route("api/Flex_OnCall_3")]
                    [HttpGet]
                    [HttpPost]
                    public IHttpActionResult Flex_OnCall_3()
                    {
                        var request = HttpContext.Current.Request;
                        var settings = Properties.Settings.Default;
            
                        using (var db = new Database(settings.DbType, settings.DbConnection))
                        {
                            // The following statement can be removed after the first run
                            // (i.e. the database table has been created). It is a good idea
                            // to do this to help improve performance.
                            db.Sql( @"IF object_id('Flex_OnCall_3', 'U') is null
                                CREATE TABLE Flex_OnCall_3 (
                                [TableID] int not null identity,
                                [sfd] nvarchar(255),
                                [today] date,
                                PRIMARY KEY( [TableID] )
                            );" );

                            var response = new Editor(db, "Flex_OnCall_3", "TableID")
                                .Model<Flex_OnCall_3Model>()
                                .Field(new Field("today")
                                        .Validator(Validation.DateFormat(Format.DATE_ISO_1036))
                                        .GetFormatter(Format.DateSqlToFormat(Format.DATE_ISO_1036))
                                        .SetFormatter(Format.DateFormatToSql(Format.DATE_ISO_1036))
                                )
                                .Process(request)
                                .Data();

                            return Json(response);
                        }
                    }
                }
            }

and turned it into this

            namespace EditorGenerator.Controllers
            {
                public class Flex_OnCall_3Controller : ApiController
                {
                    [Route("api/Flex_OnCall_3")]
                    [HttpGet]
                    [HttpPost]
                    public IHttpActionResult Flex_OnCall_3()
                    {
                        var request = HttpContext.Current.Request;
                        var settings = Properties.Settings.Default;
            
                        using (var db = new Database(settings.DbType, settings.DbConnection))
                        {
                            // The following statement can be removed after the first run
                            // (i.e. the database table has been created). It is a good idea
                            // to do this to help improve performance.
                            db.Sql(@"IF object_id('files', 'U') is null
                                        CREATE TABLE files (
                                            id int not null identity, 
                                            filename nvarchar(250) default NULL,
                                            filesize int default 0,
                                            web_path nvarchar(250) default NULL,
                                            system_path nvarchar(250) default NULL,
                                            PRIMARY KEY (id)
                                        );
                                ");

                            db.Sql(@"IF object_id('users_files', 'U') is null
                                        CREATE TABLE users_files (
                                            user_id int NOT NULL,
                                            file_id int NOT NULL,
                                            FOREIGN KEY (user_id) REFERENCES Flex_OnCall_3(TableID) ON DELETE CASCADE,
                                            FOREIGN KEY (file_id) REFERENCES files(id) ON DELETE CASCADE
                                        );
                                            ");

                            db.Sql( @"IF object_id('Flex_OnCall_3', 'U') is null
                                        CREATE TABLE Flex_OnCall_3 (
                                            [TableID] int not null identity,
                                            [sfd] nvarchar(255),
                                            [today] date,
                                            PRIMARY KEY( [TableID] )
                                        );" );

                            var response = new Editor(db, "Flex_OnCall_3", "TableID")
                            .Model<Flex_OnCall_3Model>()
                            .Field(new Field("today")
                                    .Validator(Validation.DateFormat(Format.DATE_ISO_1036))
                                    .GetFormatter(Format.DateSqlToFormat(Format.DATE_ISO_1036))
                                    .SetFormatter(Format.DateFormatToSql(Format.DATE_ISO_1036)))
                            .Model<UploadManyModel>()
                                .MJoin(new MJoin("files")
                                    .Link("Flex_AP_MasterView.TableID", "users_files.user_id")
                                    .Link("files.id", "users_files.file_id")
                                    .Field(
                                        new Field("id")
                                            .Upload(new Upload(System.IO.Path.Combine(System.IO.Directory.GetCurrentDirectory(), "wwwroot", "uploads", "__ID____EXTN__"))
                                                .Db("files", "id", new Dictionary<string, object>
                                                {
                                                    {"web_path", System.IO.Path.DirectorySeparatorChar+System.IO.Path.Combine("uploads", "__ID____EXTN__")},
                                                    {"system_path", Upload.DbType.SystemPath},
                                                    {"filename", Upload.DbType.FileName},
                                                    {"filesize", Upload.DbType.FileSize}
                                                })
                                                .Validator(Validation.FileSize(500000, "Max file size is 500K."))
                                                .Validator(Validation.FileExtensions(new[] { "jpg", "png", "gif" }, "Please upload an image."))
                                            )
                                    )
                                )
                            .Process(request)
                            .Data();

                            return Json(response);
                        }
                    }
                }
            }

dpanscik

Well... Im stil having troubles. Im stuck and dont know what to troubleshoot as I am still not 100% certain what upload needs to function.

I started with a known working example

                var response = new Editor(db, "Flex_OnCall_3Model", "TableID")
                    .Model<Flex_OnCall_3Model>()
                    .Where("accountIdentifier", accountIdentifier, "=")
                    .Where("dayOC", DateTime.Now.AddDays(-1.42), ">")
                    .Field(new Field("dayOC")
                    .Validator(Validation.DateFormat(Format.DATE_ISO_2822))
                    .GetFormatter(Format.DateSqlToFormat(Format.DATE_ISO_2822))
                    .SetFormatter(Format.DateFormatToSql(Format.DATE_ISO_2822)))
                    .Field(new Field("modifiedDate")
                    .GetFormatter(Format.DateSqlToFormat(Format.DATE_ISO_2822))
                    .SetFormatter(Format.DateFormatToSql(Format.DATE_ISO_2822)))
                    .Debug(true)
                    .Process(request)
                    .Data();

then I add the upload items

                        response = new Editor(db, "Flex_OnCall_3Model", "TableID")
                            .Model<Flex_OnCall_3Model>()
                            .Where("accountIdentifier", accountIdentifier, "=")
                            .Where("dayOC", DateTime.Now.AddDays(-1.42), ">")
                            .Field(new Field("dayOC")
                            .Validator(Validation.DateFormat(Format.DATE_ISO_2822))
                            .GetFormatter(Format.DateSqlToFormat(Format.DATE_ISO_2822))
                            .SetFormatter(Format.DateFormatToSql(Format.DATE_ISO_2822)))
                            .Field(new Field("modifiedDate")
                            .GetFormatter(Format.DateSqlToFormat(Format.DATE_ISO_2822))
                            .SetFormatter(Format.DateFormatToSql(Format.DATE_ISO_2822)))
                                .MJoin(new MJoin("files")
                                    .Link("Flex_OnCall_3Model.TableID", "users_files.user_id")
                                    .Link("files.id", "users_files.file_id")
                                    .Field(
                                        new Field("id")
                                            .Upload(new Upload(System.IO.Path.Combine(System.IO.Directory.GetCurrentDirectory(), "wwwroot", "uploads", "__ID____EXTN__"))
                                                .Db("files", "id", new Dictionary<string, object>
                                                {
                                                    {"web_path", System.IO.Path.DirectorySeparatorChar+System.IO.Path.Combine("uploads", "__ID____EXTN__")},
                                                    {"system_path", Upload.DbType.SystemPath},
                                                    {"filename", Upload.DbType.FileName},
                                                    {"filesize", Upload.DbType.FileSize}
                                                })
                                                .Validator(Validation.FileSize(500000, "Max file size is 500K."))
                                                .Validator(Validation.FileExtensions(new[] { "jpg", "png", "gif" }, "Please upload an image."))
                                            )
                                    )
                                )
                            .Debug(true)
                            .Process(request)
                            .Data();
                }

and I get this when the table attempts to draw

I don't understand what I need to do next...

allan

Hi,

The error message indicates what the issue is (I had to keep the error message succinct so maybe it doesn't explain the issue fully). It points to this line as being the issue, since it is where Flex_OnCall_3Model.TableID is referenced:

 .Link("Flex_OnCall_3Model.TableID", "users_files.user_id")

Try adding:

.Field(
  new Field("Flex_OnCall_3Model.TableID")
    .Set(false)
)

to the Editor initialisation (i.e. between lines 2 and 3 in the code above).

That said, it should see that TableID is the primary key, so it looks like there is something wrong there. Apologies. Hopefully this will work around the issue for the moment for you.

Allan

dpanscik

Perfect! I now have a working example drawing the table. Now I can use that as as a basis for integrating upload into existing code.

Thank you Allan!

dpanscik

I am testing upload and when I attempt to upload a file I get this error message. I'm not certain what this message is trying to tell me.

File upload requires that 'Process' be called with an HttpRequest or UnvalidatedRequestValues object

allan

.Process(request)

Could you try:

.Process(Request)

please? I think that should do it.

Allan

dpanscik

I think it needs to be .Process(request)

here is what happens when I change it to .Process(Request)

here is my original ` var request = '

allan

I think I've spotted the issue, and it is due to the way Generator is creating the code. It doesn't have a file upload ability, and I've never tried to add upload to a Generator package before myself, so i didn't catch this - apologies.

The issue is:

public class Flex_OnCall_3Controller : ApiController

ApiController extends from HttpRequestMessage which does not have file information. What to do is change that line to be:

public class Flex_OnCall_3Controller : Controller

And use the .Process(Request) that I suggested.

That should do the trick (apologies - don't have a Windows computed here with me to check at the moment). Assuming that does the business, I'll get Generator updated for that.

Allan

dpanscik

Getting closer to this working...

Now I get this error message.

I think my path to the controller is wrong.

            "ajax": {
                "url": "/APData",
                "type": "POST",
                "datatype": "json",
                "data": function (d) {
                    return $.extend({}, d, {
                        "form_id": "APgrid",
                        "displaypaid": $('#displayPaid:checked').val(),
                        "displaynotpaid": $('#displayNotPaid:checked').val(),
                    });
                }

    public class APDataController : Controller
    {

        [System.Web.Http.Route("/APData")]
        [System.Web.Http.HttpGet]
        [System.Web.Http.HttpPost]

The paths i tried is;
APData
and
/APData

kthorngren

Use the troubleshooting steps found at the link in the error:
https://datatables.net/manual/tech-notes/7

You might need to look at your webserver's log for details. If its a path issue you will need to look at your webserver's config to find the correct path.

Let us know what you find.

Kevin

allan

/APData should be right based on that.

Allan

dpanscik

I now have the form ajax properly hitting the controller.

Now onto the next issue.

I am getting this issue on the JSON return.

I could be mistaken but I believe this issue popped up when I switched the controller from:

    public class APDataController : ApiController

to

    public class APDataController : Controller

Severity    Code    Description Project File    Line    Suppression State
Error   CS0266  Cannot implicitly convert type 'System.Web.Mvc.JsonResult' to 'System.Web.Http.IHttpActionResult'. An explicit conversion exists (are you missing a cast?)  

dpanscik

and... Im back to my old tricks of answering my own question.

The solution was to changeIHttpActionResult to ActionResult

    public class APDataController : Controller
    {

        [System.Web.Http.Route("/APData/DataTransport")]
        [System.Web.Http.HttpGet]
        [System.Web.Http.HttpPost]
        public IHttpActionResult DataTransport()
        {

    public class APDataController : Controller
    {

        [System.Web.Http.Route("/APData/DataTransport")]
        [System.Web.Http.HttpGet]
        [System.Web.Http.HttpPost]
        public ActionResult DataTransport()
        {

dpanscik

We have re-arrived at my May 12th issue.

When I upload an image, I get this error.

File upload requires that 'Process' be called with an HttpRequest or UnvalidatedRequestValues object

Don't know what to try next...

allan

Can you show me the latest full controller code please? The Request object inside a Controller method is a HttpRequest.

Thanks,
Allan

dpanscik

Would it be ok if I send you the controller in a PM?

allan

Absolutely! Click my user name and then the "Send message" button.

Allan

dpanscik

My system path for localhost testing is very different than the system path on the production server. (although the web path is identical for both). Is there an easy way to code in two different paths that will switch depending on testing vs production?

dpanscik

additional info on above post.

For example on localhost;
{"system_path", Upload.DbType.SystemPath}, thinks the local system path is
C:\Program Files (x86)\IIS Express\wwwroot\uploads\
when the actually the localhost, local system path is...
D:\Users\dp\Dropbox\Code\AppSheet\AppSheetData\uploads

allan

You could use an environment variable, which is what I do. That's outside the scope of what Editor provides though, and is a general concept in .NET.

Allan

dpanscik

10-4 ill do the localhost trickery upstream of upload.

dpanscik

And... I'm up and running on localhost now for a line of testing...

Thank you Allan for all of your assistance.