Executing batches of SQL statements using ADO.NET

Executing batches of SQL statements using ADO.NET

I was writing an auditing tool recently that generated scripts to create triggers. Of course i wanted to guarantee that these scripts did not fail so i did the right thing and first checked if the trigger existed, and dropped it if it did.

Now i wrote these scripts originally in SQL Server Management Studio of course, and used the GO statement to separate them into batches. If you don't do this you will get a horrible error message informing you that CREATE statements must be the first line in a batch.

For more information about batches see: http://www.teratrax.com/tsg/help/queries.html

Now when i went to execute this scripts on the fly, lo and behold i came upon a familiar error message: 'CREATE TRIGGER' must be the first statement in a query batch.

It turns out for some perverse reason ADO.NET interprets the SQL you give it as a single batch, in fact it must go as far as to strip or ignore all GO statements within it. Logically a Command would appear to be singular, but regardless you would hope that it would execute the SQL as written.

So having this in mind there is a very simple solution. The below code splits the SQL into separate batches for singular execution:

string sql = "... your sql ...";
Regex regex = new Regex("^GO", RegexOptions.IgnoreCase | RegexOptions.Multiline);
string[] batches = regex.Split(sql);

Then of course to execute them you just need to loop through each, and execute it as a separate command.

Categories Software Development | Tagged ado.net, sql

Integrating SyntaxHighlighter with TinyMCE

Integrating SyntaxHighlighter with TinyMCE

SyntaxHighlighter is a fantastic set of js libraries that allow you ...

This article assumes that you have already installed TinyMCE (perhaps add part about installing and configuring TinyMCE??)

  1. Download SyntaxHighlighter

  2. Install

    • i just copied the css file into my theme, and the js files into a folder under my scripts dir (~/jscripts/SyntaxHighlighter)
  3. Download Nawaf's codehighlighter plugin from here (http://weblogs.asp.net/nawaf/archive/2008/04/10/syntaxhighlighter-plug-in-for-tinymce-3-x-wysiwyg-editor.aspx) Just scroll down to the bottom of the page, where it lists attachment

  4. Extract this into TinyMCE's plugin directory

  5. Now the next step is to modify your TinyMCE configuration to include the following: plugins: 'codehighlighting', extendedvalidelements: 'textarea[name|class|cols|rows]', removelinebreaks : false, themeadvancedbuttons3add : 'codehighlighting'

    Where you place the button, as defined on the last line above is up to you consult the wiki for more info.

  6. Now all is left to do is to load syntax highlighter on the page you intend to use it. Of course you could manually code this into the page but as i would generally use this within a control i created a class to insert the correct javascript. Now all you have to do is call the static methods in the Page_Load of your control.

public class ClientScriptHelper { public static void RegisterSyntaxHighlighter(Page page) { page.ClientScript.RegisterClientScriptInclude("shCore", page.ResolveClientUrl("~/jscripts/SyntaxHighlighter/shCore.js")); page.ClientScript.RegisterClientScriptInclude("shBrushCSharp", page.ResolveClientUrl("~/jscripts/SyntaxHighlighter/shBrushCSharp.js")); page.ClientScript.RegisterClientScriptInclude("shBrushXml", page.ResolveClientUrl("~/jscripts/SyntaxHighlighter/shBrushXml.js"));

    string clipboardPath = page.ResolveClientUrl("~/jscripts/SyntaxHighlighter/clipboard.swf");
    page.ClientScript.RegisterStartupScript(typeof(ClientScriptHelper), "SyntaxHighlighterInit", @"
        <script language='javascript'>
            dp.SyntaxHighlighter.ClipboardSwf = '" + clipboardPath + @"';
            dp.SyntaxHighlighter.HighlightAll('code');
        </script>   
    ");
}

public static void RegisterTinyMCEFull(Page page)
{
    page.ClientScript.RegisterClientScriptInclude("TinyMCE", 
        page.ResolveClientUrl("~/jscripts/tiny_mce/tiny_mce.js"));
    page.ClientScript.RegisterClientScriptBlock(typeof(ClientScriptHelper), "TinyMCEInit", @"
        <script type='text/javascript'>
            tinyMCE.init({
                mode: 'textareas',
                theme: 'advanced',
                fix_list_elements: true,
                plugins: 'emotions,codehighlighting',
                theme_advanced_buttons3_add: 'emotions, codehighlighting',
                extended_valid_elements: 'textarea[name|class|cols|rows]',          
                remove_linebreaks : false
            });
        </script>
    ");
}

}

Please note above that the initialisation for the SyntaxHighligter is done after the page has loaded. If you are
going to enter this manually, put that block *at the bottom of the page* or it might not work correctly.

The benefits of doing it this way are:
    1. I only have to type it in one place and can reuse it in many
    2. I can dynamically load the libraries (which do take a little while) based upon my own parameters
    3. I can customise the layout based upon my own parameters
    4. They are all registered with the same type, so multiple controls on one page can call this and still have it register only once
    5. I don't have to worry about initalising the SyntaxHightlighter in the right place

And that should be all you really need to know

References:

http://weblogs.asp.net/nawaf/archive/2008/04/10/syntaxhighlighter-plug-in-for-tinymce-3-x-wysiwyg-editor.aspx http://weblogs.asp.net/nawaf/archive/2008/04/06/syntaxhighlighter-plugin-for-tinymce-wysiwyg-editor.aspx

Categories Software Development | Tagged SyntaxHighlighter, TinyMCE

Compiling Castle Active Records for a Partial Trust Environment

Compiling Castle Active Records for a Partial Trust Environment

Castle Active Record's is a brilliant object relational data-mapper that sits on top of nHibernate. Essentially this allows me to abstract the majority of my database calls away but creating a set of classes with a few additional properties attached to them.

I am currently using this system at work in several projects and personally i love it. It allows me to quickly reflect changes in the database, and the about 95% of all queries i can now write in code against objects.

How to compile for partial trust

Checked out the latest source from SVN Installed the latest version of nant 0.86b1 Put the nant bin directory in my path Ran the following statement

nant -D:common.testrunner.enabled=false -D:assembly.allow-partially-trusted-callers=true

It built successfully (for .net 3.5) with 2 non-fatal errors. Recommends a later version of nant (the nightly build) Downloaded the latest nightly build (2008-08-18) Recompiled using this nant with the same parameters Built again with 2 non-fatal errors And voila you should be done with your files in the "build" directory

It's important to note that you can create the assemblies as unsigned (by specifying -D:sign=false) but this will cause you issues if you wish to enable lazy loading later on

D:\Dev\Tools\nant-0.86-nightly-2008-08-18\bin\nant -D:common.testrunner.enabled=false -D:assembly.allow-partially-trusted-callers=true

Links:

  • http://forum.castleproject.org/viewtopic.php?t=1439
  • http://forum.castleproject.org/viewtopic.php?t=4659&sid=9a6619df040193fb021198a62f899cfd
  • http://vhendriks.wordpress.com/2007/11/21/monorail-on-shared-hosting/
Categories Software Development | Tagged .net, Active Records, c#, Castle, nant, ORM

Enabling Lazy Loading for Active Records under Partial Trust

Lazy loading is one of the great features of nHibernate and is one that i could not do without. The problem is that like with most good things they don't work inheritanly on a medium trust web host. The problem is that lazy loading relies on creating proxy classes for your records, at runtime. However you can using a nifty little tool generate your own proxy classes such that Active Records (nHibernate) does not have to generate them for you.

  1. Download the source for the nHibernateProxyGenerator from http://blechie.com/WPierce/archive/2008/02/17/Lazy-Loading-with-nHibernate-Under-Medium-Trust.aspx

  2. Copy across your recently compiled libraries for Active Records into the lib dir. Make sure that you have signed them or this will not work.

  3. Build it. Depending on what version of source you got you may have to change the code a little to get it to compile. I had to update the config so it used a generic dictionary, but that was all.

  4. Now as you are going to have to regenerate the proxies every time your change your model i would recommend that you copy the build dir of this program into a folder inside your libraries folder.

  5. Create a build step to autmomatically generate the proxies for your Model project. Something like this: "$(SolutionDir)libs\NHibernateProxyGenerator\NPG.exe" /in:"$(TargetDir)BadMonkeh.DAO.dll" /out:"$(SolutionDir)libs\BadMonkeh.DAO.Proxies.dll"

    Obviously this step assumes you places NPG in a folder structure libs\NHibernateProxyGenerator off your builds dir, and that your assembly is called BadMonkeh.DAO.dll.

  6. Then add the following line to your configuration inside the activerecord section, substituting the name of your proxy library:

  7. And finally add a reference to the proxy library in your web.config.

References:

  • http://blechie.com/WPierce/archive/2008/02/17/Lazy-Loading-with-nHibernate-Under-Medium-Trust.aspx
  • http://www.nhforge.org/wikis/howtonh/pre-generate-lazy-loading-proxies.aspx
Categories Software Development | Tagged .net, Active Records, c#, Castle, lazy loading, orm

Implementing Active Records in a Partial Trust Environment

Implementing Active Records in a Partial Trust Environment

a) Create a new project for the Model (a.k.a Data Access Layer)

b) Presuming you already have an existing database the next step is to generate classes for all of your strong entities. This can be done using the fantastic tool available here: http://www.bryanchen.com/2007/07/18/caragen-tool-the-castle-active-record-autonomous-generator-tool/ One thing to note about this tool is that is will not generate relationships between entities so you'll have to do that yourself. If you are unsure the on how to do this the manual is a great help. Also you might want to update the templates first to fit your coding style, and fix up any areas in the resulting classes afterwards

c) The next step is to create the connection source for your database. It is better practice to do this in an xml file but you can do it in code of you prefer.

First add the following line inside your webconfig, in the configSections:

<section name="activerecord" requirePermission="false" type="Castle.ActiveRecord.Framework.Config.ActiveRecordSectionHandler, Castle.ActiveRecord"/>

Then add a new section as below:

<activerecord isWeb="true">
    <config>
        <add key="connection.driver_class" value="NHibernate.Driver.SqlClientDriver" />
        <add key="dialect" value="NHibernate.Dialect.MsSql2005Dialect" />
        <add key="connection.provider" value="NHibernate.Connection.DriverConnectionProvider" />
        <add key="connection.connection_string_name" value="badmonkeh" />            
    </config>
</activerecord>

In this case i am referring to a MSSQL 2005 database with a predefined connection string with the name badmonkeh.

d) Then we need a class to initialise your database connection. This class basically has to tweak a couple of settings initialise all of the our record types. An elegant solution would be to use reflection for this, but unfortunately my host does not trust me enough for that. Below is an example class.

public class ActiveRecordsInitialiser { public static void InitaliseSession() { IConfigurationSource config = ActiveRecordSectionHandler.Instance; ActiveRecordStarter.Initialize(config, GetAllActiveRecordTypes()); NHibernate.Cfg.Environment.UseReflectionOptimizer = false; }
private static Type[] GetAllActiveRecordTypes() { List<Type> types = new List<Type>();
//TODO: Manually add types here return types.ToArray(); } }

d) The next step is to ensure that the model is initialised every time the domain is loaded. Open up your global.asax file, or create a new "Global Application Class" if you do not have one. In the Appllication_Start event insert the following code:

`YourNameSpace.ActiveRecordsInitialiser.InitaliseSession();`

Obviously replacing the namespace with your own.

e) To get this to run you will need to add a reference to the following libraries (that are included with active records) to your web.config:

Iesi.Collections.dll, log4net.dll, Castle.Components.Validator.dll

f) Enable lazy loading in a partial trust environment is the final (optional) step. Personally lazy loading is a huge benefit so i can't do without it. However i will leave this for a post of it's own :)

Categories Software Development | Tagged .net, Active Records, c#, Castle, lazy loading, orm, partial trust