1.增、刪、改通用方法 /// <summary> /// 增、刪、改通用方法 /// </summary> /// <param name="commandText">Command對象的CommandText屬性(sql語句或存儲過程名稱)</param> /// <param name="co ...
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(); } } }View Code
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; }View Code
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; }View Code
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(); } } }View Code
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; }View Code
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; } } } }View Code
7.PSqlCommandModel
/// <summary> /// CommandModel /// </summary> public struct PSqlCommandModel { /// <summary> /// CommandText /// </summary> public string CommandText { set; get; } /// <summary> /// CommandParameters /// </summary> public NpgsqlParameter[] CommandParameters { set; get; } }View Code
