Figure 1

Here I have created the Entity Business object so that it becomes easy to understand the code. Its little complex process to understand, if you have any question, please let me know.

Let’s understand the few points here

Step 1: Code for the Primary Table:

_adpPrimary = new SqlDataAdapter(new SqlCommand("Select * from tPrimary where ID = " + _id, DBSourceConnection));

//Point 1 – Creating the insert command and attaching with insert stored procedure.

_adpPrimary.InsertCommand = new SqlCommand("sp_InsertPrimaryEntity", DBSourceConnection);

SqlCommand cmdInsert = _adpPrimary.InsertCommand;

cmdInsert.CommandType = CommandType.StoredProcedure;

cmdInsert.Parameters.Add(new SqlParameter("@ID", SqlDbType.BigInt));

cmdInsert.Parameters["@ID"].Direction = ParameterDirection.Output;//point 2 creating the out paramter

cmdInsert.Parameters["@ID"].SourceColumn = "ID";

cmdInsert.Parameters.Add(new SqlParameter("@PDescription", SqlDbType.VarChar, 50));

cmdInsert.Parameters["@PDescription"].SourceColumn = "PDescription";

//Point 5: Fill the schema into the DataTable

_adpPrimary.FillSchema(_dsEntity, SchemaType.Source);

//rename the table name in the dataset

_dtPrimryEntity = _dsEntity.Tables["Table"];

_dtPrimryEntity.TableName = "tPrimary";

//if _ID is a vaild number then fetch the data from datbase.

if (_id > 0)

     _adpPrimary.Fill(_dsEntity, "tPrimary");

//Point 4: if ID is of identity type of column then set the auto increament seed //and step -1 so that it does not conflict with the exiting rows in the //database

_dtPrimryEntity.Columns["ID"].AutoIncrementSeed = -1;

_dtPrimryEntity.Columns["ID"].AutoIncrementStep = -1;

//Point 1: creating update command

_adpPrimary.UpdateCommand = new SqlCommand("sp_UpdatePrimaryEntity", DBSourceConnection);

SqlCommand cmdupdate = _adpPrimary.UpdateCommand;

cmdupdate.CommandType = CommandType.StoredProcedure;

cmdupdate.Parameters.Add(new SqlParameter("@ID", SqlDbType.BigInt));

cmdupdate.Parameters["@ID"].SourceColumn = "ID";

cmdupdate.Parameters.Add(new SqlParameter("@PDescription", SqlDbType.VarChar, 50));

cmdupdate.Parameters["@PDescription"].SourceColumn = "PDescription";

//Point 1: creating Delete command

_adpPrimary.DeleteCommand = new SqlCommand("sp_DeletePrimaryEntity", DBSourceConnection);

SqlCommand cmdDelete = _adpPrimary.DeleteCommand;

cmdDelete.CommandType = CommandType.StoredProcedure;

cmdDelete.Parameters.Add(new SqlParameter("@ID", SqlDbType.BigInt));

Similarly fetch and create the insert, update and delete command for the foreign table.

Step 2: Code for Foreign Table:

_adpForeign = new SqlDataAdapter(new SqlCommand("Select * from tForeign where ID = " + _id, DBSourceConnection));

_adpForeign.InsertCommand = new SqlCommand("sp_InsertForeignEntity", DBSourceConnection);

SqlCommand cmdForgienInsert = _adpForeign.InsertCommand;

cmdForgienInsert.CommandType = CommandType.StoredProcedure;

cmdForgienInsert.Parameters.Add(new SqlParameter("@ID", SqlDbType.BigInt));

cmdForgienInsert.Parameters["@ID"].SourceColumn = "ID";

cmdForgienInsert.Parameters.Add(new SqlParameter("@SDescription", SqlDbType.VarChar, 50));

                    cmdForgienInsert.Parameters["@SDescription"].SourceColumn = "SDescription";

_adpForeign.FillSchema(_dsEntity, SchemaType.Source);

//rename the table name in the dataset

_dtForeignEntity = _dsEntity.Tables["Table"];

_dtForeignEntity.TableName = "tForeign";

//if _ID is a vaild number then fetch the data from datbase.

if (_id > 0)

    _adpForeign.Fill(_dsEntity, "tForeign");

_adpForeign.UpdateCommand = new SqlCommand("sp_UpdateForeignEntity", DBSourceConnection);

SqlCommand cmdForeignUpdate = _adpForeign.UpdateCommand;

cmdForeignUpdate.CommandType = CommandType.StoredProcedure;

cmdForeignUpdate.Parameters.Add(new SqlParameter("@ID", SqlDbType.BigInt));

cmdForeignUpdate.Parameters["@ID"].SourceColumn = "ID";

cmdForeignUpdate.Parameters.Add(new SqlParameter("@SDescription", SqlDbType.VarChar, 50));

                    cmdForeignUpdate.Parameters["@SDescription"].SourceColumn = "SDescription";

_adpForeign.DeleteCommand = new SqlCommand("sp_DeleteForeignEntity", DBSourceConnection);

SqlCommand cmdForeignDelete = _adpForeign.DeleteCommand;

cmdForeignDelete.CommandType = CommandType.StoredProcedure;

cmdForeignDelete.Parameters.Add(new SqlParameter("@ID", SqlDbType.BigInt));

cmdForeignDelete.Parameters["@ID"].SourceColumn = "ID";

Step 3: Add the relation in bet the primary and foreign table

//set the relationship in between the primary and foreign data table

DataRelation rel = new DataRelation("PrimaryToForeign", _dtPrimryEntity.Columns["ID"], _dtForeignEntity.Columns["ID"]);

                    _dsEntity.Relations.Add(rel);

Step 4: Write a method to update changes into the database:

SqlTransaction trans = null;

try

{

    trans = _sqlConnection.BeginTransaction();

    _adpPrimary.InsertCommand.Transaction = trans;

    _adpPrimary.UpdateCommand.Transaction = trans;

    _adpPrimary.DeleteCommand.Transaction = trans;

    _adpForeign.InsertCommand.Transaction = trans;

    _adpForeign.UpdateCommand.Transaction = trans;

    _adpForeign.DeleteCommand.Transaction = trans;

    _adpPrimary.Update(_dsEntity, "tPrimary");

    _adpForeign.Update(_dsEntity, "tForeign");

    _dsEntity.AcceptChanges();

    trans.Commit();

}

catch (Exception ex)

{

    if (trans != null)

        trans.Rollback();

    System.Diagnostics.Trace.WriteLine(ex);

}

Step 5: To delete the data if required:

If you see in the above code I am calling update method first on primary table but in delete I have to do it in reverse. I am deleting all the records from the business object.

SqlTransaction trans = null;

 trans = _sqlConnection.BeginTransaction();

 _adpPrimary.InsertCommand.Transaction = trans;

 _adpPrimary.UpdateCommand.Transaction = trans;

 _adpPrimary.DeleteCommand.Transaction = trans;

 _adpForeign.InsertCommand.Transaction = trans;

 _adpForeign.UpdateCommand.Transaction = trans;

 _adpForeign.DeleteCommand.Transaction = trans;

 foreach (DataRow dr in _dtPrimryEntity.Rows)

 {

            DataRow[] chRows = dr.GetChildRows("PrimaryToForeign");

            foreach (DataRow drch in chRows)

            {

                drch.Delete();

            }

            dr.Delete();

        }

        //delete child rows first

        _adpForeign.Update(_dsEntity, "tForeign");

        _adpPrimary.Update(_dsEntity, "tPrimary");

        _dsEntity.AcceptChanges();

        trans.Commit();

}

catch (Exception ex)

{

    if (trans != null)

        trans.Rollback();

    System.Diagnostics.Trace.WriteLine(ex);

}

Now, from step 6 to step 9 we would see how to use the entity class in the code to fetch, update, delete and insert records.

Step 6: Code to fetch the data:

long id = Convert.ToInt64(txtID.Text);

SampleBOEntity boEntity = new SampleBOEntity(id);

System.Windows.MessageBox.Show("data for id = " + id.ToString() + " has been fetched successfully");

Step 7: Code to Add records:I am inserting 2 primary rows and its children rows and updating to the database.

SampleBOEntity boEntity = new SampleBOEntity(-1);

DataSet dsEntity = boEntity.EntityDataSet;

//adding First Relation Row

DataRow drParent = dsEntity.Tables["tPrimary"].NewRow();

drParent["PDescription"] = "Adding First Row";

DataRow drChild = dsEntity.Tables["tForeign"].NewRow();

drChild["SDescription"] = "Child of First Row";

drChild.SetParentRow(drParent);

dsEntity.Tables["tPrimary"].Rows.Add(drParent);

dsEntity.Tables["tForeign"].Rows.Add(drChild);

//adding Second Relation Row along with 2 foreign rows

DataRow drParent2 = dsEntity.Tables["tPrimary"].NewRow();

drParent2["PDescription"] = "Adding Second Row";

DataRow drChild2 = dsEntity.Tables["tForeign"].NewRow();

drChild2["SDescription"] = "Child of Second Row";

drChild2.SetParentRow(drParent2);

DataRow drChild3 = dsEntity.Tables["tForeign"].NewRow();

drChild3["SDescription"] = "Another Child of Second Row";

drChild3.SetParentRow(drParent2);

dsEntity.Tables["tPrimary"].Rows.Add(drParent2);

dsEntity.Tables["tForeign"].Rows.Add(drChild2);

dsEntity.Tables["tForeign"].Rows.Add(drChild3);

boEntity.SaveEntity();

System.Windows.MessageBox.Show("data saved to databse successfully");

Step 8: Code to fetch and make amendment in the rows:

long id = Convert.ToInt64(txtID.Text);

SampleBOEntity boEntity = new SampleBOEntity(id);

DataSet dsEntity = boEntity.EntityDataSet;

foreach (DataRow dr in dsEntity.Tables["tPrimary"].Rows)

{

    DataRow[] chRows = dr.GetChildRows("PrimaryToForeign");

    dr.BeginEdit();

    foreach (DataRow drch in chRows)

    {

        drch.BeginEdit();

        drch["SDescription"] = "Updated Foreign Row";

        drch.EndEdit();

    }

    dr["Pdescription"] = "Update Primary Row";

    dr.EndEdit();

}

boEntity.SaveEntity();

System.Windows.MessageBox.Show("data saved to databse successfully");

Step 9: Code to fetch and delete the rows:

long id = Convert.ToInt64(txtID.Text);

SampleBOEntity boEntity = new SampleBOEntity(id);

boEntity.Delete();

System.Windows.MessageBox.Show("Records deleted from databse successfully");

Summary: Something in the software development needs comes when some data only make sense when it exists in group such as primary table data and secondary table date. It is very difficult to make changes in the database for identity columns along with Primary and Foreign table. Here we have learnt how easily we can achieve this functionality with ADO.NET.

Note: Download the attached zip file for the sample code and backup of the database used in the sample. Restore the a2zdotnet database into the SQLExpress edition from the backup file and run the sample.

Reference: