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!

Update:

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

Importing IIS Logs into a SQL Database / Table