David, Alex and me just needed to get a load of data from some Excel sheets and work with the data. What's better than to load the data into a DataSet using OleDB and process it using LINQ to DataSets?
Make sure you know what the format of your columns is and that the first row in your sheet holds the name of the column. Then below would be what you need for code.
string filename = @"C:\myfile.xls";
string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + filename + ";" +
"Extended Properties=Excel 8.0;";
OleDbDataAdapter dataAdapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);
DataSet myDataSet = new DataSet();
dataAdapter.Fill(myDataSet, "ExcelInfo");
DataTable dataTable = myDataSet.Tables["ExcelInfo"];
var query = from r in dataTable.AsEnumerable()
select new
{
RelationNr = r.Field<double>("RelationNumber"),
ClientName = r.Field<string>("ClientName"),
};
foreach (var item in query)
Console.WriteLine(item.ClientName);
}
In my last post I explained how you could use WCF and MSMQ to respond to messages asynchronously. We've setup a class library with our service, a console application for our host and a console application for our client. In this post I'll explain how we can use the class library again for hosting our WCF service in a Windows Service.
1: Add the Windows Service
Add a new Windows Service project. Right-click the solution, choose "Add" -> "New Project..." and select "Windows" from the tree on the left. Then select "Windows Service" from the project templates. Call it "MailServiceHost" for example. It'll create the project with a file (class) called 'Service1.cs". Rename it to 'MailService.cs' using the solution explorer.
2: Implement OnStart and OnStop methods
When you look into your MailService class, you'll see that it's derived from ServiceBase and that two methods are already defined. We'll first implement the OnStart method. Here we'll do exactly the same as in the console application created in the previous article.
The only difference now is that it's a private member variable (defined on class level) because the OnStop method must have access to it as well.
1 ServiceHost _host = null;
2
3 protected override void OnStart(string[] args)
4 {
5 string queueName = ConfigurationManager.AppSettings["SendMailQueueName"];
6
7 Trace.WriteLine("Starting Class-A E-mail Service...");
8
9 if (!MessageQueue.Exists(queueName))
10 {
11 Trace.WriteLine("Creating queue : " + queueName);
12 MessageQueue.Create(queueName, true);
13 }
14
15 _host = new ServiceHost(typeof(SendMailService));
16 _host.Open();
17 }
Here's a quick overview
Now we have to implement the OnStop method.
protected override void OnStop()
Trace.WriteLine("Shutting down Class-A E-mail Service...");
if (_host != null)
_host.Close();
_host = null;
Note that the above code does not do any error handling or check if the ServiceHost its state is actually open, etc, etc.
3: Configuration
Now we have to add configuration. Add a new Application Configuration file and just copy & paste the entire configuration file from our previous Console Application host project.
4: ProjectInstaller
We've created our service, but aren't done yet. For Windows Services it's best if you add a ProjectInstaller so the service can easily be installed. Choose to add a new item to the project and select the "General" category and then the "Installer Class" template.
[RunInstaller(true)]
public partial class ProjectInstaller : Installer
public ProjectInstaller()
InitializeComponent();
ServiceProcessInstaller processInstaller= new ServiceProcessInstaller();
ServiceInstaller serviceInstaller = new ServiceInstaller();
processInstaller.Account = ServiceAccount.LocalSystem;
serviceInstaller.StartType = ServiceStartMode.Automatic;
serviceInstaller.ServiceName = "Class-A E-Mail MSMQ Service";
Installers.Add(serviceInstaller);
Installers.Add(processInstaller);
In the above code we've added a ServiceProcessInstaller and a ServiceInstaller. The ServiceProcessInstaller is used by InstallUtil.exe and we're using it to specify that we want to use the Local System account to run our service. If you specify ServiceAccount.User and don't provide a username and password, it'll request these during installation of the service. With the ServiceInstaller we're specifying that our service has to start automatically on Windows startup and the name of our service.
That's it, our WCF service is now running inside a Windows (Managed) Service.
Download the solution for a complete view on what we've done.
It's been a while since I blogged about one of my favorite topics, Windows Communication Foundation.
In this article I'll explain how you can use MSMQ with WCF to really process messages asynchronously. It's unbelievable how easy this is. Read my complete WCF series on how to set up your first service. The idea in this example is that we have to send some e-mails, but we don't want that to be done immediately.
We'll setup the service with a Console Application. In another post we'll host our service using a Windows Service.
1: Make sure MSMQ is installed.
MSMQ must of course be installed. Check your Windows components to see if it's installed. In Vista this is under "Control Panel" -> "Programs and Features" -> "Turn Windows features on or off".
On the right you can see I've turned on everything. If it's already installed you should be able to take a look at the queues. Right-click on the "Computer" icon and choose "Manage". It's under "Services and Applications".
2: Setup a class library.
Create a new project and make it a class library. Add a really simple interface. Again, if you want to know the how and why, read my complete WCF series.
[ServiceContract(Namespace="http://schemas.class-a.nl/msmq/example01/2008/02/", SessionMode=SessionMode.NotAllowed)]
public interface ISendMail
[OperationContract(Name = "SubmitMessage", IsOneWay = true)]
void SubmitMessage(MailMessage message);
Now we need an implementation for our service. In the code below I only output the message to the console. Normally you'd use tracing or logging for this, but in a bit we'll build a console application to host everything.
public class SendMailService : ISendMail
public void SubmitMessage(MailMessage message)
Console.WriteLine("To : " + message.ToAddress);
Console.WriteLine("From : " + message.FromAddress);
Console.WriteLine("Subject : " + message.Subject);
Console.WriteLine("Body : " + message.Body);
As you can see I'm transferring a MailMessage. Here's the datacontract for it.
[DataContract(Name="MailMessage", Namespace="http://schemas.class-a.nl/msmq/example01/2008/02/")]
public class MailMessage
[DataMember(Name="ToAddress", IsRequired=true, Order=1)]
public string ToAddress { get; set; }
[DataMember(Name = "FromAddress", IsRequired = true, Order = 2)]
public string FromAddress { get; set; }
[DataMember(Name = "CCAddress", IsRequired = false, Order = 3)]
public string CCAddress { get; set; }
[DataMember(Name = "BCCAddress", IsRequired = false, Order = 4)]
public string BCCAddress { get; set; }
[DataMember(Name = "Subject", IsRequired = true, Order = 5)]
public string Subject { get; set; }
[DataMember(Name = "Body", IsRequired = true, Order = 6)]
public string Body { get; set; }
That's our service, let's get to hosting
3: Create a host
In a next post I will explain hosting this WCF service in a Windows Service. For now, a Console Application will do. It's also much simpler to debug and test our application that way. Add a new Console Application to your solution.
First thing we need to do, is make sure we have a queue. First create a reference to the System.Messaging assembly. Then past the following code in your Main() method.
string queueName = ConfigurationManager.AppSettings["SendMailQueueName"];
if (!MessageQueue.Exists(queueName))
MessageQueue.Create(queueName, true);
As you can see, we're getting the SendMailQueueName from the configuration and if it doesn't exist yet, we'll create it. For the configuration, just download the complete solution.
Now just create a ServiceHost and open it.
Type serviceType = typeof(SendMailService);
using (ServiceHost host = new ServiceHost(serviceType))
host.Open();
Beside the fact we've checked for existence of the queue using System.Messaging, we did nothing so far concerning MSMQ. The only thing noticeable is setting the operation as a OneWay operation.
4: Setting up the host configuration
Let's have a look at the services configuration. We've configured a single service, based on the SendMailService. It as two endpoints, but one's for exchanging metadata (MEX endpoint). As you can see from the binding and the address of the other endpoint, this is the first time we're specifying that we want to use MSMQ. We're using the NetMsmqBinding and a private queue for our service.
<system.serviceModel>
<services>
<service behaviorConfiguration="MetadataBehavior" name="EmailService.SendMailService">
<endpoint
address="net.msmq://localhost/private/ClassA_SendMail"
binding="netMsmqBinding"
contract="EmailService.ISendMail"
bindingConfiguration="SendMailNetMsmqBinding" />
address="mex"
binding="mexHttpBinding"
contract="IMetadataExchange" />
<host>
<baseAddresses>
<add baseAddress="http://localhost:8080/SendMail/" />
</baseAddresses>
</host>
</service>
</services>
</system.serviceModel>
Pay attention to the 'behaviorConfiguration' attribute on the service and my 'SendMailNetMsmqBinding binding configuration'. The behavior configuration isn't interesting, it's just enabling the MEX endpoint to work. The binding configuration for the endpoint is slightly interesting. As you can see below I've set the security to 'None'. That's because my laptop isn't attached to a domain. By default it's on transport security, meaning it'll use Active Directory integration, which won't allow me to connect in my situation. You'll get a nice :
Binding validation failed because the binding's MsmqAuthenticationMode property is set to WindowsDomain but MSMQ is installed with Active Directory integration disabled. The channel factory or service host cannot be opened.
The deadLetterQueue attribute is Windows version specific, because I can only set it to custom on Vista and Windows Server 2008. No need for a custom created queue here, it'll just group the messages in the dead-letter queue, so you'll know they're yours. Read this article on MSDN for more info.
<bindings>
<netMsmqBinding>
<binding name="SendMailNetMsmqBinding" deadLetterQueue="Custom">
<security mode="None" />
</binding>
</netMsmqBinding>
</bindings>
5: Creating a client
Normally our client is calling the service directly. We can't do that, but we can get the metadata from our service, because we've set up a MEX endpoint. Add a new console application to your solution and create a service reference to the service. You can add a service reference by starting your service WITHOUT debugging. This way the option isn't disabled in your console client.
Now open the proxy code and insert a message!
static void Main(string[] args)
SendMailClient svc = new SendMailClient();
MailMessage msg = new MailMessage();
msg.ToAddress = "dennis@nospam.class-a.nl";
msg.FromAddress = "dvdstelt@nospam.gmail.com";
msg.Subject = "Test mail";
msg.Body = "Wow, is this going to work?";
svc.SubmitMessage(msg);
Isn't that extremely simple?! The only difference is in the configuration. This proves a lot of what's WCF is in the configuration.
Test MSMQ by stopping the service and just running the client. You can see the message in MSMQ. Start your host and it should disappear almost immediately.
Adel Khalil wrote a post about Mark Miller's latest post on his weblog. And now I'm continuing the linking fest! :)
The woman on the left is a super model named Sara. The reason Mark Miller is blogging about Sara is that she was at DevConnections in Las Vegas. You could challenge her in a coding contest. Interesting was that she had never touched a PC before in her life! Six hours of training in DevExpress' CodeRush and she can beat any developer out there, not using CodeRush! Isn't that fantastic?! See the video for a little proof. You could also go watch Gina code an Outlook style app in under 30 minutes.
Jim Holmes practiced his code using ReSharper but failed to win from Sara! Read about it here. Don't forget to read the comments, where some ReSharper developer comes around and comments : "Still, our tool is better". The arrogance! :) DevExpress challenged him to come by and test his tool against Sara and CodeRush. Way to go DevExpress!
I think the idea to compete against a super model in a coding contest is an excellent idea and proves their product is worth taking a look. Especially since so many people think ReSharper is the only way to go.
Everyone who owns Vista knows UAC by now. Everyone also knows it's not smart to turn it off, but still a lot of people do so. It's not easy for Microsoft to make their customers happy, is it now?! ;-)
Anyway, a smarter solution is something I read on a weblog a long time ago, but I keep forgetting who actually wrote it. You won't turn off UAC but you no longer need to respond to three separate dialogs anymore. I needed it again and started my search, to find it again on Tim Sneath his weblog, right here.
For personal reference and to inform others I'm posting it here again, in short form.
Thanks Tim Sneath!
It seems Microsoft is setting up a new site called "Visual Studio Gallery", but haven't said anything yet. The site is live though. I've searched through my entire RSS feed database but could not find a single link to this website. :-)
The site holds a large amount of products and extensions that aid Visual Studio developers in their daily work. It's really nice to have a site like this. I only hope, before this site will go 'live', they'll implement some rating options and perhaps add a review ability so you'll know a bit faster what the best tools are.
Of course Reflector would rank as the best tool ever, _if_ they'd put it online! Can't believe it's not online yet! Where's the ability to upload?! ;-)
Update: Jeroen Vos pointed out there were no links yet! Here it is : http://www.visualstudiogallery.com/
I just finished the two day Professional Agile Programming course in which I introduced developers to some best practices and such, including a view over some good practices from methodologies (XP/Scrum/MSF). For this course I've been in contact with DevExpress about a tool I wanted to show; Refactor! Pro. Another tool is FinalBuilder, but that's for another post.
It's unbelievable what you can do with Refactor! Pro and how easy it can make your life, when you know how to use the tool. Not only in this training, but in others as well, I explain people the [wikipedia:Single responsibility principle] and how to use the [wikipedia:Extract Method] refactoring to achieve this. Or how you can remove [wikipedia:Duplicate code] using the Extract Method refactoring. Take for example this simple for-loop.
for (int i = 0; i < 20; i++)
CheckSomeThing(i, 12);
In a demo I used this loop multiple times, but with different numbers. Now imagine you select this code and perform the Extract Method refactoring. You'll end up with this.
private static void ExtractedMethod()
But I want the numbers 20 and 12 passed as parameters. With normal Visual Studio, you'd have to undo this. Then create two integer variables and replace the numbers 20 and 12 with those integers. Then extract it to a method and replace the variables (which are passed into the ExtractedMethod method) with the actual numbers again. Not with Refactor! Pro, because it has two additional refactorings
With the first one we can extract the number 20 from the loop by selecting it and performing the refactoring.
After that you can promote the variable to a parameter.
The original call the the extracted method will also be synchronized, meaning that the number 20 will be passed as an argument into the extracted method. If you do the same for the number 12, you end up with the following code. The first line is the calling method.
private void SomeMethod() { ExtractedMethod(20, 12); }
private static void ExtractedMethod(int numberOfTimes, int number)
for (int i = 0; i < numberOfTimes; i++)
CheckSomeThing(i, number);