How to convert image to base64, insert into XML file

Kevin Fralick

Is there an easy way to query an image from sql database and place it into xml document in base64binary format?

I have the following code but I simply need to add an image to it (inmpicture) and convert it to that format.

SqlConnection

con = new SqlConnection("server=###;database=###;Initial Catalog=###;uid=###;pwd=###");

query.Connection = con;

SqlCommand query = new SqlCommand("SELECT inmpicture as Portrait, ltrim(rtrim(a.bookno)) as BookingRecordIdentifier,ltrim(rtrim(a.ecsoid)) as PersonIdentifier, ltrim(rtrim(a.dob)) as Dob FROM jail.dbo.jailbook a where a.lname is not null");

mysqldataadapter.SelectCommand = query;

con.Open();
mysqldataadapter.Fill(mydataset, "AddBookingRecord");

mydataset.WriteXml("c:\\Mugshot.xml");
SqlDataAdapter mysqldataadapter = new SqlDataAdapter();DataSet mydataset = new DataSet();"AddBookingRecord");DataTable mydataTable = mydataset.Tables["AddBookingRecord"];"c:\\Mugshot.xml");

con.Close();

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vulpes15y ago
The .NET version of Base64 uses the character set A-Z, a-z, 0-9, +, / as well as = for padding.

So, ignoring the dots at the end, the rest of it does look like a Base64 string.
Sam Hobbs15y ago
Kevin, if you simply look at the pages from the results of the search I posted, there is plenty of sample code. I am sorry that there is so much sample code that it is overwhelming, but there is ample sample code.

Base 64 encoding converts 8-bit values, which have a range of 0-255, to characters that consist of any of 64 characters. So the data will include delimiters. Something that I might do is to write a small sample console program that converts test data to base 64 then back to its original value. I do that kind of thing very often. You can try out the functions that way, then when you are familiar with the way it works, you can put the code in your main program.
Kevin Fralick15y ago
Thanks Vulpes. I thought that was the solution. I looked at the output data and it was appearing differently as it does within the database.

The output looks like this:

"/9j/4AAQSkZJRgABAAEAGgAUAAD//gAfTEVBRCBUZWNobm9sb2dpZXMgSW5jLiBWMS4wMQD..."

Is that what base64binary looks like?

But then I noticed that even if I don't include your code, the output looks the same.
Vulpes15y ago
Assuming it will be the first column in the DataTable that you want, you should be able to get the image in the first row of the table in byte array form as follows:

byte[] bytes = (byte[])mydataTable.Rows[0][0];
string base64 = Convert.ToBase64String(bytes);
Kevin Fralick15y ago
That helps somewhat Sam. Thanks.

I think the following command may be what I need.

System.Convert.ToBase64String(field)

Where field would be 'inmpicture' in my case. Unfortunately, I do not know how to make reference to this field since I'm pulling it into a dataset.

If you know of a way I could reference the field within my dataset so that I could run this command against it, I would greatly appreciate it.
Sam Hobbs15y ago
Look here; does that help?