Category Archives: C#

Windows Services in C#: Pulling in the Event Log for your Windows Service (part 8)

Technically this falls in line closer with the EventLog series I did in January. (For more info see my series on event logging, it started on January 16 of this year and ran to the 19th, http://shrinkster.com/p6d). However, in most cases the Event Log is tied in closely with Windows Services since it’s through the event log that services do most of their communication with the outside world. As such it can be important to read in your events back into your control app.

To read the log we’ll use two different methods, just to show multiple techniques. To start, let’s add a text box to the TLManager, under the buttons. I named mine txtEventLog. Next, in the component area of the toolbox find the EventLog component and drop it on the form. Like the timer, it goes in the tray area under the form. I named mine TimerEventLog.

In the properties for the TimeEventLog, there’s a few properties we must set. First, set the Log property to “ArcaneTimeLogger” and the Source to “ArcaneTimeLoggerService”, so it will know to read our log.

Next set EnableRaisingEvents to true. This will allow the EventLog component to raise an event whenever something gets written to our log. Since we set this to true, we must indicate what method is responsible for the event. Click on the Event button (the lightening bolt) in the properties window to switch to events view, then double click the EventWritten to generate a default named event. It will generate TimeEventLog_EntryWritten.

Now switch to code view, and add this code to the event:

    private void TimeEventLog_EntryWritten(object sender, System.Diagnostics.EntryWrittenEventArgs e)

    {

      txtEventLog.Text = e.Entry.Message

        + System.Environment.NewLine

        + txtEventLog.Text;

    }

This will take the latest event log entry, and add it to the text box. Note I put it first, then add back what was already in the text box. This way the most recent event will always be at the top.

That’s it for this part, now everytime something is written to the event log, the EventLog component will catch the event and let us know about it.

Nice, but sometimes you also want to know what’s already there in the log. Instead of using a component let’s see how to do this with code. First, add a “using System.Diagnostics” reference to the header of your class (form).

Now, I think it would look nice when the TLManager loads to already have the text box populated with the past events. The System.Diagnostics library contains an EventLog class we can use to create an object for our particular event log. Once that’s done, we can cycle through the Entries collection to read what’s there. Here’s some code I added to the TLManger_Load event to do just that.

      StringBuilder sb = new StringBuilder();

      EventLog atl = new EventLog(“ArcaneTimeLogger”);

      for (int i = atl.Entries.Count – 1; i > -1; i–)

      {

        sb.AppendLine(atl.Entries[i].Message);

      }

      txtEventLog.Text = sb.ToString();

I start by creating a string builder object to hold all of the events. Next, and the key is the EventLog atl… line. I pass in the name of our log so the atl object will know what log it belongs to.

Next you will see a for loop that cycles through the entries. Note I’m deliberately starting with the last entry, the most recent one, and counting down to the oldest one. This will ensure the most recent event appears first in the text box.

Finally, I copy the data we’ve built into the string builder into the text box. And that’s it, two ways to monitor event logs and pull their data into your application.

Windows Services in C#: Sending Commands to your Windows Service (part 7)

Yesterday we looked at starting and stopping your windows service from another application. It would also be helpful to be able to send other commands beyond the basic set of Start/Stop/Pause etc.

On the good side, there is an API through which it is possible to send a command to your windows service, fairly easily as a matter of fact. The down side is it’s a one way communication, through the built in mechanism it’s not possible to return any data. Even more limiting is the only data you are allowed to pass in is an integer, and it’s value must be between 128 and 255.

Even with those limitations, if all you need to do is execute a few simple commands the built in API can be more than enough to meet your needs.

To illustrate, we’ll expand the windows service we’ve been using as an example. We’ll add the ability to send a command that will force the event log to be updated immediately, rather than waiting on the timer to fire off its event.

First, we’ll add two items to the service. The first is a public enum. Strictly speaking we don’t have to use an enum, but it makes for more readability.

    // Must be int between 128 and 255

    public enum commands

    {

      LogIt = 255

    }

Next we’ll add a new method to the windows service called OnCustomCommand. This is an override to the base classes method. As you can see it first calls the base method, then checks the value of the integer that was passed in, in this case against our enum. If it finds a match, it calls the WriteToLog method immediately. (The WriteToLog was discussed yesterday, so I won’t reiterate here).

    protected override void OnCustomCommand(int command)

    {

      base.OnCustomCommand(command);

      if (command == (int)commands.LogIt)

      {

        WriteToLog(“Arcane LogIt:”);       

      }

    }

OK, that’s all that’s needed for the service. Let’s switch to our program, and add another button called LogIt.

[Pic of TLManager]

Now we’ll add a little code to the click event for the log it button.

    private void btnLogIt_Click(object sender, EventArgs e)

    {

      ServiceController sc = new ServiceController(“ArcaneTimeLogging”);

      sc.ExecuteCommand(255);

    }

Like in our other examples we create a reference to our service by creating a new ServiceController object and passing in the name of our service. Once we have an sc object we call the ExecuteCommand method. This allows us to send messages to a windows service.

Numbers 0-127 are reserved for windows, and are handled in the base class. 128 to 255 are there for your own use. In the example above I used 255 just to show that you could pass an integer value directly without using an enum.

One last small item, we don’t want the LogIt button to be enabled if our service isn’t running. We’ll add a little logic to the SetDisplay, Start and Stop methods to include setting the buttons enabled status properly. Here’s the updated routines.

    private void SetDisplay(ServiceController sc)

    {

      sc.Refresh();

      if (sc.Status == ServiceControllerStatus.Stopped)

      {

        btnStop.Enabled = false;

        btnStart.Enabled = true;

        btnLogIt.Enabled = false;

        lblStatus.Text = “Stopped”;

      }

      if (sc.Status == ServiceControllerStatus.Running)

      {

        btnStart.Enabled = false;

        btnStop.Enabled = true;

        btnLogIt.Enabled = true;

        lblStatus.Text = “Running”;

      }

    }

 

    private void btnStart_Click(object sender, EventArgs e)

    {

      ServiceController sc = new ServiceController(“ArcaneTimeLogging”);

      sc.Start();

      btnStart.Enabled = false;

      btnStop.Enabled = true;

      lblStatus.Text = “Running”;

      sc.Refresh();

    }

 

    private void btnStop_Click(object sender, EventArgs e)

    {

      ServiceController sc = new ServiceController(“ArcaneTimeLogging”);

      sc.Stop();

      btnStop.Enabled = false;

      btnStart.Enabled = true;

      lblStatus.Text = “Stopped”;

      sc.Refresh();

    }

And that’s all there is to it. Compile and reinstall your service, then launch your TLManager program. With the service started, click the LogIt button a few times then go into MMC and take a look at your event log. You should see a new message appear each time you click the LogIt button.

Windows Services in C#: Controlling Your Service from Another Application (part 6)

If you’ve ever used SQL Server, you know it comes with a little control program that allows you to start and stop the SQL Server service. Wouldn’t it be cool if you could write a small program to do the same with your service? Well you can, and today we’ll learn how.

Before we begin, I made a few little tweaks to the TimeLoggerService source code that will make it a bit easier to work with, and implement some of the things we’ll want to do in our control program.

    public TimeLoggerService()

    {

      InitializeComponent();

      // Set the timer to fire every twenty seconds

      // (remember the timer is in millisecond resolution,

      //  so 1000 = 1 second. )

      _timer = new Timer(20000);

 

      // Now tell the timer when the timer fires

      // (the Elapsed event) call the _timer_Elapsed

      // method in our code

      _timer.Elapsed += new

        System.Timers.ElapsedEventHandler(_timer_Elapsed);

    }

 

    private void WriteToLog(string msg)

    {

      EventLog evt = new EventLog(“ArcaneTimeLogger”);

      string message = msg + “: “

        + DateTime.Now.ToShortDateString() + ” “

        + DateTime.Now.ToLongTimeString();

      evt.Source = “ArcaneTimeLoggerService”;

      evt.WriteEntry(message, EventLogEntryType.Information);

    }

 

    protected override void OnStart(string[] args)

    {

      _timer.Start();

      WriteToLog(“Arcane Start”);

    }

 

    protected override void OnStop()

    {

      _timer.Stop();

      WriteToLog(“Arcane Stop “);

    }

 

    // This method is called when the timer fires

    // it’s elapsed event. It will write the time

    // to the event log.

    protected void _timer_Elapsed(object sender, ElapsedEventArgs e)

    {

      WriteToLog(“Arcane Timer”);

    }

In the class constructor, the only change I made was to change the time from 60 seconds (60000 milliseconds) down to 20 seconds (20000 milliseconds). To be honest I got tired of waiting on it to log for my tests.

Next, I created a “WriteToLog” method that handles the actual writing of a message to the event log. This code is identical to what was previously in the timer_Elapsed event, except I take a passed in message and append the current date/time to the log. Note one other change, I modified it to use the LongTimeString instead of ShortTimeString, so I could get the seconds to display.

I then modified the OnStart and OnStop to log start and stop messages for me, which is probably a good idea for your service to do too. Finally I modified the _timer_Elasped event where I’d taken the WriteToLog code from, and made a call to our new method. OK, that takes care of changes to the windows service.

Now, let’s add a new project to our solution. In the Solution Explorer, right click on the solution name and pick Add Project, then pick Windows Application. Note that we could do this with a command line app or class library as well, but for this demo we’ll use a windows form. I gave my new app the imaginative name of “TimeLoggerManager”.

I renamed the Form1 to TLManager, and allowed VS to rename all the occurances of Form1 for me. I’m now going to add a few basic controls, one label (lblStatus), and two command buttons (btnStart and btnStop). I’m also going to add a timer control, tmrRefresh. Set the timer to enabled and pick a reasonable time, maybe every 10 or 15 seconds (10000 or 15000 in the Interval property, remember it gets set in milliseconds as well).

In order to use some of the classes we’ll need, we must set a reference to the System.ServiceProcess assembly. Right click on the TimeLoggerManager and Add Reference, then on the .Net tab scroll down to System.ServiceProcess, click on it and press OK.

Now switch to code view on the form, and in the using area add a “using System.ServiceProcess” reference.

The first thing we need to do is find out what the status is of the service event. To do this we’ll first get a reference to our service by creating a ServiceController object, note in the “new” area we have to pass in the name of our service in order to get a reference to it. Once our object is created, I’ll pass it to a method that will set everything up for the form.  

      ServiceController sc = new ServiceController(“ArcaneTimeLogging”);

      SetDisplay(sc);

Set Display is a custom method I wrote, here’s it’s code:

    private void SetDisplay(ServiceController sc)

    {

      sc.Refresh();

      if (sc.Status == ServiceControllerStatus.Stopped)

      {

        btnStop.Enabled = false;

        btnStart.Enabled = true;

        lblStatus.Text = “Stopped”;

      }

      if (sc.Status == ServiceControllerStatus.Running)

      {

        btnStart.Enabled = false;

        btnStop.Enabled = true;

        lblStatus.Text = “Running”;

      }

    }

The first thing called is sc.Refresh, this will cause the ServiceController to update all of the properties in our sc object with the correct values. Next I can query the Status property of our SC object, and set my command buttons and labels appropriately.

Starting and stopping our service is just as easy, all we have to do is create an instance of a service controller object, and then call it’s Start or Stop method.

    private void btnStart_Click(object sender, EventArgs e)

    {

      ServiceController sc = new ServiceController(“ArcaneTimeLogging”);

      sc.Start();

      btnStart.Enabled = false;

      btnStop.Enabled = true;

      lblStatus.Text = “Running”;

      sc.Refresh();

    }

 

    private void btnStop_Click(object sender, EventArgs e)

    {

      ServiceController sc = new ServiceController(“ArcaneTimeLogging”);

      sc.Stop();

      btnStop.Enabled = false;

      btnStart.Enabled = true;

      lblStatus.Text = “Stopped”;

      sc.Refresh();

    }

Because you can also start and stop the service from other locations, like VS or the MMC, it’s important to keep the display in sync. In the event for the timer, all we have to do is create another reference and pass it to the same SetDisplay method so everything stays in sync.

    private void tmrRefresh_Tick(object sender, EventArgs e)

    {

      ServiceController sc = new ServiceController(“ArcaneTimeLogging”);

      SetDisplay(sc);

    }

Go ahead and give it all a try. Start your service, then check it in the MMC. Use MMC to stop the service, then watch the app automatically update to reflect the status.

Tomorrow we’ll look at sending commands to our windows service, then to wrap up the series we’ll look at integrating the event log into our application. Stay tuned!

Windows Services in C#: Controlling Your Service from Visual Studio (part 5)

In part 3 of this series I documented how to use the Microsoft Management Console to control your service and view the event log. But did you know you can do it right inside Visual Studio?

Inside Visual Studio, open the Server Explorer (I keep mine docked over on the left). Under any database servers you may have should be your computer, click the + symbol to expand the tree.

[Pic of Server Explorer]

Now you can see quite a few items, including Services and Event Logs. Expand the services tree, and let’s scroll down to our service, ArcaneCodeTimeLogger. Right clicking will show us the various commands available to us. Since the service is already running, you can pause or stop it.

[Pic of Services in Server Explorer]

Having this functionality right within Visual Studio makes it very easy when it’s time to debug and test your various methods such as OnStart, OnStop, OnPause, etc. But wait, there’s more!

Just as with services, you can also examine the event log. Scroll up to the event log node and expand it. If you read my earlier series on event logging (http://shrinkster.com/p6d), you know I suggest creating your own distinct event log instead of shoving everything into the Application log. Now you can see why, it makes it very easy to pick out the messages for your app. Expand the two nodes for our service and you can see the first part of the messages appearing in the tree.

[Pic of Event Log in Server Explorer]

To see the complete message, simply double click on it. It will appear, along with other associated data, in the Properties window of Visual Studio.

[Pic of Properties showing detailed EventLog Message]

One thing you should note, when you use Visual Studio to debug your Windows Service, VS “helpfully” hides a lot of your windows, including the Server Explorer. You can get it back though, simply go to the View and pick the Server Explorer to make it appear again.

Now, you may be wondering why way back in part 3 we went through the MMC (Microsoft Management Console) instead of doing it this way. There are often multiple ways to accomplish tasks, and it’s often useful to know them all. For example, let’s say you have your service installed on a users PC and need to stop it or look at its events. If you don’t have Visual Studio installed on the box, what are you going to do?

When you do have VS, using the Server Explorer from within Visual Studio can make it easy to develop and debug your Windows Services. Take a few minutes to explore it’s capabilities, so you’ll have a second way to work with your services.

Windows Services in C#: Debugging Windows Services (part 4)

In part 1 of this series I mentioned debugging a windows service was a little different than normal debugging of an application. Today we’ll look into how you can debug your windows service.

First, open Visual Studio and have your project loaded, if it’s not already there. Now go over to the MMC (as I described in part 3) and make sure it’s logging events.

Now comes the neat part. Under the Debug menu in Visual Studio, select “Attach to process…”. When the dialog below appears, you will need to check the “Show processes from all users” and “Show processes in all sessions” boxes. Now your list should update correctly.

Scroll down and look for the process with the same name as your executable, in my case it was TimeLogger.exe. Click on it, and the click the “Attach” button in the lower left.

[Picture of Attach to Service Dialog]

If all went well Visual Studio should shift to “Run” mode. Your code will be locked (sorry, no edit continue with windows services). But you can go in and create breakpoints, as I’ve done here (click on the pic to see a larger version of it):

[Pic of VS ready to debug]

Now sit back and wait a minute, when our service fires the _timer_Elapsed event it will fall into the standard debug mode you’re used to, as you can see below.

[Pic of VS stepping thru the service]

In the screen above you can see where I took one step and am now on the line of code “string message =…”. I have access to my locals, as well as the call stack and other debugging tools. From here I can do the normal debug tasks, including stepping or just hitting F5 to continue.

When you are done debugging and are ready to disconnect from the service, simply return to the Debug menu and this time pick “Stop Debugging” (or hit Shift+F5). Visual Studio disconnects you from the running service and returns you to normal code editing mode.

Resetting for another test is still a bit painful. You’ll want to stop your service, then in your Visual Studio Command Prompt window run InstallUtil, this time with the /u option to uninstall it. (instalutil /u timelogger.exe). Then you can build, then reinstall your service.

I said this yesterday, but I want to stress it again. If you are developing under Vista, it is vitally important you run VS Command Prompt as the Administrator (simply right click on the menu option and pick run as administrator). If you don’t do this, instalutil will fail every time.

And that’s how you debug an windows service. It’s not really that difficult, now that you know the steps involved.

Windows Services in C#: Adding the Installer (part 3)

OK, you’ve crafted your service, now you’re ready to install it so you can test. To do so you’ll need to create an installer for your project. However, you don’t create an installer in the traditional fashion.

Instead, switch to the “TimeLoggerService.cs [Design]” tab. Now in that big gray area right click, and pick “Add Installer”.

[Picture of Add Installer Menu]

Visual Studio will do some magic and you’ll have a new ProjectInstaller.cs added to your project. It also added a few new references to the solution. If the “ProjectInstaller.cs [Design]” tab is not up, bring it up, and click on the serviceInstaller1 item.

Let’s start by giving it a decent name, I chose ArcaneTimeLoggerServiceInstaller. Now for the Description property I entered “The Arcane Code Time Logging Service”. For DisplayName I gave it “Arcane Code Time Logger”. Finally, I’m leaving the StartType property to Manual, you may wish to alter this for your “real world” service.

Now go back and click on the serviceProcessInstaller1. We’ll change it’s name to ArcaneTimeLoggerServiceProcessInstaller. If you remember the discussion from part 1, you will recall a discussion about the security. Here in the Account property is where you will want to set that. Since all this sample does is a minimal amount of logging, I can go with a fairly low level of security and set to “LocalSystem”.

OK, we’re almost done. Right click the project name (in my case TimeLogger) and select properties from the menu. (Note, make sure to click the project, not the solution!) Now on the Application tab, under “Startup object” pick TimeLogger.Program. Now save everything and build your project.

Assuming your build was successful, you can now install and test your windows service. There are two ways to install, we can use the installutil.exe, or create a full blown MSI installer. Since we are just at the point of debugging, we will use the simple installutil.exe.

To preset all the pathing you’ll need for install util, we’ll need to open a Visual Studio Command Window. Start, All Programs, Microsoft Visual Studio 2005, Visual Studio Tools, Visual Studio Command Window. If you are running under Vista, STOP! Do NOT click on Visual Studio Command Window. Instead, right click and pick “Run as Administrator”. Again that’s for Vista, for XP just click since you likely have Admin rights.

The moral is without Admin rights InstallUtil fails every time, and it drove me up the wall trying to figure this out.

Now in the command window navigate to the bin\debug folder where your project compiled. Type in installutil TimeLogger.exe (or whatever you named your exe).

[Picture of Command Prompt]

If everything goes well, you s hould get the messages “The Commit phase completed successfully” and “The transacted install has completed”. Now let’s go see if we were successful.

Open the Microsoft Management Console (Start, Run, MMC)). When it opens, pick the Services and EventViewer snap-ins. Under Services, you should easily find the ArcaneTimeLogger, just double click on it and start it. Once it starts you can close the dialog.

Now head over to the Event Viewer. Click on the “Create Custom View”, to make it easy to find our log events. In the “Create Custom View” dialog, select “By source” and in the drop down check the ones for ArcaneTimeLogging. Click OK to close.

[Picture of Create Custom View]

Your view should now update to look something like this:

[Picture of MMC with our Events]

Congratulations, you’ve now coded and installed a basic windows service, and more over logged events from your service. This sample app we just created can serve as a basic template for all of your future windows services.

By the way, we should probably not get carried away with the euporia. Let’s take a moment and clean up. Return to the services area of the MMC and double click on our ArcaneTimeLogger. Now Stop the service, so it won’t be continually logging the time.

Now that it’s not running, let’s uninstall it. Return to the Visual Studio Command Window and simply type the command “installutil /u TimeLogger.exe”. The /u switch will tell InstallUtil to uninstall our service named TimeLogger.exe. And with that you’ve take care of your clean up work. Tomorrow we’ll talk a bit about debugging a windows service.

Windows Services in C#: Getting Started (part 2)

Yesterday we covered the basics you need to understand in order to write Windows Services. With that out of the way, it’s time to roll up our sleeves and write some code.

To get started, open Visual Studio and select new project. Windows Service won’t appear in the top level list of Visual C# items, instead you’ll need to drill down and in the tree under Visual C# select Windows. Now you should see “Windows Service” appear in the list of templates. Pick it, then go down and key in a name. For this demo I’m going to do something very simple, so let’s call it “TimeLogger” and press OK.

[New Project Dialog]

Now let’s take a moment to see what has been created for us. Over the in the solution explorer, beside the properties and references you’ll see two files, program.cs and service1.cs. Let’s look inside Program.cs, shall we?

    static void Main()

    {

      ServiceBase[] ServicesToRun;

 

      // More than one user Service may run within the same process. To add

      // another service to this process, change the following line to

      // create a second service object. For example,

      //

      //  ServicesToRun = new ServiceBase[] {new Service1(), new MySecondUserService()};

      //

      ServicesToRun = new ServiceBase[] { new Service1() };

 

      ServiceBase.Run(ServicesToRun);

    }

 

This simple code does an incredible amount. First, it creates an array of ServiceBase objects named ServicesToRun. This will hold the list of all the services this project will hold. Next it adds the single service that was automatically generated for you, Service1, to the array, and finally it calls the Run method to launch all of the services.
 
The first thing we’ll want to do is change the name of Service1 to something more meaningful. Click on the file name in Solution Explorer, and change it’s name from Service1 to TimeLoggerService. You’ll then be asked if you want to change all references, say Yes to let VS take care of all the work for you. You should now see that Program.cs has been updated:
 

      ServicesToRun = new ServiceBase[] { new TimeLoggerService() };

If you had other services to run, you’d add them to the array prior to the ServiceBase.Run command. What’s important to note though is the Run command launches the service file in it’s own thread, then keeps on trucking returning control back to windows.

Specifically, it’s the Service Control Manager (SCM) which is launching your app via the Main method in program.cs, then expecting control back. If you didn’t use Run, but instead tried to process a bunch of time consuming commands, the SCM would eventually time out and report your service failed to start. For that reason it’s important to put no extra code in the Main method other than what’s needed to get to the ServiceBase.Run command.

Now let’s take a look at the TimeLoggerService class. Double click on it in the Solution Explorer, and you will see a new editor tab appear with “TimeLoggerService.cs [Design]” in the title. The big gray area doesn’t show you much, so let’s ignore it for right now and look at the Properties window.

The most important property is ServiceName. This is the one you really want to change, as it’s the one that shows up throughout the Windows infrastructure. Windows can only have one version of a service going, so if you don’t change it the next project you start will also have the default name “Service1” and the two will collide like an iceberg with the Titanic. I’m changing the name to ArcaneTimeLogger.

AutoLog is another property to note, when true (the default) messages for the starting, stopping, etc. of your service will be taken care of for you. Since you can never have too much info, I would leave this set to true.

Next are a series of “Can” properties: CanHandlePowerEvent, CanHandleSessionChange, CanPauseandContinue, CanShutdown, and CanStop. If any of these are set to true, you will have to then insert a method to handle the event (or events in the case of Pause and Continue).

OK, that handles all of the important properties we need to examine. Let’s look at some code. Open the TimeLoggerService.cs so you can see the code.

  public partial class TimeLoggerService : ServiceBase

  {

    public TimeLoggerService()

    {

      InitializeComponent();

    }

 

    protected override void OnStart(string[] args)

    {

      // TODO: Add code here to start your service.

    }

 

    protected override void OnStop()

    {

      // TODO: Add code here to perform any tear-down necessary to stop your service.

    }

  }

The first thing you should notice is our class inherits from ServiceBase. The ServiceBase has two methods you must implement, OnStart and OnStop, and you’ll notice Visual Studio has helpfully created them for you. If you want to start your own service class from scratch, be sure to inherit from ServiceBase and implement these two methods.

Now let’s add a little code. We’ll add a timer and log the time every minute. Here’s the complete class:

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Diagnostics;

using System.ServiceProcess;

using System.Text;

using System.Timers;

 

namespace TimeLogger

{

  public partial class TimeLoggerService : ServiceBase

  {

    // Note we had to add System.Timers to the using area

    private Timer _timer = null;

 

    public TimeLoggerService()

    {

      InitializeComponent();

      // Set the timer to fire every sixty seconds

      // (remember the timer is in millisecond resolution,

      //  so 1000 = 1 second. )

      _timer = new Timer(60000);

 

      // Now tell the timer when the timer fires

      // (the Elapsed event) call the _timer_Elapsed

      // method in our code

      _timer.Elapsed += new

        System.Timers.ElapsedEventHandler(_timer_Elapsed);

    }

 

    protected override void OnStart(string[] args)

    {

      _timer.Start();

    }

 

    protected override void OnStop()

    {

      _timer.Stop();

    }

 

    protected override void OnContinue()

    {

      base.OnContinue();

      _timer.Start();

    }

 

    protected override void OnPause()

    {

      base.OnPause();

      _timer.Stop();

    }

 

    protected override void OnShutdown()

    {

      base.OnShutdown();

      _timer.Stop();

    }

 

    // This method is called when the timer fires

    // it’s elapsed event. It will write the time

    // to the event log.

    protected void _timer_Elapsed(object sender, ElapsedEventArgs e)

    {

      EventLog evt = new EventLog(“ArcaneTimeLogger”);

      string message = “Arcane Time:”

        + DateTime.Now.ToShortDateString() + ” “

        + DateTime.Now.ToShortTimeString();

      evt.Source = “ArcaneTimeLoggerService”;

      evt.WriteEntry(message, EventLogEntryType.Information);

    }

  }

}

The code is pretty self explanatory; I added OnContinue, OnPause (which are the implementations of CanPauseAndContinue), and OnShutdown (for CanShutdown) methods and set those properties to true. I then added a method, _timer_Elapsed, that will do all the work when the timer fires. Also note I had to add a reference to System.Timers to be able to use them.

OK, we now have a spiffy new service just eager and ready to be run. Well, if you recall from part 1 you simply can’t run a windows service, you have to install it. And that’s a big subject we’ll cover tomorrow in part 3!

Windows Services in C#: Getting Started (Part 1)

On occasion you have need for an application that will run all of the time on a workstation or server, whether anyone is logged into that workstation, or perhaps running in the background. For those requirements you’ll want to create a Windows Service.

There are a few things you have to keep in mind when creating a Windows Service. The first, and most important is that there is no user interface. Because Windows Services run in the background, even when no user is logged in, most times (especially when running on a server) there is no one to display a user interface to. As such, things like MessageBoxes and Forms are not allowed. Instead, if you need to log messages and the like it’s recommend you use the Event Logger. (For more info see my series on event logging, it started on January 16 of this year and ran to the 19th, http://shrinkster.com/p6d).

The next thing you will want to decide is how you’d like your service to start. You have three choices; in Manual mode (which is the default) either a user or some program event must start the service. In Automatic mode, windows will automatically kick off the event for you, and start it running when Windows itself starts up. Finally, in Disabled mode, nobody can start it until the start up type is changed.

Now you need to think about security, what permissions will your service need? LocalService is the most secure, it gets sandboxed on a local station and any access to insecure tasks, such as writing to the hard disk, are restricted. This is a good choice when you want to create an app that monitors something on the box and logs it’s output to the event log, but needs little other in the way of resources.

For a server you might wish to bump up to NetworkService. This will allow communication with other PCs on the network, but still manages any potentially harmful access to the machine.

If you need unlimited privileges, you’ll need to set your security to LocalSystem. I encourage you to really think about this before you pick LocalSystem as your level, as you open yourself up to severe vulnerabilities if anyone were to hack your code. However I do recognize there are some cases where it’s the only way to make it work, or perhaps you are in a situation where it’s safe to do so (perhaps your machine is not on the internet).

The final, and default choice is User. With User, you pass in a user id and password, and your service has the same permissions as that user. The ideal situation would be to create a special user account just for this service, and set the permissions just high enough to get the job done, and no higher. Also you’ll likely want to setup an ID with a non-expiring password. If you omit a user id/password then the person who launches the service will be prompted for a valid user id/password at run time, probably not a desirable option.

You should also be aware that debugging a windows service is very different from your typical debugging, whether you are used to WinForms or ASP.Net coding. We’ll cover debugging in a post of its own later in this series.

Just as debugging is different, so is installation. Because of its nature a Windows service has to be installed, you can’t just copy it and run it. Fortunately Visual Studio makes it easy for you by generating a setup project you can use. Again, we’ll cover that later in the series.

OK, you now have all the background you need under your belt, tomorrow we’ll start generating some code!

Arcane Thoughts: Thinking Inside the Box

Today I’m at an offsite meeting, talking about a new project. I won’t get into too many specific details, but we have to pull data from a web service and update an Oracle database. We can use a vendor provided Java API that runs on a Unix box to do the updates, or we can write to the database directly as long as we handle integrity issues.

So we spent the day brainstorming, to come up with possible solutions. Here is the list of contenders:

  • Write a Java app that runs on Unix that uses the vendor API’s.
  • Write a Java app that runs on Unix and updates the database directly.
  • Write a C# app that runs on a Windows Server, where a Batch Scheduler will kick it off.
  • Write a C# app that runs as a Windows Service under XP (we haven’t taken the Vista plunge at work yet).
  • Write a SQL Server Integration Services package that is run by the SQL Server job scheduler. It will use the web service as the input and update Oracle.
  • Use one of the above methods to pull the data then let BizTalk process it from there.

We haven’t made a decision yet, and my point was not so much to talk about the pro’s and cons of each solution. Instead it’s to get you to think creatively when it comes to new solutions for your company. Sitting down and cranking out yet another C# or VB.Net app may not always be the best approach. You may have a task you can accomplish with less code by using SQL Server Integration Services. Or maybe BizTalk might fit the bill.

All too often as programmers our first answer to any solution is to pull up Visual Studio and start grinding out code. Take some time though, to explore a few other options. There’s a rich set of tools out there, and sometimes the best solution to a programming problem may not be programming.

Orcas and SQL Server Compact Edition

The new beta of Orcas (the next version of Visual Studio) is now out. You can download it either as an installer or as a Virtual PC image. I opted to download the VPC image (I would have put it in a VPC anyway, so why not save some work?).

The main info page for Orcas is at http://msdn2.microsoft.com/en-us/vstudio/aa700831.aspx or http://shrinkster.com/oqp. From there you can pick the download type you want. As I mentioned before, there’s an installer version and the VPC version. If you have a spare machine lying around, you can use the installer, otherwise I highly recommend the Virtual PC version. (I grabbed the Visual Studio Team Suite Only – VPC).

It’s very very VERY important you completely read the instructions. Did I mention it was important to read the instructions? Well in case I didn’t, be sure to read the instructions.

There’s actual two VPC images you have to download. The first is named Orcas, the second though is called BASE (it’s in an exe). When you run the Orcas VPC the first time, it will ask you to point to the base VPC. In addition, you will need to know the user id and password to login, both available on the download page and buried in the instructions. (See, I told you it was important to read the instructions!)

Like most people playing with Orcas, the first thing I did was tested what I already knew. In this case, I loaded in some of the SQL Server Compact Edition samples I’ve published here in the past few weeks. Most notably the code in my post on April 13th (http://shrinkster.com/oqq).

In some ways I wish I had a lot of new technical content to share with you. Harrowing tales of how I was able to fight the bugs and to conquer the evil things lurking in Orcas. But I can’t. It just worked. And, I’m happy to say worked without any flaws. I didn’t have to install any special add-ins (Orcas comes with SSCE assemblies preinstalled) or make special references.

I’ll keep playing with it, but for now I’m quite happy to report that while I don’t see any radical changes with SSCE in Orcas, I don’t see any issues so far either.

SQL Server 2005 Compact Edition – Important Component

In my post on Getting Started with SQL Server ( http://shrinkster.com/nsk ), I mentioned 3 things you need. SQL Server CE, Visual Studio 2005 SP 1, and the SQL Server CE Books on Line. As it turns out there’s an important fourth item.

The missing component is the “Microsoft SQL Server Compact Edition Tools for Visual Studio 2005 Service Pack 1”, available at http://www.microsoft.com/downloads/details.aspx?familyid=877C0ADC-0347-4A47-B842-58FB71D159AC&displaylang=en or http://shrinkster.com/oam .

This service pack fixes a few things using SSCE with VS, one of them I consider critical. When you do a Project, References from VS, in the .Net list you will now be able to find a selection for System.Data.SqlServerCe.

Second, all of the Create Database dialogs now correctly read “SQL Server Compact Edition” instead of “SQL Server Mobile”. It also updates device CAB files correctly and includes new features such as Click Once support.

This should be installed fourth in your list, so if you’ve already done the other components, you are good to go for installing this. To recap:

  1. Install SQL Server Compact Edition components. (http://shrinkster.com/l9f).
  2. Install Visual Studio Service Pack 1. (http://shrinkster.com/lel )
  3. Install SQL Server CE Books On-line. ( http://shrinkster.com/lem )
  4. Install SSCE for VS SP1, as I’ve described here. ( http://shrinkster.com/oam).

A special thanks to Doug Tunure, our regional MS Developer Evangelist who helped me communicate with the SSCE team to figure out what was missing, and thanks to the SSCE team for a great tool.

System Views in SQL Server Compact Edition: Tables

First off, let me say a few thank you’s. Thanks to the Lower Alabama .Net User Group for putting on a great code camp. And thanks to all of you who attended my presentation, I felt honored, especially considering some of the others who were speaking at the same time. Now, on to the views.

While SQL Server Compact Edition does not support the creation of views, it does come with several views built in. For those of you familiar with full blown SQL Server, these will be familiar as they are all part of the INFORMATION_SCHEMA.

There are seven, Columns, Indexes, Key_Column_Usage, Tables, Table_Constraints, Provider_Types, and Referential_Constraints. When SSCE was created, these views were setup to mimic many of the views of “big” SQL Server. But because SSCE only supports a limited set of features, many of the fields wind up being NULL. Like in the INFORMATION_SCHEMA.TABLES view.

For this example, I’m creating a couple of very simple tables. Start by creating a brand new SSCE database. Next, here’s the SQL needed to create my example tables, you should be able to use either Visual Studio Database Explorer or SQL Server Management Studio.

create table employee (

empid uniqueidentifier not null,

last   nvarchar(100),

first nvarchar(100),

birth  datetime,

constraint pk_empid primary key (empid)

);

go

create table inventory (

partid uniqueidentifier not null,

partname nvarchar(100) not null,

qtyonhand int,

location nvarchar(50),

constraint pk_partid primary key (partid)

)

OK, now that you have a few tables created, let’s issue the following command:

select * from information_schema.tables;

What gets brought back is:

[SSCE Table Output 1]

You’ll note all the NULLs, as I said most of this was jettisoned in order to put the Compact in Compact Edition. As such, we have a lot of unnecessary fields in the output. Let’s refine the query a little:

select table_name, table_type from information_schema.tables;

Produces this output:

[SSCE Table Output 2]

At first glance you may think the TABLE_TYPE of TABLE is redundant. And with the state of SSCE today you’d be right. But for the time being if you want to write queries that will survive into the future, I’d suggest writing your SQL as:

select table_name from information_schema.tables where table_type = ‘TABLE’;

This will give you this output:

[SSCE Table Output 3]

Now you have a nice, safe query that will return all table names in your database, and you can rest assured that it will survive into the future should Microsoft decide to add other object types to the Tables view.

So what could you do with this? Well I setup a little test harness similar to the one I did in the last series. I created a simple windows form with one button named Tables and a label we’ll call lblResults. I also made a few other minor changes from my previous code base. Here’s the code, so you can see:

 

    // Class level to hold connection

    SqlCeConnection _cn = new SqlCeConnection(ConnectString());

 

    public Form1()

    {

      InitializeComponent();

    }

    #region ConnectString

    // This became static so it could be called

    // during the constructor, so we could set the

    // class level variable.

    private static string ConnectString()

    {

      string connectionString;

      string fileName = “SSCE_View_Test.sdf”;

 

      connectionString = string.Format(

        “DataSource=\”{0}\” “, fileName) ;

 

      return connectionString;

    }

    #endregion

 

    #region OpenConnection

    private void OpenConnection()

    {

      if (_cn.State==ConnectionState.Closed)

      {

        _cn.Open();

      }

 

    }

    #endregion

 

    #region btnTables_Click

    private void btnTables_Click(object sender, EventArgs e)

    {

      // In case it’s not already open

      // Calling it too much won’t hurt,

      // as it checks first before trying

      OpenConnection();

 

      SqlCeCommand cmd;

      string sql = “select table_name “

      + “from information_schema.tables “

      + “where table_type = ‘TABLE'”;

 

      try

      {

        cmd = new SqlCeCommand(sql, _cn);

        cmd.CommandType = CommandType.Text;

        SqlCeResultSet rs =

          cmd.ExecuteResultSet(ResultSetOptions.Scrollable);

 

        if (rs.HasRows)

        {

          int ordTable = rs.GetOrdinal(“table_name”);

          StringBuilder output = new StringBuilder();

          rs.ReadFirst();

          output.AppendLine(rs.GetString(ordTable));

          while (rs.Read())

          {

            output.AppendLine(rs.GetString(ordTable));

          }

          lblResults.Text = output.ToString();

        }

        else

          lblResults.Text = “No tables found.”;

      }

      catch (SqlCeException sqlexception)

      {

        MessageBox.Show(sqlexception.Message, “Oh Crap.”,

          MessageBoxButtons.OK, MessageBoxIcon.Error);

      }

      catch (Exception ex)

      {

        MessageBox.Show(ex.Message, “Oh Crap.”,

          MessageBoxButtons.OK, MessageBoxIcon.Error);

      }

 

    }

    #endregion

  }

 

Here’s the output:

[SSCE Table Output 4]

 

You could use this to build your own SQL Queries, reports, or to do code generation.

The rest of the examples in this series will fall into the same test harness, so be sure to get a good look and understanding. The rest of the week we’ll spend exploring some of the more useful SSCE Views.

SQL Server Compact Edition with C# and VB.Net

As a wrap up before this weekend’s code camp (http://alabamacodecamp.com), I wanted to upload the entire project so you could have it in one spot. In addition, I’ve also created a VB.Net version. I won’t go back over and comment on everything, you can look over the posts from my last few days for detailed explanations this is simply so you can have the code all in one spot.

I did the project as a simple windows project, here is an example of what my final form looked like:

[SSCE Windows Form Sample]

The C# version of the form looked identical except in the title bar, which reads SSCE C# Version.

Here is the complete C# version of the code behind the form. Note there is one difference from the code I put in my previous blog posts, in the LoadARow method I had the parameters in the order of last name, then first name, but in the tnLoadTable_Click when I called LoadARow I had put the cool people’s names in the order of first name, last name. I fixed that in the version below.

 

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Text;

using System.Windows.Forms;

using System.Data.SqlServerCe;

using System.IO;

 

namespace SSCE1

{

public partial class Form1 : Form

{

#region Form1

public Form1()

{

InitializeComponent();

}

#endregion

 

#region btnCreateDatabase_Click

private void btnCreateDatabase_Click(object sender, EventArgs e)

{

string connectionString;

string fileName = “ArcaneCode.sdf”;

string password = “arcanecode”;

 

if (File.Exists(fileName))

{

File.Delete(fileName);

}

 

connectionString = string.Format(

“DataSource=\”{0}\”; Password='{1}'”, fileName, password);

 

SqlCeEngine en = new SqlCeEngine(connectionString);

en.CreateDatabase();

 

lblResults.Text = “Database Created.”;

 

}

#endregion

 

#region btnCreateTable_Click

private void btnCreateTable_Click(object sender, EventArgs e)

{

SqlCeConnection cn = new SqlCeConnection(ConnectString());

 

if (cn.State==ConnectionState.Closed)

{

cn.Open();

}

 

SqlCeCommand cmd;

 

string sql = “create table CoolPeople (“

+ “LastName nvarchar (40) not null, “

+ “FirstName nvarchar (40), “

+ “URL nvarchar (256) )”;

 

cmd = new SqlCeCommand(sql, cn);

 

try

{

cmd.ExecuteNonQuery();

lblResults.Text = “Table Created.”;

}

catch (SqlCeException sqlexception)

{

MessageBox.Show(sqlexception.Message, “Oh Crap.”, MessageBoxButtons.OK, MessageBoxIcon.Error);

}

catch (Exception ex)

{

MessageBox.Show(ex.Message, “Oh Crap.”, MessageBoxButtons.OK, MessageBoxIcon.Error);

}

finally

{

cn.Close();

}

 

}

#endregion

 

#region ConnectString

private string ConnectString()

{

string connectionString;

string fileName = “ArcaneCode.sdf”;

string password = “arcanecode”;

 

connectionString = string.Format(

“DataSource=\”{0}\”; Password='{1}'”, fileName, password);

 

return connectionString;

}

#endregion

 

#region btnLoadTable_Click

private void btnLoadTable_Click(object sender, EventArgs e)

{

try

{

LoadARow(“Carl”, “Franklin”, @”http:\\www.dnrtv.com”);

LoadARow(“Richard”, “Campbell”, @”http:\\www.dotnetrocks.com”);

LoadARow(“Leo”, “Laporte”, @”http:\\www.twit.tv”);

LoadARow(“Steve”, “Gibson”, @”http:\\www.grc.com”);

LoadARow(“Arcane”, “Code”, @”http:\\arcanecode.wordpress.com”);

}

catch (Exception ex)

{

MessageBox.Show(ex.Message, “Oh Crap.”, MessageBoxButtons.OK, MessageBoxIcon.Error);

}

}

#endregion

 

#region LoadARow

private void LoadARow(string first, string last, string url)

{

SqlCeConnection cn = new SqlCeConnection(ConnectString());

 

if (cn.State == ConnectionState.Closed)

{

cn.Open();

}

 

SqlCeCommand cmd;

 

string sql = “insert into CoolPeople “

+ “(LastName, FirstName, URL) “

+ “values (@lastname, @firstname, @url)”;

 

try

{

cmd = new SqlCeCommand(sql, cn);

cmd.Parameters.AddWithValue(“@lastname”, last);

cmd.Parameters.AddWithValue(“@firstname”, first);

cmd.Parameters.AddWithValue(“@url”, url);

cmd.ExecuteNonQuery();

lblResults.Text = “Row Added.”;

}

catch (SqlCeException sqlexception)

{

MessageBox.Show(sqlexception.Message, “Oh Crap.”, MessageBoxButtons.OK, MessageBoxIcon.Error);

}

catch (Exception ex)

{

MessageBox.Show(ex.Message, “Oh Crap.”, MessageBoxButtons.OK, MessageBoxIcon.Error);

}

finally

{

cn.Close();

}

 

}

#endregion

 

#region cmdLoadDataGrid_Click

private void cmdLoadDataGrid_Click(object sender, EventArgs e)

{

SqlCeConnection cn = new SqlCeConnection(ConnectString());

 

if (cn.State==ConnectionState.Closed)

{

cn.Open();

}

 

try

{

// Set the command to use the table, not a query

SqlCeCommand cmd = new SqlCeCommand(“CoolPeople”, cn);

cmd.CommandType = CommandType.TableDirect;

 

// Get the table

SqlCeResultSet rs = cmd.ExecuteResultSet(

ResultSetOptions.Scrollable);

 

// load the result set into the datasource

dgvCoolPeople.DataSource = rs;

}

catch (SqlCeException sqlexception)

{

MessageBox.Show(sqlexception.Message, “Oh Crap.”,

MessageBoxButtons.OK, MessageBoxIcon.Error);

}

catch (Exception ex)

{

MessageBox.Show(ex.Message, “Oh Crap.”,

MessageBoxButtons.OK, MessageBoxIcon.Error);

}

 

// Note, do not close the connection,

// if you do the grid won’t be able to display.

// For production code you probably want to make

// your result set (rs) a class level variable

 

}

#endregion

 

#region btnReadRecords_Click

private void btnReadRecords_Click(object sender, EventArgs e)

{

SqlCeConnection cn = new SqlCeConnection(ConnectString());

 

if (cn.State == ConnectionState.Closed)

{

cn.Open();

}

 

// Build the sql query. If this was real life,

// I’d use a parameter for the where bit

// to avoid SQL Injection attacks.

string sql = “select LastName, FirstName from CoolPeople “;

if (txtName.Text.Length > 0)

{

sql += “where LastName like ‘” + txtName.Text + “%’ “;

}

 

try

{

SqlCeCommand cmd = new SqlCeCommand(sql, cn);

cmd.CommandType = CommandType.Text;

 

// if you don’t set the result set to

// scrollable HasRows does not work

SqlCeResultSet rs = cmd.ExecuteResultSet(

ResultSetOptions.Scrollable);

 

// If you need to be able to update the result set, instead use:

// SqlCeResultSet rs = cmd.ExecuteResultSet(

// ResultSetOptions.Scrollable | ResultSetOptions.Updatable);

 

if (rs.HasRows)

{

// Use the get ordinal function so you don’t

// have to worry about remembering what

// order your SQL put the field names in.

int ordLastName = rs.GetOrdinal(“LastName”);

int ordFirstname = rs.GetOrdinal(“FirstName”);

 

// Hold the output

StringBuilder output = new StringBuilder();

 

// Read the first record and get it’s data

rs.ReadFirst();

output.AppendLine(rs.GetString(ordFirstname)

+ ” “ + rs.GetString(ordLastName));

 

// Now read thru the rest of the records.

// When there’s no more data, .Read returns false.

while (rs.Read())

{

output.AppendLine(rs.GetString(ordFirstname)

+ ” “ + rs.GetString(ordLastName));

}

 

// Set the output in the label

lblResults.Text = output.ToString();

}

else

{

lblResults.Text = “No Rows Found.”;

}

 

}

catch (SqlCeException sqlexception)

{

MessageBox.Show(sqlexception.Message, “Oh Crap.”,

MessageBoxButtons.OK, MessageBoxIcon.Error);

}

catch (Exception ex)

{

MessageBox.Show(ex.Message, “Oh Crap.”,

MessageBoxButtons.OK, MessageBoxIcon.Error);

}

finally

{

// Don’t need it anymore so we’ll be good and close it.

// in a ‘real life’ situation

// cn would likely be class level

cn.Close();

}

 

}

#endregion

 

}

}

 

Here is the VB.Net version of the code. I tried to make all of the method names, variable names and comments match the C# version as much as possible.

 

Imports System

Imports System.Collections.Generic

Imports System.ComponentModel

Imports System.Data

Imports System.Drawing

Imports System.Text

Imports System.Windows.Forms

Imports System.Data.SqlServerCe

Imports System.IO

 

Public Class Form1

 

#Region “btnCreateDatabase_Click”

  ‘ Create an empty SSCE Database with a password.

  ‘ Note that when creating a db with code, adding a

  ‘ password automatically encrypts the database

  Private Sub btnCreateDatabase_Click(ByVal sender As System.Object, _

  ByVal e As System.EventArgs) Handles btnCreateDatabase.Click

 

    Dim connectString As String = “”

    Dim fileName As String = “ArcaneCode.sdf”

    Dim password As String = “arcanecode”

 

    If File.Exists(fileName) Then

      File.Delete(fileName)

    End If

 

    connectString = String.Format( _

    “DataSource=””{0}””; Password='{1}'”, fileName, password)

 

    Dim eng As SqlCeEngine = _

    New SqlCeEngine(connectString)

    eng.CreateDatabase()

 

    lblResults.Text = “Database Created”

 

  End Sub

#End Region

 

#Region “btnCreateTable_Click”

  ‘ Issue a SQL command to create a table

  ‘ Note this only creates the table, it

  ‘ does not put any rows in it.

  Private Sub btnCreateTable_Click(ByVal sender As System.Object, _

  ByVal e As System.EventArgs) Handles btnCreateTable.Click

 

    Dim cn As New SqlCeConnection(ConnectString())

 

    If cn.State = ConnectionState.Closed Then

      cn.Open()

    End If

 

    Dim cmd As SqlCeCommand

 

    Dim sql As String = “create table CoolPeople (“ _

        + “LastName nvarchar (40) not null, “ _

        + “FirstName nvarchar (40), “ _

        + “URL nvarchar (256) )”

 

    cmd = New SqlCeCommand(sql, cn)

 

    Try

      cmd.ExecuteNonQuery()

      lblResults.Text = “Table created.”

    Catch sqlexception As SqlCeException

      MessageBox.Show(sqlexception.Message, “Oh Crap.” _

      , MessageBoxButtons.OK, MessageBoxIcon.Error)

    Catch ex As Exception

      MessageBox.Show(ex.Message, “Oh Crap.” _

      , MessageBoxButtons.OK, MessageBoxIcon.Error)

    Finally

      cn.Close()

    End Try

 

  End Sub

#End Region

 

#Region “btnLoadTable_Click”

  ‘ This routine calls a subroutine that

  ‘ does the real work of inserting rows

  ‘ into the database.

  Private Sub btnLoadTable_Click(ByVal sender As System.Object, _

  ByVal e As System.EventArgs) Handles btnLoadTable.Click

 

    Try

      LoadARow(“Scott”, “Hanselman”, “http:\\www.hanselminutes.com”)

      LoadARow(“Wally”, “McClure”, “http:\\aspnetpodcast.com/CS11/Default.aspx”)

      LoadARow(“John”, “Dvorak”, “http:\\www.crankygeeks.com”)

      LoadARow(“Arcane”, “Code”, “http:\\arcanecode.wordpress.com”)

    Catch ex As Exception

      MessageBox.Show(ex.Message, “Oh Crap.”, _

        MessageBoxButtons.OK, MessageBoxIcon.Error)

    End Try

 

  End Sub

#End Region

 

#Region “ConnectString”

  ‘ A central place to serve up the connection string

  Private Function ConnectString() As String

 

    Dim connectionString As String

    Dim fileName As String = “ArcaneCode.sdf”

    Dim password As String = “arcanecode”

 

    connectionString = String.Format( _

    “DataSource=””{0}””; Password='{1}'”, fileName, password)

 

    Return connectionString

 

  End Function

#End Region

 

#Region “LoadARow”

  ‘ Generates the SQL and issues the command to

  ‘ insert a single row into the database

  Private Sub LoadARow(ByVal first As String, _

  ByVal last As String, ByVal url As String)

 

    Dim cn As New SqlCeConnection(ConnectString())

 

    If cn.State = ConnectionState.Closed Then

      cn.Open()

    End If

 

    Dim cmd As SqlCeCommand

 

    Dim sql As String = “insert into CoolPeople “ _

        + “(LastName, FirstName, URL) “ _

        + “values (@lastname, @firstname, @url)”

 

    Try

      cmd = New SqlCeCommand(sql, cn)

      cmd.Parameters.AddWithValue(“@lastname”, last)

      cmd.Parameters.AddWithValue(“@firstname”, first)

      cmd.Parameters.AddWithValue(“@url”, url)

      cmd.ExecuteNonQuery()

      lblResults.Text = “Row Added.”

    Catch sqlexception As SqlCeException

      MessageBox.Show(sqlexception.Message, “Oh Crap.”, _

        MessageBoxButtons.OK, MessageBoxIcon.Error)

    Catch ex As Exception

      MessageBox.Show(ex.Message, “Oh Crap.”, _

        MessageBoxButtons.OK, MessageBoxIcon.Error)

    Finally

      cn.Close()

    End Try

 

  End Sub

#End Region

 

#Region “btnLoadGrid_Click”

  Private Sub btnLoadGrid_Click(ByVal sender As System.Object _

  , ByVal e As System.EventArgs) Handles btnLoadGrid.Click

 

    Dim cn As New SqlCeConnection(ConnectString())

 

    If cn.State = ConnectionState.Closed Then

      cn.Open()

    End If

 

    Try

      ‘ Set the command to use the table, not a query

      Dim cmd As SqlCeCommand = New SqlCeCommand(“CoolPeople”, cn)

      cmd.CommandType = CommandType.TableDirect

 

      ‘ Get the Table

      Dim rs As SqlCeResultSet = cmd.ExecuteResultSet( _

        ResultSetOptions.Scrollable)

 

      ‘ Load the result set into the database

      dgvCoolPeople.DataSource = rs

 

    Catch sqlexception As SqlCeException

      MessageBox.Show(sqlexception.Message, “Oh Crap.”, _

        MessageBoxButtons.OK, MessageBoxIcon.Error)

    Catch ex As Exception

      MessageBox.Show(ex.Message, “Oh Crap.”, _

        MessageBoxButtons.OK, MessageBoxIcon.Error)

    End Try

    ‘ Note, do not close the connection,

    ‘ if you do the grid won’t be able to display.

    ‘ For production code you probably want to make

    ‘ your result set (rs) a class level variable

 

  End Sub

#End Region

 

#Region “btnReadRecords_Click”

  Private Sub btnReadRecords_Click(ByVal sender As System.Object _

  , ByVal e As System.EventArgs) Handles btnReadRecords.Click

 

    Dim cn As New SqlCeConnection(ConnectString())

 

    If cn.State = ConnectionState.Closed Then

      cn.Open()

    End If

 

    ‘ Build the sql query. If this was real life,

    ‘ I’d use a parameter for the where bit

    ‘ to avoid SQL Injection attacks.

    Dim sql As String = “select LastName, FirstName from CoolPeople “

    If txtName.Text.Length > 0 Then

      sql += “where LastName like ‘” + txtName.Text + “%’ “

    End If

 

    Try

 

      Dim cmd As SqlCeCommand = New SqlCeCommand(sql, cn)

      cmd.CommandType = CommandType.Text

 

      ‘ if you don’t set the result set to

      ‘ scrollable HasRows does not work     

      Dim rs As SqlCeResultSet = cmd.ExecuteResultSet( _

        ResultSetOptions.Scrollable)

 

      If rs.HasRows Then

 

        ‘ Use the get ordinal function so you don’t

        ‘ have to worry about remembering what

        ‘ order your SQL put the field names in.

        Dim ordLastName As Integer = rs.GetOrdinal(“LastName”)

        Dim ordFirstName As Integer = rs.GetOrdinal(“FirstName”)

 

        ‘ Hold the output

        Dim output As StringBuilder = New StringBuilder()

 

        ‘ Read the first record and get it’s data

        rs.ReadFirst()

        output.AppendLine(rs.GetString(ordFirstName) _

            + ” “ + rs.GetString(ordLastName))

 

        ‘ Now read thru the rest of the records.

        ‘ When there’s no more data, .Read returns false.

        Do While rs.Read()

          output.AppendLine(rs.GetString(ordFirstName) _

          + ” “ + rs.GetString(ordLastName))

        Loop

 

        ‘ Set the output in the label

        lblResults.Text = output.ToString()

      Else

        lblResults.Text = “No Rows Found.”

      End If

 

    Catch sqlexception As SqlCeException

      MessageBox.Show(sqlexception.Message, “Oh Crap.”, _

        MessageBoxButtons.OK, MessageBoxIcon.Error)

    Catch ex As Exception

      MessageBox.Show(ex.Message, “Oh Crap.”, _

        MessageBoxButtons.OK, MessageBoxIcon.Error)

    Finally

      ‘ Don’t need it anymore so we’ll be good and close it.

      ‘ in a ‘real life’ situation

      ‘ cn would likely be class level

      cn.Close()

    End Try

 

  End Sub

#End Region

End Class

There you go, the complete sample project for working with SQL Server Compact Edition. If you found this useful, please post a comment and let us know what sorts of applications you build using SSCE.

Hope to see you at code camp!

Reading From A SQL Server Compact Edition Database With C#

In our discussions so far, I’ve shown how to create SSCE databases and load them with data. By now I’m sure you are wondering how to pull that data back out. Today I will show you two methods, first a way to bind the data to a control, then how to read through a tables rows programmatically.

First, create a form and put a button on, call it btnLoadGrid. Next add a data grid viewer control, I named mine dgvCoolPeople after the table we’ll be reading.

In the click event for the button, here’s the code you’ll need:  

private void cmdLoadDataGrid_Click(object sender, EventArgs e)

    {

      SqlCeConnection cn = new SqlCeConnection(ConnectString());

 

      if (cn.State==ConnectionState.Closed)

      {

        cn.Open();

      }

 

      try

      {

        // Set the command to use the table, not a query

        SqlCeCommand cmd = new SqlCeCommand(“CoolPeople”, cn);

        cmd.CommandType = CommandType.TableDirect;

 

        // Get the table

        SqlCeResultSet rs = cmd.ExecuteResultSet(

          ResultSetOptions.Scrollable);

 

        // load the result set into the datasource

        dgvCoolPeople.DataSource = rs;

      }

      catch (SqlCeException sqlexception)

      {

        MessageBox.Show(sqlexception.Message, “Oh Crap.”,

          MessageBoxButtons.OK, MessageBoxIcon.Error);

      }

      catch (Exception ex)

      {

        MessageBox.Show(ex.Message, “Oh Crap.”,

          MessageBoxButtons.OK, MessageBoxIcon.Error);

      }

 

      // Note, do not close the connection,

      // if you do the grid won’t be able to display.

      // For production code you probably want to make

      // your result set (rs) a class level variable

 

    }

 

First we open the database, as you have seen before. Next we set the command to a new SqlCeCommand, and pass in the name of the table, CoolPeople. Then we tell the command it’s type is TableDirect. Using this method we can directly access the table, which is very fast if we are doing a quick read through all rows.

Next we execute the command and return a SqlCeResultSet, which is then loaded into the data source for the dgvCoolPeople grid viewer control.

The one important thing to note is NOT to close the connection, otherwise it will also close your grid. Normally I would keep my connection at the class level instead of in a method.

And that’s all there is to it, I should mention if you want your grid updateable make sure to use ResultSetOptions.Scrollable|ResultSetOptions.Updatable in the ExecuteResultSet method.

Now that you’ve seen how to bind your control, let’s look at what it takes to step through the rows programmatically. Go back to your form and add another button, call it btnReadRecords. Also add a textbox named txtName. Finally if you are using your code from before, you already have a label named lblResult, if not go ahead and add it as well.  

    private void btnReadRecords_Click(object sender, EventArgs e)

    {

      SqlCeConnection cn = new SqlCeConnection(ConnectString());

 

      if (cn.State == ConnectionState.Closed)

      {

        cn.Open();

      }

 

      // Build the sql query. If this was real life,

      // I’d use a parameter for the where bit

      // to avoid SQL Injection attacks.

      string sql = “select LastName, FirstName from CoolPeople “;

      if (txtName.Text.Length > 0)

      {

        sql += “where LastName like ‘” + txtName.Text + “%’ “;

      }

 

      try

      {

        SqlCeCommand cmd = new SqlCeCommand(sql, cn);

        cmd.CommandType = CommandType.Text;

 

        // if you don’t set the result set to

        // scrollable HasRows does not work

        SqlCeResultSet rs = cmd.ExecuteResultSet(

          ResultSetOptions.Scrollable);

 

        // If you need to be able to update the result set, instead use:

        // SqlCeResultSet rs = cmd.ExecuteResultSet(

        //  ResultSetOptions.Scrollable | ResultSetOptions.Updatable);

 

        if (rs.HasRows)

        {

          // Use the get ordinal function so you don’t

          // have to worry about remembering what

          // order your SQL put the field names in.

          int ordLastName = rs.GetOrdinal(“LastName”);

          int ordFirstname = rs.GetOrdinal(“FirstName”);

 

          // Hold the output

          StringBuilder output = new StringBuilder();

 

          // Read the first record and get it’s data

          rs.ReadFirst();

          output.AppendLine(rs.GetString(ordFirstname)

            + ” “ + rs.GetString(ordLastName));

 

          // Now read thru the rest of the records.

          // When there’s no more data, .Read returns false.

          while (rs.Read())

          {

            output.AppendLine(rs.GetString(ordFirstname)

              + ” “ + rs.GetString(ordLastName));

          }

 

          // Set the output in the label

          lblResults.Text = output.ToString();

        }

        else

        {

          lblResults.Text = “No Rows Found.”;

        }

 

      }

      catch (SqlCeException sqlexception)

      {

        MessageBox.Show(sqlexception.Message, “Oh Crap.”,

          MessageBoxButtons.OK, MessageBoxIcon.Error);

      }

      catch (Exception ex)

      {

        MessageBox.Show(ex.Message, “Oh Crap.”,

          MessageBoxButtons.OK, MessageBoxIcon.Error);

      }

      finally

      {

        // Don’t need it anymore so we’ll be good and close it.

        // in a ‘real life’ situation

        // cn would likely be class level

        cn.Close();

      }

 

    }

 

Again we open the connection, then setup our SQL statement. This time I’ve constructed a simple select. If the user enters a letter for a name, I’ve added code for an optional where clause to limit the number of rows returned.

Like with the grid, we need to create a command that we can execute. This time we’ll pass in the SQL statement, and indicate that it is a SQL Statement by setting the command type to text.

Let me take a short side trip, if you typed in the code, when you hit the period after “CommandType”, you should have noticed 3 options.. The first two we have discussed, TableDirect and Text. You’ll also notice there’s a choice for “StoredProcedure”.

If you read the previous articles, you’ll probably be scratching your head as I’ve already said SSCE does not support stored procedures. So why is this option in the list?

Ya got me. My guess is they are sharing the intellisense with another library, and didn’t or couldn’t remove it. Either way, you should ignore it. If you try to use it all you’ll do is generate a run time error.

Back to the code, you see the next thing that is done is a check for HasRows. I need to emphasize something very important: HasRows only works when the Scrollable option is set in the ExecuteResultSet method! I can’t tell you why, I can just tell you to make sure to use a scrollable option or else you’ll have no end of headaches.

So if we have rows, we obviously want to process them. To retrieve column data from a SSCE row, the SqlCeResultSet object has a variety of GetType methods, where Type is such things as String or Int. In order to make it work, you pass the GetType methods what they call an ordinal value, which is nothing more than an integer that indicates the column number you want to retrieve.

I, for one don’t want to have to keep up with which column is which number, further I want the flexibility to change my column order or add new columns without worrying about a lot of code refactoring. That’s where the GetOrdinal method comes into play.

Simply call GetOrdinal and pass in a string with the name of the column, and SSCE will tell you what column number it’s in. Because I use these several times I took these and stored them in int variables. Now I’m free to go change my SQL all I want and don’t have to worry about breaking the rest of my code. It’s a technique I highly advise you to follow.

OK, so we know which column goes where, from here it’s pretty simple. Use the ReadFirst method to move to the first row, then let’s grab it’s data using the GetString methods.

Next we enter a while loop, the Read method will move us to the next record, and return false when there are no more records (thus exiting the loop).

And finally we copy the output we’ve been building into the label control. In this case I have no more need for the connection, so unlike in the grid example I can go ahead and close this connection variable.

These two techniques can be interchanged, for example I could have used a table direct to load my label, or a text type command to load the grid view. But these two basic techniques should give you all the functionality to complete your toolset for working with SSCE databases.