C#数据库操作实例
最近学习了C#，一个实例程序，对Access数据库的单个表文件进行操作的例子。其中包括了对数据的查询，添加，删除，更新。其中用到的内容总结如下：
（1）程序类划分:User类----数据类，与数据库中的myUser表相对应
ConnectDatabase类-----连接字符串生成，负责生成连接字符串
ManageUser类------负责各项操作事物，包括添加，删除，查询，更新等功能的实现
（2）出于练习的目的，采用了ListView控件显示所有数据。用到了添加，删除等部分的代码。
（3）部分代码
ConnectDatabase类的代码：
public class ConnectDatabase
{
public virtual string GetConnectionString()
{
//Access数据库连接字符串
string connstr="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=..//..//data//db.mdb";
return connstr;
}
}
User类的代码:
/*
* Created by SharpDevelop.
* User: lqbjh
* Date: 2008-8-4
* Time: 7:02
*
* To change this template use Tools | Options | Coding | Edit Standard Headers.
*/using System;
using System.Windows.Forms;
namespace DataManage
{
/// <summary>
/// Description of User.
/// </summary>
public class User
{
#region Field Membersprivate int m_iD;
private string m_name;
private string m_password;
private float m_salary;
private string m_address;
private DateTime m_birthday;
private string m_memo;#endregion
#region Property Members
// [PrimaryKey(PrimaryKeyType.Identity, "ID")]
public virtual int ID
{
get
{
return this.m_iD;
}
set
{
this.m_iD=value;
}
}
// [Property("Name")]
public virtual string Name
{
get
{
return this.m_name;
}
set
{
this.m_name = value;
}
}//[Property("Password")]
public virtual string Password
{
get
{
return this.m_password;
}
set
{
this.m_password = value;
}
}//[Property("Salary")]
public virtual float Salary
{
get
{
return this.m_salary;
}
set
{
this.m_salary = value;
}
}//[Property("Address")]
public virtual string Address
{
get
{
return this.m_address;
}
set
{
this.m_address = value;
}
}//[Property("Birthday")]
public virtual DateTime Birthday
{
get
{
return this.m_birthday;
}
set
{
if(value>=Convert.ToDateTime("1900-1-1")&&value.Year<=DateTime.Now.Year)
this.m_birthday = value;
else
MessageBox.Show("生日设置错误，超出范围!");
}
}//[Property("Memo")]
public virtual string Memo
{
get
{
return this.m_memo;
}
set
{
this.m_memo = value;
}
}
#endregion
public User()
{
}
public User(int id,string name,string password,float salary,string address,DateTime birthday,string memo)
{
this.m_iD=id;
this.m_name=name;
this.m_password=password;
this.m_salary=salary;
this.m_address=address;
this.m_birthday=birthday;
this.m_memo=memo;
}
}
}
MangeUser类的代码:
/*
* Created by SharpDevelop.
* User: lqbjh
* Date: 2008-8-4
* Time: 7:14
*
* To change this template use Tools | Options | Coding | Edit Standard Headers.
*/using System;
using System.Data;
using System.Data.OleDb;
using System.Collections;
namespace DataManage
{
/// <summary>
/// Description of ManageUser.
/// </summary>
public class ManageUser
{
public ManageUser()
{
}
//取得连接
public OleDbConnection getConn()
{
ConnectDatabase connstr=new ConnectDatabase();
string connStr=connstr.GetConnectionString();
OleDbConnection oledb=new OleDbConnection(connStr);
return oledb;
}
//依据姓名获得用户信息
public User getUserFromName(string Searchname)
{
User tempUser=new User();
try
{
OleDbConnection oleconn=getConn();//数据库连接
string strSel="select * from MyUser where Name='"+ Searchname+"'";//查询语句
OleDbCommand myCommand=new OleDbCommand(strSel,oleconn);//查询命令
oleconn.Open();//打开数据库连接
OleDbDataReader reader;
reader=myCommand.ExecuteReader();//执行查询命令，返回记录集
if(reader.Read())
{
tempUser.ID=(int)reader["ID"];
tempUser.Name=reader["Name"].ToString();
tempUser.Salary=(float)reader["Salary"];
tempUser.Password=reader["Password"].ToString();
tempUser.Memo=reader["Memo"].ToString();
tempUser.Birthday=(DateTime)reader["Birthday"];
tempUser.Address=reader["Address"].ToString();
}
else
{
throw new Exception("没有记录");
}
reader.Close();//关闭记录集
oleconn.Close();//关闭连接}
catch(Exception e)
{
throw new Exception("打开数据库出错"+e.Message);
}
return tempUser;
}
//获取所有用户的信息
public ArrayList getAllUser()
{
ArrayList arr=new ArrayList();
try
{
OleDbConnection oleconn=getConn();//数据库连接
string strSel="select * from MyUser";//查询语句
OleDbCommand myCommand=new OleDbCommand(strSel,oleconn);//查询命令
oleconn.Open();//打开数据库连接
OleDbDataReader reader;
reader=myCommand.ExecuteReader();//执行查询命令，返回记录集
while(reader.Read())
{
User tempUser=new User();
tempUser.ID=(int)reader["ID"];
tempUser.Name=reader["Name"].ToString();
tempUser.Salary=(float)reader["Salary"];
tempUser.Password=reader["Password"].ToString();
tempUser.Memo=reader["Memo"].ToString();
tempUser.Birthday=(DateTime)reader["Birthday"];
tempUser.Address=reader["Address"].ToString();
arr.Add(tempUser);
}
reader.Close();//关闭记录集
oleconn.Close();//关闭连接}
catch(Exception e)
{
throw new Exception("打开数据库出错"+e.Message);
}
return arr;
}
public void InsertUser(User insertuser)
{
try
{
OleDbConnection oleconn=getConn();//数据库连接
oleconn.Open();//打开数据库连接
string strSel="insert into [MyUser]([Name],[Password],[Salary],[Address],[Birthday],[Memo])"; //插入语句
strSel+=" values('"+insertuser.Name+"','"+insertuser.Password+"',"+insertuser.Salary.ToString();
strSel+=",'"+insertuser.Address+"',#"+insertuser.Birthday.ToString()+"#,'"+insertuser.Memo+"')";
OleDbCommand myCommand=new OleDbCommand(strSel,oleconn);//查询命令
myCommand.ExecuteNonQuery();
oleconn.Close();//关闭连接}
catch(Exception e)
{
throw new Exception("打开数据库出错"+e.Message);
}
}
public void DeleteUser(int m_id)
{
try
{
OleDbConnection oleconn=getConn();
oleconn.Open();
string strSel="Delete From [Myuser] where ID="+m_id.ToString();
OleDbCommand myCommand=new OleDbCommand(strSel,oleconn);
myCommand.ExecuteNonQuery();
oleconn.Close();
}
catch(Exception e)
{
throw new Exception("删除记录出错"+e.Message);
}
}
public string GetInsertSQL(User insertuser)
{
string strSel="insert into [MyUser]([Name],[Password],[Salary],[Address],[Birthday],[Memo])"; //插入语句
strSel+=" values('"+insertuser.Name+"','"+insertuser.Password+"',"+insertuser.Salary.ToString();
strSel+=",'"+insertuser.Address+"',#"+insertuser.Birthday.ToString()+"#,'"+insertuser.Memo+"')";
return strSel;
}
public string GetDelUserSQL(int m_id)
{
string strSel="Delete From [Myuser] where ID="+m_id.ToString();
return strSel;
}
public string GetUserFromNameSQL(string Name)
{
string strSel="select * from MyUser where Name='"+ Name+"'";//查询语句
return strSel;
}
public ArrayList exeSelect(string SQLSel)
{
ArrayList arr=new ArrayList();
try
{
OleDbConnection oleconn=getConn();//数据库连接
string strSel="";
if(SQLSel=="")
strSel="select * from MyUser";//查询语句
else
strSel=SQLSel;
OleDbCommand myCommand=new OleDbCommand(strSel,oleconn);//查询命令
oleconn.Open();//打开数据库连接
OleDbDataReader reader;
reader=myCommand.ExecuteReader();//执行查询命令，返回记录集
while(reader.Read())
{
User tempUser=new User();
tempUser.ID=(int)reader["ID"];
tempUser.Name=reader["Name"].ToString();
tempUser.Salary=(float)reader["Salary"];
tempUser.Password=reader["Password"].ToString();
tempUser.Memo=reader["Memo"].ToString();
tempUser.Birthday=(DateTime)reader["Birthday"];
tempUser.Address=reader["Address"].ToString();
arr.Add(tempUser);
}
reader.Close();//关闭记录集
oleconn.Close();//关闭连接}
catch(Exception e)
{
throw new Exception("打开数据库出错"+e.Message);
}
return arr;
}
public void exeNoQuery(string strSQL)
{
string strSel;
if(strSQL!="")
{
strSel=strSQL;
}
else
{
return;
}
try
{
OleDbConnection oleconn=getConn();//数据库连接
oleconn.Open();//打开数据库连接
// string strSel="insert into [MyUser]([Name],[Password],[Salary],[Address],[Birthday],[Memo])"; //插入语句
// strSel+=" values('"+insertuser.Name+"','"+insertuser.Password+"',"+insertuser.Salary.ToString();
// strSel+=",'"+insertuser.Address+"',#"+insertuser.Birthday.ToString()+"#,'"+insertuser.Memo+"')";
OleDbCommand myCommand=new OleDbCommand(strSel,oleconn);//查询命令
myCommand.ExecuteNonQuery();
oleconn.Close();//关闭连接}
catch(Exception e)
{
throw new Exception("打开数据库出错"+e.Message);
}
}
}
}
