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();

            }

        }

    }

}

巴特西

C# 应用 - 封装类访问 Postgresql 数据库

1. 引入库类

2. 连接数据库

3. 访问数据库

3.1 增删改数据库

3.1.1 返回 int

3.1.2 增删改调用实例

3.2 查数据库

3.2.1 返回 datatable

2.2.2 返回 int

3.2.3 查数据库实例

4. 将 datatable 转成对象

5. 事务

最新文章

热门文章