0
\$\begingroup\$

I've come up with a simple Datasource design which allows me to quickly create Datasources and even abstract it behind Interfaces. Of course I ask myself if I can do something better.

The goal of these classes is to have a simple and easily exchangeable layer which just returns Objects. By easily exchangeable I mean that it can be extracted into an Interface and can then be easily swapped.

Please assume the following things:

  • CustomConnection is derived from IDbConnection, Third-Party.
  • CustomConnection is not thread-safe.
  • CustomCommand supports named parameters.
  • Item is an arbitrary class holding something.
  • The layout of Item matches that of the database (Properties == Constructor == Columns).

public class DataSource : IDisposable
{
        private Object sync = new Object();

        private CustomConnection connection;
        private CustomCommand itemGetter;
        private CustomCommand itemSearcher;

        public DataSource(string server, string database, string username, string password)
        {
            connection = new CustomConnection("ConnectionStringGoesHere");
        }

        // Yes, I'm aware that this might throw an exception.
        public void Open()
        {
            connection.Open();
            CheckConnection();

            // Create the commands
            itemGetter = connection.CreateCommand();
            itemGetter.CommandText = @"
                SELECT
                    someColumn,
                    secondColumn,
                    thirdColumn
                FROM
                    someTable
                WHERE
                    someOtherColumn = @UNIQUE OR
                    evenMoreColumns = @UNIQUE
                LIMIT 1;";
            itemGetter.Parameters.Add("UNIQUE", CustomDbType.VarChar);

            itemSearcher = connection.CreateCommand();
            itemSearcher.CommandText = @"
                SELECT
                    someColumn,
                    secondColumn,
                    thirdColumn
                FROM
                    someTable
                WHERE
                    someOtherColumn LIKE @SEARCH OR
                    evenMoreColumns LIKE @SEARCH OR
                    manyMoreColumns LIKE @SEARCH;";
            itemSearcher.Parameters.Add("SEARCH", CustomDbType.VarChar);
        }

        public Item GetItem(string unique)
        {
            lock(sync)
            {
                CheckConnection();

                if (string.IsNullOrEmpty(unique))
                {
                    return null;
                }

                itemGetter.Parameters["UNIQUE"].Value = unique;

                using (CustomDataReader reader = itemGetter.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        int field = 0;
                        return new Item(
                            (string)reader[field++],
                            (int)reader[field++],
                            (decimal)reader[field++]);
                    }
                }

                return null;
            }
        }

        public Item[] GetItems(string search)
        {
            lock(sync)
            {
                CheckConnection();

                if (string.IsNullOrEmpty(search))
                {
                    return new Item() {};
                }

                itemSearcher.Parameters["SEARCH"].Value = search;

                List<Item> items = new List<Item>();
                using (CustomDataReader reader = itemSearcher.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        int field = 0;
                        items.Add(new Item(
                            (string)reader[field++],
                            (int)reader[field++],
                            (decimal)reader[field++]));
                    }
                }

                return items.ToArray();
            }
        }

        protected void CheckConnection()
        {
            if (connection == null)
            {
                throw new Exception("Connection is null!");
            }
            if (connection.State == System.Data.ConnectionState.Broken || connection.State == System.Data.ConnectionState.Closed)
            {
                throw new Exception("Connection is unusable!");
            }
        }

        #region IDisposable Member

        private bool disposed;

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }
        protected virtual void Dispose(bool disposing)
        {
            if (!this.disposed)
            {
                if (disposing)
                {
                    if (itemGetter != null)
                    {
                        itemGetter.Dispose();
                        itemGetter = null;
                    }
                    if (itemSearcher != null)
                    {
                        itemSearcher.Dispose();
                        itemSearcher = null;
                    }
                    if (connection != null)
                    {
                        connection.Dispose();
                        connection = null;
                    }
                }
                disposed = true;
            }
        }
        #endregion
    }
}

In the mean time I've "refined(?)" my approach by using some sort of lazy creation for the queries, so that those get only created if it is necessary.

Checking of the connection is also gone, as it added nothing of value to it.

public class DataSource : IDisposable
{
        private Object sync = new Object();

        private CustomConnection connection;
        private CustomCommand itemGetter;
        private CustomCommand itemSearcher;

        public DataSource(String server, String database, String username, String password)
        {
            this.connection = new CustomConnection("ConnectionStringGoesHere");
        }

        public void Open()
        {
            connection.Open();
        }

        public Item GetItem(String unique)
        {
            lock(sync)
            {
                if (itemGetter == null)
                {
                    itemGetter = connection.CreateCommand();
                    itemGetter.CommandText = @"
                        SELECT
                            someColumn,
                            secondColumn,
                            thirdColumn
                        FROM
                            someTable
                        WHERE
                            someOtherColumn = @UNIQUE OR
                            evenMoreColumns = @UNIQUE
                        LIMIT 1;";
                    itemGetter.Parameters.Add("UNIQUE", CustomDbType.VarChar);
                }

                if (String.IsNullOrEmpty(unique))
                {
                    return null;
                }

                itemGetter.Parameters["UNIQUE"].Value = unique;

                using (CustomDataReader reader = itemGetter.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        int field = 0;
                        return new Item(
                            (String)reader[field++],
                            (int)reader[field++],
                            (decimal)reader[field++]);
                    }
                }

                return null;
            }
        }

        public Item[] GetItems(String search)
        {
            lock(sync)
            {
                if (itemSearcher == null)
                {
                    itemSearcher = connection.CreateCommand();
                    itemSearcher.CommandText = @"
                        SELECT
                            someColumn,
                            secondColumn,
                            thirdColumn
                        FROM
                            someTable
                        WHERE
                            someOtherColumn LIKE @SEARCH OR
                            evenMoreColumns LIKE @SEARCH OR
                            manyMoreColumns LIKE @SEARCH;";
                    itemSearcher.Parameters.Add("SEARCH", CustomDbType.VarChar);
                }

                if (String.IsNullOrEmpty(search))
                {
                    return new Item() {};
                }

                itemSearcher.Parameters["SEARCH"].Value = search;

                List<Item> items = new List<Item>();
                using (CustomDataReader reader = itemSearcher.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        int field = 0;
                        items.Add(new Item(
                            (String)reader[field++],
                            (int)reader[field++],
                            (decimal)reader[field++]));
                    }
                }

                return items.ToArray();
            }
        }

        #region IDisposable Member

        private bool disposed;

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        protected virtual void Dispose(bool disposing)
        {
            if (!this.disposed)
            {
                if (disposing)
                {
                    if (itemGetter != null)
                    {
                        itemGetter.Dispose();
                    }
                    if (itemSearcher != null)
                    {
                        itemSearcher.Dispose();
                    }
                    if (connection != null)
                    {
                        connection.Dispose();
                    }
                }
                disposed = true;
            }
        }
        #endregion
    }
}
\$\endgroup\$
  • \$\begingroup\$ Have you considered using one of the simple one-file ORMs such as Dapper instead? code.google.com/p/dapper-dot-net \$\endgroup\$ – Den Mar 21 '12 at 10:25
  • \$\begingroup\$ @Den: Yes, but at the moment the target is .NET 2.0. So every ORM I've seen so far fell flat. \$\endgroup\$ – Bobby Mar 21 '12 at 10:29
2
\$\begingroup\$

You can move parameter checks out of locks:

public Item[] GetItems(string search)
{
    if (string.IsNullOrEmpty(search))
        return new Item() {};

    lock(sync)
    {
        CheckConnection();

        itemSearcher.Parameters["SEARCH"].Value = search;

        List<Item> items = new List<Item>();
        using (CustomDataReader reader = itemSearcher.ExecuteReader())
        {
            while (reader.Read())
            {
                int field = 0;
                items.Add(new Item(
                    (string)reader[field++],
                    (int)reader[field++],
                    (decimal)reader[field++]));
            }
        }

        return items.ToArray();
    }
}

Also, I think you are missing finalizer in your Dispose pattern.

\$\endgroup\$
  • 1
    \$\begingroup\$ Why do you need locking at all by the way? Can't you create commands each time? \$\endgroup\$ – Den Mar 21 '12 at 10:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.