ADO.NET. Управление базами данных. Связь по схеме OLE DB провайдера. Коррекция стилей DataGridView, страница 32

  {

   ds = new DataSet ("StudsExams");

   da = new OleDbDataAdapter ("Select * from Studs", cn);

   da.MissingSchemaAction = MissingSchemaAction.AddWithKey;

   da.Fill (ds, "Studs");

   da.SelectCommand.CommandText = "Select * from Exams";

   da.Fill (ds, "Exams");

   ds.Relations.Add(new DataRelation ("StudsExams",

    ds.Tables[0].Columns[0],

    ds.Tables[1].Columns[1]));

//    ds.Tables[0].Columns.Add("Exams", typeof(int), "Count(Child.StudID)");

   bsStuds = new BindingSource(ds, ds.Tables[0].TableName);

   bsExams = new BindingSource(bsStuds, ds.Relations[0].RelationName);

   AddStyle();

   gridStud.DataSource = bsStuds;

   gridExam.DataSource = bsExams;

   bn.BindingSource = bsStuds;

   changes = new TablesChange (ds.Tables);

   ds.Tables[0].RowDeleting += new DataRowChangeEventHandler (changes.RowChanged);

   ds.Tables[1].RowDeleting += new DataRowChangeEventHandler (changes.RowChanged);

   ds.Tables[0].RowChanged += new DataRowChangeEventHandler (changes.RowChanged);

   ds.Tables[1].RowChanged += new DataRowChangeEventHandler (changes.RowChanged);

  }

  void UpdateDB ()

  {

   Debug.WriteLine("\n\nDatabase update results:\n");

   int nRows = 0;

   OleDbCommand cmd = new OleDbCommand();

   cmd.Connection = cn;

   string s = "INSERT INTO `Studs` (`Name`, `Phone`, `Addr`) Values (";

   foreach (DataRow row in changes.Added[0])

   {

    HandleEmptyFields(row, ds.Tables[0]);

    cmd.CommandText = s + "'" + row[1] + "','" + row[2] + "','" + row[3] + "')";

    nRows += cmd.ExecuteNonQuery();

    cmd.CommandText = "SELECT @@IDENTITY";

    int id = (int)cmd.ExecuteScalar();

    row[0] = id;

    row.AcceptChanges();

    DataRow[] rows = row.GetChildRows(ds.Relations[0]);

    for (int i = 0; i < rows.Length; i++)

      rows[i][1] = id;

    Debug.WriteLine("Exams corrected: " + rows.Length);

   }

   Debug.WriteLine("Studs added: " + nRows);

   nRows = 0;

   s = "INSERT INTO `Exams` (`StudID`, `Course`, `Credit`, `Date`, `Mark`) VALUES (";

   foreach (DataRow row in changes.Added[1])

   {

    HandleEmptyFields(row, ds.Tables[1]);

    cmd.CommandText = s + row[1] + ",'" + row[2] + "'," + row[3] +

      ",'" + row[4] + "'," + row[5] + ") ";

    nRows += cmd.ExecuteNonQuery();

    cmd.CommandText = "SELECT @@IDENTITY";

    int id = (int)cmd.ExecuteScalar();

   row[0] = id;

    row.AcceptChanges();

   }

   Debug.WriteLine("Exams added: " + nRows);

   nRows = 0;

   s = "UPDATE `Studs` SET `Name` ='";

   string where = " WHERE `StudID` =";

   foreach (DataRow row in changes.Changed[0])

   {

    HandleEmptyFields(row, ds.Tables[0]);

    cmd.CommandText = s + row[1] + "',`Phone` ='" + row[2] + "',`Addr` ='" + row[3]

      + "'" + where + row[0];

    nRows += cmd.ExecuteNonQuery();

   }

   Debug.WriteLine("Studs changed: " + nRows);

   nRows = 0;

   s = "UPDATE `Exams` SET `StudID`=";

   where = " WHERE `ExamID` = ";

   foreach (DataRow row in changes.Changed[1])

   {

    HandleEmptyFields(row, ds.Tables[1]);

    cmd.CommandText = s + row[1] + ",`Course` ='" + row[2] + "',`Credit` =" + row[3]

      + ",`Date` ='" + row[4] + "',`Mark` =" + row[5] + where + row[0];

    nRows += cmd.ExecuteNonQuery();

   }

   Debug.WriteLine("Exams changed: " + nRows);

   nRows = 0;

   s = "DELETE FROM `Exams` WHERE `ExamID` = ";

   foreach (DataRow row in changes.Deleted[1])

   {

    cmd.CommandText = s + row[0];

    nRows += cmd.ExecuteNonQuery();

   }

   Debug.WriteLine("Exams deleted: " + nRows);

   nRows = 0;

   s = "DELETE FROM `Studs` WHERE `StudID` = ";

   foreach (DataRow row in changes.Deleted[0])

   {

    cmd.CommandText = s + row[0];

    nRows += cmd.ExecuteNonQuery();

   }