C#数据库操作——新建表、查询、删除、添加、更新

数据库名：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++;

支持原创，感谢作者http://blog.sina.com.cn/s/blog_4b1260cb0100kjfj.html