MySQL INSERT statement with steroids
Do you remember when you first learned about basic SQL statements? INSERT, UPDATE, DELETE and SELECT?
While SELECT is a whole different beast, the other three are pretty much the same across different database engines, so you usually learn the basics only.
The thing is, you should REALLY read the documentation for your specific engine, because most of the time, each engine adds cool functionality to the standards (OK, this will break your code when you change from database X to Y, but in the meantime, it makes development so much easier)
How many times, have you written code that looks like this:
1 2 3 4 5 6 7 8 9 | bool exist = CheckIfDuplicate(key); if(!exist) { //insert } else { //update } |
I hope not as many as me... This is the day I learned about:
So if you are not familiar with this syntax and you're a heavy MySQL user, please go read the documentation. Go. Now.
Drag & Drop from ListView to TreeView
I started working on a new project at work. This is the "fun stage" of the project: the prototypes and the investigation part.
One of the UI requirements was to drag and drop elements from a ListView to a TreeNode. So, this is how to enable Drag&Drop:
First, you have to enable the AllowDrop property for your TreeView
treeView1.AllowDrop = true;
Then, in your ListView, you have to add code to the ItemDrag event:
listView1.DoDragDrop(listView1.SelectedItems, DragDropEffects.Move);
The DoDragDrop method begins a Drag&Drop operation. The first parameter, is the element(s) you want to drag around. The second, is the type of dragging-and-dropping you want to do. Here is a list:
| None | The drop target does not accept the data. |
| Copy | The data from the drag source is copied to the drop target. |
| Move | The data from the drag source is moved to the drop target. |
| Link | The data from the drag source is linked to the drop target. |
| Scroll | The target can be scrolled while dragging to locate a drop position that is not currently visible in the target. |
| All | The combination of the Copy, Move, and Scroll effects. |
You can combine more than 1 DragDropEffect by bitwise ORing them:
listView1.DoDragDrop(listView1.SelectedItems, DragDropEffects.All | DragDropEffects.Link);
In my project, I only need DragDropEffects.Move functionality.
Well. Now we only have two more things to do. First, we have to take care of the DragOver event. This is raised by your target control (in this case a TreeView) every time the mouse moves over the target's visible area.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | private void treeView1_DragOver(object sender, DragEventArgs e) { if (e.Data.GetDataPresent(typeof(ListView.SelectedListViewItemCollection))) { e.Effect = DragDropEffects.Move; } Point position = treeView1.PointToClient(new Point(e.X, e.Y)); TreeNode tn = treeView1.GetNodeAt(position); if (tn != null) { treeView1.SelectedNode = tn; if (!tn.IsExpanded) { tn.Expand(); } } } |
There are some interesting bits of code in there:
if (e.Data.GetDataPresent(typeof(ListView.SelectedListViewItemCollection))) { e.Effect = DragDropEffects.Move; }
Our data (the one we are dragging around) is stored in the Data property of the event. The GetDataPresent method determines whether this data can be converted to the specified format.
In this case, our format is ListView.SelectedListViewItemCollection. Why? you may ask. Well, because this is the returning type of listView1.SelectedItems (which is the object we are dragging around, remember?).
We could put this particular piece of code inside the target's (TreeView) DragEnter event. This way, it only has to be executed once.
This is also important:
Point position = treeView1.PointToClient(new Point(e.X, e.Y)); TreeNode tn = treeView1.GetNodeAt(position);
The DragEventArgs object holds some information about the event. In this case we are using e.X and e.Y to know the location of the mouse at any given time. But the thing is that the mouse location is relative to the screen, so we have to convert it to client coordinates. That's exactly what PointToClient does.
Now that we have the position relative to the client, we can get the TreeNode under that position. If there is no TreeNode under that position (maybe the mouse is moving over whitespace) it returns null.
The rest of the code is trivial, it expands a TreeNode if its not expanded already.
Lastly, we have the DragDrop event, in which we release the mouse button over our target TreeNode.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | private void treeView1_DragDrop(object sender, DragEventArgs e) { Point position = treeView1.PointToClient(new Point(e.X, e.Y)); if (e.Data.GetDataPresent(typeof(ListView.SelectedListViewItemCollection))) { TreeNode tn = treeView1.GetNodeAt(position); var li = (ListView.SelectedListViewItemCollection)e.Data.GetData(typeof(ListView.SelectedListViewItemCollection)); foreach (ListViewItem item in li) { MessageBox.Show(tn.Text + " = " + item.Text); } } } |
The only interesting or new line in here is this one:
var li = (ListView.SelectedListViewItemCollection)e.Data.GetData(typeof(ListView.SelectedListViewItemCollection));
this method retrieves the data associated with the specified data format (at least, that's what MSDN says) but you still have to cast it accordingly.
Once you have your data, you can do anything you want with it (i'm just displaying which TreeNode received the Items and the Items' text)
Happy hacking.
UPDATE
If you want to see which TreeNode you're selecting while dragging, just add this to your code:
1 | treeView1.HideSelection = false; |
New Books
It's been kinda quiet lately. I've been busy reading.
Yup, that's right. Reading.
I got a book shipment from amazon.com last week and I have been reading since then. These are the books I got:
- Head First Design Patterns
- Design Patterns
- Refactoring
- Mythical Man Month
- Programming Pearls
- Coders at Work
So, as you can see, I have a *LOT* to read. The good thing about my job, is that it allows me some spare time to educate myself.
I haven't done any programming in the last week, but I promise I'll write a post on blackberry development in the next few days, just let me finish my book first