DataBase和DataSet

Command对象
SelectCommand:用来获取数据源中的记录；
InsertCommand:用来向数据源中新插入一条记录；
UpateCommand:用来更新数据源中的数据；
DeleteCommand:用来删除数据源中的记录；

  private void Page_Load(object sender, System.EventArgs e)
  {
   sqlDataAdapter1.Fill(dsEmployee1);
   DataGrid1.DataBind();
  } 


DataRowVersion
在调用DataRow对象的BeginEdit方法之后，如果更改该值，则Current和Proposed值变得可用。
在调用DataRow对象的CancelEdit方法之后，Proposed值将被删除。
在调用DataRow对象的EndEdit方法之后，Proposed值变成Current值。
在调用DataRow对象的AcceptChanges方法之后，Original值变得与Current值相同。
在调用DataTeble对象的AcceptChanges方法之后，Original值变得与Current值相同。
在调用DataRow对象的RejectChanges之后，Proposed值将被丢弃，版本变成Current。

Fill方法
Fill(DataSet):用数据源返回的行填充指定数据集
Fill(DataTable):
Fill(DataSet,tableName):在指定的数据集中，用数据源返回的行填充称为tableName的数据表
Fill(DataTable,DataReader):用指定的DataReader填充数据表

DataAdpter的常用案例
  private void Page_Load(object sender, System.EventArgs e)
  {
   if(!IsPostBack)
    myBind(0);
  }
  private void myBind(int nIndex)
  {
   string strCon =System.Configuration.ConfigurationSettings.AppSettings["DSN"];
   SqlConnection con = new SqlConnection(strCon);
   //首先绑定ddlClassCode
   SqlDataAdapter da = new SqlDataAdapter("select Distinct classCode from tbClassInfo",con);
   DataSet ds = new DataSet("myDs");
   da.Fill(ds,"Class");
   ddlClassCode.DataTextField = "ClassCode";
   ddlClassCode.DataSource = ds.Tables["Class"].DefaultView;
   ddlClassCode.DataBind();
   ddlClassCode.SelectedIndex = nIndex;
   string strSelectedClass = ddlClassCode.SelectedItem.Text;
   string strSql = "select * from tbStudentInfo where StudentID in (select studentid from tbClassInfo where classcode='"+strSelectedClass+"')";
   da.SelectCommand.CommandText =strSql;
//   //映射
   da.TableMappings.Add("tbStudentInfo","Student");
   da.TableMappings[0].ColumnMappings.Add("StudentID","学生ID");
   da.TableMappings[0].ColumnMappings.Add("StudentName","学生姓名");
   da.TableMappings[0].ColumnMappings.Add("StudentPass","密码");
   da.TableMappings[0].ColumnMappings.Add("Sex","性别");
   da.TableMappings[0].ColumnMappings.Add("BirthDay","生日");
   da.TableMappings[0].ColumnMappings.Add("Email","邮件地址");
   da.TableMappings[0].ColumnMappings.Add("Score","成绩"); 
   da.Fill(ds,"tbStudentInfo");
   dgShow.DataSource = ds.Tables["Student"].DefaultView;
   dgShow.DataBind();
  }


  DataSet ds;
  private void Page_Load(object sender, System.EventArgs e)
  {
   if(!IsPostBack)
   {
    SqlConnection con = new SqlConnection("Server=(local);uid=sa;pwd=12345678;database=pubs");
    con.Open();
    SqlCommand com = new SqlCommand("Select Count(authors.au_fname) from"+
     "(authors INNER JOIN titleauthor ON authors.au_id = titleauthor.au_id)"+
     "INNER JOIN titles ON titleauthor.title_id=titles.title_id",con);
    Session["nMax"] =(int)com.ExecuteScalar();
    Session["nCurr"] =0;
    con.Close();
    SqlDataAdapter da = new SqlDataAdapter("Select authors.au_fname ,authors.au_lname,"
     +"titles.title from"+
     "(authors INNER JOIN titleauthor ON authors.au_id = titleauthor.au_id)"+
     "INNER JOIN titles ON titleauthor.title_id=titles.title_id",con);
    ds = new DataSet("authors");
    da.Fill(ds,0,1,"AuthorAndTitle");
    Fill();
    Session["da"] = da;
   }
  }
  private void Fill()
  {
   tbAuthor.Text = ds.Tables["AuthorAndTitle"].Rows[0]["au_fname"]+ " "
    +ds.Tables["AuthorAndTitle"].Rows[0]["au_lname"];
   tbTitle.Text = ds.Tables["AuthorAndTitle"].Rows[0]["title"].ToString();
  }
  private void btnPrev_Click(object sender, System.EventArgs e)
  {
   int iMax = (int)Session["nMax"];
   int iCurr = (int)Session["nCurr"];
   SqlDataAdapter da = (SqlDataAdapter)Session["da"];
   if(iCurr<=iMax)
   {
    ds = new DataSet("authors");
    iCurr++;
    da.Fill(ds,iCurr,1,"AuthorAndTitle");
    Fill();
   }
   Session["nCurr"] = iCurr;
  }
  private void btnNext_Click(object sender, System.EventArgs e)
  {
   int iMax = (int)Session["nMax"];
   int iCurr = (int)Session["nCurr"];
   SqlDataAdapter da = (SqlDataAdapter)Session["da"];
   if(iCurr>0)
   {
    ds = new DataSet("authors");
    iCurr--;
    da.Fill(ds,iCurr,1,"AuthorAndTitle");
    Fill();
   }
   Session["nCurr"] = iCurr;
  }

Updata方法
Update(DataSet):根据指定的数据集中的数据表更新数据
Update(DateRows):根据指定的数据行数组更新数据源
Update(DateTable):根据指定的数据表更数据源


使用DataAdpter更新数据库
  private void Page_Load(object sender, System.EventArgs e)
  {
   Bind();
  }
  private void Bind()
  {
   SqlConnection nwindConn = new SqlConnection( "server=(local);uid=sa;pwd=12345678;database=Northwind" );
   SqlDataAdapter catDA = new SqlDataAdapter("SELECT * FROM Categories", nwindConn);      
   DataSet catDS = new DataSet();
   catDA.Fill(catDS, "Categories");  
   dgShow.DataSource = catDS.Tables["Categories"].DefaultView;
   dgShow.DataBind();
  }
  private void btnUpdate_Click(object sender, System.EventArgs e)
  {
   SqlConnection nwindConn = new SqlConnection( "server=(local);uid=sa;pwd=12345678;database=Northwind" ); 
   SqlDataAdapter catDA = new SqlDataAdapter("SELECT * FROM Categories", nwindConn);     
   catDA.UpdateCommand = new SqlCommand("UPDATE Categories SET CategoryName = @CategoryName " + "WHERE CategoryID = @CategoryID" , nwindConn);
   catDA.UpdateCommand.Parameters.Add("@CategoryName", SqlDbType.NVarChar, 15, "CategoryName");
   SqlParameter workParm = catDA.UpdateCommand.Parameters.Add("@CategoryID", SqlDbType.Int);
   workParm.SourceColumn = "CategoryID";
   workParm.SourceVersion = DataRowVersion.Original;
   DataSet catDS = new DataSet();
   catDA.Fill(catDS, "Categories");   
   DataRow cRow = catDS.Tables["Categories"].Rows[0];
   cRow["CategoryName"] = "NewName";
   catDA.Update(catDS,"Categories");
   Bind();
  }
  private void btnAdd_Click(object sender, System.EventArgs e)
  {
   SqlConnection nwindConn = new SqlConnection( "server=(local);uid=sa;pwd=12345678;database=Northwind" ); 
   SqlDataAdapter catDA = new SqlDataAdapter("SELECT * FROM Categories", nwindConn);       
   catDA.InsertCommand = new SqlCommand("Insert into Categories(CategoryName,Description) values"
    +" (@CategoryName,@Description)", nwindConn);
   catDA.InsertCommand.Parameters.Add("@CategoryName", SqlDbType.NVarChar, 15, "CategoryName");
   catDA.InsertCommand.Parameters.Add("@Description", SqlDbType.NText, 16, "Description");
   DataSet catDS = new DataSet();
   catDA.Fill(catDS, "Categories");   
   DataRow dr = catDS.Tables["Categories"].NewRow();
   dr["CategoryName"] = "Added New Name";
   dr["Description"] = "my Description";
   catDS.Tables["Categories"].Rows.Add(dr);
   catDA.Update(catDS,"Categories");
   Bind();
  }

使用CommandBuilder更新数据库
  private void Page_Load(object sender, System.EventArgs e)
  {
   // Create the DataSet and DataAdapter
   SqlConnection myConnection = new SqlConnection( "server=(local);uid=sa;pwd=12345678;database=Pubs" );
   DataSet myDataSet = new DataSet();
   SqlDataAdapter myDataAdapter = new SqlDataAdapter("Select * From Authors", myConnection );
   myDataAdapter.Fill( myDataSet, "Authors" );
   dgShow.DataSource = myDataSet.Tables[0].DefaultView;
   dgShow.DataBind();
  }
  private void btnUpdate_Click(object sender, System.EventArgs e)
  {
   // Create the DataSet and DataAdapter
   SqlConnection myConnection = new SqlConnection( "server=(local);uid=sa;pwd=12345678;database=Pubs" );
   DataSet myDataSet = new DataSet();
   SqlDataAdapter myDataAdapter = new SqlDataAdapter("Select * From Authors", myConnection );
   myDataAdapter.Fill( myDataSet, "Authors" );
   // Change value of first row
   myDataSet.Tables[ "Authors" ].Rows[ 0 ][ "au_fname" ] = "张三";
   // Update the Database Table
   SqlCommandBuilder myBuilder = new SqlCommandBuilder( myDataAdapter );
   myDataAdapter.Update( myDataSet, "Authors" );
   dgShow.DataSource = myDataSet.Tables[0].DefaultView;
   dgShow.DataBind();
  }

数据适配器的事件
OnRowUpdating:在数据行更新前执行
OnRowUpdated:在数据行更新后执行

SqlRowUpdated   EventArgs属性
属性                                                                             描述
Command                                                   要执行的数据命令
Errors                                                           错误
Row                                                              要更新的行
StatementType                                           要执行的命令类型，可能为Select、Insert、Delete和Update
RecordsAffected                                        要响应的行数
TableMapping                                            更新所使用的DataTableMapping


DataAdapter事件
  private void Page_Load(object sender, System.EventArgs e)
  {
   // Create the DataSet and DataAdapter
   SqlConnection myConnection = new SqlConnection( "server=(local);uid=sa;pwd=12345678;database=Pubs" );
   DataSet myDataSet = new DataSet();
   SqlDataAdapter myDataAdapter = new SqlDataAdapter("Select * From Authors", myConnection );
   myDataAdapter.Fill( myDataSet, "Authors" );
   dgShow.DataSource = myDataSet.Tables[0].DefaultView;
   dgShow.DataBind();
  }
  private void btnUpdate_Click(object sender, System.EventArgs e)
  {
   // Create the DataSet and DataAdapter
   SqlConnection myConnection = new SqlConnection( "server=(local);uid=sa;pwd=12345678;database=Pubs" );
   DataSet myDataSet = new DataSet();
   SqlDataAdapter myDataAdapter = new SqlDataAdapter("Select * From Authors", myConnection );
   //使用委托
   myDataAdapter.RowUpdating += new SqlRowUpdatingEventHandler(MyUpdatingHandler);
   myDataAdapter.RowUpdated += new SqlRowUpdatedEventHandler(MyUpdatedHandler);
   myDataAdapter.Fill( myDataSet, "Authors" );
   // Change value of first row
   myDataSet.Tables[ "Authors" ].Rows[ 0 ][ "au_fname" ] = "Johns";
   // Update the Database Table
   SqlCommandBuilder myBuilder = new SqlCommandBuilder( myDataAdapter );
   myDataAdapter.Update( myDataSet, "Authors" );
   dgShow.DataSource = myDataSet.Tables[0].DefaultView;
   dgShow.DataBind();
  }
  public void MyUpdatingHandler(object adapter,SqlRowUpdatingEventArgs e)
  {
   switch(e.StatementType)
   {
    case StatementType.Update:
    {
     SqlConnection myConnection = new SqlConnection( "server=(local);uid=sa;pwd=12345678;database=Pubs" );
     string strSql = "Select * From Authors where au_fname='"
      +e.Row["au_fname",DataRowVersion.Original]+"'";
     SqlCommand com = new SqlCommand(strSql,myConnection);
     myConnection.Open();
     if(com.ExecuteNonQuery()==0)
     {
      Response.Write("出错！有用户已经修改过数据集!");
      e.Status = UpdateStatus.ErrorsOccurred;//报错
     }
     myConnection.Close();
     break;
    }
   }
  }
  public void MyUpdatedHandler(object adapter,SqlRowUpdatedEventArgs e)
  {
   switch(e.StatementType)
   {
    case StatementType.Update:
     if(e.Status==UpdateStatus.ErrorsOccurred)
      e.Status = UpdateStatus.SkipCurrentRow;
     break;
   }
  }