If we have a varchar column as a Primary Key (don't ask - we also are not able to alter the schema) then we can still use Max to generate IDs programmatically.

However, because the column is char, then SQL Server will determine that '9' is actually greater than '1','0' [10] so we need to be a little bit smarter.

Use this:

1. DECLARE @Length INT
2. SET @Length=(SELECT MAX(LEN(ID)) from test)
3. SELECT TOP 1 ID FROM Test WHERE LEN(ID)=@Length ORDER BY ID DESC

Thanks to http://www.dotnetspider.com/forum/192995-Can-we-apply-Max-function-SQL-Query-for.aspx

And if you want to call this using LINQ to SQL (or variant thereof), you could try:

CREATE PROCEDURE web_GetMaxCampaignCode
(@maxcode int output)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

  DECLARE @Length INT
  SET @Length=(SELECT MAX(LEN(campaign_code)) from ref_campaign_code)
  SET @maxcode = (SELECT TOP 1 campaign_code FROM ref_campaign_code WHERE LEN(campaign_code)=@Length ORDER BY campaign_code DESC)
END
GO


Calling Code:

public static string GetFirstName(int id)
{
using (MyDataContext dataContext = new MyDataContext())
{
string FirstName;
var query = dataContext.GetFirstName(id, ref FirstName);
return FirstName;
}
}

Then just drag your sproc onto the dbml and call it like above.

Interesting real-world example of what goes on under the hood of SQL Server the other day.

We have a routine that locks rows (using REPEATABLE READ) in a transaction that can take some time to run (we simulated 5 minutes).

The same tables are accessed from a couple of websites, however as long as they weren't accessing THOSE rows they would be fine to proceed as normal.  I know we're mixing and matching our locking models here, but I can't see how it would work (nor do I have the time to investigate any further!) if we were to use an optimistic locking strategy for our update routine.  (As an aside, perhaps breaking it into chunks an committing rather than a 5-minute long transaction would help I'm sure)

Anyway, we tested in isolation and all was well, however as soon as we try it with the actual system then it fails-  the system stops responding.

Looking at the query plan, we saw the problem straight away - one of the queries being run by the website was actually doing a table scan.

But why?  These tables were all indexed where the join was happening.

Turns out that SQL Server is clever enough to know when a Table Scan is more efficient than a random access via an index.

Because our test data only had a handful of rows, this meant that a Table Scan happened every time.  Running the same query on a database with 100000s of records and the table scan disappears, replaced with an Index seek.

If you ever get the above error, you can restore from the query command line, via use of the REPLACE command.

This is illustrated here - http://blog.sqlauthority.com/2007/09/27/sql-server-fix-error-3154-the-backup-set-holds-a-backup-of-a-database-other-than-the-existing-database/

I've copied this fix for my own reference:

Fix/WorkAround/Solution:

1) Use WITH REPLACE while using the RESTORE command. 

2) Delete the older database which is conflicting and restore again using RESTORE command.

I understand my solution is little different than BOL but I use it to fix my database issue successfully.

3) Sample Example :
RESTORE DATABASE AdventureWorks
FROM DISK = 'C:\BackupAdventureworks.bak'
WITH REPLACE

Also, to REPLACE AND MOVE, use the following:

RESTORE DATABASE mydb
FROM DISK = 'C:\Temp\data\mybak.bak'
WITH REPLACE,
MOVE 'original_dat' to 'C:\work\data\data2.mdf',
MOVE 'original_log' to 'C:\work\data\data2.ldf'

When you're creating an app that uses Linq and you want to see the SQL that is generated, you can attach to the Log property of the DataContext.

To get this to stream to the DebugWindow, use the following bit of code (credits to Kris Vandermotten - http://www.u2u.info/Blogs/Kris/Lists/Posts/Post.aspx?ID=11)

using System;
using System.Diagnostics;
using System.Globalization;
using System.IO;
using System.Text;

namespace Vandermotten.Diagnostics
{
    /// <summary>
    /// Implements a <see cref="TextWriter"/> for writing information to the debugger log.
    /// </summary>
    /// <seealso cref="Debugger.Log"/>
    public class DebuggerWriter : TextWriter
    {
        private bool isOpen;
        private static UnicodeEncoding encoding;
        private readonly int level;
        private readonly string category;

        /// <summary>
        /// Initializes a new instance of the <see cref="DebuggerWriter"/> class.
        /// </summary>
        public DebuggerWriter()
            : this(0, Debugger.DefaultCategory)
        {
        }

        /// <summary>
        /// Initializes a new instance of the <see cref="DebuggerWriter"/> class with the specified level and category.
        /// </summary>
        /// <param name="level">A description of the importance of the messages.</param>
        /// <param name="category">The category of the messages.</param>
        public DebuggerWriter(int level, string category)
            : this(level, category, CultureInfo.CurrentCulture)
        {
        }

        /// <summary>
        /// Initializes a new instance of the <see cref="DebuggerWriter"/> class with the specified level, category and format provider.
        /// </summary>
        /// <param name="level">A description of the importance of the messages.</param>
        /// <param name="category">The category of the messages.</param>
        /// <param name="formatProvider">An <see cref="IFormatProvider"/> object that controls formatting.</param>
        public DebuggerWriter(int level, string category, IFormatProvider formatProvider)
            : base(formatProvider)
        {
            this.level = level;
            this.category = category;
            this.isOpen = true;
        }

        protected override void Dispose(bool disposing)
        {
            isOpen = false;
            base.Dispose(disposing);
        }

        public override void Write(char value)
        {
            if (!isOpen)
            {
                throw new ObjectDisposedException(null);
            }
            Debugger.Log(level, category, value.ToString());
        }

        public override void Write(string value)
        {
            if (!isOpen)
            {
                throw new ObjectDisposedException(null);
            }
            if (value != null)
            {
                Debugger.Log(level, category, value);
            }
        }

        public override void Write(char[] buffer, int index, int count)
        {
            if (!isOpen)
            {
                throw new ObjectDisposedException(null);
            }
            if (buffer == null || index < 0 || count < 0 || buffer.Length - index < count)
            {
                base.Write(buffer, index, count); // delegate throw exception to base class
            }
            Debugger.Log(level, category, new string(buffer, index, count));
        }

        public override Encoding Encoding
        {
            get
            {
                if (encoding == null)
                {
                    encoding = new UnicodeEncoding(false, false);
                }
                return encoding;
            }
        }

        public int Level
        {
            get { return level; }
        }

        public string Category
        {
            get { return category; }
        }
    }
}

On my recent blog post, I demonstrated that I'd been able to submit the same LINQ query to both an in-memory repository and a L2S Repo.

However, after discussing this on StackOverflow, it seems that we must be careful.  While I have seen this technique demonstrated in other articles, I've not found anything from an official perspective, so I will probably err on the side of caution with this one.

While L2O and L2S may overlap on 90% of cases, that 10% is always going to be the one to bite you when you don't have time to react.  So, it's back to a simpler approach.

I'm still going to stick with the Repository pattern, but instead of exposing the Repository as IQueryable<T> (even if the underlying source is IEnumerable<T>) I'm going to encapsulate the actual queries within a Fetch(ICriteria) method instead.  This worked well for me in a previous application, but it has the obvious drawback that we have to define the queries differently for each Repository implementation.

However, it still allows us to abstract our DAL from our Application layer, as shown in this simple diagram:

So we can still use a Domain Driven approach to developing our app, which is great, but we have to ensure that integration testing is thorough when we introduce our other implemented repositories.

I've started work on a new project recently and the first thing for me to sort out is the Data Access Layer.  Because the Repository pattern served me so well in the past, I'm going to start there.

However, the previous solution used a bespoke QueryBuilder which converted an ICriteria into the appropriate SQL.  This SQL was then just passed through as CommandText on an ADO.NET connection to the appropriate database, Oracle or SQL Server. 

What we did was to mock the Repository using LINQ to Objects, and this allowed us to construct our application in a Domain-Driven fashion.

Great!

However, there was always room for improvement.  I've wanted to use LINQ as my ORM for quite some time now, and I know the purists out there will say that LINQ isn't an ORM, but for practical purposes in this project it's going to be.

Also, I've decided to use L2S rather than the Entity Framework.  My mantra is Design For Test, and right now L2E is just not persistent-ignorant in the slightest.  There are hacks as I've outlined in previous blog posts, but building hacks into the foundation of your application is a recipe for disaster.

While L2S may not be supported in future by MS, who cares?!  It works, plenty of good, solid apps have been built on it, AND IF WE GET THINGS RIGHT, there's no reason why we can't switch to L2E v2.0 next year.

We can do this because we are going to abstract out the actual DataContext, and access our data using POCO and Repository pattern.

In addition, if we can swap out the DataContext, then given that LINQ to Objects and LINQ to SQL queries are the same (or at least I hope they are, more on this later) then we can have a set of queries that we can run on both in-memory and database sources.  Truly Datasource-Agnostic.

I got the inspiration for this from http://compiledexperience.com/Blog/post/Domain-Driven-Design-Repositories-in-LINQ-to-SQL.aspx - and it works!

This diagram shows the LinqToSQL assembly, but we can also replace the SQLDataSource and SQLUnitOfWork with a MemoryDataSource and MemoryUnitOfWork.

For example, if we want to test an in-memory collection, we do the following:

            MemoryUnitOfWork context = new MemoryUnitOfWork();

            Repository<Customer> r = new Repository<Customer>(context);
            r.Save(new Customer(1, "Joe Bloggs"));
            r.Save(new Customer(2, "Joanna Bloggs"));

            IEnumerable<Customer> customers = from c in r.Fetch(null) where c.ID == 1 select c;

            List<Customer> results = customers.ToList<Customer>();

            Assert.AreEqual("Joe Bloggs", results[0].Name);

And if we want to test an actual connection to a database using L2S:

            DataContext db = new DataContext("Data Source=(local);Initial Catalog=DMGTest;Integrated Security=SSPI");

            SqlUnitOfWork context = new SqlUnitOfWork(db);

            Repository<Customer> r = new Repository<Customer>(context);

            IEnumerable<Customer> customers = from c in r.Fetch(null) where c.ID == 1 select c;

            List<Customer> results = customers.ToList<Customer>();

            Assert.AreEqual("Joe Bloggs", results[0].Name);

Note the line

IEnumerable<Customer> customers = from c in r.Fetch(null) where c.ID == 1 select c;

which is identical in both versions.

My next step is to encapsulate the queries using the Specification pattern, as Ritesh shows here.

So now we have completely isolated our domain from our DAL, and with the introduction of the Specification pattern we can look at our queries from a domain perspective also.

The code is just bashed out at the moment, but if you want it then just drop me a line and I'll send it over.

OK, so I'm starting to get to grips a little more with the Repository pattern.  Still got a long way to go, but it makes sense and in true XP style I'm going to run with it.

The namespace/deployment overview is as follows:

I've hidden some of the extra features, such as the DTO namespace.  The BusinessObjects encapsulate the 'raw' DTOs, which are used primarily in serializing the data as JSON via the WebService.

I've also not shown that the RepositoryFactory (and subsequent Repos) could be used directly without having to go via our WebService.

Remember that you can't just go to a BusinessObject and ask it to fetch itself - ALL data access is managed via the Repository.

I've also kept the interface deliberately simple - I don't want to be introducing LINQ goodness at that stage.  Everything is POCO.

You can find a related SO post here - http://stackoverflow.com/questions/741490/repository-pattern-pocos-or-iqueryable/741558#741558

Starting a new database project, the most important thing for me is to design it for test.  In order to do that effectively, we have to keep the business objects and the data access separate.

This concept is nothing new, but there's a lot of different ways to do that these days.

The first idea I had was to have the data access encapsulated within a DataProvider layer which would be interfaced directly by the application.   This would create and return basic DTO objects for the application to play with, as there is little to no business logic in the classes at this point.

Following discussions with my team, this morphed slightly into having the DTOs converted into fully-fledged Business Objects, which would have knowledge of the DAL and access the database in this way.

It seemed okay.... but then I got thinking about how to return a collection of objects, and how all that worked.  So that brought me to the Repository Pattern.

Simply put, the Repository Pattern abstracts the Data Access part from the Business Objects, which means that to fetch an Object, one has to go via the Repo.

There are a number of ways to implement this - doing a search will bring back a LOT of Linq-related implementations.

Right now, I'm going to keep it more in line with the original specification of this pattern.  My Repository is simply going to return the Business Objects themselves.  At the moment it is read only so there is no need for CUD - because of the way we have separated this we can easily come back to this at a later date.

I've been looking at the EF lately and while I've still not settled on how it can be tested (given the lack of persistence agnosticism), I do feel that MS has received enough stick from the community that this will be a feature of v2.

The following article by John Papa provides some good ideas as to how one would use EF as part of an n-tier architecture - http://msdn.microsoft.com/en-us/magazine/cc700340.aspx

I have to give a special mention to this tool as it helps me generate scripts for my Unit Tests really quickly.  It's free at the moment, although each version has an expiration date, but it's well worth a look if you need something to quickly generate scripts.

http://www.sqlscripter.com/

It's also an example of REALLY GOOD USER INTERFACE DESIGN.  The author has taken the time to predict what you're most likely to do and wrapped up any repetitive tasks (for example logging in to the same database with the same credentials, or scripting a certain subset of tables with the same criteria).  You can tell it's a bit rough round the edges, but then it's not a commercial product yet.

Unit Testing of database applications is a large topic, so I'll concentrate on a specific scenario.

If you have the following setup:

TestFixture

• Init - populate database with default data
• Teardown - run delete script to restore the database to it's previous state.

This will be sufficient if none of our tests modify the data in between.  But we'll need to to test Create, Update and Delete methods of our business objects.

So, if one Test updates the database, but another Test is run after it which is expecting the database to be in it's original state (i.e. the state it's in straight after the TestFixture.Init() method has been called)  then we have a problem.  But we can't determine the order that the tests will run in, so what are our options?

We could partition our tests into different TestFixtures, so avoiding the problem altogether.  Or we could run the Populate and Delete scripts after EVERY test - though this is likely to be slow and will probably become impractical as our test library grows.

A good solution would be to have each test run in isolation, where changes to the database are rolled back after each test is run - this way the database can be updated in one test but returned to it's original state before the next test runs.

We can do this by using the TransactionScope object, like so:

[Test]
private void TestSomething()
{
  using(new TransactionScope())
  {
    //Update the database
    //Perform Assertions
  }
}

If we need to do some common initialisation of the TransactionScope object, for example setting IsolationLevel or Timeout properties, then we can wrap this up in a class as follows:

    public class RollbackTransaction: IDisposable
    {
        private TransactionScope mScope;

        public RollbackTransaction()
        {
            TransactionOptions tOptions = new TransactionOptions();
            tOptions.IsolationLevel = IsolationLevel.RepeatableRead;
            tOptions.Timeout = new TimeSpan(2, 0, 0);
            mScope = new TransactionScope(TransactionScopeOption.RequiresNew,
                                                          tOptions);
        }

        ///<summary>
        ///Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources.
        ///</summary>
        ///<filterpriority>2</filterpriority>
        public void Dispose()
        {
            mScope.Dispose();
        }
    }

and instead of using(new TransactionScope()), we would instead have using(new RollbackTransaction()).