This article provides a solution to reduce the size of a database in an ongoing web application project.
Introduction
When we create a project, we mostly don't think about document management, and initially, we start inserting documents into the database. After some years, the size of the database increases day by day, then we need a solution to reduce the size of the database.
This article will help to do such an activity in an ongoing web application project.
Background
We need a Helper to some activity:
ConnectToSharedFolder
in network with authentication and permissions - We have
CreateDirectoryPathIfNot
if not available in case of new type of functional documents MigrateFileToRootDirectory
from database to physical location DownloadFile
from network location
Using the Code
Our helper is FileHelper
:
public class FileHelper
FileFor
is an enumeration which will help to idenify for which functional activity these documents will take place.
public enum FileFor
{
PurchaseRequisition,
SalesOrder,
PurchaseOrder,
JournalVouchers,
Contracts,
Invoice,
CustomerDocuments,
VendorDocuments,
}
ConnectToSharedFolder
class will help us to communicate with network location.
public class ConnectToSharedFolder
tmpUpload
is File Control, or you can have any object which will provide you HttpPostedFile
, to save a new file for SalesOrder
, for file reference, this will return filepathToUpload
, we will save this location database table.
In TemperoryStorageForFiles
, we can hold multiple Files.
if (tmpUpload.HasFile)
{
var helper = new FileHelper(FileFor.SalesOrder);
helper.AddFile(tmpUpload.PostedFile);
var lastRecordIndex = helper.TemperoryStorageForFiles.Count;
var obj = helper.TemperoryStorageForFiles[lastRecordIndex - 1];
var filepathToUpload = obj.FilePath;
helper.SaveFile();
}
We have a simple database table:
rowid
is primary key filename
is your document File name uploadedDoc
is File data in binary format FileLocation
is for referencing filepathToUpload
select rowid,filename,uploadedDoc,FileLocation from tbl_doc
Logic for migrating existing records to Physical File Location, MigrateFileToRootDirectory
function will help to do this activity. With this, we migrate 10 records as a bunch.
var data =
string.Format(@"select top 10 rowid,filename,
uploadedDoc from tbl_doc where isnull(FileLocation,'')=''")
.ExecuteDataTable();
foreach (DataRow dr in data.Rows)
{
var rowid= dr["rowid"].ToString();
var filename= dr["filename"].ToString();
var document = (byte[]) dr["uploadedDoc"];
var filepathToUpload = FileHelper.MigrateFileToRootDirectory
(FileFor.SalesOrder, filename, document);
string.Format(@"update tbl_doc set FileLocation='{0}',
uploadedDoc = NULL where rowid='{1}'", filepathToUpload, rowid)
.ExecuteNonQuery();
}
To download a file:
var data =
string.Format(@"select filename,FileLocation from tbl_doc where rowid=1")
.ExecuteDataTable();
if(data!=null && data.Rows.Count>0)
{
var filename = data.Rows[0]["filename"].ToString();
var fileurl = data.Rows[0]["FileLocation"].ToString();
FileHelper.DownloadFile(filename, fileurl);
}
Need some configuration parameter:
private string RootFilePath = ConfigurationManager.AppSettings["FileStorageLocation"];
private readonly NetworkCredential Credentials =
new NetworkCredential(@"UserName", "Password");
Encrypted file uploaded structure:
Database Document column becomes empty and file location is updated:
Points of Interest
After this activity is complete, document table size reduces and all documents are safely moved to physical location [this location is in network or any shared folder in network.]
History
- 25th October, 2022: Version 1.0.0.1