Hackviking He killed Chuck Norris, he ruled dancing so he took up a new hobby…

22Mar/130

Power of scripting challange

I was speaking at an IT-consultant company kick-off today. Explained to the server maintenance guys how important scripting is! Not only power shell but classic vbscript. A server tech that can't script is behind the guy who can. Scripting tech can do twice the work in half the time in a big server park. They complained back and forth and sad "No way I am going to learn that, all the good stuff you can do needs a real program anyway!". WRONG!

So I took a bet with them! I will produce script that can do something real. Something that you actually do every day as a server tech more or less.
The conditions finally came to:

  • Has to be pure vbscript, only allowed to use operative system built in objects like File System Object.
  • Has to perform a vital task, something that usually claims several manual steps to complete.
  • Has to work like any other command line tool, menus, help and so forth.
  • I got until Monday 213-03-25 to complete the script!

Hey guys... Follow me!

challenge-accepted

18Mar/130

Google Maps searchbox with autocomplete

I was sitting trying to do some mods on a Queclink GL200 GPS transmitter for the MPS project. After many hours of no luck at all I gave up for the day. If anyone have some input on that please contact me!

So i started messing around with the Google Maps API demo that I made for them instead. Adding some auto complete to the search form instead. I thought I would share what I managed to do. The challange is to do a mach up of Google Maps API and jQuery to get it to work good.

The trick is to attach the jQuery handler to the object. Why?
You have to create the search box dynamically in order to push it on top of the Google Maps canvas.

Entire demo can be found here: http://jsfiddle.net/kallsbo/XgsC6/

First initialize the map and all it's settings:

var map;
var addressField;
var geocoder;

$(document).ready(function () {
    // Define map options
    var mapOptions = {
        center: new google.maps.LatLng(57.698254, 12.037024),
        zoom: 16,
        mapTypeId: google.maps.MapTypeId.HYBRID,
        panControl: true,
        zoomControl: true,
        mapTypeControl: true,
        scaleControl: true,
        streetViewControl: true,
        overviewMapControl: true
    };

    // Define map
    map = new google.maps.Map(document.getElementById("map_canvas"), mapOptions);

    // Define Gecoder
    geocoder = new google.maps.Geocoder();

    // Init searchbox
    initSearchBox();
});

function initSearchBox() {
    // Add searchbox
    var searchControlDiv = document.createElement('div');
    var searchControl = new SearchControl(searchControlDiv, map);

    searchControlDiv.index = 1;
    map.controls[google.maps.ControlPosition.TOP_CENTER].push(searchControlDiv);
}

As you can see we initialize the search box control and put it in a div at the top of the canvas. This is how we create the control and it's auto complete function:

function SearchControl(controlDiv, map) {
    // Set CSS styles for the DIV containing the control
    // Setting padding to 5 px will offset the control
    // from the edge of the map.
    controlDiv.style.padding = '5px';

    // Set CSS for the control border.
    var controlUI = document.createElement('div');
    controlUI.style.backgroundColor = 'white';
    controlUI.style.borderStyle = 'solid';
    controlUI.style.borderWidth = '2px';
    controlUI.style.cursor = 'pointer';
    controlUI.style.textAlign = 'center';
    controlUI.title = 'Sök ex: gatunamn, stad';
    controlDiv.appendChild(controlUI);

    // Create the search box
    var controlSearchBox = document.createElement('input');
    controlSearchBox.id = 'search_address';
    controlSearchBox.size = '80';
    controlSearchBox.type = 'text';
}

So when you have gotten this far in the code you have the search input box as a VAR. Now whe can use that VAR to attache the function for the auto complete to it:

    // Initiat autocomplete
    $(function () {
        $(controlSearchBox).autocomplete({
            source: function (request, response) {

                if (geocoder == null) {
                    geocoder = new google.maps.Geocoder();
                }

                geocoder.geocode({
                    'address': request.term
                }, function (results, status) {
                    if (status == google.maps.GeocoderStatus.OK) {
                        var searchLoc = results[0].geometry.location;
                        var lat = results[0].geometry.location.lat();
                        var lng = results[0].geometry.location.lng();
                        var latlng = new google.maps.LatLng(lat, lng);
                        var bounds = results[0].geometry.bounds;

                        geocoder.geocode({
                            'latLng': latlng
                        }, function (results1, status1) {
                            if (status1 == google.maps.GeocoderStatus.OK) {
                                if (results1[1]) {
                                    response($.map(results1, function (loc) {
                                        return {
                                            label: loc.formatted_address,
                                            value: loc.formatted_address,
                                            bounds: loc.geometry.bounds
                                        }
                                    }));
                                }
                            }
                        });
                    }
                });
            },
            select: function (event, ui) {
                var pos = ui.item.position;
                var lct = ui.item.locType;
                var bounds = ui.item.bounds;

                if (bounds) {
                    map.fitBounds(bounds);
                }
            }
        });
    });

Then finish up creating the object and push it the the Google Maps Canvas as a custom control:

    // Set CSS for the control interior.
    var controlText = document.createElement('div');
    controlText.style.fontFamily = 'Arial,sans-serif';
    controlText.style.fontSize = '12px';
    controlText.style.paddingLeft = '4px';
    controlText.style.paddingRight = '4px';
    controlText.appendChild(controlSearchBox);
    controlUI.appendChild(controlText);
}
26Feb/130

Script: List users homedir size

I compiled a vbscript that loops through all the sub folders of a given folder and print out the size. The script takes the folder name and searches the Active Directory for a corresponding user. If found it prints the name of the user in the output.

Use:

cscript homedirsize.vbs

Three input boxes will appear...

  1. Path where the homedir folders are located. Ex. d:\home
  2. Width, in characters, of the first output column.
  3. Width, in characters, of the second output column.

Download script here:

'****************************************************************************************
'
' Name: Homedirsize.vbs
' 
' Retrieves the size of each subdirectory and matches them to an AD account.
' Outputs a list of directories, username and size.
'
' Written by: Kristofer Källsbo
' 2013-02-26 - http://www.hackviking.com
'
'****************************************************************************************
Option explicit
dim path, column1, column2, objRoot, domainname, fso, partline, i, rootFolder, folder

' get path of homedirs
path = inputbox("Enter path of homedirs:")

' get column widths
column1 = Cint(inputbox("Enter width of first output column:"))
column2 = Cint(inputbox("Enter width of second output column:"))

' get the current domain
Set objRoot = GETOBJECT("LDAP://RootDSE")
domainname = objRoot.GET("defaultNamingContext")

' get the file system object
Set fso = CreateObject("Scripting.FileSystemObject")

' print description lines
wscript.echo "homedirsize.vbs runned on " & Date & " - " & Time
wscript.echo ""
wscript.echo LeftJustified("Foldername", column1) & LeftJustified("Username", column2) & "Size (Mb)"

for i = 0 to column1 + column2 + 8
	partline = partline & "-"
next

wscript.echo partline

' start looping all the subfolders
Set rootFolder = fso.GetFolder(path)
For Each folder in rootFolder.SubFolders
	Dim folderSize
	folderSize = folder.Size
			
	wscript.echo LeftJustified(folder.Name, column1) & LeftJustified(FindUser(folder.Name, domainname), column2) & FormatNumber(((folderSize/1024)/1024),2) & " Mb"
Next

Set fso = Nothing

FUNCTION FindUser(BYVAL UserName, BYVAL Domain) 
	Dim cn,cmd,rs
	SET cn = CREATEOBJECT("ADODB.Connection")
	SET cmd = CREATEOBJECT("ADODB.Command")
	SET rs = CREATEOBJECT("ADODB.Recordset")

	cn.open "Provider=ADsDSOObject;"
	
	cmd.activeconnection=cn
	cmd.commandtext="SELECT Name FROM 'LDAP://" & Domain & _
			 "' WHERE sAMAccountName = '" & UserName & "'"
	
	SET rs = cmd.EXECUTE

	IF err<>0 THEN
		FindUser = 2
		wscript.echo "Error connecting to Active Directory Database:" & err.description
	ELSE
		IF NOT rs.BOF AND NOT rs.EOF THEN
     			rs.MoveFirst
     			FindUser = rs.Fields("Name").Value
		ELSE
			FindUser = "N/A"
		END IF
	END IF
	cn.close
END FUNCTION

Function LeftJustified(ColumnValue, ColumnWidth)
   If(ColumnWidth < Len(ColumnValue) OR ColumnWidth = Len(ColumnValue)) then
		LeftJustified = Left(ColumnValue, ColumnWidth - 1) & " "
	else
		LeftJustified = ColumnValue & Space(ColumnWidth - Len(ColumnValue))
	End if
End Function
20Feb/130

Script: NTFS rights on user home directories

Have a normal Windows setup where the user have a home folder on the file server. All the users is connected to there \\fileserver\home$\%username% via GPO on logon. How ever we found that some of the folders had rights that where messed up. So i wrote a quick script that loopes through all folders and checks if there is a user account in the domain if not it will move the directory to __unconnected__ folder. For all know users it uses cacls command to set rights for the user and admins only. If you need something else you can just edit the cacls command before you run it! Script is provided as is and feel free to modify it...

Download script here:

Option Explicit
'ON ERROR RESUME NEXT
Dim path, objRoot, domainname, fso, rootFolder, folder, objShell, intRunError
path = inputbox("Enter path of homedirs:")

' Get current domain
IF domainname = "" THEN
	SET objRoot = GETOBJECT("LDAP://RootDSE")
	domainname = objRoot.GET("defaultNamingContext")
END IF

' Setup FSO connection
Set fso = CreateObject("Scripting.FileSystemObject")
Set rootFolder = fso.GetFolder(path)
Set objShell = WScript.CreateObject( "WScript.Shell" )

' Go through all homedir folders
For Each folder in rootFolder.SubFolders
	if(FindUser(folder.Name, domainname) = 1) Then
		' Folder found reset the permissions
		wscript.echo folder.Name + " - has a user connected! Reseting the permissions..."
		intRunError = objShell.Run("%COMSPEC% /c Echo Y| cacls " & folder.Path & " /t /c /g Administrators:F ""Domain Admins"":F " & folder.Name & ":F", 1, True)
		If intRunError <> 0 Then
			wscript.echo folder.Name + " - ERROR assigning rights!"
			wscript.echo intRunError
		else
			wscript.echo folder.Name + " - Rights asigned!"
		End If
	elseif(FindUser(folder.Name, domainname) = 0) then
		' This folder isn't connected move it
		If(folder.Name <> "__unconnected__") then
			wscript.echo folder.Name + " - doesn't have a user connected! Moving to .\__unconnected__"
			fso.MoveFolder folder.Path, rootFolder.Path + "\__unconnected__\"
		End If
	else
		wscript.echo "ERROR: Connection to AD failed!"
	End If
Next

Set objRoot = Nothing
Set fso = Nothing
Set rootFolder = Nothing
Set objShell = Nothing

' Function to check if user exists
FUNCTION FindUser(BYVAL UserName, BYVAL Domain) 
	Dim cn,cmd,rs
	SET cn = CREATEOBJECT("ADODB.Connection")
	SET cmd = CREATEOBJECT("ADODB.Command")
	SET rs = CREATEOBJECT("ADODB.Recordset")

	cn.open "Provider=ADsDSOObject;"
	
	cmd.activeconnection=cn
	cmd.commandtext="SELECT ADsPath FROM 'LDAP://" & Domain & _
			 "' WHERE sAMAccountName = '" & UserName & "'"
	
	SET rs = cmd.EXECUTE

	IF err<>0 THEN
		FindUser = 2
		wscript.echo "Error connecting to Active Directory Database:" & err.description
	ELSE
		IF NOT rs.BOF AND NOT rs.EOF THEN
     			rs.MoveFirst
     			FindUser = 1
		ELSE
			FindUser = 0
		END IF
	END IF
	cn.close
END FUNCTION
16Mar/112

LINQ to SQL: Left join with null values

I was staying up late last night coding on my project and hit a snag. Trying to join two tables that have a 1 to many relationship. The issue was that I was trying to get all the rows from one table and where there where get the corresponding entries from the other table. In short I have one table containing to-do-lists and one table containing the tasks for each list. But I have been a good boy and done all the tasks on some lists so they are empty. When I tried to join them I only got back the lists that had task on them. I dusted of my old copy of SQL Queries For Mere Mortals and was able to do it in SQL. But I wanted do it with SQL to LINQ. After a few hours I couldn't get any further. Tried to Google it and came up short. So I posted this on stackoverflow.com:

I'm trying to get data from two tables. Todo_Lists and Todo_Items. I manged to join the two like this:

from list in dataContext.Todo_Lists
from item in dataContext.Todo_List_Items
where list.UserID == userID &amp;&amp; list.ListID == item.ListID
select new
{
    ListID = list.ListID,
    ListName = list.ListName, 
    ItemID = item.ItemID, 
    ItemName = item.ItemName 
};

That's all good in the hood if I just want the lists with Items on them. But I need to return all the lists and where there are Items I need them to be joined in.

Thankfull for any information!

That's the good thing living in the far north, we have day when the US have night! So when I got back from work I found two answers. None of them was a complete solution but combined they pushed me in the right direction. I realized that I had to give the LINQ engine an alternative to null. This is what I came up with:

from List in dataContext.Todo_Lists
join Item in dataContext.Todo_List_Items on List.ListID equals Item.ListID into compList
from resList in compList.DefaultIfEmpty()
where List.ListTrashed == false &amp;&amp; (resList.ItemTrashed == false || resList.ItemTrashed == null)
orderby List.ListSortOrder ascending, resList.ItemSortOrder ascending
select new
{
    List.ListID,
    List.ListName,
    List.ListSortOrder,
    ItemID = (resList.ItemID == null ? int.MinValue : resList.ItemID),
    ItemSortOrder = (resList.ItemSortOrder == null ? int.MinValue : resList.ItemSortOrder)
};

So lets break it down!

from List in dataContext.Todo_Lists

I want to get the Lists from the Todo_Lists table.

join Item in dataContext.Todo_List_Items on List.ListID equals Item.ListID into compList

I want to join the Items/Task from Todo_List_Items table where the ListID column relationship matches. The two mashed tables goes into compList for complete list.

from resList in compList.DefaultIfEmpty()

Then I select into resList for result list from comList with .DefaultEmpty(). DefaultEmpty() returns the default value if there isn't an entry. That makes it possible to check for empty records later in the code.

where List.ListTrashed == false &amp;&amp; (resList.ItemTrashed == false || resList.ItemTrashed == null)

Now comes the where. I only want the list that I haven't sent to the trashcan. This is also true for the items but they can also return a null value. So if I only say resList.ItemTrashed == false I'm right back where I started with only getting the lists with items on them. But by adding || (or) resList.ItemTrashed == null I give the engine an option to match null as a suitable value for selection. That can only return an item that has null or false and I will not receive any that are trashed (resList.ItemTrashed == true). An other thing to note here is all the where options for table Todo_Lists is on the List object and all others are on the resList object. Don't worry the intellisense will take care of you there.

orderby List.ListSortOrder ascending, resList.ItemSortOrder ascending

Next I want to sort my result. Of course I want to prioritize my lists so I use sort order values to do so. This is really straight forward you don't have to take into account the possible null values just tell how you want it sorted and it will all be good.

select new
{
    List.ListID,
    List.ListName,
    List.ListSortOrder,
    ItemID = (resList.ItemID == null ? int.MinValue : resList.ItemID),
    ItemSortOrder = (resList.ItemSortOrder == null ? int.MinValue : resList.ItemSortOrder)
};

Now it's just the selection of the fields left. From List I want all the records returned that meets the where clause so I just select the fields I want. From the Items its a different story. ItemID can be null but that value can't be returned so you have to substitute it with something else. It also has to be of the same cast as the original field. So if the ItemSortOrder field is null replace it with int.MinValue. That value is not part of the sort order and easy to match when I loop through the records to detect that the list doesn't have any items. If the field isn't null I want the value so I put in : resList.ItemSortOrder.

I have to credit msarchet and saus for getting me in the right direction. Without them I wouldn't got this far. Thanks guys!

Tagged as: , , , 2 Comments
14Mar/110

jQuery UI: Samples has redundant code

I have messed around a lot with .Net MVC, jQuery and jQuery UI for some time now. I just discovered that one of the examples includes redundant code. I don't know if all of them do but you need to watch your self. I have seen a few sites that run the jQuery examples straight up and they may have problems with this. It's really not that big of a deal but redundant code can become a comp ability issue and it's always bad practice. You also send more data to each client and you waste bandwidth.

The example I found was the tabs control with the ability to add and remove tabs. This is the code they published:

	#dialog label, #dialog input { display:block; }
	#dialog label { margin-top: 0.5em; }
	#dialog input, #dialog textarea { width: 95%; }
	#tabs { margin-top: 1em; }
	#tabs li .ui-icon-close { float: left; margin: 0.4em 0.2em 0 0; cursor: pointer; }
	#add_tab { cursor: pointer; }
	&lt;/style&gt;
	&lt;script&gt;
	$(function() {
		var $tab_title_input = $( "#tab_title"),
			$tab_content_input = $( "#tab_content" );
		var tab_counter = 2;

		// tabs init with a custom tab template and an "add" callback filling in the content
		var $tabs = $( "#tabs").tabs({
			tabTemplate: "&lt;li&gt;&lt;a href='#{href}'&gt;#{label}&lt;/a&gt; &lt;span class='ui-icon ui-icon-close'&gt;Remove Tab&lt;/span&gt;&lt;/li&gt;",
			add: function( event, ui ) {
				var tab_content = $tab_content_input.val() || "Tab " + tab_counter + " content.";
				$( ui.panel ).append( "&lt;p&gt;" + tab_content + "&lt;/p&gt;" );
			}
		});

		// modal dialog init: custom buttons and a "close" callback reseting the form inside
		var $dialog = $( "#dialog" ).dialog({
			autoOpen: false,
			modal: true,
			buttons: {
				Add: function() {
					addTab();
					$( this ).dialog( "close" );
				},
				Cancel: function() {
					$( this ).dialog( "close" );
				}
			},
			open: function() {
				$tab_title_input.focus();
			},
			close: function() {
				$form[ 0 ].reset();
			}
		});

		// addTab form: calls addTab function on submit and closes the dialog
		var $form = $( "form", $dialog ).submit(function() {
			addTab();
			$dialog.dialog( "close" );
			return false;
		});

		// actual addTab function: adds new tab using the title input from the form above
		function addTab() {
			var tab_title = $tab_title_input.val() || "Tab " + tab_counter;
			$tabs.tabs( "add", "#tabs-" + tab_counter, tab_title );
			tab_counter++;
		}

		// addTab button: just opens the dialog
		$( "#add_tab" )
			.button()
			.click(function() {
				$dialog.dialog( "open" );
			});

		// close icon: removing the tab on click
		// note: closable tabs gonna be an option in the future - see http://dev.jqueryui.com/ticket/3924
		$( "#tabs span.ui-icon-close" ).live( "click", function() {
			var index = $( "li", $tabs ).index( $( this ).parent() );
			$tabs.tabs( "remove", index );
		});
	});
	</script>

<div>
	<div id="dialog" title="Tab data">
		<form>
			<fieldset>
				<label for="tab_title">Title</label>
				<input type="text" name="tab_title" id="tab_title" value="" />
				<label for="tab_content">Content</label>
				<textarea name="tab_content" id="tab_content"></textarea>
			</fieldset>
		</form>
	</div>

	<button id="add_tab">Add Tab</button>

	<div id="tabs">
		<ul>
			<li><a href="#tabs-1">Nunc tincidunt</a> <span>Remove Tab</span></li>
		</ul>
		<div id="tabs-1">
			<p>Proin elit arcu, rutrum commodo, vehicula tempus, commodo a, risus. Curabitur nec arcu. Donec sollicitudin mi sit amet mauris. Nam elementum quam ullamcorper ante. Etiam aliquet massa et lorem. Mauris dapibus lacus auctor risus. Aenean tempor ullamcorper leo. Vivamus sed magna quis ligula eleifend adipiscing. Duis orci. Aliquam sodales tortor vitae ipsum. Aliquam nulla. Duis aliquam molestie erat. Ut et mauris vel pede varius sollicitudin. Sed ut dolor nec orci tincidunt interdum. Phasellus ipsum. Nunc tristique tempus lectus.</p>
		</div>
	</div>

</div><!-- End demo -->

The problem is that the register the submit action for the form twice. This code initializes the model popup including the form. I have highlighted the peace of code thats interesting here.

		var $dialog = $( "#dialog" ).dialog({
			autoOpen: false,
			modal: true,
			buttons: {
				<span style="color: #ff0000;">Add: function() {
					addTab();
					$( this ).dialog( "close" );
				},</span>
				Cancel: function() {
					$( this ).dialog( "close" );
				}
			},
			open: function() {
				$tab_title_input.focus();
			},
			close: function() {
				$form[ 0 ].reset();
			}
		});

That peace of code does the exact same thing as this peace of code:

 var $form = $( "form", $dialog ).submit(function() {
			addTab();
			$dialog.dialog( "close" );
			return false;
		});

The first one is a must to initialize the module popup, this is just redundant. I have played around a lot with this and I can't find any impact in functionality. If you know any or find anything please give me a comment back, but I can't see that there would be any issues. Then in the end I'm rather corrected and admitting that I'm wrong then running code that isn't OK.

13Mar/110

LINQ to SQL: Update several rows

So I was coding away on an MVC project with integrated jQuery. I use jQuery UI Tabs in my page and let the user add new, delete and re-order them. All this I wan't to save to the database so I can display it the next time the user logs on. I'm using JsonResult to handle the Ajax calls from jQuery on the server side. Add and remove is pretty straight forward with LINQ to SQL like this:

//Add
DBTodo_ListsDataContext datacontext = new DBTodo_ListsDataContext();
Todo_List newList = new Todo_List();
newList.ListName = listName;
newList.ListDesc = listDesc;
newList.UserId = userID;
datacontext.Todo_Lists.InsertOnSubmit(newList);
datacontext.SubmitChanges();

//Remove : or really mark as trashed
DBTodo_ListsDataContext datacontext = new DBTodo_ListsDataContext();
Todo_List trashList = datacontext.Todo_Lists.Where(m =&gt; m.UserId == userID).Single(m =&gt; m.ListID == listID);
trashList.ListTrash = true;
datacontext.SubmitChanges();

I haven't included all the code above but you get the idea.
We write easy Linq querys and LINQ to SQL takes care of the SQL querys for us. So when I set out to build the sort order update function I thought that Linq was going to do a better job. On the tab re-order I send an Ajax request with an int array with all the tab id's. The array is in the same order as the tabs is visually. Something like this:

{ 10, 14, 11, 16, 17, 18, 8, 19, 21, 20, 22, 27, 23, 24, 28, 29, 26, 30, 31, 32, 33, 34, 35, 37, 36, 38, 39, 40, 41, 42, 43, 44, 45, 47, 46, 50, 51, 53, 54, 55, 63, 60, 1 }

So now I have to use that array to reset all the sort order fields for the lists in the array above. First I went like this:

public JsonResult UpdateSortOrder(List&lt;int&gt; listsSortOrder) {
int sortOrder = 0;
Guid userID = (Guid)Membership.GetUser().ProviderUserKey;
DBTodo_ListsDataContext datacontext = new DBTodo_ListsDataContext();
// Loop throug the sort order
foreach (int list in listsSortOrder) {
var dbListItem = datacontext.Todo_Lists.Where(m =&gt; m.UserId == userID).Single(m =&gt; m.ListID == list);
dbListItem.ListSortOrder = sortOrder;
// Count up the sort order
sortOrder++;
}
// Commit changes
datacontext.SubmitChanges();

return Json(new { sorted = true }, JsonRequestBehavior.AllowGet);
}

I know from the beginning that the line selecting the items for me would generate an SQL query each time, this one:

var dbListItem = datacontext.Todo_Lists.Where(m =&gt; m.UserId == userID).Single(m =&gt; m.ListID == list);

How ever I was hoping that LINQ to SQL would concat all the update statements into one SQL query. But when I did a little profiling on the  SQL server I found out that the SubmitChanges() generated one SQL connection and query for each updated item, in this case 43 of them. So I checked the execution time on the web server for this code, i tried three times and got the result below:

Request #1: 0.1050060s
Request #2: 0.1180067s
Request #3: 0.1050060s

Not that this is long but you have to take into account that this is on my dev machine. I'm the only user and I hope my finished project will have more then one user. And then I haven't even taken into account the overhead of SQL querys to the production server that doesn't reside on the web server witch my SQL does on my dev machine. So what is a better approach? Build the command in one and the same query string and do one DB call. On the LINQ to SQL datacontext there is a method called ExecuteCommand() that we can use to execute a query straight into the DB. Like this:

public JsonResult UpdateSortOrder(List&lt;int&gt; listsSortOrder) {
int sortOrder = 0;
System.Text.StringBuilder query = new System.Text.StringBuilder();
string userID = Membership.GetUser().ProviderUserKey.ToString();
foreach (int list in listsSortOrder)             {
query.Append("UPDATE ToDo_Lists SET ListSortOrder = ");
query.Append(sortOrder);
query.Append(" WHERE UserID = '");
query.Append(userID);
query.Append("' AND ListID = ");
query.Append(list);query.Append(";");
sortOrder++;
}
DBTodo_ListsDataContext datacontext = new DBTodo_ListsDataContext();
datacontext.ExecuteCommand(query.ToString(), new object[] { });
return Json(new { sorted = true }, JsonRequestBehavior.AllowGet);
}

So what's so much better with this? The execution times talks for it self. The execute for three test with this code came back to:

Request #1: 0.0500029s
Request #2: 0.0230013s
Request #3: 0.0310018s

So my conclusion is that sometimes it's a great idea to go around LINQ to SQL for performance. So sometimes you have to go old-school!

Tagged as: , , , , No Comments
12Sep/090

Must declare the scalar variable @ID

Was just burning the midnight oil on a project for a client. Working with asp.Net for the first time in ages. Making a quick fix, adding a new form for editing customer details, I used theSqlDataSource control. Usually I work with the SqlCommand in SqlDataClient directly but now I needed paging and sorting fixed quick and easy! I had a really big problem with the select command parameters. When you use them with the SqlCommand you declare them with an @ prefix in the select command like this:

Select * FROM Customers WHERE CustomersID = @ID 

(SELECT * is a sin, I know!)

Then declare the variable as: SqlCommand.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int))
Received errors like "Must declare the scalar variable @ID" and so on. Checked, dubble checked and triple checked... The variable was defined!
But no! Some idiot at MS decided to think outside of the box and the declaration for the SqlDataSource control should be without the @!

Not:

<asp:SqlDataSource ID="sdsContacts" runat="server" ConnectionString="<%$ ConnectionStrings:DB_CONN %>"
SelectCommand="Select * FROM Customers WHERE CustomersID = @ID" ProviderName="System.Data.SqlClient">
<SelectParameters>
<asp:Parameter Name="@ID" Direction="Input" />
</SelectParameters>
</asp:SqlDataSource>

But instead this:

<asp:SqlDataSource ID="sdsContacts" runat="server" ConnectionString="<%$ ConnectionStrings:DB_CONN %>"
SelectCommand="Select * FROM Customers WHERE CustomersID = @ID" ProviderName="System.Data.SqlClient">
<SelectParameters>
<asp:Parameter Name="ID" Direction="Input" />
</SelectParameters>
</asp:SqlDataSource>
Tagged as: , , No Comments