WPF simulate mouse click in code behind

Let’s say there is an event captured on a control and you want to simulate a click of a button to fire whatever that button is bound to do, there are a few ways of doing this, one that I’ve used before is using an AutomationPeer:

AutomationPeer button1AP = UIElementAutomationPeer.CreatePeerForElement(button1);
(button1AP.GetPattern(PatternInterface.Invoke) as IInvokeProvider).Invoke();

Another option is to use:

button1.RaiseEvent(new RoutedEventArgs(Button.ClickEvent));

SQL Server: Bulk updates, bulk inserts and table valued parameters for arrays

When writing bulk updates or inserts into Oracle with ODP.NET you can use PLSQLAssociativeArray (example here) and you may search around for an equivalent array type in SQL Server but there isn’t one. Firstly this is disappointing, then you kind of get used to the idea, hopefully this post will help you on onto this path.
First off I thought it was wrong not to have an array type, “Oracle does, this is stupid” I thought. After a cup of tea, I put a couple of seconds thought into this and the best I could come up with is all the data we use in SQL is tabular data anyway and you could represent a PLSQLAssociativeArray as a table with one column right? So is it any loss? No, of course not, but the programmer in me is used to dealing with arrays.
However there is a drawback that I haven’t completely got over yet, and that is that you must define the type name of the SqlParameter, this must be a valid type, so randomly creating some SQL and firing it at the database isn’t an option. But I have other options in my mind, here’s some examples of bulk operations utilizing table valued parameters:

Create a table to manipulate:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Greetings](
	[PrimaryId] [int] NOT NULL,
	[FirstValue] [nchar](10) NULL,
	[SecondValue] [nchar](10) NULL
) ON [PRIMARY]

GO

Create a loosely typed DataTable and populate it with some data to insert into the table:

DataTable dt = new DataTable("Tim");
dt.Columns.Add(new DataColumn("FirstCol", typeof(int)));
dt.Columns.Add(new DataColumn("SecondCol", typeof(String)));
dt.Columns.Add(new DataColumn("ThirdCol", typeof(String)));

DataRow r1 = dt.NewRow();
r1["FirstCol"] = 1;
r1["SecondCol"] = "Hello";
r1["ThirdCol"] = "Hi";
dt.Rows.Add(r1);

DataRow r2 = dt.NewRow();
r2["FirstCol"] = 2;
r2["SecondCol"] = "Goodbye";
r2["ThirdCol"] = "Bye";
dt.Rows.Add(r2);

(brings back memories of .NET 1.1 doesn’t it)

Next up create a table valued type in SQL Server, of course you could keep well defined types in your database, but for this example I just want something “temporary”. Also this could be a generated string from the DataTable, look around someone might already be doing this, else it’s easy enough to roll your own

using (SqlCommand cmd = conn.CreateCommand())
{
    cmd.CommandType = CommandType.Text;
    cmd.CommandText =
        "BEGIN TRY"
        + " CREATE TYPE dbo.T1"
        + " AS TABLE ( [FirstCol] [int] NOT NULL, [SecondCol] [nchar](10) NULL, [ThirdCol] [nchar](10) NULL )"
        + " END TRY"
        + " BEGIN CATCH"
        + " END CATCH";
    cmd.ExecuteNonQuery();
}

So by this point in execution your app would have a type defined called dbo.T1, you could of course use a temporary generated name for this too. Note the TRY CATCH in there; ignoring errors.

Next do a bulk insert: (Note: SqlBulkCopy may be quicker, I’ve not checked)

using (SqlCommand cmd = conn.CreateCommand())
{
    SqlParameter p = new SqlParameter("@MyInputDT", SqlDbType.Structured);
    p.Value = dt;
    p.TypeName = "dbo.T1";
    cmd.Parameters.Add(p);
    cmd.CommandType = CommandType.Text;
    cmd.CommandText =
        "INSERT INTO dbo.Greetings (PrimaryId, SecondValue)"
        + " SELECT FirstCol, ThirdCol"
        + " FROM @MyInputDT";
    cmd.ExecuteNonQuery();
}

This to me is better than passing two arrays, a size parameter and looping though and inserting each. But we’ve got SqlBulkCopy, so you may not want to use this approach.

By this point in execution there would be two new entries in the dbo.Greetings table but I’ve deliberately missed out the FirstValue, so that will be NULL.

Let’s do a bulk update to fix it, also remove the “temporary” table valued type:

using (SqlCommand cmd = conn.CreateCommand())
{
    SqlParameter p = new SqlParameter("@MyUpdDT", SqlDbType.Structured);
    p.Value = dt;
    p.TypeName = "dbo.T1";
    cmd.Parameters.Add(p);
    cmd.CommandType = CommandType.Text;
    cmd.CommandText =
        "UPDATE Greetings"
        + " SET FirstValue = SecondCol"
        + " FROM @MyUpdDT"
        + " WHERE PrimaryId = FirstCol"
        + " BEGIN TRY"
        + " DROP TYPE dbo.T1"
        + " END TRY"
        + " BEGIN CATCH"
        + " END CATCH";
    cmd.ExecuteNonQuery();
}

So there’s a bulk insert and bulk update, I think this is pretty neat, but the requirement on having a well defined type is a gripe, now I’m over the lack of array type I want a var type now, shame.

See also Table-Valued Parameters in SQL Server 2008 (ADO.NET)

SQL Server: Updating XML fields – XPath, XQuery – #sql #sqlserver #xml #xpath #xquery

Further to my post SQL Server: Querying XML fields – XPath, XQuery, I’d like to expand on this and cover updating XML fields too.

Unfortunately it appears you can only do one update at at a time, so often looping is required, extracting data into temporary tables or complex XPath.
I’m going to cover the later as it’s one that isn’t so well written about.

The great thing about SQL Server is you can use the XPath String Functions, for example I’ve used the concatenation and substring functions before and here is a simple example to show how they can be used. Of note is also the neat way to include SQL variables into the XPath.

-- Create a temporary table for the example, drop if already exists

BEGIN TRY
DROP TABLE #T1
END TRY
BEGIN CATCH
END CATCH

CREATE TABLE #T1
(Val XML)

-- Stick in some XML data

INSERT INTO #T1 (Val) VALUES ('<root xmlns="http://tim.xsd">
  <branch>
    <leaf name="displayname" type="String">cycling</leaf>
    <leaf name="alternativename" type="String">is sometimes fun</leaf>
  </branch>
</root>')
INSERT INTO #T1 (Val) VALUES ('<root xmlns="http://tim.xsd">
  <branch>
    <leaf name="displayname" type="String">running</leaf>
    <leaf name="alternativename" type="String">is sometimes fun</leaf>
  </branch>
</root>')

-- Update the cycling row using a SQL variable and some functions (concat and substring)

DECLARE @timvar VARCHAR(MAX)
SELECT @timvar = ' is always '

UPDATE #T1 SET Val.modify('declare namespace TIM="http://tim.xsd";
  replace value of (/TIM:root/TIM:branch/TIM:leaf[@name="alternativename"]/text())[1]
  with fn:concat(
    "Everybody knows that ",
    (/TIM:root/TIM:branch/TIM:leaf[@name="displayname"]/text())[1],
    sql:variable("@timvar"),
    fn:substring((/TIM:root/TIM:branch/TIM:leaf[@name="alternativename"]/text())[1], 14, 3))
')
WHERE Val.query('declare namespace TIM="http://tim.xsd";(/TIM:root/TIM:branch[1]/TIM:leaf[@name="displayname"]/text())[1]')
.value('.', 'NVARCHAR(MAX)') = 'cycling'

-- Check the result

SELECT * FROM #T1

And the result is:

<root xmlns="http://tim.xsd">
  <branch>
    <leaf name="displayname" type="String">cycling</leaf>
    <leaf name="alternativename" type="String">Everybody knows that cycling is always fun</leaf>
  </branch>
</root>

<root xmlns="http://tim.xsd">
  <branch>
    <leaf name="displayname" type="String">running</leaf>
    <leaf name="alternativename" type="String">is sometimes fun</leaf>
  </branch>
</root>

Sheet, sheet, sheet #funny #excel

I’m not one for commenting on computer jokes, but today I burst out laughing in the office.

I quote
“Sheet… sheet, sheet, sheet, ah, that one…”
“Which?”
“That sheet!”
“OK, so now we’ve got the sheet, what do we do with him?”

Of course they were talking about Microsoft Excel Worksheets, but still, I couldn’t contain myself. I never thought Excel would make me smile.

SQL Server: Read only views via GROUP BY and UPDATE FROM pitfalls #sql #sqlserver

I often hit a pitfall when doing an UPDATE FROM when it is from a table INNER JOINed with itself but I only want to update one side of the join. However SQL Server would complain that it doesn’t know which Table_1 is to be considered anyway. Why would I be doing this? Imagine a table with a parent child relationship, so an id column and a parent id column that contains the id of the parent record. If I wanted to update children based on some criteria of the parent I would look at doing an UPDATE FROM with the table inner joining to itself on id equals parent id with the where criteria I wanted.

I get around this by using views, but it can be tricky to make sure I only update the child records and not the parent records, so I employ a mix of table and view. Unfortunately there is no such thing as a read only query or a read only view, but you can do a few tricks to make them non-updatable. For a view a GROUP BY (or DISTINCT) is one way and that is what I’ll show too.

Here are a couple of examples of things that work and things that don’t, unfortunately I came up with the example before this article, so it isn’t specific to a parent child relationship, but still a good example:

CREATE TABLE [dbo].[Table_1](
	[id] [int] NULL,
	[val] [int] NULL
) ON [PRIMARY]
GO

-- Three rows.
INSERT INTO Table_1 (id, val) VALUES (1, 5)
INSERT INTO Table_1 (id, val) VALUES (2, 10)
INSERT INTO Table_1 (id, val) VALUES (3, 15)
GO

CREATE VIEW View_2
AS
SELECT id, val FROM Table_1 GROUP BY id, val
GO

-- Fails, because of GROUP BY, effectively the view is read only, without the group by this would work.
UPDATE View_2
SET val = 1

-- Fails, which Table_1 is to be updated? It doesn't make sense does it?
UPDATE Table_1
SET val = 1
FROM Table_1 t1
INNER JOIN Table_1 t2
ON t1.Id != t2.Id
WHERE t1.id = 1

-- Only 1 row affected, View_2 is not updated but NOT because of GROUP BY, it is not updated because we are updating Table_1
-- The query actually returns two rows (or all three if you see what I mean), try it for yourself.
UPDATE Table_1
SET val = 1
FROM Table_1 t1
INNER JOIN View_2
ON t1.Id != View_2.Id
WHERE t1.id = 1

-- The exception to this is if Table_1 isn't in the FROM part. So the following statement updates all rows even though you might think View_2 should make this fail; nasty!
UPDATE Table_1
SET val = 1
FROM View_2

An alternative to a view is to use the WITH statement, like so:

WITH v AS (SELECT id FROM Table_1)
UPDATE Table_1
SET val = 1
FROM Table_1 t1
INNER JOIN v
ON t1.Id != v.Id
WHERE t1.id = 1

SQL Server: Convert a string into a hex string and back #sql #sqlserver

Worth noting this one down as it’s not entirely obvious how to convert a string into a hex string or back in SQL Server, fortunately it is easy, there is a built in function called fn_varbintohexstr to go one way, great, but nothing I know of going the other, searching online I see people writing their own.

SELECT MASTER.dbo.fn_varbintohexstr(CAST('Help' AS VARBINARY)) -- '0x48656C70'

But it is even easier than that in both ways, the above function is not required:

-- VARCHAR TO VARBINARY
SELECT CONVERT(VARBINARY(MAX), 'Help') -- 0x48656C70

-- VARBINARY TO VARCHAR
SELECT CONVERT(VARCHAR(MAX), 0x48656c70) -- 'Help'

-- VARCHAR TO (HEX) VARCHAR
SELECT CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), 'Help'), 1) -- '0x48656C70'

-- (HEX) VARCHAR TO VARCHAR
SELECT CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), '0x48656c70', 1)) -- Requires 0x, returns 'Help'
SELECT CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), '48656c70', 2)) -- Assumes 0x string, no 0x wanted, returns 'Help'

-- One to one pitfall

SELECT CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), '0x48656c70')) -- Same as adding 0 as last parameter, this is 1:1 conversion, so you get '0x48656c70' back

Oracle: Trim, replace, substring and indexof #oracle

Further to my post about SQL Server, here’s the differences I need to consider when scripting the same thing in Oracle:
REVERSE AND REPLACE are the same
LEN => LENGTH
LEFT => SUBSTR
CHARINDEX => INSTR
(String concatenation) + => ||
PATINDEX => REGEXP_INSTR (which is more powerful out of the box, of course you can do regex with SQL Server, but not out of the box)

SQL Server: Trim, replace, substring and indexof #sql #sqlserver

I often have to deal with file paths in SQL, whilst at home in C# I always scratch my head when I need to do string manipulation in SQL, so here’s a quick outline (via an example) of the string manipulation functions I often use and frequently forget.

DECLARE @pathStr AS NVARCHAR(255)
DECLARE @folderPathStr AS NVARCHAR(255)

SELECT @pathStr = 'C:\Temp\another dir\file%20name.ext'
--C:\Temp\another dir\file%20name.ext
--12345678901234567890123456789012345

SELECT @pathStr = REPLACE(@pathStr, '%20', '_')
-- @pathStr is now 'C:\Temp\another dir\file_name.ext'

-- substring 10 characters
SELECT @folderPathStr = LEFT(@pathStr, 10)
-- @folderPathStr is now 'C:\Temp\an'

-- But for folder path we want to take the filename off, so we need the last occurance of the backslash
-- CHARINDEX('\', @pathStr, 1) would return 3, which is the first occurance, if you want the last then reverse the string
-- to get the number of characters from the end, then take this away from the total
-- Note: CHARINDEX is 1 based, but passing 0 here would be fine too.
-- CHARINDEX('\', REVERSE(@pathStr), 1) would return 14
-- LEN(@pathStr) - CHARINDEX('\', REVERSE(@pathStr), 1) would return 19
-- So we can use this to do a substring of length 19, we do this via the LEFT function
SELECT @folderPathStr = LEFT(@pathStr, LEN(@pathStr) - CHARINDEX('\', REVERSE(@pathStr), 1))
-- @folderPathStr is now 'C:\Temp\another dir'

So to summarise, LEN, LEFT (also there is a RIGHT), REPLACE, REVERSE and CHARINDEX are in my normal armory.

Also, we must not forget PATINDEX, this is the pattern matching version of CHARINDEX, which you can use for endswith, startswith and contains:

SELECT * FROM docnames
WHERE PATINDEX('%.doc', val) > 0 -- All .doc files (endswith(.doc))
OR PATINDEX('me%', val) > 0 -- or filenames beginning me (startswith(me))
OR PATINDEX('%error%', val) > 0 -- or filenames contains error (contains(error))

SQL Server: LIKE clause syntax for reserved characters like percent % #sql #sqlserver

The most common thing I hit when trying to do a LIKE is trying to use the percent sign (%).
Fortunately the LIKE clause has a means of escaping the reserved characters, you simply stick a different letter in front of the reserved character then indicate that is the escape character, best to see this in an example:

SELECT * FROM
(
	SELECT 'me' AS val
	UNION ALL
	SELECT 'you' AS val
	UNION ALL
	SELECT 'me%20and%20you' AS val
) vals
WHERE val LIKE '%/%%' ESCAPE '/'

Here I am escaping the percent sign with a forward slash and the result would be ‘me%20and%20you’.
If I didn’t then no matter how many percent signs I used there is no combination to actually filter only those rows that contain a percent sign.

Shell32 Interop DLL Generation

As far as I’m aware you’ve got two choices when you want to call non .NET assemblies, DllImport or Interop.

For DllImport you’ll want to consult pinvoke.net.

But that DllImport syntax isn’t for everyone, some like to pretend we are working with .NET assemblies and want to use an Interop DLL, but how?

First you need to generate one using Tlbimp.exe (Type Library Importer)
For example
c:\Windows\System32>tlbimp shell32.dll /out:me.dll
Microsoft (R) .NET Framework Type Library to Assembly Converter 3.5.30729.1
Copyright (C) Microsoft Corporation. All rights reserved.
Type library imported to c:\Windows\System32\me.dll

Now just include the me.dll in your project (as a normal .NET assembly reference), stick a “using Shell32 = me;” / “Import Shell32 = me” in your source code and voila, Shell32.Folder etc is at your disposal.

Note: retrospectively thinking, don’t generate the dll into System32, you’re just asking for trouble.