January 24, 2012

Creating a simple pie chart with HTML5 Canvas Part 2

Last week we created a pie chart using HTML5’s Canvas element. This week we will utilize jQuery to add some simple functionality.  For this exercise, we want to be able to visualize the exact values of each slice of the pie when the user positions the mouse over them. To do this, we will use the .mousemove event from jQuery to update text on the page.

The first step is to include jQuery.

 

<script src=”http://ajax.googleapis.com/ajax/libs/jquery/1.7.1/jquery.min.js”></script>

 

Next, we will need to refactor the script from last week a bit in order to reference it from the mousemove event. To do this, we will create an array to store the data for each slice.

 

var pieData = [];

for(var i in data) {

                pieData[i] = [];

                pieData[i]['value'] = data[i];

                pieData[i]['startAngle'] = 2 * Math.PI * lastPosition;

                pieData[i]['endAngle'] = 2 * Math.PI * (lastPosition + (data[i]/total));

                lastPosition += data[i]/total;

}

 

Now that the data is stored a bit differently than last week, we will need to update the script that renders the pie chart. Specifically, we will change the ctx.arc call to reference the data we just stored in the pieData array.

 

for(var i = 0; i < data.length; i++)

{

                ctx.beginPath();

                ctx.moveTo(center[0],center[1]);

                ctx.arc(center[0],center[1],radius,pieData[i]['startAngle'],pieData[i]['endAngle'],false);

                ctx.lineTo(center[0],center[1]);

                ctx.closePath();

                ctx.fillStyle = colors[i];

                ctx.fill();

}

 

Next we will add some text to the page that will be used to display the value of the pie chart that the user is currently hovering over with the mouse.

 

<div>

        Hover over a slice

</div>

 

Now that we have a placeholder for the text, we can add the mousemove event that will first evaluate whether the user is within the radius of the chart and then do a bit of math to determine which slice the mouse is over.

 

$(“#canvas”).mousemove(function(e) {

                var x = Math.floor((e.pageX-$(“#canvas”).offset().left));

                var y = Math.floor((e.pageY-$(“#canvas”).offset().top));

                var fromCenterX = x – center[0];

                var fromCenterY = y – center[1];

                var fromCenter = Math.sqrt(Math.pow(Math.abs(fromCenterX), 2) + Math.pow(Math.abs(fromCenterY), 2 ));

 

                if (fromCenter <= radius) {

                                var angle = Math.atan2(fromCenterY, fromCenterX);

                                if (angle < 0) angle = 2 * Math.PI + angle; // normalize

 

                                for (var slice in pieData) {

                                                if (angle >= pieData[slice]['startAngle'] && angle <= pieData[slice]['endAngle']) {

                                                                $(“div.sliceValue”).text(“The value for the slice is ” + pieData[slice]['value']);

                                                                return;

                                                }

                                }

                }

});

 

If all went well, you should now have a working demo. If not, you can view the source of the working demo linked below.

 

http://yojimbocorp.com/demos/pieChart2.html

January 17, 2012

Creating a simple pie chart with HTML5 Canvas

This week we take a look at how to use the HTML5 canvas element to create a simple pie chart. To begin, we need to define the element using the canvas tag. Please note the id, height, and width are required.

<canvas id=”canvas” width=”400″ height=”300″></canvas>

In order to interact with this canvas through Java script, we will need to first get the element by Id and then create a context.


<script type="text/javascript">

var canvas = document.getElementById(‘canvas’);

var ctx = canvas.getContext(“2d”);

</script>

These two steps are essentially all that is required to start drawing to the canvas. For this example, we will be drawing a simple pie chart, so let’s add some data. We’ll use some hard coded values for data in the interest of simplicity.

 

var data = [75,68,32,95,20,51];

var colors = ["#7E3817", "#C35817", "#EE9A4D", "#A0C544", "#348017", "#307D7E"];

var center = [canvas.width / 2, canvas.height / 2];

var radius = Math.min(canvas.width, canvas.height) / 2;

var lastPosition = 0, total = 0;

 

Next we will need to get the total value of all the data being represented in the pie chart. This is required in order to scale the size of each slice correctly. To do this, we simply need to loop through the data array and add each value into the ‘total’ variable defined above.

 

for(var i in data) { total += data[i]; }

 

And finally, we are ready start using canvas to draw each slice of the pie. To do this, we will loop through the data array again and call the canvas methods to create and fill the shapes.

 

for (var i = 0; i < data.length; i++) {

ctx.fillStyle = colors[i];

ctx.beginPath();

ctx.moveTo(center[0],center[1]);

ctx.arc(center[0],center[1],radius,lastPosition,lastPosition+(Math.PI*2*(data[i]/total)),false);

ctx.lineTo(center[0],center[1]);

ctx.fill();

lastPosition += Math.PI*2*(data[i]/total);

}

 

Loading the page in an HTML5 enabled browser should result in our chart being rendered as follows.

 

pie_chart

 

Next week we will take a look at adding labels to the chart as well as adding some user interaction with animation.

January 12, 2012

Large WCF Requests, Don’t forget httpRuntime settings

Recently, I was working on a project where the client was passing an array of bytes (byte[]) into a WCF service method. The array represented a file the user wanted to upload to the server. The WCF service was throwing cryptic error messages when the user attempted to upload a large file. There are obvious issues with this approach, but without a redesign of the upload process, my client was looking for a quick fix. There are many articles that cover the various options that one has to adjust under serviceBehaviors for WCF (e.g. maxReceivedMessageSize), but increasing all of those to their maximum value DID NOT solve the problem.

After looking at the issue through a packet filter, I finally figured out that IIS was rejecting the request at a level above WCF and then I remember the httpRuntime config section. You can read about it here:

http://msdn.microsoft.com/en-us/library/e1f13641

The relevant attribute is maxRequestLength

The default is 4096KB as of the writing of this post.

Obviously, no matter what other settings you tweak, if you run into this limit, IIS will block the request.

This can also come up when writing plain old vanilla ASP.NET file upload if you’re not streaming the file to the server.

Streaming files to your server would avoid running into these problems, but if you’re in a pinch and working on an internal application used by a small number of users, this is the quick and dirty way to get around the limit.

Tags: ,
January 11, 2012

Homegrown Dynamic IP Tracking

I often need to remotely access my home computer. Since dynamic DNS services are not always available to me, I needed a way to be notified when my ISP issued a new IP address. The first task was to choose one of the many services that happily provide your public address. The ideal candidate would be one that gives a relatively easy to parse response. After a bit of searching, I found that the nice folks over at http://dyndns.org offer just that with http://checkip.dyndns.org

The .Net WebClient provides an easy means to retrieve the response:

var client = new WebClient();

var response = client.DownloadString(“http://checkip.dyndns.org”);

 

After a bit of simple parsing, we have our public IP address:

const string startToken = “Current IP Address: “;

var startIndex = response.IndexOf(startToken);

startIndex += startToken.Length;

var endIndex = response.IndexOf(“</body>”);

var ip = response.Substring(startIndex, endIndex – startIndex);

 

Now that we have the public IP address, notification becomes a task for the .Net SmtpClient. Using email as the notification method certainly isn’t the only solution, but has the benefit of being private. For this particular project, I’m using an App.config to populate properties of the SmtpClient.

var appSettings = ConfigurationManager.AppSettings;

var email = Convert.ToString(appSettings["EmailAddress"]);

var displayName = Convert.ToString(appSettings["DisplayName"]);

var fromAddress = new MailAddress(email, displayName);

var toAddress = new MailAddress(email, displayName);

var fromPassword = Convert.ToString(appSettings["EmailPassword"]);

var smtp = new SmtpClient

{

Host = Convert.ToString(appSettings["EmailHost"]),

Port = Convert.ToInt32(appSettings["EmailPort"]),

EnableSsl = Convert.ToBoolean(appSettings["EnableSsl"]),

DeliveryMethod = SmtpDeliveryMethod.Network,

UseDefaultCredentials = false,

Credentials = new NetworkCredential(fromAddress.Address, fromPassword)

};

 

And now, sending the message is simply a matter of constructing a .Net MailMessage and passing it to the SmtpClient Send method.

var subject = “Current Home IP Address: ” + ip;

var body = subject;

using (var message = new MailMessage(fromAddress, toAddress)

{

Subject = subject,

Body = body

})

{

smtp.Send(message);

}

 

It is certainly feasible to schedule a daily task for this to run, but I decided to wrap it into a service so notification emails are only sent if the public IP changes. The entire project can be found in our brand new github repository linked below.

https://github.com/yojimbocorp/blog-posts

January 2, 2012

How to get the value of a custom attribute in C#

In a recent project, I needed to get the value of a custom attribute that was set on various class properties. More precisely, the custom attribute was a string that needed to be evaluated programmatically to see if it matched a given value. This particular case came up during unit testing of localized strings in a web application, but may be applicable to a wide variety of use cases involving custom attributes.

In this example, there is a class that sets a custom attribute like this:

public class UserAccount

{

[LocalizedDisplayName("SOME_RESOURCE_ID")]

public string UserAccountName { get; set; }

}

This attribute is referenced throughout the application to display the correct text for the current culture, but the problem is that there is no inherent mechanism to get the actual string value of the resource associated with the given class property for testing purposes. The solution was to create a utility method which does just that:

public static object GetAttributeValue(Type objectType, string propertyName, Type attributeType,

string attributePropertyName)

{

var propertyInfo = objectType.GetProperty(propertyName);

if (propertyInfo != null)

{

if (Attribute.IsDefined(propertyInfo, attributeType))

{

var attributeInstance = Attribute.GetCustomAttribute(propertyInfo, attributeType);

if (attributeInstance != null)

{

foreach (PropertyInfo info in attributeType.GetProperties())

{

if (info.CanRead &&

String.Compare(info.Name, attributePropertyName,

StringComparison.InvariantCultureIgnoreCase) == 0)

{

return info.GetValue(attributeInstance, null);

}

}

}

}

}

return null;

}

This method is in an AttributeUtil class. An example call to the method looks like this:

var value = AttributeUtil.GetAttributeValue(typeof(UserAccount), “UserAccountId”,

typeof(LocalizedDisplayNameAttribute),”DisplayName”);

It was surprisingly hard to find relevant information for this particular problem, so I hope you found this useful. A complete example project is available in our codeplex repository.

GetAttributeValue

As an exercise to the reader, you may choose to package GetAttributeValue as an extension method on the Attribute class itself. Also, beware that this will not work against attributes whose value is an array type, because we’re passing NULL as the second parameter to PropertyInfo.GetValue method.

Tags:
December 27, 2011

How to Show the Silverlight Busy Indicator When Your Task Has to Run on the UI Thread

Warning: this is a “hack” to be used when all other options have been exhausted

Recently, I was working on a project that required me to implement export to Excel from a third-party grid. The task was quite simple as the grid control already had a method for the purpose of creating a workbook. However, when using the method from a button click handler my entire UI would freeze for the duration of the export. The export had to be performed on the UI thread, because it relied on a method located on the control (which in turn touched other properties of the control). I knew I couldn’t get around the freeze, but wanted to display a busy indicator. After searching the web I failed to find a solution. The issue is that the busy indicator itself has to use the UI thread to begin animating and Silverlight queues user interface updates (to improve performance), so just because you set the ‘IsBusy’ property true, you’re not guaranteed that the user has seen the indicator.

I had some work to do before kicking off the export and was already spawning a background thread via the method ThreadPool.QueueUserWorkItem.

However, the only way to get the busy indicator to display was to add a Sleep operation as the first step of my thread and I wasn’t sure how long I needed to sleep before I had some indication that the busy indicator has displayed.

My final workaround consisted of the following steps:

1)      Implement the LayoutUpdated event on the BusyIndicator instance. Inside this event, I set a private bool field to true. The field is marked as volatile to prevent compiler optimizations which sometimes cause multiple threads to see a different value (the alternative would have been to use another field and lock on it when touching the bool).

2)      In my button click event, I set the private field to false anytime I set the ‘IsBusy’ property of the busy indicator to true.

3)      I then added this code block at the top of my background task (basically waiting for the busy indicator to update its layout – i.e. display itself) before doing any other work:

while (!_hasBusyIndicatorUpdatedLayout)
{
         Thread.Sleep(100);
}

After exiting this while loop, I kicked off the call to process my Excel export on the UI thread.

There are a couple of issues with this solution:

1)      It’s a hack

2)      The busy indicator’s animation will be frozen (along with the entire application)

However, the alternative was to have a frozen UI all around. I am attaching a sample project implementing all of the above. If you want to see what happens without the check, just comment out the ‘while’ loop shown above.

If anyone has come up with a more elegant solution or improves upon this, please comment below.

The complate click handler looks like this:

private void ButtonClick(object sender, RoutedEventArgs e)
        {
            SetBusy("Exporting data...");

            ThreadPool.QueueUserWorkItem((state) =>
                                             {
                                                 // this is how we check if the busy indicator has 
                                                 // started to display to the user
                                                 while (!_hasBusyIndicatorUpdatedLayout)
                                                 {
                                                     Thread.Sleep(100);
                                                 }

                                                 // now we can perform our busy task
                                                 DoBusyWorkOnUiThread();
                                                 RemoveBusy();
                                             });
        }

Download the project below for the full implementation. If you comment out the line in the constructor that does this:

busyIndicator.LayoutUpdated += BusyIndicatorLayoutUpdated;

You will see that the busy indicator no longer displays.

Download Project

Tags: , ,
September 10, 2011

Editing SSRS 2008 Projects in Visual Studio 2010

Microsoft has been releasing updates to Visual Studio at an ever faster pace. Unfortunately, one technology was left behind. The ability to edit SQL Server Reporting Services (SSRS) projects inside Visual Studio is actually provided by Business Intelligence Studio, which ships only when SQL Server rolls out an update. As a result, up until recently, one had to have a copy of Visual Studio 2008 (or rather the Business Intelligence portion of it that is installed by SQL Server 2008) to edit SSRS 2008 projects. It’s even worse for people who are still using SSRS 2005 (they have to have Visual Studio 2005 if they didn’t want to upgrade their projects). For those of you willing to move to SSRS 2008 and who would like to use a single editor (with the corresponding TFS integration), you now have the option to install Denali CTP 3 (or a more current version, depending on when you find this post) to get the update to Visual Studio 2010 that finally lets you edit SSRS project types. Don’t worry, you don’t have to install the entire CTP product, just the business intelligence portion of it.

The steps (as of 9/1/2011) are:

1) Make sure you’ve updated to Visual Studio 2010 Service Pack 1

2) Download the appropriate version of Denali CTP 3 (I ran the 1033\SQL Server Denali CTP 3 Install.html and followed the links to the version I wanted — x86 or x64). When you launch the installer you can choose the “Express” option and it will still let you install Business Intelligence Studio.

When you install Denali, only install Business Intelligence Studio as illustrated by the screeshot below (unless you want to play with all of it).

Denali Installer Options

Denali Installer Options

Warning: If after following the steps above Visual Studio 2010 warns you that you do not have the complete Service Pack 1 installed, you may have to re-apply Visual Studio 2010 Service Pack 1. Subsequently, the installer may prompt you for the MSI containing the latest Silverlight SDK. This page will help you deal with that error. This happened to me on one of my development machines, but not the other. Read this article for more: Visual studio 2010 sp1 error: silverlight_sdk.msi unavailable. This is one of the dangers of installing CTP products but a small price to pay for the convenience of editing SSRS 2008 projects inside Visual Studio 2010.

 

June 26, 2011

Facebook Connect Authentication injected into ASP.NET MVC 3 Forms Authentication

I was recently admiring the simplicity of the PHP example on how to implement Server-side Authentication using OAuth 2.0 on facebook.com. You can read the full document here: http://developers.facebook.com/docs/authentication/

The code below assumes that you’ve read the entire “Server-side Flow” section and have made some attempt to understand it.

I used Visual Studio 2010 and generated a new project of type “ASP.NET MVC 3 Web Application”, on the next step I choose Internet Application (once you’ve mastered the code below you’ll also be able to do this starting with Empty).

Next you can do some cleanup:

Delete Controllers/AccountController.cs

Delete Views/Account

In web.config you can delete the sections <membership>, <profile> and<roleManager>. You can also delete the <connectionStrings> section (or at least the one with the name “ApplicationServices”).

The reason for all this is that we won’t be using any of the built-in profile/membership/roles providers or the database connection string they rely upon.

Update the <authentication> node to look like this:

<authentication mode=”Forms”>
<!– we don’t want the expiration to slide, because we respect the Facebook token expiration settings –>
<forms loginUrl=”~/Facebook/Login” slidingExpiration=”false” />
</authentication>

Create a fake domain in your C:\Windows\System32\drivers\etc\hosts file (make sure you use this domain when you setup your Facebook application). For the sake of discussion I am going to use dev.somefakedomain.com. All you have to do is add this line to the file (you can edit it via Notepad – make sure you launch Notepad with Administrator privileges):

127.0.0.1 dev.somefakedomain.com

Modify your project settings to look like this image:

project_settings

The port can be anything you like, as long as it’s not used by another application on your machine (I use whatever port Visual Studio assigned).

Add a new controller. I called it Facebook (if you don’t call it “Facebook”, don’t forget to change the loginUrl part of the <forms> tag in web.config).

Replace the entire body of your controller with this code:

using System;
using System.Collections.Generic;
using System.Web.Mvc;
using System.Security.Cryptography;
using System.Net;
using System.IO;
using System.Web.Script.Serialization;
using System.Web.Security;

namespace FacebookLogin.Controllers
{
    public class FacebookController : Controller
    {
        // these really should be in a config file:
        private string appId = "getyourown!";
        private string appSecret = "lookup your own!";
        /*
         * This is important. Using the default 'localhost' for debugging will fail,
         * because of problems with writing cookies and because Facebook requires a real
         * domain when you setup your application. What you have to do is create a 'fake'
         * domain by editing your C:\Windows\System32\drivers\etc\hosts file.
         * Google how to do this */
        private string authorizeUrl = "http://dev.somefakedomain.com:2064/Facebook/Login";

        public ActionResult Login()
        {
            try
            {
                string error = Request["error"];
                if (!String.IsNullOrEmpty(error))
                {
                    ViewBag.ErrorMessage = Request["error_description"];
                }
                else
                {
                    string code = Request["code"];
                    // when we get redirected here by Forms Authentication we'll
                    // have a ReturnUrl indicating the page we came from
                    string returnUrl = Request.QueryString["ReturnUrl"];

                    if (String.IsNullOrEmpty(code))
                    {
                        // CSRF protection
                        var hashBytes = new MD5CryptoServiceProvider().ComputeHash(Guid.NewGuid().ToByteArray());
                        string state = Convert.ToBase64String(hashBytes);
                        Session["state"] = state;

                        // add the return Url to the state parameter so Facebook can send it all back to us
                        if (!String.IsNullOrEmpty(returnUrl))
                        {
                            state += returnUrl;
                        }

                        // good programmers encode strings before passing them to a query string
                        state = Url.Encode(state);

                        // don't forget to change the "scope" values listed below
                        // to something appropriate for your facebook application
                        // facebook warns not to get greedy as users may deny access
                        string redirectUrl = "http://www.facebook.com/dialog/oauth?client_id=" + appId +
                                             "&redirect_uri=" +
                                             Url.Encode(authorizeUrl) +
                                             "&scope=publish_stream&state=" +
                                             state;
                        return Redirect(redirectUrl);
                    }

                    string sessionState = Convert.ToString(Session["state"]);
                    string requestState = Request.QueryString["state"];

                    if (!String.IsNullOrEmpty(requestState) &&
                        !String.IsNullOrEmpty(sessionState) &&
                        requestState.Length >= sessionState.Length &&
                        requestState.Substring(0, sessionState.Length) == sessionState)
                    {
                        string tokenUrl = "https://graph.facebook.com/oauth/access_token?client_id=" + appId +
                                          "&redirect_uri=" + Url.Encode(authorizeUrl) +
                                          "&client_secret=" + appSecret +
                                          "&code=" + code;
                        string response = GetPageContent(tokenUrl);

                        var responseDictionary = ParseQueryString(response);
                        if (responseDictionary.ContainsKey("access_token"))
                        {
                            // note: you don't HAVE to respect this, as long as you
                            // get a new token before trying to use the FB API
                            double facebookTokenExpirationSeconds =
                                Convert.ToDouble(responseDictionary["expires"]);

                            // Note: you may want to store responseDictionary["access_token"] and
                            // facebookTokenExpirationSeconds somewhere so you can use it for other FB API requests
                            string graphUrl = "https://graph.facebook.com/me?access_token=" +
                                              responseDictionary["access_token"];

                            var serializer = new JavaScriptSerializer();
                            dynamic facebookUser = serializer.DeserializeObject(GetPageContent(graphUrl));

                            // grab facebook name and Id to use as our forms authentication ticket
                            string facebookName = facebookUser["name"];
                            long facebookUserId = Convert.ToInt64(facebookUser["id"]);

                            // get the cookie the way forms authentication would put it together.
                            //Note: I am using the facebookUserId as the "username" as it's guaranteed to be unique
                            var authCookie = FormsAuthentication.GetAuthCookie(facebookUserId.ToString(), true);
                            var ticket = FormsAuthentication.Decrypt(authCookie.Value);
                            // we want to change the expiration of our forms authentication cookie
                            // to match the token expiration date, but you can also use your own expiration
                            DateTime expiration = ticket.IssueDate.AddSeconds(facebookTokenExpirationSeconds);
                            var newTicket = new FormsAuthenticationTicket(ticket.Version, ticket.Name,
                                ticket.IssueDate, expiration, ticket.IsPersistent, facebookName);

                            // encrypt the cookie again
                            authCookie.Value = FormsAuthentication.Encrypt(newTicket);

                            // manually set it (instead of calling FormsAuthentication.SetAuthCookie)
                            Response.Cookies.Add(authCookie);

                            // If we added the Redirect Url to our 'state', grab it and redirect
                            if (requestState.Length > sessionState.Length)
                            {
                                returnUrl = requestState.Substring(sessionState.Length);
                                if (!String.IsNullOrEmpty(returnUrl))
                                {
                                    return Redirect(returnUrl);
                                }
                            }

                            // otherwise redirect back to Home or whatever your default page is
                            return RedirectToAction("Index", "Home");
                        }
                        else
                        {
                            ViewBag.ErrorMessage = "Facebook authorization replied with this invalid response: " +
                                response;
                        }

                    }
                    else
                    {
                        ViewBag.ErrorMessage =
                            "There is a problem with the redirect from Facebook. You may be a victim of CSRF.";
                    }

                }
            }
            catch (Exception ex)
            {
                ViewBag.ErrorMessage = "Login failed with this exception: " + ex.Message;
            }

            return View();
        }

        public ActionResult Logout()
        {
            FormsAuthentication.SignOut();
            return RedirectToAction("Index", "Home");
        }

        private static string GetPageContent(string url)
        {
            var request = WebRequest.Create(url);
            var reader = new StreamReader(request.GetResponse().GetResponseStream());
            return reader.ReadToEnd();
        }

        private static IDictionary ParseQueryString(string query)
        {
            var result = new Dictionary();

            // if string is null, empty or whitespace
            if (string.IsNullOrEmpty(query) || query.Trim().Length == 0)
            {
                return result;
            }

            foreach (var pair in query.Split("&".ToCharArray()))
            {
                if (!String.IsNullOrEmpty(pair))
                {
                    var pairParts = pair.Split("=".ToCharArray());
                    if (pairParts.Length == 2)
                    {
                        result.Add(pairParts[0], pairParts[1]);
                    }
                }
            }

            return result;
        }
    }
}

Add a Login view and replace it with code:

@{
    ViewBag.Title = "Facebook Login Error";
}

@if (!String.IsNullOrEmpty(ViewBag.ErrorMessage))
{

Error: @ViewBag.ErrorMessage

}
else
{

Facebook login encountered an error without a description.

}

Replace the contents of _LogOnPartial.cshtml with this code:

@if (Request.IsAuthenticated)
{
    string facebookName = "BLANK";
    FormsIdentity ident = User.Identity as FormsIdentity;

    if (ident != null)
    {
        FormsAuthenticationTicket ticket = ident.Ticket;
        facebookName = ticket.UserData;
    }

    Welcome @facebookName!
    [ @Html.ActionLink("Logout", "Logout", "Facebook") ]
}
else
{
    @:[ @Html.ActionLink("Login", "Login", "Facebook") ]
}

Now that you’ve done this you can use the built-in [Authorize] tag on any controller method and the user will get sent to your Facebook/Login controller. The sky is the limit from here.

You could also use the Facebook SDK on codeplex, which provides a richer programming model and more functionality, but if you want to understand what’s happening behing the scenes, this gives you a good idea of the handshake required for successful Facebook authentication.

Note that this example uses the built-in Session object, which will fail on a web server farm. You have a few options:

1) You can change the code that uses Session to rely on cookies instead

2) You can use a session provider that relies on a database or a separate app server

3) You can get rid of that code if you don’t care about CSRF

If there is interest, I will post a working project example.

January 25, 2011

Re-index All Tables in your SQL Database: Short & Dirty Script

I am not advocating that you use this in a production system, but if you’re looking to re-index all tables in your database (say on development or test), here is a short script for it:

exec sp_msforeachtable 'dbcc dbreindex(''?'', '''', 80)'

 

Note, the last parameter controls the fill factor, set it to 0 to re-use the last known fill factor or read more about it in Books Online. With sp_msforeachtable you’re looping through all tables in the database and executing the provided script. As you can see above you use a question mark (?) to substitute the table name. This is a decent alternative to manual looping through INFORMATION_SCHEMA.TABLES which is another way to get a list of all tables in your database.

If you were just looking to obtain a dbcc script you can run this command and it will generate the equivalent script that is run by the first command I showed:

select 
	'dbcc dbreindex(''' + TABLE_NAME + ''', '''', 80)' 
from 
	INFORMATION_SCHEMA.TABLES 
where 
	TABLE_TYPE = 'BASE TABLE'
January 3, 2011

T-SQL: Parsing data type xml

If you only occasionally dabble in XML and XPath utilizing the XML data type in SQL Server can be a bit daunting. I found it very useful in passing parameters to stored procedures containing object graphs. The same can be accomplished the table parameter if you only have a list of one type of objects, but if you need to pass an object graph of parent/child objects of different types, using XML will be your best bet.

Below I demonstrate how one could represent a list of Contact objects in an XML document and subsequently access the data via a select statement. the @Contacts parameter could be used as a parameter to a stored procedure if you’re running SQL 2005 or higher. Use your imagination for the rest.

declare @Contacts xml

set @Contacts =
    ‘<root>
    <Contact>
        <ContactID>1</ContactID>
        <Email>contact1@contact1.com</Email>
    </Contact>
    <Contact>
        <ContactID>2</ContactID>
        <Email>contact2@contact2.com</Email>
    </Contact>
    <Contact>
        <ContactID>3</ContactID>
        <Email>contact3@contact3.com</Email>
    </Contact>
    </root>’

select
    ContactID = Contact.value(‘ContactID[1]‘, ‘int’),
    Email = Contact.value(‘Email[1]‘, ‘varchar(255)’)
from
    @Contacts.nodes(‘root/Contact’) as Contacts([Contact])

Tags: , ,
Follow

Get every new post delivered to your Inbox.