Access数据库操作 建立表和字段,注意!是表,不是数据库,数据库你可要自己先建立好(空的就可以了)!!! /// <summary> /// 打开数据库,建立新的表和字段 /// </summary> /// <param name="spath">数据库全路径</param> /// <param name="dataname">表名</param> /// <param name="items">字段数组</param> private void newdatatable(string spath, string dataname, string[] items) { try { //连接到一个数据库 string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + spath; OleDbConnection myConn = new OleDbConnection(strCon); myConn.Open(); string strnew = " CREATE TABLE " + dataname + "( " + items[0] + " TEXT(50) CONSTRAINT PK_tblCustomers PRIMARY KEY , "; for (int i = 1; i < items.Length - 1; i++) { strnew += items[i] + " TEXT(50) , "; } strnew += items[items.Length - 1] + " TEXT(50) )"; OleDbCommand myCommand = new OleDbCommand(strnew, myConn); myCommand.ExecuteNonQuery(); myConn.Close(); } catch (Exception ed) { MessageBox.Show("新建表错误信息： " + ed.ToString(), "错误！", MessageBoxButtons.OK, MessageBoxIcon.Error); } } 删除数据库中的表/// <summary> /// 删除数据库中的表 /// </summary> /// <param name="spath">数据库全名</param> /// <param name="dataname">表名</param> private void deletetable(string spath, string dataname) { try { //连接到一个数据库 string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + spath; OleDbConnection myConn = new OleDbConnection(strCon); myConn.Open(); string strnew = " DROP TABLE " + dataname; OleDbCommand myCommand = new OleDbCommand(strnew, myConn); myCommand.ExecuteNonQuery(); myConn.Close(); } catch (Exception ed) { //MessageBox.Show("删除表错误信息： " + ed.ToString(), "错误！", MessageBoxButtons.OK, MessageBoxIcon.Error); } } 读入所有记录,如果要把dataset中的数据显示出来,把datagirdview的数据源绑定到dataset.tables[dataname]就ok了 /// <summary> /// 打开指定的access数据库,读入所有记录,填充到DataSet中 /// </summary> /// <param name="spath">access数据库名</param> /// <param name="dataname">access数据库中的表名</param> public bool readdata(string spath, string dataname) { try { //创建一个 OleDbConnection对象 string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + spath; OleDbConnection myConn = new OleDbConnection(strCon); // string strCom = " SELECT * FROM " + dataname + " ORDER BY id"; string strCom = " SELECT * FROM " + dataname ; //创建一个 DataSet对象 myConn.Open(); OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn); myCommand.Fill(this.dataSet1, dataname); myConn.Close(); return true; } catch (Exception e) { // MessageBox.Show("连接数据库发生错误：" + e.ToString(), "错误！", MessageBoxButtons.OK, MessageBoxIcon.Error); return false; } } 新增记录/// <summary> /// 新增记录 /// </summary> /// <param name="spath">数据库全名</param> /// <param name="dataname">表名</param> /// <param name="captions">字段名</param> /// <param name="items">添加的纪录内容</param> public void newdata(string spath, string dataname, string[] captions, object[] items) { try { //连接到一个数据库 string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + spath; OleDbConnection myConn = new OleDbConnection(strCon); myConn.Open(); string strInsert; int tt = captions.Length; int sign = -1;//记录日期字段所在索引号,用来格式化日期格式(只要日期,不要时间) strInsert = " INSERT INTO " + dataname + " ( " + captions[0] + " , "; for (int i = 1; i < tt - 1; i++) { if (captions[i].Contains("日期")) { sign = i; } strInsert += captions[i] + " , "; } strInsert += captions[tt - 1] + " ) VALUES ( ' "; for (int i = 0; i < tt - 1; i++) { if (i == sign) { string[] ss = items[i].ToString().Split(' '); strInsert += ss[0] + " ' , ' "; } else { strInsert += items[i].ToString() + " ' , ' "; } } strInsert += items[tt - 1].ToString() + " ') "; OleDbCommand myCommand = new OleDbCommand(strInsert, myConn); myCommand.ExecuteNonQuery(); myConn.Close(); } catch (Exception ed) { MessageBox.Show("新增记录错误信息： " + ed.ToString(), "错误！", MessageBoxButtons.OK, MessageBoxIcon.Error); } } 搜索access数据库 /// <summary> /// 搜索access数据库 /// </summary> /// <param name="spath">access数据库名</param> /// <param name="dataname">access数据库中的表名</param> /// <param name="key">搜索关键字</param> /// <returns></returns> public bool searchdata(string spath, string dataname, string keyword) { string str = ""; bool yn = false; //创建一个 OleDbConnection对象 string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + spath; OleDbConnection myConn = new OleDbConnection(strCon); string strCom = " SELECT * FROM " + dataname + " WHERE " + keyword; try { myConn.Open(); OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn); myCommand.Fill(this.dataSet1, "search"); yn = true; } catch (Exception e) { MessageBox.Show("发生错误：" + e.ToString(), "错误！", MessageBoxButtons.OK, MessageBoxIcon.Error); } finally { myConn.Close(); } return yn; } 查询条件关键字 名称 实例 =(等于) select * from scott.emp where job=’MANAGER’; select * from scott.emp where sal=1100; != (不等于) select * from scott.emp where job!=’MANAGER’; select * from scott.emp where sal!=1100; ^=(不等于) select * from scott.emp where job^=’MANAGER’; select * from scott.emp where sal^=1100; <>(不等于) select * from scott.emp where job<>’MANAGER’; select * from scott.emp where sal<>1100; <(小于) select * from scott.emp where sal<2000; select * from scott.emp where job<’MANAGER’; >(大于) select * from scott.emp where sal>2000; select * from scott.emp where job>’MANAGER’; <=(小于等于) select * from scott.emp where sal<＝2000; select * from scott.emp where job<＝’MANAGER’; >=(大于等于) select * from scott.emp where sal>=2000; select * from scott.emp where job>=’MANAGER’; in(列表) select * from scott.emp where sal in (2000,1000,3000); select * from scott.emp where job in (’MANAGER’,’CLERK’); not in(不在列表) select * from scott.emp where sal not in (2000,1000,3000); select * from scott.emp where job not in (’MANAGER’,’CLERK’); between(介于之间) select * from scott.emp where sal between 2000 and 3000; select * from scott.emp where job between ’MANAGER’ and ’CLERK’; not between (不介于之间) select * from scott.emp where sal not between 2000 and 3000; select * from scott.emp where job not between ’MANAGER’ and ’CLERK’; like(模式匹配) select * from scott.emp where job like ’M%’; select * from scott.emp where job like ’M__’; not like (模式不匹配) select * from scott.emp where job not like ’M%’; select * from scott.emp where job not like ’M__’; Is null (是否为空) select * from scott.emp where sal is null; select * from scott.emp where job is null; is not null(是否为空) select * from scott.emp where sal is not null; select * from scott.emp where job is not null; like和not like适合字符型字段的查询，%%代表任意长度的字符串，_下划线代表一个任意的字符。like ‘m%%’ 代表m开头的任意长度的字符串，like ‘m__’ 代表m开头的长度为3的字符串。ADO中的通配符是两个"%%",还有上面的关键自都要大写,条件中的值大多要有用单引号括起来.