ADO.NET操作PostgreSQL:数据库操作类(已封装)
程序员文章站
2022-12-24 15:53:14
1.增、删、改通用方法 /// /// 增、删、改通用方法 /// /// Command对象的CommandText属性(sql语句或存储过程名称) ///
view code
view code
view code
view code
view code
view code
view code
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(); } } }
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; }
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; }
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(); } } }
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; }
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; } } } }
7.psqlcommandmodel
/// <summary> /// commandmodel /// </summary> public struct psqlcommandmodel { /// <summary> /// commandtext /// </summary> public string commandtext { set; get; } /// <summary> /// commandparameters /// </summary> public npgsqlparameter[] commandparameters { set; get; } }
上一篇: 前端复习笔记
下一篇: 人参杞子泡酒有哪些功效