Archive for category Windows

Powershell to write bunch of data from sql to the file system

I recently had need to write a bunch of xml we had stored in sql out to a bunch of files. So we have one xml file per row, and I wanted to out put these into a folder, each one in it’s own file. After some Googling I got this working;

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = “Data Source=localhost;Initial Catalog=testDB;User Id=sa;Password=thepassword;Persist Security Info=True”
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = “select top 500 id, [xml] from xmlLog order by id desc”
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
for ($i=0;$i -le $DataSet.Tables[0].Rows.Count – 1;$i++) { $DataSet.Tables[0].Rows[$i][1] >> (“c:\xml\file” + $i + “.xml”) }

This gave me a folder called xml in my c:\ containing 500 xml files!

No Comments

Executing long running process from a web site

I recently had to build a report which accepts one xls excel file, churns through a bunch the data looking stuff up on other systems and performing calculations, then spits out another xls excel sheet the other end. The whole process takes about 15 to 20 minutes to run and at present is a simple command line application which accepts a couple of parameters (input and output filenames) and I fire up manually- obviously a situation which is no good moving forward.

All our internal business systems are web forms/ mvc running from local IIS boxes so ideally I would like to add a screen where the user can submit their xls file, hit go, and then get the result back 15/ 20 minutes later- but I don’t want to tie up one of the threads in the aspnet worker thread pool for the entire duration. Some Googling revealed the best approach appears to be to write a windows service which hosts the long running process then get the web app to call across to that to kick off the task.

Implementation

This is to be a standard Windows Service which will run my long running process, hosting a WCF service which the website can use to call into it with. If you;ve not done this before, you can follow the steps on the MSDN ; msdn.microsoft.com/en-us/library/ms733069.aspx.

Create your WCF Service which will respond to requests from the website. In my instance I wanted to be able to start the operation, passing a byte[] containing the xls file, request status updates, then finally request the resulting xls file, again as a byte[]. For the sake of this example, the WCF service is the one which is doing all the work with regards spawning the worker thread and tracking updates from the actual worker class, and the worker class is a type called “Calculator”.

    using System.ServiceModel;
    [ServiceContract(Namespace = "http://Moneybarn.VIVS.FleetRevaluation")]
    public interface IMyWCFService
    {
        [OperationContract]
        string Start(byte[] excel_file, string user_name);

        [OperationContract]
        Progress GetProgress();

        [OperationContract]
        byte[] GetResult();
    }

    using System.Threading;
    public class MyWCFService: IMyWCFService
    {
        private static string CurrentUserInstance;
        private static Calculator MyCalculatorInstance;
        private static Progress LastUpdate;
        private static Thread WorkerThread;
        private static bool IsComplete;
        private static string LastError;
        private static byte[] FinishedFile;

        public string Start(byte[] excel_file, string user_name)
        {
            if (WorkerThread == null)
            {
                CurrentUserInstance = user_name;
                MyCalculatorInstance = new Calculator(excel_file);

                MyCalculatorInstance.OnProgress += new OnProgressEventHandler(MyCalculatorInstance_OnProgress);
                MyCalculatorInstance.OnComplete += new OnCompleteEventHandler(MyCalculatorInstance_OnComplete);
                MyCalculatorInstance.OnError += new OnErrorEventHandler(MyCalculatorInstance_OnError);

                WorkerThread = new Thread(new ThreadStart(MyCalculatorInstance.Start));
                WorkerThread.Start();

                return null; // no news is good news!
            }
            else
            {
                return "Instance already running for user " + CurrentUserInstance;
            }
        }

        public Progress GetProgress()
        {
            return LastUpdate;
        }

        public string GetError()
        {
            return LastError;
        }

        public byte[] GetResult()
        {
            if (IsComplete)
            {
                byte[] buff = FinishedFile;

                MyCalculatorInstance = null;
                WorkerThread = null;
                CurrentUserInstance = string.Empty;
                LastUpdate = null;
                FinishedFile = null;
                IsComplete = false;

                return buff;
            }
            else
                return null;
        }

        private void MyCalculatorInstance_OnError(string error)
        {
            LastError = error;
        }

        private void MyCalculatorInstance_OnComplete(byte[] the_file)
        {
            FinishedFile = the_file;
            IsComplete = true;
        }

        private void MyCalculatorInstance_OnProgress(int stage, float percent)
        {
            LastUpdate = new Progress() { 
                Stage = stage, 
                Percent = (int)(percent * 100) 
            };
        }
    }

Add a new class called ProjectInstaller- this will handle registering your service with Windows

    using System.ComponentModel;
    using System.Configuration.Install;
    using System.ServiceProcess;

    [RunInstaller(true)]
    public class ProjectInstaller : Installer
    {
        private ServiceProcessInstaller process;
        private ServiceInstaller service;

        public ProjectInstaller()
        {
            process = new ServiceProcessInstaller();
            process.Account = ServiceAccount.LocalSystem;
            service = new ServiceInstaller();
            service.ServiceName = "MyWindowsService";  // change this!
            Installers.Add(process);
            Installers.Add(service);
        }
    }

Add the actual service body; add a new “Windows Service” file to the project. This is the meat of the service, and contains no real logic- it’s only job is to fire up the ServiceHost for your WCF service.

    using System.ServiceModel;
    using System.ServiceProcess;

    public class MyWindowsService : ServiceBase
    {
        public ServiceHost serviceHost = null;

        public MyWindowsService()
        {
            // Name the Windows Service
            ServiceName = "MyWindowsService";
        }

        public static void Main()
        {
            ServiceBase.Run(new MyWindowsService());
        }

        // Start the Windows service.
        protected override void OnStart(string[] args)
        {
            if (serviceHost != null)
            {
                serviceHost.Close();
            }

            // The type of your WCF service
            serviceHost = new ServiceHost(typeof(MyWCFService));

            serviceHost.Open();
        }

        protected override void OnStop()
        {
            if (serviceHost != null)
            {
                serviceHost.Close();
                serviceHost = null;
            }
        }
    }

Finally there is a little bit of config to pop into the app.config for the WCF endpoints- I’ve created a custom binding here to allow me to receive larger files over the WCF call than the default limits allow (in this case, up to 10 meg);

  <system.serviceModel>
    <bindings>
      <wsHttpBinding>
        <binding name="bigWSHttpBinding" 
                 maxBufferPoolSize="10485760" maxReceivedMessageSize="10485760"> <!-- 10 MB limit -->
          <readerQuotas maxDepth="10485760" maxStringContentLength="10485760" maxArrayLength="10485760" maxBytesPerRead="10485760" />
        </binding>
      </wsHttpBinding>
    </bindings>
    <services>
      <service behaviorConfiguration="MyWCFServiceBehavior"
        name="Demo.MyWCFService">
        <endpoint address="" binding="wsHttpBinding" bindingConfiguration="bigWSHttpBinding"
          contract="Demo.IMyWCFService" />
        <endpoint address="mex" binding="mexHttpBinding" contract="IMetadataExchange" />
        <host>
          <baseAddresses>
            <add baseAddress="http://localhost:8000/MyWCFService/service" />
          </baseAddresses>
        </host>
      </service>
    </services>
    <behaviors>
      <serviceBehaviors>
        <behavior name="MyWCFServiceBehavior">
          <serviceMetadata httpGetEnabled="true"/>
          <serviceDebug includeExceptionDetailInFaults="true"/>
        </behavior>
      </serviceBehaviors>
    </behaviors>
  </system.serviceModel>

I also created a couple of little batch files which I mark as “Copy to output directory” for installing and removing the service;

rem install.bat
c:\windows\microsoft.net\Framework\v4.0.30319\InstallUtil MyWindowsService.exe
net start MyWindowsService
rem remove.bat
net stop MyWindowsService
c:\windows\microsoft.net\Framework\v4.0.30319\InstallUtil MyWindowsService.exe /u

That’s the Windows service part built. You can now build, and jump to the bin output folder and run the install.bat file from the command line- with any luck (more likly with a little debugging!) you will be up and running. The second part is even easier;

Jump over to your web app, add a service reference to the url of the base address from your app config above then it’s just a matter of exposing those web service methods so that you can call them from your page with an ajax call- so in MVC I created a controller with Start, GetProgress & GetResult methods which returned JsonResults called straight from the client with jQuery.

2 Comments

How to fix CD / DVD Drive not showing up in Windows 8

Just installed Windows 8… which is a LOT faster than Windows 7 at pretty much everything- but I’m still not sold on the crazy new UI and there are a few things which seem quite mental. Noticed yesterday that, despite having installed from a DVD, my DVD drives just disappeared! Lucky full-windows8.com had blogged how to fix this with a weird registry hack;

They recommend you run this from the admin command prompt;

reg.exe add “HKLM\System\CurrentControlSet\Services\atapi\Controller0″ /f /v EnumDevice1 /t REG_DWORD /d 0×00000001

Repeat for additional drives, incrementing the very last number each time. Worked for me. Mental.

No Comments

Grabbing data from the registry on a remote machine using dot net and WMI

I recently had to write an app which, given a computer name, would grab their telephone extension from a registry key (For the purpose of the code example below, I just grab the CommonFilesDir key from the windows node. There’s a couple of ways you can achieve this- either using RegistryKey.OpenRemoteBaseKey which gave me a whole bunch of permissions issues, plus you need the remote registry and remote administration services active on the server/client OR you can use the Windows Management Instrumentation (WMI) service. I took this route, as it’s a service which is active by default on all of our machines on the domain, and it was easier to get the permissions right.

Before I paste the code, a GOTCHA to beware of, straight from the MSDN here;

The registry provider is hosted in LocalService—not the LocalSystem. Therefore, obtaining information remotely from the subtree HKEY_CURRENT_USER is not possible

ConnectionOptions options = new ConnectionOptions();
options.Impersonation = ImpersonationLevel.Impersonate;
options.EnablePrivileges = true;
options.Username = "<domain_admin_username>";
options.Password = "<domain_admin_password>";

// http://msdn.microsoft.com/en-us/library/system.management.managementscope.aspx
ManagementScope ms = new ManagementScope("\\\\<computer name>\\root\\default", options);          
   
ms.Connect();

// http://msdn.microsoft.com/en-us/library/windows/desktop/aa390788(v=vs.85).aspx
ManagementClass mc = new ManagementClass("stdRegProv");
mc.Scope = ms;

ManagementBaseObject inParams = mc.GetMethodParameters("GetStringValue"); 
// there are other methods for grabbing other reg types- see 
// http://msdn.microsoft.com/en-us/library/windows/desktop/aa393664(v=VS.85).aspx

inParams["hDefKey"] = RegHive.HKEY_LOCAL_MACHINE;
inParams["sSubKeyName"] = "SOFTWARE\\Microsoft\\Windows\\CurrentVersion";
inParams["sValueName"] = "CommonFilesDir";

ManagementBaseObject outParams =
        mc.InvokeMethod("GetStringValue", inParams, null);

if (outParams["ReturnValue"].ToString() == "0")
{
    Console.WriteLine(outParams["sValue"]);
}
else
{
    Console.WriteLine("Error " + outParams["ReturnValue"] + " please refer to http://msdn.microsoft.com/en-us/library/ms681382%28v=3Dvs.85%29.aspx");
}

Console.ReadLine();

This code requires this enum..

public enum RegHive : uint
{
  HKEY_CLASSES_ROOT = 0x80000000,
  HKEY_CURRENT_USER = 0x80000001,
  HKEY_LOCAL_MACHINE = 0x80000002,
  HKEY_USERS = 0x80000003,
  HKEY_CURRENT_CONFIG = 0x80000005
}

If you receive a COM error back “The RPC server is unavailable”, make sure the target machine’s firewall isn’t blocking the call- this will open the port;
netsh firewall set service RemoteAdmin

No Comments

Open Windows Firewall ports for SQL Server 2008

http://support.microsoft.com/kb/968872

As noted in the article, you can run this batch file;

@echo =========  SQL Server Ports  ===================
@echo Enabling SQLServer default instance port 1433
netsh firewall set portopening TCP 1433 "SQLServer" 
@echo Enabling Dedicated Admin Connection port 1434
netsh firewall set portopening TCP 1434 "SQL Admin Connection" 
@echo Enabling conventional SQL Server Service Broker port 4022  
netsh firewall set portopening TCP 4022 "SQL Service Broker" 
@echo Enabling Transact-SQL Debugger/RPC port 135 
netsh firewall set portopening TCP 135 "SQL Debugger/RPC" 
@echo =========  Analysis Services Ports  ==============
@echo Enabling SSAS Default Instance port 2383
netsh firewall set portopening TCP 2383 "Analysis Services" 
@echo Enabling SQL Server Browser Service port 2382
netsh firewall set portopening TCP 2382 "SQL Browser" 
@echo =========  Misc Applications  ==============
@echo Enabling HTTP port 80 
netsh firewall set portopening TCP 80 "HTTP" 
@echo Enabling SSL port 443
netsh firewall set portopening TCP 443 "SSL" 
@echo Enabling port for SQL Server Browser Service's 'Browse' Button
netsh firewall set portopening UDP 1434 "SQL Browser" 
@echo Allowing multicast broadcast response on UDP (Browser Service Enumerations OK)
netsh firewall set multicastbroadcastresponse ENABLE 

No Comments

Command Line- create a folder named with todays date

mkdir %date:~-4,4%%date:~-7,2%%date:~0,2%
will produce a folder named like this; YYYYMMDD

Thanks to Mr Carmichael for this..

No Comments

Automatic NAS Box shutdown – How to execute applications on Windows Shutdown (Part 2/2)

In Part 1 we looked at how to figure out what message was being sent from the web management page of my Netgear ReadyNAS Duo network storage box, and then how to replicate that to happy from a c# dot net console application. To finish this off, we now need some way of making that new console app run automatically when I shut down my Windows 7 PC.

I started in the Task Scheduler panel in Control Panel > Administrative Tools.

On the right hand panel you can then select “Create Task…”. From this dialogue you have a number of tabs along the top- the second one along, right after General is “Triggers” – switch to this and then hit the “New…” button which brings up a third window called “New Trigger”.  From the drop down list entitled “Begin the task:” check the selected option from the default “On a schedule” to “On an event”.  So you should now see something similar to this screen shot;

At this point I was kind of stumped- The other options in that “Begin the task:” list were things like “At log on”, “At startup”, “On work station lock” etc – there was no “On Shutdown”!! So I selected “On an event”. This gives you three new options in Settings – Log, Source and Event. From here it was down to taking an educated guess. I knew that shutting down Windows was likly to be logged as a “System” event, so in the first drop down I selected System.

What to choose in the Source drop down was not so clear- so to decide on an event source to use, I opened the Event Viewer, again from Control Panel > Administrative Tools. From the left hand panel, I opened the “Windows Logs” branch, then from within that selected “System”.  These are arranged by time, so it’s just a matter of going back to when you turned your computer on today, then going back a couple more to see the last things your computer logged before turning off when you last used it.

In the end I opted for the event shown in the above screen shot- by reading the description I noticed it mentioned shutting down explorer.exe (a main component in Windows) because of the “power off of computer”, which I figured, with out knowing the in’s and out’s of what this really meant, sounded pretty promising. So from the same window I noted the Source “USER32″ and Event ID “1074″.  Going back to the New Trigger window I found that USER32 did indeed appear in the list, so I selected that and entered 1074 as the event ID.  This is pretty much everything for the trigger, so hit ok, then move on to the Actions tab, and click “New…”

From here it’s pretty easy- the action we want is to “Start a program” then just use the browser button to find the program you want to run. Click OK and return to the General tab.

This final step may not be required, but I did it anyway just to be sure- on the general tab, I changed the radio button at the end and selected “Run whether user is logged on or not”. I also changed the “Configure for:” drom down at the bottom to be “Windows 7, Windows Server 2008 R2″. And that’s it- Shut down your computer to give it a go. If you are following on from my previous post and you pointed the action dialogue to run the .exe produced from the last post, you should see your computer shut down and then a few seconds later the NAS box will power down.

Drop me a comment if I’ve missed any critical details!

You can see the finished app here

No Comments

Ninite Easy PC Setup – Silent Unattended Install Multiple Programs At Once

Nige at work pointed me over to an awesome site for creating a one stop setup for installing all your useful apps in one go after a format- just tick the apps you want and download the installer.

Ninite Easy PC Setup – Silent Unattended Install Multiple Programs At Once.

No Comments

Introducing the Microsoft Web Farm Framework – ScottGu’s Blog

Introducing the Microsoft Web Farm Framework – ScottGu’s Blog.

No Comments

Command line to delete every file older than 30 days

I found a new command line directive i’d never heard of today, along with a brilliant example of it’s use- once you have changed your working directory, running this will remove any files or folders older than 30 days;

FORFILES /D -30 /C "CMD /C IF @isdir==FALSE (echo Deleting File @file) & (ATTRIB -H @file) & (DEL /F /Q @file) ELSE (echo Deleting Directory @file) & (ATTRIB -H @FILE) & (RD /S /Q @FILE)"

As an example, I’ve since used this to iterate over a bunch of folders, deleting any files over 30 days;

d:
for /f "delims=|" %%f in ('dir /b D:\_SQL_Backup\') DO (
  cd \_SQL_Backup
  cd %%f
  FORFILES /D -14 /C "CMD /C IF @isdir==FALSE (echo Deleting File @file) & (ATTRIB -H @file) & (DEL /F /Q @file)"
)

No Comments