Random .Net SQL Generator (Open Source)

A random generator for all types of queries in SQL - extend-able to any SQL provider

I have often ran into the case where I need to insert a bunch of random records against a table or do a lot of queries to test for integrity - but those tools always seem to cost money. Some classmates and I did a project last semester that allowed for random SQL generation of all types. This is a generation tool for random queries in SQL in the Windows environment. It can produce random queries, tables, deletes, and updates as well as generate random data for a table. This was implemented with PostgresSQL, but we tried to structure it to allow easy adaptation for other SQL implementations as well.

The interface was meant to be very simple and requires no information about the tables themselves, just feed in basic information like the connectionstring and the table name to run the query against:

insert 100 random records

PgSqlGenerator pg = new PgSqlGenerator("127.0.0.1", 5432, 
    "postgres", "sql", "my_database");
Table people = pg.GetTable("people");
QueryInformation report = 
    pg.ExecuteBulkInsert(people, 100);

//now check the results:
Console.WriteLine("Query: " + report.Query);
Console.WriteLine("Time Taken: " + report.Time);
Console.WriteLine("Rows Affected: " + report.Affected);

It is far from bug-free, but works very well - figured we would share what we did.



Creating Nice '|' Divided Menus with CSS

it is common for websites (this one included) to have the ubiquitous '|' as a link divider, here is a neat way to do it with pure css

To get this menu:

You dont need to actually put in the '|' character, you can do it with just the following advanced css selectors:
<style type="text/css">
	ul#nav li{
		float:left; 
		margin-right:20px;
	}
	
	ul#nav li:after{
		content:'|';
		margin-left:20px;
	}
	
	ul#nav > li:last-child:after{
		content:'';	
		margin-left:0;
	}
</style>

<ul id="nav">
    <li><a href="#">Home</a></li>
    <li><a href="#">Contact</a></li>
    <li><a href="#">FAQ</a></li>
    <li><a href="#">Other Crap</a></li>
</ul>

I realize it's not exactly space-saving, but it is a cool trick that could be adapted to all sorts of possible situations.

Generate random values in C#

all sorts of random values including date, times, numbers, binary, bit, etc.

Nothing new here, but it's nice to have it all in one spot.

generate random values

public int Integer(int max = Int16.MaxValue)
{
    max = max > Int16.MaxValue ? Int16.MaxValue : max;
    return new Random().Next(-max, max);
}

public string Binary(int length = 8)
{            
    int decNum = new Random().Next(1000, int.MaxValue);
    return Convert.ToString(decNum, 2)
        .Substring(0, length);
}

public DateTime Date(int start_year = 1995)
{
    DateTime start = new DateTime(start_year, 1, 1);
    int range = ((TimeSpan)(DateTime.Today - start))
        .Days;
    start.AddDays(new Random().Next(range))
        .AddSeconds(new Random().Next(86400));
}

public TimeSpan Time()
{
    return Date().TimeOfDay;
}

public int Bit()
{
    return new Random().Next() % 2 == 0 ? 1 : 0;
}

public bool Bool()
{
    return new Random().Next() % 2 == 0 ? true : false;
}

public string String(int limit = 255)
{
    // added spaces so there will be 
    // a higher chance of spacing words
    string legal = "        abcdefghijklmnopqrstuvwxyz" +
        "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789      ";
    StringBuilder s = new StringBuilder();
    //Random length for the string
    limit = new Random().Next(1, limit); 
    for (int i = 0; i < limit; i++)
        s.Append(legal[new Random().Next(legal.Length)]);
    return s.ToString();
}

Learning Ruby on Rails for a .Net MVC C# Coder

quick reference for equivalent methods/approaches

I am learning RoR for a new project that requires the ability to truly run cross-platform (don't even say Mono...). That said, I love .net, but Ruby on Rails is a fantastic MVC framework. I am learning a lot about MVC programming in general and here is my contribution to others trying to learn RoR in my situation.

And yes, I know there are many more syntax options than the ones I show, but this is just a quick reference -if you want them all, check out the API.

Everything presented here will render the same html in the respective languages, as well as behave the same as data access (ORM):

stylesheet/javascript links

Asp.Net MVC
<link rel="stylesheet" type="text/css" 
 href="<%=Url.Content("~/public/stylesheets/style.css") %>"
/>
<script type="text/javascript" 
 src="<%=Url.Content("~/public/javascripts/jquery.js") %>"
></ script>

RoR
<%= stylesheet_link_tag 'style' %>
<%= javascript_include_tag 'jquery' %>


hyperlinks

Asp.Net MVC
<%= Html.ActionLink("View Widgets", "Index", "Widget") %>
<%= Html.ActionLink("Widget 5", "Show", "Widget",
  new {id = 5}) %>

RoR
<%= link_to 'View Widgets', widgets_path %>
<%= link_to 'Widget 5', widget_path(5) %>


forms

Asp.Net MVC
<% using(Html.BeginForm()) { %>
 <%= Html.LabelFor(x => x.Name) %>
 <%= Html.TextBoxFor(x => x.Name) %>
 <input type="submit" value="submit" />
<% } >

RoR
<% form_for(@widget) do |f| %>
 <%= f.label :name %>
 <%= f.text_field :name %>
 <%= f.submit 'submit' %>
<% end %>


rendering partials

Asp.Net MVC
<%= Html.Partial("form") %>

RoR
<%= render :partial => 'form' %>


looping

Asp.Net MVC
<% foreach(var widget in Model.Widgets) { %>
  <%= widget.Name %>
<% } %>

RoR
<% for widget in @widgets %>
  <%= widget.name %>
<% end %>


validation

Asp.Net MVC
[MetadataType(typeof(WidgetValidation))]
public partial class Widget { }

public class WidgetValidation
{
  [Required(ErrorMessage = "Name is Required")]
  [RegularExpression("[\\S]{5,}", ErrorMessage = "Min 5 chars")]
  [StringLength(30, ErrorMessage = "Max 30 chars")]
  public string Name { get; set; }
}

RoR
class Widget < ActiveRecord::Base
  validates_presence_of :name
  validates_length_of :name, :in => 5..30
end


orm data access

Asp.Net MVC (Linq-to-Sql)
var db = new MyDataContext();
var widgets = db.Widgets;
var widget = db.Widgets.SingleOrDefault(x => x.Id == 5);
var components = widgets.Components.OrderBy(x => x.name);

RoR (ActiveRecord)
@widgets = Widget.all
@widget = Widget.find(5)
@components = @widget.components.sort_by{|w| w.name}


saving a model

Asp.Net MVC (Linq-to-Sql)
public ActionResult Create(Widget widget)
{
  db.InsertOnSubmit(widget);
  db.SubmitChanges();
}

RoR (ActiveRecord)
def create
  @widget = Widget.new(params[:widget])
  @widget.save
end


helper methods

Asp.Net MVC
// define it:
public static string DivThis(this HtmlHelper html, string text)
{
  return "<div>" + text + "</div>";
}

<!-- call it: -->
<= Html.DivThis("hello") %>

RoR
# define it
def div_this(text)
  '<div>' + text + '</div>'
end

<!-- call it: -->
<%= div_this('hello') %>



temporary data

Asp.Net MVC
// set it
TempData["someKey"] = "hello"

<!-- use it -->
<%= TempData["someKey"].ToString() %>

RoR
# set it
flash[:some_key] = 'hello'

<!-- use it -->
<%= flash[:some_key] %>


That's all I have for now, hopefully I will be adding more in the near future. I think I like RoR...

ServerInfo - Easily scan a Machine/Server Farm for Information

new open-source project in Asp.Net MVC 2

More than once I have been asked what databases are on what server, if server X is running application Y or what websites server ABC is running. These are are relatively simple things to accomplish, and there are tools available to get this information, but this is an extremely simple and portable solution - all the data is kept in xml, so there is no need to install a backend.

All that it is required to get all this information is to enter ip addresses and the user has the rights to scan the machines requested.

In addition, this can keep track of all of the owners of the machines and has a GUI for running WMI Queries, which is extremely powerful if you know how to use it.

This is written with Asp.Net MVC 2, C# and xml; it requires .Net 4.0 framework. I will be updating this to MVC 3/Razor in the near future.

Inline AJAX DropDown and Text Editing with Asp.Net MVC and jQuery

including how to use a database to populate the dropdown

First thing is first, you will need to download jQuery and the Jeditable plugin (I prefer to refer to it as the Jedi-Table!). Be sure to put these references in your View (or Masterpage). Next, you have to set up a view on which to use an inline edit. I find that I often want to use this approach on tables of information. For this View, I will set it to use an IEnumerable of an Item I have called 'ItemOwner' (this is arbitrary and does not really matter). It will be a simple table that lists the Name and the Country of the owner, both of which will be editable inline. Here is the Index in my ExampleController.cs:
myDataContext db = new myDataContext();
public ActionResult Index()
{
    // get the info for the 'Countries' dropdown:
    ViewData["countries"] = db.Countries
        .Select(x => new SelectListItem() 
        { 
            Text = x.Name, 
            Value = x.Id.ToString() 
        }).ToJson();

    // get the 'ItemOwners' I am interested in:
    var owners = db.ItemOwners.Take(3);

    return View(owners);
}

As you can see there, I am also pulling the countries from the database and throwing them into the ViewState - we will get to this later. Since the Country is actually a foreign key relation, the value is set to an integer which is the identity field in the database. It is also using a .ToJson() extension which takes a IEnumerable<SelectListItem> and puts it into a simple JSON string that I use which is here:
public static string 
    ToJson(this IEnumerable<SelectListItem> slis)
{
    string output = "{";
    if (slis != null)
    {
        for (int i = 0; i < slis.Count(); i++)
        {    
            output += " '" + slis.Skip(i)
            .First().Value + "': '" + 
            slis.Skip(i).First().Text + "'" + 
            (i == slis.Count() - 1 ? " " : ",");
        }
    }
    return output += "}";
}

There is probably a better way to do that... but I don't know it?!

I am also pulling 3 ItemOwners from the database, I know this is silly, but it just an example. Here is how I am displaying them in the view:
<table>
    <thead>
        <tr>
            <th>Name</th>
            <th>Country</th>
        </tr>
    </thead>
    <tbody>
        <% foreach(var owner in Model) { %>
        <tr>
            <td><%= owner.Name %></td>
            <td><%= owner.Country.Abbreviation %></td>
        </tr>
        <% } %>
    </tbody>
</table>

Now that there is a simple table we want to make it a bit more interactive. Since we aregoing to make all of these fields editable, we need to add in a way to distinguish exactly what they are. To do that, we will need two things: the id of the item they are editing, and the type of inline editing we will be doing (i.e. dropdown or text input). So to do that, let's add in a few css classes and an identifieng ID:
<td id="name<%= owner.Id %>" class="editable text">
    <%= owner.Name %></td>
<td id="ctry<%= owner.Id %>" class="editable dropdown">
    <%= owner.Country.Abbreviation %></td>

And now add a little css to make them appear to be clickable:
td.editable:hover 
{ cursor:pointer; background-color:Orange; }

Now they all look like you can click on them, so we can move on to making the click actually do something.

This is where the jQuery comes in, and it is very simple. I have made these 'helper' methods in Javascript to make all of my inline calls centrally controllable, I keep this in my sites script folder so if I change one inline edit, I change them all; it also makes for more readable Javascript on each page.
function InlineDropdown(collectionToDropDown, ajaxAddress, dropDownDataSet) {
    collectionToDropDown.editable(ajaxAddress,
    {
        data: dropDownDataSet,
        type: 'select',
        indicator: 'saving...',
        tooltip: 'click to edit...',
        submit: 'Save',
        style: 'inherit',
        placeholder: 'click to edit'
    });
}

function InlineTextbox(collectionToInline, ajaxAddress) {
    collectionToInline.editable(ajaxAddress, 
    {
        indicator: 'saving...',
        tooltip: 'click to edit...',
        style: 'inherit',
        placeholder: 'click to edit'
    });
}

function InlineTextarea(collectionToInline, ajaxAddress) {
    collectionToInline.editable(ajaxAddress, 
    {
        type        : 'textarea',
        rows        : 4,
        indicator   : 'saving...',
        tooltip     : 'click to edit...',
        style       : 'inherit',
        submit      : 'Save',
        onblur      : 'ignore',
        placeholder : 'click to edit'
    });
}

Obviously you can read all about the options on the Jeditable page, but this is how I set them. Also notice I have a InineTextarea included as well for a textarea which is not covered here but works the exact same.

Now the jQuery calls are almost trivial:
InlineTextbox(
    $('td.editable.text'), 
    "<%= Url.Content("~/Ajax/ItemOwner.ashx") %>"
);

InlineDropdown(
    $('td.editable.dropdown'), 
    "<%= Url.Content("~/Ajax/ItemOwner.ashx") %>", 
    <%= ViewData["countries"].ToString() %>
);

What that is doing is sending the POST requests to the specified address. The POST contains a few things:
  • id - the id of the element that sent the request
  • value - the new value passed by the element
We are also passing more information there - remember that we passed both the type of field to edit and the id of the ItemOwner to edit, ie [name837] which emans we want to edit the Name field of ItemOwner 837. So we simply set up an ashx handler (which we specified above) to do the dirty work:
public void ProcessRequest(HttpContext context)
{
    string newValue;
    try
    {
        myDataContext db = new myDataContext();
        string elementId = context.Request.Form["id"];

        // since we made the first 4 of the id the 'field' whic to edit
        // we can just pull the first 4 letters for use in our switch:
        string fieldToEdit = elementId.Substring(0, 4);

        //now take anything after those 4 and it is the Id:
        int idToEdit = Convert.ToInt32(elementId.Remove(0, 4));

        // the value is simply a string:
        newValue = context.Request.Form["value"].Trim();

        // now that we have the id, get the ItemOwner from the db
        ItemOwner owner = db.ItemOwners.FirstOrDefault(x => x.Id == idToEdit);

        // after all is said and done, we will return newValue to the user so the field
        // looks as if the change has taken place (which it has)

        // using the field we pulled above, decide what to do:
        switch (fieldToEdit)
        {
            // name is easy
            case "name": owner.Name = newValue; break;

            // since the country is an integer foreign key, we need to Convert.ToInt32:
            case "ctry":
                owner.CountryId = Convert.ToInt32(newValue);
                // now that we have recorded the value, we want to return the text to
                // the user and not the id value which would make no sense
                newValue = db.Countries.FirstOrDefault(x => x.Id == owner.CountryId).Abbreviation;
                break;
            // if it wasn't caught, something is wrong:
            default: throw new Exception("invalid fieldToEdit passed");
        }

        db.SubmitChanges(); // save it
    }
    // now if an exceptions were reported, the user can see what happened
    // this also inform the user nothing was saved
    // you could easily make this not reported to the user and logged elsewhere
    catch (Exception ex) 
    { newValue = "Error: " + ex.Message + " [nothing written to db]"; }

    //now return what you want in the element:
    context.Response.Write(newValue);       
}

And that is all it takes.

Edit an Object Property Value Dynamically at Run Time

no need to write a huge switch statement to make a run time decision

Imagine you have an Object with 50 properties, and at runtime, you only need to change the value of one of them. You could write a switch statement to run through all of them, but there is a better way. This is how it would be done with a switch:
switch(propertyName)
{
    case "Name": obj.Name = newVal; break;
    case "Phone": obj.Phone = newVal; break;
    ///and so on...

That sounds like a terrible idea. With Reflection, it was easy to build an extension to update any object with a new value at runtime (provided it can be written to) with the following code:
public static void SetPropertyValue(this object o, 
    string propertyName, object newValue)
{
    PropertyInfo pi;
    pi = o.GetType().GetProperty(propertyName);
    if (pi == null)
        throw new Exception("No Property [" + 
            propertyName + "] in Object [" + 
            o.GetType().ToString() + "]");
    if (!pi.CanWrite)
        throw new Exception("Property [" + 
            propertyName + "] in Object [" + 
            o.GetType().ToString() + 
            "] does not allow writes");
    pi.SetValue(o, newValue, null);
}

Now, instead of that 50+ line mess above, all you need to do to change the "Phone" property to 'newVal' is:
obj.SetPropertyValue("Phone", newVal);

I added this to my Naspinski.Utilities Set on CodePlex as well.

A relationship is being added or deleted from an AssociationSet ...

this error may be cause by a Foreign Key changed that gets missed by Linq-to-Entities

My old Linq-to-Entities project came back to haunt me. I had to make some changes to the DB to allow a couple things, and with that, I changed some INT NOT NULL REFERENCES change to INT REFERENCES - the obvious difference being that they now allowed null values. No big deal really, I went into my .edmx file and did 'Update Model from Database' and everything seemed to be working fine, until I tried a certain operation that kicked out this doozy:

A relationship is being added or deleted from an AssociationSet 'FK__Gizmo__CategoryI__0425A276'. With cardinality constraints, a corresponding 'Gizmo' must also be added or deleted.


Now those arent the actual values, but you get the idea. This is telling me that my Foreign Key relationship is being violated, which confused me. I had changed that FK to be nullable, so this should not be happenening. I then tried the same operation in SQL Server Management Studio, just to be sure it was legal on the SQL side, and it worked fine. So I figured, like so many times before, the problem lies with Linq-to-Entities. I opened my .edmx and saw something like this:
As you can see, it clearly shows a one-to-many relationship from Category->Gizmo. This was no longer the case, but L2E failed to pick up on it.

The bottom line is that 'Update Model from Database' did not catch the Foreign Key change


Once I figured this out, it is a simple to fix. Simply right-click on the relationship (in the box above) and click 'Properties'; that will bring up the Properties dialogue. Once this is open, change the End of the referenced table from '1 (One)' to '0...1 (Zero or One)' and save your .edmx.



This should not be necessary as you would assume 'Update Model from Database' would catch things like this, but like so many other things in L2E, it just doesn't work like you want it to. I can't wait for .Net 4.0, supposedly most of the problems with L2E are getting fixed; we'll just have to wait and see.