基於 Dapper 的一個 DbUtils

    /// <summary>
    /// v1.0
    /// </summary>
    public partial class DbUtils
    {
        string ConnectionString;
        /// <summary>
        /// 
        /// </summary>
        /// <param name="dbName"></param>
        public DbUtils(string dbName)
        {
            ConnectionString = $"server=.;database={dbName};uid=;pwd=;";
        }

        IDbConnection OpenConnection()
        { 
            var conn = new NpgsqlConnection(ConnectionString);
            conn.Open();
            return conn;
        }

        public int Execute(string sql, object param = null, IDbTransaction transaction = null)
        {
            using (var conn = OpenConnection())
            {
                var result = conn.Execute(sql, param, transaction);
                conn.Close();
                conn.Dispose();
                return result;
            }
        }

        public T ExecuteScalar<T>(string sql, object param = null, IDbTransaction transaction = null)
        {
            using (var conn = OpenConnection())
            {
                var result = conn.ExecuteScalar<T>(sql, param, transaction);
                conn.Close();
                conn.Dispose();
                return result;
            }
        }

        public IEnumerable<T> Query<T>(string sql, object param = null, IDbTransaction transaction = null)
        {
            using (var conn = OpenConnection())
            {
                var result = conn.Query<T>(sql, param, transaction);
                conn.Close();
                conn.Dispose();
                return result;
            }
        }

        public IEnumerable<T> Query<T>(string sql, int page, int limit, object param = null, IDbTransaction transaction = null)
        {
            int offset = (page - 1) * limit;
            sql += $" limit {limit} offset {offset}";
            return Query<T>(sql, param);
        }

        public T QueryFirst<T>(string sql, object param = null, IDbTransaction transaction = null)
        {
            using (var conn = OpenConnection())
            {
                var result = conn.QueryFirst<T>(sql, param, transaction);
                conn.Close();
                conn.Dispose();
                return result;
            }
        }

        public T QueryFirstOrDefault<T>(string sql, object param = null, IDbTransaction transaction = null)
        {
            using (var conn = OpenConnection())
            {
                var result = conn.QueryFirstOrDefault<T>(sql, param, transaction);
                conn.Close();
                conn.Dispose();
                return result;
            }
        }

        //public static T QueryMultiple<T>(string sql, object param = null, IDbTransaction transaction = null)
        //{
        //    using (var conn = OpenConnection())
        //    {
        //        var result = conn.QueryMultiple(sql, param, transaction);
        //        conn.Close();
        //        conn.Dispose();
        //        return result;
        //    }
        //}

        public async Task<int> ExecuteAsync(string sql, object param = null, IDbTransaction transaction = null)
        {
            using (var conn = OpenConnection())
            {
                var result = await conn.ExecuteAsync(sql, param, transaction);
                conn.Close();
                conn.Dispose();
                return result;
            }
        }

        public async Task<T> ExecuteScalarAsync<T>(string sql, object param = null, IDbTransaction transaction = null)
        {
            using (var conn = OpenConnection())
            {
                var result = await conn.ExecuteScalarAsync<T>(sql, param, transaction);
                conn.Close();
                conn.Dispose();
                return result;
            }
        }

        public async Task<IEnumerable<T>> QueryAsync<T>(string sql, object param = null, IDbTransaction transaction = null)
        {
            using (var conn = OpenConnection())
            {
                var result = await conn.QueryAsync<T>(sql, param, transaction);
                conn.Close();
                conn.Dispose();
                return result;
            }
        }

        public async Task<IEnumerable<T>> QueryAsync<T>(string sql, int page, int limit, object param = null, IDbTransaction transaction = null)
        {
            int offset = (page - 1) * limit;
            sql += $" limit {limit} offset {offset}";
            return await QueryAsync<T>(sql, param);
        }

        public async Task<T> QueryFirstAsync<T>(string sql, object param = null, IDbTransaction transaction = null)
        {
            using (var conn = OpenConnection())
            {
                var result = await conn.QueryFirstAsync<T>(sql, param, transaction);
                conn.Close();
                conn.Dispose();
                return result;
            }
        }

        public async Task<T> QueryFirstOrDefaultAsync<T>(string sql, object param = null, IDbTransaction transaction = null)
        {
            using (var conn = OpenConnection())
            {
                var result = await conn.QueryFirstOrDefaultAsync<T>(sql, param, transaction);
                conn.Close();
                conn.Dispose();
                return result;
            }
        }
    }

    /// <summary>
    /// 
    /// </summary>
    public partial class DbUtils
    {
        /// <summary>
        /// 
        /// </summary>
        /// <param name="table"></param>
        /// <param name="fields"></param>
        /// <returns></returns>
        public static string GetInsertSQL(string table, string fields)
        {
            return $"INSERT INTO {table}({fields}) VALUES ({GetInsertFields(fields)});";
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="table"></param>
        /// <param name="fields"></param>
        /// <returns></returns>
        public static string GetInsertSQLReturnId(string table, string fields)
        {
            return $"INSERT INTO {table}({fields}) VALUES ({GetInsertFields(fields)}) RETURNING Id;";
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="talbe"></param>
        /// <param name="fields"></param>
        /// <returns></returns>
        public static string GetUpdateSQL(string talbe, string fields)
        {
            return $"UPDATE {talbe} SET {GetUpdateFields(fields)} where id=@id";
        }

        static string GetInsertFields(string value)
        {
            var result = value.Split(new[] { ',', ' ', '\r', '\n' }, StringSplitOptions.RemoveEmptyEntries);
            var fields = string.Join(",@", result);
            return "@" + fields;
        }

        static string GetUpdateFields(string value)
        {
            var collection = value.Split(new[] { ',', ' ', '\r', '\n' }, StringSplitOptions.RemoveEmptyEntries);

            var sb = new StringBuilder();
            foreach (var item in collection)
            {
                sb.Append(item);
                sb.Append("=@");
                sb.Append(item);
                sb.Append(",");
            }

            if (sb.Length > 1)
                sb.Remove(sb.Length - 1, 1);

            return sb.ToString();
        }
    }