How to download an image as base64 from db only, not from a folder, usind c#, .net

ASWS

-> .net mvc, within the controller action, I have the following question:

on server side I retrieve a blob "avatar_pic" from my db "table1" and I want to send it to the client... -> I store (avatar) images in a db table as blob, not as files on the server, so the other given examples here dont work for my setup.

As far as I understood, I should then declare something similar to this:
.Field(new Field("table1.avatar_pic")
in order to transfer my server side base64 string with ajax.
(something I converted beforehand from a blob to a base64 string in the controller action)

But in the end how does this "new field" have to be declared properly in the .net controller? Any explicit example would be really welcome, I did spend quite some time reading most likely all the wrong stuff, feeling quite stupid already...

So again for your understanding, I acces my e.g. "table1" and retrieve a blob directly from the db, during the controller action... I then do convert it to a base64 string, so far so good, but after this I'm stuck with something like this:

.Field(new Field("table1.avatar_pic"),

which doesnt seem to work work except for basic string transfer.

-> on the datatable client side I then want to render the retrieved base64 string like in this example:

{ data: "table1.avatar_pic", render: function (data, type, row) {
return '<img src="data:image/png;base64,'+data+'" width="50px" height="50px">'; }}

but as said above, I cant retrieve this data on client side.

ASWS

to sum it up, I would just need a short example of c# mvc controller syntax, in order to send a base64 string to the client side... (and in the opposite direction (upload) too actually) - many thanks in advance!!!

allan

Rather than using base64, personally I would use a URL such as:

' <img src="/dynamic-image?id='+data+'" width="50px" height="50px">';

Where data is the id of the image. Then have a script at the server-side that will create the image dynamically from the database blob. That will be better for bandwidth use, at the expense of multiple http requests.

How exactly you create an image, I don't know I'm afraid. In the PHP world you would use createimage. I presume that .NET has a similar method somewhere.

Allan

ASWS

Hi Allan, ok got it, that makes sense. I just thought that there might have been some editor ready method that I'm not aware of. But in this case its clear, I will do so, thank you for your fast reply.

Obviously my next question would be how to do it the other way round, upload to db... Your editor examples are all about saving files on the server, but I need to import into db. What do you advice? Again customize something, or anything available out of the box?

Cheers
Mark

allan

Hi Mark,

The Upload class does actually allow the file content to be written to the database through the Upload.DbType.Content option.

Allan

ASWS

here a quick and easy c# .net mvc solution for image field 'download' & display into datatables/editor, better ideas welcome...

mvc controller:

.Field(new Field("myTableWithBlob.table_id"))

in the mvc view, simply define a base64 array, (should be done in the mvc controller and passed as a viewbag e.g., but can also be done inside the view itself, maybe ugly but more readable in a complex application:

int i = 1;
Dictionary<int, string>
avatarArray = new Dictionary<int, string>();
foreach (var item in Model.Where(m => m.AvatarPic != null))
{
    avatarArray[i] = System.Convert.ToBase64String(item.AvatarPic);
    i++;
}

finally in js simply handover the mvc created array to a java array:

var jsAvatarArray = @Html.Raw(Json.Serialize(@avatarArray));

then in the datatable definition like:

$('#mytable').DataTable({
dom: "etc etc",
ajax: ".....
...
...
...
{ data: "myTableWithBlob.table_id", render: function (data, type, row) {
if (typeof jsAvatarArray[data] === "undefined") {
return 'n/a';
} else {
return '<img src="data:image/png;base64,'+jsAvatarArray[data]+'" width="50px" height="50px">';
}
}
},

===============================================================
works ok.
happy to contribute, happy to learn a better solution

ASWS

Allan,
any snippet at hand for "Upload.DbType.Content" ??
kind regards!
Mark

allan

Hi Mark,

Something like this should do it:

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},
                {"data", Upload.DbType.Content}
            })
    )
    .SetFormatter( Format.NullEmpty() );

Allan

ASWS

Hi Allan

ok, Im trying to make it run, not sure I understand all the example field namings.
Furthermore, as I just upload an img to db, I dont need any of these path etc variables...

so would it work like simply:

"new Upload()"

instead of

new Upload( request.PhysicalApplicationPath + @uploads__ID____EXTN__ )

as in my code (that doesn't work btw):

table name: "contact_info"
name of id field: "address_id"
name of blob field: "avatar_pic"

.Field(new Field("contact_info.address_id")
.SetFormatter(Format.IfEmpty(null))
.Upload(new Upload()
.Db("contact_info", "address_id", new Dictionary<string, object>
{
//{"filename", Upload.DbType.FileName},
//{"filesize", Upload.DbType.FileSize},
{"avatar_pic", Upload.DbType.ContentBinary},
})
.Validator(Validation.FileSize(500000, "Max file size is 500K."))
.Validator(Validation.FileExtensions(new[] { "jpg", "png", "gif" }, "Please upload an image."))
).SetFormatter(Format.NullEmpty()))

ASWS

actually in your example setup:

"image" is both a table and a field name
"id" is the name of a table key, and actually the same name ("id") for both tables, staff and image

so when as "editor newbe" I did read the examples fast trying to understand the syntax in a glimpse, it was a little confusing.

image_id
staff_id
image_nr (field)
image (table)

e.g. would be a lot easier wording throughout the examples...

ASWS

as upload error I get:

'File uploaded to a field that does not have upload options configured'

ASWS

to sum it up:
I still didnt figure out the server / controller syntax for any blob upload into db

.Field(new Field("contact_info.address_id")
.SetFormatter(Format.IfEmpty(null))
.Upload(new Upload()
.Db("contact_info", "address_id", new Dictionary<string, object>
{
//{"filename", Upload.DbType.FileName},
//{"filesize", Upload.DbType.FileSize},
{"avatar_pic", Upload.DbType.ContentBinary},
})
.Validator(Validation.FileSize(500000, "Max file size is 500K."))
.Validator(Validation.FileExtensions(new[] { "jpg", "png", "gif" }, "Please upload an image."))
).SetFormatter(Format.NullEmpty()))

?

ASWS

is it actually possible to directly import a blob with these editor methods?

means can I import as is or in addition do I need to mix editor with c# net syntax,
something like .OpenReadStream()) etc?

ASWS

I'm stuck pls help me out, someone...

allan

File uploaded to a field that does not have upload options configured

Suggests to me that the field contact_info.address_id is not configured as an upload field type on the client-side. Perhaps you can show me the Javascript for your Editor? It certainly sounds like an unusual field name for a file upload .

is it actually possible to directly import a blob with these editor methods?

Yes, the DbType.Content option should do it. There is also ContentBinary is you are writing into a bin field in the database. The relevant code in the library is here.

Allan

allan

Btw, generally I recommend against storing files in the database - it means you need to query the db to read the file back, rather than just a plain old http request for it. The only advantage it really has is if you have a backup policy for your database already in place.

Allan

ASWS

 var editorCreate = new $.fn.dataTable.Editor({
                ajax: "/api/AdminFileUploadEdit",
                table: "#file_upload_edit",
                 fields: [
                     { label: "Auswahl Benutzer / Firma:",
                        name: "radio",
                        type: "radio",
                        options: [
                            { label: "Benutzer", value: 1 },
                            { label: "Firma", value: 0 },
                        ],
                        def: 1
                        },
                        { label: "Benutzer:",
                        name: "address.user_id",
                        type: "datatable",
                        className: "text-info",
                        optionsPair: {
                            value: 'user_id',
                        },
                        config: {
                            pageLength: 4,
                            ordering: false,
                            //paging: false,
                            info: false,
                            columns: [
                                {
                                    title: 'Name',
                                    data: 'family_name',
                                    def: 'User'
                                },
                                {
                                    title: 'Vorname',
                                    data: 'given_name1',
                                    def: 'Default'
                                }
                            ]
                        },
                    },
                    { label: "Firma:",
                        name: "address.company_id",
                        type: "datatable",
                        className:"text-primary",
                        optionsPair: {
                            value: 'company_id',
                        },
                        config: {
                            pageLength: 4,
                            ordering: false,
                            //paging: false,
                            info: false,
                            columns: [
                                {
                                    title: 'Name',
                                    data: 'short_name',
                                    def: 'Default Company'
                                },
                            ]
                        },
                    },
                    { label: "Avatar:",
                        name: "contact_info.address_id",
                        type: "upload",
                        display: function ( data ) {
                        return '<img src="data:image/png;base64,'+jsAvatarArray[data]+'" width="50px" height="50px">';
                        },
                        clearText: "Löschen",
                        noImageText: 'Keine Bilddatei',
                    },
                 ],
                 i18n: {
                    create: {
                        title: "Avatar Upload - Eingabeform"
                    },
                    edit: {
                        title: "Avatar Upload - Eingabeform"
                    },
                    remove: {
                        title: "Löschen"
                    },
                    datetime: {
                        //previous: 'Précédent',
                        //next:     'Premier',
                        //months:   [ 'Janvier', 'Février', 'Mars', 'Avril', 'Mai', 'Juin', 'Juillet', 'Août', 'Septembre', 'Octobre', 'Novembre', 'Décembre' ],
                        //weekdays: ['Dim', 'Lun', 'Mar', 'Mer', 'Jeu', 'Ven', 'Sam']
                        previous: 'Zurück',
                        next: 'Vor',
                        months: ['Januar', 'Februar', 'März', 'April', 'Mai', 'Juni', 'Juli', 'August', 'September', 'Oktober', 'November', 'Dezember'],
                        weekdays: ['So', 'Mo', 'Di', 'Mi', 'Do', 'Fr', 'Sa']
                    }
                }
            }).on('initCreate', function (e, data, action) {
                editorCreate.field('address.company_id').hide();
            });

Edited by Allan - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

ASWS

.Field(new Field("contact_info.address_id")
.SetFormatter(Format.IfEmpty(null))
.Upload(new Upload()
.Db("contact_info", "address_id", new Dictionary<string, object>
{
{"avatar_pic", Upload.DbType.ContentBinary},
})
.Validator(Validation.FileSize(500000, "Max file size is 500K."))
.Validator(Validation.FileExtensions(new[] { "jpg", "png", "gif" }, "Please upload an image."))
).SetFormatter(Format.NullEmpty()))

?

Edited by Allan - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

ASWS

above js and controller server side

current error message on edit action is:
MySqlException: Field 'xyz_id' doesn't have a default value

on upload I need to provide a foreign key to the target table
seems like this key is missing
does the above ".Upload(new Upload()..." statement need its own joins included?

all relevant tables are already joined initially though, like
.LeftJoin("city", "city.city_id", "=", "address.city_id") etc...

ASWS

or does ".Upload(new Upload()..." need any foreign key explicitly set on each upload?

allan

Field 'xyz_id' doesn't have a default value

Is xyz_id a placeholder for address_id there, or is that the actual error message?

on upload I need to provide a foreign key to the target table

Ah - I might be starting to see the issue now. In Editor the file upload is async to the rest of the form. So you need to have a separate table for the files to be stored in (typically just called files but you might want to use avatars in this case or similar). The host table (contact_info) would then have a foreign key reference to that files table linking the two together. That way Editor can upload to the files table and write the reference key into the host table when the form is submitted.

Allan

ASWS

Hi Allan

many thanks again for your answers here... Indeed xyz_id is just a placeholder.
I guess from your answer I start to see the target structure. I will rethink it and give it a try.
In case it works I'll post the entire mvc code back. Otherwise I will be back with yet another question... )

Mark

ASWS

sorry I dont seem to get it right...

I have 2 tables:

table: adress:
address_id (key)
etc...

table: contact_info:
contact_info_id (key)
address_id (foreign.key)
avatar_pic (blob)
etc...

inserts into the "contact_info" table require no key field (contact_info_id) as this is auto-generated, updates can be on the foreign-key (unique) or table key

=> server / controller?:

.Field(new Field("contact_info.address_id")
.SetFormatter(Format.IfEmpty(null))
.Upload(new Upload()
.Db("contact_info", "address_id", new Dictionary<string, object>
{
{"contact_info.avatar_pic", Upload.DbType.ContentBinary}
})
.Validator(Validation.FileSize(500000, "Max file size is 500K."))
.Validator(Validation.FileExtensions(new[] { "jpg", "png", "gif" }, "Please upload an image."))
).SetFormatter(Format.NullEmpty()))

as I understood this snippet is independent from the rest of the js & server / controller code,
joins should be attributed exclusively to this statement above, how?

seems so easy, but won't work anyway, I tried a lot of diff. combinations by guesswork, but without succes....

ASWS

Not to forget, I only want to insert or update the selected image directly into the "contact_info.avatar_pic" (blob) field, nothing else, no file on server, no path reference, nothing. just put this thing into this damn blob field...

ASWS

but also provide the foreign key in each transaction...
=> so: address.address_id & avatar_pic (blob) are the main elements.

ASWS

_I have 2 tables:_

**table: adress:**
address_id (key)
etc...

**table: contact_info:**
contact_info_id (key)
address_id (foreign.key)
avatar_pic (blob)
etc...

inserts into the "contact_info" table require no key field (contact_info_id) as this is auto-generated, updates can be on the foreign-key (unique) or table key

=> server / controller?:

.Field(new Field("contact_info.address_id")
.SetFormatter(Format.IfEmpty(null))
.Upload(new Upload()
.Db("contact_info", "address_id", new Dictionary<string, object>
{
{"contact_info.avatar_pic", Upload.DbType.ContentBinary}
})
.Validator(Validation.FileSize(500000, "Max file size is 500K."))
.Validator(Validation.FileExtensions(new[] { "jpg", "png", "gif" }, "Please upload an image."))
).SetFormatter(Format.NullEmpty()))

ASWS

Hi Allan,
could you pls help me out here? How to do this with editor syntax...

ASWS

hm
maybe the problem is that the target table "contact_info" has 2 key columns? And the upload db() method delivers only 2 values: the table key itself and the Upload.DbType values, but nothing else, so that in fact the 2nd mandatory key column never gets a value, therefore the error message:
'Field 'address_id' doesn't have a default value
?

ASWS

.Field(new Field("address.address_id")
     .Upload(new Upload()
     .Db("contact_info", "contact_info_id", new Dictionary<string, object>
     {
     {"contact_info.address_id", <== HERE PROVIDE A FOREIGN KEY, HOW??? },
     {"contact_info.avatar_pic", Upload.DbType.ContentBinary}
     })
    .Validator(Validation.FileSize(500000, "Max file size is 500K."))
    .Validator(Validation.FileExtensions(new[] { "jpg", "png", "gif" }, "Please upload an image."))
            ).SetFormatter(Format.NullEmpty()))

allan

Hi - sorry for not being able to get back to you about this yesterday. contact_info should contain information about the file only (or create a new table called images or avatar or something like that which can contain information about the file only, which might make things easier here!)

You need to have a table which is specifically for the file only. No other information (e.g. the address_id wouldn't work).

Let's assume you create a avatar table which has: id and data (blob). Then your contact_info table would have a field called avatar_id which is a foreign key reference to the avatar table.

Does that start to make a little more sense now?

Allan

ASWS

Hi Allan

thank you so much for your input. As you suggested I did add the additional images table.
The image-blob insert works now, but the image table "id" key-field remains a problem to me.

Let's say according to your example the image table has 2 fields, id and image.

.Db("image", "id", new Dictionary<string, object>

Each time the upload db method inserts a row, or the id field is just left empty by your method, or in case the table has autoincrement enabled for the "id" key-field, then obviously it contains the auto-incremented id.

Is there a way to set the "id"-key for each new row inserted to a custom pre-defined value other then "nothing" or "auto-increment"? If I could assign a specific value, that would be terrific to integrate.