access操作mysql_Access数据库操作

建立表和字段,注意!是表,不是数据库,数据库你可要自己先建立好(空的就可以了)!!!        ///

/// 打开数据库,建立新的表和字段

///

/// 数据库全路径

/// 表名

/// 字段数组

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

}

}

删除数据库中的表///

/// 删除数据库中的表

///

/// 数据库全名

/// 表名

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了  ///

/// 打开指定的access数据库,读入所有记录,填充到DataSet中

///

/// access数据库名

/// access数据库中的表名

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;

}

}

新增记录///

/// 新增记录

///

/// 数据库全名

/// 表名

/// 字段名

/// 添加的纪录内容

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数据库         ///

/// 搜索access数据库

///

/// access数据库名

/// access数据库中的表名

/// 搜索关键字

///

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

>(大于)

select * from scott.emp where sal>2000;

select * from scott.emp where job>’MANAGER’;

<=(小于等于)

select * from scott.emp where sal

select * from scott.emp where job

>=(大于等于)

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中的通配符是两个"%%",还有上面的关键自都要大写,条件中的值大多要有用单引号括起来.