MongoDb 002 : CRUD – Part 1

We will continue with our exploration of MongoDb in this second part of tutorial, working our CRUD examples.

Let’s beging with the ‘C’. In order to create a document, you would need to use, as you can guess, the Insert command.

db.subject.insert({"title":"maths"})

Notice that we are using a new collection named subject here. MongoDb would automatically create the collection if it doesn’t exist. As you would have rightly guessed by now, we would be relying on JSON for passing any parameters for commands.

Let’s now do the same using C#. The first step you need to do is to install the MongoDB.Driver official Nuget package. Once the nuget is installed for our project, let’s start pushing some code in to our collection.

Following Commands gives us access to

string ConnectionString = "mongodb://localhost:27017";
var Client = new MongoClient(ConnectionString);
var Database = Client.GetDatabase("testdb");

The next step is to create the Data Structure which would be serialized and pushed into the DB.

public class SubjectEntity
{
public ObjectId Id { get; set; }

[BsonElement("title")]
public string Title { get; set; }
}

If you look closely, I have decorated each of the elements with BsonElement Attribute. This tells the compiler the literal it should use as key while creating the JSON document. Also if you notice, I have a property called Id of type ObjectId. This is a special type which would hold the unique ID which would be generated by MongoDb. This value is similiar to a GUID and unlike relational Db, we cannot use an autoincrement key, unless we do that bit of logic within our C# code. Honestly I wouldn’t recommend that.

Since I would like to use the ID generated by MongoDb, I have not decorated the Id field with BsonElement attribute, however I would still need in my Data Structure so that I could use it while retrieving data.

Okay, fine then. Let’s push some data to the Db now.

var subjectCollection = Database.GetCollection("subject");
subjectCollection .InsertOne(SubjectEntityInstance);

The above code inserts a object of type SubjectEntity to the Db. Once executed, hit our Console again and check if the Document is added to the SubjectCollection using the find command we learned in the first part of tutorial. Looks good right ?

Let’s try finding it with a find command from C# now.

var nameFilter = Builders.Filter
.Eq(x => x.Title,'maths');
var result = subjectCollection
.FindSync(nameFilter).First();

As you can understand from the code, we are first creating an instance of the filter we would like to use. In this case, we are using an Equality Filter (Subject title should be “maths”). We finally pass the filter to our FindSync Method.

We would be exploring part two of CRUD, Update and Delete in the second part of the tutorial.

The complete list of beginners guide in this series can be found in here

Advertisements

MongoDb 001 : Basic Commands

Before we actually get into MongoDb, let’s begin by importing a Test Db. You can access the JSON file here. Once you have downloaded the file, use following command to import the document into mongodb.

mongoimport --db testdb --collection students --drop --students.json

Let’s break it down, the command tells you to import a file students.json into a database called testdb and collection called students. Let’s go ahead verify if our import is good.

show dbs
use students
show collections

We first checked if the db named students has been created by checking the list of dbs. ‘show dbs’ command displays list of all dbs in the server. We followed it up with ‘use students’ command, which is similiar to the ‘use ‘ command in MySql. Finally, we issued ‘show collection’ command to view the collections in the db. Remember collections are analogous to tables in relational database.

We will now go ahead check out data. In relational database, data is saved as a row in the table. When it comes to Nosql databases, each row is represented as a document.
To view contents of a collection, we issue a find command
db..find()
In our case, it would be

db.students.find()

This would show our 3 documents within the collection. Let’s explore the find command a bit. We could give a specific search filter as arguement, in case we need to find a particular document, similiar to the ‘where’ condition in relational database. Instead of the customary “=” syntax, MongoDb relies on JSON for passing the argument.

db.students.find({"name":"jia anu"})

In case you want to pass more than one arguement, things don’t change much. Yes, you guess right, you pass more data in json.

db.students.find({"name":"jia anu","age":1})

If we need to search use a comparison operator ‘greater than’ or ‘less than’, you would need to use the ‘$gte and $lte commands.
Let’s go ahead and issue a command, say, we need to find all students who age is ‘less than or equal to 1’

db.students.find({"name":"jia anu","age":{$lte:1}})

A comprehensive list of comparison operator can be found in here

The complete list of beginners guide in this series can be found in here

MongoDb : Beginners Guide

MongoDb is definitely fascinating, and it is here to stay, there is no doubts about it. Here is my attempt to put together a small series of tutorial for beginners. Probably sometime down the line, I will put together a beginners guide on some other NoSql Db as well, but here is one for MongoDb, the most widely used NoSql.

001 : Basic Commands

002 : CRUD – Part 1

Workaround for MySql 5.7 EF DbFirst Issue

Anyone working on .Net application with MySql 5.7 and EF in Db First Approach would have come across what is an officially confirmed bug.  This bug rises when you attempt to generate/update your Entity Model after changes to the Database.

“Unable to generate the model because of the following exception: ‘System.Data.StrongTypingException: The value for column ‘IsPrimaryKey’ in table ‘TableDetails’ is DBNull. —> System.InvalidCastException:”

The workaround you need to do to fix this one

  1. Open your Workbench and execute following commands on your Db.
set global optimizer_switch='derived_merge=off';
set optimizer_switch='derived_merge=off';
  1. Close your workbench ( you might have to restart your visual studio as well ).
  2. Generate/Update your model.
That should work.

Appending Strings and Parameters in Firebird.

One of the issues I recently faced while working with Firebird was how to use a string within a ‘Execute Statement’. For example,

 


execute statement 'ALTER TABLE TESTTABLE ADD COLNAME VARCHAR(20) DEFAULT 'HELLO'';

This throws an error. though the answer looked farely simple when done. You need to two Single Quotes.


execute statement 'ALTER TABLE TESTTABLE ADD COLNAME VARCHAR(20) DEFAULT ''HELLO''';

There might be another situation, when you need to append string based on a variable. Here a way out to do the same, using Pipe Character.


EXECUTE BLOCK AS
DECLARE PARAM VARCHAR(20);
BEGIN
PARAM = 'DYNAMICCOL';
if (NOT exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'TESTTABLE' and rf.RDB$FIELD_NAME = 'DYNAMICCOL'))
then
BEGIN
execute statement 'ALTER TABLE TESTTABLE ADD '||PARAM||' SMALLINT;';
END
END ^
SET TERM ; ^

Firebird 3.0 Issues

Migrating to Firebird 3.0 has been routine that caused more headache than expected. One of most common, yet pestering error was “Your user name and password are not defined“. Interestingly, it worked perfectly fine when we were using the Firebird 4.10 Ado.net Nuget, however, the moment we update our Nuget package to 5.7, we are doomed.

The fix was easy though, all you needed to do was hit your Firebird.Config file in your server and look for following line.

UserManager = Legacy_UserManager, Srp

It loooks to be some new security feature added to Firebird 3.0, however, I was quite happy without it. Commenting out the line cleared off my issues .