Home > Code > C# > How to Store, Read & Delete a Word Document in Database Using .NET

How to Store, Read & Delete a Word Document in Database Using .NET

by sher azam   on Sep 03, 2014   Category: C#   |  Views: 1492    |  Points: 25   |  Gold 


This technical tip explains how developers can store, read & delete a Word document in Database inside their .NET Applications. One of the tasks you may need to perform 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. The storage of all previous versions of documents would be required to be stored in a database system. The ability to store documents in the database is also extremely useful when your application provides a web-based service. This sample shows how to store a document into a database and then load it back into a Document object for working with. For the sake of simplicity, the name of the file 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 the documents. The second column “FileContent” is stored as a BLOB object which stores the document object is byte form.

//Shows how to setup a connection to a database and execute commands.

//[C# Code Sample]

// Create a connection to the database.
mConnection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dbName);

// Open the database connection.
mConnection.Open();

//[VB Code Sample]

' Create a connection to the database.
mConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbName)

' Open the database connection.
mConnection.Open()

//Stores a document to the specified database

//[C# Code Sample]


public static void StoreToDatabase(Document doc)
{
// Save the document to a MemoryStream object.
MemoryStream stream = new MemoryStream();
doc.Save(stream, SaveFormat.Doc);

// 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 + "', @Doc)";
OleDbCommand command = new OleDbCommand(commandString, mConnection);

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

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

}

//[VB.NET Code Sample]


Public Shared Sub StoreToDatabase(ByVal doc As Document)
' Save the document to a MemoryStream object.
Dim stream As New MemoryStream()
doc.Save(stream, SaveFormat.Doc)

' Get the filename from the document.
Dim fileName As String = Path.GetFileName(doc.OriginalFileName)

' Create the SQL command.
Dim commandString As String = "INSERT INTO Documents (FileName, FileContent) VALUES('" & fileName & "', @Doc)"
Dim command As New OleDbCommand(commandString, mConnection)

' Add the @Doc parameter.
command.Parameters.AddWithValue("Doc", stream.ToArray())

' Write the document to the database.
command.ExecuteNonQuery()

End Sub

//Retrieves and returns the document from the specified database using the filename as a key to fetch the document.

//[C# Code Sample]


public static Document ReadFromDatabase(string fileName, string path)
{
// 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;

}

//[VB.NET Code Sample]


Public Shared Function ReadFromDatabase(ByVal fileName As String, ByVal path As String) As Document
' Create the SQL command.
Dim commandString As String = "SELECT * FROM Documents WHERE FileName='" & fileName & "'"
Dim command As New OleDbCommand(commandString, mConnection)

' Create the data adapter.
Dim adapter As New OleDbDataAdapter(command)

' Fill the results from the database into a DataTable.
Dim dataTable As 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 Then
Throw New ArgumentException(String.Format("Could not find any record matching the document ""{0}"" in the database.", fileName))

End If

' The document is stored in byte form in the FileContent column.
' Retrieve these bytes of the first matching record to a new buffer.
Dim buffer() As Byte = CType(dataTable.Rows(0)("FileContent"), Byte())

' Wrap the bytes from the buffer into a new MemoryStream object.
Dim newStream As New MemoryStream(buffer)

' Read the document from the stream.
Dim doc As New Document(newStream)

' Return the retrieved document.
Return doc

End Function

//Delete the document from the database, using filename to fetch the record.

//[C# Code Sample]


public static void DeleteFromDatabase(string fileName)
{
// Create the SQL command.
string commandString = "DELETE * FROM Documents WHERE FileName='" + fileName + "'";
OleDbCommand command = new OleDbCommand(commandString, mConnection);

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

}

//[VB.NET Code Sample]


Public Shared Sub DeleteFromDatabase(ByVal fileName As String)
' Create the SQL command.
Dim commandString As String = "DELETE * FROM Documents WHERE FileName='" & fileName & "'"
Dim command As New OleDbCommand(commandString, mConnection)

' Delete the record.
command.ExecuteNonQuery()
//Stores the document to a database, then reads the same document back again, and finally deletes the record containing the document from the database.

//[C# Code Sample]


// Store the document to the database.
StoreToDatabase(doc);
// Read the document from the database and store the file to disk.
Document dbDoc = ReadFromDatabase(fileName, dataDir);

// 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);

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

//[VB.NET Code Sample]

' Store the document to the database.
StoreToDatabase(doc)
' Read the document from the database and store the file to disk.
Dim dbDoc As Document = ReadFromDatabase(fileName, dataDir)

' Save the retrieved document to disk.
Dim newFileName As String = Path.GetFileNameWithoutExtension(fileName) & " from DB" & Path.GetExtension(fileName)
dbDoc.Save(dataDir & newFileName)

' Delete the document from the database.
DeleteFromDatabase(fileName)

' Close the connection to the database.
mConnection.Close()



More about Aspose.Words for .NET

Aspose.Words is a word processing component that enables Java & .NET applications to read, write and modify Word documents without using Microsoft Word. Other useful features include document creation, content and formatting manipulation, mail merge abilities, reporting features, TOC updated/rebuilt, Embedded OOXML, Footnotes rendering and support of DOCX, DOC, WordprocessingML, HTML, XHTML, TXT and PDF formats (requires Aspose.Pdf). It supports both 32-bit and 64-bit operating systems. You can even use Aspose.Words to build applications with Mono.

More about Aspose.Words for .NET


- Homepage of Aspose.Words for .NET: http://www.aspose.com/.net/word-component.aspx

- Download Aspose.Words for .NET: http://www.aspose.com/community/files/51/.net-components/aspose.words-for-.net/default.aspx



Post Code  |  Code Snippet Home

User Responses


No response found, be the first to review this code snippet.

Submit feedback about this code snippet

Please sign in to post feedback

Latest Posts