public void Delete <T>(string where = null, SqlTransaction transaction = null) where T : IModelBase
        {
            if (transaction != null)
            {
                var        deleteQuery = ModelBaseHelper.CreateDeleteQuery <T>(where);
                SqlCommand cmd         = new SqlCommand()
                {
                    Connection = transaction.Connection, CommandText = deleteQuery, Transaction = transaction
                };
                cmd.ExecuteNonQuery();
            }
            else
            {
                using (SqlConnection conn = new SqlConnection(_connectionString))
                {
                    conn.Open();

                    var        deleteQuery = ModelBaseHelper.CreateDeleteQuery <T>(where);
                    SqlCommand cmd         = new SqlCommand()
                    {
                        Connection = conn, CommandText = deleteQuery
                    };
                    cmd.ExecuteNonQuery();

                    conn.Close();
                }
            }
        }
        /// <summary>
        /// Bulkcopies the list of data into the database
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="data"></param>
        public void BulkCopy <T>(List <T> data) where T : IModelBase, new()
        {
            var columns = new T().GetFields().ToDictionary(p => p.Key, q => q.Value.GetType());

            using (SqlConnection conn = new SqlConnection(_connectionString))
            {
                conn.Open();

                SqlTransaction trans = conn.BeginTransaction();

                DataTable table = new DataTable();
                table.Columns.AddRange(columns.Select(c => new DataColumn(c.Key, c.Value)).ToArray());

                SqlBulkCopy bulk = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, trans)
                {
                    DestinationTableName = ModelBaseHelper.GetTableName <T>()
                };
                foreach (var column in columns)
                {
                    bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping(column.Key, column.Key));
                }

                data.ForEach(row => table.Rows.Add(row.ToObjectArray()));

                bulk.WriteToServer(table);

                trans.Commit();
            }
        }
        //public void InsertInto<T>(T item, bool clearTable = false) where T : IModelBase
        //{
        //    InsertInto<T>(new[] { item }, clearTable);
        //}

        public IEnumerable <long> InsertInto <T>(IEnumerable <T> dataList, bool clearTable = false) where T : IModelBase
        {
            var result = new List <long>();

            using (SqlConnection conn = new SqlConnection(_connectionString))
            {
                conn.Open();

                if (clearTable)
                {
                    SqlCommand cmd = new SqlCommand()
                    {
                        Connection = conn, CommandText = ModelBaseHelper.CreateDeleteQuery <T>()
                    };

                    var scalar = cmd.ExecuteScalar();
                    if (scalar != null)
                    {
                        result.Add((long)scalar);
                    }
                }

                var insertQuery = ModelBaseHelper.CreateInsertQuery <T>();
                foreach (var combiRow in dataList)
                {
                    SqlCommand cmd = new SqlCommand()
                    {
                        Connection = conn, CommandText = insertQuery
                    };
                    combiRow.AddAsParametersToSqlCommand(cmd);

                    var scalar = cmd.ExecuteScalar();
                    if (scalar != null)
                    {
                        result.Add((long)scalar);
                    }
                }

                conn.Close();
            }

            return(result);
        }
        public void Update <T>(IEnumerable <T> dataList, string where = null, string[] fields = null) where T : IModelBase
        {
            using (SqlConnection conn = new SqlConnection(_connectionString))
            {
                conn.Open();

                var updateQuery = ModelBaseHelper.CreateUpdateQuery <T>(fields, where);
                foreach (var combiRow in dataList)
                {
                    SqlCommand cmd = new SqlCommand()
                    {
                        Connection = conn, CommandText = updateQuery
                    };
                    combiRow.AddAsParametersToSqlCommand(cmd);
                    cmd.ExecuteNonQuery();
                }

                conn.Close();
            }
        }
        public void Create <T>() where T : IModelBase
        {
            using (SqlConnection conn = new SqlConnection(_connectionString))
            {
                conn.Open();

                var query = ModelBaseHelper.CreateTableExistsCode(ModelBaseHelper.GetSchema <T>(), ModelBaseHelper.GetTableName <T>());
                query += "BEGIN\r\n";
                query += ModelBaseHelper.CreateTableCommand <T>() + ")\r\n";
                query += "END";

                SqlCommand cmd = new SqlCommand()
                {
                    Connection = conn, CommandText = query
                };
                cmd.ExecuteNonQuery();

                conn.Close();
            }
        }
        public T SelectSingle <T>(string where = null, string[] fields = null, bool distinct = false, int top = 0) where T : IModelBase, new()
        {
            T result = new T();

            using (SqlConnection conn = new SqlConnection(_connectionString))
            {
                conn.Open();

                var        selectQuery = ModelBaseHelper.CreateSelectQuery <T>(fields, distinct, where, 1);
                SqlCommand cmd         = new SqlCommand()
                {
                    Connection = conn, CommandText = selectQuery
                };
                var reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    result = ModelBaseHelper.ReadFromSqlReader <T>(reader, fields);
                }

                conn.Close();
            }

            return(result);
        }