C#断开式连接数据库

首先声明对象:

  private SqlConnection objSqlConnection;     //连接对象
  private SqlDataAdapter objDataAdapter;      //数据适配器对象

1.增加

try
   {
    //打开连接
    objSqlConnection.Open();

    //创建数据集对象
    DataSet objDataSet = new DataSet();
    objDataAdapter = new SqlDataAdapter("Select * from Passenger",objSqlConnection);

    //将数据集填充到适配器
    objDataAdapter.Fill(objDataSet,"Passenger");

    // Create an explicit, reusable INSERT command
    objDataAdapter.InsertCommand = objSqlConnection.CreateCommand();
    objDataAdapter.InsertCommand.CommandText =
     "INSERT INTO Passenger " +
     "(FlightCode,PassportNo,Name,SeatNo,"+
     "Sex,Age) " +
     "VALUES('"+this.txtFlightCode.Text+"','"+this.txtPassport.Text+"'," +
     "'"+this.txtName.Text+"',"+this.txtSeatNo.Text+", '"+this.cboSex.SelectedItem.ToString()+"',"+this.txtAge.Text+")";
              
    //Create a data table
    DataTable objDataTable = new DataTable();
    objDataSet.Tables.Add(objDataTable);

    DataRow objDataRow = objDataSet.Tables[0].NewRow();
        
    objDataRow["FlightCode"]=this.txtFlightCode.Text;
    objDataRow["PassportNo"]=this.txtPassport.Text;
    objDataRow["Name"]=this.txtName.Text;
    objDataRow["SeatNo"]=Convert.ToInt32(this.txtSeatNo.Text);
    objDataRow["Sex"]=this.cboSex.SelectedText;
    objDataRow["Age"]=Convert.ToInt32(this.txtAge.Text);
    objDataSet.Tables[0].Rows.Add(objDataRow);
        
    objDataAdapter.Update(objDataSet,"Passenger");
    Application.DoEvents();
    MessageBox.Show("已插入行");

    this.ClearFields();

   }
   catch(SqlException ex)
   {
    MessageBox.Show(ex.Message);
   }
   catch(Exception ex)
   {
    MessageBox.Show(ex.Message);
   }
   finally
   {
    objSqlConnection.Close();
   }

2.删除

try
   {
    //打开连接

    objSqlConnection.Open();
    DataSet objDataSet = new DataSet();
    objDataAdapter = new SqlDataAdapter("Select * from Passenger",objSqlConnection);
    objDataAdapter.Fill(objDataSet,"Passenger");

    // Create an explicit, reusable DELETE command
    objDataAdapter.DeleteCommand = objSqlConnection.CreateCommand();
    objDataAdapter.DeleteCommand.CommandText = "DELETE from Passenger where FlightCode='"+this.txtFlightCode.Text+"'";
              
    DataTable objDataTable = new DataTable();
    objDataSet.Tables.Add(objDataTable);
    
    foreach(DataRow dr in objDataSet.Tables[0].Rows)
    {
     //Check the values
     if(dr["FlightCode"].Equals(this.txtFlightCode.Text))
     {
      DataRow objDataRow = dr;
      this.txtFlightCode.Text = objDataRow["FlightCode"].ToString();
      this.txtPassport.Text = objDataRow["PassportNo"].ToString();
      this.txtName.Text = objDataRow["Name"].ToString();
      this.txtSeatNo.Text = objDataRow["SeatNo"].ToString();
      this.cboSex.SelectedText = objDataRow["Sex"].ToString();
      this.txtAge.Text = objDataRow["Age"].ToString();

      //Deleting the record from DataSet
      objDataRow.Delete();
      objDataAdapter.Update(objDataSet,"Passenger");
      objDataSet.AcceptChanges();
      MessageBox.Show("已删除记录");
      ClearFields();
      break;
     }
     //If not found, message is displayed.
     else
     {
      MessageBox.Show("未找到记录");
      break;
     }

    }
   }
   catch(SqlException ex)
   {
    MessageBox.Show(ex.Message);
   }
   catch(Exception ex)
   {
    MessageBox.Show(ex.Message);
   }
   finally
   {
    objSqlConnection.Close();
   }

分享到搜狐微博