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

3Sep/150

Easiest way to embed assembly’s into your executables

I usually write a lot of command line tools for different quick fixes. Distributing them to clients with a lot of attached DLL files are not always the best. They get copied around different servers and sooner or later someone runs them without the proper DLL files present. For quick and dirty tools there might be faulty error handling and the tool fails in a critical moment due to the missing assembly. I usually embed the DLL file into the executable, especially when doing command line tools. By far the easiest way of doing that in .Net is to install a Nuget package called Costura.Fody!

Just install it into the project and build the release and all assembly's get compressed and embedded into the executable. Visit Costura.Fody github page for more information!

16Jan/140

.Net C#: Webbrowser control print line break

I was updating an old project for a client today. I needed to print a simple list and figured that the easiest way should be to format the data in HTML. Most developers out there has built webpages now days and we all know that it is a quick way to format the information and print it. So after a quick creation of the HTML i found an article named "Displaying custom HTML in WebBrowser control" by Gunnar Peipman. So easy enough to print the custom HTML from the control, based on Gunnar's article I came up with this code:

WebBrowser webPrint = new WebBrowser();
webPrint.Navigate("about:blank");

if (webPrint.Document != null) { 
    webPrint.Document.Write(string.Empty);
}
webPrint.DocumentText = printHTML.ToString();
webPrint.DocumentCompleted += webPrint_DocumentCompleted; 

void webPrint_DocumentCompleted(object sender, WebBrowserDocumentCompletedEventArgs e) { 
    WebBrowser webPrint = (WebBrowser)sender; 
    webPrint.ShowPrintDialog(); 
}

If you don't use the "DocumentComplete" event handler your printout will be blank. In the print I used a basic HTML table to show the data with rows like this:

<table cellpadding="3" style="width:100%">
<tr style="color:White;background-color:Black;font-weight:bold;"><td>Box ID</td><td>Gång</td><td>Hylla</td><td>Nivå</td><td>Nåbar från golvet</td></tr>
<tr><td>10346</td><td>AB</td><td>3</td><td>F</td><td>True</td></tr>
<tr><td>10294</td><td>AB</td><td>3</td><td>D</td><td>True</td></tr>
<tr><td>10296</td><td>AB</td><td>3</td><td>C</td><td>True</td></tr>
<tr><td>10298</td><td>AB</td><td>3</td><td>C</td><td>True</td></tr>
<tr><td>10300</td><td>AB</td><td>3</td><td>B</td><td>True</td></tr>
<tr><td>10302</td><td>AB</td><td>3</td><td>B</td><td>True</td></tr>

When printing data that took up several pages I ended up with this:

pagebreak error

The data was cut of at the line breaks. I Googled a lot and found a few suggestions about css "page-break.." but they didn't have any effect on the webbrowser control print layout. Finally I found a simple soulition, just add a bland column to the left of the table and the line break works. Like this:

<table cellpadding="3" style="width:100%">
<tr style="color:White;background-color:Black;font-weight:bold;"><td> </td><td>Box ID</td><td>Gång</td><td>Hylla</td><td>Nivå</td><td>Nåbar från golvet</td></tr>
<tr><td> </td><td>10346</td><td>AB</td><td>3</td><td>F</td><td>True</td></tr>
<tr><td> </td><td>10294</td><td>AB</td><td>3</td><td>D</td><td>True</td></tr>
<tr><td> </td><td>10296</td><td>AB</td><td>3</td><td>C</td><td>True</td></tr>
<tr><td> </td><td>10298</td><td>AB</td><td>3</td><td>C</td><td>True</td></tr>
<tr><td> </td><td>10300</td><td>AB</td><td>3</td><td>B</td><td>True</td></tr>
<tr><td> </td><td>10302</td><td>AB</td><td>3</td><td>B</td><td>True</td></tr>
<tr><td> </td><td>10382</td><td>AB</td><td>4</td><td>F</td><td>True</td></tr>

Then the printout looked like this:

pagebreak fix

15Oct/130

Picasa Web Downloader Source

Had a few downloads of the Picasa Web Downloader that I put up yesterday. Also had a few request of the source code, people seem to be nervous about putting there login information into an application that isn't open source.

So now it is open source available at: https://code.google.com/p/picasa-web-downloader/
Compiled and readu to run:


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 && 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 && (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 && (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
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 => m.UserId == userID).Single(m => 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<int> 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 => m.UserId == userID).Single(m => 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 => m.UserId == userID).Single(m => 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<int> 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