SprocIt - Sql Access Utility

03.17.16

I like to use this class when I don't want the overhead of an ORM. I think using raw ADO.net is ugly and requires a lot of saramony to use I wrote this utlilty class to pretty up the code. The class uses method chaining to clean up the setup of ADO.

Use Cases:

using (var sprocIt = new SprocIt().UsingConnection(connectionString))
{
     sprocIt
        .WithSproc("sproc.name")
        .WithParams("@Name", "@Name2")
        .WithValues(value, value2)
        .Execute();
}
using (var sprocIt = new SprocIt().UsingConnection(connectionString))
{
	var reader = sprocIt
		.WithSql("Select * from table where Id = @id")
		.WithParams("@Id")
		.WithValues(1)
		.ExecuteReader();
		
	while(reader.Read())
	{
		....
	}
}

Class

using System;
using System.Data;
using System.Data.SqlClient;

namespace Runtime
{
    /// <summary>
    /// Helper class that wraps SqlCommand uses method chaining to setup an execution.
    /// e.g.
    /// using (var sprocIt = new SprocIt().UsingConnection(connectionString))
    /// {
    ///    sprocIt
    ///        .WithSproc("sproc.name")
    ///        .WithParams("@Name")
    ///        .WithValues(value)
    ///        .Execute();
    /// }
    /// </summary>
    public class SprocIt : IDisposable
    {
        private const int Timeout = 1200;
        private SqlCommand _command = new SqlCommand();
        private SqlConnection _connection = new SqlConnection();
        private string _connectionString = string.Empty;

        public virtual void Dispose()
        {
            CloseConnection();
            _connection.Dispose();
            _command.Dispose();
        }

        public virtual int Execute()
        {
            OpenConnection();
            try
            {
                return _command.ExecuteNonQuery();
            }
            finally
            {
                CloseConnection();
            }
        }

        public virtual SqlDataReader ExecuteReader()
        {
            OpenConnection();
            return _command.ExecuteReader();
        }

        public virtual object ExecuteResult()
        {
            OpenConnection();
            try
            {
                return _command.ExecuteScalar();
            }
            finally
            {
                CloseConnection();
            }
        }

        public virtual T ExecuteResult<T>()
        {
            OpenConnection();
            object result = null;
            try
            {
                result = _command.ExecuteScalar();
                return (T)result;
            }
            catch (InvalidCastException)
            {
                if (typeof(T) == typeof(int) && result != null) //fix a string value that is an int 
                {
                    int value;
                    if (int.TryParse(result.ToString(), out value))
                        return (T)((object)value);
                }
            }
            finally
            {
                CloseConnection();
            }
            return default(T);
        }

        public virtual SprocIt UsingConnection(string connection)
        {
            _connectionString = connection;
            _connection = new SqlConnection(_connectionString);
            return this;
        }

        public virtual SprocIt WithName(string sprocName)
        {
            if (string.IsNullOrWhiteSpace(sprocName)) throw new ArgumentNullException("sprocName");
            _command = new SqlCommand(sprocName, _connection)
            {
                CommandType = CommandType.StoredProcedure,
                CommandTimeout = Timeout
            };
            return this;
        }

        public virtual SprocIt WithParams(params string[] parameters)
        {
            for (int i = 0; i < parameters.Length; i++)
            {
                if (_command.Parameters.Count <= i)
                    _command.Parameters.Add(new SqlParameter());
                _command.Parameters[i].ParameterName = parameters[i];
            }
            return this;
        }

        public virtual SprocIt WithSproc(string sprocName)
        {
            if (string.IsNullOrWhiteSpace(sprocName)) throw new ArgumentNullException("sprocName");
            _command = new SqlCommand(sprocName, _connection)
            {
                CommandType = CommandType.StoredProcedure,
                CommandTimeout = Timeout
            };
            return this;
        }

        public virtual SprocIt WithSql(string sql)
        {
            if (string.IsNullOrWhiteSpace(sql)) throw new ArgumentNullException("sql");
            _command = new SqlCommand(sql, _connection)
            {
                CommandType = CommandType.Text,
                CommandTimeout = Timeout
            };
            return this;
        }

        public virtual SprocIt WithValues(params object[] values)
        {
            for (int i = 0; i < values.Length; i++)
            {
                if (_command.Parameters.Count <= i)
                    _command.Parameters.Add(new SqlParameter());
                _command.Parameters[i].Value = values[i];
            }
            return this;
        }

        private void CloseConnection()
        {
            if (_connection.State == ConnectionState.Open)
                _connection.Close();
            _command.Parameters.Clear();
        }

        private void OpenConnection()
        {
            _connection.Open();
        }
    }
}

MVC Model Binding Complex Collections Gotcha

09.15.10

Problem:

Despite using proper MVC binding HTML conventions values are not being bound correctly to your list. The list may have the correct number of objects but all the value are set to default.

Solution:

Use properties instead of public variables.

Correct Html Markup.

<div>Id</div>
<div>
    <input id="Products_0__Id"
           name="Products[0].Id"
           type="text"
           value="1" />
</div>
<div>Name</div>
<div>
    <input id="Products_0__Name"
           name="Products[0].Name"
           type="text"
           value="Product 1" />
</div>
<div>Quantity</div>
<div>
    <input id="Products_0__Quantity"
           name="Products[0].Quantity"
           type="text"
           value="2" />
</div>
<div>Unit Price</div>
<div>
    <input id="Products_0__UnitPrice"
           name="Products[0].UnitPrice"
           type="text"
           value="200.00" />
</div>

Incorrect View Model:

public class Basket
{
    public List<Product> Products { get; set; }
    public Totals Totals { get; set;}
}
public class Product
{
    public int Id;
    public string Name;
    public int Quantity;
    public decimal UnitPrice;
}

Correct View Model:

public class Basket
{
    public List<Product> Products { get; set; }
    public Totals Totals { get; set;}
}
public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int Quantity { get; set; }
    public decimal UnitPrice { get; set; }
}

User Generated Html Content

10.22.09

Problem: You need to handle user generated html content from an unknown source for display in your webapp.

Solution: Well this is alwasy a moving target due to cross site scripting, but my approach is the following.

1. I use MarkItUp to allow users an easy way to format their html.

2. After users has submitted his changes I run it through an HTML Sanitizer (Scroll to the bottom) that users a white list approach. For asp.net mvc you will need to mark your controller as [ValidateInput(false)]

2. If the Sanitization process has removed any user created content I do not save the content.  I then Return there modified content with a warning message, "Some illegal content tags where detected and removed double check your work and try again."  

3. If the content passes through the sanitization process cleanly, I save the raw html content to the database.

4. When rendering to the client I just pass the raw html out of the db to the page.

More Info:

StackOverflow.com

ASP.Net MVC And Ninject

04.10.09

Problem: You want to be able use the Ninject frame work with the new and shiny ASP.Net MVC framework.

Solution: Assembly: Ninject.Framework.MVC Contains an Abstract Base class you can Inherit from to do most of your heavy lifting.

Some Code

Global.asax.cs

public class MvcApplication : NinjectHttpApplication
{
      private static IKernel _kernal;
      protected override void RegisterRoutes(RouteCollection routes)
      {                 
          routes.IgnoreRoute("{resource}.axd/{*pathInfo}");
          routes.MapRoute("Default", // Route name
                          "{controller}/{action}/{id}", // URL with parameters
                          new { controller = "Home", action = "Index", id = "" } // Parameter defaults
                         );
      }                 
     protected override IKernel CreateKernel()
     {
         if (_kernal == null)
         {
             var modules = new IModule[]
             {
                 new AutoControllerModule(Assembly.GetExecutingAssembly()),
                 new DemoModual()
             };                
             _kernal = new StandardKernel(modules);
         }              
         return _kernal;         
      }
}

Generic List Operations

02.04.09

Problem: You need a quick and easy way to manipulate elements contained in a list.

Solution: Lambda Expression, these expressions are used in the same fashion you would use anonymous methods but are less wordy.

Anonymous methods:

employees.FindAll(delegate(employee e1)
{ 
    return e1.Name == "Dave"; 
})

Lambda Expression:

employees.FindAll(e1 => e1.Name == "Dave");
employees.Sort((e1,e2) => e1.LastName.CompareTo(e2.LastName));

For more complex expression use the syntax like (params) => {statment}.

employees.Sort((e1, e2) =>
{                                      
    if (e1.LastName.CompareTo(e2.LastName) == 0)
    {
        return e1.FirstName.CompareTo(e2.FirstName);
    }
    else
    {
        return e1.LastName.CompareTo(e2.LastName);
    }
});

For more Info: MSDN

Resharper Templates

06.12.08

I like and use Resharper because it is cheaper than CodeRush and I don't have to remember as many key commands most things are done with an alt + enter. It also works well with Unit Testing and for more yummy goodness with testing download JP's templates for Resharper.

livetemplates.xml (14.74 KB)

filetemplates.xml (3.79 KB)

Configuration System Failed to Initialize

05.23.08

Problem: Your application contains User Scope Settings and you receive the exception.

“Configuration system failed to initialize” when attempting to retrieve these settings.

Solution: The settings file has been corrupted and is unreadable. Close or stop your application and delete the user.config file. The file is located at:

C:\Documents and Settings\[UserName]\Local Settings\Application Data\[AppCompany]\[AppName] \[AppVersion]\

If you are in Visual Studio you can easily find the exact file that is causing the problem by viewing the details and drilling down to the inner exception to find the file name.

Interview Questions - Fibonacci

04.28.08

I found a interesting interview questions that is most likely a bit over done, but I still like it, it reminds me of my intro to programming classes from back in the day, which for me was only about 5 years ago. This one concerns the Fibonacci Numbers. The problem is write the Fibonacci Sequence without Recursion. With Recursion the fib sequence is relatively easy:

public static int GetFibValueRecursive(int place)
 {
     if (place <= 0) return 0;
     if (place == 1) return 1;
     return GetFibValueRecursive(place - 1) + GetFibValueRecursive(place - 2);
 }

First step to this problem I would write a test to make sure my code is correctly solving the problem.

public void GetFibValue_GivenPlace_ReturnCorrectValue()
{
     Assert.AreEqual(0, Fibonacci.GetFibValue(-1));
     Assert.AreEqual(0, Fibonacci.GetFibValue(0));
     Assert.AreEqual(1, Fibonacci.GetFibValue(1));
     Assert.AreEqual(1, Fibonacci.GetFibValue(2));
     Assert.AreEqual(34, Fibonacci.GetFibValue(9));
     Assert.AreEqual(144, Fibonacci.GetFibValue(12));           
}


Then the code.
public static int GetFibValue(int place)
{
     if (place <= 0) return 0;
     int previous = -1;
     int result = 1;
     for (int i = 0; i <= place; ++i)
     {
         int sum = result + previous;
         previous = result;
         result = sum;
     }
     return result;
}

Bam! The Fibonacci Sequence without recursion of course if you wanted more than just a value for given place I would write it a little different perhaps return a list of values instead of a single value.
FibSequence.zip (71.66 KB)

General C# Questions

04.09.08

One of my career goals as a developer is to work for Microsoft, now that I have graduated school and have 2 years of development experience I am going to start actively perusing this goal. The word on the street is that the interview process for Microsoft positions is not easy. To prep for interviews I am trolling the web looking for possible interview questions and posting my answers. To start off I found these general questions on C#.

1. What is metadata? What information is stored in Metadata?

Metadata describes every type and member defined in your code in a language-neutral manner. It stores the description of the assembly, description of types, and the attributes

2. What are some of the Tables stored in metadata?

Each metadata table holds information about the elements of your program. For example, one metadata table describes the classes in your code, another table describes the fields, and so on. If you have ten classes in your code, the class table will have tens rows, one for each class. Metadata tables reference other tables and heaps. For example, the metadata table for classes references the table for methods.

3. What are the two basic kinds of types in .net framework?

Two basic kinds of types are value types and reference types.

4. What is boxing and unboxing?

Boxing a value type packages it inside an instance of the Object reference type, unboxing is the reverse.

5. What class does all classes implicitly inherited from?

System.Object

6. What is namespace, assembly?

Namespace declares a scope that lets you organize code and gives you a way to create globally unique types. An assembly is a collection of types and resources that are built to work together and form a logical unit of functionality.

7. What is global assembly cache, what is the purpose?

The global assembly cache stores assemblies specifically designated to be shared by several applications on the computer.

8. What are Primitive types?

Any data types directly supported by the compiler are called primitive types. Primitive types map directly to types that exist in the base class library.

9. What is reflection?

Reflection allows the inspection of metadata in a PE file and late binding (run time) to types and their members.

10. What is a delegate?

A delegate is a type that references a method with the same signature.

11. Describe the accessibility modifier “protected internal”.

Access is limited to current project or types derived from the containing class.

12. What does the term immutable mean?

It's simply a class that’s state does not change after it was initialized.

13. What’s the difference between System.String and System.Text.StringBuilder classes?

StringBuilder class represents a mutable string of characters. System.String is immutable.

14. What’s the advantage of using System.Text.StringBuilder over System.String?

A StringBuilder object is preferable for a concatenation operation if an arbitrary number of strings are concatenated; for example, if a loop concatenates a random number of strings of user input.

15. Can you store multiple data types in System.Array?

No, array objects with the same array type share the same Type object.

16. What’s the difference between the System.Array.CopyTo() and System.Array.Clone()?

CopyTo() is a deep copy that copies the values. Clone() is a shallow copy that copies only the elements of the Array.

17. What’s the .NET collection class that allows an element to be accessed using a unique key?

System.Collections.Hashtable or System.Collections.Generic.Dictionary, I prefer the Generic Dictionary it is faster.

18. Will the finally block get executed if an exception has not occurred?

Yes

19. Can multiple catch blocks be executed for a single try statement?

No, the order of the catch clauses is important because the catch clauses are examined in order. Catch the more specific exceptions before the less specific ones.

C#

System.Data.EvaluateException

02.01.08

Problem: You are using Select Method to retrieve rows from a typed DataTable and you keep getting the fallowing exception:

System.Data.EvaluateException: Cannot perform '=' operation on System.String and System.Int32.

This isn't the most helpful error message.

Solution: You are missing hyphens.

Example:

Wrong:

CustomerRow[] rows = Select(string.Format("LastName = {0}", LastName)) as CustomerRow[];

Right:

CustomerRow[] rows = Select(string.Format("LastName = '{0}'", LastName)) as CustomerRow[];