ADO.NET操作PostgreSQL:数据库操作类(已封装)

2018-09-19 02:58:33来源:博客园 阅读 ()

新老客户大回馈,云服务器低至5折

1.增、删、改通用方法

        /// <summary>
        /// 增、删、改通用方法
        /// </summary>
        /// <param name="commandText">Command对象的CommandText属性(sql语句或存储过程名称)</param>
        /// <param name="commandParameters">Command对象的Parameters属性(sql语句或存储过程参数)</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string commandText, NpgsqlParameter[] commandParameters)
        {
            using (NpgsqlConnection conn = new NpgsqlConnection(datalink.PsconnectionString))
            {
                using (NpgsqlCommand cmd = new NpgsqlCommand(commandText, conn))
                {
                    cmd.Parameters.AddRange(commandParameters);
                    conn.Open();
                    return cmd.ExecuteNonQuery();
                }
            }
        }
View Code

 

2.读取1行记录

        /// <summary>
        /// 读取1行记录
        /// </summary>
        /// <typeparam name="T">结果集对应的Model</typeparam>
        /// <param name="Reader">读取结果集的SqlDataReader</param>
        /// <param name="commandText">Command对象的CommandText属性(sql语句或存储过程名称)</param>
        /// <param name="commandParameters">Command对象的Parameters属性(sql语句或存储过程参数)</param>
        /// <returns></returns>
        public static T ExecuteReader<T>(Func<NpgsqlDataReader, T> Reader, string commandText, NpgsqlParameter[] commandParameters)
        {
            T entity = default(T);
            using (NpgsqlConnection conn = new NpgsqlConnection(datalink.PsconnectionString))
            {
                using (NpgsqlCommand cmd = new NpgsqlCommand(commandText, conn))
                {
                    cmd.Parameters.AddRange(commandParameters);
                    conn.Open();
                    using (NpgsqlDataReader sr = cmd.ExecuteReader())
                    {
                        while (sr.Read())
                        {
                            entity = Reader(sr);
                        }
                    }
                }
            }
            return entity;
        }
View Code

 

3.读取n行记录

        /// <summary>
        /// 读取n行记录
        /// </summary>
        /// <typeparam name="T">结果集对应的Model</typeparam>
        /// <param name="Reader">读取结果集的SqlDataReader</param>
        /// <param name="commandText">Command对象的CommandText属性(sql语句或存储过程名称)</param>
        /// <param name="commandParameters">Command对象的Parameters属性(sql语句或存储过程参数)</param>
        /// <returns></returns>
        public static List<T> ExecuteReaderList<T>(Func<NpgsqlDataReader, T> Reader, string commandText, NpgsqlParameter[] commandParameters)
        {
            List<T> list = new List<T>();
            using (NpgsqlConnection conn = new NpgsqlConnection(datalink.PsconnectionString))
            {
                using (NpgsqlCommand cmd = new NpgsqlCommand(commandText, conn))
                {
                    cmd.Parameters.AddRange(commandParameters);
                    conn.Open();
                    using (NpgsqlDataReader sr = cmd.ExecuteReader())
                    {
                        while (sr.Read())
                        {
                            list.Add(Reader(sr));
                        }
                    }
                }
            }
            return list;
        }
View Code

 

4.读取第1行第1列记录

        /// <summary>
        /// 读取第1行第1列记录
        /// </summary>
        /// <param name="commandText">Command对象的CommandText属性(sql语句或存储过程名称)</param>
        /// <param name="commandParameters">Command对象的Parameters属性(sql语句或存储过程参数)</param>
        /// <returns></returns>
        public static object ExecuteScalar(string commandText, NpgsqlParameter[] commandParameters)
        {
            using (NpgsqlConnection conn = new NpgsqlConnection(datalink.PsconnectionString))
            {
                using (NpgsqlCommand cmd = new NpgsqlCommand(commandText, conn))
                {
                    cmd.Parameters.AddRange(commandParameters);
                    conn.Open();
                    return cmd.ExecuteScalar();
                }
            }
        }
View Code

 

5.分页查询

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <typeparam name="T">结果集对应的Model</typeparam>
        /// <param name="Reader">读取结果集的DataReader</param>
        /// <param name="table">数据表名称</param>
        /// <param name="limitation">查询条件</param>
        /// <param name="sidx">排序字段名称</param>
        /// <param name="sord">排序方式</param>
        /// <param name="page">页码</param>
        /// <param name="rows">每页的数据量</param>
        /// <returns></returns>
        public static PagedData<T> SearchPagedList<T>(Func<NpgsqlDataReader, T> Reader, string table, string sidx, string sord, int page, int rows, string limitation)
        {
            PagedData<T> result = new PagedData<T> { PageIndex = page, PageSize = rows };

            string sql = "select * from \"" + table + "\" where " + limitation + " order by \"" + sidx + "\"" + " " + sord + " limit @PageSize offset (@PageIndex -1) * @PageSize;";
            sql += "select cast(count(*) as integer) from \"" + table + "\" where " + limitation;

            using (NpgsqlConnection conn = new NpgsqlConnection(postgresqlconn.connectionString))
            {
                using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn))
                {
                    cmd.Parameters.Add("@PageIndex", NpgsqlDbType.Integer).Value = page;
                    cmd.Parameters.Add("@PageSize", NpgsqlDbType.Integer).Value = rows;
                    conn.Open();
                    using (NpgsqlDataReader sr = cmd.ExecuteReader())
                    {
                        result.DataList = new List<T>();
                        while (sr.Read())
                        {
                            result.DataList.Add(Reader(sr));
                        }
                        bool bln = sr.NextResult();
                        while (sr.Read())
                        {
                            result.DataCount = (int)sr[0];
                            result.PageCount = result.DataCount % result.PageSize == 0 ? result.DataCount / result.PageSize : result.DataCount / result.PageSize + 1;
                        }
                    }
                }
            }
            return result;
        }
View Code

 

6.执行事务

        /// <summary>
        /// 执行事务
        /// </summary>
        /// <param name="commandModel">Command参数对象列表</param>
        /// <param name="message">如果事务提交,返回受影响行数;如果事务回滚,返回异常信息。</param>
        /// <returns></returns>
        public static bool ExecuteTransaction(List<PSqlCommandModel> commandModel, out string message)
        {
            message = string.Empty;
            int rows = 0;
            using (NpgsqlConnection connection = new NpgsqlConnection(datalink.PsconnectionString))
            {
                connection.Open();

                NpgsqlCommand command = connection.CreateCommand();
                NpgsqlTransaction transaction = connection.BeginTransaction(); ;

                command.Connection = connection;
                command.Transaction = transaction;

                try
                {
                    foreach (var item in commandModel)
                    {
                        command.CommandText = item.CommandText;
                        command.Parameters.Clear();
                        command.Parameters.AddRange(item.CommandParameters ?? new NpgsqlParameter[] { });
                        rows += command.ExecuteNonQuery();
                    }
                    message = rows.ToString();
                    transaction.Commit();
                    return true;
                }
                catch (Exception e)
                {
                    message = e.Message;
                    transaction.Rollback();
                    return false;
                }
            }
        }
    }
View Code

 

7.PSqlCommandModel

    /// <summary>
    /// CommandModel
    /// </summary>
    public struct PSqlCommandModel
    {
        /// <summary>
        /// CommandText
        /// </summary>
        public string CommandText { set; get; }

        /// <summary>
        /// CommandParameters
        /// </summary>
        public NpgsqlParameter[] CommandParameters { set; get; }
    }
View Code

 

标签:

版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点,本站所提供的摄影照片,插画,设计作品,如需使用,请与原作者联系,版权归原作者所有

上一篇:Webservice开发、引用

下一篇:Git命令