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[];
result.PageCount = result.DataCount % result.PageSize == ? result.DataCount / result.PageSize : result.DataCount / result.PageSize + ;
}
}
}
}
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 = ;
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; }
}
以下是调用，需要Person实体类
public class Person
{
/// <summary>
/// Constructor
/// </summary>
public Person() { } /// <summary>
/// Constructor
/// </summary>
/// <param name="idCard"></param>
/// <param name="realName"></param>
/// <param name="gender"></param>
/// <param name="address"></param>
public Person( string idCard, string realName, bool gender, string address)
{
IdCard = idCard;
RealName = realName;
Gender = gender;
Address = address;
} /// <summary>
///
/// </summary>
public int PersonId { set; get; } /// <summary>
///
/// </summary>
public string IdCard { set; get; } /// <summary>
///
/// </summary>
public string RealName { set; get; } /// <summary>
///
/// </summary>
public bool Gender { set; get; } /// <summary>
///
/// </summary>
public string Address { set; get; } }
Person Model
/// <summary>
/// Insert
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public static int Insert(Person entity)
{
string sql = @"insert into ""Person"" (""IdCard"",""RealName"",""Gender"",""Address"") values (@IdCard,@RealName,@Gender,@Address)";
NpgsqlParameter[] cmdParams = {
new NpgsqlParameter("@IdCard", NpgsqlDbType.Varchar) { Value = entity.IdCard ?? (object)DBNull.Value },
new NpgsqlParameter("@RealName", NpgsqlDbType.Varchar) { Value = entity.RealName ?? (object)DBNull.Value },
new NpgsqlParameter("@Gender", NpgsqlDbType.Bit) { Value = entity.Gender },
new NpgsqlParameter("@Address", NpgsqlDbType.Varchar) { Value = entity.Address ?? (object)DBNull.Value },
};
return PostgresqlBaseDal.ExecuteNonQuery(sql, cmdParams);
} /// <summary>
/// Update
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public static int Update(Person entity)
{
string sql = @"update ""Person"" set ""IdCard""=@IdCard,""RealName""=@RealName,""Gender""=@Gender,""Address""=@Address where ""PersonId""=@PersonId";
NpgsqlParameter[] cmdParams = {
new NpgsqlParameter("@PersonId", NpgsqlDbType.Integer) { Value = entity.PersonId },
new NpgsqlParameter("@IdCard", NpgsqlDbType.Varchar) { Value = entity.IdCard ?? (object)DBNull.Value },
new NpgsqlParameter("@RealName", NpgsqlDbType.Varchar) { Value = entity.RealName ?? (object)DBNull.Value },
new NpgsqlParameter("@Gender", NpgsqlDbType.Bit) { Value = entity.Gender },
new NpgsqlParameter("@Address", NpgsqlDbType.Varchar) { Value = entity.Address ?? (object)DBNull.Value },
};
return PostgresqlBaseDal.ExecuteNonQuery(sql, cmdParams);
} /// <summary>
/// Delete
/// </summary>
/// <param name="personId"></param>
/// <returns></returns>
public static int Delete(int personId)
{
string sql = @"delete from ""Person"" where ""PersonId""=@PersonId";
NpgsqlParameter[] cmdParams = {
new NpgsqlParameter("@PersonId", NpgsqlDbType.Integer) { Value = personId },
};
return PostgresqlBaseDal.ExecuteNonQuery(sql, cmdParams);
} /// <summary>
/// Reader
/// </summary>
/// <param name="reader"></param>
/// <returns></returns>
private static Person Reader(NpgsqlDataReader reader)
{
Person newEntity = new Person();
if (reader != null && !reader.IsClosed)
{
if (reader["PersonId"] != DBNull.Value) newEntity.PersonId = (int)reader["PersonId"];
if (reader["IdCard"] != DBNull.Value) newEntity.IdCard = (string)reader["IdCard"];
if (reader["RealName"] != DBNull.Value) newEntity.RealName = (string)reader["RealName"];
if (reader["Gender"] != DBNull.Value) newEntity.Gender = (bool)reader["Gender"];
if (reader["Address"] != DBNull.Value) newEntity.Address = (string)reader["Address"];
}
return newEntity;
} /// <summary>
/// GetEntity
/// </summary>
/// <param name="personId"></param>
/// <returns></returns>
public static Person GetEntity(int personId)
{
string sql = @"select * from ""Person"" where ""PersonId""=@PersonId";
NpgsqlParameter[] cmdParams = {
new NpgsqlParameter("@PersonId", NpgsqlDbType.Integer) { Value = personId },
};
return PostgresqlBaseDal.ExecuteReader(Reader, sql, cmdParams);
}
Person Dal
