October 30, 2014
Hot Topics:
RSS RSS feed Download our iPhone app

Saving Rich Edit Control Text to SQL Server

  • June 28, 2004
  • By Tom Archer
  • Send Email »
  • More Articles »
Welcome to this week's installment of .NET Tips & Techniques! Each week, award-winning Architect and Lead Programmer Tom Archer from the Archer Consulting Group demonstrates how to perform a practical .NET programming task. The demo project provided with this article uses the sample SQL Server Northwind database and its Employees table (Photo column) as that database should be present with any installation of SQL Server.

Recently I had a situation where a client's user interface (UI) had several rich-edit controls on various dialogs enabling the end-user to input formatted text. In addition to being able to save this data to a SQL Server database (as well as read it back), the client also wanted the formatting saved. This task was not nearly as straightforward as I had imagined and took me a couple of hours to piece together. Hence, I decided to write a helpful article for any of you who might run into this particular problem in your daily programming lives.

Figure 1 illustrates a demo application I've provided with this article. You can see a rich-edit control along with buttons to save and read the data. All I did to the demo was place the rich-edit control on the form and the Save, Read, and Close buttons. I'll, therefore, present both the Save and Read methods.


Figure 1: Demo application illustrating how to save and read RTF data to and from SQL Server

Saving RTF Data to SQL Server

The easiest method of saving RTF data from a rich-edit control was to first save the contents of the control to disk and then write that file's contents to a BLOB (binary large object) column. You can see this in the code sample below where I use the RichTextBox.SaveFile method (giving it a generic temporary file name). I then instantiate a FileStream object and read this file into a Byte array. Once that is done, I use the SQLClient classes to write the data to the Northwind Employees table:
private void btnSave_Click(object sender, System.EventArgs e)
{
  FileStream stream = null;
  SqlConnection cn = null;
  SqlCommand cmd = null;
  try
  {
    richTextBox1.SaveFile("temp.rtf");
    stream = new FileStream("temp.rtf", FileMode.Open, FileAccess.Read);
    int size = Convert.ToInt32(stream.Length);
    Byte[] rtf = new Byte[size];
    stream.Read(rtf, 0, size);
    
    cn = new SqlConnection("Database=Northwind;Integrated Security=true;");
    cn.Open();
    cmd = new SqlCommand("UPDATE Employees SET Photo=@Photo WHERE EmployeeID=1", cn);
    
    SqlParameter paramRTF = 
    new SqlParameter("@Photo",
                      SqlDbType.Image,
                      rtf.Length,
                      ParameterDirection.Input,
                      false,
                      0,0,null,
                      DataRowVersion.Current,
                      rtf);
                      cmd.Parameters.Add(paramRTF);
      
    int rowsUpdated = Convert.ToInt32(cmd.ExecuteNonQuery());
    MessageBox.Show(String.Format("{0} rows updated", rowsUpdated));
  }
  catch(Exception ex)
  {
    MessageBox.Show(ex.Message);
  }
  finally
  {
    if (null != stream) stream.Close();
    if (null != cmd) cmd.Parameters.Clear();
    if (null != cn) cn.Close();
  }
}
A SQLConnection object is instantiated where I don't specify a server name, knowing that it defaults to the localhost. I then open the connection and create a SqlCommand object, where I let the command object know that I will be setting up a parameter object to update the first record in the table (where the EmployeeID is equal to 1). The ampersand that precedes the Photo column name tells the command object that a parameter will be forthcoming. This is necessary because it is the only way to update a BLOB column. The instantiation of the SqlParameter object where I associate it with the Byte array containing the RTF data follows the creation of the SqlCommand object. Next, I add the new SqlParameter object to the command object and call the command's ExecuteNonQuery method (which simply means that the command will not return data). The ExecuteNonQuery returns the number of rows updated (which should be 1) and I then display that in a message box.





Page 1 of 2



Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Sitemap | Contact Us

Rocket Fuel