c# mysql 表增加字段_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++;

  • 2
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值