C#数据库操作实例

OStack程序员社区-中国程序员成长平台 › 门户 › 编程› C++›C++教程

原作者: [db:作者] 来自: [db:来源] 收藏邀请

最近学习了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 Members

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