Thursday, July 10, 2008

SQL statements

- Return primary key of the new inserted entry:

Method 1:
INSERT (..) VALUES (..) INTO tbl ; SELECT @@IDENTITY AS NewID;
Method 2 (preferred over Method 1 in some cases):
INSERT (..) VALUES (..) INTO tbl ; SELECT NEWID = SCOPE_IDENTITY();

An article for more.

- Create a table from another table:

SELECT * FROM tbl_old INTO tbl_new

- Create an empty table from an existing table:

SELECT * INTO tbl_new FROM tbl_old where 1 = 0

- Get column names in a table:

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.Columns where TABLE_NAME = 'tbl_name'

- Count rows in union statement:

SELECT count(*) FROM (
SELECT field1 FROM table1
UNION
SELECT field2 FROM table2
) as t

- COALESCE - returns first non-null value in list, or null if all values are null

See COALESCE (Transact-SQL).

- Insert from another table in batch mode
 
INSERT INTO MyTable (FirstCol, SecondCol) SELECT Col1, Col2 FROM MyTable2

Thursday, July 3, 2008

C# simulate mouse and keyboard events

This is achieved by calling windows API functions.
Also see
reference.
using System.Runtime.InteropServices;

public class Form1 : System.Windows.Forms.Form {
public enum VK : ushort
{
SHIFT = 0x10,
CONTROL = 0x11,
MENU = 0x12,
ESCAPE = 0x1B,
BACK = 0x08,
TAB = 0x09,
RETURN = 0x0D,
PRIOR = 0x21,
NEXT = 0x22,
END = 0x23,
HOME = 0x24,
LEFT = 0x25,
UP = 0x26,
RIGHT = 0x27,
DOWN = 0x28,
SELECT = 0x29,
PRINT = 0x2A,
EXECUTE = 0x2B,
SNAPSHOT = 0x2C,
INSERT = 0x2D,
DELETE = 0x2E,
HELP = 0x2F,
NUMPAD0 = 0x60,
NUMPAD1 = 0x61,
NUMPAD2 = 0x62,
NUMPAD3 = 0x63,
NUMPAD4 = 0x64,
NUMPAD5 = 0x65,
NUMPAD6 = 0x66,
NUMPAD7 = 0x67,
NUMPAD8 = 0x68,
NUMPAD9 = 0x69,
MULTIPLY = 0x6A,
ADD = 0x6B,
SEPARATOR = 0x6C,
SUBTRACT = 0x6D,
DECIMAL = 0x6E,
DIVIDE = 0x6F,
F1 = 0x70,
F2 = 0x71,
F3 = 0x72,
F4 = 0x73,
F5 = 0x74,
F6 = 0x75,
F7 = 0x76,
F8 = 0x77,
F9 = 0x78,
F10 = 0x79,
F11 = 0x7A,
F12 = 0x7B,
OEM_1 = 0xBA, // ',:' for US
OEM_PLUS = 0xBB, // '+' any country
OEM_COMMA = 0xBC, // ',' any country
OEM_MINUS = 0xBD, // '-' any country
OEM_PERIOD = 0xBE, // '.' any country
OEM_2 = 0xBF, // '/?' for US
OEM_3 = 0xC0, // '`~' for US
MEDIA_NEXT_TRACK = 0xB0,
MEDIA_PREV_TRACK = 0xB1,
MEDIA_STOP = 0xB2,
MEDIA_PLAY_PAUSE = 0xB3,
LWIN =0x5B,
RWIN =0x5C
}

#region Dll Imports

[StructLayout(LayoutKind.Sequential)]
struct MOUSEINPUT
{
public int dx;
public int dy;
public uint mouseData;
public uint dwFlags;
public uint time;
public IntPtr dwExtraInfo;
}

[StructLayout(LayoutKind.Sequential)]
struct KEYBDINPUT
{
public ushort wVk;
public ushort wScan;
public uint dwFlags;
public uint time;
public IntPtr dwExtraInfo;
}

[StructLayout(LayoutKind.Sequential)]
struct HARDWAREINPUT
{
public uint uMsg;
public ushort wParamL;
public ushort wParamH;
}

[StructLayout(LayoutKind.Explicit)]
struct INPUT
{
[FieldOffset(0)]
public int type;
[FieldOffset(4)]
public MOUSEINPUT mi;
[FieldOffset(4)]
public KEYBDINPUT ki;
[FieldOffset(4)]
public HARDWAREINPUT hi;
}

[DllImport("user32.dll", SetLastError=true)]
private static extern uint SendInput(uint nInputs, INPUT[] pInputs, int cbSize);

const int INPUT_MOUSE = 0;
const int INPUT_KEYBOARD = 1;
const int INPUT_HARDWARE = 2;
const uint KEYEVENTF_EXTENDEDKEY = 0x0001;
const uint KEYEVENTF_KEYUP = 0x0002;
const uint KEYEVENTF_UNICODE = 0x0004;
const uint KEYEVENTF_SCANCODE = 0x0008;
const uint XBUTTON1 = 0x0001;
const uint XBUTTON2 = 0x0002;
const uint MOUSEEVENTF_MOVE = 0x0001;
const uint MOUSEEVENTF_LEFTDOWN = 0x0002;
const uint MOUSEEVENTF_LEFTUP = 0x0004;
const uint MOUSEEVENTF_RIGHTDOWN = 0x0008;
const uint MOUSEEVENTF_RIGHTUP = 0x0010;
const uint MOUSEEVENTF_MIDDLEDOWN = 0x0020;
const uint MOUSEEVENTF_MIDDLEUP = 0x0040;
const uint MOUSEEVENTF_XDOWN = 0x0080;
const uint MOUSEEVENTF_XUP = 0x0100;
const uint MOUSEEVENTF_WHEEL = 0x0800;
const uint MOUSEEVENTF_VIRTUALDESK = 0x4000;
const uint MOUSEEVENTF_ABSOLUTE = 0x8000;


private MOUSEINPUT createMouseInput(int x, int y, uint data, uint t, uint flag) {
MOUSEINPUT mi = new MOUSEINPUT();
mi.dx = x;
mi.dy = y;
mi.mouseData = data;
mi.time = t;
//mi.dwFlags = MOUSEEVENTF_ABSOLUTE| MOUSEEVENTF_MOVE;
mi.dwFlags = flag;
return mi;
}

private KEYBDINPUT createKeybdInput(short wVK, uint flag)
{
KEYBDINPUT i = new KEYBDINPUT();
i.wVk = (ushort) wVK;
i.wScan = 0;
i.time = 0;
i.dwExtraInfo = IntPtr.Zero;
i.dwFlags = flag;
return i;
}

private short getCVal(char c) {
if (c >= 'a' && c <= 'z') return c - 'a' + 0x61;
else if (c >= '0' && c <= '9') return c - '0' + 0x30;
else if (c == '-') return 0x6D; // Note it's NOT 0x2D as in ASCII code!
else return 0; // default
}

///
/// Each time first move the upper left corner, then move from there.
/// x, y: pixel value of position.
///

private void sim_mov(int x, int y) {
INPUT[] inp = new INPUT[2];
inp[0].type = INPUT_MOUSE;
inp[0].mi = createMouseInput(0, 0, 0, 0, MOUSEEVENTF_ABSOLUTE | MOUSEEVENTF_MOVE);
inp[1].type = INPUT_MOUSE;
inp[1].mi = createMouseInput(x, y, 0, 0, MOUSEEVENTF_MOVE);
SendInput((uint)inp.Length, inp, Marshal.SizeOf(inp[0].GetType()));
}

private void sim_click()
{
INPUT[] inp = new INPUT[2];
inp[0].type = INPUT_MOUSE;
inp[0].mi = createMouseInput(0, 0, 0, 0, MOUSEEVENTF_LEFTDOWN);
inp[1].type = INPUT_MOUSE;
inp[1].mi = createMouseInput(0, 0, 0, 0, MOUSEEVENTF_LEFTUP);
SendInput((uint)inp.Length, inp, Marshal.SizeOf(inp[0].GetType()));
}

private void sim_type(string txt)
{
int i, len;
char[] c_array;
short c;
INPUT[] inp;

if (txt == null || txt.Length == 0) return;

c_array = txt.ToCharArray();
len = c_array.Length;
inp = new INPUT[2];

for (i = 0; i < len; i ++)
{
c = getCVal(txt[i]);

inp[0].type = INPUT_KEYBOARD;
inp[0].ki = createKeybdInput(c, 0);
inp[1].type = INPUT_KEYBOARD;
inp[1].ki = createKeybdInput(c, KEYEVENTF_KEYUP);

SendInput((uint)inp.Length, inp, Marshal.SizeOf(inp[0].GetType()));
}
}

private void sim_actions() {
sim_mov(x0, y0);
sim_click();
}

public Form1() {
InitializeComponent();
//sim_actions();
}

[STAThread]
static void Main() {
Application.Run(new Form1());
}
}

C# screen capture

This is achieved by calling windows API functions.
Also see Reference.

using System.Runtime.InteropServices;
using System.Drawing.Imaging;

public class Form1 : System.Windows.Forms.Form {
#region Dll Imports
[DllImport("user32.dll")]
private static extern bool PrintWindow(IntPtr hwnd, IntPtr hdcBlt, uint nFlags);
#endregion

private void screen_capture() {
try {
Bitmap bm = new Bitmap(this.Width, this.Height);
Graphics g = Graphics.FromImage(bm);
IntPtr hdc = g.GetHdc();
Form1.PrintWindow(this.Handle, hdc, 0);
//MessageBox.Show("color: " + bm.GetPixel(550, 300));
g.ReleaseHdc(hdc);
g.Flush();
g.Dispose();
//this.pictureBox1.Image = bm;
bm.Save("test.bmp");
} catch (Exception e) {
MessageBox.Show(this, "error: " + e.Message);
}
}

public Form1() { InitializeComponent(); /* screen_capture(); */ }

[STAThread]
static void Main() { Application.Run(new Form1()); }
}

Blog Archive

Followers