Hi,
I'm writing a program in Visual C# 2005 Professional Edition.
This program connects to a SQL Server 2005 database called
"Generations" (in which there is only one table, called
"Generations"), and it allows the user to add, edit and delete the
various records of the table.
"Generations" table has the following fields:
"IDPerson", NamePerson", "AgePerson" and "IDParent".
A record contains the information about a person (his name, his age and
the ID of his parent).
The "IDPerson" and "IDParent" fields are connected with a
PRIMARY - FOREIGN KEY constraint (I attach below the T-SQL Query that
creates the database with the table, the fields and the inner
constraint):

USE master
GO
--DROP Database Generations; (insert this statement only if
"Generations" database doesn't exist yet)
--GO (insert this statement only if "Generations" database
doesn't exist yet)
CREATE DATABASE Generations
GO
USE Generations
GO
CREATE TABLE Generations (IDPerson int IDENTITY (1,1) PRIMARY KEY,
NamePerson nvarchar(50) NOT NULL, AgePerson int NOT NULL,IDParent int
NULL FOREIGN KEY REFERENCES Generations(IDPerson));
INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
('Paul', 97, NULL);
INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
('Henry', 74, 1);
INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
('Bob', 51, 2);
INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
('Mike', 25, 3);
INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
('John', 78, NULL);
INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
('Peter', 47, 5);
INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
('Patrick', 25, 6);
INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
('Michael', 2, 7);
INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
('Jim', 65, NULL);
INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
('Justin', 40, 9);

At this point I have created a very simple C# program to view the
fields' values of every record in a Form.
So, I have inserted a "txtIDPerson", "txtNamePerson",
"txtAgePerson" and "txtIDParent" TextBox objects, and a
"btnPrevoiusPerson", "btnNextPerson", "btnAddPerson",
"btnEditPerson", "btnDeletePerson", "btnOk" and
"btnCancel" Button objects (I think the names explain clearly their
function).
I have written the code that imports the database structure and data in
the offline ADO.NET objects (included the relationship between
"IDPerson" and "IDParent" fields).

The issue that I don't solve is an error that raises when I try to
delete a person that has sons (in other words, a record with one or
more records descending).
For example, if I try to delete "Paul" (who has Henry as son, Bob
as grandchild and Mike as great-grandchild), the compiler gives the
following error:

"The DELETE statement is in conflict with SAME TABLE REFERENCE
"FK__Generatio__IDPar_7D78A4E7". The conflict has occurred in
"dbo.Generations" table, column 'IDParent' of "Generations"
database".

If I try to delete Mike (who has no sons), there are no problems.

The last thing I have noticed is that, if in the T-SQL statement I
create the "Generations" table without the "IDPerson" -
"IDParent" constraint (but I leave it unchanged in the C# code),
everything functions correctly: if I try to delete Paul, the program
deletes Paul, Henry, Bob and Mike.

As I need to keep the constraint in the SQL Server database too (not
only in the C# code), do you know if there is a way to make it
function? (is it a bug of SQL Server 2005, or the bug is me?)

I attach the C# code below, if there is anyone who wants to test it (of
course you must connect to your own SQL Server 2005 instance where you
have created "Generations" with the T-SQL code, so you have to
customize the cn.ConnectionString C# statement. The T-SQL code for the
database creation and population is attached above).

Thank you very much

using System;
using System.Collections;
using System.Data;
using System.Data.OleDb;
using System.Data.Common;
using System.Data.SqlClient;
using System.Drawing;
using System.Windows.Forms;

class SeparateMain
{
public static void Main()
{
Application.Run(new Generations());
}
}
class Generations: Form
{
/**************************************************************************/
/* CREAZIONE CONTROLLI PRESENTI NEL FORM
*/
/**************************************************************************/

// SQL Server database objects
public SqlConnection cn;
public SqlDataAdapter daGenerations;
public DataSet dsTablesSet;
public DataTable dtGenerations;
public CurrencyManager cmGenerations;

// Graphical objects (Labels, TextBoxes and Buttons)
public Label lblIDPerson, lblNamePerson, lblAgePerson, lblIDParent;
public TextBox txtIDPerson, txtNamePerson, txtAgePerson,
txtIDParent;
public Button btnPreviousPerson, btnNextPerson, btnAddPerson,
btnEditPerson, btnDeletePerson, btnOk, btnCancel, btnClose;


public Generations()
{
// Form dimension and position
Size = new System.Drawing.Size(570, 300);
Text = "Generations";
CenterToScreen();

// SQL Server objects istantiation and initialization
cn = new SqlConnection();
daGenerations = new SqlDataAdapter();
dsTablesSet = new DataSet();
dtGenerations = new DataTable();

dsTablesSet.DataSetName = "TablesSet";

// Table "Generations"
dtGenerations.TableName = "Generations";

dtGenerations.Columns.Add("IDPerson", typeof(int));
dtGenerations.Columns["IDPerson"].AutoIncrement = true;
dtGenerations.Columns["IDPerson"].AutoIncrementSeed = 1;
dtGenerations.Columns["IDPerson"].AutoIncrementStep = 1;

dtGenerations.Columns.Add("NamePerson", typeof(string));
dtGenerations.Columns["NamePerson"].AllowDBNull = false;
dtGenerations.Columns["NamePerson"].DefaultValue = "";

dtGenerations.Columns.Add("AgePerson", typeof(int));
dtGenerations.Columns["AgePerson"].AllowDBNull = false;

dtGenerations.Columns.Add("IDParent", typeof(int));

dtGenerations.PrimaryKey = new DataColumn[] {
dtGenerations.Columns["IDPerson"] };

dsTablesSet.Tables.Add(dtGenerations);

// "Generations" inner relation
dsTablesSet.Relations.Add("Generations_ParentSon",
dtGenerations.Columns["IDPerson"], dtGenerations.Columns["IDParent"]);

cn.ConnectionString = "Persist Security Info=False;Integrated
Security=SSPI;database=Generations;server=(local)\\TECLOGICA;Connect
Timeout=10";

string strSQL;
SqlParameterCollection pc;
SqlParameter param;

// "Generations" UPDATE, INSERT, DELETE logic
strSQL = "SELECT IDPerson, NamePerson, AgePerson, IDParent FROM
Generations ORDER BY IDPerson";
daGenerations.SelectCommand = new SqlCommand(strSQL, cn);

strSQL = "UPDATE Generations SET NamePerson = @NamePerson_New,
AgePerson = @AgePerson_New, IDParent = @IDParent_New WHERE IDPerson =
@IDPerson_Orig";
daGenerations.UpdateCommand = new SqlCommand(strSQL, cn);
pc = daGenerations.UpdateCommand.Parameters;

pc.Add("@NamePerson_New", SqlDbType.NVarChar, 50,
"NamePerson");
pc.Add("@AgePerson_New", SqlDbType.Int, 0, "AgePerson");
pc.Add("@IDParent_New", SqlDbType.Int, 0, "IDParent");

param = pc.Add("@IDPerson_Orig", SqlDbType.Int, 0, "IDPerson");
param.SourceVersion = DataRowVersion.Original;

strSQL = "SET IDENTITY_INSERT Generations ON; INSERT INTO
Generations (IDPerson, NamePerson, AgePerson, IDParent) VALUES
(@IDPerson, @NamePerson, @IDParent); SET IDENTITY_INSERT Generations
OFF";
daGenerations.InsertCommand = new SqlCommand(strSQL, cn);
pc = daGenerations.InsertCommand.Parameters;
pc.Add("@IDPerson", SqlDbType.Int, 0, "IDPerson");
pc.Add("@NamePerson", SqlDbType.NVarChar, 50, "NamePerson");
pc.Add("@AgePerson", SqlDbType.Int, 0, "AgePerson");
pc.Add("@IDParent", SqlDbType.Int, 0, "IDParent");

strSQL = "DELETE FROM Generations WHERE IDPerson = @IDPerson";
daGenerations.DeleteCommand = new SqlCommand(strSQL, cn);
pc = daGenerations.DeleteCommand.Parameters;
pc.Add("@IDPerson", SqlDbType.Int, 0, "IDPerson");

daGenerations.Fill(dsTablesSet, "Generations");

// Initialize CurrencyManager object
cmGenerations = (CurrencyManager)BindingContext[dsTablesSet,
"Generations"];

// Graphical objects istantiation and initialization
lblIDPerson = new Label();
lblIDPerson.Parent = this;
lblIDPerson.Size = new Size(120, 25);
lblIDPerson.Location = new Point(10, 10);
lblIDPerson.Text = "ID";

txtIDPerson = new TextBox();
txtIDPerson.Parent = this;
txtIDPerson.Size = new Size(120, 25);
txtIDPerson.Location = new Point(lblIDPerson.Left,
lblIDPerson.Bottom);
txtIDPerson.Enabled = false;
txtIDPerson.DataBindings.Add("Text", dsTablesSet,
"Generations.IDPerson");

lblNamePerson = new Label();
lblNamePerson.Parent = this;
lblNamePerson.Size = new Size(120, 25);
lblNamePerson.Location = new Point(lblIDPerson.Right + 20,
lblIDPerson.Top);
lblNamePerson.Text = "Name";

txtNamePerson = new TextBox();
txtNamePerson.Parent = this;
txtNamePerson.Size = new Size(120, 25);
txtNamePerson.Location = new Point(lblNamePerson.Left,
lblNamePerson.Bottom);
txtNamePerson.Enabled = false;
txtNamePerson.DataBindings.Add("Text", dsTablesSet,
"Generations.NamePerson");

lblAgePerson = new Label();
lblAgePerson.Parent = this;
lblAgePerson.Size = new Size(120, 25);
lblAgePerson.Location = new Point(lblNamePerson.Right + 20,
lblNamePerson.Top);
lblAgePerson.Text = "Age";

txtAgePerson = new TextBox();
txtAgePerson.Parent = this;
txtAgePerson.Size = new Size(120, 25);
txtAgePerson.Location = new Point(lblAgePerson.Left,
lblAgePerson.Bottom);
txtAgePerson.Enabled = false;
txtAgePerson.DataBindings.Add("Text", dsTablesSet,
"Generations.AgePerson");

lblIDParent = new Label();
lblIDParent.Parent = this;
lblIDParent.Size = new Size(120, 25);
lblIDParent.Location = new Point(lblAgePerson.Right + 20,
lblAgePerson.Top);
lblIDParent.Text = "IDParent";

txtIDParent = new TextBox();
txtIDParent.Parent = this;
txtIDParent.Size = new Size(120, 25);
txtIDParent.Location = new Point(lblIDParent.Left,
lblIDParent.Bottom);
txtIDParent.Enabled = false;
txtIDParent.DataBindings.Add("Text", dsTablesSet,
"Generations.IDParent");

btnPreviousPerson = new Button();
btnPreviousPerson.Parent = this;
btnPreviousPerson.Size = new Size(120, 25);
btnPreviousPerson.Location = new Point(150, 100);
btnPreviousPerson.Text = "Previous";
btnPreviousPerson.Click += new
EventHandler(btnPreviousPerson_Click);

btnNextPerson = new Button();
btnNextPerson.Parent = this;
btnNextPerson.Size = new Size(120, 25);
btnNextPerson.Location = new Point(btnPreviousPerson.Right +
20, btnPreviousPerson.Top);
btnNextPerson.Text = "Next";
btnNextPerson.Click += new EventHandler(btnNextPerson_Click);

btnAddPerson = new Button();
btnAddPerson.Parent = this;
btnAddPerson.Size = new Size(120, 25);
btnAddPerson.Location = new Point(80, 160);
btnAddPerson.Text = "Add";
btnAddPerson.Click += new EventHandler(btnAddPerson_Click);

btnEditPerson = new Button();
btnEditPerson.Parent = this;
btnEditPerson.Size = new Size(120, 25);
btnEditPerson.Location = new Point(btnAddPerson.Right + 20,
btnAddPerson.Top);
btnEditPerson.Text = "Edit";
btnEditPerson.Click += new EventHandler(btnEditPerson_Click);

btnDeletePerson = new Button();
btnDeletePerson.Parent = this;
btnDeletePerson.Size = new Size(120, 25);
btnDeletePerson.Location = new Point(btnEditPerson.Right + 20,
btnEditPerson.Top);
btnDeletePerson.Text = "Delete";
btnDeletePerson.Click += new
EventHandler(btnDeletePerson_Click);

btnOk = new Button();
btnOk.Parent = this;
btnOk.Size = new Size(120, 25);
btnOk.Location = new Point(150, 220);
btnOk.Text = "OK";
btnOk.Visible = false;
btnOk.Click += new EventHandler(btnOk_Click);

btnCancel = new Button();
btnCancel.Parent = this;
btnCancel.Size = new Size(120, 25);
btnCancel.Location = new Point(btnOk.Right + 20, btnOk.Top);
btnCancel.Text = "Cancel";
btnCancel.Visible = false;
btnCancel.Click += new EventHandler(btnCancel_Click);

btnClose = new Button();
btnClose.Parent = this;
btnClose.Size = new Size(120, 25);
btnClose.Location = new Point(420, 220);
btnClose.Text = "Close";
btnClose.Click += new EventHandler(btnClose_Click);
}

void btnAddPerson_Click(object sender, EventArgs e)
{
cmGenerations.AddNew();

txtNamePerson.Enabled = true;
txtIDParent.Enabled = true;

btnPreviousPerson.Visible = false;
btnNextPerson.Visible = false;
btnAddPerson.Visible = false;
btnEditPerson.Visible = false;
btnDeletePerson.Visible = false;

btnOk.Visible = true;
btnCancel.Visible = true;
}

void btnEditPerson_Click(object sender, EventArgs e)
{
txtNamePerson.Enabled = true;
txtIDParent.Enabled = true;

btnPreviousPerson.Visible = false;
btnNextPerson.Visible = false;
btnAddPerson.Visible = false;
btnEditPerson.Visible = false;
btnDeletePerson.Visible = false;

btnOk.Visible = true;
btnCancel.Visible = true;
}

void btnDeletePerson_Click(object sender, EventArgs e)
{
cmGenerations.RemoveAt(cmGenerations.Position);
daGenerations.Update(dtGenerations);
}

void btnPreviousPerson_Click(object sender, EventArgs e)
{
if (cmGenerations.Position > 0)
cmGenerations.Position--;
}

void btnNextPerson_Click(object sender, EventArgs e)
{
if (cmGenerations.Position < cmGenerations.Count-1)
cmGenerations.Position++;
}

void btnOk_Click(object sender, EventArgs e)
{
cmGenerations.EndCurrentEdit();
daGenerations.Update(dtGenerations);

txtNamePerson.Enabled = false;
txtIDParent.Enabled = false;

btnOk.Visible = false;
btnCancel.Visible = false;

btnPreviousPerson.Visible = true;
btnNextPerson.Visible = true;
btnAddPerson.Visible = true;
btnEditPerson.Visible = true;
btnDeletePerson.Visible = true;
}

void btnCancel_Click(object sender, EventArgs e)
{
cmGenerations.CancelCurrentEdit();

txtNamePerson.Enabled = false;
txtIDParent.Enabled = false;

btnOk.Visible = false;
btnCancel.Visible = false;

btnPreviousPerson.Visible = true;
btnNextPerson.Visible = true;
btnAddPerson.Visible = true;
btnEditPerson.Visible = true;
btnDeletePerson.Visible = true;
}

void btnClose_Click(object sender, EventArgs e)
{
Close();
}
}

Hi,

DO not post the entire code , nobody will run it in the first place

It's clear what is happening, you cannot delete a row if it has another row
that makes reference to it.

First you ahve to decide what makes the most sense in y our situation. You
could change the FK to allow it to accept null, or you could add a "dead"
person record.

In any case you will have to change the parentID column in the children of
the row you want to delete BEFORE actually delete it.


--
--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation


"polocar" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi,
> I'm writing a program in Visual C# 2005 Professional Edition.
> This program connects to a SQL Server 2005 database called
> "Generations" (in which there is only one table, called
> "Generations"), and it allows the user to add, edit and delete the
> various records of the table.
> "Generations" table has the following fields:
> "IDPerson", NamePerson", "AgePerson" and "IDParent".
> A record contains the information about a person (his name, his age and
> the ID of his parent).
> The "IDPerson" and "IDParent" fields are connected with a
> PRIMARY - FOREIGN KEY constraint (I attach below the T-SQL Query that
> creates the database with the table, the fields and the inner
> constraint):
>
> USE master
> GO
> --DROP Database Generations; (insert this statement only if
> "Generations" database doesn't exist yet)
> --GO (insert this statement only if "Generations" database
> doesn't exist yet)
> CREATE DATABASE Generations
> GO
> USE Generations
> GO
> CREATE TABLE Generations (IDPerson int IDENTITY (1,1) PRIMARY KEY,
> NamePerson nvarchar(50) NOT NULL, AgePerson int NOT NULL,IDParent int
> NULL FOREIGN KEY REFERENCES Generations(IDPerson));
> INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
> ('Paul', 97, NULL);
> INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
> ('Henry', 74, 1);
> INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
> ('Bob', 51, 2);
> INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
> ('Mike', 25, 3);
> INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
> ('John', 78, NULL);
> INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
> ('Peter', 47, 5);
> INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
> ('Patrick', 25, 6);
> INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
> ('Michael', 2, 7);
> INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
> ('Jim', 65, NULL);
> INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
> ('Justin', 40, 9);
>
> At this point I have created a very simple C# program to view the
> fields' values of every record in a Form.
> So, I have inserted a "txtIDPerson", "txtNamePerson",
> "txtAgePerson" and "txtIDParent" TextBox objects, and a
> "btnPrevoiusPerson", "btnNextPerson", "btnAddPerson",
> "btnEditPerson", "btnDeletePerson", "btnOk" and
> "btnCancel" Button objects (I think the names explain clearly their
> function).
> I have written the code that imports the database structure and data in
> the offline ADO.NET objects (included the relationship between
> "IDPerson" and "IDParent" fields).
>
> The issue that I don't solve is an error that raises when I try to
> delete a person that has sons (in other words, a record with one or
> more records descending).
> For example, if I try to delete "Paul" (who has Henry as son, Bob
> as grandchild and Mike as great-grandchild), the compiler gives the
> following error:
>
> "The DELETE statement is in conflict with SAME TABLE REFERENCE
> "FK__Generatio__IDPar_7D78A4E7". The conflict has occurred in
> "dbo.Generations" table, column 'IDParent' of "Generations"
> database".
>
> If I try to delete Mike (who has no sons), there are no problems.
>
> The last thing I have noticed is that, if in the T-SQL statement I
> create the "Generations" table without the "IDPerson" -
> "IDParent" constraint (but I leave it unchanged in the C# code),
> everything functions correctly: if I try to delete Paul, the program
> deletes Paul, Henry, Bob and Mike.
>
> As I need to keep the constraint in the SQL Server database too (not
> only in the C# code), do you know if there is a way to make it
> function? (is it a bug of SQL Server 2005, or the bug is me?)
>
> I attach the C# code below, if there is anyone who wants to test it (of
> course you must connect to your own SQL Server 2005 instance where you
> have created "Generations" with the T-SQL code, so you have to
> customize the cn.ConnectionString C# statement. The T-SQL code for the
> database creation and population is attached above).
>
> Thank you very much
>
> using System;
> using System.Collections;
> using System.Data;
> using System.Data.OleDb;
> using System.Data.Common;
> using System.Data.SqlClient;
> using System.Drawing;
> using System.Windows.Forms;
>
> class SeparateMain
> {
> public static void Main()
> {
> Application.Run(new Generations());
> }
> }
> class Generations: Form
> {
> /**************************************************************************/
> /* CREAZIONE CONTROLLI PRESENTI NEL FORM
> */
> /**************************************************************************/
>
> // SQL Server database objects
> public SqlConnection cn;
> public SqlDataAdapter daGenerations;
> public DataSet dsTablesSet;
> public DataTable dtGenerations;
> public CurrencyManager cmGenerations;
>
> // Graphical objects (Labels, TextBoxes and Buttons)
> public Label lblIDPerson, lblNamePerson, lblAgePerson, lblIDParent;
> public TextBox txtIDPerson, txtNamePerson, txtAgePerson,
> txtIDParent;
> public Button btnPreviousPerson, btnNextPerson, btnAddPerson,
> btnEditPerson, btnDeletePerson, btnOk, btnCancel, btnClose;
>
>
> public Generations()
> {
> // Form dimension and position
> Size = new System.Drawing.Size(570, 300);
> Text = "Generations";
> CenterToScreen();
>
> // SQL Server objects istantiation and initialization
> cn = new SqlConnection();
> daGenerations = new SqlDataAdapter();
> dsTablesSet = new DataSet();
> dtGenerations = new DataTable();
>
> dsTablesSet.DataSetName = "TablesSet";
>
> // Table "Generations"
> dtGenerations.TableName = "Generations";
>
> dtGenerations.Columns.Add("IDPerson", typeof(int));
> dtGenerations.Columns["IDPerson"].AutoIncrement = true;
> dtGenerations.Columns["IDPerson"].AutoIncrementSeed = 1;
> dtGenerations.Columns["IDPerson"].AutoIncrementStep = 1;
>
> dtGenerations.Columns.Add("NamePerson", typeof(string));
> dtGenerations.Columns["NamePerson"].AllowDBNull = false;
> dtGenerations.Columns["NamePerson"].DefaultValue = "";
>
> dtGenerations.Columns.Add("AgePerson", typeof(int));
> dtGenerations.Columns["AgePerson"].AllowDBNull = false;
>
> dtGenerations.Columns.Add("IDParent", typeof(int));
>
> dtGenerations.PrimaryKey = new DataColumn[] {
> dtGenerations.Columns["IDPerson"] };
>
> dsTablesSet.Tables.Add(dtGenerations);
>
> // "Generations" inner relation
> dsTablesSet.Relations.Add("Generations_ParentSon",
> dtGenerations.Columns["IDPerson"], dtGenerations.Columns["IDParent"]);
>
> cn.ConnectionString = "Persist Security Info=False;Integrated
> Security=SSPI;database=Generations;server=(local)\\TECLOGICA;Connect
> Timeout=10";
>
> string strSQL;
> SqlParameterCollection pc;
> SqlParameter param;
>
> // "Generations" UPDATE, INSERT, DELETE logic
> strSQL = "SELECT IDPerson, NamePerson, AgePerson, IDParent FROM
> Generations ORDER BY IDPerson";
> daGenerations.SelectCommand = new SqlCommand(strSQL, cn);
>
> strSQL = "UPDATE Generations SET NamePerson = @NamePerson_New,
> AgePerson = @AgePerson_New, IDParent = @IDParent_New WHERE IDPerson =
> @IDPerson_Orig";
> daGenerations.UpdateCommand = new SqlCommand(strSQL, cn);
> pc = daGenerations.UpdateCommand.Parameters;
>
> pc.Add("@NamePerson_New", SqlDbType.NVarChar, 50,
> "NamePerson");
> pc.Add("@AgePerson_New", SqlDbType.Int, 0, "AgePerson");
> pc.Add("@IDParent_New", SqlDbType.Int, 0, "IDParent");
>
> param = pc.Add("@IDPerson_Orig", SqlDbType.Int, 0, "IDPerson");
> param.SourceVersion = DataRowVersion.Original;
>
> strSQL = "SET IDENTITY_INSERT Generations ON; INSERT INTO
> Generations (IDPerson, NamePerson, AgePerson, IDParent) VALUES
> (@IDPerson, @NamePerson, @IDParent); SET IDENTITY_INSERT Generations
> OFF";
> daGenerations.InsertCommand = new SqlCommand(strSQL, cn);
> pc = daGenerations.InsertCommand.Parameters;
> pc.Add("@IDPerson", SqlDbType.Int, 0, "IDPerson");
> pc.Add("@NamePerson", SqlDbType.NVarChar, 50, "NamePerson");
> pc.Add("@AgePerson", SqlDbType.Int, 0, "AgePerson");
> pc.Add("@IDParent", SqlDbType.Int, 0, "IDParent");
>
> strSQL = "DELETE FROM Generations WHERE IDPerson = @IDPerson";
> daGenerations.DeleteCommand = new SqlCommand(strSQL, cn);
> pc = daGenerations.DeleteCommand.Parameters;
> pc.Add("@IDPerson", SqlDbType.Int, 0, "IDPerson");
>
> daGenerations.Fill(dsTablesSet, "Generations");
>
> // Initialize CurrencyManager object
> cmGenerations = (CurrencyManager)BindingContext[dsTablesSet,
> "Generations"];
>
> // Graphical objects istantiation and initialization
> lblIDPerson = new Label();
> lblIDPerson.Parent = this;
> lblIDPerson.Size = new Size(120, 25);
> lblIDPerson.Location = new Point(10, 10);
> lblIDPerson.Text = "ID";
>
> txtIDPerson = new TextBox();
> txtIDPerson.Parent = this;
> txtIDPerson.Size = new Size(120, 25);
> txtIDPerson.Location = new Point(lblIDPerson.Left,
> lblIDPerson.Bottom);
> txtIDPerson.Enabled = false;
> txtIDPerson.DataBindings.Add("Text", dsTablesSet,
> "Generations.IDPerson");
>
> lblNamePerson = new Label();
> lblNamePerson.Parent = this;
> lblNamePerson.Size = new Size(120, 25);
> lblNamePerson.Location = new Point(lblIDPerson.Right + 20,
> lblIDPerson.Top);
> lblNamePerson.Text = "Name";
>
> txtNamePerson = new TextBox();
> txtNamePerson.Parent = this;
> txtNamePerson.Size = new Size(120, 25);
> txtNamePerson.Location = new Point(lblNamePerson.Left,
> lblNamePerson.Bottom);
> txtNamePerson.Enabled = false;
> txtNamePerson.DataBindings.Add("Text", dsTablesSet,
> "Generations.NamePerson");
>
> lblAgePerson = new Label();
> lblAgePerson.Parent = this;
> lblAgePerson.Size = new Size(120, 25);
> lblAgePerson.Location = new Point(lblNamePerson.Right + 20,
> lblNamePerson.Top);
> lblAgePerson.Text = "Age";
>
> txtAgePerson = new TextBox();
> txtAgePerson.Parent = this;
> txtAgePerson.Size = new Size(120, 25);
> txtAgePerson.Location = new Point(lblAgePerson.Left,
> lblAgePerson.Bottom);
> txtAgePerson.Enabled = false;
> txtAgePerson.DataBindings.Add("Text", dsTablesSet,
> "Generations.AgePerson");
>
> lblIDParent = new Label();
> lblIDParent.Parent = this;
> lblIDParent.Size = new Size(120, 25);
> lblIDParent.Location = new Point(lblAgePerson.Right + 20,
> lblAgePerson.Top);
> lblIDParent.Text = "IDParent";
>
> txtIDParent = new TextBox();
> txtIDParent.Parent = this;
> txtIDParent.Size = new Size(120, 25);
> txtIDParent.Location = new Point(lblIDParent.Left,
> lblIDParent.Bottom);
> txtIDParent.Enabled = false;
> txtIDParent.DataBindings.Add("Text", dsTablesSet,
> "Generations.IDParent");
>
> btnPreviousPerson = new Button();
> btnPreviousPerson.Parent = this;
> btnPreviousPerson.Size = new Size(120, 25);
> btnPreviousPerson.Location = new Point(150, 100);
> btnPreviousPerson.Text = "Previous";
> btnPreviousPerson.Click += new
> EventHandler(btnPreviousPerson_Click);
>
> btnNextPerson = new Button();
> btnNextPerson.Parent = this;
> btnNextPerson.Size = new Size(120, 25);
> btnNextPerson.Location = new Point(btnPreviousPerson.Right +
> 20, btnPreviousPerson.Top);
> btnNextPerson.Text = "Next";
> btnNextPerson.Click += new EventHandler(btnNextPerson_Click);
>
> btnAddPerson = new Button();
> btnAddPerson.Parent = this;
> btnAddPerson.Size = new Size(120, 25);
> btnAddPerson.Location = new Point(80, 160);
> btnAddPerson.Text = "Add";
> btnAddPerson.Click += new EventHandler(btnAddPerson_Click);
>
> btnEditPerson = new Button();
> btnEditPerson.Parent = this;
> btnEditPerson.Size = new Size(120, 25);
> btnEditPerson.Location = new Point(btnAddPerson.Right + 20,
> btnAddPerson.Top);
> btnEditPerson.Text = "Edit";
> btnEditPerson.Click += new EventHandler(btnEditPerson_Click);
>
> btnDeletePerson = new Button();
> btnDeletePerson.Parent = this;
> btnDeletePerson.Size = new Size(120, 25);
> btnDeletePerson.Location = new Point(btnEditPerson.Right + 20,
> btnEditPerson.Top);
> btnDeletePerson.Text = "Delete";
> btnDeletePerson.Click += new
> EventHandler(btnDeletePerson_Click);
>
> btnOk = new Button();
> btnOk.Parent = this;
> btnOk.Size = new Size(120, 25);
> btnOk.Location = new Point(150, 220);
> btnOk.Text = "OK";
> btnOk.Visible = false;
> btnOk.Click += new EventHandler(btnOk_Click);
>
> btnCancel = new Button();
> btnCancel.Parent = this;
> btnCancel.Size = new Size(120, 25);
> btnCancel.Location = new Point(btnOk.Right + 20, btnOk.Top);
> btnCancel.Text = "Cancel";
> btnCancel.Visible = false;
> btnCancel.Click += new EventHandler(btnCancel_Click);
>
> btnClose = new Button();
> btnClose.Parent = this;
> btnClose.Size = new Size(120, 25);
> btnClose.Location = new Point(420, 220);
> btnClose.Text = "Close";
> btnClose.Click += new EventHandler(btnClose_Click);
> }
>
> void btnAddPerson_Click(object sender, EventArgs e)
> {
> cmGenerations.AddNew();
>
> txtNamePerson.Enabled = true;
> txtIDParent.Enabled = true;
>
> btnPreviousPerson.Visible = false;
> btnNextPerson.Visible = false;
> btnAddPerson.Visible = false;
> btnEditPerson.Visible = false;
> btnDeletePerson.Visible = false;
>
> btnOk.Visible = true;
> btnCancel.Visible = true;
> }
>
> void btnEditPerson_Click(object sender, EventArgs e)
> {
> txtNamePerson.Enabled = true;
> txtIDParent.Enabled = true;
>
> btnPreviousPerson.Visible = false;
> btnNextPerson.Visible = false;
> btnAddPerson.Visible = false;
> btnEditPerson.Visible = false;
> btnDeletePerson.Visible = false;
>
> btnOk.Visible = true;
> btnCancel.Visible = true;
> }
>
> void btnDeletePerson_Click(object sender, EventArgs e)
> {
> cmGenerations.RemoveAt(cmGenerations.Position);
> daGenerations.Update(dtGenerations);
> }
>
> void btnPreviousPerson_Click(object sender, EventArgs e)
> {
> if (cmGenerations.Position > 0)
> cmGenerations.Position--;
> }
>
> void btnNextPerson_Click(object sender, EventArgs e)
> {
> if (cmGenerations.Position < cmGenerations.Count-1)
> cmGenerations.Position++;
> }
>
> void btnOk_Click(object sender, EventArgs e)
> {
> cmGenerations.EndCurrentEdit();
> daGenerations.Update(dtGenerations);
>
> txtNamePerson.Enabled = false;
> txtIDParent.Enabled = false;
>
> btnOk.Visible = false;
> btnCancel.Visible = false;
>
> btnPreviousPerson.Visible = true;
> btnNextPerson.Visible = true;
> btnAddPerson.Visible = true;
> btnEditPerson.Visible = true;
> btnDeletePerson.Visible = true;
> }
>
> void btnCancel_Click(object sender, EventArgs e)
> {
> cmGenerations.CancelCurrentEdit();
>
> txtNamePerson.Enabled = false;
> txtIDParent.Enabled = false;
>
> btnOk.Visible = false;
> btnCancel.Visible = false;
>
> btnPreviousPerson.Visible = true;
> btnNextPerson.Visible = true;
> btnAddPerson.Visible = true;
> btnEditPerson.Visible = true;
> btnDeletePerson.Visible = true;
> }
>
> void btnClose_Click(object sender, EventArgs e)
> {
> Close();
> }
> }
>

MSSQL 2005 supports a type of referential integrity that will delete rows in
related tables but I don't know how to use it yet. Ordinarily our code is
responsible for the CRUD operations on the row(s) in other tables related
with Foreign Keys.


<%= Clinton Gallagher
NET csgallagher AT metromilwaukee.com
URL http://www.metromilwaukee.com/clintongallagher/



"polocar" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi,
> I'm writing a program in Visual C# 2005 Professional Edition.
> This program connects to a SQL Server 2005 database called
> "Generations" (in which there is only one table, called
> "Generations"), and it allows the user to add, edit and delete the
> various records of the table.
> "Generations" table has the following fields:
> "IDPerson", NamePerson", "AgePerson" and "IDParent".
> A record contains the information about a person (his name, his age and
> the ID of his parent).
> The "IDPerson" and "IDParent" fields are connected with a
> PRIMARY - FOREIGN KEY constraint (I attach below the T-SQL Query that
> creates the database with the table, the fields and the inner
> constraint):
>
> USE master
> GO
> --DROP Database Generations; (insert this statement only if
> "Generations" database doesn't exist yet)
> --GO (insert this statement only if "Generations" database
> doesn't exist yet)
> CREATE DATABASE Generations
> GO
> USE Generations
> GO
> CREATE TABLE Generations (IDPerson int IDENTITY (1,1) PRIMARY KEY,
> NamePerson nvarchar(50) NOT NULL, AgePerson int NOT NULL,IDParent int
> NULL FOREIGN KEY REFERENCES Generations(IDPerson));
> INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
> ('Paul', 97, NULL);
> INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
> ('Henry', 74, 1);
> INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
> ('Bob', 51, 2);
> INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
> ('Mike', 25, 3);
> INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
> ('John', 78, NULL);
> INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
> ('Peter', 47, 5);
> INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
> ('Patrick', 25, 6);
> INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
> ('Michael', 2, 7);
> INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
> ('Jim', 65, NULL);
> INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
> ('Justin', 40, 9);
>
> At this point I have created a very simple C# program to view the
> fields' values of every record in a Form.
> So, I have inserted a "txtIDPerson", "txtNamePerson",
> "txtAgePerson" and "txtIDParent" TextBox objects, and a
> "btnPrevoiusPerson", "btnNextPerson", "btnAddPerson",
> "btnEditPerson", "btnDeletePerson", "btnOk" and
> "btnCancel" Button objects (I think the names explain clearly their
> function).
> I have written the code that imports the database structure and data in
> the offline ADO.NET objects (included the relationship between
> "IDPerson" and "IDParent" fields).
>
> The issue that I don't solve is an error that raises when I try to
> delete a person that has sons (in other words, a record with one or
> more records descending).
> For example, if I try to delete "Paul" (who has Henry as son, Bob
> as grandchild and Mike as great-grandchild), the compiler gives the
> following error:
>
> "The DELETE statement is in conflict with SAME TABLE REFERENCE
> "FK__Generatio__IDPar_7D78A4E7". The conflict has occurred in
> "dbo.Generations" table, column 'IDParent' of "Generations"
> database".
>
> If I try to delete Mike (who has no sons), there are no problems.
>
> The last thing I have noticed is that, if in the T-SQL statement I
> create the "Generations" table without the "IDPerson" -
> "IDParent" constraint (but I leave it unchanged in the C# code),
> everything functions correctly: if I try to delete Paul, the program
> deletes Paul, Henry, Bob and Mike.
>
> As I need to keep the constraint in the SQL Server database too (not
> only in the C# code), do you know if there is a way to make it
> function? (is it a bug of SQL Server 2005, or the bug is me?)
>
> I attach the C# code below, if there is anyone who wants to test it (of
> course you must connect to your own SQL Server 2005 instance where you
> have created "Generations" with the T-SQL code, so you have to
> customize the cn.ConnectionString C# statement. The T-SQL code for the
> database creation and population is attached above).
>
> Thank you very much
>
> using System;
> using System.Collections;
> using System.Data;
> using System.Data.OleDb;
> using System.Data.Common;
> using System.Data.SqlClient;
> using System.Drawing;
> using System.Windows.Forms;
>
> class SeparateMain
> {
> public static void Main()
> {
> Application.Run(new Generations());
> }
> }
> class Generations: Form
> {
> /**************************************************************************/
> /* CREAZIONE CONTROLLI PRESENTI NEL FORM
> */
> /**************************************************************************/
>
> // SQL Server database objects
> public SqlConnection cn;
> public SqlDataAdapter daGenerations;
> public DataSet dsTablesSet;
> public DataTable dtGenerations;
> public CurrencyManager cmGenerations;
>
> // Graphical objects (Labels, TextBoxes and Buttons)
> public Label lblIDPerson, lblNamePerson, lblAgePerson, lblIDParent;
> public TextBox txtIDPerson, txtNamePerson, txtAgePerson,
> txtIDParent;
> public Button btnPreviousPerson, btnNextPerson, btnAddPerson,
> btnEditPerson, btnDeletePerson, btnOk, btnCancel, btnClose;
>
>
> public Generations()
> {
> // Form dimension and position
> Size = new System.Drawing.Size(570, 300);
> Text = "Generations";
> CenterToScreen();
>
> // SQL Server objects istantiation and initialization
> cn = new SqlConnection();
> daGenerations = new SqlDataAdapter();
> dsTablesSet = new DataSet();
> dtGenerations = new DataTable();
>
> dsTablesSet.DataSetName = "TablesSet";
>
> // Table "Generations"
> dtGenerations.TableName = "Generations";
>
> dtGenerations.Columns.Add("IDPerson", typeof(int));
> dtGenerations.Columns["IDPerson"].AutoIncrement = true;
> dtGenerations.Columns["IDPerson"].AutoIncrementSeed = 1;
> dtGenerations.Columns["IDPerson"].AutoIncrementStep = 1;
>
> dtGenerations.Columns.Add("NamePerson", typeof(string));
> dtGenerations.Columns["NamePerson"].AllowDBNull = false;
> dtGenerations.Columns["NamePerson"].DefaultValue = "";
>
> dtGenerations.Columns.Add("AgePerson", typeof(int));
> dtGenerations.Columns["AgePerson"].AllowDBNull = false;
>
> dtGenerations.Columns.Add("IDParent", typeof(int));
>
> dtGenerations.PrimaryKey = new DataColumn[] {
> dtGenerations.Columns["IDPerson"] };
>
> dsTablesSet.Tables.Add(dtGenerations);
>
> // "Generations" inner relation
> dsTablesSet.Relations.Add("Generations_ParentSon",
> dtGenerations.Columns["IDPerson"], dtGenerations.Columns["IDParent"]);
>
> cn.ConnectionString = "Persist Security Info=False;Integrated
> Security=SSPI;database=Generations;server=(local)\\TECLOGICA;Connect
> Timeout=10";
>
> string strSQL;
> SqlParameterCollection pc;
> SqlParameter param;
>
> // "Generations" UPDATE, INSERT, DELETE logic
> strSQL = "SELECT IDPerson, NamePerson, AgePerson, IDParent FROM
> Generations ORDER BY IDPerson";
> daGenerations.SelectCommand = new SqlCommand(strSQL, cn);
>
> strSQL = "UPDATE Generations SET NamePerson = @NamePerson_New,
> AgePerson = @AgePerson_New, IDParent = @IDParent_New WHERE IDPerson =
> @IDPerson_Orig";
> daGenerations.UpdateCommand = new SqlCommand(strSQL, cn);
> pc = daGenerations.UpdateCommand.Parameters;
>
> pc.Add("@NamePerson_New", SqlDbType.NVarChar, 50,
> "NamePerson");
> pc.Add("@AgePerson_New", SqlDbType.Int, 0, "AgePerson");
> pc.Add("@IDParent_New", SqlDbType.Int, 0, "IDParent");
>
> param = pc.Add("@IDPerson_Orig", SqlDbType.Int, 0, "IDPerson");
> param.SourceVersion = DataRowVersion.Original;
>
> strSQL = "SET IDENTITY_INSERT Generations ON; INSERT INTO
> Generations (IDPerson, NamePerson, AgePerson, IDParent) VALUES
> (@IDPerson, @NamePerson, @IDParent); SET IDENTITY_INSERT Generations
> OFF";
> daGenerations.InsertCommand = new SqlCommand(strSQL, cn);
> pc = daGenerations.InsertCommand.Parameters;
> pc.Add("@IDPerson", SqlDbType.Int, 0, "IDPerson");
> pc.Add("@NamePerson", SqlDbType.NVarChar, 50, "NamePerson");
> pc.Add("@AgePerson", SqlDbType.Int, 0, "AgePerson");
> pc.Add("@IDParent", SqlDbType.Int, 0, "IDParent");
>
> strSQL = "DELETE FROM Generations WHERE IDPerson = @IDPerson";
> daGenerations.DeleteCommand = new SqlCommand(strSQL, cn);
> pc = daGenerations.DeleteCommand.Parameters;
> pc.Add("@IDPerson", SqlDbType.Int, 0, "IDPerson");
>
> daGenerations.Fill(dsTablesSet, "Generations");
>
> // Initialize CurrencyManager object
> cmGenerations = (CurrencyManager)BindingContext[dsTablesSet,
> "Generations"];
>
> // Graphical objects istantiation and initialization
> lblIDPerson = new Label();
> lblIDPerson.Parent = this;
> lblIDPerson.Size = new Size(120, 25);
> lblIDPerson.Location = new Point(10, 10);
> lblIDPerson.Text = "ID";
>
> txtIDPerson = new TextBox();
> txtIDPerson.Parent = this;
> txtIDPerson.Size = new Size(120, 25);
> txtIDPerson.Location = new Point(lblIDPerson.Left,
> lblIDPerson.Bottom);
> txtIDPerson.Enabled = false;
> txtIDPerson.DataBindings.Add("Text", dsTablesSet,
> "Generations.IDPerson");
>
> lblNamePerson = new Label();
> lblNamePerson.Parent = this;
> lblNamePerson.Size = new Size(120, 25);
> lblNamePerson.Location = new Point(lblIDPerson.Right + 20,
> lblIDPerson.Top);
> lblNamePerson.Text = "Name";
>
> txtNamePerson = new TextBox();
> txtNamePerson.Parent = this;
> txtNamePerson.Size = new Size(120, 25);
> txtNamePerson.Location = new Point(lblNamePerson.Left,
> lblNamePerson.Bottom);
> txtNamePerson.Enabled = false;
> txtNamePerson.DataBindings.Add("Text", dsTablesSet,
> "Generations.NamePerson");
>
> lblAgePerson = new Label();
> lblAgePerson.Parent = this;
> lblAgePerson.Size = new Size(120, 25);
> lblAgePerson.Location = new Point(lblNamePerson.Right + 20,
> lblNamePerson.Top);
> lblAgePerson.Text = "Age";
>
> txtAgePerson = new TextBox();
> txtAgePerson.Parent = this;
> txtAgePerson.Size = new Size(120, 25);
> txtAgePerson.Location = new Point(lblAgePerson.Left,
> lblAgePerson.Bottom);
> txtAgePerson.Enabled = false;
> txtAgePerson.DataBindings.Add("Text", dsTablesSet,
> "Generations.AgePerson");
>
> lblIDParent = new Label();
> lblIDParent.Parent = this;
> lblIDParent.Size = new Size(120, 25);
> lblIDParent.Location = new Point(lblAgePerson.Right + 20,
> lblAgePerson.Top);
> lblIDParent.Text = "IDParent";
>
> txtIDParent = new TextBox();
> txtIDParent.Parent = this;
> txtIDParent.Size = new Size(120, 25);
> txtIDParent.Location = new Point(lblIDParent.Left,
> lblIDParent.Bottom);
> txtIDParent.Enabled = false;
> txtIDParent.DataBindings.Add("Text", dsTablesSet,
> "Generations.IDParent");
>
> btnPreviousPerson = new Button();
> btnPreviousPerson.Parent = this;
> btnPreviousPerson.Size = new Size(120, 25);
> btnPreviousPerson.Location = new Point(150, 100);
> btnPreviousPerson.Text = "Previous";
> btnPreviousPerson.Click += new
> EventHandler(btnPreviousPerson_Click);
>
> btnNextPerson = new Button();
> btnNextPerson.Parent = this;
> btnNextPerson.Size = new Size(120, 25);
> btnNextPerson.Location = new Point(btnPreviousPerson.Right +
> 20, btnPreviousPerson.Top);
> btnNextPerson.Text = "Next";
> btnNextPerson.Click += new EventHandler(btnNextPerson_Click);
>
> btnAddPerson = new Button();
> btnAddPerson.Parent = this;
> btnAddPerson.Size = new Size(120, 25);
> btnAddPerson.Location = new Point(80, 160);
> btnAddPerson.Text = "Add";
> btnAddPerson.Click += new EventHandler(btnAddPerson_Click);
>
> btnEditPerson = new Button();
> btnEditPerson.Parent = this;
> btnEditPerson.Size = new Size(120, 25);
> btnEditPerson.Location = new Point(btnAddPerson.Right + 20,
> btnAddPerson.Top);
> btnEditPerson.Text = "Edit";
> btnEditPerson.Click += new EventHandler(btnEditPerson_Click);
>
> btnDeletePerson = new Button();
> btnDeletePerson.Parent = this;
> btnDeletePerson.Size = new Size(120, 25);
> btnDeletePerson.Location = new Point(btnEditPerson.Right + 20,
> btnEditPerson.Top);
> btnDeletePerson.Text = "Delete";
> btnDeletePerson.Click += new
> EventHandler(btnDeletePerson_Click);
>
> btnOk = new Button();
> btnOk.Parent = this;
> btnOk.Size = new Size(120, 25);
> btnOk.Location = new Point(150, 220);
> btnOk.Text = "OK";
> btnOk.Visible = false;
> btnOk.Click += new EventHandler(btnOk_Click);
>
> btnCancel = new Button();
> btnCancel.Parent = this;
> btnCancel.Size = new Size(120, 25);
> btnCancel.Location = new Point(btnOk.Right + 20, btnOk.Top);
> btnCancel.Text = "Cancel";
> btnCancel.Visible = false;
> btnCancel.Click += new EventHandler(btnCancel_Click);
>
> btnClose = new Button();
> btnClose.Parent = this;
> btnClose.Size = new Size(120, 25);
> btnClose.Location = new Point(420, 220);
> btnClose.Text = "Close";
> btnClose.Click += new EventHandler(btnClose_Click);
> }
>
> void btnAddPerson_Click(object sender, EventArgs e)
> {
> cmGenerations.AddNew();
>
> txtNamePerson.Enabled = true;
> txtIDParent.Enabled = true;
>
> btnPreviousPerson.Visible = false;
> btnNextPerson.Visible = false;
> btnAddPerson.Visible = false;
> btnEditPerson.Visible = false;
> btnDeletePerson.Visible = false;
>
> btnOk.Visible = true;
> btnCancel.Visible = true;
> }
>
> void btnEditPerson_Click(object sender, EventArgs e)
> {
> txtNamePerson.Enabled = true;
> txtIDParent.Enabled = true;
>
> btnPreviousPerson.Visible = false;
> btnNextPerson.Visible = false;
> btnAddPerson.Visible = false;
> btnEditPerson.Visible = false;
> btnDeletePerson.Visible = false;
>
> btnOk.Visible = true;
> btnCancel.Visible = true;
> }
>
> void btnDeletePerson_Click(object sender, EventArgs e)
> {
> cmGenerations.RemoveAt(cmGenerations.Position);
> daGenerations.Update(dtGenerations);
> }
>
> void btnPreviousPerson_Click(object sender, EventArgs e)
> {
> if (cmGenerations.Position > 0)
> cmGenerations.Position--;
> }
>
> void btnNextPerson_Click(object sender, EventArgs e)
> {
> if (cmGenerations.Position < cmGenerations.Count-1)
> cmGenerations.Position++;
> }
>
> void btnOk_Click(object sender, EventArgs e)
> {
> cmGenerations.EndCurrentEdit();
> daGenerations.Update(dtGenerations);
>
> txtNamePerson.Enabled = false;
> txtIDParent.Enabled = false;
>
> btnOk.Visible = false;
> btnCancel.Visible = false;
>
> btnPreviousPerson.Visible = true;
> btnNextPerson.Visible = true;
> btnAddPerson.Visible = true;
> btnEditPerson.Visible = true;
> btnDeletePerson.Visible = true;
> }
>
> void btnCancel_Click(object sender, EventArgs e)
> {
> cmGenerations.CancelCurrentEdit();
>
> txtNamePerson.Enabled = false;
> txtIDParent.Enabled = false;
>
> btnOk.Visible = false;
> btnCancel.Visible = false;
>
> btnPreviousPerson.Visible = true;
> btnNextPerson.Visible = true;
> btnAddPerson.Visible = true;
> btnEditPerson.Visible = true;
> btnDeletePerson.Visible = true;
> }
>
> void btnClose_Click(object sender, EventArgs e)
> {
> Close();
> }
> }
>

I'm a C# newbie, so I can't offer much on your code, but I'm pretty familiar
with databases.

The behavior you are describing is normal for a Foreign Key (FK) constraint.
An FK is a relational implementation of a subset constraint. If you remove
the parent row, but there are still child rows, you have violated the subset
constraint, and the database engine (propely) won't let you do that.
Depending on the particular database engine, FK's can be defined to do one
of three things upon attempted deletes:
1. RESTRICT (also called NO ACTION by some databses). This is the default
for a foreign key.
2. CASCADE DELETE
3. SET NULL
4. CASCADE UPDATE

Restrict FK's do what you have encountered. They disallow deletes. With a
RESTRICT key, you have to write your code to delete from the "bottom up".
You start deleting at the bottom of the generational hierarchy and work your
way up to the highest level.

With CASCADE DELETE foreign keys (which MS SQL Server supports), you can
just delete the parent, and the children are automatically deleted by the
engine. This can be useful, but you need to make sure you want to delete
that particular parent, because the engine won't stop you from hurting
yourself. Can't speak to SQL Server on this particular issue, but Oracle
generally does a lot better if you index the foreign key column sot aht the
engine can delete the correct rows quickly.

Don't know if SQL Server supports SET NULL. A SET NULL key doesn't delete
the child row, but nulls out the foreign key. This means you would be left
with "child" rows that cannot be identifed with any particular parent.

CASCADE UPDATE propogates updated primary keys to the child rows. However,
if you find the need to update primary keys, I think the database has bigger
problems. I have never used this.

By far the most common type of foreign key is RESTRICT. I've only seen
CASCADE used a few times, and have never heard of anyone actually using SET
NULL.

Thanks,

Mac



"clintonG" <(E-Mail Removed)> wrote in message
news:u4xi%(E-Mail Removed)...
> MSSQL 2005 supports a type of referential integrity that will delete rows
> in related tables but I don't know how to use it yet. Ordinarily our code
> is responsible for the CRUD operations on the row(s) in other tables
> related with Foreign Keys.
>
>
> <%= Clinton Gallagher
> NET csgallagher AT metromilwaukee.com
> URL http://www.metromilwaukee.com/clintongallagher/
>
>
>
> "polocar" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Hi,
>> I'm writing a program in Visual C# 2005 Professional Edition.
>> This program connects to a SQL Server 2005 database called
>> "Generations" (in which there is only one table, called
>> "Generations"), and it allows the user to add, edit and delete the
>> various records of the table.
>> "Generations" table has the following fields:
>> "IDPerson", NamePerson", "AgePerson" and "IDParent".
>> A record contains the information about a person (his name, his age and
>> the ID of his parent).
>> The "IDPerson" and "IDParent" fields are connected with a
>> PRIMARY - FOREIGN KEY constraint (I attach below the T-SQL Query that
>> creates the database with the table, the fields and the inner
>> constraint):
>>
>> USE master
>> GO
>> --DROP Database Generations; (insert this statement only if
>> "Generations" database doesn't exist yet)
>> --GO (insert this statement only if "Generations" database
>> doesn't exist yet)
>> CREATE DATABASE Generations
>> GO
>> USE Generations
>> GO
>> CREATE TABLE Generations (IDPerson int IDENTITY (1,1) PRIMARY KEY,
>> NamePerson nvarchar(50) NOT NULL, AgePerson int NOT NULL,IDParent int
>> NULL FOREIGN KEY REFERENCES Generations(IDPerson));
>> INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
>> ('Paul', 97, NULL);
>> INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
>> ('Henry', 74, 1);
>> INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
>> ('Bob', 51, 2);
>> INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
>> ('Mike', 25, 3);
>> INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
>> ('John', 78, NULL);
>> INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
>> ('Peter', 47, 5);
>> INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
>> ('Patrick', 25, 6);
>> INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
>> ('Michael', 2, 7);
>> INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
>> ('Jim', 65, NULL);
>> INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
>> ('Justin', 40, 9);
>>
>> At this point I have created a very simple C# program to view the
>> fields' values of every record in a Form.
>> So, I have inserted a "txtIDPerson", "txtNamePerson",
>> "txtAgePerson" and "txtIDParent" TextBox objects, and a
>> "btnPrevoiusPerson", "btnNextPerson", "btnAddPerson",
>> "btnEditPerson", "btnDeletePerson", "btnOk" and
>> "btnCancel" Button objects (I think the names explain clearly their
>> function).
>> I have written the code that imports the database structure and data in
>> the offline ADO.NET objects (included the relationship between
>> "IDPerson" and "IDParent" fields).
>>
>> The issue that I don't solve is an error that raises when I try to
>> delete a person that has sons (in other words, a record with one or
>> more records descending).
>> For example, if I try to delete "Paul" (who has Henry as son, Bob
>> as grandchild and Mike as great-grandchild), the compiler gives the
>> following error:
>>
>> "The DELETE statement is in conflict with SAME TABLE REFERENCE
>> "FK__Generatio__IDPar_7D78A4E7". The conflict has occurred in
>> "dbo.Generations" table, column 'IDParent' of "Generations"
>> database".
>>
>> If I try to delete Mike (who has no sons), there are no problems.
>>
>> The last thing I have noticed is that, if in the T-SQL statement I
>> create the "Generations" table without the "IDPerson" -
>> "IDParent" constraint (but I leave it unchanged in the C# code),
>> everything functions correctly: if I try to delete Paul, the program
>> deletes Paul, Henry, Bob and Mike.
>>
>> As I need to keep the constraint in the SQL Server database too (not
>> only in the C# code), do you know if there is a way to make it
>> function? (is it a bug of SQL Server 2005, or the bug is me?)
>>
>> I attach the C# code below, if there is anyone who wants to test it (of
>> course you must connect to your own SQL Server 2005 instance where you
>> have created "Generations" with the T-SQL code, so you have to
>> customize the cn.ConnectionString C# statement. The T-SQL code for the
>> database creation and population is attached above).
>>
>> Thank you very much
>>
>> using System;
>> using System.Collections;
>> using System.Data;
>> using System.Data.OleDb;
>> using System.Data.Common;
>> using System.Data.SqlClient;
>> using System.Drawing;
>> using System.Windows.Forms;
>>
>> class SeparateMain
>> {
>> public static void Main()
>> {
>> Application.Run(new Generations());
>> }
>> }
>> class Generations: Form
>> {
>> /**************************************************************************/
>> /* CREAZIONE CONTROLLI PRESENTI NEL FORM
>> */
>> /**************************************************************************/
>>
>> // SQL Server database objects
>> public SqlConnection cn;
>> public SqlDataAdapter daGenerations;
>> public DataSet dsTablesSet;
>> public DataTable dtGenerations;
>> public CurrencyManager cmGenerations;
>>
>> // Graphical objects (Labels, TextBoxes and Buttons)
>> public Label lblIDPerson, lblNamePerson, lblAgePerson, lblIDParent;
>> public TextBox txtIDPerson, txtNamePerson, txtAgePerson,
>> txtIDParent;
>> public Button btnPreviousPerson, btnNextPerson, btnAddPerson,
>> btnEditPerson, btnDeletePerson, btnOk, btnCancel, btnClose;
>>
>>
>> public Generations()
>> {
>> // Form dimension and position
>> Size = new System.Drawing.Size(570, 300);
>> Text = "Generations";
>> CenterToScreen();
>>
>> // SQL Server objects istantiation and initialization
>> cn = new SqlConnection();
>> daGenerations = new SqlDataAdapter();
>> dsTablesSet = new DataSet();
>> dtGenerations = new DataTable();
>>
>> dsTablesSet.DataSetName = "TablesSet";
>>
>> // Table "Generations"
>> dtGenerations.TableName = "Generations";
>>
>> dtGenerations.Columns.Add("IDPerson", typeof(int));
>> dtGenerations.Columns["IDPerson"].AutoIncrement = true;
>> dtGenerations.Columns["IDPerson"].AutoIncrementSeed = 1;
>> dtGenerations.Columns["IDPerson"].AutoIncrementStep = 1;
>>
>> dtGenerations.Columns.Add("NamePerson", typeof(string));
>> dtGenerations.Columns["NamePerson"].AllowDBNull = false;
>> dtGenerations.Columns["NamePerson"].DefaultValue = "";
>>
>> dtGenerations.Columns.Add("AgePerson", typeof(int));
>> dtGenerations.Columns["AgePerson"].AllowDBNull = false;
>>
>> dtGenerations.Columns.Add("IDParent", typeof(int));
>>
>> dtGenerations.PrimaryKey = new DataColumn[] {
>> dtGenerations.Columns["IDPerson"] };
>>
>> dsTablesSet.Tables.Add(dtGenerations);
>>
>> // "Generations" inner relation
>> dsTablesSet.Relations.Add("Generations_ParentSon",
>> dtGenerations.Columns["IDPerson"], dtGenerations.Columns["IDParent"]);
>>
>> cn.ConnectionString = "Persist Security Info=False;Integrated
>> Security=SSPI;database=Generations;server=(local)\\TECLOGICA;Connect
>> Timeout=10";
>>
>> string strSQL;
>> SqlParameterCollection pc;
>> SqlParameter param;
>>
>> // "Generations" UPDATE, INSERT, DELETE logic
>> strSQL = "SELECT IDPerson, NamePerson, AgePerson, IDParent FROM
>> Generations ORDER BY IDPerson";
>> daGenerations.SelectCommand = new SqlCommand(strSQL, cn);
>>
>> strSQL = "UPDATE Generations SET NamePerson = @NamePerson_New,
>> AgePerson = @AgePerson_New, IDParent = @IDParent_New WHERE IDPerson =
>> @IDPerson_Orig";
>> daGenerations.UpdateCommand = new SqlCommand(strSQL, cn);
>> pc = daGenerations.UpdateCommand.Parameters;
>>
>> pc.Add("@NamePerson_New", SqlDbType.NVarChar, 50,
>> "NamePerson");
>> pc.Add("@AgePerson_New", SqlDbType.Int, 0, "AgePerson");
>> pc.Add("@IDParent_New", SqlDbType.Int, 0, "IDParent");
>>
>> param = pc.Add("@IDPerson_Orig", SqlDbType.Int, 0, "IDPerson");
>> param.SourceVersion = DataRowVersion.Original;
>>
>> strSQL = "SET IDENTITY_INSERT Generations ON; INSERT INTO
>> Generations (IDPerson, NamePerson, AgePerson, IDParent) VALUES
>> (@IDPerson, @NamePerson, @IDParent); SET IDENTITY_INSERT Generations
>> OFF";
>> daGenerations.InsertCommand = new SqlCommand(strSQL, cn);
>> pc = daGenerations.InsertCommand.Parameters;
>> pc.Add("@IDPerson", SqlDbType.Int, 0, "IDPerson");
>> pc.Add("@NamePerson", SqlDbType.NVarChar, 50, "NamePerson");
>> pc.Add("@AgePerson", SqlDbType.Int, 0, "AgePerson");
>> pc.Add("@IDParent", SqlDbType.Int, 0, "IDParent");
>>
>> strSQL = "DELETE FROM Generations WHERE IDPerson = @IDPerson";
>> daGenerations.DeleteCommand = new SqlCommand(strSQL, cn);
>> pc = daGenerations.DeleteCommand.Parameters;
>> pc.Add("@IDPerson", SqlDbType.Int, 0, "IDPerson");
>>
>> daGenerations.Fill(dsTablesSet, "Generations");
>>
>> // Initialize CurrencyManager object
>> cmGenerations = (CurrencyManager)BindingContext[dsTablesSet,
>> "Generations"];
>>
>> // Graphical objects istantiation and initialization
>> lblIDPerson = new Label();
>> lblIDPerson.Parent = this;
>> lblIDPerson.Size = new Size(120, 25);
>> lblIDPerson.Location = new Point(10, 10);
>> lblIDPerson.Text = "ID";
>>
>> txtIDPerson = new TextBox();
>> txtIDPerson.Parent = this;
>> txtIDPerson.Size = new Size(120, 25);
>> txtIDPerson.Location = new Point(lblIDPerson.Left,
>> lblIDPerson.Bottom);
>> txtIDPerson.Enabled = false;
>> txtIDPerson.DataBindings.Add("Text", dsTablesSet,
>> "Generations.IDPerson");
>>
>> lblNamePerson = new Label();
>> lblNamePerson.Parent = this;
>> lblNamePerson.Size = new Size(120, 25);
>> lblNamePerson.Location = new Point(lblIDPerson.Right + 20,
>> lblIDPerson.Top);
>> lblNamePerson.Text = "Name";
>>
>> txtNamePerson = new TextBox();
>> txtNamePerson.Parent = this;
>> txtNamePerson.Size = new Size(120, 25);
>> txtNamePerson.Location = new Point(lblNamePerson.Left,
>> lblNamePerson.Bottom);
>> txtNamePerson.Enabled = false;
>> txtNamePerson.DataBindings.Add("Text", dsTablesSet,
>> "Generations.NamePerson");
>>
>> lblAgePerson = new Label();
>> lblAgePerson.Parent = this;
>> lblAgePerson.Size = new Size(120, 25);
>> lblAgePerson.Location = new Point(lblNamePerson.Right + 20,
>> lblNamePerson.Top);
>> lblAgePerson.Text = "Age";
>>
>> txtAgePerson = new TextBox();
>> txtAgePerson.Parent = this;
>> txtAgePerson.Size = new Size(120, 25);
>> txtAgePerson.Location = new Point(lblAgePerson.Left,
>> lblAgePerson.Bottom);
>> txtAgePerson.Enabled = false;
>> txtAgePerson.DataBindings.Add("Text", dsTablesSet,
>> "Generations.AgePerson");
>>
>> lblIDParent = new Label();
>> lblIDParent.Parent = this;
>> lblIDParent.Size = new Size(120, 25);
>> lblIDParent.Location = new Point(lblAgePerson.Right + 20,
>> lblAgePerson.Top);
>> lblIDParent.Text = "IDParent";
>>
>> txtIDParent = new TextBox();
>> txtIDParent.Parent = this;
>> txtIDParent.Size = new Size(120, 25);
>> txtIDParent.Location = new Point(lblIDParent.Left,
>> lblIDParent.Bottom);
>> txtIDParent.Enabled = false;
>> txtIDParent.DataBindings.Add("Text", dsTablesSet,
>> "Generations.IDParent");
>>
>> btnPreviousPerson = new Button();
>> btnPreviousPerson.Parent = this;
>> btnPreviousPerson.Size = new Size(120, 25);
>> btnPreviousPerson.Location = new Point(150, 100);
>> btnPreviousPerson.Text = "Previous";
>> btnPreviousPerson.Click += new
>> EventHandler(btnPreviousPerson_Click);
>>
>> btnNextPerson = new Button();
>> btnNextPerson.Parent = this;
>> btnNextPerson.Size = new Size(120, 25);
>> btnNextPerson.Location = new Point(btnPreviousPerson.Right +
>> 20, btnPreviousPerson.Top);
>> btnNextPerson.Text = "Next";
>> btnNextPerson.Click += new EventHandler(btnNextPerson_Click);
>>
>> btnAddPerson = new Button();
>> btnAddPerson.Parent = this;
>> btnAddPerson.Size = new Size(120, 25);
>> btnAddPerson.Location = new Point(80, 160);
>> btnAddPerson.Text = "Add";
>> btnAddPerson.Click += new EventHandler(btnAddPerson_Click);
>>
>> btnEditPerson = new Button();
>> btnEditPerson.Parent = this;
>> btnEditPerson.Size = new Size(120, 25);
>> btnEditPerson.Location = new Point(btnAddPerson.Right + 20,
>> btnAddPerson.Top);
>> btnEditPerson.Text = "Edit";
>> btnEditPerson.Click += new EventHandler(btnEditPerson_Click);
>>
>> btnDeletePerson = new Button();
>> btnDeletePerson.Parent = this;
>> btnDeletePerson.Size = new Size(120, 25);
>> btnDeletePerson.Location = new Point(btnEditPerson.Right + 20,
>> btnEditPerson.Top);
>> btnDeletePerson.Text = "Delete";
>> btnDeletePerson.Click += new
>> EventHandler(btnDeletePerson_Click);
>>
>> btnOk = new Button();
>> btnOk.Parent = this;
>> btnOk.Size = new Size(120, 25);
>> btnOk.Location = new Point(150, 220);
>> btnOk.Text = "OK";
>> btnOk.Visible = false;
>> btnOk.Click += new EventHandler(btnOk_Click);
>>
>> btnCancel = new Button();
>> btnCancel.Parent = this;
>> btnCancel.Size = new Size(120, 25);
>> btnCancel.Location = new Point(btnOk.Right + 20, btnOk.Top);
>> btnCancel.Text = "Cancel";
>> btnCancel.Visible = false;
>> btnCancel.Click += new EventHandler(btnCancel_Click);
>>
>> btnClose = new Button();
>> btnClose.Parent = this;
>> btnClose.Size = new Size(120, 25);
>> btnClose.Location = new Point(420, 220);
>> btnClose.Text = "Close";
>> btnClose.Click += new EventHandler(btnClose_Click);
>> }
>>
>> void btnAddPerson_Click(object sender, EventArgs e)
>> {
>> cmGenerations.AddNew();
>>
>> txtNamePerson.Enabled = true;
>> txtIDParent.Enabled = true;
>>
>> btnPreviousPerson.Visible = false;
>> btnNextPerson.Visible = false;
>> btnAddPerson.Visible = false;
>> btnEditPerson.Visible = false;
>> btnDeletePerson.Visible = false;
>>
>> btnOk.Visible = true;
>> btnCancel.Visible = true;
>> }
>>
>> void btnEditPerson_Click(object sender, EventArgs e)
>> {
>> txtNamePerson.Enabled = true;
>> txtIDParent.Enabled = true;
>>
>> btnPreviousPerson.Visible = false;
>> btnNextPerson.Visible = false;
>> btnAddPerson.Visible = false;
>> btnEditPerson.Visible = false;
>> btnDeletePerson.Visible = false;
>>
>> btnOk.Visible = true;
>> btnCancel.Visible = true;
>> }
>>
>> void btnDeletePerson_Click(object sender, EventArgs e)
>> {
>> cmGenerations.RemoveAt(cmGenerations.Position);
>> daGenerations.Update(dtGenerations);
>> }
>>
>> void btnPreviousPerson_Click(object sender, EventArgs e)
>> {
>> if (cmGenerations.Position > 0)
>> cmGenerations.Position--;
>> }
>>
>> void btnNextPerson_Click(object sender, EventArgs e)
>> {
>> if (cmGenerations.Position < cmGenerations.Count-1)
>> cmGenerations.Position++;
>> }
>>
>> void btnOk_Click(object sender, EventArgs e)
>> {
>> cmGenerations.EndCurrentEdit();
>> daGenerations.Update(dtGenerations);
>>
>> txtNamePerson.Enabled = false;
>> txtIDParent.Enabled = false;
>>
>> btnOk.Visible = false;
>> btnCancel.Visible = false;
>>
>> btnPreviousPerson.Visible = true;
>> btnNextPerson.Visible = true;
>> btnAddPerson.Visible = true;
>> btnEditPerson.Visible = true;
>> btnDeletePerson.Visible = true;
>> }
>>
>> void btnCancel_Click(object sender, EventArgs e)
>> {
>> cmGenerations.CancelCurrentEdit();
>>
>> txtNamePerson.Enabled = false;
>> txtIDParent.Enabled = false;
>>
>> btnOk.Visible = false;
>> btnCancel.Visible = false;
>>
>> btnPreviousPerson.Visible = true;
>> btnNextPerson.Visible = true;
>> btnAddPerson.Visible = true;
>> btnEditPerson.Visible = true;
>> btnDeletePerson.Visible = true;
>> }
>>
>> void btnClose_Click(object sender, EventArgs e)
>> {
>> Close();
>> }
>> }
>>
>
>

Very helpful mac. I found this [1] while rooting around which briefly
describes and links to the enhanced 2005 feature set noting cascading has
been updated and may be relative to this news article.

I don't know better at the moment but I'd rather use CASCADE DELETE and let
the database clean unwanted rows from related tables. Were you inferring the
FK in the related table should be indexed when using MSSQL?

<%= Clinton Gallagher

[1] http://msdn2.microsoft.com/en-us/library/ms170355.aspx


"mac" <(E-Mail Removed)> wrote in message
news:1ujwg.15172$Nv.10393@fed1read10...
> I'm a C# newbie, so I can't offer much on your code, but I'm pretty
> familiar with databases.
>
> The behavior you are describing is normal for a Foreign Key (FK)
> constraint. An FK is a relational implementation of a subset constraint.
> If you remove the parent row, but there are still child rows, you have
> violated the subset constraint, and the database engine (propely) won't
> let you do that. Depending on the particular database engine, FK's can be
> defined to do one of three things upon attempted deletes:
> 1. RESTRICT (also called NO ACTION by some databses). This is the default
> for a foreign key.
> 2. CASCADE DELETE
> 3. SET NULL
> 4. CASCADE UPDATE
>
> Restrict FK's do what you have encountered. They disallow deletes. With
> a RESTRICT key, you have to write your code to delete from the "bottom
> up". You start deleting at the bottom of the generational hierarchy and
> work your way up to the highest level.
>
> With CASCADE DELETE foreign keys (which MS SQL Server supports), you can
> just delete the parent, and the children are automatically deleted by the
> engine. This can be useful, but you need to make sure you want to delete
> that particular parent, because the engine won't stop you from hurting
> yourself. Can't speak to SQL Server on this particular issue, but Oracle
> generally does a lot better if you index the foreign key column sot aht
> the engine can delete the correct rows quickly.
>
> Don't know if SQL Server supports SET NULL. A SET NULL key doesn't delete
> the child row, but nulls out the foreign key. This means you would be
> left with "child" rows that cannot be identifed with any particular
> parent.
>
> CASCADE UPDATE propogates updated primary keys to the child rows.
> However, if you find the need to update primary keys, I think the database
> has bigger problems. I have never used this.
>
> By far the most common type of foreign key is RESTRICT. I've only seen
> CASCADE used a few times, and have never heard of anyone actually using
> SET NULL.
>
> Thanks,
>
> Mac
>
>
>
> "clintonG" <(E-Mail Removed)> wrote in message
> news:u4xi%(E-Mail Removed)...
>> MSSQL 2005 supports a type of referential integrity that will delete rows
>> in related tables but I don't know how to use it yet. Ordinarily our code
>> is responsible for the CRUD operations on the row(s) in other tables
>> related with Foreign Keys.
>>
>>
>> <%= Clinton Gallagher
>> NET csgallagher AT metromilwaukee.com
>> URL http://www.metromilwaukee.com/clintongallagher/
>>
>>
>>
>> "polocar" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> Hi,
>>> I'm writing a program in Visual C# 2005 Professional Edition.
>>> This program connects to a SQL Server 2005 database called
>>> "Generations" (in which there is only one table, called
>>> "Generations"), and it allows the user to add, edit and delete the
>>> various records of the table.
>>> "Generations" table has the following fields:
>>> "IDPerson", NamePerson", "AgePerson" and "IDParent".
>>> A record contains the information about a person (his name, his age and
>>> the ID of his parent).
>>> The "IDPerson" and "IDParent" fields are connected with a
>>> PRIMARY - FOREIGN KEY constraint (I attach below the T-SQL Query that
>>> creates the database with the table, the fields and the inner
>>> constraint):
>>>
>>> USE master
>>> GO
>>> --DROP Database Generations; (insert this statement only if
>>> "Generations" database doesn't exist yet)
>>> --GO (insert this statement only if "Generations" database
>>> doesn't exist yet)
>>> CREATE DATABASE Generations
>>> GO
>>> USE Generations
>>> GO
>>> CREATE TABLE Generations (IDPerson int IDENTITY (1,1) PRIMARY KEY,
>>> NamePerson nvarchar(50) NOT NULL, AgePerson int NOT NULL,IDParent int
>>> NULL FOREIGN KEY REFERENCES Generations(IDPerson));
>>> INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
>>> ('Paul', 97, NULL);
>>> INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
>>> ('Henry', 74, 1);
>>> INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
>>> ('Bob', 51, 2);
>>> INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
>>> ('Mike', 25, 3);
>>> INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
>>> ('John', 78, NULL);
>>> INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
>>> ('Peter', 47, 5);
>>> INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
>>> ('Patrick', 25, 6);
>>> INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
>>> ('Michael', 2, 7);
>>> INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
>>> ('Jim', 65, NULL);
>>> INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
>>> ('Justin', 40, 9);
>>>
>>> At this point I have created a very simple C# program to view the
>>> fields' values of every record in a Form.
>>> So, I have inserted a "txtIDPerson", "txtNamePerson",
>>> "txtAgePerson" and "txtIDParent" TextBox objects, and a
>>> "btnPrevoiusPerson", "btnNextPerson", "btnAddPerson",
>>> "btnEditPerson", "btnDeletePerson", "btnOk" and
>>> "btnCancel" Button objects (I think the names explain clearly their
>>> function).
>>> I have written the code that imports the database structure and data in
>>> the offline ADO.NET objects (included the relationship between
>>> "IDPerson" and "IDParent" fields).
>>>
>>> The issue that I don't solve is an error that raises when I try to
>>> delete a person that has sons (in other words, a record with one or
>>> more records descending).
>>> For example, if I try to delete "Paul" (who has Henry as son, Bob
>>> as grandchild and Mike as great-grandchild), the compiler gives the
>>> following error:
>>>
>>> "The DELETE statement is in conflict with SAME TABLE REFERENCE
>>> "FK__Generatio__IDPar_7D78A4E7". The conflict has occurred in
>>> "dbo.Generations" table, column 'IDParent' of "Generations"
>>> database".
>>>
>>> If I try to delete Mike (who has no sons), there are no problems.
>>>
>>> The last thing I have noticed is that, if in the T-SQL statement I
>>> create the "Generations" table without the "IDPerson" -
>>> "IDParent" constraint (but I leave it unchanged in the C# code),
>>> everything functions correctly: if I try to delete Paul, the program
>>> deletes Paul, Henry, Bob and Mike.
>>>
>>> As I need to keep the constraint in the SQL Server database too (not
>>> only in the C# code), do you know if there is a way to make it
>>> function? (is it a bug of SQL Server 2005, or the bug is me?)
>>>
>>> I attach the C# code below, if there is anyone who wants to test it (of
>>> course you must connect to your own SQL Server 2005 instance where you
>>> have created "Generations" with the T-SQL code, so you have to
>>> customize the cn.ConnectionString C# statement. The T-SQL code for the
>>> database creation and population is attached above).
>>>
>>> Thank you very much
>>>
>>> using System;
>>> using System.Collections;
>>> using System.Data;
>>> using System.Data.OleDb;
>>> using System.Data.Common;
>>> using System.Data.SqlClient;
>>> using System.Drawing;
>>> using System.Windows.Forms;
>>>
>>> class SeparateMain
>>> {
>>> public static void Main()
>>> {
>>> Application.Run(new Generations());
>>> }
>>> }
>>> class Generations: Form
>>> {
>>> /**************************************************************************/
>>> /* CREAZIONE CONTROLLI PRESENTI NEL FORM
>>> */
>>> /**************************************************************************/
>>>
>>> // SQL Server database objects
>>> public SqlConnection cn;
>>> public SqlDataAdapter daGenerations;
>>> public DataSet dsTablesSet;
>>> public DataTable dtGenerations;
>>> public CurrencyManager cmGenerations;
>>>
>>> // Graphical objects (Labels, TextBoxes and Buttons)
>>> public Label lblIDPerson, lblNamePerson, lblAgePerson, lblIDParent;
>>> public TextBox txtIDPerson, txtNamePerson, txtAgePerson,
>>> txtIDParent;
>>> public Button btnPreviousPerson, btnNextPerson, btnAddPerson,
>>> btnEditPerson, btnDeletePerson, btnOk, btnCancel, btnClose;
>>>
>>>
>>> public Generations()
>>> {
>>> // Form dimension and position
>>> Size = new System.Drawing.Size(570, 300);
>>> Text = "Generations";
>>> CenterToScreen();
>>>
>>> // SQL Server objects istantiation and initialization
>>> cn = new SqlConnection();
>>> daGenerations = new SqlDataAdapter();
>>> dsTablesSet = new DataSet();
>>> dtGenerations = new DataTable();
>>>
>>> dsTablesSet.DataSetName = "TablesSet";
>>>
>>> // Table "Generations"
>>> dtGenerations.TableName = "Generations";
>>>
>>> dtGenerations.Columns.Add("IDPerson", typeof(int));
>>> dtGenerations.Columns["IDPerson"].AutoIncrement = true;
>>> dtGenerations.Columns["IDPerson"].AutoIncrementSeed = 1;
>>> dtGenerations.Columns["IDPerson"].AutoIncrementStep = 1;
>>>
>>> dtGenerations.Columns.Add("NamePerson", typeof(string));
>>> dtGenerations.Columns["NamePerson"].AllowDBNull = false;
>>> dtGenerations.Columns["NamePerson"].DefaultValue = "";
>>>
>>> dtGenerations.Columns.Add("AgePerson", typeof(int));
>>> dtGenerations.Columns["AgePerson"].AllowDBNull = false;
>>>
>>> dtGenerations.Columns.Add("IDParent", typeof(int));
>>>
>>> dtGenerations.PrimaryKey = new DataColumn[] {
>>> dtGenerations.Columns["IDPerson"] };
>>>
>>> dsTablesSet.Tables.Add(dtGenerations);
>>>
>>> // "Generations" inner relation
>>> dsTablesSet.Relations.Add("Generations_ParentSon",
>>> dtGenerations.Columns["IDPerson"], dtGenerations.Columns["IDParent"]);
>>>
>>> cn.ConnectionString = "Persist Security Info=False;Integrated
>>> Security=SSPI;database=Generations;server=(local)\\TECLOGICA;Connect
>>> Timeout=10";
>>>
>>> string strSQL;
>>> SqlParameterCollection pc;
>>> SqlParameter param;
>>>
>>> // "Generations" UPDATE, INSERT, DELETE logic
>>> strSQL = "SELECT IDPerson, NamePerson, AgePerson, IDParent FROM
>>> Generations ORDER BY IDPerson";
>>> daGenerations.SelectCommand = new SqlCommand(strSQL, cn);
>>>
>>> strSQL = "UPDATE Generations SET NamePerson = @NamePerson_New,
>>> AgePerson = @AgePerson_New, IDParent = @IDParent_New WHERE IDPerson =
>>> @IDPerson_Orig";
>>> daGenerations.UpdateCommand = new SqlCommand(strSQL, cn);
>>> pc = daGenerations.UpdateCommand.Parameters;
>>>
>>> pc.Add("@NamePerson_New", SqlDbType.NVarChar, 50,
>>> "NamePerson");
>>> pc.Add("@AgePerson_New", SqlDbType.Int, 0, "AgePerson");
>>> pc.Add("@IDParent_New", SqlDbType.Int, 0, "IDParent");
>>>
>>> param = pc.Add("@IDPerson_Orig", SqlDbType.Int, 0, "IDPerson");
>>> param.SourceVersion = DataRowVersion.Original;
>>>
>>> strSQL = "SET IDENTITY_INSERT Generations ON; INSERT INTO
>>> Generations (IDPerson, NamePerson, AgePerson, IDParent) VALUES
>>> (@IDPerson, @NamePerson, @IDParent); SET IDENTITY_INSERT Generations
>>> OFF";
>>> daGenerations.InsertCommand = new SqlCommand(strSQL, cn);
>>> pc = daGenerations.InsertCommand.Parameters;
>>> pc.Add("@IDPerson", SqlDbType.Int, 0, "IDPerson");
>>> pc.Add("@NamePerson", SqlDbType.NVarChar, 50, "NamePerson");
>>> pc.Add("@AgePerson", SqlDbType.Int, 0, "AgePerson");
>>> pc.Add("@IDParent", SqlDbType.Int, 0, "IDParent");
>>>
>>> strSQL = "DELETE FROM Generations WHERE IDPerson = @IDPerson";
>>> daGenerations.DeleteCommand = new SqlCommand(strSQL, cn);
>>> pc = daGenerations.DeleteCommand.Parameters;
>>> pc.Add("@IDPerson", SqlDbType.Int, 0, "IDPerson");
>>>
>>> daGenerations.Fill(dsTablesSet, "Generations");
>>>
>>> // Initialize CurrencyManager object
>>> cmGenerations = (CurrencyManager)BindingContext[dsTablesSet,
>>> "Generations"];
>>>
>>> // Graphical objects istantiation and initialization
>>> lblIDPerson = new Label();
>>> lblIDPerson.Parent = this;
>>> lblIDPerson.Size = new Size(120, 25);
>>> lblIDPerson.Location = new Point(10, 10);
>>> lblIDPerson.Text = "ID";
>>>
>>> txtIDPerson = new TextBox();
>>> txtIDPerson.Parent = this;
>>> txtIDPerson.Size = new Size(120, 25);
>>> txtIDPerson.Location = new Point(lblIDPerson.Left,
>>> lblIDPerson.Bottom);
>>> txtIDPerson.Enabled = false;
>>> txtIDPerson.DataBindings.Add("Text", dsTablesSet,
>>> "Generations.IDPerson");
>>>
>>> lblNamePerson = new Label();
>>> lblNamePerson.Parent = this;
>>> lblNamePerson.Size = new Size(120, 25);
>>> lblNamePerson.Location = new Point(lblIDPerson.Right + 20,
>>> lblIDPerson.Top);
>>> lblNamePerson.Text = "Name";
>>>
>>> txtNamePerson = new TextBox();
>>> txtNamePerson.Parent = this;
>>> txtNamePerson.Size = new Size(120, 25);
>>> txtNamePerson.Location = new Point(lblNamePerson.Left,
>>> lblNamePerson.Bottom);
>>> txtNamePerson.Enabled = false;
>>> txtNamePerson.DataBindings.Add("Text", dsTablesSet,
>>> "Generations.NamePerson");
>>>
>>> lblAgePerson = new Label();
>>> lblAgePerson.Parent = this;
>>> lblAgePerson.Size = new Size(120, 25);
>>> lblAgePerson.Location = new Point(lblNamePerson.Right + 20,
>>> lblNamePerson.Top);
>>> lblAgePerson.Text = "Age";
>>>
>>> txtAgePerson = new TextBox();
>>> txtAgePerson.Parent = this;
>>> txtAgePerson.Size = new Size(120, 25);
>>> txtAgePerson.Location = new Point(lblAgePerson.Left,
>>> lblAgePerson.Bottom);
>>> txtAgePerson.Enabled = false;
>>> txtAgePerson.DataBindings.Add("Text", dsTablesSet,
>>> "Generations.AgePerson");
>>>
>>> lblIDParent = new Label();
>>> lblIDParent.Parent = this;
>>> lblIDParent.Size = new Size(120, 25);
>>> lblIDParent.Location = new Point(lblAgePerson.Right + 20,
>>> lblAgePerson.Top);
>>> lblIDParent.Text = "IDParent";
>>>
>>> txtIDParent = new TextBox();
>>> txtIDParent.Parent = this;
>>> txtIDParent.Size = new Size(120, 25);
>>> txtIDParent.Location = new Point(lblIDParent.Left,
>>> lblIDParent.Bottom);
>>> txtIDParent.Enabled = false;
>>> txtIDParent.DataBindings.Add("Text", dsTablesSet,
>>> "Generations.IDParent");
>>>
>>> btnPreviousPerson = new Button();
>>> btnPreviousPerson.Parent = this;
>>> btnPreviousPerson.Size = new Size(120, 25);
>>> btnPreviousPerson.Location = new Point(150, 100);
>>> btnPreviousPerson.Text = "Previous";
>>> btnPreviousPerson.Click += new
>>> EventHandler(btnPreviousPerson_Click);
>>>
>>> btnNextPerson = new Button();
>>> btnNextPerson.Parent = this;
>>> btnNextPerson.Size = new Size(120, 25);
>>> btnNextPerson.Location = new Point(btnPreviousPerson.Right +
>>> 20, btnPreviousPerson.Top);
>>> btnNextPerson.Text = "Next";
>>> btnNextPerson.Click += new EventHandler(btnNextPerson_Click);
>>>
>>> btnAddPerson = new Button();
>>> btnAddPerson.Parent = this;
>>> btnAddPerson.Size = new Size(120, 25);
>>> btnAddPerson.Location = new Point(80, 160);
>>> btnAddPerson.Text = "Add";
>>> btnAddPerson.Click += new EventHandler(btnAddPerson_Click);
>>>
>>> btnEditPerson = new Button();
>>> btnEditPerson.Parent = this;
>>> btnEditPerson.Size = new Size(120, 25);
>>> btnEditPerson.Location = new Point(btnAddPerson.Right + 20,
>>> btnAddPerson.Top);
>>> btnEditPerson.Text = "Edit";
>>> btnEditPerson.Click += new EventHandler(btnEditPerson_Click);
>>>
>>> btnDeletePerson = new Button();
>>> btnDeletePerson.Parent = this;
>>> btnDeletePerson.Size = new Size(120, 25);
>>> btnDeletePerson.Location = new Point(btnEditPerson.Right + 20,
>>> btnEditPerson.Top);
>>> btnDeletePerson.Text = "Delete";
>>> btnDeletePerson.Click += new
>>> EventHandler(btnDeletePerson_Click);
>>>
>>> btnOk = new Button();
>>> btnOk.Parent = this;
>>> btnOk.Size = new Size(120, 25);
>>> btnOk.Location = new Point(150, 220);
>>> btnOk.Text = "OK";
>>> btnOk.Visible = false;
>>> btnOk.Click += new EventHandler(btnOk_Click);
>>>
>>> btnCancel = new Button();
>>> btnCancel.Parent = this;
>>> btnCancel.Size = new Size(120, 25);
>>> btnCancel.Location = new Point(btnOk.Right + 20, btnOk.Top);
>>> btnCancel.Text = "Cancel";
>>> btnCancel.Visible = false;
>>> btnCancel.Click += new EventHandler(btnCancel_Click);
>>>
>>> btnClose = new Button();
>>> btnClose.Parent = this;
>>> btnClose.Size = new Size(120, 25);
>>> btnClose.Location = new Point(420, 220);
>>> btnClose.Text = "Close";
>>> btnClose.Click += new EventHandler(btnClose_Click);
>>> }
>>>
>>> void btnAddPerson_Click(object sender, EventArgs e)
>>> {
>>> cmGenerations.AddNew();
>>>
>>> txtNamePerson.Enabled = true;
>>> txtIDParent.Enabled = true;
>>>
>>> btnPreviousPerson.Visible = false;
>>> btnNextPerson.Visible = false;
>>> btnAddPerson.Visible = false;
>>> btnEditPerson.Visible = false;
>>> btnDeletePerson.Visible = false;
>>>
>>> btnOk.Visible = true;
>>> btnCancel.Visible = true;
>>> }
>>>
>>> void btnEditPerson_Click(object sender, EventArgs e)
>>> {
>>> txtNamePerson.Enabled = true;
>>> txtIDParent.Enabled = true;
>>>
>>> btnPreviousPerson.Visible = false;
>>> btnNextPerson.Visible = false;
>>> btnAddPerson.Visible = false;
>>> btnEditPerson.Visible = false;
>>> btnDeletePerson.Visible = false;
>>>
>>> btnOk.Visible = true;
>>> btnCancel.Visible = true;
>>> }
>>>
>>> void btnDeletePerson_Click(object sender, EventArgs e)
>>> {
>>> cmGenerations.RemoveAt(cmGenerations.Position);
>>> daGenerations.Update(dtGenerations);
>>> }
>>>
>>> void btnPreviousPerson_Click(object sender, EventArgs e)
>>> {
>>> if (cmGenerations.Position > 0)
>>> cmGenerations.Position--;
>>> }
>>>
>>> void btnNextPerson_Click(object sender, EventArgs e)
>>> {
>>> if (cmGenerations.Position < cmGenerations.Count-1)
>>> cmGenerations.Position++;
>>> }
>>>
>>> void btnOk_Click(object sender, EventArgs e)
>>> {
>>> cmGenerations.EndCurrentEdit();
>>> daGenerations.Update(dtGenerations);
>>>
>>> txtNamePerson.Enabled = false;
>>> txtIDParent.Enabled = false;
>>>
>>> btnOk.Visible = false;
>>> btnCancel.Visible = false;
>>>
>>> btnPreviousPerson.Visible = true;
>>> btnNextPerson.Visible = true;
>>> btnAddPerson.Visible = true;
>>> btnEditPerson.Visible = true;
>>> btnDeletePerson.Visible = true;
>>> }
>>>
>>> void btnCancel_Click(object sender, EventArgs e)
>>> {
>>> cmGenerations.CancelCurrentEdit();
>>>
>>> txtNamePerson.Enabled = false;
>>> txtIDParent.Enabled = false;
>>>
>>> btnOk.Visible = false;
>>> btnCancel.Visible = false;
>>>
>>> btnPreviousPerson.Visible = true;
>>> btnNextPerson.Visible = true;
>>> btnAddPerson.Visible = true;
>>> btnEditPerson.Visible = true;
>>> btnDeletePerson.Visible = true;
>>> }
>>>
>>> void btnClose_Click(object sender, EventArgs e)
>>> {
>>> Close();
>>> }
>>> }
>>>
>>
>>
>
>

Hi Clinton,

I don't know if indexing the FK in SQL Server is a good idea or not. At
work, most of my database designs end up in Oracle. About six months ago,
we had occassion to implement CASCADE DELETE foreign keys, and the DBA (who
is a sharp guy) insisted that I put an index on the foreign key column.

He had seen cases where top level deletes became quite slow because of all
the additional deletes that were happening through the foreign keys.. Keep
in mind that this was a rather unusual situation, because we were talking
about a reporting instancethat was programatically updated (including
deletes) from another source. The code could specify a ton of deletes in a
very short time.

In the case of users specifying a deletes, I can't see that many deletes
happening at once.

Cascade Deletes frequently make me a little queasy, because often people try
to delete a parent without thinking of all the historical information they
are going to delete. A NO ACTION (RESTRICT) foreign key will prevent this,
and force the person to explicitly delete the child rows first. A CASCADE
key will allow the user to charge ahead.

Thanks,

Mac


"clintonG" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Very helpful mac. I found this [1] while rooting around which briefly
> describes and links to the enhanced 2005 feature set noting cascading has
> been updated and may be relative to this news article.
>
> I don't know better at the moment but I'd rather use CASCADE DELETE and
> let the database clean unwanted rows from related tables. Were you
> inferring the FK in the related table should be indexed when using MSSQL?
>
> <%= Clinton Gallagher
>
> [1] http://msdn2.microsoft.com/en-us/library/ms170355.aspx
>

Good points to ponder. Not knowing a better strategy, in the past I've put a
flag in the top level table and simply changed the value of the flag and
referenced the value as a business rule to indicate the "authorization" to
enable or disable access to the parent row and all related tables. Contrary
to convention it did allow me to retain historical data in the database.

<%= Clinton



"mac" <(E-Mail Removed)> wrote in message
news:Onzwg.15196$Nv.6324@fed1read10...
> Hi Clinton,
>
> I don't know if indexing the FK in SQL Server is a good idea or not. At
> work, most of my database designs end up in Oracle. About six months ago,
> we had occassion to implement CASCADE DELETE foreign keys, and the DBA
> (who is a sharp guy) insisted that I put an index on the foreign key
> column.
>
> He had seen cases where top level deletes became quite slow because of all
> the additional deletes that were happening through the foreign keys..
> Keep in mind that this was a rather unusual situation, because we were
> talking about a reporting instancethat was programatically updated
> (including deletes) from another source. The code could specify a ton of
> deletes in a very short time.
>
> In the case of users specifying a deletes, I can't see that many deletes
> happening at once.
>
> Cascade Deletes frequently make me a little queasy, because often people
> try to delete a parent without thinking of all the historical information
> they are going to delete. A NO ACTION (RESTRICT) foreign key will prevent
> this, and force the person to explicitly delete the child rows first. A
> CASCADE key will allow the user to charge ahead.
>
> Thanks,
>
> Mac
>
>
> "clintonG" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Very helpful mac. I found this [1] while rooting around which briefly
>> describes and links to the enhanced 2005 feature set noting cascading has
>> been updated and may be relative to this news article.
>>
>> I don't know better at the moment but I'd rather use CASCADE DELETE and
>> let the database clean unwanted rows from related tables. Were you
>> inferring the FK in the related table should be indexed when using MSSQL?
>>
>> <%= Clinton Gallagher
>>
>> [1] http://msdn2.microsoft.com/en-us/library/ms170355.aspx
>>
>
>