ADO.net introduction

Simple Ado.net database object example

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Linq.Expressions;
using System.Text;

namespace Test
{
    /// <summary>
    /// Database object for
    /// </summary>
    public class Database
    {
        // privates
        protected ILog Logger;

        // prop
        public string ConnectionString { get; set; }

        // ctor
        public Database()
        {
            ConnectionString = ConfigurationManager.ConnectionStrings["connectionsStringName"].ConnectionString;
            Logger = LogManager.GetLogger(this.GetType());
        }

        /// <summary>
        /// Query single table
        /// </summary>
        /// <typeparam name="T">The Type and or tablename to query</typeparam>
        /// <returns>List of T</returns>
        public IList<T> Query<T>() where T : new()
        {
            IList<T> result = null;
            var tableName = DetectTableName<T>();

            try
            {
                var query = "select * from " + tableName;

                using (var sqlConnection = new SqlConnection(ConnectionString))
                {
                    if (sqlConnection.State != ConnectionState.Open)
                    {
                        sqlConnection.Open();
                    }

                    var sqlCommand = new SqlCommand(query, sqlConnection);
                    sqlCommand.CommandType = CommandType.Text;
                    sqlCommand.CommandTimeout = 120;

                    var reader = sqlCommand.ExecuteReader();

                    var mapper = new DataReaderMapper<T>();

                    result = mapper.MapListAll(reader);

                }
            }
            catch (Exception ex)
            {
                Logger.Error(string.Format("Error during the query the table: {0}" + tableName, ex));
                throw;
            }
            return result;
        }

        /// <summary>
        /// Query sing table
        /// </summary>
        /// <typeparam name="T">The type and or tablename to query</typeparam>
        /// <param name="queryExpression">The lambda expression which is converted to a query clause</param>
        /// <returns>List of T</returns>
        public IList<T> Query<T>(Expression<Func<T, bool>> queryExpression) where T : new()
        {
            IList<T> result = null;
            string tableName = DetectTableName<T>();

            try
            {

                var query = "select * from " + tableName;

                // add the where clause
                if (queryExpression != null)
                {
                    query = query + " where " + new QueryTranslator().Translate(queryExpression);
                }

                using (var sqlConnection = new SqlConnection(ConnectionString))
                {
                    if (sqlConnection.State != ConnectionState.Open)
                    {
                        sqlConnection.Open();
                    }

                    var sqlCommand = new SqlCommand(query, sqlConnection) {CommandType = CommandType.Text};
                    sqlCommand.CommandTimeout = 120;

                    var reader = sqlCommand.ExecuteReader();

                    var mapper = new DataReaderMapper<T>();

                    result = mapper.MapListAll(reader);

                }
            }
            catch (Exception ex)
            {
                Logger.Error(string.Format("Error during the query the table: {0}" + tableName, ex));
                throw;
            }
            return result;
        }

        /// <summary>
        /// Detects a table attribute and returns the defined tablename
        /// or returns the TypeName of T as tablename
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns>The tablename</returns>
        private string DetectTableName<T>()
        {
            var tableName = string.Empty;
            var attribute = typeof(T).GetCustomAttributes(false).FirstOrDefault(a => a.GetType() == typeof(Table));
            if (attribute != null)
            {
                tableName = ((Table)attribute).Name;
            }
            else
            {
                tableName = typeof(T).Name;
            }
            return tableName;
        }
    }
} 

{{opmerking.Naam}}:

{{opmerking.OpmerkingText}}

            

Saving your comment....

Naam is verplicht!
Email is verplicht!
Opmerking is verplicht!