tag:blogger.com,1999:blog-27056778104527187522024-03-21T22:34:20.026+00:00Pure Dot Net CoderAll things related to the .net frameworkRippohttp://www.blogger.com/profile/10427021389808024817noreply@blogger.comBlogger49125tag:blogger.com,1999:blog-2705677810452718752.post-87005234549750013062016-01-19T10:33:00.000+00:002016-01-19T10:37:05.530+00:00Using Kendo TemplatesFirst our template<br />
<pre class="csharp" name="code"><script id="data_upload_result_confirmation" type="text/x-kendo-template">
<h4>#= firstname # is this information correct?</h4>
<p style="text-align:center;">
<button class="delete-confirm k-button">Ok</button>
<a href="javascript:" class="delete-cancel">no</a>
</p>
</script>
</pre><br />
Then the code to transform the data then open the window<br />
<pre class="csharp" name="code"><script>
var kendoWindow = $("<div />").kendoWindow({
title: "Confirm",
resizable: false,
width: 550,
modal: true
});
var template = kendo.template(
$('#data_upload_result_confirmation').html());
var data = { firstname: "Rippo" };
var result = template(data);
kendoWindow.data("kendoWindow")
.content(result)
.center().open();
</script>
</pre>Rippohttp://www.blogger.com/profile/10427021389808024817noreply@blogger.com3tag:blogger.com,1999:blog-2705677810452718752.post-81055366950558093202016-01-06T10:13:00.001+00:002016-01-11T13:06:45.082+00:00Allowing external urls into your DEV environmentNote to self:<br />
<br />
When setting up a new machine (or site) in Visual Studio and you want IIS to server external URL's then open a command prompt with admin privalges and type:-<br />
<br />
<br />
To delete if it exists<br />
<pre>netsh http delete urlacl url=http://dev.wildesoft.net:44300/
</pre><br />
To add (notice uppercase E in Everyone)<br />
<br />
<pre>netsh http add urlacl url=http://dev.wildesoft.net:44300/ user=Everyone
</pre><br />
You may need to add the entry in .vs/config/applicationhoist.config<br />
<br />
You will also need to open up your routers firewall and also the windows firewall<br />
<br />
NOTE: to show list of all allowed URLS<br />
<pre>netsh http show urlacl
</pre><br />
<br />
Rippohttp://www.blogger.com/profile/10427021389808024817noreply@blogger.com0tag:blogger.com,1999:blog-2705677810452718752.post-91827274149612785252014-05-13T14:04:00.001+01:002014-05-13T15:40:36.647+01:00CasperJS Kendo UI TabstripWhilst playing with CasperJS I needed a way to mimic pressing a mouse click on a Kendo tabstrip.<br />
<br />
I messed around with all kind of selectors but could not get it to work. Fortunately evaluate came to my rescue.<br />
<br />
<pre class='csharp' name='code'><div id="tabstrip">
<ul>
<li>Lm3 Result</li>
<li>Results by Location</li>
<li>Payroll Analysis</li>
<li>Supplier Analysis</li>
<li>Supplier Breakdown</li>
<li>Supplier Locations</li>
</ul>
</div>
</pre></br><br />
<br />
<pre class='csharp' name='code'>casper.thenOpen("http://localhost:62726/results", function () {
test.assertTextExists(
"Analyse different elements", "showing analyse different elements");
//only way I can see to change a tab strip
casper.evaluate(function () {
var tabStrip = $("#tabstrip")
.kendoTabStrip()
.data("kendoTabStrip");
tabStrip.select(4);
});
});
</pre><br />
<br />
This utilises JQuery on the page, finds the kendo tabstrip and then selects the 4th tab item.<br />
Rippohttp://www.blogger.com/profile/10427021389808024817noreply@blogger.com0tag:blogger.com,1999:blog-2705677810452718752.post-48107863425901503502013-09-11T10:28:00.000+01:002013-09-14T13:40:10.736+01:00ConvertEmptyStringToNull in MVCWhen using MVC and model binding from a form post there comes a time when your database doesn't allow nulls but you find that any empty string from a form post comes back as null. This might be a PITA if you are directly saving the posted form back to the database and don't want to manually convert string null's to empty strings.<br />
<br />
The default behaviour of the DefaultModelBinder, is that ConvertEmptyStringToNull is by default, set to true. <br />
<br />
To get around this you can add an attribute to your property that guarantees that the binder will not convert the property to an empty string rather than null.<br />
<br />
<pre class='csharp' name='code'>[DisplayFormat(ConvertEmptyStringToNull = false)]
public virtual string Language { get; set; }
</pre><br />
Another way is to do it at a global level is to create your own model binder<br />
<br />
<pre class='csharp' name='code'>public class EmptyStringModelBaseBinder : DefaultModelBinder
{
public override object BindModel(ControllerContext controllerContext, ModelBindingContext bindingContext)
{
bindingContext.ModelMetadata.ConvertEmptyStringToNull = false;
return base.BindModel(controllerContext, bindingContext);
}
}
</pre>and add your binder in global.asax<br />
<pre class='csharp' name='code'>ModelBinders.Binders.DefaultBinder = new EmptyStringModelBaseBinder();
</pre><br />
Rippohttp://www.blogger.com/profile/10427021389808024817noreply@blogger.com3tag:blogger.com,1999:blog-2705677810452718752.post-49246147974929725922013-08-22T11:49:00.000+01:002013-08-22T11:50:21.929+01:00Set the default language for CKEditor and SCAYTIn order to set the DEFAULT language and spell checker in CKEditor when using SCATY (Spell check as you type) you need to add the following in your config values<br />
<br />
<br />
<pre class="csharp" name='code'>CKEDITOR.editorConfig = function (config) {
config.language = 'en-gb';
config.wsc_lang = "en_GB";
config.scayt_sLang = 'en_GB';
config.scayt_autoStartup = true;
...
};
</pre><br />
Note:-<br />
<br />
- the language is en DASH gb (all lower)<br />
- the wsc_lang and scayt_sLang is en UNDERSCORE upper GB<br />
<br />
Rippohttp://www.blogger.com/profile/10427021389808024817noreply@blogger.com1tag:blogger.com,1999:blog-2705677810452718752.post-26918267996155180772013-08-19T19:19:00.002+01:002013-08-19T19:19:34.380+01:00Git and mark as assume-unchangedI'm working on a small side-project that is on a public GIT repository and there are a few settings that should not be seen or used by other people. <br />
<br />
Some settings that I do not want to show are:-<br />
<br />
<ul><li>Connection strings</li>
<li>Meetup API keys</li>
</ul><br />
One great way to get around this is two first create the app.config and blank out the values you don't want. Then commit and push to your remote repository. After which key in the following command:-<br />
<br />
<pre>git update-index --assume-unchanged [fileName]
</pre><br />
Git will then stop monitoring changes to that file allowing you to put the real config info into it without fear of checking it in. If you later make changes that you DO want to check in you can run:<br />
<br />
<pre>git update-index --no-assume-unchanged [fileName]
</pre><br />
Lovely!<br />
Rippohttp://www.blogger.com/profile/10427021389808024817noreply@blogger.com0tag:blogger.com,1999:blog-2705677810452718752.post-54499316760838259582013-06-27T09:00:00.003+01:002013-06-27T09:01:05.456+01:00Generating a list of which files changed between hg versionsThis is more of a note to myself so I can refer to it later!<br />
<br />
Every time I want to see a list of changes between two Mecurial revisions I can never remember the correct syntax..<br />
<br />
<blockquote>hg status --rev x:y</blockquote><br />
Where x and y are desired revision numbers...Rippohttp://www.blogger.com/profile/10427021389808024817noreply@blogger.com0tag:blogger.com,1999:blog-2705677810452718752.post-41159960784519240312012-05-16T08:06:00.000+01:002012-05-16T08:07:15.272+01:00Using DateAdd with NHibernate LinqThe goal is to do something like this using NHibernate and Linq:-<br />
<pre class='csharp' name='code'>Session.Query<Item>.Where(x => x.Start.AddHours(3) > x.Finish );</pre>If you run this code you will get the following error message:-<br />
<br />
<em>[NotSupportedException: System.DateTime AddHours(Double)]</em><br />
<br />
<h2>So is there a solution?</h2>The answer is yes, NHibernate is very extensible. The solution below is based on NHibernate 3.3 and the Loquacious configuration.<br />
<br />
First you need to create a custom dialect and tell NHibernate about the MsSql `dateadd` function:-<br />
<pre class='csharp' name='code'>public class CustomDialect : MsSql2008Dialect
{
public CustomDialect()
{
RegisterFunction(
"AddHours",
new SQLFunctionTemplate(
NHibernateUtil.DateTime,
"dateadd(hh,?2,?1)"
)
);
}
}
</pre>Now you need to add the following two classes, the first one extends the <em>DefaultLinqToHqlGeneratorsRegistry</em> and then implement our <em>AddHoursGenerator</em> method.<br />
<pre class='csharp' name='code'>public class MyLinqtoHqlGeneratorsRegistry :
DefaultLinqToHqlGeneratorsRegistry
{
public MyLinqtoHqlGeneratorsRegistry()
{
this.Merge(new AddHoursGenerator());
}
}
public class AddHoursGenerator : BaseHqlGeneratorForMethod
{
public AddHoursGenerator()
{
SupportedMethods = new[] {
ReflectionHelper.GetMethodDefinition<DateTime?>(d =>
d.Value.AddHours((double)0))
};
}
public override HqlTreeNode BuildHql(MethodInfo method,
System.Linq.Expressions.Expression targetObject,
ReadOnlyCollection<System.Linq.Expressions.Expression> arguments,
HqlTreeBuilder treeBuilder, IHqlExpressionVisitor visitor)
{
return treeBuilder.MethodCall("AddHours",
visitor.Visit(targetObject).AsExpression(),
visitor.Visit(arguments[0]).AsExpression()
);
}
}
</pre>Now all you need to do is to add the <em>CustomDialect</em> to your configuration and tell NHibernate about your custom <em>AddHours</em> generator<br />
<pre class='csharp' name='code'>var configure = new Configuration()
.DataBaseIntegration(x => {
x.Dialect<CustomDialect>();
x.ConnectionStringName = "db";
})
.LinqToHqlGeneratorsRegistry<MyLinqtoHqlGeneratorsRegistry()
.CurrentSessionContext<WebSessionContext>();
</pre>Note: we add <em>.LinqToHqlGeneratorsRegistry<MyLinqtoHqlGeneratorsRegistry()</em><br />
<br />
I have based this on this <a href="http://fabiomaulo.blogspot.co.uk/2010/07/nhibernate-linq-provider-extension.html">blog post</a> by fabio.<br />
<br />
You can now use your code as is:-<br />
<pre class='csharp' name='code'>Session.Query<Item>.Where(x => x.Start.AddHours(3) > x.Finish );</pre>This is also possible in 3.2 but the public override HqlTreeNode BuildHql(..) parameters are slightly different...Rippohttp://www.blogger.com/profile/10427021389808024817noreply@blogger.com3tag:blogger.com,1999:blog-2705677810452718752.post-34174394801404003982012-05-08T11:31:00.000+01:002012-05-08T11:42:27.452+01:00NHibernate 3.3 and medium trustWith the new release of NHibernate 3.3 it seems from my early testing, that the Medium Trust issues have now disappeared and its good to see NHibernate 3.3 works in Medium Trust environments. I need to play with the NH Linq provider more to see what fails but from what I have seen so far I have to congratulate the NH development Team.<br />
<br />
As a side note, if anyone is wondering how I test this then please <a href="http://www.rippo.co.uk/policy.zip">download</a> the modified medium trust file (called policy.config from Rackspace cloud) unzip it and save it to your web root folder.<br />
<br />
Next simply add this to your web.config:-<br />
<pre name='code' class='xml'><system.web>
<securitypolicy>
<trustlevel name="Custom" policyFile="policy.config" />
</securityPolicy>
<trust level="Custom" originUrl="" />
</system.web>
</pre>You now have a local development system running under medium trust. For those that do not run Rackspace cloud I would recommend asking your hosting provider for their medium trust policy file.<br />
<br />
<blockquote>This is an update from a <a href="http://puredotnetcoder.blogspot.co.uk/2011/09/update-nhibernate-32-and-medium-trust.html">previous post</a> when using NHibernate 3.2 (September 2011). The JIRA issue can be found <a href="https://nhibernate.jira.com/browse/NH-2857">here</a>. <br />
</blockquote>Rippohttp://www.blogger.com/profile/10427021389808024817noreply@blogger.com2tag:blogger.com,1999:blog-2705677810452718752.post-65901644232451629542012-04-27T08:31:00.000+01:002012-04-28T07:38:47.101+01:00Using filters for unmapped columns in NHibernateUsing filters in NHibernate is a little known feature to new users. Filters can be applied in quite few places. <br />
<br />
This blog shows us how we can apply a filter onto the class definition:-<br />
<pre class='xml' name='code'><class name="Domain.Model.BlogPost, Domain.Model" table="Posts"
where="(IsLive=1)">
...
</class>
</pre>However be warned this will always return blog posts that are live. Sometimes you may want to get blog posts that are not live. If you use a filter this way then you will never be able to return <em>isLive=0</em> or ALL blogs regardless of the flag.<br />
<br />
Fortunately NHibernate allows us to switch filters on/off at a flick of a switch, add this to your mappings file:-<br />
<pre class='xml' name='code'><filter-def name="BlogFlag">
<filter-param name=":isLive" type="System.Int"/>
</filter-def>
<class name="Domain.Model.BlogPost, Domain.Model" table="Posts">
<filter name="BlogFlag" condition="(isLive=:flag)"/>
...
</class>
</pre>Using the flag could not be simpler, just set the flag before you query:-<br />
<pre class='csharp' name='code'>//return live blogs
session.EnableFilter("BlogFlag").SetParameter("isLive", 1);
session.QueryOver<MyEntity>();
//return blogs that are not live
session.EnableFilter("BlogFlag").SetParameter("isLive", 0);
session.QueryOver<MyEntity>();
//return all blogs
session.QueryOver&t;MyEntity>();
</pre>Now one question you are asking is why would I use filters? Why wouldn't I just use <br />
<pre class='csharp' name='code'>//Use a where clause! Isn't this more sensible?
session.QueryOver<MyEntity>()
.Where(w => w.IsLive == isLive);
</pre>However one of the unknown undocumented features of filters is that the database column <em>IsLive</em>, <em>DOES</em> not need to mapped to a fully mapped property. <br />
<br />
The second reason is that if I <em>ALWAYS</em> want just live <em>Blog posts</em> therefore adding <em>where="(IsLive=1)"></em> to the class definition makes sense as the developer will does not need to remember to add the where clause for <em>EVERY</em> query.<br />
<br />
In the next blog post I will show you how to use filters on collections.Rippohttp://www.blogger.com/profile/10427021389808024817noreply@blogger.com0tag:blogger.com,1999:blog-2705677810452718752.post-81784433156000477362012-04-20T13:06:00.001+01:002012-05-16T08:06:57.770+01:00Use a button instead of an anchor tagWhen using JQuery and href's we sometimes see the following:-<br />
<pre class="xml" name="code"><a id='MyLink' href="#">My Link</a></pre>or even worse:-<br />
<pre class="xml" name="code"><a id='ClickMe' href="javascript:void(0);">Click Me</a>
</pre>and some jQuery which executes some code, <em>BUT</em> does not redirect the user.<br />
<pre class="xml" name="code"><script>
$('#ClickMe').click(function(e) {
e.preventDefault();
alert('Me clicked');
});
</script>
</pre>Now this works if:-<br />
<ol><li>Javascript is enabled</li>
<li>If JS is disabled then the link should actually should have a fall back, however in this case we don't want to redirect anywhere</li>
<li>Not all browsers support e.preventDefault() so to circumvent this we add a return false at the end of the function</li>
<li>If the page is long; that is longer than the height of the screen then in some browsers the page will jump to the top, losing the Y position</li>
</ol><pre class="xml" name="code"><script>
$('#MyLink').click(function(e) {
e.preventDefault();
alert('Link clicked');
return false; //Just in case!
});
</script>
</pre>If you think about it these "anchors" exist solely to provide a click event, but do not actually link to other content.<br />
<br />
<h2>So is there a better solution?</h2>One very nice approach is to convert the <em>anchor tag</em> to a <em>button element</em>.<br />
<br />
It can be styled like so:<br />
<pre class="xml" name="code"><button id='Click Me' style="border:none; background:transparent; cursor: pointer;">Click me</button>
</pre>the javascript can then be changed to:-<br />
<pre class="xml" name="code"><script>
$('#ClickMe').click(function(e) {
alert('Me clicked');
});
</script>
</pre>And of course click events can be attached to buttons without worry of the browser jumping to the top, and without adding extraneous javascript such as <em>onclick="return false;"</em> or <em>event.preventDefault()</em> or even <em>return false</em>.Rippohttp://www.blogger.com/profile/10427021389808024817noreply@blogger.com2tag:blogger.com,1999:blog-2705677810452718752.post-78281610970730157742012-04-17T07:54:00.000+01:002012-04-17T07:54:21.589+01:00Using QueryOverProjectionBuilder with QueryOverWe all strive to keep our code <a target='_blank' href="http://en.wikipedia.org/wiki/Don't_repeat_yourself">DRY</a> and sometimes we may have several <em>QueryOver</em> methods that returns the same DTO. This may then lead us to try and create a method that returns a <em>SelectList</em> to reuse in our queries. This gives us the benefit of only changing one method if for example our DTO changes. <br />
<br />
First lets look at the QueryOver method. As you can see I have a method call named <em>GetDtoList()</em><br />
<pre class='csharp' name='code'>return Session.QueryOver<InvoiceDto>()
.SelectList(GetDtoList())
.TransformUsing(Transformers.AliasToBean<InvoiceDto>())
.List<InvoiceDto>();
</pre>Now for the <em>GetList</em> method. As you can see we return a <em>Func</em> of <em>QueryOverProjectionBuilder</em>. In the code we simply build the list as we normally would and just return it:-<br />
<pre class='csharp' name='code'>Func<QueryOverProjectionBuilder<InvoiceDto>,
QueryOverProjectionBuilder<InvoiceDto>> GetDtoList() {
InvoiceDto dto = null;
return list => list
.Select(w => w.ClientName).WithAlias(() => dto.ClientName)
.Select(w => w.InvoiceDate).WithAlias(() => dto.InvoiceDate)
.Select(w => w.InvoiceId).WithAlias(() => dto.InvoiceId);
}
</pre>Pretty nice I think although the use case for this may be a little limited. The next challenge is to use this when we join to another entity i.e.<br />
<pre class='csharp' name='code'>return Session.QueryOver<Invoice>()
.JoinQueryOver<Client>()
.SelectList(GetDtoList())
.TransformUsing(Transformers.AliasToBean<InvoiceDto>())
.List<InvoiceDto>();
</pre><br />
<br />Rippohttp://www.blogger.com/profile/10427021389808024817noreply@blogger.com0tag:blogger.com,1999:blog-2705677810452718752.post-72710515011217921412012-04-10T07:58:00.000+01:002012-05-14T07:09:02.384+01:00Using stored procedures with NHibernateFor some reason newbies to NHibernate struggle to find answers on Google or S.O when trying to use Stored Procedures with NHibernate. I have posted this here as a guide to help.<br />
<br />
First define in a mapping file that is marked as embedded resource, call it StoredProcs.hbm.xml (the name doesn't matter but the extension .hbm.xml does) :-<br />
<pre class='xml' name='code'><?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
<sql-query name="SummaryReport">
exec getSummaryReport :productId
</sql-query>
</hibernate-mapping>
</pre>The stored procedure returns the following SQL columns:-<br />
<pre class='csharp' name='code'>int ProductId
nvarchar(75) ProductName
decimal(18,2) SalesTotal
bit IsActive
</pre>First we define our c# class:-<br />
<pre class='csharp' name='code'>public class SummaryReport {
public virtual int ProductId { get; set; }
public virtual string ProductName { get; set; }
public virtual decimal SalesTotal { get; set; }
public virtual boolen IsActive { get; set; }
}
</pre>NOTE: The column names returned from the stored procedure <em>MUST</em> match exactly (case sensitive) the property names on your c# class. <br />
<br />
Now for the calling code, as you can see we make use of the method <em>GetNamedQuery</em> and <em>SetResultTransformer</em>:-<br />
<pre class='csharp' name='code'>var results = Session
.GetNamedQuery("SummaryReport")
.SetInt32("productId", productId);
.SetResultTransformer(
new AliasToBeanResultTransformer(typeof(SummaryReport)));
return results.List<SummaryReport>();
</pre>Note: we call the stored procedure with a parameter <em>:productId</em>. The colon tells NHibernate that this is a parameter. In our calling code we have <em>.SetInt32("productId", productId);</em>, notice that the colon is not required.<br />
<br />
Simple isn't it?<br />Rippohttp://www.blogger.com/profile/10427021389808024817noreply@blogger.com2tag:blogger.com,1999:blog-2705677810452718752.post-74801055097671460872012-04-04T07:28:00.003+01:002012-04-04T08:29:17.586+01:00SetResultTransformer into an anonymous typeThe other day I was looking for a way to shortcut the <em>SetResultTransformer</em> and cast a list to an anonymous type rather than having to hand craft DTO classes with public properties (getters/setters). I stumbled on a GitHub Gist by <a target='_blank' href="https://github.com/Buthrakaur">Buthrakaur</a> (Filip Kinský) that did just this.<br />
<br />
<b>Why would we want to do this?</b><br />
<ul><li>We may want to return a few columns from an entity</li>
<li>We want to return Json from an MVC view which just gets thrown away</li>
<li>We want to cut down the amount of code that just handles redundant DTO's because we want to pass data around</li>
</ul><br />
The solution to this is really easy (thanks Filip), first we create an anonymous type inline and then project straight into this using an extension method <em>.ListAs(dto)</em><br />
<pre class='csharp' name='code'>//first create our anonymous type DTO
var dto = new {
Id = 0L,
Source = string.Empty,
Destination = string.Empty,
Is301 = false
};
//notice the ListAs(dto) extension method
var model = Session.QueryOver<CmsRedirect>()
.SelectList(s => s
.Select(x => x.Id).WithAlias(() => dto.Id)
.Select(x => x.Source).WithAlias(() => dto.Source)
.Select(x => x.Destination).WithAlias(() => dto.Destination)
.Select(x => x.Do301).WithAlias(() => dto.Is301)
)
.Take(take).Skip(page * pageSize)
.ListAs(dto);
return Json(new { Total = total, List = model },
JsonRequestBehavior.AllowGet);
</pre>The source to the GIST can be <a target='_blank' href="https://gist.github.com/1613003">found here</a>.<br />
<pre class='csharp' name='code'>public static class NHibernateExtensions {
public static IList<TRes> ListAs<TRes>(
this IQueryOver qry, TRes resultByExample) {
var ctor = typeof(TRes).GetConstructors().First();
return qry.UnderlyingCriteria
.SetResultTransformer(
Transformers.AliasToBeanConstructor(
(ConstructorInfo) ctor)
).List<TRes>();
}
}
</pre>And the Json that gets returned is:-<br />
<pre class='xml' name='code'>{
"Total":3,
"List":[
{"Id":101000,"Source":"clients.aspx",
"Destination":"portfolio","Is301":true},
{"Id":101101,"Source":"consultancy.aspx",
"Destination":"consultancy","Is301":true},
{"Id":101102,"Source":"contact.aspx",
"Destination":"contact","Is301":true},
]
}
</pre>This is perfect for plugging straight into my <a target='_blank' href="http://www.kendoui.com/documentation/ui-widgets/grid/overview.aspx">Kendo UI Grid</a>.<br />
<br />
A big thank you to Filip for taking some of the repetiveness out of my day.Rippohttp://www.blogger.com/profile/10427021389808024817noreply@blogger.com0tag:blogger.com,1999:blog-2705677810452718752.post-3509551916321012692012-04-01T20:30:00.000+01:002012-04-01T20:30:48.163+01:00QueryOver restriction using an aliasAdding a restriction on a where clause from an alias can be tricky to understand using QueryOver. The <em>SQL</em> we are trying to achieve is:-<br />
<pre name='code' class='sql'>from projects p
inner join user u on p.UserId = u.UserId
where m.UserId = 100 or p.OwnerId = 100
</pre>Our goal is to achieve the following:-<br />
<ul><li>We have a project that has a <em>owner</em> and a list of <em>managers</em>.</li>
<li>Both managers and owners are <em>users</em></li>
<li>We want to get all projects for a user that they are the owner OR the manager</li>
<li>We want to use <em>QueryOver</em><br />
</ul>The domain:- <pre name='code' class='csharp'>public class User {
long Id;
string Name;
}
public class Project {
long Id;
User Owner;
IList<User> Managers;
}
</pre>The query is built like this:- <pre name='code' class='csharp'>User manager = null;
var query = session
.QueryOver<Project>()
.JoinAlias(j => j.Managers, () => manager)
.Where(w => manager.Id == user1.Id || w.Owner.Id == user1.Id)
.List<Project>();
</pre>The above query joins <em>Project</em> to <em>Managers</em> using an alias <em>manager</em>. This allows us to then use the alias <em>manager</em> in the where statement e.g. <pre name='code' class='csharp'>.Where(w => manager.Id == user1.Id || w.Owner.Id == user1.Id)
</pre>This works correctly but could have a small glitch. What happens if our domain contains a user that is a owner BUT is not a manager? The problem is the SQL will be created using an <em>inner join</em>. This problem can be fixed by simply adding a <em>LEFT</em> join to the query. <pre name='code' class='csharp'>.JoinAlias(j => j.Managers, () => manager).Left
</pre>It should be noted that this was an answer I gave to a recent <a target='_blank' href="http://stackoverflow.com/questions/9645264/restriction-on-collection-with-disjunction-using-nhibernate-queryover/9646079#9646079">StackOverFlow question</a>.Rippohttp://www.blogger.com/profile/10427021389808024817noreply@blogger.com0tag:blogger.com,1999:blog-2705677810452718752.post-87434003397136434472012-03-29T06:06:00.001+01:002012-03-29T14:17:19.300+01:00Back to the blogAfter almost 3 months lay off due to our second son being born (Nico) I have decided to start blogging again (well Nico has decided that I can). He is starting to sleep more through the night and I am now waking up in the morning feeling (almost) refreshed and quite lucid.<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiCp73iW4BCx40s20DvMkw1PBGwgdeCVAc3vr7-CRzSPysU82NOirfEDJ0DbqS43BTLr4B3ZVeXCR6IdhPJj2qkaBRGFrRKqszy8tcqHlH2uYgOGtNNWGvyarlTHLUi53FMFie427Z18Ezd/s1600/P1130647.jpg" imageanchor="1" style=""><img border="0" height="300" width="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiCp73iW4BCx40s20DvMkw1PBGwgdeCVAc3vr7-CRzSPysU82NOirfEDJ0DbqS43BTLr4B3ZVeXCR6IdhPJj2qkaBRGFrRKqszy8tcqHlH2uYgOGtNNWGvyarlTHLUi53FMFie427Z18Ezd/s400/P1130647.jpg" /></a></div><br />
<br />
I still will be blogging about the .net framework which will include MVC, NHibernate and Simple.Data.<br />
<br />
Fingers crossed I can get 4-6 posts out a month and work my way towards 8 posts. If there is anything you want to know then give me a shout!<br />
<br />
Thanks for everyones support, RippoRippohttp://www.blogger.com/profile/10427021389808024817noreply@blogger.com0tag:blogger.com,1999:blog-2705677810452718752.post-62479868948312775342011-12-13T09:06:00.002+00:002011-12-13T09:06:30.913+00:00Automatically trim html controls in a asp.net mvc projectI have noticed over the years that users being users sometimes posts forms on a website that has leading or trailing spaces into the input controls. This I find is especially a problem when the user has copied and pasted an email/website address from a internet page or email. On the surface this might be OK but for me especially for email and website addresses when I display this back in a href then sometimes we get:-<br />
- <em>'mailto:// email@email.com '</em> or <br />
- <em>href=' http://www.wildesoft.net '</em><br />
<br />
So what I need to remember is to either a) trim before I send to the database or b) trim after I retrieve from the database. I for one prefer option one, better to keep the database correct rather than fixing output in the UI. But is there a way that we can do this for every html control that is posted? With asp.net MVC we can override the default model binder which performs a trim before the value gets to the action method on a controller.<br />
<br />
All you need to do is to add the following class to your mvc project:-<br />
<pre class='csharp' name='code'>public class TrimModelBinder : DefaultModelBinder
{
protected override void SetProperty(
ControllerContext controllerContext,
ModelBindingContext bindingContext,
PropertyDescriptor propertyDescriptor, object value)
{
if (propertyDescriptor.PropertyType == typeof(string))
{
var val = (string)value;
if (!string.IsNullOrEmpty(val))
val = stringValue.Trim();
value = val;
}
base.SetProperty(controllerContext, bindingContext,
propertyDescriptor, value);
}
}
</pre>Then in your application start up method:-<br />
<pre class='csharp' name='code'>protected void Application_Start() {
InitContainer();
...
ModelBinders.Binders.DefaultBinder = new TrimModelBinder();
}
</pre>Now when a user keys in a leading or trailing space into any input control then the <em>TrimModel</em> binder kicks in and automatically removes it for you.<br />
<br />
The only instance where this may be a problem is when a user has a leading or trailing space on their password.Rippohttp://www.blogger.com/profile/10427021389808024817noreply@blogger.com10tag:blogger.com,1999:blog-2705677810452718752.post-13859473011820869192011-12-05T08:05:00.001+00:002011-12-05T09:18:14.332+00:00Simple.Data and mysqlTo connect <a target='_blank' href="https://github.com/markrendle/Simple.Data">Simple.Data</a> to a MySQl database is easiest done via <a target='_blank' href="http://nuget.org/List/Packages/Simple.Data.Mysql">Nuget</a>. NuGet is a Visual Studio extension that makes it easy to install and update open source libraries and tools in Visual Studio.<br />
<br />
As of 1st December 20111 the code has been updated to work with version Simple.Data 0.12.2.1 <br />
<br />
To install via Nuget goto your <a target='_blank' href="http://docs.nuget.org/docs/start-here/using-the-package-manager-console">Package Manager Console</a> window and type:-<br />
<blockquote>PM> Install-Package Simple.Data.Mysql<br />
</blockquote><br />
After installation has been completed then you may need to also install the <a target='_blank' href="http://www.mysql.com/downloads/connector/net/">data connector</a>. As of 1st December the latest version of the connector is 6.4.4<br />
<br />
<blockquote>PM> Install-Package Mysql.Data<br />
</blockquote><br />
<em>Please note:</em> You may not need to perform this step as the version of you choice will be loaded dynamically at run time. This dynamic loading means that it's enough that the Mysql.Data.dll file is present in the same directory as Simple.Data.Mysql.Mysql40.dll at runtime. You don't have to take a dependency on the connector if you don't want to.<br />
<br />
If all has succeeded then your solution will contain a <em>packages.config</em> file.<br />
<pre class='xml' name='code'><?xml version="1.0" encoding="utf-8"?>
<packages>
<package id="MySql.Data" version="6.4.4" />
<package id="Simple.Data.Ado" version="0.12.2.1" />
<package id="Simple.Data.Core" version="0.12.2.1" />
<package id="Simple.Data.Mysql" version="0.12.2.1" />
</packages>
</pre><br />
Now you are ready to start <a href="http://puredotnetcoder.blogspot.com/2011/11/simpledata-projection-into-dto.html">querying your database</a>.<br />
<br />
If you need any help then please direct your questions to the <a href="http://groups.google.com/group/simpledata?lnk=srg&hl=en&ie=UTF-8">user group</a>.<br />
<br />
A big thank you to Vidar Sømme and Richard Hopton who has made all this possible.<br />Rippohttp://www.blogger.com/profile/10427021389808024817noreply@blogger.com0tag:blogger.com,1999:blog-2705677810452718752.post-60735406891046138072011-11-30T07:00:00.000+00:002011-12-01T08:31:02.350+00:00Simple.Data and bulk insertsSimple.Data allows you to pass Lists (or IEnumerables) of dynamically or statically typed objects (ExpandoObjects) to the Insert, Update and UpdateBy methods. This is great if you need to perform any kind of bulk insert/update.<br />
<pre class="csharp" name="code">var db = Database.OpenNamedConnection("dbConnection");
var list = new List<User>();
for (var i = 0; i < 10; i++)
list.Add(new User {
Id = i + 10000, Username = "User" + i,
Password = "Pwd" + i,
DateCreated = DateTime.Now.AddDays(-i),
RoleId = (i % 3)
}
);
//All users are inserted into the database
// with this single call
db.User.Insert(list);
</pre>When bulk inserting into SQL Server <em>Simple.Data</em> makes a call to the ADO.NET <em>DbCommand.Prepare()</em> which actually creates a compiled version of the insert statement on the server itself . Then insert statements are then run one by one which should improve performance. However you may not see and performance gain if you are only bulk inserting 2 or 3 rows as there is a small upfront overhead. This compiled temporary stored procedure will be destroyed when the current connection is closed. The SQL profiler shows us:- <br />
<pre class="sql" name="code">declare @p1 int
set @p1=-1
exec sp_prepexec @p1 output,N'
@p0 int,
@p1 varchar(50),
@p2 varchar(50),
@p3 datetime,@p4 int',
N'insert into [dbo].[User] ([Id],[Username],[Password],
[DateCreated],[RoleId]) values (@p0,@p1,@p2,@p3,@p4)',
@p0=10000,@p1='User0',@p2='Pwd0',
@p3='Nov 29 2011 8:59:44:897PM',@p4=0
select @p1
</pre>and then sends each insert as:- <br />
<pre class="sql" name="code">exec sp_execute 1,@p0=10001,@p1='User1',@p2='Pwd1',
@p3='Nov 28 2011 8:59:44:897PM',@p4=1
exec sp_execute 1,@p0=10002,@p1='User2',@p2='Pwd2',
@p3='Nov 27 2011 8:59:44:897PM',@p4=2
exec sp_execute 1,@p0=10003,@p1='User3',@p2='Pwd3',
@p3='Nov 26 2011 8:59:44:897PM',@p4=0
</pre>Note: In the case of Insert you get back a new list of objects with any database-assigned default values such as identity values or timestamps.<br />
<br />
The following code is just a check to see that the inserts worked:- <br />
<pre class="csharp" name="code">foreach (var item in db.User.All())
Console.WriteLine(string.Concat(
item.Id, " ",
item.Username, " ",
item.Password, " ",
item.DateCreated, " ",
item.RoleId)
);
</pre><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjNd1g9lNwPMaqWOPFCl3NtutvpfEwOU0_tddie_PjdyiU601aIvgihRk4uCt9gx9X4DgJ0XC0wfXibGwaK8i52_o5cZrcHJOO7G6SzcWQJ-LLIUzM3pgqUBKswqmhH-kcvyf4ap3_aMgJS/s1600/Capture.PNG" imageanchor="1"><img border="0" height="122" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjNd1g9lNwPMaqWOPFCl3NtutvpfEwOU0_tddie_PjdyiU601aIvgihRk4uCt9gx9X4DgJ0XC0wfXibGwaK8i52_o5cZrcHJOO7G6SzcWQJ-LLIUzM3pgqUBKswqmhH-kcvyf4ap3_aMgJS/s400/Capture.PNG" width="309" /></a></div><br />
I believe before version 1 release Mark is going to create an Upsert which will either update or insert your entity based on whether the record exists in the database.<br />
<br />
<blockquote>Since writng this post, Mark has also done some macro optimisations using Bulk insert which makes use of <a href="http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx">SqlBulkCopy</a>. Read this <a href="http://blog.markrendle.net/2011/12/01/macro-optimisations/">blog post</a> to find out more.</blockquote>Rippohttp://www.blogger.com/profile/10427021389808024817noreply@blogger.com1tag:blogger.com,1999:blog-2705677810452718752.post-38427883774802601522011-11-28T08:49:00.001+00:002012-04-17T14:12:15.181+01:00Simple.Data RangesIn this blog I am going to show you how you can use <em>FindAll</em> using ranges that makes use of the <em>BETWEEN</em> operator. The BETWEEN operator (in SQL) is used in a WHERE clause to select a range of data between two values.<br />
<br />
Lets look at finding users by Id:-<br />
<pre class='csharp' name='code'>var db = Database.OpenNamedConnection("dbConnection");
var list = db.User.FindAllById(10002.to(10005));
foreach (var item in list)
Console.WriteLine(string.Concat(item.Id, " ", item.Username,
" ", item.Password, " ", item.DateCreated, " ",
item.RoleId));
</pre>This actually produces the following SQL:-<br />
<pre class='sql' name='code'>select
User.Id,
User.Username,
User.Password,
User.DateCreated,
User.RoleId
from
User
WHERE
User.Id BETWEEN 10002 AND 10005
</pre>and returns the following data:-<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjJk2gy4Wc1RUkcpwQ8PCVHQ7WzWqyIFSyZMCQj-hltsTsyv-kIgTX3BADAzrOp7DLDxM4PcDlMT565vjpfQTg6DT6wtZ29i5woCtixpP6_K_Vdqnof7VCXwHjnqwKEnYM9LaAN9np-gELd/s1600/Capture.PNG" imageanchor="1" style=""><img border="0" height="60" width="315" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjJk2gy4Wc1RUkcpwQ8PCVHQ7WzWqyIFSyZMCQj-hltsTsyv-kIgTX3BADAzrOp7DLDxM4PcDlMT565vjpfQTg6DT6wtZ29i5woCtixpP6_K_Vdqnof7VCXwHjnqwKEnYM9LaAN9np-gELd/s400/Capture.PNG" /></a></div><br />
OK so far so good but what about if we want to get all users that have a <em>RoledId</em> of 1 to 2. This is where the dyanamic features of .net4 and Simple.Data comes alive. All I need to do is change <em>FindAllById</em> to <em>FindAllByRoleId</em>:-<br />
<pre class='csharp' name='code'>var list = db.User.FindAllByRoleId(1.to(2));
foreach (var item in list)
Console.WriteLine(string.Concat(item.Id, " ", item.Username,
" ", item.Password, " ", item.DateCreated, " ",
item.RoleId));
</pre>The SQL where clause will now be<br />
<pre name='code' class='sql'>WHERE User.RoleId BETWEEN 1 AND 2
</pre>and returns the following data:-<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEixkOI8gfBiaFm8fvAFnNqjJa3_qVeKdfsC0_qd9E4nvVRnQbdgl_7UMacuV5VQv1ftb-FFkCDJVQ5MGPuuFhTnDOADIGSDZFA24IgjracelxrgXbysJEYG4gfJ5OZGNojJxRjpTG_OCrHz/s1600/Capture.PNG" imageanchor="1" style=""><img border="0" height="76" width="305" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEixkOI8gfBiaFm8fvAFnNqjJa3_qVeKdfsC0_qd9E4nvVRnQbdgl_7UMacuV5VQv1ftb-FFkCDJVQ5MGPuuFhTnDOADIGSDZFA24IgjracelxrgXbysJEYG4gfJ5OZGNojJxRjpTG_OCrHz/s400/Capture.PNG" /></a></div><br />
OK great but what about date ranges?<br />
<pre class='csharp' name='code'>var list = db.User
.FindAllByDateCreated("2011-11-20".to("2011-11-22 17:00"));
foreach (var item in list)
Console.WriteLine(string.Concat(item.Id, " ", item.Username,
" ", item.Password, " ", item.DateCreated, " ",
item.RoleId));
</pre>The SQL where clause will now be<br />
<pre name='code' class='sql'>WHERE User.DateCreated
BETWEEN '2011-11-20 00:00:00'
AND '2011-11-22 17:00:00'
</pre>and returns the following data:-<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgVGXh8jaLGpvgtDcCB21gt0pBjid7U-Pn6C-zcHcVoQpHhN5aCPKUFnHPbxZxmqvQCi8x4tBOltoGp7OStPRGzSPrOM1SDZOQ0E9Gv0ok7N4IBIFg6tKotjLqtAAXGXhuX3JfsumrxkAcU/s1600/Capture.PNG" imageanchor="1" style=""><img border="0" height="41" width="311" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgVGXh8jaLGpvgtDcCB21gt0pBjid7U-Pn6C-zcHcVoQpHhN5aCPKUFnHPbxZxmqvQCi8x4tBOltoGp7OStPRGzSPrOM1SDZOQ0E9Gv0ok7N4IBIFg6tKotjLqtAAXGXhuX3JfsumrxkAcU/s400/Capture.PNG" /></a></div><br />
Great can we also make user of using the BETWEEN operator for strings? you bet:-<br />
<pre class='csharp' name='code'>var list = db.User.FindAllByUsername("User2".to("User4"));
</pre>The SQL where clause will now be<br />
<pre name='code' class='sql'>WHERE User.Username BETWEEN 'User2' AND 'User4'
</pre><br />
You can also use FindAllBy... range for arrays, this will make use of the IN operator (in SQL) which allows you to specify multiple values in a WHERE clause, more to follow...<br />
<br />
<br />
<br />Rippohttp://www.blogger.com/profile/10427021389808024817noreply@blogger.com0tag:blogger.com,1999:blog-2705677810452718752.post-66407038807648688892011-11-21T20:49:00.001+00:002011-11-23T20:24:29.865+00:00Simple.Data explicit joinsI am delving a bit more into <em>Simple.Data</em> and starting to look at joins. I am going to begin with a classic one-to-many relationship between the tables <em>cmsmeta</em> and <em>cmspage</em>:- <br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhh8a8DmlGojoBKjlksVHK5XZEtDTS_lQBEiywJMT6RwKLbzOgxeHESuz5y_pIVKWnl4NbzAo8A3oUoaHIRlzajHU3rDUjyrXVJ3oKD42yAgEiMw3KjggnRH0nSmAcPTohPSS2ZwbnoXlOr/s1600/Untitled-1.png" imageanchor="1" style=""><img border="0" height="213" width="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhh8a8DmlGojoBKjlksVHK5XZEtDTS_lQBEiywJMT6RwKLbzOgxeHESuz5y_pIVKWnl4NbzAo8A3oUoaHIRlzajHU3rDUjyrXVJ3oKD42yAgEiMw3KjggnRH0nSmAcPTohPSS2ZwbnoXlOr/s400/Untitled-1.png" /></a></div><br />
So how does simple.data handle joins? In this blog I am going to show you <a target='_blank' href="http://simplefx.org/simpledata/docs/#/content%2F03%20Querying%20Data%2F02%20Join.htm">explicit joins</a>. <br />
<br />
The following code shows how we can project 2 columns from both tables into a DTO using the on(...) form. <br />
<pre class='csharp' name='code'>var db = Database.OpenConnection(...);
var pages = db.CmsPage
.Query()
.Join(db.CmsMeta).On(db.CmsPage.MetaId == db.CmsMeta.Id)
.Select(
db.CmsPage.Id,
db.CmsPage.PageName,
db.CmsMeta.Keywords.As("MetaKeywords"),
db.CmsMeta.Title.As("MetaTitle")
).Cast<PageDto>();
foreach (var page in pages)
Console.WriteLine(string.Concat(page.Id, " ", page.PageName, ",", page.MetaKeywords, "-", page.MetaTitle));
</pre>The DTO looks like this:-<br />
<pre class='csharp' name='code'>internal class PageDto { {
public long Id { get; set; }
public string PageName { get; set; }
public string MetaTitle { get; set; }
public string MetaKeywords { get; set; }
}
</pre>As you can see we are joining <em>CmsPage</em> with <em>CmsMeta</em> using <em>on(...)</em>. <br />
<br />
There is also another way to describe an explicit join and that is to use named parameters, that is describe the join as <em>Id: db.CmsPageMetaId</em>, whichever you choose is down to personal taste. For example:- <br />
<pre class='csharp' name='code'>var pages = db.CmsPage
.Query()
.Join(db.CmsMeta, Id: db.CmsPage.MetaId)
.Select(...)
</pre>Both pieces of code produces the following:-<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj05a41kVN02-_VRH0AJvwxnybC44jIjd0VG1j1fNxBLMOki0O4s-WBfvOvcX6ijCeRj95C2Ax5DrsarQGtiZoBTjwHz7e0QSJUxZpvz3AL-lcEDR-bArCMTf-fk7UN2AoMBXGt67MEYUQn/s1600/Untitled-2.png" imageanchor="1" style=""><img border="0" height="99" width="393" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj05a41kVN02-_VRH0AJvwxnybC44jIjd0VG1j1fNxBLMOki0O4s-WBfvOvcX6ijCeRj95C2Ax5DrsarQGtiZoBTjwHz7e0QSJUxZpvz3AL-lcEDR-bArCMTf-fk7UN2AoMBXGt67MEYUQn/s400/Untitled-2.png" /></a></div>and in case you are wondering the generated SQL looks like this:-<br />
<pre class='sql' name='code'>select
cmspage.Id,
cmspage.PageName,
cmsmeta.Keywords AS MetaKeywords,
cmsmeta.Title AS MetaTitle
from
cmspage
JOIN cmsmeta ON (cmsmeta.Id = cmspage.MetaId)
</pre>There is another type of join that is called <em>Natural Joins</em> which requires referential integrity and uses the Foreign Key constraints on the tables. Read more about <a href="http://simplefx.org/simpledata/docs/#/content%2F03%20Querying%20Data%2F02%20Join.htm">natural joins</a>. Unless you are using the innoDb engine in MySql then natural joins are not supported.<br />Rippohttp://www.blogger.com/profile/10427021389808024817noreply@blogger.com1tag:blogger.com,1999:blog-2705677810452718752.post-18956042760443382832011-11-20T16:04:00.001+00:002011-11-21T09:49:38.589+00:00Simple.Data a projection into a dtoSorry for the long delay since my last post but my wife gave birth to our son Nico a few weeks ago and to honest this has taken up all my time....<br />
<br />
Anyway back to the blog, today I decided to have a quick look into <a href="https://github.com/markrendle/Simple.Data">Simple.Data</a>. First I created a new console application and then installed the package via nuget:-<br />
<pre name='code' class='text'>PM> Install-Package Simple.Data.Mysql</pre>If you are using Sql Server then just use:-<br />
<pre name='code' class='text'>PM> Install-Package Simple.Data.SqlServer</pre>The first thing I decided to try was projecting a couple of columns from a single table into a DTO. My schema looks like this (note mysql):-<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgHxz5bstTI49Rqdt-7XgTnjL8l_NvrXgae1dJ0LFhDjOc7Ta7GZ9Pa0K7cUrrmMWSiEYv79eB3hjy-d4Csvt7P89FTsIGrjPEp1Efga-ZMZWTLTrM35rRLSiENV0_1Xa3xg35W-fuYETpW/s1600/Untitled-1.png" imageanchor="1" style=""><img border="0" height="223" width="208" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgHxz5bstTI49Rqdt-7XgTnjL8l_NvrXgae1dJ0LFhDjOc7Ta7GZ9Pa0K7cUrrmMWSiEYv79eB3hjy-d4Csvt7P89FTsIGrjPEp1Efga-ZMZWTLTrM35rRLSiENV0_1Xa3xg35W-fuYETpW/s400/Untitled-1.png" /></a></div><pre name='code' class='csharp'>var db = Database.OpenConnection("server=1.1.1.1;user=usera;database=wildesoft;password=*********;");
IEnumerable<CmsPageDto> pages = db.CmsPage
.All()
.Select(db.CmsPage.PageId.As("Id"), db.CmsPage.PageName)
.Cast<CmsPageDto>();
foreach (var page in pages) {
Console.WriteLine(page.Id + " " + page.PageName);
}
</pre>The dto looks like this:-<br />
<pre name='code' class='csharp'>public class CmsPageDto {
public int Id { get; set; }
public string PageName { get; set; }
}
</pre>One thing that I could not fathom out was how to change the column <em>PageId</em> to just <em>Id</em>. After downloading the Simple.Data source code and looked at the tests I worked out that I needed to use the <em>.As("NewColumnName")</em><br />
<br />
and volia...<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg75BN3MfuArMWRKpEO2QPwvOB0wlvcz8dBKrQdQbLBXdANeEvvEd2ef4GYgd3s0rb1pypAqwJgMghCrYZEQs0Vd30Pv9R5VcUHq0BaYpROMoR3JLO6BhHJBNkkE_SbdjKGYsrgck7Lzdn3/s1600/Untitled-2.png" imageanchor="1" style=""><img border="0" height="167" width="309" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg75BN3MfuArMWRKpEO2QPwvOB0wlvcz8dBKrQdQbLBXdANeEvvEd2ef4GYgd3s0rb1pypAqwJgMghCrYZEQs0Vd30Pv9R5VcUHq0BaYpROMoR3JLO6BhHJBNkkE_SbdjKGYsrgck7Lzdn3/s400/Untitled-2.png" /></a></div><br />
The generated SQL looks like this:-<br />
<pre name='code' class='sql'>select
cmspage.PageId AS Id,
cmspage.PageName
from
cmspage
</pre>A sweet spot is that preserves the case of the table and columns names, very important for MySql server.<br />
<br />
Boy is Simple.Data well just simple to use! I am going to use Simple.Data in my next project and hopefully be able to blog about it a bit more..Rippohttp://www.blogger.com/profile/10427021389808024817noreply@blogger.com2tag:blogger.com,1999:blog-2705677810452718752.post-74045397372580601222011-09-22T14:54:00.000+01:002011-09-22T14:57:23.551+01:00Mapping by Code - Part 1Over the next few weeks I am going to put together a sample application showing how you would build a project using Mapping by code. Why do this? Well firstly there are not too many examples on the web yet about NHibernate 3.2 and mapping by code and secondly I am running out of Blog ideas :)<br />
<br />
The domain I shall use will based on events, that is events that people pay to book on to. I have vastly simplified the domain so that it looks like this:-<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgr-GyWF188d9AE2X9H9McJLihyVLwFZK5Y2xEmOUTCUDibxCdLwKv_1dqIkqCWEVj179WOevgCZVRmXJLb1l_DEtVs-dtfbQWGqAueJy-wzGKHmGvYinqF_eU4-srZxg7BF8NIeeWnBOhl/s1600/Capture.PNG" imageanchor="1"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgr-GyWF188d9AE2X9H9McJLihyVLwFZK5Y2xEmOUTCUDibxCdLwKv_1dqIkqCWEVj179WOevgCZVRmXJLb1l_DEtVs-dtfbQWGqAueJy-wzGKHmGvYinqF_eU4-srZxg7BF8NIeeWnBOhl/s400/Capture.PNG" width="550" /></a></div><br />
<h2>Event</h2>This is where events are held, a typical event would be "Developers Forum" or "How to get ahead in business" etc. The event has a name and a time it starts and finishes.<br />
<br />
<h2>Event Package</h2>This table holds the different packages that a visitor can book. For example a package could be:-<br />
"Gold" - you get a better seat and a cup of tea<br />
"Silver" - you get a seat but no tea<br />
"Bronze" - you are forced to stand<br />
<br />
<h2>Event Visitor</h2>As you can see this table holds the people who are attending. A visitor can book more than one ticket so I have decided to use a hierarchical structure, that is the person who books has a ParentId of NULL and the additional people he/she books on will get a Id in ParentId.<br />
<br />
<h2>Event Payment</h2>This table just keeps track of who has paid. Notice that I have denormalised this table a bit and have created a column called NoTickets sold.<br />
<br />
Please note that this domain is for demonstration purposes as I just wanted to show how hierarchical structures can be used in NHibernate.<br />
<br />
Next post will be how we create a C# project to map this domain<br />
Rippohttp://www.blogger.com/profile/10427021389808024817noreply@blogger.com7tag:blogger.com,1999:blog-2705677810452718752.post-16010114103504702102011-09-15T21:06:00.000+01:002011-09-15T21:06:21.308+01:00Queryover, eager loading parent and children with a filterSometimes we want to use eager loading as:-<br />
<br />
1. The dataset returned is small<br />
2. We don't care about the <a href="http://puredotnetcoder.blogspot.com/2011/07/using-eager-loading-can-produce.html">cartesain</a> product.<br />
3. We want to load the parents<br />
4. We want to load the children<br />
5. We want to filter the results based on a where clause on the children<br />
<br />
So how do we go about this, the domain model for this example looks like this:-<br />
<pre class="csharp" name="code">public class Movie : Entity
{
public virtual string Director { get; set; }
public virtual IList<Actor> ActorList { get; set; }
}
public class Actor : Entity
{
public virtual string Name { get; set; }
public virtual string Role { get; set; }
}</pre>When using NHibernate and QueryOver we would specify eager loading by using:-<br />
<pre class='csharp' name='code'>Actor actor = null;
return Session.QueryOver<Movie>()
.Fetch(f => f.ActorList).Eager
.Left.JoinAlias(f => f.ActorList, () => actor)
.Where(() => actor.Name.Like("m%"))
.List();
</pre>Lets look at the SQL output that gets generated:- <br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhUk0_Tc7w5F0Gt9IgU8LYfJYEm_scVSsxUOoCLu_Nw6cH_b2VLm1dXzs4HhU2fO5xn9vMe-PBuwBvRNQMNGfONNj-k7sMVV0wa4c4i-N7boqXTk9I3RwymEaz_g9WfGvhdPjgQtx0RaPEz/s1600/Capture.PNG" imageanchor="1" style=""><img border="0" height="319" width="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhUk0_Tc7w5F0Gt9IgU8LYfJYEm_scVSsxUOoCLu_Nw6cH_b2VLm1dXzs4HhU2fO5xn9vMe-PBuwBvRNQMNGfONNj-k7sMVV0wa4c4i-N7boqXTk9I3RwymEaz_g9WfGvhdPjgQtx0RaPEz/s400/Capture.PNG" /></a></div><br />
Excellent this is exactly what I require, one sql statement is sent to the database and both parents and children are hydrated at the same time.<br />
<br />
<em>However</em>, there is one caveat here, look closely at my c# code and you will see that I am specifying a left join.<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhj-G0REkxIbY4Tf6YZ9DYOsMS9e4t_eA_m-JC6cpb1J3UzDL-CE-FecBINuXt-SqCVEjKHeipfiYUaS6AHj90N5-Hept72GX_HNnku1fxMKillfKCXM2IvYnvXnxg-_rX0VA5TADLg3CGX/s1600/Capture.PNG" imageanchor="1" style=""><img border="0" height="80" width="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhj-G0REkxIbY4Tf6YZ9DYOsMS9e4t_eA_m-JC6cpb1J3UzDL-CE-FecBINuXt-SqCVEjKHeipfiYUaS6AHj90N5-Hept72GX_HNnku1fxMKillfKCXM2IvYnvXnxg-_rX0VA5TADLg3CGX/s400/Capture.PNG" /></a></div>Now for me this is where the some confusion crops up. If I take out the <em>.left</em> out of my code so that it is:-<br />
<pre class='csharp' name='code'>return Session.QueryOver<Movie>()
.Fetch(f => f.ActorList).Eager
.JoinAlias(f => f.ActorList, () => actor)
.Where(() => actor.Name.Like("m%"))
.List();
</pre>and rerun my example the sql that gets generated is:-<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiNEWIzHWax_KzwXmM9ZpFtKEc7nUnZAT8f-KXL2FXUQmCxdHjaEOnb_AUGKmoYuPZw8R_vhK4p9wJv_Hw72j92ZuGpDBoBfiTwCNnyBwxRZ0GNzHIJYJ3fzelrUmeeSzhvQtlFEZhZgFy5/s1600/Capture.PNG" imageanchor="1" style=""><img border="0" height="400" width="387" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiNEWIzHWax_KzwXmM9ZpFtKEc7nUnZAT8f-KXL2FXUQmCxdHjaEOnb_AUGKmoYuPZw8R_vhK4p9wJv_Hw72j92ZuGpDBoBfiTwCNnyBwxRZ0GNzHIJYJ3fzelrUmeeSzhvQtlFEZhZgFy5/s400/Capture.PNG" /></a></div>As you can see we have resorted back to lazy loading, this is quite strange and I am not 100% sure if this is a bug or the intended behaviour.<br />
<br />
So it looks like if you want to take advantage of <em>eager</em> loading and <em>queryover</em> then you always need to specify a <em>.left</em> join in your code!<br />
Rippohttp://www.blogger.com/profile/10427021389808024817noreply@blogger.com13tag:blogger.com,1999:blog-2705677810452718752.post-29545420067738120082011-09-12T08:12:00.000+01:002011-09-14T07:40:49.395+01:00NHibernate QueryOver and NewId or RandReturning a random record using NHibernate is quite simple to achieve. If you do not have the whole collection in memory then you ought to consider using <em>NEWID()</em> (for MSSQL) or <em>RAND()</em> (for MySql)<br />
<br />
The following MSSQL query will return 5 random Testimonials:-<br />
<pre class='sql' name='code'>select TOP 5 * from Testimonials order by newid()
</pre>So how would you do this using NHibernate? The following ICriteria example shows how you would register the <em>MySql</em> function <em>RAND()</em>. If you are using <em>MSSQL</em> then you just need to replace the word <em>RAND</em> with <em>NewId</em>.<br />
<pre class='csharp' name='code'>public class RandomOrder : Order {
public RandomOrder() : base("", true) { }
public override SqlString ToSqlString(
ICriteria criteria, ICriteriaQuery criteriaQuery) {
return new SqlString("RAND()");
}
}
</pre>The following code based on ICritrtia returns a random list of Testimonials:-<br />
<pre class='csharp' name='code'>public IList<CmsTestimonial> GetRandomTestimonials(int count) {
ICriteria criteria = Session
.CreateCriteria(typeof(CmsTestimonial))
.AddOrder(new RandomOrder())
.SetMaxResults(count);
return criteria.List<CmsTestimonial>();
}
</pre>The following code returns a single random Testimonial:-<br />
<pre class='csharp' name='code'>public CmsTestimonial GetRandomTestimonial() {
ICriteria criteria = Session
.CreateCriteria(typeof(CmsTestimonial))
.AddOrder(new RandomOrder())
.SetMaxResults(1);
return criteria.UniqueResult<CmsTestimonial>();
}
</pre><h2>Can I do this using QueryOver?</h2>Ok all good so far but what about if we want to use <em>QueryOver</em> rather than <em>ICriteria</em>?<br />
<br />
Make sure you keep the RandomOrder class and then create this extension method:-<br />
<pre class='csharp' name='code'>public static class NHibernateExtensions {
public static IQueryOver<TRoot, TSubType>
OrderByRandom<TRoot, TSubType>(
this IQueryOver<TRoot, TSubType> query) {
query.UnderlyingCriteria.AddOrder(new RandomOrder());
return query;
}
}
</pre>and change your ICriteria query to the following QueryOver query<br />
<pre class='csharp' name='code'>public IList<CmsTestimonial> GetRandomTestimonials(int count) {
return Session
.QueryOver<CmsTestimonial>()
.OrderByRandom()
.Take(count)
.List();
}
</pre>NHibernate has so many extensible points and really mostly makes life easy us developers! This is a real sweet spot.Rippohttp://www.blogger.com/profile/10427021389808024817noreply@blogger.com5