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();
        }
    }
}

GoalSystem, Delves - Character Sheet

08.25.13

GoalSystem, Delves - Character Sheet, is a web application design to build and store characters for the table top game GoalSystem, Delves.

Requirements:

  • Registration should be painless.
  • Creating characters should be easy.
  • Site design should be clean and simple.
  • Site needs to be done in under 100 hours.

Implementation:

GoalsSheet.com was developed in less than a month by a single developer(me) and as a side project. The goal was to learn ASP.Net MVC 4, Twitter Bootstrap, and Entity Framework. I tried  make registration as painless as possible. I choose to use OAuth Web Security and used both Google and Facebook providers. To provide a clean and simple design I used Twitter Bootstrap’s scaffolding and forms templates. To speed up development I built a framework that used an adapter pattern over entity framework models. This allowed me to  create  basic CRUD views for any entity model in about 10 lines of code. I choose SQL Server 2005  for my backend because it was familiar and all ready setup on my web server.

Technology Overview:

GoalsSheet.com was build with ASP.Net MVC 4, jquery 1.8, Twiter Bootstrap 2.3.2, OAuth, MS SQL 2005 and Entity Framework 4.4

Screen Shot:

goalsSheet

Wildwood Campsites

06.12.08

Just finished with a client’s website www.wildwoodcampsites.com, I am pretty happy with it and so are they.

Few things that where fun was implementing a "Get Directions," Map with Microsoft Virtual Earth and a Silverlight picture viewer. I have worked with the Google maps api in the past it wasn't bad, but I hate the registration it makes it a pain to move and reuse. I found virtual Earth API far superior with great examples. I was able to get something cool up and working with little Java Script experience. The picture viewer I did not code from scratch, dnntemplet.com was offering it for free as a Beta version; although it didn't work I was able to fix it by digging around in the source code. I am really excited to see what the web will look like coming up with Flex.org and Silverlight 2.0

Universal/Global Spell checks vs Free Dyslexic Helper, System Wide Spell Check

01.26.07

I have been looking for a good universal or system wide spell check for a long time I finally gave up and coded one for my self. I really like my spell check its simple and its fast. Basically it takes two key strokes to use, one to copy the text, two to launch the spell checker with a global assigned hot key. After you have corrected the spelling you paste it back. That is it, simple and fast. Do me a favor anyone that reads this blog compare what I threw together to some of the others. If enough people use/like my spell check I could add some new features like an auto-update and optimizing its memory usage.

My Spell check
Dyslexic Helper - System Wide Spell check.

Others

Enso Words 39.95 - This one takes way to many key strokes and seems too intrusive on what you are doing. I didn't want something to take up my whole screen and all of my attention. It is however very clever program and well done.

GlobalSpellChecker (freeware) - I like it, its free, and it contains a dictionary(very cool). But it uses the Microsoft Word spell checker and you have to leave the keyboard and actually access the program with your mouse, I guess you could combine it with slickrun to make this faster. It also looks like it only does one word per call to GLobalSpellCheck.

Spell Check Anywhere $29.97 - It seems to be the same as what I have done but you have to pay for it.

Style Helper/Color Converter

01.02.07

I found out that editing Cascading Style Sheets (CSS) for my blog as well as other projects can be really annoying, mostly when you are playing colors and font. I find my self constantly editing one number in a color hex set, saving the CSS file, hitting CTRL + F5, and then waiting for browser to refresh. My solution for this was a quick windows app I put together to help me coordinate my colors and font.


This app is very simple, to use it all you have to do is either enter the hex values or click on the buttons next to them choose the proper color or text from the dialog. You can also use dialogs to convert RGB to hex or hex to RGB.

Feel free to downlaod and use this application as much as you like.
DOWNLOAD
StyleHelper.exe (64 KB)
If you would like to see how it works or add more to it you can download the source.
StyleHelper.zip (110.3 KB)

System Wide Spell Check

12.18.06

Dyslexic Helper is a spell check that can be used anywhere in windows.

I have a bit of a problem spelling due to my dyslexia, to help me with this I wrote an application that spell checks copied text. This application can be run anywhere in windows by a combination of keys or the application menu, accessed via the system tray. Feel free to use this application as much as you like.

To use this application just follow the steps below.

  1. DownloadDyslexicHelper.zip (3.36 mb)
  2. Unzip the File named DyslexicHelper.zip
  3. Once unzipped open the newly created folder and run the setup.exe
  4. This setup will install the necessary files to run Dyslexic Helper. Dyslexic helper will also place a shortcut file in your programs folder and another in the startup folder.
  5. Once the application is installed run it start>All Programs>Dyslexic Helper.

  1. Now that Dyslexic Helper is running, to use it you just need to highlight any text in windows copy it and hit the Windows key + F7. This hot key combination can be changed through options in the menu.

  1. Finally correct your spelling and paste it where you need it.

This application is written by me using VB.Net as well as C# and the entire source is availble here, if you would like to see how it works. The spell check control I used is called NetSpell. I have changed this control a little bit but mostly just for looks. The system wide hot key control I found at The Code Project I modified it a little so I could change the hotkey at runtime and return the current selected hotkey as a text value.

DyslexicHelper.zip (3.36 mb)