PostgreSQL 封装操作数据库方法
/// <summary> /// 模块名:操作postgres数据库公共类 /// 作用:根据业务需求对数据库进行操作. /// 注:系统中的公共方法,根据需要,逐一引入 /// 作者:东北大亨 /// 编写日期:2012-03-19 /// </summary> using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Configuration; using System.Data; using Npgsql; using System.Reflection; namespace DBDHcms.DBUtility { public class PostgreSqlHelp { #region 获得系统设置参数 /// <summary> /// 获得系统设置参数 /// </summary> /// <param name="key1">查询参数(传进参数为字符串)</param> /// <returns>获得系统设置参数</returns> public static string GetConfigString() { return ConfigurationManager.AppSettings["ConnectionString"]; } #endregion #region 获得系统设置参数 /// <summary> /// 获得系统设置参数 /// </summary> /// <param name="key1">查询参数(传进参数为字符串)</param> /// <returns>获得系统设置参数</returns> public static string GetConfigConnectString(string strKey) { return ConfigurationManager.ConnectionStrings[strKey].ConnectionString; } #endregion #region 创建数据库连接 /// <summary> /// 创建数据库连接 /// </summary> /// <returns></returns> protected NpgsqlConnection GetDBConnection() { NpgsqlConnection connect; connect = new NpgsqlConnection(GetConfigConnectString("")); connect.Open(); return connect; } #endregion #region 根据参数和sql语句查询结果返回DataTable /// <summary> /// 执行SQL返回DataView /// </summary> /// <param name="StrText"></param> /// <param name="StrConnection"></param> /// <returns>返回Table</returns> public static DataView GetPostgreDataView(string strSql) { String strCon = GetConfigString(); DataView dv = new DataView(); using (NpgsqlConnection conn = new NpgsqlConnection(strCon)) { conn.Open(); NpgsqlCommand cmd = new NpgsqlCommand(strSql, conn); NpgsqlDataAdapter NpgDa = new NpgsqlDataAdapter(cmd); DataSet ds = new DataSet(); NpgDa.Fill(ds); if (ds.Tables.Count > 0) { dv = ds.Tables[0].DefaultView; } return dv; } } /// <summary> /// 返回DataTable /// </summary> /// <param name="strSql">sql语句 </param> /// <param name="cmdParms">参数集合</param> /// <returns>dataTable</returns> public static DataTable GetPostGreDataTable(string strSql, NpgsqlParameter[] cmdParms) { String strCon = GetConfigString(); NpgsqlConnection conn = new NpgsqlConnection(strCon); DataTable dt; try { conn.ConnectionString = strCon; dt = new DataTable(); NpgsqlCommand cmd = new NpgsqlCommand(); PrepareCommand(cmd, conn, null, CommandType.Text, strSql, cmdParms); NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(strSql, conn); adapter.SelectCommand = cmd; adapter.Fill(dt); return dt; } catch (NpgsqlException ex) { throw ex; } finally { conn.Close(); } } #endregion #region 新增重载,执行SQL语句,返回影响的记录数 /// <summary> /// 新增重载,执行SQL语句,返回影响的记录数 /// </summary> /// <param name="connection">SqlConnection对象</param> /// <param name="trans">OleDbTransaction对象</param> /// <param name="SQLString">SQL语句</param> /// <returns>影响的记录数</returns> public static int ExecuteSql(NpgsqlConnection connection, NpgsqlTransaction trans, string SQLString, params NpgsqlParameter[] cmdParms) { using (NpgsqlCommand cmd = new NpgsqlCommand()) { try { PrepareCommand(cmd, connection, trans, SQLString, cmdParms); int rows = (int)cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return rows; } catch (NpgsqlException e) { trans.Rollback(); throw e; } } } #endregion #region 执行查询语句,返回DataSet /// <summary> /// 执行查询语句,返回DataSet /// </summary> /// <param name="SQLString">查询语句</param> /// <returns>DataSet</returns> public static DataSet Query(string SQLString, params NpgsqlParameter[] cmdParms) { // 创建数据库的连接 String strCon = GetConfigString(); using (NpgsqlConnection conn = new NpgsqlConnection(strCon)) { NpgsqlCommand cmd = new NpgsqlCommand(); PrepareCommand(cmd, conn, null, SQLString, cmdParms); using (NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd)) { DataSet ds = new DataSet(); try { da.Fill(ds, "ds"); cmd.Parameters.Clear(); } catch (NpgsqlException ex) { throw new Exception(ex.Message); } return ds; } } } #endregion #region 参数赋值 /// <summary> /// 参数赋值 /// </summary> /// <param name="cmd"></param> /// <param name="conn"></param> /// <param name="trans"></param> /// <param name="cmdType"></param> /// <param name="cmdText"></param> /// <param name="cmdParms"></param> private static void PrepareCommand(NpgsqlCommand cmd, NpgsqlConnection conn, NpgsqlTransaction trans, CommandType cmdType, string cmdText, NpgsqlParameter[] cmdParms) { if (conn.State != ConnectionState.Open) conn.Open(); cmd.Connection = conn; cmd.CommandText = cmdText; if (trans != null) cmd.Transaction = trans; cmd.CommandType = cmdType; if (cmdParms != null) { foreach (NpgsqlParameter parm in cmdParms) cmd.Parameters.Add(parm); } } #endregion #region 根据sql语句返回结果集 /// <summary> /// 执行SQL返回Table /// </summary> /// <param name="strSqlText">查询T-SQL</param> /// <returns>返回Table</returns> public static DataTable GetPostgreTable(string strSqlText) { String strCon = GetConfigString(); using (NpgsqlConnection conn = new NpgsqlConnection(strCon)) { conn.Open(); NpgsqlCommand cmd = new NpgsqlCommand(strSqlText, conn); NpgsqlDataAdapter _NpgDa = new NpgsqlDataAdapter(cmd); DataTable dt = new DataTable(); _NpgDa.Fill(dt); return dt; } } #endregion #region 执行简单SQL语句 /// <summary> /// 执行SQL语句,返回影响的记录数 /// </summary> /// <param name="SQLString">SQL语句</param> /// <returns>影响的记录数</returns> public static int ExecuteSql(string SQLString) { String strCon = GetConfigString(); using (NpgsqlConnection connection = new NpgsqlConnection(strCon)) { using (NpgsqlCommand cmd = new NpgsqlCommand(SQLString, connection)) { try { connection.Open(); int rows = cmd.ExecuteNonQuery(); return rows; } catch (NpgsqlException e) { connection.Close(); throw e; } } } } #endregion #region 执行存储过程 /// <summary> /// 执行存储过程 /// </summary> /// <param name="storedProcName">存储过程名字</param> /// <param name="parameters">参数</param> /// <param name="rowsAffected">返回参数</param> /// <returns></returns> public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected) { String strCon = GetConfigString(); using (NpgsqlConnection conn = new NpgsqlConnection(strCon)) { int result; conn.Open(); NpgsqlCommand command = BuildQueryCommand(conn, storedProcName, parameters); rowsAffected = command.ExecuteNonQuery(); result = (int)command.Parameters["@OUT_NUM"].Value; conn.Close(); return result; } } #endregion #region 构建 OleDbCommand 对象(用来返回一个结果集,而不是一个整数值) /// <summary> /// 构建 OleDbCommand 对象(用来返回一个结果集,而不是一个整数值) /// </summary> /// <param name="connection">数据库连接</param> /// <param name="storedProcName">存储过程名</param> /// <param name="parameters">存储过程参数</param> /// <returns>OleDbCommand</returns> private static NpgsqlCommand BuildQueryCommand(NpgsqlConnection connection, string storedProcName, IDataParameter[] parameters) { NpgsqlCommand command = new NpgsqlCommand(storedProcName, connection); command.CommandType = CommandType.StoredProcedure; foreach (NpgsqlParameter parameter in parameters) { if (parameter != null) { // 检查未分配值的输出参数,将其分配以DBNull.Value. if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && (parameter.Value == null)) { parameter.Value = DBNull.Value; } command.Parameters.Add(parameter); } } return command; } #endregion #region 执行多条SQL语句,实现数据库事务。 /// <summary> /// 执行多条SQL语句,实现数据库事务。 /// </summary> /// <param name="SQLStringList">SQL语句的哈希表(key为sql语句,value是该语句的SqlParameter[])</param> public static int ExecuteSqlTran(System.Collections.Generic.List<CommandInfo> cmdList) { // 创建数据库的连接 String ConnStr = GetConfigString(); using (NpgsqlConnection conn = new NpgsqlConnection(ConnStr)) { conn.Open(); using (NpgsqlTransaction trans = conn.BeginTransaction()) { NpgsqlCommand cmd = new NpgsqlCommand(); try { int count = 0; //循环 foreach (CommandInfo myDE in cmdList) { string cmdText = myDE.CommandText; NpgsqlParameter[] cmdParms = (NpgsqlParameter[])myDE.Parameters; PrepareCommand(cmd, conn, trans, cmdText, cmdParms); if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine) { if (myDE.CommandText.ToLower().IndexOf("count(") == -1) { trans.Rollback(); return 0; } object obj = cmd.ExecuteScalar(); bool isHave = false; if (obj == null && obj == DBNull.Value) { isHave = false; } isHave = Convert.ToInt32(obj) > 0; if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave) { trans.Rollback(); return 0; } if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave) { trans.Rollback(); return 0; } continue; } int val = cmd.ExecuteNonQuery(); count += val; if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0) { trans.Rollback(); return 0; } cmd.Parameters.Clear(); } trans.Commit(); return count; } catch { trans.Rollback(); throw; } } } } /// <summary> /// /// </summary> /// <param name="cmd"></param> /// <param name="conn"></param> /// <param name="trans"></param> /// <param name="cmdText"></param> /// <param name="cmdParms"></param> private static void PrepareCommand(NpgsqlCommand cmd, NpgsqlConnection conn, NpgsqlTransaction trans, string cmdText, NpgsqlParameter[] cmdParms) { if (conn.State != ConnectionState.Open) conn.Open(); cmd.Connection = conn; cmd.CommandText = cmdText; if (trans != null) cmd.Transaction = trans; cmd.CommandType = CommandType.Text; if (cmdParms != null) { // 多参数处理 foreach (NpgsqlParameter parameter in cmdParms) { if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && (parameter.Value == null)) { parameter.Value = DBNull.Value; } cmd.Parameters.Add(parameter); } } } #endregion #region 批量执行SQL语句 /// <summary> /// 批量执行SQL语句 /// </summary> /// <param name="Sqlstr">SQL语句数组</param> /// <param name="param">SQL参数对象数组</param> /// <returns>返回成功与否</returns> public static int ExecuteSqls(String[] Sqlstr, List<NpgsqlParameter[]> param) { String ConnStr = GetConfigString(); using (NpgsqlConnection conn = new NpgsqlConnection(ConnStr)) { using (NpgsqlCommand cmd = new NpgsqlCommand()) { NpgsqlTransaction tran = null; cmd.Transaction = tran; try { conn.Open(); tran = conn.BeginTransaction(); cmd.Connection = conn; cmd.Transaction = tran; int count = Sqlstr.Length; for (int i = 0; i < count; i++) { cmd.CommandText = Sqlstr[i]; cmd.Parameters.AddRange(param[i]); cmd.ExecuteNonQuery(); } tran.Commit(); return 1; } catch { tran.Rollback(); return 0; } finally { cmd.Dispose(); conn.Close(); } } } } #endregion #region 获取泛型集合 /// <summary> /// 获取泛型集合(2013年09月修改和添加) /// </summary> /// <typeparam name="T">类型</typeparam> /// <param name="strSqlText">要查询的T-SQL</param> /// <returns></returns> public List<T> GetEntityList<T>(string strSqlText) { String strConnectStr = GetConfigString(); using (NpgsqlConnection conn = new NpgsqlConnection(strConnectStr)) { using (NpgsqlDataAdapter sda = new NpgsqlDataAdapter(strSqlText, conn)) { DataSet ds = new DataSet(); sda.Fill(ds); int iCount = ds.Tables[0].Rows.Count; if (0 < iCount) { return DataSetToList<T>(ds, 0); } else { return null; } } } } #endregion #region DataSetToList /// <summary> /// DataSetToList /// </summary> /// <typeparam name="T">转换类型</typeparam> /// <param name="dataSet">数据源</param> /// <param name="tableIndex">需要转换表的索引</param> /// <returns></returns> public List<T> DataSetToList<T>(DataSet dataSet, int tableIndex) { //确认参数有效 if (dataSet == null || dataSet.Tables.Count <= 0 || tableIndex < 0) return null; DataTable _returnDt = dataSet.Tables[tableIndex]; IList<T> list = new List<T>(); for (int i = 0; i < _returnDt.Rows.Count; i++) { //创建泛型对象 T _t = Activator.CreateInstance<T>(); //获取对象所有属性 PropertyInfo[] _propertyInfo = _t.GetType().GetProperties(); for (int j = 0; j < _returnDt.Columns.Count; j++) { foreach (PropertyInfo _ProInfo in _propertyInfo) { //属性名称和列名相同时赋值 if (_returnDt.Columns[j].ColumnName.ToUpper().Equals(_ProInfo.Name.ToUpper())) { if (_returnDt.Rows[i][j] != DBNull.Value) { _ProInfo.SetValue(_t, _returnDt.Rows[i][j], null); } else { _ProInfo.SetValue(_t, null, null); } break; } } } list.Add(_t); } return list.ToList(); } #endregion #region 执行数据库查询操作 /// <summary> /// 执行数据库查询操作( 2013-11-12添加) /// </summary> /// <param name="connectionString"></param> /// <param name="cmdType"></param> /// <param name="cmdText"></param> /// <param name="commandParameters"></param> /// <returns></returns> public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params NpgsqlParameter[] commandParameters) { NpgsqlCommand cmd = new NpgsqlCommand(); String ConnStr = GetConfigString(); using (NpgsqlConnection conn = new NpgsqlConnection(ConnStr)) { PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return val; } } #endregion public static int ExecuteSqlTran(List<String> SQLStringList) { String ConnStr = GetConfigString(); using (NpgsqlConnection conn = new NpgsqlConnection(ConnStr)) { conn.Open(); NpgsqlCommand cmd = new NpgsqlCommand(); cmd.Connection = conn; NpgsqlTransaction tx = conn.BeginTransaction(); cmd.Transaction = tx; try { int count = 0; for (int n = 0; n < SQLStringList.Count; n++) { string strsql = SQLStringList[n]; if (strsql.Trim().Length > 1) { cmd.CommandText = strsql; count += cmd.ExecuteNonQuery(); } } tx.Commit(); return count; } catch { tx.Rollback(); return 0; } finally { } } } public static object ExcuteScalar(string sql, params NpgsqlParameter[] pms) { String ConnStr = GetConfigString(); using (NpgsqlConnection conn = new NpgsqlConnection(ConnStr)) { conn.Open(); NpgsqlCommand sqlcmd = new NpgsqlCommand(sql, conn); sqlcmd.CommandType = CommandType.Text; sqlcmd.CommandTimeout = 5000; if (pms != null && pms.Length > 0) sqlcmd.Parameters.AddRange(pms); object obj = sqlcmd.ExecuteScalar(); sqlcmd.Parameters.Clear(); return obj; } } public static DataSet ExcuteTableExpand(string sql, int intStartRecord, int intMaxRecord, string tableName) { DataSet ds = new DataSet(); String ConnStr = GetConfigString(); using (NpgsqlConnection conn = new NpgsqlConnection(ConnStr)) { conn.Open(); NpgsqlCommand sqlcmd = new NpgsqlCommand(sql, conn); sqlcmd.CommandType = CommandType.Text; sqlcmd.CommandTimeout = 5000; NpgsqlDataAdapter sqladp = new NpgsqlDataAdapter(sqlcmd); sqladp.Fill(ds, intStartRecord, intMaxRecord, tableName); conn.Close(); return ds; } } #region 执行一条计算查询结果语句,返回查询结果 /// <summary> /// 执行一条计算查询结果语句,返回查询结果(object)---好用测试通过 /// </summary> /// <param name="SQLString">计算查询结果语句</param> /// <returns>查询结果(object)</returns> public static object GetSingle(string SQLString, params NpgsqlParameter[] cmdParms) { // 创建数据库的连接 String ConnStr = GetConfigString(); using (NpgsqlConnection conn = new NpgsqlConnection(ConnStr)) { using (NpgsqlCommand cmd = new NpgsqlCommand()) { try { PrepareCommand(cmd, conn, null, SQLString, cmdParms); object obj = cmd.ExecuteScalar(); cmd.Parameters.Clear(); if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) { return null; } else { return obj; } } catch (NpgsqlException e) { string strCode = e.Code; string ier = e.ErrorCode.ToString(); if ("23514".Equals(ier)) { // 主键重复 return 3; } else { // 未知错误 return 9; } } } } } private static void PrepareCommand1(NpgsqlCommand cmd, NpgsqlConnection conn, NpgsqlTransaction trans, string cmdText, NpgsqlParameter[] cmdParms) { if (conn.State != ConnectionState.Open) conn.Open(); cmd.Connection = conn; cmd.CommandText = cmdText; if (trans != null) cmd.Transaction = trans; cmd.CommandType = CommandType.Text; if (cmdParms != null) { // 多参数处理 foreach (NpgsqlParameter parameter in cmdParms) { if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && (parameter.Value == null)) { parameter.Value = DBNull.Value; } cmd.Parameters.Add(parameter); } } } #endregion } }
/// <summary>
/// 模块名:操作postgres数据库公共类
/// 作用:处理传入参数实体操作
/// 作者:东北大亨
/// 编写日期:2012-03-19
/// </summary>
using System;
using System.Collections.Generic;
//using System.Linq;
using System.Text;
using Npgsql;
namespace DBDHcms.DBUtility
{
public enum EffentNextType
{
/// <summary>
/// 对其他语句无任何影响
/// </summary>
None,
/// <summary>
/// 当前语句必须为"select count(1) from .."格式,如果存在则继续执行,不存在回滚事务
/// </summary>
WhenHaveContine,
/// <summary>
/// 当前语句必须为"select count(1) from .."格式,如果不存在则继续执行,存在回滚事务
/// </summary>
WhenNoHaveContine,
/// <summary>
/// 当前语句影响到的行数必须大于0,否则回滚事务
/// </summary>
ExcuteEffectRows,
/// <summary>
/// 引发事件-当前语句必须为"select count(1) from .."格式,如果不存在则继续执行,存在回滚事务
/// </summary>
SolicitationEvent
}
public class CommandInfo
{
public object ShareObject = null;
public object OriginalData = null;
event EventHandler _solicitationEvent;
public event EventHandler SolicitationEvent
{
add
{
_solicitationEvent += value;
}
remove
{
_solicitationEvent -= value;
}
}
public void OnSolicitationEvent()
{
if (_solicitationEvent != null)
{
_solicitationEvent(this, new EventArgs());
}
}
public string CommandText;
public System.Data.Common.DbParameter[] Parameters;
public EffentNextType EffentNextType = EffentNextType.None;
public CommandInfo()
{
}
public CommandInfo(string sqlText, NpgsqlParameter[] para)
{
this.CommandText = sqlText;
this.Parameters = para;
}
public CommandInfo(string sqlText, NpgsqlParameter[] para, EffentNextType type)
{
this.CommandText = sqlText;
this.Parameters = para;
this.EffentNextType = type;
}
}
}