数据库名:data.mdb; 内含一个表:table1; table1有三列:日期、地点、人物,日期设为主键;//声明与定义
using System.Data.OleDb
private string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"data.mdb\"";
private string strTableName;
private OleDbCommand myCommand = new OleDbCommand();
private OleDbConnection myConnection = new OleDbConnection();
private OleDbDataAdapter myAdapter = new OleDbDataAdapter();
private DataSet myDataSet = new DataSet();
数据库记录操作的Sql语句/
查询:string strSearch = "SELECT * FROM table1 WHERE 日期>=#" + this.dmBegin.ToString() + "# and 日期<=#" + this.dmEnd.ToString() + "#";
string strSearch = "SELECT * FROM table1 WHERE 日期LIKE '%" + dm.ToString() + "%' OR 地点 LIKE '%" + this.textBox1.Text + "'";
删除:string strDel = "DELETE * FROM table1 WHERE 日期= '" +dm.ToString() + "'";
添加:string strInst = "INSERT INTO table1(日期,地点,人物) VALUES ('" + this.textBox1.Text + "','" + this.textBox2.Text + "','" + this.textBox3.Text + "')";
更新:string strUpdate = "UPDATE table1 SET" + " 地点='" + this.textBox1.Text + "',人物='" + this.textBox6.Text + "'WHERE 日期='" + this.textBox5.Text + "'";
// 数据库操作代码//
///获得数据库中的所有表的名称
this.myConnection.ConnectionString = this.strCon; this.myConnection.Open();
DataTable dt = myConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[]
{
null, null, null, "TABLE"
});
foreach (DataRow dr in dt.Rows) {
this.strTableName+=(String)dr["TABLE_NAME"]+"\n";
}
this.myConnection.Close();
//新建一个表,并增加记录,新表的名字为table2,第一列“日期”,date型数据,后面几列均为文本型
DataTable dtCurrent = new DataTable();
int nr = dtCurrent.Rows.Count;
int nc = dtCurrent.Columns.Count;
string StrCmd = "CREATE TABLE table2(" + dtCurrent.Columns[0].ColumnName.ToString() + " date,";
string strCol = dtCurrent.Columns[0].ColumnName.ToString() + ",";
for (int i = 1; i < nc - 1; i++) {
StrCmd = StrCmd + dtCurrent.Columns[i].ColumnName.ToString() + " nvarchar(200),";
strCol = strCol + dtCurrent.Columns[i].ColumnName.ToString() + ","; }
StrCmd = StrCmd + dtCurrent.Columns[nc - 1].ColumnName.ToString() + " nvarchar(200))";
strCol = strCol + dtCurrent.Columns[nc - 1].ColumnName.ToString();
this.myConnection.ConnectionString =this.strCon;
this.myCommand.Connection = this.myConnection;
this.myCommand.CommandText = StrCmd;
this.myConnection.Open();
this.myCommand.ExecuteNonQuery();
for (int i = 0; i < nr - 1; i++) {
string strInsert = " INSERT INTO table2(" + strCol + ") VALUES ( '";
strInsert += Convert.ToDateTime(dtCurrent.Rows[i][0].ToString()) + "','";
for (int j = 1; j < nc - 1; j++) {
strInsert += dtCurrent.Rows[i][j].ToString() + "','";
}
strInsert += dtCurrent.Rows[i][nc - 1].ToString() + "')";
this.myCommand.CommandText = strInsert;
this.myCommand.ExecuteNonQuery();
}
this.myConnection.Close();
//查询读取记录
public void dbSearch() {
this.myConnection.Open();
try {
this.myCommand.CommandText = strSearch ;//查询多有用"SELECT * FROM table1";
this.myCommand.Connection = this.myConnection;
this.myAdapter.SelectCommand =this.myCommand;
this.myDataSet.Clear();
this.myAdapter.Fill(this.myDataSet);
this.myConnection.Close();
dataGridViewBook.DataSource = this.myDataSet.Tables[0];
}
catch (Exception ex) {
this.myConnection.Close();
MessageBox.Show(ex.Message);
}
this.myConnection.Close();
}
//新建记录
private void dbInst() {
this.myCommand.CommandText = strInst
this.myAdapter.InsertCommand = this.myCommand;
try {
this.myAdapter.InsertCommand.Connection.Open();
this.myAdapter.InsertCommand.ExecuteNonQuery();
} catch (Exception ex) {
this.myAdapter.InsertCommand.Connection.Close();
MessageBox.Show(ex.Message);
}
this.myAdapter.InsertCommand.Connection.Close();
}
//删除记录
private void dbDel() {
this.myCommand.CommandText = strDel;
this.myAdapter.DeleteCommand = this.myCommand;
this.myAdapter.DeleteCommand.Connection.Open();
try {
this.myAdapter.DeleteCommand.ExecuteNonQuery();
} catch (Exception ex) {
this.myAdapter.DeleteCommand.Connection.Close();
MessageBox.Show(ex.Message);
}
this.myAdapter.DeleteCommand.Connection.Close();
}
//更新记录
private void dbUpdate() {
this.myCommand.CommandText = strUpdate;
this.myAdapte.UpdateCommand = this.myCommand;
try {
this.myAdapter.UpdateCommand.Connection.Open();
this.myAdapter.UpdateCommand.ExecuteNonQuery();
} catch (Exception ex) {
this.myAdapter.UpdateCommand.Connection.Close();
MessageBox.Show(ex.Message);
}
this.myAdapter.UpdateCommand.Connection.Close();
}
//上下移动数据库的记录
private BindingManagerBase Mybind;
this.Mybind = this.BindingContext[this.myDataSet.Tables[0]];
this.Mybind.Position = 0;
Mybind.Position--;
Mybind.Position++;