//取得连接 public OleDbConnection getConn() { ConnectDatabase connstr=new ConnectDatabase(); string connStr=connstr.GetConnectionString(); OleDbConnection oledb=new OleDbConnection(connStr); return oledb; } (1)采用OleDbCommand,OleDbDataReader访问数据库 1.查询 public User getUserFromName(string Searchname) { User tempUser=new User(); try { OleDbConnection oleconn=getConn();//数据库连接 string strSel="select * from MyUser where UserName='"+ Searchname+"'";//查询语句 OleDbCommand myCommand=new OleDbCommand(strSel,oleconn);//查询命令 oleconn.Open();//打开数据库连接 OleDbDataReader reader; reader=myCommand.ExecuteReader();//执行查询命令,返回记录集 if(reader.Read()) { tempUser.ID=(int)reader["UserID"]; tempUser.Name=reader["UserName"].ToString(); tempUser.Salary=(float)reader["UserSalary"]; tempUser.Password=reader["UserPassword"].ToString(); tempUser.Memo=reader["UserMemo"].ToString(); tempUser.Birthday=(DateTime)reader["UserBirthday"]; tempUser.Address=reader["UserAddress"].ToString(); } else { throw new Exception("没有记录"); } reader.Close();//关闭记录集 oleconn.Close();//关闭连接 } catch(Exception e) { throw new Exception("打开数据库出错"+e.Message); } return tempUser; } 2.插入记录 public void InsertUser(User insertuser) { try { OleDbConnection oleconn=getConn();//数据库连接 oleconn.Open();//打开数据库连接 string strSel="insert into [MyUser]([UserName],[UserPassword], [UserSalary],[UserAddress],[UserBirthday],[UserMemo])"; //插入语句 strSel+=" values ('"+insertuser.Name+"','"+insertuser.Password+"',"+insertuser.Salary.ToSt ring(); 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); } } 3.删除记录 public void DeleteUser(int m_id) { try { OleDbConnection oleconn=getConn(); oleconn.Open(); string strSel="Delete From [Myuser] where UserID="+m_id.ToString(); OleDbCommand myCommand=new OleDbCommand(strSel,oleconn); myCommand.ExecuteNonQuery(); oleconn.Close(); } catch(Exception e) { throw new Exception("删除记录出错"+e.Message); } } (2)采用OleDbDataAdapter,OleDbCommandBuilder,DataSet,DataTable,DataRow访 问数据库 添加记录如下 public void InsertUserA(User insertUser) { using(OleDbConnection conn=getConn()) { OleDbDataAdapter adapter = new OleDbDataAdapter(); string queryString="Select * from MyUser order by UserID"; adapter.SelectCommand = new OleDbCommand(queryString, conn); OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter); // builder.QuotePrefix="["; // builder.QuoteSuffix="]"; conn.Open(); DataSet users = new DataSet(); adapter.Fill(users,"MyUser"); DataTable dt=new DataTable(); dt=users.Tables["MyUser"]; DataRow r=dt.NewRow(); r["UserName"]=insertUser.Name; r["UserPassword"]=insertUser.Password; r["UserAddress"]=insertUser.Address; r["UserSalary"]=insertUser.Salary; r["UserBirthday"]=insertUser.Birthday; r["UserMemo"]=insertUser.Memo; dt.Rows.Add(r); adapter.Update(users, "MyUser"); } } 需要注意字段不能和关键字相同,否则会出现Insert into出错的提示。解决办法在 前一篇 (3)采用参数化查询的方式 public class AccessUtil { public AccessUtil() { } private string connString; public string ConnString { get { return connString; } set { connString = value; } } public AccessUtil(string connstr) { this.connString = connstr; } //带参数的插入语句,返回值为id关键字的值,单条插入语句 public int ExecuteInsert(string SQL, OleDbParameter[] parameters) { using(OleDbConnection conn=new OleDbConnection(connString)) { OleDbCommand cmd = new OleDbCommand(SQL, conn); try { conn.Open(); if (parameters!=null) { cmd.Parameters.AddRange(parameters); } cmd.ExecuteNonQuery(); cmd.CommandText = @"Select @@identity"; int value = Int32.Parse(cmd.ExecuteScalar().ToString ()); return value; } catch (System.Exception e) { throw e; } } } //不带参数的插入语句,返回值为关键字的值 public int ExecuteInsert(string SQL) { return ExecuteInsert(SQL, null); } //带参数的插入、删除、更新语句,返回受影响的记录的个数 public int ExecuteNoQuery(string SQL, OleDbParameter[] parameters) { using(OleDbConnection conn=new OleDbConnection(connString)) { conn.Open(); OleDbCommand cmd = new OleDbCommand(SQL, conn); try { if (parameters!=null) { cmd.Parameters.AddRange(parameters); } int rows=cmd.ExecuteNonQuery(); return rows; } catch (System.Exception e) { throw e; } } } //不带参数的插入、删除、更新语句,返回受影响的记录的个数 public int ExecuteNoQuery(string SQL) { return ExecuteNoQuery(SQL, null); } //带参数的查询语句,返回所查询到的记录集 public DataSet ExecuteQuery(string SQL, OleDbParameter[] parameters) { using(OleDbConnection conn=new OleDbConnection(connString)) { DataSet ds = new DataSet(); try { conn.Open(); OleDbDataAdapter da = new OleDbDataAdapter(SQL, conn); if (parameters != null) { da.SelectCommand.Parameters.AddRange(parameters); } da.Fill(ds, "ds"); } catch(System.Exception e) { throw e; } return ds; } } //不带参数的查询,返回所查询到的记录集 public DataSet ExecuteQuery(string SQL) { return ExecuteQuery(SQL, null); } } class ManageUser { //Access数据库工具对象 AccessUtil accessutil = new AccessUtil (ConnectDatabase.GetConnectionString()); public ArrayList GetAllUserArr()//获得User表中的所有记录,存储进 ArrayList。 { string SQL = "select * from MyUser order by ID"; DataSet ds=accessutil.ExecuteQuery(SQL);//返回的临时表的名称 为“ds” /* ArrayList arr = new ArrayList(); for (int i = 0; i < ds.Tables ["ds"].Rows.Count;i++ ) { arr.Add(DataRow2User(ds.Tables["ds"].Rows [i])); }*/ ArrayList arr = DataTable2ArrayList(ds.Tables["ds"]); return arr; } public DataSet GetAllUserDataSet()//存储成DataSet { string SQL = "select * from MyUser order by ID"; DataSet ds = accessutil.ExecuteQuery(SQL); return ds; } private User DataRow2User(DataRow dr)//将数据表中的一条记录转换为 一个User类的实例 { User user = new User(); user.ID = Int32.Parse(dr["ID"].ToString()); user.Name = dr["Name"].ToString(); user.Address = dr["Address"].ToString(); user.Birthday = Convert.ToDateTime(dr ["Birthday"].ToString()); user.Memo = dr["Memo"].ToString(); user.Salary =(float) Convert.ToDouble(dr ["Salary"].ToString()); user.Password = dr["Password"].ToString(); return user; } private ArrayList DataTable2ArrayList(DataTable dt)//将一个表中的 记录转化为ArrayList对象 { ArrayList tempArr = new ArrayList(); DataTableReader dr = new DataTableReader(dt); while(dr.Read()) { User user = new User(); user.ID = Int32.Parse(dr["ID"].ToString()); user.Name = dr["Name"].ToString(); user.Address = dr["Address"].ToString(); user.Birthday = Convert.ToDateTime(dr["Birthday"].ToString()); user.Memo = dr["Memo"].ToString(); user.Salary = (float)Convert.ToDouble(dr["Salary"].ToString ()); user.Password = dr["Password"].ToString(); tempArr.Add(user); } return tempArr; } public DataSet GetUserByName(string name) { String SQL = "Select * from MyUser where Name=?"; OleDbParameter[] parameter = new OleDbParameter[1]; parameter[0] = new OleDbParameter("@Name", OleDbType.VarChar); parameter[0].Value = name; DataSet dt= accessutil.ExecuteQuery(SQL, parameter); return dt; } public int InsertUser(User inUser) { String SQL = "insert into [MyUser]([Name],[Password], [Salary],[Address],[Birthday],[Memo]) values(?,?,?,?,?,?)"; OleDbParameter[] parameters = new OleDbParameter[6]; parameters[0] = new OleDbParameter("@Name", OleDbType.VarChar); parameters[0].Value = inUser.Name; parameters[1] = new OleDbParameter("@Password", OleDbType.VarChar); parameters[1].Value = inUser.Password; parameters[2] = new OleDbParameter("@Salary", OleDbType.Single); parameters[2].Value = inUser.Salary; parameters[3] = new OleDbParameter("@Address", OleDbType.VarChar); parameters[3].Value = inUser.Address; parameters[4] = new OleDbParameter("@Birthday", OleDbType.Date); parameters[4].Value = inUser.Birthday; parameters[5] = new OleDbParameter("@Memo", OleDbType.VarChar); parameters[5].Value = inUser.Memo; return accessutil.ExecuteInsert(SQL, parameters); } public void DelUserById(int id) { String SQL = "DELETE FROM [MyUser] where ID=?"; OleDbParameter[] parameters = new OleDbParameter[1]; parameters[0] = new OleDbParameter("@ID", OleDbType.Integer); parameters[0].Value = id; accessutil.ExecuteNoQuery(SQL, parameters); } public void UpdateUser(User userupdate) { String SQL = "update [MyUser] Set [Name]=?,[Password]=?, [Salary]=?,[Address]=?,[Birthday]=?,[Memo]=? where [ID]=?"; OleDbParameter[] parameters = new OleDbParameter[7]; parameters[0] = new OleDbParameter("@Name", OleDbType.VarChar); parameters[0].Value = userupdate.Name; parameters[1] = new OleDbParameter("@Password", OleDbType.VarChar); parameters[1].Value = userupdate.Password; parameters[2] = new OleDbParameter("@Salary", OleDbType.Single); parameters[2].Value = userupdate.Salary; parameters[3] = new OleDbParameter("@Address", OleDbType.VarChar); parameters[3].Value = userupdate.Address; parameters[4] = new OleDbParameter("@Birthday", OleDbType.Date); parameters[4].Value = userupdate.Birthday; parameters[5] = new OleDbParameter("@Memo", OleDbType.VarChar); parameters[5].Value = userupdate.Memo; parameters[6] = new OleDbParameter("@ID", OleDbType.Integer); parameters[6].Value = userupdate.ID; accessutil.ExecuteNoQuery(SQL, parameters); } }