A database, as you may already know, is a collection of data stored in an organized way allowing you to retrieve it efficiently. While every typical .NET enterprise application incorporates a database, the data it stores is also usually accompanied by some files e.g. pictures, videos, documents etc.
In .NET applications, these files are treated as binary data and the best way to store them would also be in a database. In order to maintain relevance between the data and the files, traditionally you have two options:
- Either store the binary data in the related JSON document. For example, a customer object containing its picture
- Or store the files in your file-system (hard-disk) and keep links pointing to those files in your JSON document. For example, store customer object in the database and the picture separately in your hard-disk. Store the link to this picture in your customer object as well.
Both options work, but not without annoying problems. For instance;
- Storing files (binary data) in JSON documents makes them unnecessarily large. This affects performance when you are fetching the document. Fetching a lot of them might result in choking your network.
- Storing files in a file-system and keeping links in your JSON documents is problematic as well i.e. you have to manually manage where you keep your separate files and also update all your links whenever files change location. Querying also causes problems.
NosDB on the other-hand addresses this pain and provides an effective way to store supporting files and documents through a feature called Attachments. To use it, create a separate collection with large-file support (see the documentation for details). Store all the files in this collection and let NosDB handle it for you. Assign some metadata in a JSON document with the attachment to help you fetch your attachments using plain SQL queries. NosDB is a JSON document database that stores and retrieves attachments in chunks thereby not causing any network issues.
To see it in action, let’s take an example from the sports industry.
Inserting Picture with Metadata
Let’s insert a player’s profile and its related files in the database. Each file can have its own metadata which helps you query the file based on selected criteria. Therefore, you would insert meaningful metadata with the file as well e.g. player ID, file extension, category and type. This should be enough to meet the defined criteria in this use case.
// Insert player profile into database store collection
JSONDocument playerProfile = new JSONDocument();
playerProfile.Key = "7683"; //setting playerID
playerProfile.Add("Name", "Christian Pulisic");
playerProfile.Add("Club", "Borussia Dortmund");
// Insert player profile picture and metadata into Attachments collection
string attachmentId = "player:7683:Pic"; //Unique ID of file to insert
JSONDocument metadata = new JSONDocument(); //attachment's metadata
using (filestream = new FileStream(@"D:PlayersProfilePic7683.jpg", FileMode.Open, FileAccess.Read))
attachmentStore.InsertAttachment(attachmentId, filestream, metadata);
Get Player Picture with SQL Queries
By now we have stored some data in the database but we must also be able to extract the information to make it useful. We will be using NosDB
AttachmentStore API methods and SQL queries to retrieve required data and files. Here are a few examples from our use case.
- Get Player Profile along with profile picture
- Get Player Profile
123//Get player having playerID 7683string playerId = "7683";JSONDocument playerProfile = profileCollection.GetDocument(playerId);
- Get Profile Picture
123//Get Profile Picture of the player abovestring profilePicID = “player”+playerProfile.Key+”Pic”; //Regenerate IDAttachment profilePicture = attachmentStore.GetAttachment(profilePicID);
- Get Player Profile
- Get Profile Pictures of all players
123//Retrieve Profile Pictures of all playersstring query = "SELECT * FROM attachments WHERE UserMetadata.type = “profile_pic”";List<Attachment> attachments = attachmentStore.GetAttachments(query);
- Get Profile picture of Player having playerID 7683
123//Retrieve Profile Pictures of player having playerID 7683string query = "SELECT * FROM attachments WHERE UserMetadata.type = “profile_pic” AND UserMetadata.playerID = “7683”";List<Attachment> attachments = attachmentStore.GetAttachments(query);
- Get all the Files
To retrieve all the files, a GetAttachments() method is provided in the AttachmentStore API.
12//Get all attachmentsList<Attachment> attachments = attachmentStore.GetAttachments();
Data is not all simple keys and values. To handle supporting files as binary data, NosDB Client API allows you to transfer this data to the database in configurable chunks thus allowing effective use of network bandwidth. Also, since the database cannot read the attachment contents, NosDB allows you to store query-able meta data information in JSON format. This helps you to fetch attachments using standard SQL queries. There is still a lot to be explored including the Delete, Update and some overloads of Get APIs. You can look into NosDB Programmer’s Guide for additional information.