Alkampfer’s Place

Retrieving long XML data from SQL without XmlReader

As I told in a previous post if you have to retrieve a great amount of XML generated by a FOR XML in a sql server 2005 environment, you cannot use the ExecuteScalar() method of the Command object. The executeScalar in fact returns only a small amount of XML, so the right way to do this is to use XmlReader.

Now I’m working in a project where we have the DAL written with Enterprise Library (I must admit that I do no like very much the approach of Entlib but this is another story). The concrete DAL is instantiated with IoC, but we have a generic implementation that using Entlib is capable of issuing the SQL query to different types of databases.

Now I have a stored that should return a lot of data in XML format, for Sql 2005 database we can use a FOR XML and all went good, for other database (like oracle) you can generate xml data with a stored procedure in PL-SQL. The decision not to create the XML in the Business Layer is only for simplicity. Now I face a problem, if I use the Generic Database Object of enterprise library, how can I retrieve the content returned with a Select .. FOR XML?

If I use ExecuteScalar the result will be truncated, If I use the XmlReader the concrete DAL cannot work with other types of database. But there is a different way to read data generated with a FOR XML. The reason is that SQL server return the XML in chunk of data, so you can use a simple IDataReader in this way.

StringBuilder aggregator = new StringBuilder();
using (IDataReader dr = CurrentDatabase.ExecuteReader(c)) {
    while (dr.Read())
        aggregator.Append((String) dr[0]);
}

You can simply use a StringBuilder, and reading with a IDataReader, for each row there will be only one column, of type string, that contain a fragment of the XML returned

alk.

Test code is "First class code"

In an hypothetical “ten Commandments” of the Unit Test probably the first is

Test code is First Class Code

I believe that one of the worst mistake a developer can do when writing Unit Tests is to consider unit test “second class code”, in the end the test code does not goes into production (pay attention to the smell test logic in production) so it does not worth to spend too much time with Test Code.

This is absolutely false. If you really want to gain the maximum advantage from Unit Testing practices, you need to consider with great care your Test Code. Test code should be refactored often, it should be organized with well known patterns, and it should adhere to whatever convention or ruleset you use for your production code. Failing to do so quite often will produce fragile tests and when a developer that change 10 lines of code in a class sees that 20 tests are failing due to excessive fragility it tends to ignore test errors, or worse, he removes the test from the suite. In such a situation you need to stop a little bit, look at the test code, and refactor to reduce fragility. Since there are a well know set of pattern to find and reduce this problem, it really worth to spent time to have stronger test code.

Test code should be simple, if test code is too complex who can assert that the logic of the test is good? Maybe the test is failing because the test has a bug. To avoid this you need to refactor test to achieve simple test, try to look in your test code for Conditional Test Logic smell, and immediately remove the condition, you will obtain better test.

Use SandBox for Database or other resource, this will reduce test race, and it helps you to run test in new machine with less effort. It is frustrating when you run tests and they fail because another programmer is exercising the same database. Create Scripts to setup all the sandbox, make test autoconsistent. As an example, to test a database you can create a routine that verify if an instance of SQLEXPRESS exists on the machine, try to login with Integrated Security, generate a RandomDatabase Name and store it into a file, then create a database, create all the structures to run the test. As an alternative include a complete script in the test to recreate a local test database. All these practices will require you time, but they will pay in the future

Alk.

Tags: Unit Testing

The wonderful world of Unit Testing

Last Friday our usergroup organized a Workshop with the argument “unit Testing and asp.net MVC”. The event was good, I was one the speakers,  and did a presentation on “Introduction to Testing”. The audience was interested, most of the attendee remained until 20:00 PM, even if the official Close Time was 19:00. People did a lot of questions and seems that the overall interest for Unit Testing world is growing.

It seems that we are at a point where a lot of people heard about these “unit Tests”, but only few of them really knows the complexity and the deep of the argument. Doing Unit Testing is not a matter of knowing a tool or another. You can use nUnit, xUnit, mbUnit, msTest or wethever tool you like, but the basis of Unit Testing is not dependant on tools or language or environment.

the excellent site http://xunitpatterns.com/ try to make emphases on pattern used during Unit Testing. It is not important whatever tool or language you use, concepts like Fragile Test, Database Sandbox, and all the others that you can find in the site are universal.

The main problem is that learning to use a tool or framework like xUnit, MbUnit, etc it is a matter of hours, learning to write really good Unit Tests it is a matter of months. It is important to understand that Unit Testing is a complex world, and there is a lot of time to spend on the subject before actually master it effectively.

alk.

Tags: Unit Testing

A better nUnit assertion to verify content of a database Row

Sometimes you need to check that the content of various fields in a database row match some constraints, in this situation a little helper could make it possible to write more elegant assertion

[Test]
public void TestInsertACustomerDataFluent()
{
    NorthwindCustomerDao sut = new NorthwindCustomerDao();
    sut.CreateACustomer("RICCI", "DotNetMarche", "contact", "Loc piano frassineta 31");
    DbAssert.OnQuery(
        @"SELECT * from Customers where CustomerId = ‘RICCI’")
        .That("CustomerID", Is.EqualTo("RICCI"))
        .That("CompanyName", Is.EqualTo("DotNetMarche"))
        .That("ContactName", Is.EqualTo("contact"))
        .That("Address", Text.Contains("31")).ExecuteAssert();
}

The assertion can be read very quicly, assert that on query “Select … ” CustomerID is equal to Ricci, etc etc. The advantage is that you can easily use constraint for each field, in this way the whole assertion is really clear. The class to obtain this result is the following

    public class DbAssert
    {

        public static DbAssert OnQuery(String query)
        {
            return new DbAssert(query);
        }

        public DbAssert That(String field, Constraint constraint)
        {
            constraints.Add(field, constraint);
            return this;
        }

        private DbAssert(string query)
        {
            this.query = query;
        }

        private String query;
        private Dictionary<String, Constraint> constraints = new Dictionary<String, Constraint>();

        public void ExecuteAssert()
        {
            using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.NorthWindTestConnectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = query;
                    conn.Open();
                    using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.SingleRow))
                    {
                        NUnit.Framework.Assert.That(dr.Read(), "La query " + query + " non ha ritornato dati");
                        foreach(KeyValuePair<String, Constraint> kvp in constraints)
                        {
                            NUnit.Framework.Assert.That(dr[kvp.Key], kvp.Value, String.Format("field {0} reason:", kvp.Key));
                        }
                    }
                }
            }
        }

    }

In this version I simply use a Sql connection and a connection string stored in Settings, but it is not difficult to adapt this class to your project, or extend to make it possible to specify from external code the database to use.

alk.

Tags: xUnit Testing

error MSB3323 Unable to find manifest signing certificate in the certificate store and CC.NEt

This morning I did some modification to a project that is deployed with click once, I checked in and the build was broken. I go to CC.NET dashboard and I see that the error is

error MSB3323 Unable to find manifest signing certificate in the certificate store

I tried to do various experiments, but after half hour I came to the solution, simply go to the Project Properties, Signing tab, then I deselect “Sign the ClickOnce manifest” and check it again, all works well again.

I really does not know why but seems like if the old certificate had a problem

alk.