|
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using DBHelper;
using DBHelper.SqlClient;
namespace DBHelperQuickRelationshipSamples
{
/// <summary>
/// Summary description for Form1.
/// </summary>
public class frmRelationship : System.Windows.Forms.Form
{
private SqlTableHelper mobjOrderHeaderTblHelper;
private SqlTableHelper mobjOrderDetailsTblHelper;
private SqlTableHelper mobjItemTblHelper;
private SqlConnectionProvider mobjCnnProvider;
private System.Windows.Forms.DataGrid mdgrOrderHeader;
private System.Windows.Forms.DataGrid mdgrOrderDetails;
private System.Windows.Forms.DataGrid mdgrItem;
private System.Windows.Forms.Button mbtnSelectAllOrder;
private System.Windows.Forms.Button mbtnSelectOrderDetails;
private System.Windows.Forms.Button mbtnFillChild;
/// <summary>
/// Required designer variable.
/// </summary>
private System.ComponentModel.Container components = null;
public frmRelationship()
{
//
// Required for Windows Form Designer support
//
InitializeComponent();
//Initialize connection provider
mobjCnnProvider = new SqlConnectionProvider();
mobjCnnProvider.ConnectionString = "Initial Catalog=SwComponent;Data Source=amenthyst;UID=sa;Password=vios";
//Initialize table helper
mobjOrderHeaderTblHelper = new SqlTableHelper("x_OrderHeader");
mobjOrderHeaderTblHelper.MainConnectionProvider = mobjCnnProvider;
mobjOrderHeaderTblHelper.Compile();
mobjOrderDetailsTblHelper = new SqlTableHelper("x_OrderDetails");
mobjOrderDetailsTblHelper.MainConnectionProvider = mobjCnnProvider;
mobjOrderDetailsTblHelper.Compile();
mobjItemTblHelper = new SqlTableHelper("x_Item");
mobjItemTblHelper.MainConnectionProvider = mobjCnnProvider;
mobjItemTblHelper.Compile();
}
/// <summary>
/// Clean up any resources being used.
/// </summary>
protected override void Dispose( bool disposing )
{
if( disposing )
{
if (components != null)
{
components.Dispose();
}
}
base.Dispose( disposing );
}
#region Windows Form Designer generated code
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.mdgrOrderHeader = new System.Windows.Forms.DataGrid();
this.mdgrOrderDetails = new System.Windows.Forms.DataGrid();
this.mdgrItem = new System.Windows.Forms.DataGrid();
this.mbtnSelectOrderDetails = new System.Windows.Forms.Button();
this.mbtnSelectAllOrder = new System.Windows.Forms.Button();
this.mbtnFillChild = new System.Windows.Forms.Button();
((System.ComponentModel.ISupportInitialize)(this.mdgrOrderHeader)).BeginInit();
((System.ComponentModel.ISupportInitialize)(this.mdgrOrderDetails)).BeginInit();
((System.ComponentModel.ISupportInitialize)(this.mdgrItem)).BeginInit();
this.SuspendLayout();
//
// mdgrOrderHeader
//
this.mdgrOrderHeader.DataMember = "";
this.mdgrOrderHeader.HeaderForeColor = System.Drawing.SystemColors.ControlText;
this.mdgrOrderHeader.Location = new System.Drawing.Point(24, 8);
this.mdgrOrderHeader.Name = "mdgrOrderHeader";
this.mdgrOrderHeader.Size = new System.Drawing.Size(496, 120);
this.mdgrOrderHeader.TabIndex = 7;
//
// mdgrOrderDetails
//
this.mdgrOrderDetails.DataMember = "";
this.mdgrOrderDetails.HeaderForeColor = System.Drawing.SystemColors.ControlText;
this.mdgrOrderDetails.Location = new System.Drawing.Point(24, 168);
this.mdgrOrderDetails.Name = "mdgrOrderDetails";
this.mdgrOrderDetails.Size = new System.Drawing.Size(496, 120);
this.mdgrOrderDetails.TabIndex = 8;
//
// mdgrItem
//
this.mdgrItem.DataMember = "";
this.mdgrItem.HeaderForeColor = System.Drawing.SystemColors.ControlText;
this.mdgrItem.Location = new System.Drawing.Point(24, 296);
this.mdgrItem.Name = "mdgrItem";
this.mdgrItem.Size = new System.Drawing.Size(496, 120);
this.mdgrItem.TabIndex = 9;
//
// mbtnSelectOrderDetails
//
this.mbtnSelectOrderDetails.Location = new System.Drawing.Point(24, 432);
this.mbtnSelectOrderDetails.Name = "mbtnSelectOrderDetails";
this.mbtnSelectOrderDetails.Size = new System.Drawing.Size(288, 23);
this.mbtnSelectOrderDetails.TabIndex = 10;
this.mbtnSelectOrderDetails.Text = "Select Order Details based on selected Order Header";
this.mbtnSelectOrderDetails.Click += new System.EventHandler(this.mbtnSelectOrderDetails_Click);
//
// mbtnSelectAllOrder
//
this.mbtnSelectAllOrder.Location = new System.Drawing.Point(40, 136);
this.mbtnSelectAllOrder.Name = "mbtnSelectAllOrder";
this.mbtnSelectAllOrder.Size = new System.Drawing.Size(192, 23);
this.mbtnSelectAllOrder.TabIndex = 11;
this.mbtnSelectAllOrder.Text = "Select All Orders";
this.mbtnSelectAllOrder.Click += new System.EventHandler(this.mbtnSelectAllOrder_Click);
//
// mbtnFillChild
//
this.mbtnFillChild.Location = new System.Drawing.Point(24, 472);
this.mbtnFillChild.Name = "mbtnFillChild";
this.mbtnFillChild.Size = new System.Drawing.Size(344, 23);
this.mbtnFillChild.TabIndex = 12;
this.mbtnFillChild.Text = "Select Order Details and Items based on selected Order Header";
this.mbtnFillChild.Click += new System.EventHandler(this.mbtnFillChild_Click);
//
// frmRelationship
//
this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
this.ClientSize = new System.Drawing.Size(544, 510);
this.Controls.Add(this.mbtnFillChild);
this.Controls.Add(this.mbtnSelectAllOrder);
this.Controls.Add(this.mbtnSelectOrderDetails);
this.Controls.Add(this.mdgrItem);
this.Controls.Add(this.mdgrOrderDetails);
this.Controls.Add(this.mdgrOrderHeader);
this.Name = "frmRelationship";
this.Text = "Relationship Samples";
((System.ComponentModel.ISupportInitialize)(this.mdgrOrderHeader)).EndInit();
((System.ComponentModel.ISupportInitialize)(this.mdgrOrderDetails)).EndInit();
((System.ComponentModel.ISupportInitialize)(this.mdgrItem)).EndInit();
this.ResumeLayout(false);
}
#endregion
/// <summary>
/// The main entry point for the application.
/// </summary>
[STAThread]
static void Main()
{
Application.Run(new frmRelationship());
}
private void mbtnSelectAllOrder_Click(object sender, System.EventArgs e)
{
DataTable tblData;
tblData = mobjOrderHeaderTblHelper.SelectAll();
mdgrOrderHeader.DataSource = tblData;
}
private void mbtnSelectOrderDetails_Click(object sender, System.EventArgs e)
{
DataTable tblData;
DataTable tblChildData;
DBRelation objForeignRelation;
DataRow[] arowData;
int iSelected;
//add foreign keys, apply same concept for parent relationship situation
mobjOrderHeaderTblHelper.ChildRelations.Clear();
objForeignRelation = mobjOrderHeaderTblHelper.AddForeignKeys(mobjOrderDetailsTblHelper, new string[] {"OrderDetailsOrderHeaderID"});
//construct beginning row to retrieve child rows
iSelected = Convert.ToInt32(mdgrOrderHeader[mdgrOrderHeader.CurrentRowIndex, 0]);
Console.WriteLine("Order Header ID: " + iSelected);
mobjOrderHeaderTblHelper.SelectCriteria = String.Format("OrderHeaderID = {0}", iSelected);
mobjOrderHeaderTblHelper.Data.Rows.Clear();
tblData = mobjOrderHeaderTblHelper.SelectSome();
//retrieve child rows, use GetParentRowsDatabase for retrive parent rows case
mobjOrderDetailsTblHelper.Data.Rows.Clear();
arowData = mobjOrderHeaderTblHelper.GetChildRowsDatabase(tblData.Rows[0], objForeignRelation);
Console.WriteLine("Total child rows: " + arowData.Length);
tblChildData = arowData[0].Table;
mdgrOrderDetails.DataSource = tblChildData;
if (mdgrItem.DataSource != null)
{
((DataTable)mdgrItem.DataSource).Clear();
}
}
private void mbtnFillChild_Click(object sender, System.EventArgs e)
{
int iSelected;
string strSelected;
//add foreign keys, apply same concept for parent relationship situation
mobjOrderHeaderTblHelper.ChildRelations.Clear();
mobjOrderDetailsTblHelper.ChildRelations.Clear();
mobjOrderHeaderTblHelper.AddForeignKeys(mobjOrderDetailsTblHelper, new string[] {"OrderDetailsOrderHeaderID"});
//add foreign relationship explicitly as foreign key in x_Item not reference primary key
//in x_OrderDetails
mobjOrderDetailsTblHelper.ChildRelations.Add("x_OrderDetailsFKx_Item|ItemID",
mobjOrderDetailsTblHelper, mobjItemTblHelper,
new DataColumn[] {mobjOrderDetailsTblHelper.Data.Columns["OrderDetailsItemID"]},
new DataColumn[] {mobjItemTblHelper.Data.Columns["ItemID"]});
//construct beginning row to retrieve child rows
iSelected = Convert.ToInt32(mdgrOrderHeader[mdgrOrderHeader.CurrentRowIndex, 0]);
Console.WriteLine("Order Header ID: " + iSelected);
strSelected = String.Format("OrderHeaderID = {0}", iSelected);
//clear data before retrieve foreign/child records
mobjOrderHeaderTblHelper.Data.Clear();
mobjOrderDetailsTblHelper.Data.Clear();
mobjItemTblHelper.Data.Clear();
mobjCnnProvider.OpenConnection();
mobjCnnProvider.BeginTransaction();
//retrive all related child/foreign rows based on select criteria for top table helper.
//use FillParents for retrieving all related parent rows case
SqlDataSetHelper.FillChilds(mobjOrderHeaderTblHelper, strSelected);
mobjCnnProvider.CommitTransaction();
mobjCnnProvider.CloseConnection();
mdgrOrderHeader.DataSource = mobjOrderHeaderTblHelper.Data;
mdgrOrderDetails.DataSource = mobjOrderDetailsTblHelper.Data;
mdgrItem.DataSource = mobjItemTblHelper.Data;
}
}
}
|
By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.
If a file you wish to view isn't highlighted, and is a text file (not binary), please
let us know and we'll add colourisation support for it.
Had worked as analyst programmer for 4 years. Now helping in family business but still involved actively in .Net development whenever there is a free time.