如何更新SQL Server数据库

最新推荐文章于 2024-09-14 19:55:29 发布

xiashu

最新推荐文章于 2024-09-14 19:55:29 发布

阅读量879

点赞数

分类专栏： ADO.net 文章标签： sql server 数据库 object exception dataset button

本文链接：https://blog.csdn.net/xiashu/article/details/1424862

版权

ADO.net 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

private void Form1_Load(object sender, System.EventArgs e)
  {//显示所有记录
   this.sqlDataAdapter1.Fill(this.dataSet1,"Customers");
   this.dataGrid1.DataSource=this.dataSet1;
   if(!this.dataGrid1.IsExpanded(0))
    this.dataGrid1.Expand(0);
  }

  private void button1_Click(object sender, System.EventArgs e)
  {//查询记录
   try
   {
    this.dataSet1.Clear();
    string StrSQL="SELECT * FROM Customers WHERE ";
    StrSQL+=this.comboBox1.Text+" LIKE '";
    StrSQL+=this.comboBox2.Text+"'";
    if(this.comboBox1.Text=="All")
     StrSQL="SELECT * FROM Customers";
    this.sqlDataAdapter1.SelectCommand.CommandText=StrSQL;
    this.sqlDataAdapter1.SelectCommand.Connection=this.sqlConnection1;
    //打开数据库连接
    this.sqlConnection1.Open();
    //执行SQL命令
    this.sqlDataAdapter1.SelectCommand.ExecuteNonQuery();
    //关闭连接
    this.sqlConnection1.Close();
    //更新数据库
    this.sqlDataAdapter1.Fill(this.dataSet1,"Customers");
    this.dataGrid1.DataSource=this.dataSet1;
   }
   catch(Exception Err)
   {
    MessageBox.Show("查询数据库记录操作失败："+Err.Message,"信息提示",
     MessageBoxButtons.OK,MessageBoxIcon.Information);
    //如果打开了连接，则关闭它
    if(this.sqlConnection1.State==ConnectionState.Open)
    {
     this.sqlConnection1.Close();
    }
   }
  }

  private void Form1_Closed(object sender, System.EventArgs e)
  {//关闭程序
   //如果打开了连接，则关闭它
   if(this.sqlConnection1.State==ConnectionState.Open)
   {
    this.sqlConnection1.Close();
   }
  }

  private void comboBox1_SelectedIndexChanged(object sender, System.EventArgs e)
  {//显示查询值列表项
   this.dataSet2.Clear();
   string StrSQL="SELECT * FROM Customers";
   this.sqlDataAdapter1.SelectCommand.CommandText=StrSQL;
   this.sqlDataAdapter1.SelectCommand.Connection=this.sqlConnection1;
   //打开数据库连接
   this.sqlConnection1.Open();
   //执行SQL命令
   this.sqlDataAdapter1.SelectCommand.ExecuteNonQuery();
   //关闭连接
   this.sqlConnection1.Close();
   //更新数据库
   this.sqlDataAdapter1.Fill(this.dataSet2,"Customers");
   this.comboBox2.DataSource=this.dataSet2;
   if(this.comboBox1.Text=="CustomerID")
   {
    this.comboBox2.DisplayMember="Customers.CustomerID";
    this.comboBox2.ValueMember="Customers.CustomerID";
    return;
   }
   if(this.comboBox1.Text=="CompanyName")
   {
    this.comboBox2.DisplayMember="Customers.CompanyName";
    this.comboBox2.ValueMember="Customers.CompanyName";
    return;
   }
   if(this.comboBox1.Text=="ContactName")
   {
    this.comboBox2.DisplayMember="Customers.ContactName";
    this.comboBox2.ValueMember="Customers.ContactName";
    return;
   }
   if(this.comboBox1.Text=="ContactTitle")
   {
    this.comboBox2.DisplayMember="Customers.ContactTitle";
    this.comboBox2.ValueMember="Customers.ContactTitle";
    return;
   }
   if(this.comboBox1.Text=="Address")
   {
    this.comboBox2.DisplayMember="Customers.Address";
    this.comboBox2.ValueMember="Customers.Address";
    return;
   }
   if(this.comboBox1.Text=="City")
   {
    this.comboBox2.DisplayMember="Customers.City";
    this.comboBox2.ValueMember="Customers.City";
    return;
   }
   if(this.comboBox1.Text=="Region")
   {
    this.comboBox2.DisplayMember="Customers.Region";
    this.comboBox2.ValueMember="Customers.Region";
    return;
   }
   if(this.comboBox1.Text=="PostalCode")
   {
    this.comboBox2.DisplayMember="Customers.PostalCode";
    this.comboBox2.ValueMember="Customers.PostalCode";
    return;
   }
   if(this.comboBox1.Text=="Country")
   {
    this.comboBox2.DisplayMember="Customers.Country";
    this.comboBox2.ValueMember="Customers.Country";
    return;
   }
   if(this.comboBox1.Text=="Phone")
   {
    this.comboBox2.DisplayMember="Customers.Phone";
    this.comboBox2.ValueMember="Customers.Phone";
    return;
   }
   if(this.comboBox1.Text=="Fax")
   {
    this.comboBox2.DisplayMember="Customers.Fax";
    this.comboBox2.ValueMember="Customers.Fax";
    return;
   }
  }

  private void button2_Click(object sender, System.EventArgs e)
  {//更新数据库记录
   try
   {
    DataSet MyDataSet=this.dataSet1.GetChanges();
    if(MyDataSet!=null)
    {
     int Rows=this.sqlDataAdapter1.Update(MyDataSet);
     MessageBox.Show("成功更新数据库记录："+Rows.ToString()+"条！","信息提示",
      MessageBoxButtons.OK,MessageBoxIcon.Information);
     MyDataSet.AcceptChanges();
    }
    else
    {
     MessageBox.Show("数据库记录没有变化","信息提示",
      MessageBoxButtons.OK,MessageBoxIcon.Information);
    }
   }
   catch(Exception Err)
   {
    MessageBox.Show("更新数据库记录操作失败："+Err.Message,"信息提示",
     MessageBoxButtons.OK,MessageBoxIcon.Information);
    //如果打开了连接，则关闭它
    if(this.sqlConnection1.State==ConnectionState.Open)
    {
     this.sqlConnection1.Close();
    }
   }
  }