欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-12-24 15:53:14
1.增、删、改通用方法 /// /// 增、删、改通用方法 /// /// Command对象的CommandText属性(sql语句或存储过程名称) ///

1.增、删、改通用方法

ADO.NET操作PostgreSQL:数据库操作类(已封装)
        /// <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行记录

ADO.NET操作PostgreSQL:数据库操作类(已封装)
        /// <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行记录

ADO.NET操作PostgreSQL:数据库操作类(已封装)
        /// <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列记录

ADO.NET操作PostgreSQL:数据库操作类(已封装)
        /// <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.分页查询

ADO.NET操作PostgreSQL:数据库操作类(已封装)
        /// <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.执行事务

ADO.NET操作PostgreSQL:数据库操作类(已封装)
        /// <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

ADO.NET操作PostgreSQL:数据库操作类(已封装)
    /// <summary>
    /// commandmodel
    /// </summary>
    public struct psqlcommandmodel
    {
        /// <summary>
        /// commandtext
        /// </summary>
        public string commandtext { set; get; }

        /// <summary>
        /// commandparameters
        /// </summary>
        public npgsqlparameter[] commandparameters { set; get; }
    }
view code