Tuesday, July 31, 2018

[Complete] How to save/load RichTextbox content (with images) to/from database


[Complete] How to save/load RichTextbox content (with images) to/from database

[Complete] How to save/load RichTextbox content (with images) to/from database

Hi, This blog will show you about “How to save/load RichTextbox content (with format) to/from database. With this example working on VB.Net and MySQL database.

First, Take a look RichTextbox. Simple use rtf property.

VB Code
Public Class FormTest01
    Private Sub FormTest01_Load(sender As Object, e As EventArgs) Handles Me.Load
        Try

        Catch ex As Exception

        End Try
    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Try
                                       Dim MRTF As String
            MRTF = RichTextBox1.Rtf

            RichTextBox2.Rtf = MRTF
        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString)
        End Try
    End Sub
End Class

Result


We can send content with easy code.




Solution 2 : Working with “SaveFile” and “LoadFile”


VB Code
Public Class FormTest01
    Private Sub FormTest01_Load(sender As Object, e As EventArgs) Handles Me.Load
        Try

        Catch ex As Exception

        End Try
    End Sub
Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        Try
            'SaveFile From RichTextbox1
            RichTextBox1.SaveFile(Application.StartupPath & "\tmpRTF\rtfRichTextbox1.txt", RichTextBoxStreamType.RichText)


            'LoadFile to RichTextbox2
            RichTextBox2.LoadFile(Application.StartupPath & "\tmpRTF\rtfRichTextbox1.txt", RichTextBoxStreamType.RichText)

        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString)
        End Try
    End Sub
End Class

Result





Solution 3 : working with database
Prepare database table


Table name : myblobtable
Columns :     mbt_id (Integer),  mbt_richtextbox  (MediumBlob)
Why it’s Mediumblob
         BLOB can be 65535 bytes (64 KB) maximum.  *64KB is too small to save file.
         MEDIUMBLOB for 16777215 bytes (16 MB)
 LONGBLOB for 4294967295 bytes (4 GB).

Save form

Input content and click Button1


VB Code (Concept is just save rtf to blob column)
Imports System.IO
Imports MySql.Data.MySqlClient

Public Class FormTest02

    Dim objConn As MySqlConnection
    Dim objCmd As New MySqlCommand
    Dim dtAdapter As New MySqlDataAdapter
    Dim Trans As MySqlTransaction

    Dim StrSql As String
    Dim RowExc As Integer

    Private Sub FormTest02_Load(sender As Object, e As EventArgs) Handles Me.Load
        Try
            ConDB()

        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString)
        End Try
    End Sub

    Public Sub ConDB()
        Try
            Dim StrWer As StreamReader
            StrWer = File.OpenText(Application.StartupPath & "\AppConn.ini")

            Dim strConnString As String
            strConnString = StrWer.ReadLine()
            objConn = New MySqlConnection(strConnString)
            objConn.Open()
        Catch ex As Exception
            Throw ex
        End Try
    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Try
            'Save
            If objConn.State = ConnectionState.Closed Then
                ConDB()
            Else
            End If

            Dim blob_bytes_RichTextboxData As Byte() = System.Text.Encoding.Default.GetBytes(RichTextBox1.Rtf)

            Trans = objConn.BeginTransaction(IsolationLevel.ReadCommitted)
            StrSql = "INSERT INTO myblobtable (mbt_richtextbox) VALUES (?BlobRTBData);"
                                        With objCmd
                                       .CommandType = CommandType.Text
                                       .CommandText = StrSql
                                       .Parameters.AddWithValue("?BlobRTBData", blob_bytes_RichTextboxData)
                                        .Connection = objConn
                                       .Transaction = Trans
            End With
            RowExc = objCmd.ExecuteNonQuery
            If RowExc > 0 Then
                Trans.Commit()
           objCmd.Parameters.RemoveAt("?BlobRTBData")
           objCmd.Dispose()
                                    MessageBox.Show("Save success.")
            Else
           Trans.Rollback()
                                    MessageBox.Show("Save fail.")
            End If
        Catch ex As Exception
                                  MessageBox.Show(ex.Message.ToString)
        End Try
    End Sub
End Class



Data in database

Table : myblobtable





Load form

VB Code  Exp1 : Read string from blob and set to RichTextBox1.Rtf


Imports System.IO
Imports MySql.Data.MySqlClient

Public Class FormTest03

    Dim objConn As MySqlConnection
    Dim objCmd As New MySqlCommand
    Dim dtAdapter As New MySqlDataAdapter

    Dim StrSql As String

    Dim dtRTB As New DataTable

    Private Sub FormTest03_Load(sender As Object, e As EventArgs) Handles Me.Load
        Try
            ConDB()

            LoadRichTextbox()

        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString)
        End Try
    End Sub

    Public Sub ConDB()
        Try
            Dim StrWer As StreamReader
            StrWer = File.OpenText(Application.StartupPath & "\AppConn.ini")

            Dim strConnString As String
            strConnString = StrWer.ReadLine()
            objConn = New MySqlConnection(strConnString)
            objConn.Open()
        Catch ex As Exception
            Throw ex
        End Try
    End Sub

    Private Sub LoadRichTextbox()
        Try
            If objConn.State = ConnectionState.Closed Then
                ConDB()
            Else
            End If

            dtRTB.Clear()
            StrSql = "SELECT * FROM myblobtable WHERE mbt_id=1;"

            With objCmd
                .Connection = objConn
                .CommandText = StrSql
                .CommandType = CommandType.Text
            End With
            Dim myData As MySqlDataReader
            myData = objCmd.ExecuteReader
            myData.Read()
            Dim rfile As Byte() = myData("mbt_richtextbox")
  RichTextBox1.Rtf = System.Text.Encoding.Default.GetString(rfile)

            myData.Close()

        Catch ex As Exception
            Throw ex
        End Try
    End Sub
End Class


VB Code Exp2 : Read blob to file and set to RichTextbox
Imports System.IO
Imports MySql.Data.MySqlClient

Public Class FormTest03

    Dim objConn As MySqlConnection
    Dim objCmd As New MySqlCommand
    Dim dtAdapter As New MySqlDataAdapter

    Dim StrSql As String

    Dim dtRTB As New DataTable

    Private Sub FormTest03_Load(sender As Object, e As EventArgs) Handles Me.Load
        Try
            ConDB()

            LoadRichTextbox()

        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString)
        End Try
    End Sub

    Public Sub ConDB()
        Try
            Dim StrWer As StreamReader
            StrWer = File.OpenText(Application.StartupPath & "\AppConn.ini")

            Dim strConnString As String
            strConnString = StrWer.ReadLine()
            objConn = New MySqlConnection(strConnString)
            objConn.Open()
        Catch ex As Exception
            Throw ex
        End Try
    End Sub

    Private Sub LoadRichTextbox()
        Try
            If objConn.State = ConnectionState.Closed Then
                ConDB()
            Else
            End If

            dtRTB.Clear()
            StrSql = "SELECT * FROM myblobtable WHERE mbt_id=1;"

            With objCmd
                .Connection = objConn
                .CommandText = StrSql
                .CommandType = CommandType.Text
            End With
            Dim myData As MySqlDataReader
            myData = objCmd.ExecuteReader
            myData.Read()
            Dim rfile As Byte() = myData("mbt_richtextbox")
  File.WriteAllBytes(Application.StartupPath & "\tmpRTF\rtfData.txt", rfile)
            RichTextBox1.LoadFile(Application.StartupPath & "\tmpRTF\rtfData.txt", RichTextBoxStreamType.RichText)
            myData.Close()

        Catch ex As Exception
            Throw ex
        End Try
    End Sub
End Class

Result




End of simple way to working with RichTextbox.

Thank you