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

            }

        }