Maintaining Rich Text Format in SQL

Recently i had to make User Interface with few rich text box controls. Rich text box control allows the user to input formatted text. But this formatted text is of no use if we don't persist the input format. I took few hours to get the solution. I thought why not to write a blog on it.

Create a Form and put the control like below screen shot





Formatting rich text box data :

private void Bold_Click(object sender, System.EventArgs e)
{
Font ft = new Font(richTextBox1.Font,FontStyle.Bold);
richTextBox1.SelectionFont = ft;
}

private void color_Click(object sender, System.EventArgs e)
{
richTextBox1.SelectionColor = Color.Red;
}



Saving rich text box data in SQL :

Create a table(MyImages)with one column(imgField) in SQL with DataType as image.

Create connection to SQL server to save rtf text in database. I preffered to temporarily save rtf in disk with SaveFile of rich text box control, then i created instance of FileStream object and put the data in byte array and saved the byte array in the database table(MyImages), then i deleted the temp(test.rtf) file.

private void savetoDB_Click(object sender, System.EventArgs e)
{

string connectionString = "";
SqlConnection con = new SqlConnection(connectionString);
SqlDataAdapter da = new SqlDataAdapter("Select * From MyImages", con);
SqlCommandBuilder MyCB = new SqlCommandBuilder(da);
DataSet ds = new DataSet("MyImages");
da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
string filePath = @"..\..\RTFDoc\test" + DateTime.Now.Millisecond + ".rtf";
richTextBox1.SaveFile(filePath);
FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.Read);
byte[] MyData= new byte[fs.Length];
fs.Read(MyData, 0, System.Convert.ToInt32(fs.Length));
fs.Close();
da.Fill(ds,"MyImages");
DataRow myRow;
myRow=ds.Tables["MyImages"].NewRow();
myRow["imgField"] = MyData;
ds.Tables["MyImages"].Rows.Add(myRow);
da.Update(ds, "MyImages");
con.Close();
File.Delete(filePath);
}


Reading data from SQL :

Create connection to SQL server to get formatted text from database. Then i read the data from database using SQLDataReader, i then created instance of ASCIIEncoding object to convert bytre array in a string object and using string object to fill rich text box control. Then i am closing the reader and connection instance.

private void loadfromDB_Click(object sender, System.EventArgs e)
{
string ConnectionString = "";
SqlConnection connection = new SqlConnection(ConnectionString);
SqlCommand command = new SqlCommand("Select * From MyImages", connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Byte[] rtf = new Byte[Convert.ToInt32((reader.GetBytes(0, 0, null, 0, Int32.MaxValue)))];
long bytesReceived = reader.GetBytes(0, 0, rtf, 0, rtf.Length);
ASCIIEncoding encoding = new ASCIIEncoding();
richTextBox1.Rtf = encoding.GetString(rtf, 0, Convert.ToInt32(bytesReceived));
}
reader.Close();
connection.Close();
}

Happy Coding :)