PowerShell For Fun

As an immunologist, I have to deal with a lot of plasmids. In order to share plasmids with my colleagues, I stored all information of plasmids in an SQL server (express edition) running on a Windows XP desktop.  I wrote a small winform program to manage the information stored the SQL server. I have all the data type and program logic (including SQL query) packaged in NET Classes. So if I could reuse those .NET classes in monad, I can do some routine works in MSH scripts and will not waste time to start from scratch.

What is plasmid anyway? Well, plasmid is a circular DNA molecule found in E.coli.  Biologists often use them for gene cloning and expression. If you still don’t know what I am talking about, google (http://www.google.com/search?q=plasmid) for details.

First, let’s make a DLL ready for MSH.
I opened my winform project; got rid of GUI components; recompiled the project to Class Library (PlasmidView.DLL). The DLL contained classes below:

using System;
using System.Data;
using System.Data.SqlClient;

namespace PlasmidView
{
     public class SQLProvider
     {
          // retuen a System.Data.SqlClient.SqlConnection
private static SqlConnection GetDBConnection()     {
               string ConnectionStr = "server=localhost\\SQLExpress;database=PlasmidDB;Integrated Security=SSPI;Trusted_Connection=Yes";
               try {
                    return new SqlConnection(ConnectionStr);}
               catch (SqlException e)     {
                    string SqlMsg = ConstructSqlErrorMessage(e);}
               return null;}

          public static string ConstructSqlErrorMessage(SqlException e)
          {
               string SqlMsg="";
               for (int i=0; i < e.Errors.Count; i++)     {
                    SqlMsg += "Index #" + i + ";" +
                         "Message: " + e.Errors[i].Message + ";" +
                         "LineNumber: " + e.Errors[i].LineNumber + ";" +
                         "Source: " + e.Errors[i].Source + ";" +
                         "Procedure: " + e.Errors[i].Procedure;}
               return SqlMsg;}

          // return a “status” object represent database statitcs
          public static Status GetDBStatus () {
               Status StatusToGet= null;
               SqlConnection myConnection= GetDBConnection();
               try {
                    SqlCommand myCommand = new SqlCommand("dbo.DBStatus", myConnection);
// run a stored procedure “dbo.DBStatus”
                    myCommand.CommandType = CommandType.StoredProcedure;

                    myConnection.Open();
                    SqlDataReader reader = myCommand.ExecuteReader();
                    if (reader.Read()){                    
                         StatusToGet = new Status();
                         StatusToGet.TotalPlasmidNum          =     reader.GetInt32(0);
                         StatusToGet.TotalBackboneNum     =     reader.GetInt32(1);
                         StatusToGet.LastPlasmidID          =     reader.GetInt32(2);
                         StatusToGet.TotalEnzymeNum          =     reader.GetInt32(3);
                         StatusToGet.TotalUserNum          =     reader.GetInt32(4);}
               }
               catch (SqlException e){
                    string SqlMsg = ConstructSqlErrorMessage(e);}
               finally     {
                    myConnection.Close();}
               return StatusToGet;
          }

     }
     public class Status
     {
          int totalPlasmidNum          = 0;
          int totalBackboneNum          = 0;
          int lastPlasmidID          = 0;
          int totalEnzymeNum          = 0;
          int totalUserNum          = 0;

          public int TotalPlasmidNum {
               get {return totalPlasmidNum;}
               set {totalPlasmidNum=value;}
          }

          public int TotalBackboneNum {
               get {return totalBackboneNum;}
               set {totalBackboneNum=value;}
          }

          public int LastPlasmidID {
               get {return lastPlasmidID;}
               set {lastPlasmidID=value;}
          }

          public int TotalEnzymeNum {
               get {return totalEnzymeNum;}
               set {totalEnzymeNum=value;}
          }

          public int TotalUserNum     {
               get {return totalUserNum;}
               set {totalUserNum=value;}
          }
          public Status()     {}
}

Second, let’s load the PlasmidView.DLL to MSH

[void][System.Reflection.Assembly]::LoadFile("D:\MSH\PlasmidView.dll")

Third, Let’s create a instance of  “Status” object:

$Status = new-object PlasmidView.Status
$Status |gm

    TypeName: PlasmidView.Status

Name                 MemberType Definition
----                 ---------- ----------
Equals               Method     System.Boolean Equals(Object obj)
get_LastPlasmidID    Method     System.Int32 get_LastPlasmidID()
get_TotalBackboneNum Method     System.Int32 get_TotalBackboneNum()
get_TotalEnzymeNum   Method     System.Int32 get_TotalEnzymeNum()
get_TotalPlasmidNum  Method     System.Int32 get_TotalPlasmidNum()
get_TotalUserNum     Method     System.Int32 get_TotalUserNum()
GetHashCode          Method     System.Int32 GetHashCode()
GetType              Method     System.Type GetType()
set_LastPlasmidID    Method     System.Void set_LastPlasmidID(Int32 value)
set_TotalBackboneNum Method     System.Void set_TotalBackboneNum(Int32 value)
set_TotalEnzymeNum   Method     System.Void set_TotalEnzymeNum(Int32 value)
set_TotalPlasmidNum  Method     System.Void set_TotalPlasmidNum(Int32 value)
set_TotalUserNum     Method     System.Void set_TotalUserNum(Int32 value)
ToString             Method     System.String ToString()
LastPlasmidID        Property   System.Int32 LastPlasmidID {get;set;}
TotalBackboneNum     Property   System.Int32 TotalBackboneNum {get;set;}
TotalEnzymeNum       Property   System.Int32 TotalEnzymeNum {get;set;}
TotalPlasmidNum      Property   System.Int32 TotalPlasmidNum {get;set;}
TotalUserNum         Property   System.Int32 TotalUserNum {get;set;}

Now, let’s try the GetDBStatus () method

[PlasmidView.SQLProvider]::GetDBStatus()

TotalPlasmidNum  : 21
TotalBackboneNum : 8
LastPlasmidID    : 20
TotalEnzymeNum   : 103
TotalUserNum     : 8


There two things I want to emphases:

1. The original design requires a username and password for database access.   I used Windows NT authentication for SQL server connection, and PlasmidView.SQLProvider class expose all SQL server operation as “static” methods, so the security check has been bypassed.  To regain access control over different user, I have to change C# code to check user credential every time before a SQL server operation.

2. There is really no big difference when using a .Net class in C# or monad. What you need to do is to load the DLL and create the object by new-object cmdlet. Because monad automatically convert “public” class properties to String, quoting a object under MSH prompt will get formatted list of object public properties.  

Tags: msh    monad