access数据库的数据模型（CAccess数据库应用）

Access是微软Office办公套件中一种,在上位机开发中经常使用,开发中需要安装Access数据库管理软件(会自带数据引擎),本文用的Access2007数据库引擎(网上搜索AccessDatabaseEngine-x32.exe下载)分32位和64具体根据项目多少位来定,本文需要与上篇文章<C# Winform 数据存储及显示> 一起应用

Access帮助类

public class AdoAccess { public OleDbConnection OledbCon; public OleDbDataAdapter oleAdapter; ~AdoAccess() { } /// <summary> /// 获取一个 OleDbConnection 对象 /// </summary> /// <param name="filePath">路径名称</param> public bool GetConn(string filePath) { try { string connstr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" filePath; oledbCon = new OleDbConnection(connstr); return true; } catch ( Exception e ) { System.Windows.Forms.MessageBox.Show(e.Message); return false; } } /// <summary> /// 打开 /// </summary> /// <returns></returns> public bool Open() { if ( oledbCon != null ) { try { oledbCon.Open(); return true; } catch ( Exception e ) { System.Windows.Forms.MessageBox.Show(e.Message); return false; } } else return false; } /// <summary> /// 关闭 /// </summary> /// <returns></returns> public bool Close() { if ( oledbCon != null ) { try { oledbCon.Close(); oledbCon.Dispose(); return true; } catch ( Exception e ) { System.Windows.Forms.MessageBox.Show(e.Message); return false; } } else return false; } /// <summary> /// 创建新表格，自动创建主键 /// </summary> /// <param name="tbname">表名</param> /// <param name="colname">列名(格式: "[name1] type1,[name2] type2"列名需加‘[]’)</param> /// <param name="Nokey">是否需要设置主键 true:否 false:是</param> public void CreateNewTable(string tbname, string colname,bool Nokey=true) { try { string sql; if(Nokey) sql = "CREATE TABLE " tbname "(" colname ")"; else sql = "CREATE TABLE " tbname "(序号 AUTOINCREMENT," colname ",CONSTRAINT " tbname "_PK PRIMARY KEY(ID))"; OleDbCommand cmd = new OleDbCommand(sql, oledbCon); cmd.ExecuteNonQuery(); } catch (Exception e) { System.Windows.Forms.MessageBox.Show(e.Message); } } /// <summary> /// 删除整个表格的数据 /// </summary> /// <param name="tableName">数据表名</param> public bool DeleteOneTableData(string tableName) { if ( oledbCon.State == ConnectionState.Open ) { try { string commstr1 = "delete from " tableName; OleDbCommand tempComm1 = new OleDbCommand(commstr1 , oledbCon); tempComm1.ExecuteNonQuery(); return true; } catch ( Exception e ) { System.Windows.Forms.MessageBox.Show(e.Message); return false; } } else return false; } /// <summary> /// 获取数据库所有表格名字 /// </summary> /// <returns></returns> public BindingList<string> GetAllTbName() { BindingList<string> tbname = new BindingList<string>(); try { DataTable dt = oledbCon.GetSchema("Tables"); foreach (DataRow row in dt.Rows) { string tbtype = (string)row["TABLE_TYPE"]; if (tbtype.Contains("TABLE")) { tbname.Add(row["TABLE_NAME"].ToString()); } } return tbname; } catch (Exception e) { System.Windows.Forms.MessageBox.Show(e.Message); return tbname; } } public void DeleteOneItem(string IDno , string tableName) { if ( oledbCon.State == ConnectionState.Open ) { try { string commstr1 = "delete from " tableName " where 用户名='" IDno.ToString() "'"; OleDbCommand tempComm1 = new OleDbCommand(commstr1 , oledbCon); tempComm1.ExecuteNonQuery(); } catch ( Exception e ) { System.Windows.Forms.MessageBox.Show(e.Message); } } } /// <summary> /// 增加一条记录 /// </summary> /// <param name="tableName">表名</param> /// <param name="columnName">列名 (name1,name2,name3...)</param> /// <param name="itemInfo">信息 (value1,value2,value3...)</param> /// <returns></returns> public bool InsertOneItem(string tableName , string columnName , string itemInfo) { if ( oledbCon.State == ConnectionState.Open ) { try { string commstr1 = "insert into " tableName "(" columnName ")" "values(" itemInfo ")"; OleDbCommand tempComm1 = new OleDbCommand(commstr1 , oledbCon); tempComm1.ExecuteNonQuery(); return true; } catch ( Exception e ) { System.Windows.Forms.MessageBox.Show(e.Message); return false; } } else return false; } /// <summary> /// 更新某一个行数据 /// </summary> /// <param name="tableName">表名</param> /// <param name="comd">数据sql语句</param> public void UpdateOneItem(string tableName,string comd) { if ( oledbCon.State == ConnectionState.Open ) { try { string commstr1 = "update " tableName " " comd; OleDbCommand tempComm1 = new OleDbCommand(commstr1 , oledbCon); tempComm1.ExecuteNonQuery(); } catch ( Exception e ) { System.Windows.Forms.MessageBox.Show(e.Message); } } } /// <summary> /// 更新某一个行数据 /// </summary> /// <param name="tableName">表名</param> /// <param name="comd">数据sql语句</param> public void UpdateOneItem(string sql) { if (oledbCon.State == ConnectionState.Open) { try { OleDbCommand tempComm1 = new OleDbCommand(sql, oledbCon); tempComm1.ExecuteNonQuery(); } catch (Exception e) { System.Windows.Forms.MessageBox.Show(e.Message); } } } /// <summary> /// 读取整个表格的信息 /// </summary> /// <param name="tableName"></param> /// <returns></returns> public OleDbDataReader OleReadAllData(string tableName) { try { string sql = "select *" " from " tableName; OleDbCommand tempComm1 = new OleDbCommand(sql , oledbCon); OleDbDataReader reader = tempComm1.ExecuteReader(); return reader; } catch(Exception e) { System.Windows.Forms.MessageBox.Show(e.Message); return null; } } /// <summary> /// 根据sql语句查询信息 /// </summary> /// <param name="sql">完整的sql查询语句</param> /// <returns></returns> public OleDbDataReader OleReadSomeData(string sql) { try { OleDbCommand tempComm1 = new OleDbCommand(sql , oledbCon); OleDbDataReader reader = tempComm1.ExecuteReader(); return reader; } catch ( Exception e ) { System.Windows.Forms.MessageBox.Show(e.Message); return null; } } /// <summary> /// 获取指定的表 /// </summary> /// <param name="tableName">表名</param> /// <returns></returns> public DataSet GetDataSet(string tableName) { try { string sql = "select * from " tableName " order by 序号 ASC"; DataSet tempSet = new DataSet(); oleAdapter = new OleDbDataAdapter(sql , oledbCon); oleAdapter.Fill(tempSet , "table0"); return tempSet; } catch ( Exception e ) { System.Windows.Forms.MessageBox.Show(e.Message); return null; } } /// <summary> /// 获取指定的表 /// </summary> /// <param name="tableName">表名</param> /// <param name="colname">列名</param> /// <returns></returns> public DataSet GetDataSet(string tableName, string colname) { try { string sql = "select * from " tableName " order by " colname " DESC"; DataSet tempSet = new DataSet(); oleAdapter = new OleDbDataAdapter(sql, oledbCon); oleAdapter.Fill(tempSet, "table0"); return tempSet; } catch (Exception e) { System.Windows.Forms.MessageBox.Show(e.Message); return null; } } }

单个表加载保存(可以扩展为多张表每个表)

/// <summary> /// 加载点位参数 /// </summary> public void LoadPointList() { OleDbDataReader oleread = null; AdoAccess ado = new AdoAccess(); ado.GetConn(@"D:\Data\pointfile\point.accdb"); ado.Open(); try { #region 贴装点位 oleread = ado.OleReadAllData("PastePoint"); if(oleread!=null) { PastePointList.Clear(); while(oleread.Read()) { MatPoint TempPos = new MatPoint(); TempPos.PointName = oleread.GetString(oleread.GetOrdinal("序号")); TempPos.PointX = Convert.ToDouble(oleread.GetString(oleread.GetOrdinal("X"))); TempPos.PointY = Convert.ToDouble(oleread.GetString(oleread.GetOrdinal("Y"))); TempPos.PointR = Convert.ToDouble(oleread.GetString(oleread.GetOrdinal("R"))); TempPos.PointZ = Convert.ToDouble(oleread.GetString(oleread.GetOrdinal("Z"))); TempPos.OffsetX = Convert.ToDouble(oleread.GetString(oleread.GetOrdinal("补偿X"))); TempPos.OffsetY = Convert.ToDouble(oleread.GetString(oleread.GetOrdinal("补偿Y"))); TempPos.OffsetR = Convert.ToDouble(oleread.GetString(oleread.GetOrdinal("补偿R"))); TempPos.Ignore = oleread.GetBoolean(oleread.GetOrdinal("忽略")); TempPos.Remark = oleread.GetString(oleread.GetOrdinal("备注")); TempPos.ObjName = "PastePoint"; PastePointList.Add(TempPos); } } #endregion } catch(Exception e) { MessageBox.Show(e.Message); } finally { ado.Close(); } } /// <summary> /// 保存点位参数 /// </summary> public void SavePointList() { AdoAccess ado = new AdoAccess(); ado.GetConn(@"D:\Data\pointfile\point.accdb"); ado.Open(); try { #region 贴装点位 for (int i = 0; i < PastePointList.Count; i ) { string sql = string.Format("set 序号='{0}',X='{1}',Y='{2}',R='{3}',Z='{4}',补偿X='{5}',补偿Y='{6}',补偿R='{7}',忽略={8},备注='{9}' where 序号='{10}'", point[i].PointName, point[i].PointX, point[i].PointY, point[i].PointR,point[i].PointZ,point[i].OffsetX, point[i].OffsetY,point[i].OffsetR,point[i].Ignore.ToString(), point[i].Remark, point[i].PointName); ado.UpdateOneItem("PastePoint", sql); } #endregion } catch(Exception e) { MessageBox.Show(e.Message); } finally { ado.Close(); } }

数据实现效果

access数据库的数据模型（CAccess数据库应用）(1)