Serialize and Work with a Document in a Database

One of the tasks you may need to get done when working with documents is storing and retrieving Document objects to and from a database. For example, this would be necessary if you were implementing any type of content management system. All previous versions of documents must be stored in the database system. The ability to store documents in the database is also extremely useful when your application provides a web-based service.

Aspose.Words provides an ability to convert a document into a byte array for subsequent work with this document in a database.

Convert a Document to Byte Array

To store a document in a database or to prepare a document for transmission across the web, it is often necessary to serialize the document to obtain a byte array.

To serialize a Document object in Aspose.Words:

1. Save it to a MemoryStream using the Save method overload of the Document class.
2. Call the ToArray method, which returns an array of bytes representing the document in byte form.

The steps above then can be reversed to load the bytes back into a Document object.

The selected save format is important to ensure the highest fidelity is retained when saving and reloading into the Document object. For this reason, it is suggested to use a series of OOXML file formats.

The example below shows how to serialize a Document object to obtain a byte array, and then how to unserialize the byte array to obtain a Document object again:

// For complete examples and data files, please go to https://github.com/aspose-words/Aspose.Words-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir_LoadingAndSaving();

// Load the document from disk.
Document doc = new Document(dataDir + "Test File (doc).doc");

// Create a new memory stream.
MemoryStream outStream = new MemoryStream();
// Save the document to stream.
doc.Save(outStream, SaveFormat.Docx);

// Convert the document to byte form.
byte[] docBytes = outStream.ToArray();

// The bytes are now ready to be stored/transmitted.

// Now reverse the steps to load the bytes back into a document object.
MemoryStream inStream = new MemoryStream(docBytes);

// Load the stream into a new document object.
Document loadDoc = new Document(inStream);

You can download the template file of this example from Aspose.Words GitHub.

Store, Read and Delete a Document in a Database

This section shows how to save a document in a database and then load it back into a Document object for working with it. For simplicity, the file name is the key used to store and fetch documents from the database. The database contains two columns. The first column “FileName” is stored as a String and is used to identify documents. The second column “FileContent” is stored as a BLOB object which stores the document object in the byte form.

The following code example shows how to set up a connection to a database and execute commands:

// For complete examples and data files, please go to https://github.com/aspose-words/Aspose.Words-for-.NET
string dbName = "";
// Open a database connection.
string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + RunExamples.GetDataDir_Database() + dbName;
OleDbConnection mConnection = new OleDbConnection(connString);
mConnection.Open();

In this example, we use the Microsoft Access .mdb database to store an Aspose.Words document.

The following code example shows how to save a document to the database, then read the same document again, and finally delete the record containing the document from the database:

// For complete examples and data files, please go to https://github.com/aspose-words/Aspose.Words-for-.NET
// Store the document to the database.
StoreToDatabase(doc, mConnection);
// Read the document from the database and store the file to disk.
Document dbDoc = ReadFromDatabase(fileName, mConnection);

// Save the retrieved document to disk.
string newFileName = Path.GetFileNameWithoutExtension(fileName) + " from DB" + Path.GetExtension(fileName);
dbDoc.Save(dataDir + newFileName);

// Delete the document from the database.
DeleteFromDatabase(fileName, mConnection);

// Close the connection to the database.
mConnection.Close();

Save a Document to a Database

To save a document in a database convert this document to an array of bytes, as described at the beginning of this article. Then, save this byte array into a database field.

The following code example shows how to save a document to the specified database:

// For complete examples and data files, please go to https://github.com/aspose-words/Aspose.Words-for-.NET
public static void StoreToDatabase(Document doc, OleDbConnection mConnection)
{
    // Save the document to a MemoryStream object.
    MemoryStream stream = new MemoryStream();
    doc.Save(stream, SaveFormat.Docx);

    // Get the filename from the document.
    string fileName = Path.GetFileName(doc.OriginalFileName);

    // Create the SQL command.
    string commandString = "INSERT INTO Documents (FileName, FileContent) VALUES('" + fileName + "', @Docx)";
    OleDbCommand command = new OleDbCommand(commandString, mConnection);

    // Add the @Docx parameter.
    command.Parameters.AddWithValue("Docx", stream.ToArray());

    // Write the document to the database.
    command.ExecuteNonQuery(); 
}

Specify commandString, which is an SQL expression that does all the work:

• To save a document into the database, the “INSERT INTO” command is used and a table specified along with the values of two record fields – FileName and FileContent. To avoid additional parameters, the file name is taken from the Document object itself. The FileContent field value is assigned bytes from the memory stream, which contains a binary representation of the stored document.
• The remaining line of code executes the command which stores the Aspose.Words document in the database.

Retrieve a Document from a Database

To retrieve a document from the database, select the record that contains the document data as an array of bytes. Then load the byte array from the record into MemoryStream and create a Document object that will load the document from the MemoryStream.

The following code example shows how to retrieve and return a document from the specified database using the filename as a key to fetch this document:

// For complete examples and data files, please go to https://github.com/aspose-words/Aspose.Words-for-.NET
public static Document ReadFromDatabase(string fileName, OleDbConnection mConnection)
{
    // Create the SQL command.
    string commandString = "SELECT * FROM Documents WHERE FileName='" + fileName + "'";
    OleDbCommand command = new OleDbCommand(commandString, mConnection);

    // Create the data adapter.
    OleDbDataAdapter adapter = new OleDbDataAdapter(command);

    // Fill the results from the database into a DataTable.
    DataTable dataTable = new DataTable();
    adapter.Fill(dataTable);

    // Check there was a matching record found from the database and throw an exception if no record was found.
    if (dataTable.Rows.Count == 0)
        throw new ArgumentException(string.Format("Could not find any record matching the document \"{0}\" in the database.", fileName));

    // The document is stored in byte form in the FileContent column.
    // Retrieve these bytes of the first matching record to a new buffer.
    byte[] buffer = (byte[])dataTable.Rows[0]["FileContent"];

    // Wrap the bytes from the buffer into a new MemoryStream object.
    MemoryStream newStream = new MemoryStream(buffer);

    // Read the document from the stream.
    Document doc = new Document(newStream);

    // Return the retrieved document.
    return doc;
}

The SQL command “SELECT * FROM” is used to fetch the appropriate record based on the file name.

Delete a Document from a Database

To delete a document from the database, use the appropriate SQL command without any manipulations on the Document object.

The following code example shows how to delete a document from the database, using the file name to fetch the record:

// For complete examples and data files, please go to https://github.com/aspose-words/Aspose.Words-for-.NET
public static void DeleteFromDatabase(string fileName, OleDbConnection mConnection)
{
    // Create the SQL command.
    string commandString = "DELETE * FROM Documents WHERE FileName='" + fileName + "'";
    OleDbCommand command = new OleDbCommand(commandString, mConnection);

    // Delete the record.
    command.ExecuteNonQuery();

}