Add row to dataview auto ForeignKey

  • Thread starter Thread starter Sankar Nemani
  • Start date Start date
S

Sankar Nemani

/*
Hi all,
Following is the code and my question is
I have a component that provides clients different
DataViews of a DataTable. So what I want to do is
when the clients try to add a row into the DataView
I want the row to be automatically filled with the
foreign key.
This question again appears in detail in the code below
Thanks In Advance
Sankar Nemani
*/

using System.Data;
using System;

public class Test
{
public static void Main()
{
DataSet ds;
DataTable dtCountries, dtCities;
DataView dvCitiesInIndia;
//create the dataset
ds = new DataSet();

//creating the two datatables
dtCountries = new DataTable("Countries");
dtCities = new DataTable("Cities");
ds.Tables.Add(dtCountries);
ds.Tables.Add(dtCities);

//adding columns to the countries table
dtCountries.Columns.Add("CountryID",Type.GetType
("System.Int32"));
dtCountries.Columns.Add("Country");
dtCountries.PrimaryKey = new DataColumn[]
{dtCountries.Columns["CountryID"]};

//adding columns to the cities table
dtCities.Columns.Add("CountryID",Type.GetType
("System.Int32"));
dtCities.Columns.Add("CityID",Type.GetType
("System.Int32"));
dtCities.Columns.Add("City");
dtCities.PrimaryKey = new DataColumn[]
{dtCities.Columns["CityID"]};

//now add the foreign key relation
ds.Relations.Add
("CountryCityRelation",dtCountries.Columns
["CountryID"],dtCities.Columns["CountryID"],true);

//add some data
DataRow drNew;

drNew = dtCountries.NewRow();
drNew["CountryID"] = 1;
drNew["Country"] = "USA";
dtCountries.Rows.Add(drNew);

drNew = dtCountries.NewRow();
drNew["CountryID"] = 2;
drNew["Country"] = "India";
dtCountries.Rows.Add(drNew);

drNew = dtCities.NewRow();
drNew["CountryID"] = 1;
drNew["CityID"] = 1;
drNew["City"] = "Atlanta";
dtCities.Rows.Add(drNew);

drNew = dtCities.NewRow();
drNew["CountryID"] = 1;
drNew["CityID"] = 2;
drNew["City"] = "Seattle";
dtCities.Rows.Add(drNew);

drNew = dtCities.NewRow();
drNew["CountryID"] = 1;
drNew["CityID"] = 3;
drNew["City"] = "New York";
dtCities.Rows.Add(drNew);

drNew = dtCities.NewRow();
drNew["CountryID"] = 2;
drNew["CityID"] = 4;
drNew["City"] = "Mumbai";
dtCities.Rows.Add(drNew);

drNew = dtCities.NewRow();
drNew["CountryID"] = 2;
drNew["CityID"] = 5;
drNew["City"] = "New Delhi";
dtCities.Rows.Add(drNew);

drNew = dtCities.NewRow();
drNew["CountryID"] = 2;
drNew["CityID"] = 6;
drNew["City"] = "Chennai";
dtCities.Rows.Add(drNew);


//create a dataview for cities in india
dvCitiesInIndia = new DataView(dtCities);
dvCitiesInIndia.RowFilter = "CountryID=2";

//add a new row to this dataview
DataRowView drv = dvCitiesInIndia.AddNew();
drv["CityID"] = 7;
drv["City"] = "Hyderabad";
drv.EndEdit();

/*************ISSUES***************************/
/*1.The following line of code would throw an
exception because this row is no longer part of the
dataview due to the DataView's RowFilter
i.e., "CountryID=2"*/
//System.Console.WriteLine(drv["City"]);

/*2.Now the city "Hyderabad" does not have a country
associated with it*/
/************Questions***********************/
/*I have a component that provides clients different
DataViews of a DataTable. So what I want to do is
when the clients try to add a row into the DataView
I want the row to be automatically filled with the
foreign key ex:CountryID.
Basically I want to set a default value for the
CountryID column. I know I can set a default value
for the CountryID column in the Cities "DataTable".
But that would not set a different default value for
each DataView. Then all cities would belong to only
one country. So is there a way to set a default value
for a column at a DataView level?*/

if(System.DBNull.Value.Equals(dtCities.Rows[6]
["CountryID"]))
{
System.Console.WriteLine("Country ID is null");
}
System.Console.WriteLine(dtCities.Rows[6]["City"]);
}
}
 
Back
Top