Adam Stirtan

Horizontal and vertical alignment with CSS

2010-06-16T18:52:00.000-07:00

I've been working on a lot of lately on web design in various capacities. One problem I wanted to solve once and for all was centering #div tags either horizontally, vertically or both. Obviously this is a simple enough problem and I'm sure most of you can quickly align horizontally without a headache but vertical sometimes needs some extra considerations.

Our approach to this will use only CSS and proves to be quite readable later on.

Horizontal alignment

.cssClass
{
    margin: 0 auto;
    width: 200px;
    height: 200px;
}

Well that was easy, we got our div in the middle by setting the margin on the left and right (with a single property) to be automatically determined. Because we specify what the width and height is,the browser can then compute how much area is left on both sides and then divide that in half.

Vertical alignment

To achieve the vertical alignment we must know in advance the dimensions of the div. Secondly, we set the positioning to be absolute in order to detach the div and have free roam with it. The div is moved horizontally to the center of the window first, but that won't cut it yet as the actual point which is centered is the top left corner. From there, because we know the div dimensions we can offset it to the left and up by half the width and height which centers the actual center in the window center. The only special case here is when the div cannot have a side which is am even number, but we leave that up to the reader to sort out as necessary.

.cssClass
{
    width: 300px;
    height: 200px;
    position: absolute;
    left: 50%;
    top: 50%;
    margin: -100px 0 0 -150px;
}

Create radius based circle on Bing maps

2010-05-19T05:20:00.000-07:00

I've been working with Microsoft Virtual Earth as of lately. One of the requirements for my project has been to create geofence areas based on some latitude longitude which extends out on the map with a given radius. We then use these regions as entry/exit points for moving entities on the map, hence calling them geofences.

From the picture above, you can see that a geofence has a centroid which is a distinct LatLong object, how this is defined is up to you. In my example I found the coordinate tuple by allowing the user to geocode or reverse geocode, your mileage may vary in how you need to do this. The simplest example I could think of would be the Click event on the VEMap class whereby you simply extract the LatLong by the pixel coordinates.

So let's assume we have that LatLong object already. The following method will then define a ring around that point with a given radius in either miles or KM according to the radius of the Earth in either KM or miles.

public static Shape CreateCircle(LatLong Location, double Radius, double EarthRadius)


    {
        double lat = Utilities.ToRadians((double)Location.Latitude);
        double lon = Utilities.ToRadians((double)Location.Longitude);
        double angularDistance = Radius / EarthRadius;

        List points = new List();
        for (int i = 360; i > 0; i--)
        {
            double d = Utilities.ToRadians((double)i);
            double latRadians = Math.Asin(Math.Sin(lat) * Math.Cos(angularDistance) + Math.Cos(lat) * Math.Sin(angularDistance) * Math.Cos(d));
            double lonRadians = lon + Math.Atan2(Math.Sin(d) * Math.Sin(angularDistance) * Math.Cos(lat), Math.Cos(angularDistance) - Math.Sin(lat) * Math.Sin(latRadians));

            points.Add(new LatLong(Utilities.ToDegrees(latRadians), Utilities.ToDegrees(lonRadians)));
        }
        return new Shape(ShapeType.Polygon, points);

}

From the Utilities class:

public static double EarthRadiusKM = 6367.0;


    public static double EarthRadiusMiles = 3956.0;

    public static double ToRadians(double d)
    {
        return d * (Math.PI / 180);
    }

    public static double ToDegrees(double r)
    {
        return r * 180 / Math.PI;

}

The main thing to recognize in the first method is that we are creating the ring as a collection of 360 LatLong objects around the centroid. Points are defined in decreasing order such that the VEShape returned by the method can be converted easily to WKT to be stored in SQL using a geography data type. If we were to change the loop to start the index at 0 and count to 360, the ring of the Polygon would be invalid and subsequently rejected by SQL (first hand knowledge).

Finally, we can record in SQL this new geofence by calling a stored procedure "AddGeofence" which I've defined to accept the set of converted points as WKT.

public static string AddGeofence(Shape Geofence, string ClientID, string Creator)

    {
        DbCommand command = GenericDataAccess.CreateCommand();
        command.CommandText = "AddGeofence";

        DbParameter param = command.CreateParameter();
        param.ParameterName = "@ClientID";
        param.Value = ClientID;
        param.DbType = DbType.Int32;
        command.Parameters.Add(param);

        param = command.CreateParameter();
        param.ParameterName = "@Name";
        param.Value = Geofence.Title;
        param.DbType = DbType.String;
        param.Size = 250;
        command.Parameters.Add(param);

        param = command.CreateParameter();
        param.ParameterName = "@Description";
        param.Value = Geofence.Description;
        param.DbType = DbType.String;
        param.Size = 250;
        command.Parameters.Add(param);

        param = command.CreateParameter();
        param.ParameterName = "@Geofence";
        param.Value = GeofenceFunctions.ShapeToWKT(Geofence);
        param.DbType = DbType.String;
        command.Parameters.Add(param);

        return GenericDataAccess.ExecuteScalar(command);

}

And finally in order to convert the ring to the WKT:

public static string ShapeToWKT(Shape shape)

    {
        string wkt = "";

        switch (shape.Type)
        {
            case ShapeType.Pushpin:
                wkt = PushpinToWKT(shape.Points);
                break;
            case ShapeType.Polyline:
                wkt = PolylineToWKT(shape.Points);
                break;
            case ShapeType.Polygon:
                wkt = PolygonToWKT(shape.Points);
                break;
            default:
                break;
        }
        return wkt;
    }

    private static string PolygonToWKT(List points)
    {
        string wkt = "POLYGON((";

        for (int i = 0; i < points.Count; i++)
        {
            wkt += points[i].Longitude.ToString();
            wkt += " ";
            wkt += points[i].Latitude.ToString();

            if (i < points.Count - 1) wkt += ",";
        }
        wkt += "))";

        return wkt;

}

Self Organizing Map (Kohonen Network)

2010-04-07T19:01:00.000-07:00

Well its about time I shared something new with everyone. My latest and greatest is an implementation of Kohonen networks or otherwise known as the self organizing map. In this software package I've implemented this network for two tasks using the same algorithm to show the diversity which it can easily manage.

Download

In the image to the left, you'll see the resulting behavior of the self organizing map tasked with organizing RGB color values. While this is simply a toy problem, it does however serve as a great introduction to the topic.

Secondly (and why the download is so large) I've used the SOM for image clustering. Here I've taken statistical measures of image data sets to abstract away the dependence on the actual dimensions of the images and ensure focus is kept on the information itself. The result is the algorithm being capable of clustering images very accurately. I've included two data sets for you to try, the first of which is a set of cars I found by searching Google's image search and secondly a very large data set of web icons I've used very often in my projects. The result (if set large enough) is a really cool wallpaper all done with AI.

It's coolness in action. Hope you all enjoy.

ASP.Net Nested Repeater with Paging Tutorial

2010-03-10T08:28:00.000-08:00

In developing web applications using ASP.Net one of the most hated components of programmers is nested repeaters. I covered this topic before another tutorial found here. What I'd like to show you now is how to extend this further by paging the data source such that groups of elements show on individual pages, similar to how Google's search results are displayed.

We'll start by showing you the desired result. Pictured here is a paged and nested repeater. The outer repeater is a snippet from a discussion board where users can post messages to others. For each of these it is possible to comment on them. This is where we get the nesting requirement, as the comments are specific to the outer repeater items.

At bottom is a button which allows for navigating to the next page. In this picture we are displaying the first page, if we weren't (and not on the last page) another button would be visible allowing the action "Previous" to be handled.

Now that we have the concept let's look at how we accomplish this behavior. It should be clear that we require two distinct data sources and two repeaters. One repeater inside the other.

I have created a method which is executed inside the Page_Load(..) method of the web page as follows:

protected void SetupPaging()

{

PagedDataSource objPds = new PagedDataSource();

DataTable dt = //Retrieve outer DataTable type

bjPds.DataSource = dt.DefaultView;

objPds.AllowPaging = true;

objPds.PageSize = 10;

objPds.CurrentPageIndex = CurrentPage;

btPrev.Visible = !objPds.IsFirstPage;

btNext.Visible = !objPds.IsLastPage;

DataSet ds = new DataSet();

ds.Tables.Add(dt);

ds.Tables.Add(// Get inner repeater DataTable);

DataColumn dcPosts = ds.Tables[0].Columns["DiscussionPostID"];

DataColumn dcReplies = ds.Tables[1].Columns["DiscussionPostID"];

ds.Relations.Add(new DataRelation("replies", dcPosts, dcReplies));

rptDiscussion.DataSource = objPds;

rptDiscussion.DataBind();

}

So lets examine this piece by piece to see how this works. First we instantiate a PagedDataSource object, this type has properties for setting up how many items per page and allows for a DataPager object to control which page it is currently on. The main thing we focus on however is where we set its DataSource property. We set this to the outer table's DefaultView.

Moving on, we create a DataSet, this is going to be the aggregate of both outer and inner tables. To setup the nesting we associate the two tables with a relationship. This relationship assigns which columns in both table relate to each other. In my example image, the relationship was in the outer repeater (Primary Key) and its corresponding discussion post. Each comment was associated with this discussion post and as such we assign the column as "DiscussionPostID".

Finally we add this association to the DataSet object and bind the repeater to the PagedDataSource. Our repeater is now ready for action, the only thing left is to add some controls for getting the current page. This is a straight forward method which we add to the code behind file as follows:

public int CurrentPage

{

get

{

object o = this.ViewState["_CurrentPage"];

if (o == null)

return 0;

else

return (int)o;

}

set

{

this.ViewState["_CurrentPage"] = value;

}

That completes our tutorial for now, if you have any questions leave a comment and I'll get back to as best I can.

PSO for Evolving Game Playing Agents

2010-03-09T07:38:00.000-08:00

Tomorrow I will be giving my final presentation on the progress of my computer science thesis. The title of my paper is 'Particle Swarm Optimization for Evolving Game Playing Agents' and focuses on implementing PSO for the game of Super Mario Bros. As part of this presentation I've prepared a new set of PSO slides which walks through some background information and the details on how it is applied to games.

Download Slides

My paper will attempt to demonstrate the viability for PSO game agents in a generalized way which can be applied to any game type. I've given high level examples of how this can work for the game of Checkers and a brief overview for how it would be applied to a 3D first person shooter such as Halo. While these components are not implemented, it was the goal of the project to rather develop a method for PSO which simply functioned for games.

The result, in my opinion is astounding. I've posted videos before of the Mario PSO agent in action. My only regret is that I think I was a little late to the Mario competition party and I won't be able to compete this year. It was rumored that during the next round they will be introducing water to the game. I'm confident in the algorithm to be adaptable, more so than static hard coded search strategies some other people tried. Perhaps I'll get my chance then.

Happy coding! Keep fit and have fun.

Cascade Correlation Seminar

2010-02-17T16:07:00.000-08:00

I thought I would make available some presentation slides I have created recently. I will be giving a presentation in my course for artificial neural networks tomorrow on cascade correlation.

Download Slides

For those unacquainted with cascade correlation it is a dynamic neural network which as it is processing determines its own topology. Networks begin with only input and output layers, and as necessary hidden units are introduced to the network.

The process requires to back propagation of error signals and often results in very unique solutions to problems. Cascade correlation's learning process is many times quicker than traditional back propagation, but in smaller problems its accuracy suffers. However, due to its dynamical creation, it tends to scale to much larger problems more efficiently while maintaining its break neck speeds of classification.

It's something I would like to experiment with over the summer once things calm down a bit. There are a few unexplored areas of study involved which I think potential papers are lurking.

Evolutionary Neural Networks using Genetic Algorithms and Particle Swarm Optimization in XNA

2010-01-04T19:23:00.000-08:00

I've finished my XNA simulation for my machine learning course this previous semester and am posting my resulting source code for all those interested. The project was an open ended venture in to machine learning. I chose to implement evolutionary neural networks and perform a comparative analysis between two biologically inspired algorithms as the basis for the neural network's weight adjustments.

Download Source Code

The simulation involves no user interaction beyond the included Settings.ini file where parameters of the simulation can be adjusted. This includes learning rates, PSO and GA settings and how long the simulation runs per generation.

If someone is looking for a proper implementation of a neural network in C# one can surely be found here which could be used in any area of necessity, there is nothing specific to this project.

Using just about all fathomable parameters I have found with this experimentation that PSO finds more optimal weights for the neural networks. They are found faster and retainment of these solutions are better compared to GA. However it should be noted that adaptive mutation is not implemented; that approach would likely increase the chances for GA to consistently compete in later generations.

Evolutionary Tanks

2009-11-28T20:14:00.000-08:00

Here is the latest video from my machine learning course. This is my term project where I use evolutionary algorithms for weight updates in an artificial neural network. It was written to simulate a game for a better visual understanding of the learning capabilities. More statistics are going to be included, but I thought I'd share with everyone and bask in the glory that the learning is in fact taking place.

Blue tanks update using a genetic algorithm and red tanks use particle swarm optimization. This is a snapshot of several successive generations starting from the beginning all the way to where over fitting has occurred.

Once I clean up the source code more I'll share it here as well.

Engagement Photos

2009-11-27T17:14:00.003-08:00

Here's a quick gallery of some of recently taken engagement photos. They turned out really well, by that I mean of course I don't look like an idiot in all of them.

Particle Swarm Optimization Tutorial Slides

2009-11-25T08:06:00.001-08:00

Tomorrow marks the day of a seminar I will be holding in my Machine Learning course at Brock University. I have made my power point presentation slides available to all those interested. It provides an overview of how the optimization technique is formulated from biological inspirations to algorithm implementation to applications and examples. It features short videos found on the Internet and a sample video of my Mario PSO agent in its latest form.

It is targeted to a person with no such familiarity in PSO with a background in computer science or at least a passion for artificial intelligence.

Download Presentation Slides

PSO Agent

2009-11-21T16:26:00.001-08:00

Tonight I've organized my presentation for this coming Thursday. I will be holding a seminar in Machine Learning giving a tutorial on particle swarm optimization. As a part included in the presentation I'm demonstrating my Mario agent. Here is the latest video included in the slides. Note: It is still a work in progress but is clearly miles ahead of the last video which was posted.

The main difference between the two videos is this agent is being optimized in a very high dimensional space compared to the first one.

Automatic Torrent Starting with Dropbox and uTorrent

2009-11-19T20:30:00.000-08:00

Here is just a quick tip for simplifying things a bit. Users of popular torrent client uTorrent can use their Dropbox (www.dropbox.com) account to automatically start torrents while away from their PC.

1. Create a folder in your Dropbox where you will put .torrent files

2. Enable this setting in the Preferences for uTorrent

Now whenever you put a torrent in that folder, your home PC will sync through Dropbox and uTorrent will load the torrent file.

For those with iPhones and Dropbox, well, I don't need to tell you about the potential here.

Image Posterization

2009-11-10T06:23:00.001-08:00

Last night I decided to change my wallpaper. I usually keep something for a really long time on there that I absolutely love. So every now and then I need to keep it fresh. I settled on a cell shaded render of Master Chief from Halo 3 and that got my interested in making my own. My friend Jake is the Jesus of non photo-realistic rendering (NPR) so I set out to get some answers. (Side note: you can check out his work here)

It turns out that 2D image's aren't really turned in to cell shaded, that's more a 3D approach. Perhaps I should have listened more when Jake added cell shading to our ray tracer last year, but that's how it goes, I never listen to anyone. What I was looking for if anything at all was called posterization.

So I checked out the wiki page, I did a Google search and finally got a complete idea of how it's done. Then, I quickly hammered together a little C# application which implements the posterization algorithm.

Here are my results. In honor of Jake, I used his picture, I'm sure he'll be mad, but that's the point. The slider parameter controls how many areas of colors are to be seen. As far as I can tell it looks identical to Photoshop's Posterize filter.

Iif I can figure out how to upload files to Blogger I'll share the source code with everyone. It's really quite simple. But now, on to better things, like coffee and wedding photographers.

Proof of Concept Video

2009-11-04T10:44:00.000-08:00

This is the first video of the particle swarm model being applied to controlling Mario. It simply serves as a proof of concept that my model does in fact work. The decision making is still very early and lots more work is still on-going. I just thought I'd share with you all the fact that this all hasn't been a complete waste of effort.

So without any further fluff, this is my first draft of my Mario artificial intelligence being controlled entirely using the particle swarm optimization model.

Swarm Dimensionality

2009-10-28T20:02:00.000-07:00

I've been racking my brain as of late with this Mario particle swarm. There are certain challenges in representing Mario's world in a way that particles can optimize a path for him. Particle swarms are meant for large search spaces where dimensionality is high. What I mean by that exactly is if an equation had multi variables and you need to find an approximate solution to that equation, you will be required to find values for all the variables which produces the best result. Consider the following equation:

a^2 * (b^c) - ((d + 7*ln(e)) / 3f * f^a)

Now I just randomly (not actually random of course) wrote that off the top of my head. Suppose you want to find values for the set of variables S = {a, b, c, d, e, f} which maximizes or minimizes the equation, which in other terms is optimization of its variables.

A particle swarm could easily be used, also perhaps a genetic algorithm, or many other methods. In the above equation there are a finite number of dimensions, in fact the cardinality of S. This directly means that particles will be flying around in 6 dimensional space attempting to find its optimal variable.

So, the problem I have been working on is how to represent Mario in a high dimensional manner such that particles can optimize the space. Over a coffee on the weekend I finally got it, so I'd like to share what I discovered.

Mario, if controlled by a human would have 5 possible buttons which can be pressed. Which are down, left, right, jump and speed. To control as an AI entity, we must make a decision and represent the next action as a series of boolean values where true means press it, false means do not. Pretty simple actually. The problem of course is in detection of all possible scenarios in a very short period of time, 40ms to be precise.

What I have come with essentially maps those 5 actions in to 3 dimensions. Imagine a cube where each vertex represents doing one of 2 actions (or not doing either), whether that is move left, jump, shoot, etc. Let's visualize this a little bit more.

Now let's say the x axis is the bottom, if a particle was 3 dimensional, it would be a 3-tuple, {x, y, z} where x, y, z are floating point numbers 0 <= x <= 1. Now there are three possibilities for the action on the axis which are left, right, none.

The same principle applies to the other dimensions in the same way. Finally we map values of the particles position in that dimension in the 3 regions. Left would be 0 <= x <= 1/3.

Now particles have a representation for the next action! Simple enough right? Well, we're not there yet. We need to make decisions for Mario based off what is going to happen to some extent. So what do we now?

At each frame which is drawn we know where Mario is, how many enemies are on the screen, where they are and what type they are. Exactly the same you would be able to extract if you were yourself playing the game. So using the most basic principles of calculus we can associate derivatives with enemies such that this simple principle holds:

for each (Sprite s in Scene)
s(time + 1) = s(time) + (direction * dx)

So there is the basic inference we're going with. Some special cases need to be accounted for however. For instance, a red koopa walks off a platform, but a green koopa turns around. Another more complicated instance is those flowers which come out of pipes. Things like that humans solve using trial and error and the same principle of determining if you think a street light will change to red. So we can handle those cases.

Finally, we need to expand our dimensionality. Seeing as we can represent the next state of Mario's world in 3 dimensions, it should in turn be possible to represent the next+n state using derivatives. This is going to affect particles because they are going to need to optimize across those dimensions. Effectively we can visual this by expanding the cube. Now it's difficult to imagine this, but just for fun let's look at two figures, the 4 dimensional cube and the 5 dimensional cube.

Left: 4 Dimensions, Right: 5 Dimensions

So the particles will operate in this multi-dimensional space where each 3-tuple in succession represents one action to take, followed by the next. For instance {x1, y1, z1, x2, y2, z2} would plan ahead two states, while its particles optimized its variables in 6 dimensional space.

I really hope this makes sense. I'm giving a quick presentation to update my status on this project in the coming weeks and will be posting slides with some more details and will go in to more technical details where necessary. Also, I will be posting code as I go. Currently I'm working on some final framework programming which is uninteresting by itself, but something should come shortly where Mario makes forward movement and at least jumps over top gaps and basic enemies.

Sorry for boring you to death, but at least it had some pictures right?

Mario PSO Seminar

2009-10-20T19:55:00.000-07:00

On Wednesday October 20 I will be holding a small seminar on my thesis project. The problem is developing an artificial intelligence controller for a clone of Super Mario Bros. 3 called Infinite Mario Bros. My approach is going to one which had not to my knowledge been explored. My proposed project will implement Mario as a controller using swarm intelligence to guide decision making.

There is a corresponding artificial intelligence competition held in part by divisions of IEEE and CIG. For a little food for thought, take a look at the previous years winner who demonstrated a very robust solution using an A* method.

That utilizes a commonly used graph based engine for movement. Many games use the same method which is tried and true. Consider games like Warcraft, Command & Conquer or Halo Wars. Movement of units need to find a shortest path from A to B in real time, which adapts to dynamical changes in the environment.

Needless to say, swarm intelligence will be very different, but hopefully will appear very "intelligent" to those who watch it attempt a level.

I look forward to putting up some videos of builds of the PSO as the come up, current work is focused on engine and physics calculations, so there isn't much to see. Thus, the time is now to subscribe to this RSS feed.

Happy coding.

Back in the swing

2009-08-20T13:16:00.000-07:00

September is slowly creeping up, the time at work here is coming to end and things can go back to what I consider to be normal. I'm an academic at heart, a professional student bent on world destruction with weapons of math destruction. My final year as an undergraduate approaches and I've finally found a thesis project in my last few weeks.

Beyond all the regular curriculum I'm anticipating a vast majority of my time will be devoted to a year long project for my Honors thesis. The official name of it is "Particle swarm optimization for co-evolution of game playing strategies." Which boils down to two things really. First, I'll be playing games for a year essentially, and secondly, my life is essentially now enclosed in an artificial intelligence box. Pursuing my Master's degree will likely lead down the darken hallways of A.I. even further. This is be my first of hopefully many independent musings in the field.

After I shake off the dust on my brain left from 4 months of work I'm certain I'll get back in the habit of blogging regularly. Lately I've not had the drive with feeling so drained by office monotony, sorry folks. I'm just not cut out for this type of thing.

This summer was time well spent overall. Although I did not accomplish every goal I set out for myself I do not consider any of it wasted. A large part was invested developing www.simplegps.com for @simmsalex which ended up being a very robust store front built on ASP.NET 3.5. Some other little apps were built for various reasons of less that notable worth.

Currently I'm spending much time reading on swarm intelligence and psychology/sociology which seems to be a prerequisite for PSO so I'm attempting to get grounded in it before any for loops get written.

Coming up in September I'll be attending the Ontario Universities Fair (www.ouf.ca) on behalf of Computer Science at @brockuniversity. Hoping to meet some people interested to throw their lives away in pursuit of numbers and if statements like I did, it's nothing but fun.

That's it for now, subscribe to the feed and wait for me to wander the hallways and tell about life from the inside.

Custom SQL Searching for ASP.Net

2009-08-11T05:38:00.001-07:00

SQL Server (Express or Standard+) provides integrated services called full-text search for advanced keyword searches of information. The full-text search is a robust provision by Microsoft to enable a developer to quickly leverage user searches in all facets of SQL whether that be from ASP.NET or any other language which provides means of connecting to SQL. Some of the features they offer with this method are boolean operators, inflection forms, tenses, word proximity and result ranking. While this all sounds great, I’m going to take the rougher path and show you how as I have built a custom searching back-end for my current ASP.NET project.

There are of course advantages to developing everything yourself. There is nothing more rewarding I find then learning each step of the way why and how things happen. Your algorithms can be finely tuned for the task at hand rather than taking the cookie cutter approach and of course your next project will have a far shorter stepping stone to cross when the time comes. So let’s get going with custom SQL searching by taking a look at some likely familar syntax for selecting data similar to a parametrized value.

SELECT * FROM TableName WHERE Name LIKE '%value%'

This is a simple example of using the LIKE keyword to return any rows from TableName where the rows Name column contains the word value. This would include the following if they existed: “value”, “valued”, “invalue”, etc. The % is a wildcard value, it means any 0 or more characters, so because it is before or after, all instances of value are returned.

Now that we have our feet wet, recall for a moment when you perform a _insert popular search engine here_ search your results are ranked. Based off your input the engine displays the results based off some level of assumed relevancy in order to quicker provide you with exactly what you were looking for. The first way we’re going to accomplish this is by ranking our results based off the number of results per row we’ve found. In other words we may find that a search string appears more than once in a row, that row will then be ordered higher than rows which contain less instances of the search string. Ranking is an important feature to strive for because the LIKE keyword is going to returned a completely unordered table.

When dealing with the depths of SQL you may find there are nowhere near as many built in functions and operations like other languages may provide. Luckily, this is where the science happens in computer science. To achieve ranked search results we need to count occurrences, and because we don’t have a built in method for doing so, we have to build our own.

In computational complexity theory, reduction is a method of transforming one problem in to another. In this instance we are going to reduce the problem of counting word instances to the problem of replacing sub-strings in a string. This may seem odd, but hang on with me for a minute.

To accomplish this task, we’re going to build a SQL user defined function (UDF). These are similar to stored procedures but are instead used internally by stored procedures to extent functionality. UDF’s can return data by cannot be used to modify it.

To create a UDF in Visual Studio 2008:

1. Open the Server Explorer
2. Expand your database object
3. Right click on the ‘Functions’ folder.
4. Select ‘Add New’ and then select ‘Inline Function’

This UDF is going to count words the number of occurrences of a smaller string inside a larger string and return that number in the range 0 to n.

In a nut shell, we are going to use a little trick here. We are going to use REPLACE() to replace all instances of the substring with a new substring which is statically one character larger. Once the REPLACE() has done its work, the new larger string will be n characters larger than the original. This is because all occurrances will now be 1 character longer. To find the number of occurrences we then subtract the length of the longer string from the original. That will be the n which we return.

For example:

String: “London is a rainy city, but I don’t mind the rain because I take the train”
Substring: “rain”

The UDF will modify the String to become:

“London is a rainZy city, but I don’t mind the rainZ because I take the trainZ”

Length of original string = 74
Length of modified string = 77

77 – 74 = 3 = Occurrences of substring

Finally for the end of this part, let’s take a look at the code to implement the word count.

CREATE FUNCTION dbo.WordCount

(

@Word varchar(20),

@String varchar(1000)

)

RETURNS SMALLINT

BEGIN

DECLARE @WordPlusZ varchar(21)

SELECT @WordPlusZ = @Word + 'Z'

DECLARE @DoubleString varchar(2000)

SELECT @DoubleString = REPLACE(@String, @Word, @WordPlusZ)

RETURN LEN(@DoubleString) - LEN(@String)

END

Hopefully that is clear given my explanation. Feel free to experiment with this code and shoot holes through it when you realize you can’t use this code for the LIKE keyword. However, in another part of this series we will expand on this UDF to enable functionality. The next article will focus on writing a few stored procedures which will utilize the UDF’s we are writing here and some others.

Happy coding everyone, follow me on Twitter to see when new posts arrive.

ASP.Net Nested Repeater Tutorial

2009-08-11T05:37:00.000-07:00

Displaying collections of data from data sources is a necessity for web applications. There may come a time when you will want to assemble a DataTable object and bind to a repeater which will also contain an inside repeater. The nested relationship would logically be represented in T-SQL by a foreign key relationship on the referenced tables. The syntax in ASP.Net can sometimes get a little hairy when attempting this for the first time. I’m going to walk you through the process and show you how to get this working on your web application.

Step one is simple, you’re going to lay out your templates where your repeaters are going to go. My normal way of doing this is to layout one single instance of the contents of and then surround that with the necessary

Now that you have an idea of how you want things to look, lets build a little code to create the nested data set for the repeater groups. From the code behind file, we need a new method which will occur on Page_Load to bind the required data to the outer repeater.

protected void Page_Load(object sender, EventArgs e)

{

// Bind the data to repeater only on page load

if (!Page.IsPostBack)

{

BindRepeaters();

}

private void BindRepeaters()

{

// Create a new data set

DataSet dataSet = new DataSet();

// Outter repeater data source returns a DataTable

dataSet.Tables.Add(ProductGroupFunctions.GetProductGroups());

// Inner repeater data source also returns a DataTable

dataSet.Tables.Add(ProductFunctions.GetProducts());

// Create foreign key relationship on tables using ProductGroupID

DataColumn colGroups = dataSet.Tables[0].Columns["ProductGroupID"];

DataColumn colProducts = dataSet.Tables[1].Columns["ProductGroupID"];

// Group the tables using the relationship

dataSet.Relations.Add(new DataRelation("products", colGroups, colProducts));

// Bind the outer repeater to our created DataSet object

rptProductGroup.DataSource = dataSet;

// Allow the Page to handle DataBind event

Page.DataBind();

}

So what we’ve done there is take two DataTables and combined them into logical groups. The example I’ve shown here assumes that Products belong to ProductGroups inside your database, which is process of database normalization. Then given these two sources we’re going to allow the outer repeater to show only the products which belong to its current repeated group. This looks something like the following:

ProductGroup A
Product A1
Product A2

ProductGroup B
Product B1
Product B2
Product B3

Now the final step is to fill in the labels, hyperlinks, images etc inside your repeaters. The practice is a little bit more syntactically obscure and I’m sure this is where a lot of headaches are caused.

The outer repeater is the basic one and is not handled any differently than normal repeaters. Here is the complete example from the grouped products example above in HTML code.

">

DataItem).Row.GetChildRows("products") %>">

DataItem, "[\"Picture\"]").ToString() %>">

DataItem, "[\"Name\"]").ToString() %>">

Container.DataItem, "[\"Price\"]").ToString())) %>">

ProductID=" + DataBinder.Eval(Container.DataItem, "[\"ProductID\"]").ToString() %>">

Finally to wrap up, you’ll notice in the outer repeater to fill a Label to show the group we have:

In the DataTable we built there was a corresponding column called “Name” and it’s simple as ever before to use that column.

On the inside repeater we need to perform things differently. First we need to provide the repeater with a DataSource as always. This going to be changing depending on which row of the outer repeater is being processed. This is accomplished inside the

DataSource='' >

Where “products” is in the name of the DataRelation object we inserted in our custom DataSet. Finally on inside repeater we need to specify the column names differently. There could be another column which again titled “Name” which is union joined by the relationship. As an example of an inner control we are going to fill a label control’s text value as follows:

DataItem, "[\"Name\"]").ToString() %>">

Hopefully this gives you a starting block to jump from and attempt this yourself. This can be useful in examples like I’ve demonstrated above, site maps, or any other collection of collections you may have to represent in ASP.Net.

C# List SQL databases and add to list

2009-08-11T05:32:00.000-07:00

You may come across the requirement to query a system running SQL Server for a list of all possible databases. From there you may drill down to find a specific database and need to perform a query on one of the found database’s tables. For both instances the .net class SqlConnection can be used in conjunction with it’s method GetSchema. Let’s take a look how to write a method which will return a list of databases provided a server name and SQL credentials.

public List<String> GetDatabases
(String ServerName, String UserName, String Password)
{
// Instantiate returning data structure of database names
List<String> databases = new List<String>();

// Build connection string with parameters
String connectionString =
"Data Source=" + ServerName +
";UID=" + UserName +
";Password=" + Password + ";";

try
{
// Create SqlConnection object with connection string
using (SqlConnection sqlConnection = new SqlConnection(connectionString))
{
sqlConnection.Open();

// Query SQL server for databases
DataTable dt = sqlConnection.GetSchema("Databases");

// Close connection to SQL
sqlConnection.Close();

// Iterate the datatable and parse names
foreach (DataRow dataRow in dt.Rows)
{
databases.Add(dataRow["database_name"].ToString());
}
}
}

// Handle SQL type errors
catch (SqlException ex)
{ }
// Catch all error handler
catch (Exception ex)
{ }
return databases;
}

Now, it’s likely that that return value will be used to populate a drop down list control to allow the user to select one of the databases the method found. This is a simple process but I will include it for completeness.

// User inputs server name and SQL credentials on form
List<String> databases = GetDatabases(
txtServerName.Text,
txtUserName.Text,
txtPassword.Text);

// Clear drop down list to prevent duplicates
ddlDatabases.Items.Clear();

// Iterate through data structure's strings
foreach (String db in databases)
{
// At new string value to drop down list
ddlDatabases.Items.Add(db);
}

Finally, we’re going to write a method similar to the database query which will return a list of tables in a specific database. This will use as parameter a string value which is the name of a database. It assumes that the connection string is stored after the first method was executed.

public List<String> GetTables(String database)
{
// Instantiate returning data structure of table names
List<String> tables = new List<String>();

try
{
// Create SqlConnection object with connection string
using (SqlConnection sqlConnection = new SqlConnection(ConnectionString))
{
// Build SQL query for tables of a paramaterized database
SqlCommand command = sqlConnection.CreateCommand();

command.CommandText =
"USE " + database +
" SELECT [name] AS Name FROM sysobjects WHERE xtype='U'";

sqlConnection.Open();

// Query SQL for table names and close connection when done
DataTable dt = new DataTable();
dt.Load(command.ExecuteReader(CommandBehavior.CloseConnection));

// Iterate datatable for table names
foreach (DataRow dataRow in dt.Rows)
{
tables.Add(dataRow["Name"].ToString());
}
}
}
// Handle SQL type errors
catch (SqlException ex)
{ }
// Catch all error handling
catch (Exception ex)
{ }
return tables;
}

That combined approach is a solid method for reliably retrieving initial information from a SQL server to drill down and execute a query on a specific table.