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

31Jan/120

.Net Membership Provider on MySQL

I was coding away on a new project that needed a membership back end. I usually use the built in .Net Membership Provider and make what ever changes I need on it. But this project requires a cost effective SQL solution so MS was out. MySQL has features for Membership Prover in .Net but it needs a little tweak to be setup. I found this great tutorial: http://dev.mysql.com/doc/refman/5.1/en/connector-net-tutorials-asp-roles.html

I only want to add one thing: when editing the machine.config file, right click the editor you are gone use and select "Run as administrator" or you will have a permissions error when you save!

Filed under: .Net, asp.net, C#, MySQL, sql No 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!

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>