用PostGreSQL實現三層(複習)

標籤：datagridview   style   blog   http   io   ar   os   sp   for   

modal DAL，BLL都是類庫的形式

最終結果如下：

資料庫代碼：

-- Table: student-- DROP TABLE student;CREATE TABLE student(  name text NOT NULL,  "number" integer NOT NULL,  telephone text,  CONSTRAINT "primary key" PRIMARY KEY (name))

插入

INSERT INTO Student values(‘老大‘,20,‘12121212‘)

 

一、先建立modal

using System;using System.Collections.Generic;using System.Linq;using System.Text;namespace StudentModal{    public class studentModal    {        public string Name { get; set; }        public int Number { get; set; }        public string TelePhone { get; set; }    }}

二、sqlhelper(問題：我把Server=127.0.0.1;Port=5432;User Id=postgres;Password=123456;Database=STUDENT;卸載app.config裡面，卻不能像mssql一樣讀取到)

using Npgsql;

using System;using System.Collections.Generic;using System.Configuration;using System.Data;using System.Linq;using System.Text;using Mono.Security;namespace StudentModal{        public class studentHelper    {        //private static readonly string conStr = ConfigurationManager.ConnectionStrings["conSQL"].ToString();        private static readonly string conStr = "Server=127.0.0.1;Port=5432;User Id=postgres;Password=123456;Database=STUDENT;";        private List<studentModal> studentList = new List<studentModal>();        //private string sql = "select * from Student";        /// <summary>        /// 得到所有資料----modal        /// </summary>        /// <param name="sql">sql語句</param>        /// <param name="parameters">參數</param>        /// <returns>模型</returns>        public List<studentModal> getAllStudentInfo(string sql,params NpgsqlParameter[] parameters)        {            using(NpgsqlConnection con=new NpgsqlConnection(conStr))            {                con.Open();                using (NpgsqlCommand cmd =new NpgsqlCommand())                {                    cmd.Connection = con;                    cmd.CommandText = sql;                    cmd.Parameters.AddRange(parameters);                    NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(cmd);                    DataSet dataSet = new DataSet();                    adapter.Fill(dataSet);                    //從dataTable中讀取資料形成modal                    DataTable dataTable=dataSet.Tables[0];                    int tableRow = dataTable.Rows.Count;                    for (int i = 0; i < tableRow; i++)                    {                        studentModal student = new studentModal();                        student.Name = dataTable.Rows[i]["Name"].ToString();                        student.Number =Convert.ToInt32( dataTable.Rows[i]["Number"]);//需要處理為int                        student.TelePhone = dataTable.Rows[i]["TelePhone"].ToString();                        studentList.Add(student);                    }                    return studentList;                }            }        }        ////轉換為object或者為空白        //private object FromDBValue(this object obj)        //{        //    return obj == DBNull.Value ? null : obj;        //}        ///// <summary>        ///// 轉換為資料庫中的null值        ///// </summary>        ///// <param name="obj"></param>        ///// <returns></returns>        //private object ToDBValue(this object obj)        //{        //    return obj == null ? DBNull.Value : obj;        //}    }}

三、DAL

using System;using System.Collections.Generic;using System.Linq;using System.Text;using Mono.Security;using StudentModal;namespace DAL{    public class GetStudentInfo    {        /// <summary>        /// 構建sql語句，然後得到資料        /// </summary>        string sql = "select * from ";        public List<studentModal> GetAllStudentInfoDAL(string dataTable)        {            StudentModal.studentHelper studentHelper = new studentHelper();            return studentHelper.getAllStudentInfo(sql+dataTable);        }    }}

四、BLL

using DAL;using StudentModal;using System;using System.Collections.Generic;using System.Linq;using System.Text;namespace BLL{    public class StudentBLL    {        private string dataTable = "Student";        /// <summary>        /// 從DAL中得到所需資料，供UI調用        /// </summary>        /// <returns></returns>        public List<studentModal> GetStudentListBLL()        {            DAL.GetStudentInfo studentInfo = new GetStudentInfo();            return studentInfo.GetAllStudentInfoDAL(dataTable);        }            }}

五、UI

        private void button1_Click(object sender, EventArgs e)        {            List<studentModal> studentListBLL = new List<studentModal>();            BLL.StudentBLL studentBLL = new BLL.StudentBLL();            studentListBLL= studentBLL.GetStudentListBLL();            dataGridView1.Rows.Add(studentListBLL.Count);            for (int j = 0; j < studentListBLL.Count; j++)            {                studentModal studentModal = studentListBLL[j];                dataGridView1.Rows[j].Cells[0].Value = studentModal.Name;                dataGridView1.Rows[j].Cells[1].Value = studentModal.Number;                dataGridView1.Rows[j].Cells[2].Value = studentModal.TelePhone;                //dataGridView1.Rows.Add(1);            }        }

用PostGreSQL實現三層(複習)