/// <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 - ) * 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 - ) * 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 > )

                sb.Remove(sb.Length - , );

            return sb.ToString();

        }

    }