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

}
}
}

  • 1
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值