June 1, 2018

Ado.Net - Setting-up Foreign Key Relation

1. Introduction to Ado.net Foreign Key

In “Relational Databases” like Oracle and SQL Server, a “Foreign Key” always refers to a Primary Key in a table. This establishes a relationship between two tables which is called as “Referential Integrity”. We know that a table can have only one Primary Key and can have one or more Unique Keys. In Ado.Net a Foreign Key is not restricted to refer only to Primary Key. That means, it can refer to a Unique Key column also.

In this article, we will see an example that demonstrates establishing a Foreign Key relation on 
“DataTables”. We will also explore how “Cascading” option works.

2. About the Example


The example we are going to create is shown below:

Ado.Net ForeignKey Example
Ado.Net ForeignKey Example

In this example, we are going to use Microsoft SQL Server as backend and also we need the Pubs database. One can download it from the internet or the SQL Script for creating this database is available in the SQL 2005 section of this website.
The controls numbered 1 and 2 are DataGridView Controls. The first DataGridView Control is used to display the Publishers information from the Pubs database. The second DataGridView Control is used to display the Employees information. In this Ado.net code example, we are going to set Primary Key - Foreign Key relationship between these two DataGridViews.
The “Load Data” (Marked as 3) will load the data into both the grids. So, the initial data is loaded without any Relationship between the grids. When the “Enable FKey” (Marked as 4) is checked, a foreign key is assigned to pub_id of the Employees grid and it points to the Primary Key on the Publishers. This will create the Referential Integrity between these grids.
By default, the “Cascade” (Marked as 5) checkbox is checked and when we remove the check-mark, we will be removing the "Cascading Effect" which we will see while progressing with the article. The “Clear” button will reset the form so that we can start our experiment a Fresh.

3. Configure the Database

First, let us connect to the SQL Server Database and try running the below two queries.

Query 1:
Select * from publishers;

Query 2:
SELECT 
  publishers.pub_name, employee.emp_id, 
  employee.fname + ' ' + employee.minit 
  + ' ' + employee.lname  as EmpName,
  employee.pub_id,  
  jobs.job_desc
FROM    
  employee 
  INNER JOIN jobs ON employee.job_id = jobs.job_id 
  INNER JOIN publishers ON employee.pub_id = publishers.pub_id
Order By Pub_Name;



Once we can run the above queries in SQL Server Management Studio and able to see the results, we in good shape to use the queries in our ado.net example. The first query is used by the publisher data grid. And, the second query is used by Employees data grid. Next, we must set a connection to our SQL Server as an application property. The video below helps to set the connection string. Set the “Connection String” name it as PubCon.


Video 1: Setting Connection to SQL Server Database



4. Setting up the Form

1) Include the required namespace to the project.
//Snippet 00: Namespace required for this Sample
using System.Data.SqlClient;


2) Next, we declare global “DataSet” variable called PkFkDS. In this DataSet, we are going to hold data for both Publishers and Employees data grid. 

//Snippet 01: Global DataSet
private DataSet PkFkDS = null;

3) In the form load handler, we allow access to Load button only. This is to avoid validation overhead and also to allow the form navigation in a specific sequence. 
private void PKeyFKey_Load(object sender, EventArgs e)
{
    //Snippet 02: Disable the Checkboxes and Clear buttons
    btnClear.Enabled = false;
    chkKeys.Enabled = false;
    chkCascade.Enabled = false;
}


4) We are resetting the controls when clear button is clicked. Note that we are also clearing the DataSet. Since the DataSet supply data to the Grids, the grids will get cleared through the DataSet function calls “Clear()” and “Reset()”. The clear button will leave the form to its initial state when it got loaded.

private void btnClear_Click(object sender, EventArgs e)
{
    //Snippet 03: Reset the Controls
    PkFkDS.Clear();
    PkFkDS.Reset();
    btnClear.Enabled = false;
    chkKeys.Enabled = false;
    chkCascade.Enabled = false;
    btnLoadData.Enabled = true;
}


5. Loading Data into Parent and Child DataGridViews


The load button will load the data into the Publisher and Employees DataGridView controls. This time, we are going to apply only Primary Key Constraint on the pubs-id column of the Publisher. Towards the end of this section, we will explore the behavior when there is no Referential Integrity.

5.1 Creating DataTables


1) The first thing we should do is avoiding the Multiple Loads without clearing the DataSet. So, disable the Load button and we know that it will get enabled in the Close button handler. The chkKeys CheckBox is enabled as it will allow applying the constraints when it is ticked. We want to do that once data is Loaded. 

//Snippet 04: Load the Data
private void btnLoadData_Click(object sender, EventArgs e)
{
    //Snippet 4.1: Enable Close. Avoid Multiple click of Load
    btnClear.Enabled = true;
    chkKeys.Enabled = true;
    btnLoadData.Enabled = false;


2) The “SqlConnection” object Con is created based on the Connection String. This connection string is configured as part of application setting (Refer Video 1). 

//Snippet 4.2: Create Connection to SQL Server 
SqlConnection Con = new SqlConnection(
    Pkey_Fkey.Properties.Settings.Default.PubsCon);


3) Next, we create two “DataTables” called dtabPublishers and dtabEmployees. Through “SqlDataAdapter::Fill()” method, we are populating both the DataTables with Query1 and Query2 results which we already explored in Section 3.

string SqlQuery1 = "Select * from publishers";
string SqlQuery2 = 
"SELECT publishers.pub_name, " +
"employee.emp_id, employee.fname + ' ' + employee.minit " +
"+ ' ' + employee.lname  as EmpName, employee.pub_id, " + 
"jobs.job_desc " +
"FROM employee " +
"INNER JOIN jobs ON employee.job_id = jobs.job_id " +
"INNER JOIN publishers ON employee.pub_id = publishers.pub_id " +
"Order By Pub_Name"; 
DataTable dtabPublisher = new DataTable("publishers");
DataTable dtabEmployees = new DataTable("Employees");
SqlDataAdapter adaptor1 = new SqlDataAdapter(SqlQuery1, Con);
SqlDataAdapter adaptor2 = new SqlDataAdapter(SqlQuery2, Con);
adaptor1.Fill(dtabPublisher);
adaptor2.Fill(dtabEmployees);

5.2 Adding DataTables to DataSet

Once data is packed into the DataTables, we are creating our global DataSet object and then adding both the DataTables to it through its Add() method of the “Tables Collection”. When we created the DataTable, we named it by passing the string value to its constructor. This naming can be used, while we want to retrieve the DataTable from the Tables collection. 

//Snippet 4.4: Add DataTables to DataSet
PkFkDS = new DataSet();
PkFkDS.Tables.Add(dtabPublisher);
PkFkDS.Tables.Add(dtabEmployees);

5.3 Binding DataGridView

The “DataSource Property” of the DataGridView is set with the DataTables that we created earlier. Note that we are referring our DataTable from the Tables collection using Named Index like Tables[<DataTable-Name>]. Once this “Data Binding” is done, both the DataGridViews shows the table of data. 

//Snippet 4.5: Bind DataTable to DataViews
GridViewPub.DataSource = PkFkDS.Tables["Publishers"];
GridViewEmp.DataSource = PkFkDS.Tables["Employees"];

5.4 Apply PrimaryKey

The “Constraints Collection” of the DataTable is used to define the constraints on one or more columns. We are first taking the Publishers table from the DataSet and then adding a PrimaryKey constraint to the pub_id column. We named our key as “PKey-PubId”. After adding the key, we do the clean-up.

//Snippet 4.6: Set Primary Key Cobstraint on Publisher
//DataTable
DataTable dtabPub = PkFkDS.Tables["publishers"];
dtabPub.Constraints.Add(
    "PKey-PubId",
    dtabPub.Columns["pub_id"],
    true);
//Snippet 4.7: Clean-Up Connection and Adaptors
Con.Close();
adaptor1.Dispose();
adaptor2.Dispose();


Now let us examine the behavior of the application. So far we had written code to populate the DataGridView Controls. Since there is no Foreign key relation, we can add any data to the Employees table. Ado.Net doesn’t complain about the data integrity between Parent (Publisher) and Child(Employees). The behavior is recorded in the below video.


Video 2: Parent-Child behavior without Foreign Key

6. Adding and Removing Foreign Key Constraint


The Constraints Collection of the DataTable is used to Add or remove the constraint dynamically. The DataGridView which bound to the DataTable will recognize these changes automatically. The “Add()” function overload requires both Referring and Referred Columns to establish the Parent-Child relationship through Foreign Key. The “Remove()” function is used to remove the already added constraints. 


The “Contains()” function is used to check whether a constraint is part of the Constraints Collection or not. This function is useful when we are adding or removing a constraint. While adding the constraint, it is safe to check that there should not be any constraint with the same name. The same way, while removing we should make sure that the constraint we want to remove exits in the DataTable. 


All right. Let us come to the point. When the “Enable FKey” checkbox is ticked, we should enable the Foreign Key constraint on the pub-id column of the Employees DataTable. Furthermore, we should remove the Foreign Key dynamically when the tick is removed.


6.1 Add Foreign Key Column


In the “CheckedChanged” event handler of the checkbox, based on the “CheckState”, either we add or remove the Foreign Key constraint. In the below code, first, we take the DataTable from the DataSet and then add the ForeignKey Constraint to it. Note that we named the constraint as “FKey-PubId”. This name assigned to the Constraint is useful when we want to remove it at later point of time. The second parameter of the Add() function tells the Parent Table’s Column (The Referred Column) and the third parameter tells the Foreign Key column (Referring Column) in the Child Table. 
//Snippet 5.1: Add Foreign Key Constraint
DataTable dtabEmp = PkFkDS.Tables["Employees"];
DataTable dtabPub = PkFkDS.Tables["publishers"];
try
{
    if (!dtabEmp.Constraints.Contains("FKey-PubId"))
        dtabEmp.Constraints.Add(
            "FKey-PubId",
            dtabPub.Columns["pub_id"],
            dtabEmp.Columns["pub_id"]);
}
catch (Exception Ex)
{
    MessageBox.Show(Ex.Message);
    chkKeys.Checked = false;
}


6.2 Remove Foreign Key Column


The same Constraint Collection is used to remove the Foreign Constraint. We pass the Constraint Name to the Remove() function and before that, we make sure that the Constraint named “FKey-PudId” exists in the DataTable. Both Add() and Remove() function calls are wrapped around the Try-Catch block because we may end-up with the run-time failures caused by Parent-Child integrity violations.
//Snippet 5.3: Remove Foreign Key Constrains
DataTable dtabEmp = PkFkDS.Tables["Employees"];
try
{
    if (dtabEmp.Constraints.Contains("FKey-PubId"))
        dtabEmp.Constraints.Remove("FKey-PubId");
}
catch (ConstraintException Ex)
{
    MessageBox.Show(Ex.Message);
}

The complete code is Below:
private void chkKeys_CheckedChanged(object sender, EventArgs e)
{
    //Snippet 05: Add/Remove Foreign Key
    if (chkKeys.CheckState == CheckState.Checked)
    {
        chkCascade.Enabled = true;
        
        //Snippet 5.1: Add Foreign Key Constraint
        DataTable dtabEmp = PkFkDS.Tables["Employees"];
        DataTable dtabPub = PkFkDS.Tables["publishers"];
        try
        {
            if (!dtabEmp.Constraints.Contains("FKey-PubId"))
                dtabEmp.Constraints.Add(
                    "FKey-PubId",
                    dtabPub.Columns["pub_id"],
                    dtabEmp.Columns["pub_id"]);
        }
        catch (Exception Ex)
        {
            MessageBox.Show(Ex.Message);
            chkKeys.Checked = false;
        }

    }
    else
    {
        chkCascade.Checked = true;
        chkCascade.Enabled = false;

        //Snippet 5.3: Remove Foreign Key Constrains
        DataTable dtabEmp = PkFkDS.Tables["Employees"];
        try
        {
            if (dtabEmp.Constraints.Contains("FKey-PubId"))
                dtabEmp.Constraints.Remove("FKey-PubId");
        }
        catch (ConstraintException Ex)
        {
            MessageBox.Show(Ex.Message);
        }
    }
}

Now, we can enable the Foreign Key and try adding a row in the Employees DataGridView. This time, any attempt to add a Publisher ID in the Employees that don’t exist in the Publishers table will throw an Exception. Also, note that an exception is thrown when we try to enable the constraint with existing Integrity Violations. The below video shows the behavior of the Example while adding or removing the Foreign Key constraint.


Video 3: Parent-Child behavior with Foreign Key Enabled

7. The Role of Cascading


What happens to "Referring Columns" in the child table when an Update or Delete happens to the "Referred Column" in the parent row? The answer requires an explanation for “Cascading Action”. In Ado.Net, by default, Cascading is turned-on after establishing the Referential Integrity. When a row in the parent is deleted, all the referring rows will also get deleted. The same happens for the Update also. Note that when the update happens for Referred Column, the same update is propagated to all the Referring Columns.


We have a Cascade Checkbox in our example and by default, it is selected. To turn-off the Cascading Action we should remove the check mark. Below is the code which removes and/or puts back the Default Cascading based on the checkbox status.

private void chkCascade_CheckedChanged(object sender, EventArgs e)
{
    //Snippet 6.0: Remove Delete Casecase From ForeignKey
    //Constraint
    DataTable dtabEmp = PkFkDS.Tables["Employees"];
    ForeignKeyConstraint FKey = (ForeignKeyConstraint)
        dtabEmp.Constraints["FKey-PubId"];
    if (chkCascade.CheckState == CheckState.Unchecked)
        FKey.DeleteRule = FKey.UpdateRule = Rule.SetNull;
    else
        FKey.DeleteRule = FKey.UpdateRule = Rule.Cascade;                
}

The “Rule.Cascade” sets the Cascading behavior and “Rule.None” removes the cascading behavior. First, we are taking the “ForeignKeyConstraint” object from the DataTable. After that, we are setting the “DeleteRule” and “UpdateRule”. Now, watch below video which shows the cascading behavior on Publishers-Employee relationship.

Video 4: Turning ON and Turning OFF the Cascading Behavior


Source Code: Download

April 24, 2018

SQL 2005 - Creating and Using Full-Text Index

1. Introduction – Full Text Search Queries


The Full Text Search Queries can be enabled in Microsoft SQL 2005 and later versions. Lets us consider an online shopping web site storing products in their database. Let us also assume that the ProdDetails column can store texts of 1000 Words. Now, when user searches for a specific product details, the query will end up applying a string search on the ProdDetails. If the site supports millions of products, the search will take long time.


The good solution is creating a “Full Text Index” on that column and that not only makes the search faster but also provides rich search functionality on that columns. In this Article, we will first see how to enable the full text search on a particular column and then look at some most frequently used “Full Text Search Queries”.

2. Database and Table used for this Example

In this example, we are going to use, “notes” column of the “titles” table from the “pubs” database. One can setup the pubs database by downloading it through a web search or the database creation script available in the SQL 2005 section of this web site.

Title Table Of Pubs Database
Title Table Of Pubs Database

3. Creating Full Text index (Step-By-Step) for Notes Column


To create a Full-Text Index, right click on the Titles table and select “Define Full-Text index…” from the context menu as shown in the below picture.

Create Full Text Index ContextMenu
Create Full Text Index ContextMenu

This will start a welcome screen titled “Full-Text Indexing Wizard”. Go through the instructions given and Click Next.

Full Text Indexing Wizard Welcome Screen
Full Text Indexing Wizard Welcome Screen

The below screenshot shows next series of wizard pages. In the “Select an Index” page, we are selecting the primary key on the table. For a Full-Text index the table should have at least one Primary Key or Unique Key. In our case, the title_id column is a primary key and hence we selected that.

In the “Select Table Columns” page, we are actually selecting the column which require Full-Text Search functionality. In our case, we selected the column “notes”. Then we accepted the defaults for the “Select Change Tracking” page as we need automatic tracking of the index. The option “Automatic” specifies that changes made to the Notes column is tracked so that the index will be in-sync with the data always.

In the “Select Catalog” screen, we created a new catalog called Titles-Notes. We can use one catalog to store one or more Full Text indexes. Here, we created one catalog for our Notes Column Full-Text index. Accept the defaults for other wizard pages and click finish on the final page. This will create a Full Text Search index for the notes column and enable support for Full Text Search queries. Now, let us look at some Full Text Search query examples.

Full Text Indexing Wizard Pages
Full Text Indexing Wizard Pages

The complete Settings are Shown in the below video:



4. Full Text Search - Examples


OK. Now we have enabled Full Text Search Support for the notes column. Now, let up explore full text search queries.

4.1 CONTAINS with AND


The CONTAINS Form takes two parameters. First one is the Full-Text indexed column and the second parameter is the search strings. Below is the example query which uses CONTAINS:

Full Text Query Contains with AND
Full Text Query Contains with AND

In this example, we are using the CONTAINS form full text query (Marked as 1) and passing the column name “notes” as the first parameter (Marked as 2). The search terms are passed through second parameter. Note how three search terms are specified within double quotes and the entire second parameter is given as single quoted string. The query will look for all three terms and returns the result.


4.2 CONTAINS with Generation Terms


Now, consider the below example. Here, even though the notes column is not having any row with a term live, The “FormsOf” with “INFLECTIONAL” helped to find a row with the nearest meaning. Here, living is a generation term from the word Live and hence we see that in the Query output. 

Full Text Query Contains with FormsOf
Full Text Query Contains with FormsOf

Below is one more example which brings result as it finds Researched is a Form of Research. Note that out table has limited rows. It will be useful with the table with multiple rows and will make search easier as it handles generation terms. For example, if we search for write, we will get all rows that contains either write, wrote, written and writing.

Full Text Query Contains with FormsOf 2
Full Text Query Contains with FormsOf 2

Below is last example on the FormsOf. Here, we combined two FormsOf within a single CONTAINS search.

Full Text Query Contains with FormsOf 3
Full Text Query Contains with FormsOf 3

4.3 CONTAINS with Prefix Form



The prefix form is used to specify a word(s) that starts with a specific character. In the below example, we bringing the result rows which has one or more words that starts with “Com” and this specified as “Com*”


Full Text Query Contains with PrefixForm
Full Text Query Contains with PrefixForm

The Prefix can be applied for more than one word also. For Example, in the below query the term “com hard*” apply prefix for both the words. That is, it looks for word starting with “hard” as well as “com”.

Full Text Query Contains with PrefixForm2
Full Text Query Contains with PrefixForm2

4.5 Contains with NEAR Proximity 


The Proximity form of the CONTAINS is used to return the results when two specified terms are near to each other. How much is decided by the Microsoft SQL Server. 

Full-Text-Query-Contains-with-NEAR-Proximity
Full-Text-Query-Contains-with-NEAR-Proximity

4.5 The FREETEXT filter


When the rule is not strict and if we want to bring the result based on some search terms, then "FREETEXT" is the solution for it. As the name suggests, only Microsoft knows how it filters the result based on the given FREETEXT. The FREETEXT form of the Full-Text Search query takes some time to bring the result. Below is an Example:

Full-Text-Query-FREETEXT
Full-Text-Query-FREETEXT

Once experimented, if you want to delete the Full-Text search index, use the same context menu in which we created the index. The complete Listing is below:

-- Example 1 (Simple Form)
Select notes from titles
where contains(notes, '"medical" AND "psychological" AND "electronic office" ');

--Example 2 (Generation Terms)
Select notes from titles
where contains(notes, 'FormsOf(INFLECTIONAL, Live)');
Select notes from titles
where contains(notes, 'FormsOf(INFLECTIONAL, live)');
Select notes from titles
where contains(notes, 'FormsOf(INFLECTIONAL, Research)');

--Example 3 (Prefix Form)
Select notes from titles
where contains(notes, '"com*"');

Select notes from titles
where contains(notes, '"com hard*"');

--Example 4 (Proximity Terms)
Select notes from titles
where contains(notes, 'Software NEAR Computer');

--Example 5 (FreeText)
Select notes from titles
where FreeText(notes, 'Software describe Japanese microwave cooking');



March 20, 2018

C# - Update Text Boxes from Task Threads

1. Update UI From Threads


Updating a User Interface from a thread is not straight-forward. Say for instance, let us consider a form-based application which spawns a thread. The thread wants to report the progress to the progress bar control in the form. If we try that (Updating the progress from worker thread), we will be getting an error which mentions that thread is not the owner of the progress bar. Then how do we update the UI from a thread.


Once you go through the example, you will know how to do that.

2. Thread Updating Textboxes – About the Example


The below sample application which we are going to develop is going to demonstrate updating the User Interface element from a Thread . Have a look at the screen-shot:

Pic 1. Updating the UI Elements from Thread - About the Example
Pic 1. Updating the UI Elements from Thread - About the Example

The Max Counter textbox (Marked as 1) is used to get upper bounds of the counter. For example, if user gives 150 here, the threads which counts the number continue till 150 starting from Zero. The current counter textboxes (Marked as 2 and 3) are used to display the current counting from the threads. The Start Counter buttons (Marked as 4 and 5) are used to spawn thread to begin counting. The Clear All (Marked as 6) is used to clear all the text boxes.

Note that when we click the buttons 4,5 in immediate successions, we can see two threads counting their numbers and updating the UI (2,3).  

3. Start Coding the Example


3.1 TextBox validation - Range of Values


To reduce the errors on the Max Counter TextBox, we should allow only numbers. At the same time to demonstrate the example, we need a decent value. If a value is too less, we can not see thread updating the UI and if the value is too high then we are hanging the sample for a long time. 

Considering all these, the “Leave Event” of the textbox is handled.  Have a look at the code below:

Pic 2. Max Counter Validation
Pic 2. MaxCounter Validation

In the above code, we are using “TryParse()” function to retrieve the integer value from the textbox. When text box does not have a valid integer value, we end-up in the else box as TryParse fails. In the else box we are setting the textbox to empty and setting the focus on the same control. This makes user stays at the control and they can move out of this control, only after putting down a valid value. 

When TryParse is valid, we are checking that value specified in the textbox is within the acceptable range for the Example (Shown in Green Box). We are allowing the value should be in the range of 200 to 2000. 

3.2) Start the Threads


To use the threads, first include the required namespace into the project. The code is below:

//Sample 02: Required NameSpace
using System.Threading;

After this, we have to create thread objects in the StartCounter button click event handler. Since there are two buttons in the form, we will see CPU serving both the threads by slicing it service time while user clicks the button quick successions. Now have a look at the code below:

Pic 3. Starting the Threads
Pic 3. Starting the Threads

Here, we are making use of the Max Counter textbox content and making sure parse succeed (It is a double check).  The code highlighted in yellow shows that we are creating the “Thread” object by supplying a function to “ThreadStart” delegate. 

“Start()” function call marked as Black box actually starts the background Task. The function supplied to the ThreadStart delegate is called by the Thread.Start() and runs that function in the created thread's context. This way, the Function Threaded_Counter1 runs in a separate thread. 

3.3) Define UI Update Delegates for Threads


We want to update the Thread counting in the UI. But, this is not allowed by the operating system. We will get error stating that the access to UI elements are not allowed to the Threads. This is because the Main Thread is the owner of the UI elements as it created it (& owns it). Now, we need a way that child thread asking the main thread to do the UI update. Using delegate we can ask the main thread to update the UI. Right, look at the delegate declaration below:

//Sample 04: Create a Delegate to Update the UI
private delegate void UI_UpdateDelegate(int NewValue);


The above delegate states that the function returns void and accepts an integer parameter. The integer argument is supplied by the Thread at runtime and the function will make use of the supplied value to show the counting on the fly. Have a look the counting function below:

//Sample 05: UI update functions (Used by Delegate)
private void Update_Counter1(int NewValue)
{
    txtCounter.Text = NewValue.ToString();
}

private void Update_Counter2(int NewValue)
{
    txtCounter2.Text = NewValue.ToString();
}

Both the UI update functions which comply to delegate UI_UpdateDelegate are used to update the counter display Text Boxes. Note that both the delegate functions are running in the Main Thread context and hence it won’t have any trouble in accessing the Text Boxes.

3.4) Crete Counting Threads and Update UI


OK. Now let us implement the Thread Task and send update notifications to Main thread Functions. Have a look at the code below:

Pic 4 Updating UI from Thread
Pic 4 Updating UI from Thread

In the above code, First, we are creating the delegates which points to our UI Update functions created in the previous section  3.3 (Marked as 1). We know that our UI update function expects integer as parameter. The code snippet 2 shows creating a parameter array and setting the integer value to first parameter in the array. Our delegate does not require any other parameter and hence out parameter array has only one element.

The code marked as 3 shows, how we invoke the delegate function.  We are making use “Invoke()” method and passing the delegate to it as first parameter. The parameters required for the delegate function is passed as second parameter. The invoke will call the Update_Counter1() from the Main thread context.  After invoking the UI Update function, we asking the thread to sleep for 5 Milli-seconds so that Main thread can do the UI update and we see the counter incrementing in the corresponding text boxes.

Video 1: Update UI from Threads




4. Code Listings

Listing 1


//Sample 01: Allow valid integer value between 200 to 2000
private void txtMax_Leave(object sender, EventArgs e)
{
    int max_counter;
    if (int.TryParse(txtMax.Text, out max_counter))
    {
        if (max_counter > 2000 || max_counter < 200)
        {
            MessageBox.Show("Set Max Counter between 200 to 2000");
            txtMax.Focus();
        }
    }
    else
    {
        txtMax.Text = string.Empty;
        txtMax.Focus();
    }
}

Listing 2


//Sample 03a: Start Thread1
private void btnStart_Click(object sender, EventArgs e)
{
    long max_counter;
    if (long.TryParse(txtMax.Text, out max_counter))
    {
        Thread T = new Thread(new ThreadStart(Threaded_Counter1));
        T.Start();
    }
}

//Sample 03b: Start Thread2
private void btnStart2_Click(object sender, EventArgs e)
{
    long max_counter;
    if (long.TryParse(txtMax.Text, out max_counter))
    {
        Thread T = new Thread(new ThreadStart(Threaded_Counter2));
        T.Start();
    }
}

Listing 3


//Sample 06a: Threaded Function1 with UI Update
public void Threaded_Counter1()
{
    int max_counter;
    if (int.TryParse(txtMax.Text, out max_counter))
    {
        for (int i = 0; i <= max_counter; i++)
        {
            UI_UpdateDelegate U1_Del =
                     new UI_UpdateDelegate(Update_Counter1);
            object[] delegateParams = new object[1];
            delegateParams[0] = i;
            this.Invoke(U1_Del, delegateParams);
            Thread.Sleep(5);
        }
    }
}

//Sample 06b: Threaded Function2 with UI Update
public void Threaded_Counter2()
{
    int max_counter;
    if (int.TryParse(txtMax.Text, out max_counter))
    {
        for (int i = 0; i <= max_counter; i++)
        {
            UI_UpdateDelegate U2_Del =
           new UI_UpdateDelegate(Update_Counter2);
            object[] delegateParams = new object[1];
            delegateParams[0] = i;
            this.Invoke(U2_Del, delegateParams);
            Thread.Sleep(4);
        }
    }
}


Source Code: Download

February 13, 2018

Ado.Net - Unique and PrimaryKey Constraint behavior of DataTable and DataSet

1. About ADO.Net Constraints

We know that “Relational Databases” support “Database Constrains” which helps in maintaining the “Data Integrity” within the database. The same way the constraints can be enforced from the ado.net front end itself. In this article we are going to examine how Unique Constraint and Primary Key constraint can be applied to DataTable. Then we will study the behavior of these constraints under the following conditions:
  1. Adding a DataRow to the Constraint applied DataTable
  2. Merging a DataTable with the DataSet which has same DataTable Schema.

2. About the Ado.Net Constraint Example

The example is created to study the behavior of the Ado.Net DataTable Constraints and if there is an error (Tried our best, if any exists), kill and restart the sample application. Now, have a look at the example:

Ado.Net Constraint Example

In the above picture you can see all the controls and its names are displayed. The “DataGridView Control” (dataGridView1) occupies the most of the form area and it is used to display the data. The Load Data button (btnLoadData) is used to load the data from the database (Title table of Microsoft supplied Pubs database) and when loading it applies the Ado.Net Constraint selected by the user through Radio Button controls.


The set of TextBox Controls are used to get the user data. When user clicks the Add Row button (btnAdd), the data entered by the user is added to the DataGridView. Instead of clicking the Add Row button, a user adds the row to a temporary DataTable by clicking the button btnAddToDataTable. For example, if user adds three rows using this button, the DataTable will have three rows and the ListBox control (lstDTContent) shows what is there in the DataTable so for. The content of the DataTable can be merged to the DataGridView control through a DataTable in the DataSet (You will see more about this when the article progresses) and it happens by clicking the Add Table button (btnAddTable). With this example, we can examine the behavior of the Unique and Primary Constraints of the DataTable.




3. Load Data to DataGridView (Load Button Handler)


1) Before implementing the click event handler for the btnLoadData, we need to first include the required namespace for the sample. Below is the code:

//Sample 01: include the required namespace
using System.Data.SqlClient;


2) Then two private members are used at the form level. The TitleDataSet instance of type “DataSet” is used to hold the title table content under a DataTable. The DT_title instance of type “DataTable” is used to build the DataTable on the fly to experiment the Constraints behavior during the Merge operation. Below is the code:

//Sample 02a: Declare the Dataset for Titles Table
private DataSet TitleDataSet;
//Sample 02b: Datatable we are going to use for Merge
private DataTable DT_title;

3) In the load button handler, first a DataSet instance is created and stored in the variable, TitleDataSet. Next, we are creating the SqlDataAdapter object “adaptor” by supplying the Connection String and Query String that retrieves the data from the  Pubs database.

Once the SqlDataAdapter is ready, we are making call its “Fill()” method. We passed two parameters to this method. First one is the DataSet object TitleDataSet and the second one the string; Titles. Now, the Fill() method queries the pubs table, creates a DataTable called “Titles” and places that in the DataSet object TitleDataSet. Below is the code:

//4.1: Create a DataAdaptor and Fill the Dataset
TitleDataSet = new DataSet();
string select_str = "Select title_id, title, type from titles";
SqlDataAdapter adaptor = new SqlDataAdapter(select_str,
DataSetPrimaryConstraint.Properties.Settings.Default.PubsCon);
adaptor.Fill(TitleDataSet, "Titles");


3.1 Set Unique Key Constraint on DataTable

First refer the code given below:


//4.2: Set the Constraints
DataTable dtTitles = TitleDataSet.Tables["Titles"];
dtTitles.Constraints.Clear();
if (radUnique.Checked == true)
    dtTitles.Constraints.Add("Unique1",
        dtTitles.Columns["title_id"], false);


In the previous step, we created the DataTable (Contained within TitleDataSet) from the Titles table of the pubs database. Now, we are grabbing that DataTable through "Tables Collection" of the DataSet(TitleDataSet). Note that we using the Named Index “Titles” (Passed as second parameter to Fill method to name the DataTable within DataSet as “Titles”) to get the DataTable from the Tables collection of the TitleDataSet.


One we have the Titles DataTable on hand, we are adding the constraints based on the currently selected Radio Box Control. Since the code is running on button click handler, before adding the constraint we are clearing the Constraints collection of the DataTable through "Clear()" method.


To add constraints to the DataTable, we are using the "Add()" method of the "Constraints Collection" of the DataTable; dtTiles. Have a look at the depiction below:

Constraints Collection of DataTable



Here in the example, the first parameter tells the name of the constraint and we named it as “Unique1”. The second parameter tells what column(s) is/are involved in the constraint. In our example, we are applying the constraint to “title_id” column. The third parameter tells whether it is "Unique Constraint" or "Primary Key constraint". In the depiction above, it is unique constraint since we pass Boolean false.

3.2 Set Primary Key Constraint on DataTable


When the PKEY radio box is checked, we are adding the Primary Key Constraint to the DataTable. To do that we are passing the last Boolean parameter as true. Below is the code for it.

if (radPKEY.Checked == true)
    dtTitles.Constraints.Add("Unique1", 
        dtTitles.Columns["title_id"], true);

3.3 Set DataSource to DataGridView 

Once the DataTable is Ready with or without constraint, it set to the DataGridView. Then other UI control visibility are adjusted in this load button handler. Below is the code which doesn’t require further explanation:


//4.3: Populate DataGrid Data from DataSet
dataGridView1.DataSource = TitleDataSet.Tables["Titles"];
dataGridView1.Columns[1].Width = 350;

//4.4 Diable Load button
btnLoadData.Visible = false;
radNone.Visible = false;
radUnique.Visible = false;
radPKEY.Visible = false;
adaptor.Dispose();


4. Clear button Handler


We can test the behavior of the DataTable displayed in the DataGridView by loading it with Unique and Primary Key Constraints. For that we need to implement the Clear Button's Click Event handler. Below is the code for the Clear Button handler:

//Sample 03: Define the Dataset for Titles Table
private void btnClear_Click(object sender, EventArgs e)
{
    //3.1: Clear the Data Grid Control
    dataGridView1.DataSource = null;
    dataGridView1.Rows.Clear();
    dataGridView1.Columns.Clear();

    //3.2: Clear the DataSet and DataTable
    TitleDataSet.Clear();
    DT_title.Clear();

    //3.3: Enable Load button
    btnLoadData.Visible = true;
    radUnique.Visible = true;
    radPKEY.Visible = true;
    radNone.Visible = true;

    //3.4: Clear the UI fields
    txtTitleDesc.Text = "";
    txtTitleID.Text = "";
    txtTitleType.Text = "";
    lstDTContent.Items.Clear();
}

Now let us test the constraint behavior before proceeding with the next section. Watch the video below which tests the load button with different conditions.

Video 1: Ado.net Unique and Primary Key Behaviour testing on DataGrid-1



5. Add Row to DataTable with Unique or Primary Key Constraint



Now, we will add the row dynamically to the DataTable and render that to the DataGridView.  This time, in place of editing the DataTable directly in DataGridView, we are going to add new Data Row through C-Sharp Code. The user enters the book details in the TextBoxes; txtTitleID, txtTitleType and txtTitleDesc. After entering the details, user clicks the Add Row button to add the new DataRow to the DataGridView control. 

Below is the code for the Add Row button handler:


//Sample 05: Add Row to a Data Grid
private void btnAdd_Click(object sender, EventArgs e)
{
    try
    {
        //5.1: Grab the Data Table from DataGrid
        DataTable dtTitle = TitleDataSet.Tables["Titles"];

        //5.2: Create New Row and Assign Data
        DataRow Title_Row = dtTitle.NewRow();
        Title_Row["title_id"] = txtTitleID.Text;
        Title_Row["title"] = txtTitleDesc.Text;
        Title_Row["type"] = txtTitleType.Text;

        //5.3: Add the new row and and refresh the Grid
        dtTitle.Rows.Add(Title_Row);
        dtTitle.AcceptChanges();
    }
    catch (Exception Ex)
    {
        MessageBox.Show(Ex.Message);
    }
}

The video below tests the Adding a Row to the DataTable when:
  1. There is no constraint
  2. There is a Unique Constraint applied to the Ado.Net DataTable
  3. There is a Primary Key Constraint applied to the Ado.Net DataTable.


Video 2: Ado.net Unique and Primary Key Behaviour – Add Row Dynamically to DataTable



5. The Result of Merging DataTables With Primary Key or Unique Constraint



Till now, we had not seen any difference between Unique and Primary Key while adding a new row to a constrained DataTable.  But, we can see a difference in behavior when we merge the DataTables. In our example, we are going to build in-memory DataTable (Source) and merge that with the DataTable in the DataSet (Destination).


In the Load button handler, we are creating the in-memory DataTable. The DataTable is named as Titles to match the name of the DataTable contained in the TitleDataSet. While adding the columns, we are making sure that it matches the column name and data type of the destination DataTable. Below is code which creates in-memory DataTable in the Load button’s Click handler. 


//4.5: Add columns similar to DataTable in DataSet
DT_title = new DataTable("Titles");
DT_title.Columns.Add("title_id",
    TitleDataSet.Tables["Titles"].Columns["title_id"].DataType);
DT_title.Columns.Add("title",
    TitleDataSet.Tables["Titles"].Columns["title"].DataType);
DT_title.Columns.Add("type",
    TitleDataSet.Tables["Titles"].Columns["type"].DataType);


5.1 Adding Rows to in-memory DataTable



When the vertical button btnAddToDataTable is clicked, we are creating a new DataRow by calling the "NewRow()" function of the DataTable. Then, we are populating the column values from the TextBoxes - txtTitleID, txtTitleType and txtTitleDesc. After this, the DataRow is added to the "Rows Collection" of the DataTable. Note that we are also forming a helper string that gets added to the ListBox lstDTContent. This list box helps user to get a glimpse of the in-memory DataTable. Below is code which accumulates the Rows in the In-Memory DataTable.


//Sample 6.0: Add rows to DataTable 
private void btnAddToDataTable_Click(object sender, EventArgs e)
{
    //6.1 Show the content of DataTable in the Listbox
    lstDTContent.Items.Add(
        txtTitleID.Text + "," +
        txtTitleDesc.Text);

    //6.2 Append a new row to Data Table
    DataRow NewRow = DT_title.NewRow();
    NewRow["title_id"] = txtTitleID.Text;
    NewRow["title"] = txtTitleDesc.Text;
    NewRow["type"] = txtTitleType.Text;
    DT_title.Rows.Add(NewRow);
}



5.2 Merging the DataTable with another DataTable



The in-memory DataTable that we are building by user interaction is the source table and the destination table is contained in the DataSet which got filled by the DataAdaptor. We are making call to Merge() method of the DataSet by supplying the in-memory table as parameter. The Merge() method merges the rows of the source table with the destination.


Let us say, the source DataTable DT_title is having a duplicate column value for the column title_id, the merge will have the following behaviour:


  1. No Error when destination DataTable does not have constraint.
  2. Constraint violation Error when destination DataTable has unique constraint applied to the title_id columns. 
  3. The title_id column is used to match the row in the destination DataTable and other columns values title, type is replaced by the source DataTable.

The Effect of the merge in case PrimaryKey constraint is shown in the below picture:


Merging two DataTables with Primary Key Constraint


Here, TC7777 in the source DataTable (in-memory) is matched with TC7777 in the destination DataTable (shown in Red) and then the remaining columns are replaced in the Destination Table (Shown in blue)

Below is code which performs the merge:



//Sample 7.0: Merge the Dynamic DataTable
private void btnAddTable_Click(object sender, EventArgs e)
{
    try
    {
        //7.1: Merge the Dynamic Table Created by user to DataSet 
        TitleDataSet.Merge(DT_title);
        TitleDataSet.Tables["Titles"].AcceptChanges();

        //7.2:  Clear the Dynamic Table (Already Merged) and other 
        //      Controls
        DT_title.Rows.Clear();
        txtTitleID.Text = txtTitleDesc.Text = txtTitleType.Text = "";
        lstDTContent.Items.Clear();
    }

    catch (Exception Ex)
    {
        MessageBox.Show(Ex.Message);
        DT_title.Clear();
        txtTitleID.Text = txtTitleDesc.Text = txtTitleType.Text = "";
        lstDTContent.Items.Clear();
    }
}


Video 3: Ado.net Unique and Primary Key Behaviour – Merging DataTables



Source Code: Download

Like this site? Tell it to your Friend :)