Using SQL Server for Latitude & Longitude Calculations

If you’re like me, you’ve probably stored latitude and longitude data in decimal columns for a long time. I know SQL Server has a geography type as of a few years ago, but I never really played with it much. Anyway, I finally tried it, and so far it’s great – and no individual decimal columns – and I can do calculations right in SQL.

To get started, use the geography type in a table like this?:

CREATE TABLE [dbo].[MattressStoreLocations](	
[Mattress Store Name] [nvarchar](50) NULL,
[Location] [geography] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Continue reading “Using SQL Server for Latitude & Longitude Calculations”
Using SQL Server for Latitude & Longitude Calculations

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

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

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!

Update:

In an updated article, I discuss increasing import performance the transactionRowCount option.

Importing IIS Logs into a SQL Database / Table