locked
How to retrieve MySql 'blob' image field data into base64 encoded string format in a datatable RRS feed

  • Question

  • Hello Everyone, 

    I have image data stored in MySQL db 'blob' field like below. I want to retrieve that data as a base64 encoded string format in a data table. 

    please help me to retrieve this data into a data table with base64 encoded string format. 

    Thanks

    Saturday, July 11, 2020 10:34 AM

Answers

  • Hi Learning Rocks,

    Thank you for posting here.

    The Convert class is provided in C# to convert a byte array to a Base64 string.

    Convert.ToBase64String Method

    It works well for me.

            private void button2_Click(object sender, EventArgs e)
            {
                using (MySqlConnection connection = new MySqlConnection("Server=localhost; database=test; UID=root; password=123456"))
                {
                    connection.Open();
                    string CmdString = "select picture from mytable2";
                    using (MySqlCommand command = new MySqlCommand(CmdString, connection))
                    {
                        DataTable dataTable = new DataTable();
                        dataTable.Load(command.ExecuteReader());
                        string re = Convert.ToBase64String((byte[])dataTable.Rows[0]["picture"]);
                     
                    }
                }
            }

    Hope this could be helpful.

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, July 13, 2020 7:33 AM

All replies

  • It's unclear about the base64, I see pictureid which seems like it should be a numeric auto incrementing primary key as the image which you indicate is a blob. When reading data seems that the MySql data provider would return picturedata as a byte array. So where is base64 encoded string come from and if it's the blob why would you want to convert a byte array to a base64 ?

    https://dev.mysql.com/doc/connector-net/en/connector-net-programming-blob-reading.html


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange


    Saturday, July 11, 2020 10:53 AM
  • Because, i need to show this image in android mobile which accepts only base64 encoded format. 
    Saturday, July 11, 2020 12:33 PM
  • Hi Learning Rocks,

    Thank you for posting here.

    The Convert class is provided in C# to convert a byte array to a Base64 string.

    Convert.ToBase64String Method

    It works well for me.

            private void button2_Click(object sender, EventArgs e)
            {
                using (MySqlConnection connection = new MySqlConnection("Server=localhost; database=test; UID=root; password=123456"))
                {
                    connection.Open();
                    string CmdString = "select picture from mytable2";
                    using (MySqlCommand command = new MySqlCommand(CmdString, connection))
                    {
                        DataTable dataTable = new DataTable();
                        dataTable.Load(command.ExecuteReader());
                        string re = Convert.ToBase64String((byte[])dataTable.Rows[0]["picture"]);
                     
                    }
                }
            }

    Hope this could be helpful.

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, July 13, 2020 7:33 AM