How to split SQL file by GO statement

In some situations when using scripts to create/update database in code, I had to split input SQL script file by GO statements, as SqlCommand doesn’t really understand it. But in some place, I’ve came to a strange-looking code that does exactly that but… in really weird and NOT correct way. So here we go.

How NOT to write:

//separator char not used in TSQL
char ch = '^';
 
//Ed: str is the text from SQL batch file
str = str.Replace("GOTO", "GGGG");
str = str.Replace("Goto", "GGGG");
str = str.Replace("\r\nGO\r\n", ch.ToString()) ;
str = str.Replace("\r\nGO \r\n", ch.ToString()) ;
str = str.Replace("\r\nGO", ch.ToString()) ;
str = str.Replace(" Go\r\n", ch.ToString());
str = str.Replace(" go\r\n", ch.ToString());
str = str.Replace(" Go \r\n", ch.ToString());
str = str.Replace(" go \r\n", ch.ToString());
 
str = str.Replace(" GO\n", ch.ToString());
str = str.Replace("\nGO\n", ch.ToString());
str = str.Replace(" go\n", ch.ToString());
str = str.Replace("\ngo\n", ch.ToString());
 
str = str.Replace("\r\ngo", ch.ToString()) ;
str = str.Replace("\r\nGo", ch.ToString()) ;
 
//return GOTO statements
str = str.Replace("GGGG", "Goto");
            
return  str.Split(new char[]{ch});

Weird huh? :) All this instead of simple Regular Expression, something like this one (worked for me so far):

return Regex.Split(str + "\n", @"^\s*GO\s*$", RegexOptions.IgnoreCase | RegexOptions.Multiline);

PowerShell and untrusted domains

I have a few PowerShell scripts that execute various automation tasks on our build machine, like sanity test of the latest build for example. The common scenario is to revert-VMWare-snapshot, copy latest build to target machine, execute whatever needs to be executed. This worked fine until I was asked to do the same tests on different domain, which is not in trust with domain of our build machine.

At first glance, I thought that –Credentials parameter in PowerShell will do the trick, but it turned out it doesn’t work at all for functions like Copy-Item or Set-Content. I thought “ok, let’s check PowerShell 2, I’m sure it is implemented now”. To my surprise, I still get the same error “The provider does not support the use of credentials”. So I was stuck…

At some moment, I have remembered there was a command that allowed storing credentials for network connections. After a short search, I have found it and it indeed worked! The command I’m talking about is “net use”. The usage is very easy as well:

net use \\server\share /user:domain\username password

Any PowerShell command I needed (copy, delete, set content, etc.) worked fine after this call.

Technorati Tags: ,

Setting property by name with automatic type conversion

I liked the idea of binding to properties in WPF or Workflows. So implemented something similar (although less powerful) in my code: I had a list of tasks and their data context, so I needed somehow to “bind” data from context to properties of tasks. As a part of implementation, I have created a method that allowed me to take an object (task), take one of its properties by name and assign that property a value. Everything in reflection, since property name nor its value is not known at design time and only evaluated at runtime.

Remembering that my value is always a string, I had to implement some sort of automatic conversion to target type from string. Later I have extended the method to accept any type, not just string.

Implementation was split into two different methods, as I needed type conversion as a standalone code elsewhere. Here are two methods:

 /// <summary>
 /// Fail-safe method that converts data into another type. See Remarks for more information.
 /// </summary>
 /// <param name="data">Data to convert</param>
 /// <param name="targetType">Target type. Must not be <c>null</c></param>
 /// <param name="newValue">New value</param>
 /// <returns><c>true</c> if data was converted successfully, <c>false</c> otherwise</returns>
 /// <exception cref="ArgumentNullException"><c>targetType</c> is <c>null</c> which is not expected</exception>
 /// <exception cref="InvalidCastException">When failed to convert data to specified type</exception>
 /// <remarks>
 /// This method tries to convert data of one type to another by performing following operations in specified
 /// order:
 /// <list type="bullet">
 ///     <item>
 ///         <description>If <paramref name="data"/> is <c>null</c>, result is also <c>null</c>.</description>
 ///     </item>
 ///     <item>
 ///         <description>If <paramref name="targetType"/> is assignable from type of <paramref name="data"/>, result is the same as data.</description>
 ///     </item>
 ///     <item>
 ///         <description>
 ///             If <paramref name="targetType"/> is enumeration and type of <paramref name="data"/> is string,
 ///             enumeration parsing is made using <see cref="Enum.Parse"/> method.
 ///         </description>
 ///     </item>
 ///     <item>
 ///         <description>
 ///             If all previous conditions not met, type is converted to another type by calling <see cref="Convert.ChangeType"/> method.
 ///         </description>
 ///     </item>
 ///     <item>
 ///         <description>
 ///             If conversion fails with <see cref="InvalidCastException"/> exception, <paramref name="targetType"/> in
 ///             inspected for presence of constuctor that accepts parameter of type of <paramref name="data"/>. In case
 ///             such constructor is found, it is called and its result is returned. Otherwise exception is rethrown.
 ///         </description>
 ///     </item>
 /// </list>
 /// </remarks>
 public static bool TryConvertData(object data, Type targetType, out object newValue)
 {
     newValue = null;
  
     if (targetType == null)
         throw new ArgumentNullException("targetType");
  
     bool ret = false;
     try
     {
         if (data == null)
         {
             newValue = null;
         }
         else if (targetType.IsAssignableFrom(data.GetType()))
         {
             newValue = data;
         }
         else if (targetType.IsEnum && data is string)
         {
             newValue = Enum.Parse(targetType, data.ToString(), true);
         }
         else
         {
             var converter = TypeDescriptor.GetConverter(targetType);
             if (converter.CanConvertFrom(data.GetType()))
                 newValue = converter.ConvertFrom(data);
             else
             {
                 //failed to convert data, let's see if we can find a suitable constructor - our last resort
                 ConstructorInfo ctor = targetType.GetConstructor(new Type[] { data.GetType() });
                 if (ctor != null)
                     newValue = ctor.Invoke(new object[] { data });
                 else
                     return false;
             }
         }
         ret = true;
     }
     catch { }
     return ret;
 }
  
 /// <summary>
 /// Sets a valud of public, non-static property or field for given object, converting data to target type
 /// as needed.
 /// </summary>
 /// <param name="target">Object instance to set property or field value on.</param>
 /// <param name="propertyName">Name of public, non-static property or field</param>
 /// <param name="value">Value for property</param>
 /// <param name="throwOnError">Whether throw when error occurs or continue.</param>
 /// <exception cref="ArgumentNullException">When <c>target</c> is <c>null</c>. This exception is thrown regardless of throwOnError parameter value.</exception>
 /// <exception cref="InvalidCastException">When value cannot be converted to target type. Thrown only when <b>throwOnError</b> is <c>true</c></exception>
 /// <exception cref="InvalidOperationException">When no property or field found with specified name. Thrown only when <b>throwOnError</b> is <c>true</c></exception>
 public static void SetPropertyValue(object target, string propertyName, object value, bool throwOnError)
 {
     if (target == null)
     {
         throw new ArgumentNullException("target");
     }
  
     Type type = target.GetType();
  
     //check if property with such name exists in target type
     Type memberType = null;
     PropertyInfo propertyInfo = type.GetProperty(propertyName, BindingFlags.Instance | BindingFlags.Public);
     if (propertyInfo != null && propertyInfo.CanWrite)
         memberType = propertyInfo.PropertyType;
  
     //if no such property, we check for public field
     if (memberType == null)
     {
         FieldInfo fieldInfo = type.GetField(propertyName, BindingFlags.Instance | BindingFlags.Public);
         if (fieldInfo != null)
             memberType = fieldInfo.FieldType;
     }
  
     //if we couldn't find exact match, maybe try case-insensitive?
     if (memberType == null)
     {
         propertyInfo = type.GetProperty(propertyName, BindingFlags.Instance | BindingFlags.Public | BindingFlags.IgnoreCase);
         if (propertyInfo != null && propertyInfo.CanWrite)
         {
             memberType = propertyInfo.PropertyType;
             propertyName = propertyInfo.Name;
         }
  
         //if no such property, we check for public field
         if (memberType == null)
         {
             FieldInfo fieldInfo = type.GetField(propertyName, BindingFlags.Instance | BindingFlags.Public | BindingFlags.IgnoreCase);
             if (fieldInfo != null)
             {
                 memberType = fieldInfo.FieldType;
                 propertyName = fieldInfo.Name;
             }
         }
     }
  
     if (memberType != null)
     {
         object targetValue = null;
         if (TryConvertData(value, memberType, out targetValue))
         {
             type.InvokeMember(propertyName, BindingFlags.Public | BindingFlags.Instance | BindingFlags.SetProperty | BindingFlags.SetField, null,
                 target, new object[] { targetValue });
         }
         else if (throwOnError)
         {
             throw new InvalidCastException(String.Format("Cannot convert from '{0}' to '{1}'", value.GetType().FullName, memberType.FullName));
         }
     }
     else if (throwOnError)
     {
         throw new InvalidOperationException(String.Format("Property or field '{0}' not found on type '{1}'", propertyName, type.FullName));
     }
 }
  
Technorati Tags: ,

Setting WPF Dialog owner from within WinForms application

I had some WPF dialog that I need to show from older WinForms application, or more precisely from some WinForms UserControl. The problem was to set Owner of that dialog, GetWindow() didn’t work (of course) and leaving dialog without an owner, made it possible to disappear when user switch from application to application. And I didn’t want to show it in task bar either.

Thankfully, there is a very easy way, which, for some reason, I didn’t find quite fast – use WindowInteropHelper class. Here is an example:

   1: MyWpfDialog dialog = new MyWpfDialog();
   2:  
   3: //remember, this is WinForms UserControl and its Handle property is
   4: //actually IntPtr containing Win32 HWND.
   5: new System.Windows.Interop.WindowInteropHelper(dialog).Owner = this.Handle;
   6: dialog.ShowDialog();
Technorati Tags: , ,

iPhone 3GS

Go ahead and read it - Stephen Fry’s review at http://www.guardian.co.uk/technology/2009/jun/19/stephen-fry-iphone-3gs-review. Pay attention to the title of the review: “iPhone, therefore I am” – brilliant :)

From my side, I’m sitting here, in Israel and waiting for iPhone to be officially supported by local operators. Seems that at least 2 of them have conformed that in a few months they are getting 3GS version, so we’ll see what the prices will be and maybe I’ll get one for myself too – getting tired of my current Sony-Ericsson P1i.

Technorati Tags: , , ,

Sending E-mail From PowerShell v1.0

While in PowerShell 2 there will be a “Send-Mail” cmdlet (as I heard), I needed something for current version. Here is the self-explaining PowerShell script for sending mails with optional attachments and multiple recipients.

Remember to change the hard-coded “mail.server.com” to your own mail server, otherwise nothing will work :).

##############################################################
# Send e-mail with optional attachments
#
function SendEmail
{
    param(
        $From,
        $To,
        $Subject,
        $Body,
        $Attach = $null
    )
    
    $msg = New-Object Net.Mail.MailMessage
    $msg.From = $From
    
    #Recipient address can be an array as well
    $addresses = $To
    if($To -isnot [Object[]])
    {
        $addresses = ([string]$To).Split(";")
    }
    
    foreach($singleAddress in $addresses)
    {
        $msg.To.Add($singleAddress)
    }
    
    $msg.Body = $Body
    $msg.Subject = $Subject
    
    if($Attach -ne $null)
    {
        if($Attach -is [String[]] -or $Attach -is [Object[]])
        {
            foreach($filePath in $Attach)
            {
                $att = New-Object Net.Mail.Attachment($filePath, "text/plain")
                $msg.Attachments.Add($att)
                Write-Output ([String]::Format("{0} attached", $filePath))
            }
        }
        elseif($Attach -is [string])
        {
            $att = New-Object Net.Mail.Attachment($Attach, "text/plain")
            $msg.Attachments.Add($att)
            Write-Output ([String]::Format("{0} attached", $Attach))
        }
        else
        {
            Write-Warning ([String]::Format("Attachment is of unknown type: {0}. Please use string or string/object arrays only", $Attach.GetType().Name))
        }
    }
    
    $client = New-Object net.Mail.SmtpClient("mail.server.com")
    $client.Send($msg)
    Write-Output "E-mail message sent"
}

Technorati Tags: , ,

Visual Studio 2008 template for Prototype console application

There have been several occurrences where I needed to have something quick UI, to check some set of classes. Building a whole Windows Forms or WPF application seemed to be overkill, specially when I will not be responsible for building UI later, or application will not need UI at all. So what we usually do? Right, create a command line utility, than another one and another one.. Most of them look exactly the same.

So some time ago I have created a Visual Studio project template, that will contain all I need in such console applications, and all I need – just put the code (or calls to another code) for your business logic and check it. Now I have decided to publish it as well, as I’ve been asked to send it few times by mail and people found it useful.

For more info and download link, see full article Prototype Project Template.

Technorati Tags: , ,

Replacing passwords on application logs

Many applications write various information into log files and sometimes this information is sensitive, e.g. contains user passwords. In my case, I knew that we are logging SQL statements with stored procedures calls and one of the parameters may contain user’s password. So I have been asked to replace such passwords with something neutral.

I’ve ended with following regular expression string (works only for stored procedures and alike call logs!):

(?<Pre>(pass(word)?|pwd)[^=']*=[^']*'?)(?<Target>[^']+)(?<Post>'?)

Technorati Tags: , ,

Everquest II News iGoogle gadget updated

I have updated my Everquest II News gadget to strip off all images that were breaking the view. Now the gadget looks more like a regular news feed – all text with a link to full article. Now I probably need to create such feed really and submit it to FeedBurner or something.

image

Update October 25: Go figure - just after I have updated the gadget, SOE made their news available via standard RSS. So I do not need the gadget anymore, woohoo! :)

Access Violation in DsBrowseForContainer on Windows Vista. The code

Since my post about DsBrowseForContainer problem in Vista, I have been asked several times to provide the code on solution. So here we go – below is the class that I use to browse for OU container, it is written to .NET 3.5, but can be easily adapted to earlier versions as well:

public sealed class OUPickerDialog
{
public OUPickerDialog()
{
Caption = "The container picker";
Title = "Select a container:";
}

#region Imports
// all strings in structure will be marshaled as LPWStr
[StructLayout(LayoutKind.Sequential, CharSet = CharSet.Unicode)]
private
struct DSBrowseInfo
{
public int structSize;
public IntPtr dlgOwner;

public string dlgCaption;
public string treeViewTitle;
public IntPtr rootPath;

// in/out string must be declared as String in struct/class, // not as StringBuilder public string path;
public int pathSize;

public int flags;
public IntPtr callback;
public IntPtr lParam;
public int returnFormat;

public string userName;
public string password;

public string objectClass;
public int objectClassSize;
};

[DllImport("dsuiext.dll", CharSet = CharSet.Unicode)]
private static extern int DsBrowseForContainerW(ref DSBrowseInfo info);

private const int DSBI_ENTIREDIRECTORY = 0x00090000;
private const int MAX_PATH = 256;
#endregion
#region
Properties
/// <summary>
/// Gets or sets text to display on dialog box caption

/// </summary>

public
string Caption { get; set; }

/// <summary>
/// Gets or sets title displayed above directory tree

/// </summary>

public string Title { get; set; }

/// <summary>
/// Gets or sets path to start browsing from

/// </summary>

public string RootPath { get; set; }

/// <summary>
/// Gets path selected by user or empty string if nothing has been selected
/// </summary>

public
string SelectedPath { get; private set; }

/// <summary>
/// Gets name selected by user or empty string if nothing has been selected
/// </summary>
public string SelectedName { get; private set; }
#endregion
#region Methods
/// <summary>
/// Displays a modal picker dialog and returns OU path as selected by user or empty string if nothing

/// has been selected
/// </summary>

/// <returns><c>true</c> if user selected a unit or <c>false</c> otherwise</returns>
public
bool? ShowDialog()
{
// initialize all members
SelectedPath = String.Empty;
SelectedName = String.Empty;
DSBrowseInfo dsbi = new DSBrowseInfo();

dsbi.structSize = Marshal.SizeOf(dsbi);
dsbi.dlgCaption = Caption;
dsbi.treeViewTitle = Title;
if (RootPath != null)
dsbi.rootPath = Marshal.StringToHGlobalAuto(RootPath);

dsbi.path = new string(new char[MAX_PATH]);
dsbi.pathSize = MAX_PATH;
dsbi.flags = DSBI_ENTIREDIRECTORY;

int status = DsBrowseForContainerW(ref dsbi);

bool? ret = false;
if (status == 1)
{
ret = true;
SelectedPath = dsbi.path;
SelectedName = CalcOUName(SelectedPath);
RootPath = Marshal.PtrToStringAuto(dsbi.rootPath);
}
return ret;
}

private static string CalcOUName(string ou)
{
int idx = 0;
do { idx = ou.IndexOf(@",", idx + 1);
} while (idx > 0 && ou[idx - 1] == '\\');
ou = ou.Substring(0, idx);
return ou;
}
#endregion
}

I have also uploaded a testing application I’ve been using. The code can be downloaded from here.

Technorati Tags: ,