MongoDB – Linking Records / Documents Using MongoDBRef

If you’re using MongoDB, you know as a document database it doesn’t provide the “join” feature you’ve come to rely on in standard relation databases (sql server, mySql, postgres). In many instances, you’re not using Mongo as a replacement for your existing data structure / methods – but it would be nice to relate some documents (without embedding everything [potentially duplicating data] into a single document.

I am a big fan of MongoDB and the .net / c# driver. Below is a method I’ve found that works well for relating different documents using MongoDBRef. In my scenario – which I’m sure many have better methods – I’m using a property to keep a list of related documents – then a method to retrieve the related documents if needed.

I’m my below example, I’m creating trains, train cars, and passengers – then relating them. In short: I’m using IList<MongoDBRef> to store the list, and FetchDBRefAs to get the documents again.

This works really well and is flexible. I’ve tested this against many records and have found that if you’re looking to get thousands of records with their related thousands of records – you won’t experience the same performance as a standard relational db join. But for a few records (hundreds in my testing) it’s fast and efficient. I’m also using .AsParallel() on the query – this gained me about 10%+ performance.

One note: if you’re allowing Mongo to generated the document id on insertion, queuing up records in a bulk operation (ie: InitializeOrderedBulkOperation) you’ll soon realize there isn’t an id yet to use for the MongoDBRef.

Comment if you’ve found a better method you prefer for linking documents.

My example:

using MongoDB.Bson;
using MongoDB.Driver;
using MongoDB.Driver.Linq;
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;

namespace mongoDBlinked
{
    public class passenger
    {
        public ObjectId Id { get; set; }

        public string Name { get; set; }
    }

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

        public string carNo { get; set; }

        public string serial { get; set; }

        public string note { get; set; }

        public IList<MongoDBRef> passengers { get; set; }

        public IList<passenger> GetPassengers(MongoDatabase db)
        {
            if (passengers.Count == 0)
                return new List<passenger>();

            IList<passenger> tpass = new List<passenger>();
            foreach (var related in passengers)
            {
                tpass.Add(db.FetchDBRefAs<passenger>(related));
            }

            return tpass;
        }
    }

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

        public string trainNo { get; set; }

        public string note { get; set; }

        public IList<MongoDBRef> trainCars { get; set; }

        public IList<trainCar> relTrainCards { get; set; }

        public IList<trainCar> GetTraincars(MongoDatabase db)
        {
            if (trainCars.Count == 0)
            {
                return new List<trainCar>();
            }

            IList<trainCar> tcars = new List<trainCar>();
            foreach (var related in trainCars)
            {
                tcars.Add(db.FetchDBRefAs<trainCar>(related));
            }

            return tcars;
        }
    }

    internal class Program
    {
        private static void Main(string[] args)
        {
            
            var server = new MongoClient("mongodb://localhost").GetServer();
            var database = server.GetDatabase("testmongodb");

            var collection = database.GetCollection<trainCar>("trainCars");
            var trainCollection = database.GetCollection<train>("trains");
            var peopleCollection = database.GetCollection<passenger>("passengers");
            

            //add some data
            for (int t = 0; t < 20; t++)
            {
                train newTrain = new train();
                newTrain.note = "nyc" + t.ToString();
                newTrain.trainNo = "345";
                newTrain.trainCars = new List<MongoDBRef>();

               

                //add some cars:

                for (int i = 0; i < 50; i++)
                {
                    trainCar tcar = new trainCar();

                    tcar.carNo = "0" + i.ToString();
                    tcar.note = "Needs new brakes";
                    tcar.serial = "1234";
                    tcar.passengers = new List<MongoDBRef>();
                    for (int p = 0; p < 3; p++)
                    {
                        passenger pass = new passenger();
                        pass.Name = "name" + p.ToString();
                        peopleCollection.Insert(pass);
                        tcar.passengers.Add(new MongoDBRef(peopleCollection.Name, pass.Id));
                    }

                    collection.Insert(tcar);
                 
                    newTrain.trainCars.Add(new MongoDBRef(collection.Name, tcar.Id));
                }

                

                trainCollection.Insert(newTrain);
            }
            Console.WriteLine("saved");
           

            Stopwatch stopWatch = new Stopwatch();
            stopWatch.Start();

            //get the data
            //asparallel gained me about 15% of qry time
            var query = from v in trainCollection.AsQueryable<train>().AsParallel()
                        select v;

            foreach (train aTrain in query)
            {
                Console.WriteLine("train: " + aTrain.note);
                foreach (trainCar tcar2 in aTrain.GetTraincars(database))
                {
                    if (tcar2 != null)
                    {
                        Console.WriteLine("car: " + tcar2.carNo);
                        foreach (passenger pass in tcar2.GetPassengers(database))
                        {
                            Console.WriteLine("car: " + tcar2.carNo + " pass: " + pass.Name);
                        }
                    }
                }

               
            }
            Console.WriteLine("trains loaded");

            stopWatch.Stop();           
            TimeSpan ts = stopWatch.Elapsed;

            
            string elapsedTime = String.Format("{0:00}:{1:00}:{2:00}.{3:00}",
                ts.Hours, ts.Minutes, ts.Seconds,
                ts.Milliseconds / 10);
            Console.WriteLine("RunTime " + elapsedTime);

            

            Console.ReadLine();
        }
    }
}
MongoDB – Linking Records / Documents Using MongoDBRef

Removing WordPress Spam Comments In Bulk

I’ve encountered a few WordPress blogs that had been bombarded with spam comments (some over 200k comments – taking several gigs of db space). Usually these comments accumulated over a few months or years (many using Akismet) – but if you’ve tried to delete thousands of comments through the WordPress admin, you might have noticed it taking very long and timing out.

Does your comments page look like this?
Does your comments page look like this?

 

Having these comments exist in your WordPress site only increases the size of the database, causing backups and migrations / upgrades to take longer. Unless you have plans to review thousands of comments (if so, you probably have too much time on your hands) you should be able to delete these in my opinion. The fastest option to remove the comments that I’ve found is to delete them from the database side (MySql).

Continue reading “Removing WordPress Spam Comments In Bulk”

Removing WordPress Spam Comments In Bulk

SQL Server / Renaming Databases With Active Connections

hello-my-name-isI believe there are many methods and opinions that exist on the best practice to rename a SQL Server DB. Below I’ll quickly give you the method I use to rename a database. In the below example, I rename two DB’s – a common scenario if you’re swapping your production database with a different version:

-- #1 change first db name
use master
-- set db to single user
ALTER DATABASE myProdDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE 
-- do the rename
ALTER DATABASE myProdDB MODIFY NAME = [myOldProdDB]
-- set back to multi user
ALTER DATABASE myOldProdDB SET MULTI_USER

-- #2 change second db name
use master
-- set db to single user
ALTER DATABASE myNewProdDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE 
-- do the rename
ALTER DATABASE myNewProdDB MODIFY NAME = [myProdDB]
-- set back to multi user
ALTER DATABASE myProdDB SET MULTI_USER

Note: this does not change the database or log file names, just the database name.

SQL Server / Renaming Databases With Active Connections

RavenDB Document Database – Super Quickstart Guide

Raven-info0RavenDB is among the many available document databases on the market today (mongo, redis, etc.). If you’re looking to try this db and see if it’s right for your project, below is a super quick guide to start using using RavenDB on Windows with asp.net. Below are my 10 simple steps to start using. You can skip to the bottom for entire “source” code.  Continue reading “RavenDB Document Database – Super Quickstart Guide”

RavenDB Document Database – Super Quickstart Guide

c# DataTable Copy / Filter / Sort & Clone

datatableIf you’re like me (I hope not too much like me) you probably find yourself using DataTables to hold lots of data for fast, flexible in memory usage. I get asked often, “how can I copy a DataTable?” or even more often, “how can I copy a DataTable, but change the sort or modify the rows”. Look no further. Below you’ll find my thoughts:

 

Example one (a simple copy with a sort difference):

    //datatable
    var table = new DataTable();

    //get some data
    using (var conn = new SqlConnection(yourSqlConn))
        {
            var comm = new SqlCommand(@"select * from someTable order by someColumn", conn);
            comm.CommandType = CommandType.Text;
            conn.Open();
            var data = comm.ExecuteReader();
            table.Load(data);
        }

    //bind to some control (repeater)
    rptFirstList.DataSource = table;
    rptFirstList.DataBind();

    //second table
    var secondTable = new DataTable();
    secondTable = table.Copy();
    secondTable.DefaultView.Sort = "someOtherColumn";

    //bind second
    rptSecondList.DataSource = secondTable;
    rptSecondList.DataBind();

Example two (creating a copy of the table but allowing a filter, sort or other criteria):

    //datatable
    var table = new DataTable();

    //get some data
    using (var conn = new SqlConnection(yourSqlConn))
        {
            var comm = new SqlCommand(@"select * from someTable order by someColumn", conn);
            comm.CommandType = CommandType.Text;
            conn.Open();
            var data = comm.ExecuteReader();
            table.Load(data);
        }

    //bind to some control (repeater)
    rptFirstList.DataSource = table;
    rptFirstList.DataBind();

    //second table
    var secondTable = new DataTable();

    //clone to get columns - NO data is copied
    secondTable = table.Clone();
    //loop through rows and import based on filter
    foreach (DataRow dr in table.Select("someColumn = 'value'","someColumnToSort")) {

    secondTable.ImportRow(dr);

    }

    //bind second
    rptSecondList.DataSource = secondTable;
    rptSecondList.DataBind();
c# DataTable Copy / Filter / Sort & Clone

MVC 4: Code First Migrations with SQL Server – Creating & Updating the DB Schema

Creating and keeping a database schema up to date using asp.Net & MVC 4 in Visual Studio (2010 in the below process) is quite easy and efficient.

Below are some basic steps to get you going.

Step 1. Create a local or remote SQL Server db (I’m using SQL Server 2008 Developer – locally) using SQL Management Studio. Don’t create any tables, etc., just the database.

Step 2. Update the web.config file in your root folder with a connection string for the database you just created. Below is mine:

 <connectionStrings>
    <add name="sampleDBContext" 
connectionString="Data Source=.\;Initial Catalog=sampleDB;Integrated Security=SSPI" 
providerName="System.Data.SqlClient" />
  </connectionStrings>

It’s important to have the connection string name match the name of the class of your DbSet, below is my dbcontext class:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Entity;

namespace SampleApp.Models
{
    public class sampleDBContext : DbContext
    {
        public DbSet<simpleTable> simpleTable{ get; set; }

    }
}

Step 3. In the Package Manager Console (in Visual Studio), run this command to enable migrations (at the “PM>” prompt):

Enable-Migrations -ContextTypeName SampleApp.Models.sampleDBContext

You should get this result:

Code First Migrations enabled for project SampleApp.

Step 4. At this point, the database hasn’t been updated. To update and create your tables, let’s first create a migration. In the Package Manager Console, run this command to create a migration (at the “PM>” prompt):

Add-Migration InitialMig

(If you were to open the .cs file just created in your migrations folder, you’ll see the create / update commands it will run.)

Step 5. Finally, to actually update the database, run:

Update-Database

(at the “PM>” prompt). You’ll see some messages like:

Applying code-based migrations: [201209122019596_InitialMig].
Applying code-based migration: 201209122019596_InitialMig.
Running Seed method.

Now if you look in at your database in SQL Management Studio, you’ll see your changes!

Going forward: go ahead and make changes to your models, you’ll just want to run the “Add-Migration” command, with a new migration name. ie: Add-Migration Mig2 and then run Update-Database to apply the changes. Simple!

MVC 4: Code First Migrations with SQL Server – Creating & Updating the DB Schema

Importing IIS Logs into a SQL Database / Table

1. Download the Log Parser tool from Microsoft here. I know, it’s old but works great.

2. Dump your IIS log files somewhere (ie: c:\temp\logs).

3. Run this in cmd:

C:\Program Files (x86)\Log Parser 2.2>logparser “SELECT * INTO iisLogs FROM c:\temp\logs\*.log ” -i:iisw3c -o:SQL -server:localhost -database:webLogs -username:sa -password:yourpass -createTable: ON

if you’re on 32bit, run Log Parser will be in this folder:

C:\Program Files\Log Parser 2.2>logparser “SELECT * INTO iisLogs FROM c:\temp\logs\*.log ” -i:iisw3c -o:SQL -server:localhost -database:webLogs -username:sa -password:yourpass -createTable: ON

Now you have a table w/ tons of data!

Importing IIS Logs into a SQL Database / Table