Working/Getting Started with Mongo DB and C# .NET

First of all lets describe what is Mongo DB and why it is used…
Advantages of using Mongo DB

  1. High Performance
  2. Highly Scalable
  3. High Availability

The only disadvantage mongo db has, is the disk space usage, which in these times is pretty cheap.

Mongo DB is a document based database, this means it stores collections of documents in a JSON/BSON format.

If you want to know more about mongo db check out this link:

http://www.mongodb.org/about/introduction/

First off we need to create a mongo database, MongoLab provides free hosting of a MongoDB (500mb maximum size)

https://mongolab.com/

Create an account with them, when done you can create a new database:

Now click “Create new mongodb deployment” and you’re done…

Click on the Database you created,

  1. Add a User
  2. Get the connection string for your database and change the connection string’s username and password with the User you created.

Important to note, don’t use special characters in your password because the connection string messes up when these characters are used. It is a known issue especially with the ‘@’ character.

Now create a new Visual Studio solution

In my case, I created a website MVC, anyway you can create any type of solution/project…

I also added a solution to put my data layer classes in it (more organized).

Now open Nuget and search for Mongo… you will find a package which includes the mongo db driver

When installed open up the references and note that two references were added, specifically the ones below:

Now we are good to go and start coding our way with C# and the Mongo Driver!

So first lets create a class which will represent an entity (collection) in our database, in this case I am going to create a simple entity Team (for a football team).

Note that the attributes which are used in the Team.cs class are attributes from MongoDB.Bson.Serialization.Attributes (libraries we added via nuget)

public class Team
    {
        /// <summary>
        /// This attribute is used to map the Id property to the ObjectId in the collection
        /// </summary>
        [BsonId]
        public ObjectId Id { get; set; }
 
        /// <summary>
        /// This is an attribute of my Team - in this case the team name
        /// </summary>
        [BsonElement("name")]
        public string Name { get; set; }
    }

Now we are going to need a class which manages our collections, in this case I am going to create a simple Connection class which could take as parameters the collection name and the type (in case you have more than a Team entity) Note, this is not the right way of doing it, in a real world scenario every class carries only its own responsibility. Anyway this tutorial was meant to show you how to communicate with MongoDB…

public class Connection
    {
        private string _connectionString;
        public MongoClient Entities { get; set; }
        public MongoServer Server { get; set; }
 
        private const string DatabaseName = "PUT DATABASE NAME HERE";
        public MongoDatabase Database { get; set; }
 
        public MongoCollection<Team> Teams { get; set; }
        public Connection()
        {
            _connectionString = "PUT CONNECTION STRING FROM MONGO LAB HERE - REPLACE username and password";
            if (Entities == null)
            {
                Entities = new MongoClient(_connectionString);
                Server = Entities.GetServer();
                Database = Server.GetDatabase(DatabaseName);
            }
        }
 
        public List<T> GetAll<T>(string collectionName)
        {
            var collection = GetCollectionOrCreateIfNotExistent<T>(collectionName);
            return collection.FindAllAs<T>().ToList();
        }
 
        public T Get<T>(string collectionName)
        {
            var collection = GetCollectionOrCreateIfNotExistent<T>(collectionName);
            return collection.FindOneAs<T>();
        }
 
        public void Add<T>(T entity, string collectionName)
        {
            var collection = GetCollectionOrCreateIfNotExistent<T>(collectionName);
            collection.Insert(entity);
        }
 
        /// <summary>
        /// This method will get the collection, if the collection is null it creates one for us - similar to a code first approach
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="collectionName"></param>
        /// <returns></returns>
        private MongoCollection GetCollectionOrCreateIfNotExistent<T>(string collectionName)
        {
            var collection = Database.GetCollection<T>(collectionName);
            if (collection == null) Database.CreateCollection(collectionName);
            return collection;
        }
    }

Ok so read the inline comments I added to this class, where there are no comments, its because it is self explainable…

This is my current Solution’s structure:

As you can notice now I added a TeamController in my MVC Project which will handle the data procedures and render the views accordingly.

Note: make a Reference to your data project so you will be able to use the Connection Class we created

This is my TeamController code:

public class TeamController : Controller
    {
        public ActionResult Index()
        {
            Connection conn = new Connection();
            //Calling get all method
            var teams = conn.GetAll<Team>("teams");
            return View(teams);
        }
 
        public ActionResult Details(int id)
        {
            return View();
        }
 
        public ActionResult Create()
        {
            return View();
        }
 
        [HttpPost]
        public ActionResult Create(Team collection)
        {
            try
            {
                // TODO: Add insert logic here
                Connection conn = new Connection();
                //calling the add method
                conn.Add(collection, "teams");
                return RedirectToAction("Index");
            }
            catch
            {
                return View();
            }
        }
    }

Ok let’s see if our code works as expected…

lets navigate to:

yourlocalhostaddress/Team   – Which calls our Index Action in the Team Controller (I already added two teams)

yourlocalhostaddress/Team/Create   – Which calls our Create Get Action in the Team Controller

And now we go back to our Team index and see our added team:


public class Team
    {
        /// <summary>
        /// This attribute is used to map the Id property to the ObjectId in the collection
        /// </summary>
        [BsonId]
        public ObjectId Id { get; set; }
 
        /// <summary>
        /// This is an attribute of my Team - in this case the team name
        /// </summary>
        [BsonElement("name")]
        public string Name { get; set; }
    }

Code First Entity Framework – Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries

While I was working on EF I encountered a funny exception:

Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries

At first I did not know why it was throwing this exception, so I decided to see/inspect the query that was being build in the context.

Turns out the query was a total mess, it was nesting a lot of SQL, the scrollbar was so small… so something was wrong.

The query had a .Any in it which forces the DbContext to build a query with nested sql.

To fix this error remove the

.Any

And Convert it to

.Contains — Contains is converted into an IS IN SQL Statement

The query now looks much smaller… So if you encounter this error look for .Any in your query and convert it to a .Contains

MVC/WebApi Getting location by IP Address

I am currently building a Website using WebApi, MVC and AngularJS and I was wondering how I could know from which location the User is browsing my website…

So I decided to make some research and I found a Web Service that takes as parameter an IP Address and gives you back the XML Response with details which include:

<Response>
<IP>00.00.00.00</IP>
<CountryCode>MT</CountryCode>
<CountryName>Malta</CountryName>
<RegionCode>32</RegionCode>
<RegionName>Il-Mosta</RegionName>
<City>Mosta</City>
<ZipCode/>
<TimeZone>Europe/Malta</TimeZone>
<Latitude>35.909</Latitude>
<Longitude>14.426</Longitude>
<MetroCode>0</MetroCode>
</Response>

I Changed the Ip Address to 00 for protection…

You can however try this with your IP Address:

http://freegeoip.net/xml/INSERT_YOUR_IP_HERE

I decided to create some clean code for this approach… Basically what I did consisted of creating a BaseMvcController – Or you can create a BaseApiController to get the Request Details and I created a class to hold this data…

public class RequestDetails
    {
        public DateTime TimeStamp { get; set; }
        public string CallerIp { get; set; }
        public string CallerAgent { get; set; }
        public string CalledUrl { get; set; }
    }
    
    //this is used for Web Api base controller
   public RequestDetails DownloadRequestDetails()
   {
       var requestModel = new RequestDetails
       {
           TimeStamp = DateTime.Now,
           CallerIp = HttpContext.Current.Request.UserHostAddress,
           CallerAgent = HttpContext.Current.Request.UserAgent,
           CalledUrl = HttpContext.Current.Request.Url.OriginalString
       };
       return requestModel;
   }
   
   //this is used for MVC Base Controller
   public RequestDetails DownloadRequestDetails()
   {
       var requestModel = new RequestDetails
       {
           TimeStamp = DateTime.Now,
           CallerIp = System.Web.HttpContext.Current.Request.UserHostAddress,
           CallerAgent = System.Web.HttpContext.Current.Request.UserAgent,
           CalledUrl = System.Web.HttpContext.Current.Request.Url.OriginalString
       };
       return requestModel;
   }

I did it in a base controller, so that every time I needed this data I could call it directly from any controller that inherits from it.

I also had to create a class to hold the XML data which is generated for response from the Free Geo Ip web service:

[XmlRoot("Response")]
    public class IpAddressDetails
    {
        [XmlElement("Ip")]
        public string Ip { get; set; }
        [XmlElement("CountryCode")]
        public string CountryCode { get; set; }
        [XmlElement("CountryName")]
        public string CountryName { get; set; }
        [XmlElement("RegionCode")]
        public string RegionCode { get; set; }
        [XmlElement("RegionName")]
        public string RegionName { get; set; }
        [XmlElement("City")]
        public string City { get; set; }
        [XmlElement("ZipCode")]
        public string ZipCode { get; set; }
        [XmlElement("Latitude")]
        public double Latitude { get; set; }
        [XmlElement("Longitude")]
        public double Longitude { get; set; }
        [XmlElement("MetroCode")]
        public string MetroCode { get; set; }
        [XmlElement("AreaCode")]
        public string AreaCode { get; set; }
    }

So now I have the IP Address of the User, I now need to call the service and download information about that IP Address..

private const string HttpFreegeoipNetXML = "http://freegeoip.net/xml/{0}";

public IpAddressDetails DownloadIpAddressDetails(RequestDetails requestDetails, string userId)
{
  var ipAddressDetails = new IpAddressDetails();
  
    //this is useful since when running on localhost your ip address will return ::1 
    #if DEBUG
    //insert your ip address here
    requestDetails.CallerIp = "00.00.00.00";
    #endif
    //caching is being used so that if I already have the IP Address details I don't re download them again...
  string cacheKey = requestDetails.CallerIp + userId;
  if (_cacheStoreService.IsSet(cacheKey))
  {
      return (IpAddressDetails)_cacheStoreService.Get(cacheKey);
  }
  try
  {
        //create a web client
      using (var objClient = new WebClient())
      {
            //download the contents from the URL
            //HttpFreegeoipNetXML contains this url "http://freegeoip.net/xml/{0}"
          var ipAddressXml = objClient.DownloadString(string.Format(HttpFreegeoipNetXML, requestDetails.CallerIp));
            //init an XML Serializer
          var ser = new XmlSerializer(typeof(IpAddressDetails));
            //Deserialize the response string received and cast it to the Class I created to hold the Ip Details data
          ipAddressDetails = (IpAddressDetails)ser.Deserialize(new StringReader(ipAddressXml));
            //set the new ip address in the cache store                    
          _cacheStoreService.Set(cacheKey, ipAddressDetails, TimeSpan.FromDays(20));
      }
  }
  catch (Exception ex)
  {
      _logger.Error("Error getting geo location for ip address: " + requestDetails.CallerIp, ex);
      return new IpAddressDetails();
  }

  return ipAddressDetails;
}

What I  did here is;

I started a webclient and made a request to http://freegeoip.net/xml/{0} with the ip address of the user

I then Deserialzed the string and cast it into the new type I created to hold the location details.

Read the comments I made to understand exactly what the code is doing!!

I hope you find this post useful

<Response>
<IP>00.00.00.00</IP>
<CountryCode>MT</CountryCode>
<CountryName>Malta</CountryName>
<RegionCode>32</RegionCode>
<RegionName>Il-Mosta</RegionName>
<City>Mosta</City>
<ZipCode/>
<TimeZone>Europe/Malta</TimeZone>
<Latitude>35.909</Latitude>
<Longitude>14.426</Longitude>
<MetroCode>0</MetroCode>
</Response>

Adding Active Directory Users via C#–Automated

Recently I needed to add a bunch of users (5000) to Active Directory and I needed to do this in an automated way. I did some research and found this Assembly from Microsoft ‘System.DirectoryServices.AccountManagement.dll’.

You can read more about it here: Account Management Library

What I needed is, I had a list of users in the database which than I needed to add to Active Directory so in the application I will be getting the users from context, iterate through them and add them one by one into Active Directory by using the AccountManagement Library provided by Microsoft

Lets dive into the code that does this automated inserting of Users into Active Directory.

When creating the project we need to add the required references for this Library/Utility

This is a list of constants that I need for the code, you can find the default password which will be stored for every user (its a default than I set the password to expire so that users will have to change it at their first login) the others consts are self explainable.

Than I have the context which contains the Users (A database with users) – its a mock context I did not use the real one here. IGNORE the dispose function

I also needed to create a method to check if the user was already added before, so that I update his/her name if it already exists in Active Directory. This is very handy if you need to update your active directory users accordingly (sync them up with the database).

This is the code, with iteration that adds the users to AD

  • SAM Account Name is the Username that is used to login
  • ExpirePasswordNow method ensures that the default password which was set will have to changed at first user’s login attempt.
  • Name is the Full Name of the User
  • EmailAddress is the Email of the Users NOTE, if the email can be empty you need to make a check else it will throw an error because the email cannot be set as empty. its either NULL or a valid Email address
  • Save function persists the user to AD

Thats all folks!

Removing an auto named Default Constraint DF_TableName_ColumnName MS SQL

ometimes when we create new Columns in tables in MS SQL like for example:

alter table Stock add IsAvailable bit not null default(0)

The last part with default forces MS SQL to create a DF constraint something like this ‘DF_Stock_IsAvailable_03589’

Problem is, at some point when you need to remove this column, to be able to remove it you first need to remove its DF Constraint else it won’t allow you to drop the column, because its being used by that automated constraint.

To remove this constraint what needs to be done is:

SQL

DECLARE @DFConstraintName NVARCHAR(250)
-what this does is it get the constraint for that Table, and Column
SELECT @DFConstraintName = OBJECT_NAME([default_object_id]) FROM SYS.COLUMNS
-in my case the table name is Stock and the Column is IsAvailable
WHERE [object_id] = OBJECT_ID(‘Stock’) AND [name] = ‘IsAvailable’;
-here we drop the constraint by name
EXEC(‘ALTER TABLE Stock DROP CONSTRAINT ‘ + @DFConstraintName)


Uploading files to an SFTP via C#

To upload files via C# to an SFTP first thing you will need is to install these two nuget packages:

public class FTPService
   {
       private string _ftpUsername;
       private string _ftpPassword;
       private string _ftpAddress;
       private string _remotePath;
 
       public FTPService()
       {
           _ftpUsername = WebConfigurationManager.AppSettings["FtpUsername"];
           _ftpPassword = WebConfigurationManager.AppSettings["FtpPassword"];
           _ftpAddress = WebConfigurationManager.AppSettings["FtpAddress"];
           _remotePath = WebConfigurationManager.AppSettings["RemotePath"];
       }
       public string UploadImage(string skinPath, Stream file, string uploadFileName, FTPFolderType folderType)
       {
           var value = string.Empty;
           try
           {
       
               var fileName = Path.GetFileName(uploadFileName);
                
               var remotePath = string.Format(_remotePath, skinPath, folderType.ToString().ToLower());
                
               using (var client = new SftpClient(_ftpAddress, _ftpUsername, _ftpPassword))
               {
                   client.Connect();
 
                   client.ChangeDirectory(remotePath);
                   client.UploadFile(file, fileName);
               }
               value = remotePath;
               return value;
           }
           catch (Exception e)
           {
               Debug.WriteLine("Upload of {0} failed with exception {1}", uploadFileName, e);
               throw e;
           }
       }
 
   }