Tag Archives: code - Page 2

Create SQLCE database in code

Posted by on 2012/04/03 No comments

This is not exactly something new or breaking any new boundaries but it could be a useful tip for anyone that wants to make use of SQLCE databases from a small .Net application. There are some great tools and utilities to create a new SQL compact edition database using some UI but what if you want to automate the process or allow a client app to create a new database ‘on the fly’ or when needed?

It turns out it is not really that hard at all. All you need is a reference to the System.Data.SqlServerCe assembly – which is installed when you run the Sql server compact installer. Then you simply need to use the following code (example):

string connStr = string.Format(“Data Source={0};File Mode=Shared Read;Persist Security Info=False”, sdfFilePath);
using (System.Data.SqlServerCe.SqlCeEngine SQLCEDB = new System.Data.SqlServerCe.SqlCeEngine(connStr))
{

SQLCEDB.CreateDatabase();

}

That will create a new ‘blank’ database. What if you want to also create tables inside it? That is also very simple. I created a simple library to do that – it simply takes an input script and runs each command one by one. Each statement must be separated by a ‘GO’ statement that is on its own line.

e.g.

CREATE TABLE [TableA] (
[SomeID] int NOT NULL IDENTITY (1,1)
, [Desc] nvarchar(100) NOT NULL
);
GO
CREATE TABLE [TableB] (
[OtherID] int NOT NULL IDENTITY (1,1)
, [Desc] nvarchar(100) NOT NULL
);
GO
…

The class that handles it looks like this:

public delegate void CreateCmndResponseMsgDelegate(string createCMND, string message);
public class CreateSQLCEDB : BaseSQLCEDAL
{

private List<string> createCMDs = new List<string>();
public int ErrorCount { get; set; }
public event CreateCmndResponseMsgDelegate CreateCmndResponseMsg;
private void RaiseCreateCmndResponseMsg(string createCMND, string message)
{

if (CreateCmndResponseMsg != null)
{

CreateCmndResponseMsg(createCMND, message);

}

}
public void CreateCommandsFromScript(string script)
{

string[] createCmds = script.Split(new string[] { “\r\nGO\r\n” }, StringSplitOptions.RemoveEmptyEntries);
createCMDs.AddRange(createCmds);

}
public bool RunCommands()
{

ErrorCount = 0;
base.OpenConnection();
foreach (string cmndStr in createCMDs)
{

try
{

base.ExecuteNonQuery(cmndStr);

}
catch (Exception ex)
{

ErrorCount++;
RaiseCreateCmndResponseMsg(cmndStr, ex.Message);

}

}
base.CloseConnection();
return ErrorCount == 0;

}

}

Note that the base class ‘BaseSQLCEDAL’ is simply a wrapper class to handle all interactions with SqlCeConnection, SqlCeCommand etc.

See CreateSQLCEDB for full example code.

HTMLWriter 1.5

Posted by on 2012/03/08 No comments

I’ve updated this little utility library by simplifying some method overloads plus added a static method for getting escaped html special characters.

To make the code more maintainable for the special characters (like adding new ones in the future) I’ve added an enum that use some .Net ‘attributes’ so you can see, use and reference everything about these ‘things’ in one place. To do that I had to add a special helper class so the enum can carry some additional properties (like the actual strings for the special character and the escaped string). To do this I created a custom attribute class called ‘StringValueAttribute’ like this:

public class StringValueAttribute : Attribute
{

public string StringExpandedValue;
public string StringValue;

public StringValueAttribute(string value, string stringExpandedValue)
{

this.StringValue = value;
this.StringExpandedValue = stringExpandedValue;

}

}

To retrieve the associated values from an enum another static helper class is needed: StringValueAttributeUtil

internal static class StringValueAttributeUtil
{

public static string GetStringValue(Enum value)
{

// Get the type
Type type = value.GetType();

// Get fieldinfo for this type
System.Reflection.FieldInfo fieldInfo = type.GetField(value.ToString());
// Get the stringvalue attributes
StringValueAttribute[] attribs = fieldInfo.GetCustomAttributes(
typeof(StringValueAttribute), false) as StringValueAttribute[];
// Return the first if there was a match.
return attribs.Length > 0 ? attribs[0].StringValue : null;

}
public static string GetStringExpandedValue(Enum value)
{

// Get the type
Type type = value.GetType();

// Get fieldinfo for this type
System.Reflection.FieldInfo fieldInfo = type.GetField(value.ToString());
// Get the stringvalue attributes
StringValueAttribute[] attribs = fieldInfo.GetCustomAttributes(
typeof(StringValueAttribute), false) as StringValueAttribute[];
// Return the first if there was a match.
return attribs.Length > 0 ? attribs[0].StringExpandedValue : null;

}

}

 

Then to actually make use of this helper class you decorate the enum like this: (showing only part of it…)

public enum HTMLSpecialCharacter
{

[StringValue(“<“, “&amp;lt;”)]
LessThanSign,
[StringValue(“>”, “&amp;gt;”)]
GreaterThanSign,
[StringValue(“\””, “&amp;quot;”)]
DoubleQuote,
[StringValue(“‘”, “&amp;#39;”)]
SingleQuote,

…

}

Happy HTML’ing 🙂

Update: Version 1.6 has now been released since…

QuickMon 2.6 released

Posted by on 2012/02/28 No comments

Another quick note – QuickMon 2.6 has been released. The biggest change is the introduction of some parallel threading when calling collectors – using the .Net 4.0 parallel extensions. No new collectors or notifiers added yet.

Mail Aggregator Service update

Posted by on 2012/02/28 No comments

Just a quick update. I’ve now added a ‘Sql aggregator’ as well. This means there are now 2 ways aggregated messages can be sourced – from text files or Sql server data.

The ‘sql aggregator’ can use any existing table with messages by simply adding a bit field that indicates whether or not the message (in the table) has already been sent or not. The aggregator simply calls one stored proc (or if you want to a plain tsql statement can be used but I don’t recommend it) to retrieve any messages that must be send. This list must contains a message Id, ‘to’ address, subject and a body field. How you get by them combining fields, texts and other stuff is up to the creator of the stored proc. Once sent the ‘sql aggregator’ then calls a second stored proc passing the message Id to mark the message as sent. The sql parameter must be named ‘@Id’ and be an ‘int’.

An example sql script to modify the QuickMon sql notifier table plus two stored procedures are included in the sample project.

Eample source: MailAggregator Source code

Mail Aggregator Service

Posted by on 2012/02/24 No comments

One problem with creating very useful apps sometimes is that you end up making more work or trouble for yourself 🙂 My QuickMon tool has been created to help me monitor some services and ‘stuff’ which alerts me if things go pear shape… That is all good and wonderful until you (me) realize I’m beginning to ‘spam’ myself with too many messages (since I’m using smtp alerts…). Now they are all ‘valid’ alert messages and I still need to be aware of them but at some point there are just too many individual messages flying around. QuickMon alerts are quite configurable and if you want to some suppression is possible but managing this is almost a job on its own since things change with time, conditions change etc. etc. etc.

So I started thinking (an act that in itself could be dangerous at times) about how to solve the problem by grouping messages before they get sent. QuickMon itself raise alerts and immediately send them off – fire and forget style. Sure, I can build a new notifier that tries to do some batching (not sure how I can do that yet) or modify the QuickMon service itself to ‘hold on’ to alerts for time periods but that won’t be a generic solution any more. A better solution is to build a separate ‘generic’ solution that can also be used from QuickMon plus any other apps I might still create that need smtp messages to be sent out automatically (system level stuff).

This is where the idea of the MailAggregator service started. In itself it is a very simple thing – it runs, periodically checking for all messages from some ‘source’ that need to be send to the same destination, groups them as one message and sends it. The ‘source’ could be a database table or directory with files (or possibly any other place that can store a bunch of messages that can be retrieved at one time. For my own proof of concept I created a simple working example that checks for text files in a directory, use the content as the message content, group/append it, send it and then either deletes or renames the original files.

I decided to make the service a bit more dynamic in that you can add aggregators – implementing a standard interface so adding new aggregator source types in the future would be easy (like Text file and Sql table I already mentioned). Also, the service can run multiple aggregators (of the same or different types) at the same time – each serviced on its own thread (I just love threading but yeh, you have to be careful!). As with QuickMon each instance (or host as I termed it here) can have its own separate config.

So the end result of this little experiment is a working (or should I say workable) example that can already (with some electronic duck tape) be used with QuickMon alerts.

Basic architecture overview

  • MailAggregatorService

The main component of this little project is again a simple ‘Windows Service’. The service on its own is actually very simple – really just a shell that contains the aggregator hosts which calls the aggregator  libraries. A host instance gets created for every config entry (in an array) as specified in the config file.

This service also contains my code for self registration (to install it with the -install command line)

  • MailAggregatorHost

The MailAggregatorHost (class) is the container that holds the ‘aggregator source type library’. Because each source type implements a standard interface the host does not know or care which specific aggregator it is dealing with. The host is the entity that runs in a permanent loop calling the GetMessages() method of the aggregator, sends the smtp messages and then waits until the next iteration.

  • IMailAggregatorSource

Each mail aggregator source library implements the IMailAggregatorSource interface for a particular resource type a.k.a. like Text files, Sql table or whatever. The interface itself is fairly simple and has the following members:

    • event AggregatorError
    • string GetConfigIdentifierType() – will be use later to dynamically load source types
    • bool SetConfig(string config) – configure the source type with specified config
    • List<MailAggregatedMessage> GetMessages() – get the messages dub…
    • string LastError { get;set;} – not used yet but one day….
  • MailAggregatedMessage
This is just a container to hold and pass the aggregated messages from the source type to the host.
  • MailAggregatorFFSource
This is the first example implementation of the IMailAggregatorSource interface that check for plain text files (txt or anything you specify in the config) and groups them for messages. This implementation optionally checks for lines starting with ‘TO:’ and ‘SUBJECT:’. It is possible to specify default values that will be used if these lines are not available. The rest of the file is taken as the ‘body’ of the message.
By default it only groups ‘messages’ by the ‘TO’ address but you can also specify that the subject (plus TO address) will be used for the grouping. Grouped messages simple get appended.
A maximum messages size can be specified so that when a grouped set of ‘messages’ exceed the specified size a new MailAggregatedMessage instance will be created.
Once all the reading and grouping of messages have been done the processed files get either deleted or renamed as per config. When using the renaming option there is another choice – if the renamed file already exists it can either be overwritten or appended. ‘Done’ files are renamed to the original file name plus ‘.done‘.

Example source code

If you like to play with the source code yourself you need VS2010 – it requires .Net 4 (client framework)

MailAggregator

Parallel threading and Control.BeginUpdate or EndUpdate

Posted by on 2012/02/22 No comments

Yesterday I learned the hard (old) way that using BeginUpdate and EndUpdate in a multithreading environment (Windows Forms) don’t work so ‘lekker’ together. In hindsight, I should have guessed that this would be a problem but 20/20 hindvision really doesn’t help after the fact.

While making enhancements to my QuickMon tool to facilitate multithreading (using the .Net 4 parallel extensions) while calling collectors I started experiencing weird and unpredictable freezes or application hangs – app seems to be hanging but some parts of the UI still updates but the window cannot be moved, closed (in the normal way) etc. I tried various ways using mutexes, Control.Invokes, extra timers and duck tape to try and solve the problems. Only once I removed all my BeginUpdate/EndUpdates did the freezing issue disappear.

As mentioned before it all made sense afterwards. The problem is that when multiple background threads make calls back to the UI thread (yes, even using the proper Control.Invoke() method) that has a BeginUpdate in the beginning and later an EndUpdate there is no guarantee that every BeginUpdate is followed by its EndUpdate. That could mean some EndUpdates might never be reached properly.

The solution (for now) was to just disable all BeginUpdate/EndUpdates. Of course, a more proper design change would be to use some kind of buffer and a separate update UI routine that function outside the callbacks coming from the multiple threads collecting data. This unfortunately is not a small change and cannot be done in just a day or two. In a future iteration I’ll try to implement something like this for the QuickMon Windows client. The Windows service version of the tool wasn’t affected and work as is with the multi-threading change.

Hiding parts of html page on printing

Posted by on 2012/01/27 1 comment

If you need to create a web page on which some parts should not be visible when it is printed (or even print-previewed) you can use the following CSS:

@media print
{

.notPrint
{

display:none;

}

}

Then in the html you can specify something like this:

<div class=’notPrint’>This is not visible in printing</div>

This is useful if you have things like buttons and other stuff that is not applicable when printing.

IP address to table sql function

Posted by on 2012/01/17 No comments

I have a requirement to do some stuff with a table that contains ip addresses like grouping per subnets etc. Doing this with straight tsql is tricky (if possible at all) so I created a sql server function that takes the ip address field and breaks it down to a set (table) of 4 values. It’s not perfect and brings another type of complexity of its own but at least you can compare the separate bits of the ip address as unique parts. Also, it doesn’t really have any error checking built in.

create FUNCTION [dbo].[ufn_IPAddressToTable]
(

@IpAddress VARCHAR(15)

)
RETURNS @IpTable TABLE (part1 int, part2 int, part3 int, part4 int)
AS
BEGIN

DECLARE @part1 int, @part2 int, @part3 int, @part4 int
IF (not @IpAddress is null)
BEGIN

if (CHARINDEX(‘.’, @IpAddress) > 0)
begin

set @part1 = CONVERT(int, substring(@IpAddress, 0, CHARINDEX(‘.’, @IpAddress)))
set @IpAddress = substring(@IpAddress, CHARINDEX(‘.’, @IpAddress) + 1, 100)

if (CHARINDEX(‘.’, @IpAddress) > 0)
begin

set @part2 = CONVERT(int, substring(@IpAddress, 0, CHARINDEX(‘.’, @IpAddress)))
set @IpAddress = substring(@IpAddress, CHARINDEX(‘.’, @IpAddress) + 1, 100)

if (CHARINDEX(‘.’, @IpAddress) > 0)
begin

set @part3 = CONVERT(int, substring(@IpAddress, 0, CHARINDEX(‘.’, @IpAddress)))
set @IpAddress = substring(@IpAddress, CHARINDEX(‘.’, @IpAddress) + 1, 100)
set @part4 = CONVERT(int, @IpAddress)

end

end

end
if (not(@part1 is null or @part2 is null or @part3 is null or @part4 is null))

insert @IpTable(part1, part2, part3, part4)
values (@part1, @part2, @part3, @part4)

END
RETURN

END

As an example you can use it like this:

declare @IpAddress varchar(15)
set @IpAddress = ‘127.0.0.1’
select * from dbo.ufn_IPAddressToTable(@IpAddress)

Or if you have a table with ip addresses

with ComputerIps(Area, IpPart1, IpPart2, IpPart3)
as
(

select  c.Area,
(select top 1 part1 from dbo.[ufn_IPAddressToTable](c.IpAddress)) as Part1,
(select top 1 part2 from dbo.[ufn_IPAddressToTable](c.IpAddress)) as Part2,
(select top 1 part3 from dbo.[ufn_IPAddressToTable](c.IpAddress)) as Part3
from Computers c
where not (c.IpAddress is null)  and LEN(c.IpAddress) > 0

)
select Area, IpPart1, IpPart2, IpPart3, COUNT(*) as [Computers]
from ComputerIps
group by Area, IpPart1, IpPart2, IpPart3
order by Area, IpPart1, IpPart2, IpPart3

Another problem is that it may be slow when the source table becomes large. At least it helps a bit.

Adding an user to group in AD

Posted by on 2012/01/10 No comments

I was helping a colleague that has no programming background with a little tool he needs to create to add users to an AD group. The ‘tricky’ part is that he started using VB (.net) and my VB skills is a bit (or a lot) rusty…

So I set out to help him but I hit a little snag with the step of actually adding a user to the group.  I got an exception ‘The server is unwilling to process the request‘ when trying to use the following type of code:

de = new DirectoryEntry(groupDn);
de.Properties[“member”].Add(userDn); //<!– breaks here
de.CommitChanges();
I suspected a security issue but even running this code under raised privileges (admin) does not help. The solution is to rather call the COM add method directly:
de = new DirectoryEntry(groupDn);
de.Invoke(“Add”, New Object() {userDn})
Note: groupDn and userDn must be the full LDAP path to the group and user objects.

DirectorySearcher and SizeLimit

Posted by on 2011/12/08 No comments

Seems like each time you actually wants to use some MS technology for real you find a serious bug in it…

If you query active directory to get a list of say, all the machines on the network and the list happens to be bigger than a 1000 items there is a problem using DirectorySearcher. There is a property called SizeLimit but even if you set it to anything larger than a 1000 it always only return a 1000 items.

Fortunately (like a lot of other cases) there is a way around it. DirectorySearcher have another property for PageSize which if you set that then the size limit is ignored and you can access more than the 1000 items.

e.g.

string filter = “(objectCategory=Computer)”;
using (DirectorySearcher searcher = new DirectorySearcher(filter))
{
//searcher.SizeLimit = 2000; //ignored
searcher.PageSize = 1000;
SearchResultCollection matches = searcher.FindAll();
foreach (SearchResult match in matches)
{
DirectoryEntry de = match.GetDirectoryEntry();
…
}
}