HOW TO: ???? ??? ????? JOIN DataSet ?? Visual C# .NET

• CreateJoinTable
• InsertJoinInto
• SelectJoinInto

???????

• Microsoft Windows XP ?? Windows 2000 ?? Windows NT 4.0 Service Pack 6a
• Microsoft Visual Studio .NET

• ???? ???? C# .NET ???????
• ??????? ADO.NET ????? ??????

??? DataSetHelper Shell

1. ??? ????? Visual Studio .NET.
2. ?? ??????? ???? ??? ??? ???? ?? ???? ??? ???????.
3. ?? ???? ?????? ????? ???? ? ???? ??? Visual C# ?????? ??? ????? Project ?? ???? ??? ????? ?????? ??? ?????.
4. ?? ?????? ?????? ???? DataSetHelper.
5. ??????? ??? ????? ???????? ???????? ???????:

   public class DataSetHelper
   {
       public DataSet ds;
   
       public DataSetHelper(ref DataSet DataSet)
       {
   	ds = DataSet;
       }
       public DataSetHelper()
       {
   	ds = null;
       }
   }
   					

   ???? ??????? ????? ????? ????????? ??????? ?? ?????? ????? ???? ?? ????? ?? ?? ???? ???? ???? DataSet. ?????? ??? ??? ???? ????? ??? ???? ???? DataSet ???????? DataTable ???? ???? ?????? ???????? ??? ????? ???????? ??? DataSet.

???? ????? ??????

1. ????? ????? ????? ?????? ??????? ?? ????? DataSetHelper ???? ??? ???????? ?? ??? "":

   private class FieldInfo
   {
       public string RelationName;
       public string FieldName;	//source table field name
       public string FieldAlias;	//destination table field name
       public string Aggregate;
   }
   					

   ??????: ??? ????? ??????? ??? ?????? ???? DataSetHelper ?????? ??? ?????? ???? ?? ?????? ??? ???????.
   
   
2. ????? ??????? ??? ??? ??????? ??? ????? ????? ?????? ??? ????? DataSet:

   private System.Collections.ArrayList m_FieldInfo; private string m_FieldList; 
   					

3. ????? ????? ??? ?????? ??? ????? ?????. ??? ??????? ?? ??? ??????? ????????? ?? ?????? ???? DataSetHelper.

   private void ParseFieldList(string FieldList, bool AllowRelation)
   {
       /*
        * This code parses FieldList into FieldInfo objects  and then 
        * adds them to the m_FieldInfo private member
        * 
        * FieldList systax:  [relationname.]fieldname[ alias], ...
       */ 
       if (m_FieldList == FieldList) return;
       m_FieldInfo = new System.Collections.ArrayList();
       m_FieldList = FieldList;
       FieldInfo Field; string[] FieldParts;
       string[] Fields=FieldList.Split(',');
       int i;
       for (i=0; i<=Fields.Length-1; i++)
       {
           Field=new FieldInfo();
           //parse FieldAlias
           FieldParts = Fields[i].Trim().Split(' ');
           switch (FieldParts.Length)
           {
               case 1:
                   //to be set at the end of the loop
                   break;
               case 2:
                   Field.FieldAlias=FieldParts[1];
                   break;
               default:
                   throw new Exception("Too many spaces in field definition: '" + Fields[i] + "'.");	                        
           }
           //parse FieldName and RelationName
           FieldParts = FieldParts[0].Split('.');
           switch (FieldParts.Length)
           {
               case 1: 
                   Field.FieldName=FieldParts[0];
                   break;
               case 2:
                   if (AllowRelation==false)
                       throw new Exception("Relation specifiers not permitted in field list: '" + Fields[i] + "'.");
                   Field.RelationName = FieldParts[0].Trim();
                   Field.FieldName=FieldParts[1].Trim();
                   break;
               default:
                   throw new Exception("Invalid field definition: " + Fields[i] + "'.");
           }
           if (Field.FieldAlias==null) 
               Field.FieldAlias = Field.FieldName;
           m_FieldInfo.Add (Field);
       }
   }
   					

????? CreateJoinTable

dt = dsHelper.CreateJoinTable("TestTable", ds.Tables["Employees"],
    "FirstName FName,LastName LName,DepartmentEmployee.DepartmentName Department");
				

[relationname.]fieldname[ alias], ...
				

• ColumnName ?????? DataType ?? ??????? ??? ???? ??? ????? ??? ?????? DataTable.
• ??? ??? ??????? ??????? ?? ?????? ???? ????? ??? ?????.
• ????? ????? ????? ??? ?? ?????? DataTable ?????? ????? ??? ??????.
• ???? ?? ????? ????? ?????? ??? ?????? ????? ????? ?????? ???? ??? ????? ?? ????? ????? ?? ??????? ?? ???? DataTable. ?? ???? ??? ???? ????? ?????? ?? ????? ???????.
• ??? ?? ????? DataRelation ??????? ?????. ??? ???? ??????? ??? ??? ???? ?? ????? ???? ??? ???? ?? ??????? "?" ??? ???????? "?" ????? ??????? ???? ??? ?? ??????? ?????? "??????" Orders ?????? ?????? ???????? ?? ?????? ?? ??????? ???????? ???????? ?? ????????.

public DataTable CreateJoinTable(string TableName, DataTable SourceTable, string FieldList)
{
    /*
     * Creates a table based on fields of another table and related parent tables
     * 
     * FieldList syntax: [relationname.]fieldname[ alias][,[relationname.]fieldname[ alias]]...
    */ 
    if (FieldList==null)
    {
        throw new ArgumentException("You must specify at least one field in the field list.");
        //return CreateTable(TableName, SourceTable);
    }
    else
    {
        DataTable dt = new DataTable(TableName);
        ParseFieldList(FieldList, true);
        foreach(FieldInfo Field in m_FieldInfo)
        {
            if(Field.RelationName==null)
            {
                DataColumn dc = SourceTable.Columns[Field.FieldName];
                dt.Columns.Add(dc.ColumnName, dc.DataType, dc.Expression);
            }
            else
            {
                DataColumn dc = SourceTable.ParentRelations[Field.RelationName].ParentTable.Columns[Field.FieldName];
                dt.Columns.Add(dc.ColumnName, dc.DataType, dc.Expression);
            }
        }
        if (ds!=null)
            ds.Tables.Add(dt);
        return dt;
    }
}
				

????? InsertJoinInto

dsHelper.InsertJoinInto(ds.Tables["TestTable"], ds.Tables["Employees"], 
    "FirstName FName,LastName LName,DepartmentEmployee.DepartmentName Department", 
    "EmployeeID<5", "BirthDate");
				

public void InsertJoinInto(DataTable DestTable, DataTable SourceTable, 
    string FieldList, string RowFilter, string Sort)
{
    /*
    * Copies the selected rows and columns from SourceTable and inserts them into DestTable
    * FieldList has same format as CreatejoinTable
    */ 
    if (FieldList==null)
    {
        throw new ArgumentException("You must specify at least one field in the field list.");
        //InsertInto(DestTable, SourceTable, RowFilter, Sort);
    }
    else
    {
    ParseFieldList(FieldList, true);
    DataRow[] Rows = SourceTable.Select(RowFilter, Sort);
    foreach(DataRow SourceRow in Rows)
    {
            DataRow DestRow = DestTable.NewRow();
            foreach(FieldInfo Field in m_FieldInfo)
            {
                if(Field.RelationName==null)
                {
                    DestRow[Field.FieldName] = SourceRow[Field.FieldName];
                }
                else
                {
                    DataRow ParentRow = SourceRow.GetParentRow(Field.RelationName);
                    DestRow[Field.FieldName] = ParentRow[Field.FieldName];
                }
            }
            DestTable.Rows.Add(DestRow);
        }
    }
}
				

????? SelectJoinInto

dt = dsHelper.SelectInto("TestTable", ds.Tables["Employees"], 
    "FirstName FName,LastName LName,DepartmentEmployee.DepartmentName Department", "EmployeeID<5", "BirthDate");
				

public DataTable SelectJoinInto(string TableName, DataTable SourceTable, string FieldList, string RowFilter, string Sort)
{
    /*
     * Selects sorted, filtered values from one DataTable to another.
     * Allows you to specify relationname.fieldname in the FieldList to include fields from
     *  a parent table. The Sort and Filter only apply to the base table and not to related tables.
    */ 
    DataTable dt = CreateJoinTable(TableName, SourceTable, FieldList);
    InsertJoinInto(dt, SourceTable, FieldList, RowFilter, Sort);
    return dt;
}
				

?????? ???????

1. ???? ??????? ??????? ??? DataSetHelper ???? ??? ??????? ?? ??????? ???????.
2. ???? ??????? ??????? ?????? Visual C# Windows ????? ????:
   1. ??? ????? Visual Studio .NET.
   2. ?? ??????? ???? ??? ??? ???? ?? ???? ??? ???????.
   3. ?? ???? ?????? ????? ???? ? ???? ??? Visual C# ?????? ??? ????? Project ?? ???? ??? ????? Windows ??? ?????.
3. ?? "?????? ??????" ???? ??? ?????? ?????? ??? ???? ??? ?? ???? ??? ????? Project ????????. ????? ????? DataSetHelper.
4. ?? ??????? ??????? ???? ??? ????? ????.
5. ?? ???? ?????? ????? ??????? ???? ??? ????? ??????? ?????? ?? ?? ?????? ???? ??? ??????? DataSetHelper ?????? ????? Windows.
6. ?? ???? ??????? ???? ???? ???? DataGrid ?????? ?? ????? ?? ???? ??????? ??? ???????. ??? ????? btnCreateJoin ? btnInsertJoinInto ? btnSelectJoinInto. ???????? ????? ????????? ????? ?????? DataGrid (dataGrid1).
7. ?? ????????? ???????? "???????" ? ??? ???? ??????? ??????? ??? ???? ???? ????????? ????????:

   using System.Data;
   					

8. ????? ??????? ??????? ?????? ??? ????? ???????:

   DataSet ds; DataSetHelper.DataSetHelper dsHelper;
   					

9. ?? ?????? ????????? ???????? ??????? ??? ????? Form_Load:

   ds = new DataSet();
   dsHelper = new DataSetHelper.DataSetHelper(ref ds);
   //Create source tables
   DataTable dt = new DataTable("Employees");
   dt.Columns.Add("EmployeeID",Type.GetType("System.Int32") );
   dt.Columns.Add("FirstName", Type.GetType("System.String"));
   dt.Columns.Add("LastName", Type.GetType("System.String"));
   dt.Columns.Add("BirthDate", Type.GetType("System.DateTime"));
   dt.Columns.Add("JobTitle", Type.GetType("System.String"));
   dt.Columns.Add("DepartmentID", Type.GetType("System.Int32"));
   dt.Rows.Add(new object[] {1, "Tommy", "Hill", new DateTime(1970, 12, 31),  "Manager", 42});
   dt.Rows.Add(new object[] {2, "Brooke", "Sheals", new DateTime(1977, 12, 31), "Manager", 23});
   dt.Rows.Add(new object[] {3, "Bill", "Blast", new DateTime(1982, 5, 6), "Sales Clerk", 42});
   dt.Rows.Add(new object[] {1, "Kevin", "Kline", new DateTime(1978, 5, 13), "Sales Clerk", 42});
   dt.Rows.Add(new object[] {1, "Martha", "Seward", new DateTime(1976, 7, 4), "Sales Clerk", 23});
   dt.Rows.Add(new object[] {1, "Dora", "Smith", new DateTime(1985, 10, 22), "Trainee", 42});
   dt.Rows.Add(new object[] {1, "Elvis", "Pressman", new DateTime(1972, 11, 5), "Manager", 15});
   dt.Rows.Add(new object[] {1, "Johnny", "Cache", new DateTime(1984, 1, 23), "Sales Clerk", 15});
   dt.Rows.Add(new object[] {1, "Jean", "Hill", new DateTime(1979, 4, 14), "Sales Clerk", 42});
   dt.Rows.Add(new object[] {1, "Anna", "Smith", new DateTime(1985, 6, 26), "Trainee", 15});
   ds.Tables.Add(dt);
   
   dt = new DataTable("Departments");
   dt.Columns.Add("DepartmentID", Type.GetType("System.Int32"));
   dt.Columns.Add("DepartmentName", Type.GetType("System.String"));
   dt.Rows.Add(new object[] {15, "Men's Clothing"});
   dt.Rows.Add(new object[] {23, "Women's Clothing"});
   dt.Rows.Add(new object[] {42, "Children's Clothing"});
   ds.Tables.Add(dt);
   
   ds.Relations.Add("DepartmentEmployee",     ds.Tables["Departments"].Columns["DepartmentID"], 
       ds.Tables["Employees"].Columns["DepartmentID"]);
   					

10. ?? ?????? ????????? ???????? ??????? ??? ????? btnCreateJoin_Click:

    dsHelper.CreateJoinTable("EmpDept",ds.Tables["Employees"], 
        "FirstName FName,LastName LName,BirthDate,DepartmentEmployee.DepartmentName Department");
    dataGrid1.SetDataBinding(ds, "EmpDept");
    					

11. ?? ?????? ????????? ???????? ??????? ??? ????? btnInsertJoinInto_Click:

    dsHelper.InsertJoinInto(ds.Tables["EmpDept"], ds.Tables["Employees"], 
        "FirstName FName,LastName LName,BirthDate,DepartmentEmployee.DepartmentName Department",
        "JobTitle='Sales Clerk'", "DepartmentID");
    dataGrid1.SetDataBinding(ds, "EmpDept");
    					

12. ?? ?????? ????????? ???????? ??????? ??? ????? btnSelectJoinInto_Click:

    dsHelper.SelectJoinInto("EmpDept2", ds.Tables["Employees"],
        "FirstName,LastName,BirthDate BDate,DepartmentEmployee.DepartmentName Department", 
        "JobTitle='Manager'", "DepartmentID");
    dataGrid1.SetDataBinding(ds, "EmpDept2");
    					

13. ?? ?????? ??????? ?? ?? ???? ??? ?? ?? ???????. ???? ????? DataGrid ?? ?????? ???????? ?? ????????? ????????.
    
    ??????: ????? ???? ????? ??? ??? btnCreateJoin ???????? btnSelectJoinInto ??? ?????. ??? ??? ?????? ??? ?? ?? ??? ??????? ???? ?? ??? ????? ? ????? ????? ??? ?????? ????? ??? ?????? ?????. ???????? ??? ???? ??? ???? ????? ??? btnCreateJoin ??? ????? ??? btnInsertJoinInto; ????? ?????? DataTable ?? ??? ?????. ??? ???? ??? ???? btnInsertJoinInto ??? ???? ??? DataGrid ?? ????? ?????.

????? ???????

• ColumnName ?????? DataType ?? ??????? ??? ???? ??? ????? ??? ?????? DataTable. ????? ????? ????? CreateTable ???? ????? ??????? ??? ??????? MaxLength ?? ????? ????? ????? ???????? ???????.
• ?? ??? ??? ??????? Expression; ????? ?? ???? ??? ??? ??????? ???????. ?????? ?? ???? ????? ?????? ???? ??? ?????? ????? ?????? ??????? ??? ?????? ??????. ???????? ??? ???? ???? ?? ???? ?????? ?????? ?????? ?? ??????? ????? ?? ?? ?? ??????? ???? ????? ??? ?????? ??? ???? ???. ????? ????? ????? CreateTable ???? ?? ??????? (?????? InsertInto ????? ??????? ???? ????? ?????) ? ??? ????? ?? ?? ??? ???? ???? ???????? ?????? ?? ??? ??????.
• ????? ??? ????? CreateJoinTableInsertJoinInto ??????? SelectJoinInto ?? CreateTableInsertInto ??????? SelectInto. ?????? ??? ??????? ?????? ??? CreateTableInsertInto ??????? SelectInto ???? ??? ??? ??????? ????? ?????? ?? "????? ??????? ?? Microsoft:
  326009

  (http://support.microsoft.com/kb/326009/EN-US/ )

  HOWTO: ???? ??? ????? SELECT INTO DataSet ?? Visual C# .NET
  ??? ?? ??? ???? ??? ??? ????? ??? ??? ??? ???? ???? ?????????? ?? ?????? ?? ??? ????? ????? ????? CreateJoinTable ??????? InsertJoinInto ??????? ?????? ?????? ????? ??? ????? ?????? ' Throw ??? ??? uncommenting ????????? CreateTable ??????? InsertInto ?? ???? ????????? ???????? ???????:

      if (FieldList==null)
      {
          throw new ArgumentException("You must specify at least one field in the field list.");
          //return CreateTable(TableName, SourceTable);
      }
  					

  - ? -

      if (FieldList==null)
      {
          throw new ArgumentException("You must specify at least one field in the field list.");
          //InsertInto(DestTable, SourceTable, RowFilter, Sort);
      }
  					

??????? ??????? ????????

• ?? ??? ?????? ????? ???????? ?? ????? ?????? ??? ?? ?????? ?? ???????? ????? DataColumn. ????? ?????? ?????? ????? ??????? ???????? ?? ??? ??? ?? ????? ????? ??? ???? (.) ?? ????? (?) ?? () ?????.
• ??? ??? ?????? ??? ?? ???? ?? ??? ????? ? ??? ????? ??? ?????? ????? ??? DataSet ? ???? ???? ???????. ??????? ?????? ??? ??? ??????? ????? ????? ?????? ?????? ??? ????? ?????? ?????? ?? ??? ??? DataTable ???? ????? ????? ??????. ???? ????? ????? ????? ??? DataSetHelper ??? ???? ??? DataSet ?????? ?? ??? ??????? DataGrid.DataSource ?????? ??? ????? dt ????? ?? ???? ??????? ??????? ??????? SetDataBinding.
• ??? ??? ?????? ?????? ?????? ????? ???????? ??????? (??? ???) ? ??? ????? ??? ??? ??????? InsertJoinInto ?????? ???? ???? ?? ????????. ????? ??? ??? ???? ???.

???? ??? ?????? | ????? ???????