c# access数据库操作

在这里呆了两个月，不要说什么都没有学到吧。起码连个数据库，做个增删改查的东西还是可以的。朋友叫我写个连接access的东西，我立马说不用一个小时就可以了。谁知道还是弄了个大半天的。主要是有些操作上的不同。
主要是两张表：病人表（病人id，病人姓名，性别，病人地址，病人电话），病人就诊表（病人id，就诊时间）
1.获得连接，用OleDb

 
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;

namespace PatientManager.DLL
{
    /// <summary>
    /// 创建连接
    /// </summary>
    public class DbConnection
    {
        private OleDbConnection conn = null;
        /// <summary>
        /// 创建连接
        /// </summary>
        /// <returns>返回连接</returns>
        public OleDbConnection GetConnection()
        {
           //access2007的数据库连接字串。跟2003的是有区别的。
            String strconn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + "../../../db/patient.accdb;" + "User ID=Admin;Password=;";
            conn = new OleDbConnection(strconn);
            return conn;
        }
    }
}

2.实体

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace PatientManager.DLL
{
    /// <summary>
    /// 病人信息
    /// </summary>
    public class Patient
    {
        private int patientId;

        public int PatientId
        {
            get { return patientId; }
            set { patientId = value; }
        }
        private string patientName;

        public string PatientName
        {
            get { return patientName; }
            set { patientName = value; }
        }
        private Boolean gender;

        public Boolean Gender
        {
            get { return gender; }
            set { gender = value; }
        }
        private string address;

        public string Address
        {
            get { return address; }
            set { address = value; }
        }
        private string phone;

        public string Phone
        {
            get { return phone; }
            set { phone = value; }
        }
        private DateTime times;

        public DateTime Times
        {
            get { return times; }
            set { times = value; }
        }
    }
}

3.操作数据库

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;

namespace PatientManager.DLL
{
    /// <summary>
    /// 数据库操作
    /// </summary>
    public class PatientProvider
    {
        private DbConnection db = new DbConnection();
        /// <summary>
        /// 根据id查找
        /// </summary>
        /// <param name="id">病人id</param>
        /// <returns>病人信息</returns>
        public Patient SearchById(int id)
        {
            Patient p = new Patient();
            OleDbConnection conn = db.GetConnection();
            string sql = "select * from Patient,TreamentTable where PatientId = @id";
            OleDbCommand command = new OleDbCommand(sql,conn);
            command.Parameters.Add("@id",OleDbType.BigInt).Value = id;
            conn.Open();
            using (OleDbDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    p.PatientId = Convert.ToInt32(reader["PatientId"]);
                    p.PatientName = reader["PatientName"].ToString();
                    p.Gender = Convert.ToBoolean(reader["Gender"]);
                    p.Address = reader["Address"].ToString();
                    p.Phone = reader["Phone"].ToString();
                    p.Times = DateTime.Parse(reader["Times"].ToString());
                }
            }
            conn.Close();
            return p;
        }

        /// <summary>
        /// 增加病人,采用事务处理，先增加病人表，再增加就诊表。
        /// </summary>
        /// <param name="p">病人信息</param>
        public void AddPatient(Patient p)
        {
            //创建连接 
            OleDbConnection conn = new OleDbConnection();
            conn = new DbConnection().GetConnection();
            //打开连接
            conn.Open();
            //创建事务
            OleDbTransaction tra = conn.BeginTransaction();
            string sql = "insert into Patient(PatientName,Gender,Address,Phone) values (@PatientName,@Gender,@Address,@Phone)";
            //dbCommand
            OleDbCommand command = new OleDbCommand(sql, conn);
            //添加参数
            command.Parameters.Add("@PatientName", OleDbType.VarChar, 20).Value = p.PatientName;
            command.Parameters.Add("@Gender", OleDbType.Boolean).Value = p.Gender;
            command.Parameters.Add("@Address", OleDbType.VarChar, 50).Value = p.Address;
            command.Parameters.Add("@Phone", OleDbType.VarChar, 20).Value = p.Phone;
            //事务
            command.Transaction = tra;
            //执行语句，要提交事务成功才执行
            command.ExecuteNonQuery();
            sql = "select Max(PatientId) as maxid from Patient";
            command = new OleDbCommand(sql, conn);
            command.Transaction = tra;
            int id = 0;
            using (OleDbDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    id = Convert.ToInt32(reader["maxid"]);
                }
            }
            sql = "insert into TreamentTable(id,Times) values (@id,@Times)";
            command = new OleDbCommand(sql, conn);
            command.Parameters.Add("@id", OleDbType.BigInt).Value = id;
            command.Parameters.Add("@Times", OleDbType.Date).Value = p.Times;
            command.Transaction = tra;
            command.ExecuteNonQuery();
            //提交事务
            tra.Commit();
            //关闭连接
            conn.Close();
        }

        /// <summary>
        /// 查找所有病人信息
        /// </summary>
        /// <returns>病人信息结果集</returns>
        public List<Patient> FindAll()
        {
            List<Patient> list = new List<Patient>();
            OleDbConnection conn = db.GetConnection();
            string sql = "SELECT * FROM Patient INNER JOIN TreamentTable ON Patient.PatientId = TreamentTable.id";
            OleDbCommand command = new OleDbCommand(sql, conn);
            conn.Open();
            using (OleDbDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    Patient p = new Patient();
                    p.PatientId = Convert.ToInt32(reader["PatientId"]);
                    p.PatientName = reader["PatientName"].ToString();
                    p.Gender = Convert.ToBoolean(reader["Gender"]);
                    p.Address = reader["Address"].ToString();
                    p.Phone = reader["Phone"].ToString();
                    p.Times = DateTime.Parse(reader["Times"].ToString());
                    list.Add(p);
                }
            }
            conn.Close();
            return list;
        }

        /// <summary>
        /// 删除病人信息,采用事务处理
        /// </summary>
        /// <param name="id">病人id</param>
        public void Delete(int id)
        {
            OleDbConnection conn = db.GetConnection();
            conn.Open();
            OleDbTransaction tra = conn.BeginTransaction();
            string sql = "delete from TreamentTable where id = @id";
            OleDbCommand command = new OleDbCommand(sql, conn);
            command.Parameters.Add("@id", OleDbType.BigInt).Value = id;
            command.Transaction = tra;
            command.ExecuteNonQuery();
            sql = "delete from Patient where PatientId = @id";
            command = new OleDbCommand(sql, conn);
            command.Parameters.Add("@id", OleDbType.BigInt).Value = id;
            command.Transaction = tra;
            command.ExecuteNonQuery();
            tra.Commit();
            conn.Close();
        }
        /// <summary>
        /// 更新病人信息，注意，主键id放在最后才可以更新。。。奇怪
        /// </summary>
        /// <param name="p">要修改的病人信息</param>
        public void Update(Patient p)
        {
            OleDbConnection conn = db.GetConnection();
            conn.Open();
            string sql = "update Patient set PatientName = @PatientName,Gender = @Gender,Address = @Address,Phone=@Phone where PatientId = @PatientId";
            OleDbCommand command = new OleDbCommand(sql, conn);
            command.Parameters.Add("@PatientName", OleDbType.VarChar, 20).Value = p.PatientName;
            command.Parameters.Add("@Gender", OleDbType.Boolean).Value = p.Gender;
            command.Parameters.Add("@Address", OleDbType.VarChar, 50).Value = p.Address;
            command.Parameters.Add("@Phone", OleDbType.VarChar, 20).Value = p.Phone;
            command.Parameters.Add("@PatientId", OleDbType.BigInt).Value = p.PatientId;
            command.ExecuteNonQuery();
            conn.Close();

        }
    }
}