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.
Reading RTF Data from SQL Server
Reading the RTF data is even easier than saving it because no intermediary file is necessary. The first thing I do is to create the SqlConnection object the same way it was instantiated in the btnSave_Click method. I then instantiate a SqlCommand object, where I specify that I wish to read the Photo column from the Employees table where the EmployeeID value is equal to 1. The SqlCommand.ExecuteReader method executes the command and returns a SqlDataReader object. A call to SqlDataReader.Read should return the only record that matches my criteria. Just to be sure that valid data has been read, I then check the SqlDataReader.HasRows property and verify that the Photo column I’m reading is not null. Otherwise, an exception would be thrown when I attempt to read it.
Once I’ve determined that I have valid data, I then use the SqlDataReader.GetBytes method to read the binary data into a Byte array. You’ll notice that there are actually two calls to GetBytes in the code sample below. I did this because GetBytes cannot be called to retrieve the data into a buffer until that buffer has been allocated. However, I know how big to make the buffer only after calling GetBytes! Therefore, the first call to GetBytes passes a null value for the object that is to receive the data, meaning that this call will return to me only the number of bytes contained in the Photo column. I then allocate the Byte array and call GetBytes a second time—this time passing the Byte array and receiving the data. Once the data is in the buffer, I then can use the ASCIIEncoding object to convert the data from a Byte array into a String object, which is finally used to update the rich-edit control:
private void btnRead_Click(object sender, System.EventArgs e) { richTextBox1.Clear(); SqlConnection cn = null; SqlCommand cmd = null; SqlDataReader reader = null; try { cn = new SqlConnection("Database=Northwind;Integrated Security=true;"); cn.Open(); cmd = new SqlCommand("SELECT Photo FROM Employees WHERE EmployeeID=1", cn); reader = cmd.ExecuteReader(); reader.Read(); if (reader.HasRows) { if (!reader.IsDBNull(0)) { 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)); } } } catch(Exception ex) { MessageBox.Show(ex.Message); } finally { if (null != reader) reader.Close(); if (null != cn) cn.Close(); } }