C# 应用 - 封装类访问 Postgresql 数据库
- 引入库类
- 连接数据库
- 访问数据库
1）增删改数据库
2）查数据库 - 数据转换
- 事务
1. 引入库类
引入 Npgsql.dll
using Npgsql;
using NpgsqlTypes;
2. 连接数据库
/// <summary>
/// 使用固定连接进行数据库的操作，默认false，表示每次操作都使用新的连接,true,表示数据库操作都使用变量NpgsqlConn，设置为true时必须初始化NpgsqlConn
/// </summary>
public bool UseSpecifiedConn = false;
/// <summary>
/// 使用指定连接执行数据库操作时所用的数据库连接，使用前需设置UseSpecifiedConn，并且自行管理连接的打开和关闭
/// </summary>
public NpgsqlConnection NpgsqlConn;
using Npgsql;
using NpgsqlTypes;
...
public void SetConnectStr(string connStr)
{
SetConnectStr(connStr, false);
}
/// <summary>
/// 构造函数，如果固定连接，初始化 NpgsqlConn；如果不固定，只需要设置 ConnectionString 连接信息。当然，也可以不判断，直接执行。
/// </summary>
/// <param name="connStr"></param>
/// <param name="IsUseSpecifiedConn">是否使用固定连接，用于事务</param>
public void SetConnectStr(string connStr, bool IsUseSpecifiedConn)
{
if (IsUseSpecifiedConn)
{
UseSpecifiedConn = IsUseSpecifiedConn;
NpgsqlConn = new NpgsqlConnection(connStr);
}
else
{
ConnectionString = connStr;
}
}
/// <summary>
/// 关闭数据库连接
/// </summary>
/// <returns></returns>
public bool CloseConn()
{
try
{
NpgsqlConn.Close();
return NpgsqlConn.State == ConnectionState.Closed;
}
catch (Exception ex)
{
Common.WriteLog.Error("关闭数据库连接出错!", ex);
return false;
}
}
/// <summary>
/// 检查数据库连接是否正常
/// </summary>
/// <returns></returns>
public bool TryOpenConn()
{
bool result = false;
using (NpgsqlConnection NpgsqlConn = new NpgsqlConnection(ConnectionString))
{
try
{
NpgsqlConn.Open();
result = NpgsqlConn.State == ConnectionState.Open;
}
catch (Exception ex)
{
Common.WriteLog.Error("打开数据库连接出错!", ex);
}
}
return result;
}
3. 访问数据库
3.1 增删改数据库
3.1.1 返回 int
/// <summary>
/// 增删改 postgre 数据库，返回是否成功标识
/// </summary>
/// <param name="sqlText"></param>
/// <returns></returns>
public int ExecuteNonQuery(string sqlText)
{
return ExecuteNonQuery(sqlText, null);
}
/// <summary>
/// 增删改 postgre 数据库，返回是否成功标识
/// </summary>
/// <param name="sqlText"></param>
/// <param name="param">参数集合</param>
/// <returns></returns>
public int ExecuteNonQuery(string sqlText, NpgsqlParameter[] param)
{
int result = int.MinValue;
NpgsqlConnection conn = UseSpecifiedConn ? NpgsqlConn : new NpgsqlConnection(ConnectionString);
using (var nSqlCommand = new NpgsqlCommand(sqlText, conn))
{
try
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
// 添加参数
if (param != null)
{
SetParmDBNull(param);
nSqlCommand.Parameters.AddRange(param);
}
// 执行查询并返回受影响的行数
result = nSqlCommand.ExecuteNonQuery();
}
catch (NpgsqlException ex)
{
Common.WriteLog.Error("执行数据库语句出错!执行语句:" + sqlText, ex);
if (ex != null)
{
result = ex.ErrorCode;
}
}
finally
{
if (!UseSpecifiedConn)
{
conn.Dispose();
}
}
}
return result;
}
/// <summary>
/// 将参数中null值转换为DBNull
/// </summary>
/// <param name="param"></param>
public void SetParmDBNull(NpgsqlParameter[] param)
{
foreach (var p in param)
{
if (p.Value == null)
{
p.Value = DBNull.Value;
}
}
}
3.1.2 增删改调用实例
/// <summary>
/// 插入xx
/// </summary>
/// <param name="cameraid">摄像头ID</param>
/// <param name="itag">标签</param>
/// <returns></returns>
public int InsertAXX(string cameraid, ExtendCustomTag itag)
{
if (string.IsNullOrEmpty(cameraid) || itag == null) return -1;
string sql = "insert into TableName(CameraID,TagLevel) values(@CameraID,@TagLevel)";
NpgsqlParameter[] param = new NpgsqlParameter[2];
param[0] = new NpgsqlParameter("@CameraID", NpgsqlDbType.Text) { Value = cameraid };
param[1] = new NpgsqlParameter("@TagLevel", NpgsqlDbType.Integer) { Value = itag.Level };
int result = ExecuteNonQuery(sql, param);
return result;
}
/// <summary>
/// 软删除xx
/// </summary>
/// <param name="presetId"></param>
/// <returns> 1 表示操作成功，-1 表示失败</returns>
public int DeleteAXX(string id)
{
int flag = -1;
if (!string.IsNullOrEmpty(id))
{
var deleteSql = string.Format("update tablename set status = false where id = '{0}'", id);
flag = this.ExecuteNonQuery(deleteSql);
}
return flag;
}
3.2 查数据库
3.2.1 返回 datatable
/// <summary>
/// 查询 postgre 数据库，返回 DataTable 数据
/// </summary>
/// <param name="sqlText">sql查询语句</param>
/// <returns></returns>
public DataTable ExecuteQuery(string sqlText)
{
return ExecuteQuery(sqlText, null);
}
/// <summary>
/// 查询 postgre 数据库，返回 DataTable 数据
/// </summary>
/// <param name="sqlText">sql查询语句</param>
/// <param name="param">参数集合</param>
/// <returns></returns>
public DataTable ExecuteQuery(string sqlText, NpgsqlParameter[] param)
{
var dt = new DataTable();
NpgsqlConnection conn = UseSpecifiedConn ? NpgsqlConn : new NpgsqlConnection(ConnectionString);
using (var nda = new NpgsqlDataAdapter(sqlText, conn))
{
try
{
if (param != null)
{
SetParmDBNull(param);
nda.SelectCommand.Parameters.AddRange(param);
}
var ds = new DataSet();
nda.Fill(ds);
dt = ds.Tables[0];
}
catch (Exception ex)
{
Common.WriteLog.Error("查询数据库出错!查询语句:" + sqlText, ex);
}
finally
{
if (!UseSpecifiedConn)
{
conn.Dispose();
}
}
}
return dt;
}
2.2.2 返回 int
/// <summary>
/// 查询唯一值（用来查总记录数等等..）
/// </summary>
/// <param name="sqlText"></param>
/// <returns></returns>
public int QueryOnly(string sqlText)
{
NpgsqlConnection conn = UseSpecifiedConn ? NpgsqlConn : new NpgsqlConnection(ConnectionString);
try
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
NpgsqlCommand objCommand = new NpgsqlCommand(sqlText, conn);
//结果集中的第一行第一列
int count = Convert.ToInt32(objCommand.ExecuteScalar());
return count;
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return 0;
}
finally
{
if (!UseSpecifiedConn)
{
conn.Dispose();
}
}
}
3.2.3 查数据库实例
/// <summary>
/// 获取指定摄像头的标签
/// </summary>
/// <param name="cameraid">摄像头编码</param>
/// <returns></returns>
public DataTable SelectTag(string cameraid)
{
string sql = "select CameraID,TagName,TagLevel,TagLink,TagPTStr,TagDirection from Tag where CameraID=@CameraID";
NpgsqlParameter[] param = new NpgsqlParameter[1];
param[0] = new NpgsqlParameter("@CameraID", NpgsqlDbType.Text) { Value = cameraid };
return ExecuteQuery(sql, param);
}
/// <summary>
/// 获取 xxx
/// </summary>
/// <param name="cameraEncode"></param>
/// <returns></returns>
public List<Preset> SelectPresetBy(string cameraEncode)
{
var list = new List<Preset>();
try
{
if (string.IsNullOrEmpty(cameraEncode))
{
throw new Exception("cameraId 为空");
}
string selectSql = string.Format("select * from preset where cameraencode = '{0}' order by id asc", cameraEncode);
var dt = this.ExecuteQuery(selectSql);
list = DataTableToList<Preset>(dt).ToList();
}
catch (Exception ex)
{
Common.WriteLog.Error("查找预置位列表出错!", ex);
}
return list;
}
public int CheckPoliceSchedule(DateTime checktime)
{
int res = -1;
try
{
var sqlText = string.Format("select COUNT(*) sum from xx where endtime>to_timestamp('{1}','yyyy-MM-dd HH24:MI:SS')", checktime.ToString("yyyy-MM-dd HH:mm:ss"));
res = QueryOnly(sqlText);
}
catch (Exception ex)
{
Common.WriteLog.Error("xx出错!", ex);
}
return res;
}
4. 将 datatable 转成对象
/// <summary>
/// DataTable转换List，反射机制
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public IList<T> DataTableToList<T>(DataTable dt) where T : new()
{
IList<T> list = new List<T>();// 定义集合
Type type = typeof(T); // 获得此模型的类型
var tempName = string.Empty;
foreach (DataRow dr in dt.Rows)
{
T t = new T();
PropertyInfo[] propertys = t.GetType().GetProperties();// 获得此模型的公共属性
foreach (PropertyInfo pi in propertys)
{
tempName = pi.Name;
if (dt.Columns.Contains(tempName))
{
if (!pi.CanWrite) continue;
object value = dr[tempName];
if (value != DBNull.Value)
pi.SetValue(t, value, null);
}
}
list.Add(t);
}
return list;
}
/// <summary>
/// 确认 DataTable 里面最多只有一行数据，转成 Object，反射机制
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public T DataTableToObject<T>(DataTable dt) where T : new()
{
T t = new T();
var tempName = string.Empty;
if (dt.Rows == null || dt.Rows.Count == 0)
{
return t;
}
DataRow dr = dt.Rows[0];
PropertyInfo[] propertys = t.GetType().GetProperties();// 获得此模型的公共属性
foreach (PropertyInfo pi in propertys)
{
tempName = pi.Name;
if (dt.Columns.Contains(tempName))
{
if (!pi.CanWrite) continue;
object value = dr[tempName];
if (value != DBNull.Value)
pi.SetValue(t, value, null);
}
}
return t;
}
5. 事务
public class PostgreSqlDao
{
/// <summary>
/// 打开数据库连接
/// </summary>
/// <returns></returns>
public bool OpenConn()
{
try
{
if (NpgsqlConn.State == ConnectionState.Open)
{
return true;
}
else
{
NpgsqlConn.Open();
return NpgsqlConn.State == ConnectionState.Open;
}
}
catch (Exception ex)
{
Common.WriteLog.Error("打开数据库连接出错!", ex);
return false;
}
}
...
}
// 事务使用
public bool Transaction()
{
bool res = false;
PostgreSqlDao pg = new PostgreSqlDao(Common.ConnectString, true);
if (pg.OpenConn())
{
// 新建一个事务
var trans = pg.NpgsqlConn.BeginTransaction();
try
{
foreach (var x in XX)
{
// 执行数据写入操作
}
// 提交事务
trans.Commit();
res = true;
}
catch (Exception ex)
{
// 回滚事务
trans.Rollback();
}
finally
{
// 关闭连接
pg.CloseConn();
}
}
return res;
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">多条SQL语句</param>
public bool ExecuteTransSQL(List<String> SQLStringList)
{
using (NpgsqlConnection conn = UseSpecifiedConn ? NpgsqlConn : new NpgsqlConnection(ConnectionString))
{
if (conn.State != ConnectionState.Open)
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 true;
}
catch (Exception ex)
{
tx.Rollback();
return false;
}
finally
{
if (!UseSpecifiedConn)
{
conn.Dispose();
}
}
}
}
