ADO.NET : Working with DataSet, DataTable, DataColumn, DataRow and DataRelations

Knowing to work with the offline ADO.NET classes such as DataSet and DataTable, is the Alpha and the Omega in ADO.NET before actually start queering real database data. You need to understand that ADO.NET divides it’s classes in two major categories. The Connected and the Disconnected classes. The Connected classes are those that lets your retrieve and update data in your data sources. The most important classes of that category are the Connection, DataReader, DataAdapter classes. The Disconnected category classes, are those that lets you access and manipulate offline the data you have retrieved using the Connected classes. When you decide, you can synchronize the changes you made to the Disconnected classes, using the Connected ones again. The most important Disconnected classes are the DataSet, DataTable, DataColumn, DataRow, DataRelation classes. This is the second post of the series in ADO.NET category and I hope you have already read the first one, Introduction to ADO.NET.

Following are the different concepts we will examine in this post. We will create a solution where we might add different projects to show each of the following concepts or you might need to comment and comment out each time the respective lines (in order to keep the solution smaller).

ADO.NET concepts

  1. Create DataColumn and bind it to a DataTable
  2. Create DataTable and bind it to a DataSet
  3. Column Mapping between a DataSet and a Data Source
  4. Adding Unique Constraints
  5. Create Primary Keys
  6. Create Foreign Keys
  7. Create DataRelation

Let’s start. Open Visual Studio and create a solution named AdoNetOfflineClasses.

Create DataColumn and bind it to a DataTable

Add a C# console application project named ConfiguringDataTablesAndDataSets. In the Program.cs file add a reference to the System.Data namespace. We are going to create some columns and then add them to a DataTable. Paste the following code in Program.cs file.

using System.Data;

namespace ConfiguringDataTablesAndDataSets
{
    class Program
    {
        static void Main(string[] args)
        {
            // Here we create the DataTable
            DataTable dt = new DataTable("MyTable");

            // Create the 1st column
            DataColumn column1 = dt.Columns.Add();
            column1.ColumnName = "First";
            column1.DataType = typeof(int);
            column1.DefaultValue = 0;
            column1.Unique = true;
            column1.AllowDBNull = false;

            // Create the 2nd column
            DataColumn column2 = new DataColumn();
            column2.ColumnName = "Second";
            column2.DataType = typeof(string);
            column2.MaxLength = 25;
            dt.Columns.Add(column2);

            // Add the 3rd column instantly
            dt.Columns.Add("Third", typeof(string)).MaxLength = 40;

            // Create the 4th and 5th collumn  and add them together
            DataColumn column4 = new DataColumn("Fourth");
            column4.DataType = typeof(int);
            DataColumn column5 = new DataColumn("Firth", typeof(decimal));
            dt.Columns.AddRange(new DataColumn[] { column4, column5 });

            // Display the Columns in our DataTable
            Console.WriteLine("DataTable {0} has {1} columns: ",
                dt.TableName, dt.Columns.Count);
            Console.WriteLine();
            foreach (DataColumn col in dt.Columns)
            {
                Console.WriteLine("\t\t\t{0}", col.ColumnName);
            }

            Console.ReadKey();
        }
    }
}

First you need to create a DataTable (why you need a column anyway?). In DataTable’s constructor you can pass a string value which will be the Table’s name (line 10). The DataTable has a DataColumnsCollection through the Columns property, which you use to add the columns you want. You can instantly add the column without any property and later, configure whatever property you want (lines 13-18). You can choose though, first to create and configure the DataColumn and then add it to the DataTable (lines 21-25). Also, you can create/configure/add the DataColumn in one line (line 28). Lastly, you can use the DataTable.Columns.AddRange function to add multiple DataColumns to your DataTable (line 34). Build and run your solution.

adonetoffline_1

Create DataTable and bind it to a DataSet

Creating a DataTable and adding it to a DataSet is pretty much the same as adding a DataColumn to a DataTable. You don’t have to create another project to show this feature, just comment what you have written inside your main method and paste the following code.

// Create a DataSet object
            DataSet ds = new DataSet("MyDataSet");

            // Add a DataTable named Table-1
            DataTable table1 = ds.Tables.Add("Table-1");
            // Here you can configure it, that is add some columns
            // in the way we showed previously

            DataTable table2 = new DataTable("Table-2");
            ds.Tables.Add(table2);

            DataTable table3 = new DataTable("Table-3");
            DataTable table4 = new DataTable("Table-4");
            // Configure those tables and then add them 
            // together 
            ds.Tables.AddRange(new DataTable[] { table3, table4 });

            Console.WriteLine("DataSet {0} has the following {1} DataTables",
                ds.DataSetName, ds.Tables.Count);

            foreach(DataTable dt in ds.Tables)
                Console.WriteLine("\t\t\t{0}", dt.TableName);

            Console.ReadKey();

When you create a DataSet instance, you can pass a string value as a parameter in order to name that DataSet (line 2). You can create and add a DataTable to a DataSet at the same time, using the DataSet.Tables.Add method (line 5). The returned table, of course can be configured later. You can add multiple tables to a DataSet using DataSet.Tables.AddRange fuction (line 16). Build and run your solution.

adonetoffline_2

Column Mapping between a DataSet and a Data Source

Ok, let’s get some serious now. When you query data with ADO.NET from a data source such as an SQL Server database, you need to configure the mapping between the data source and the DataSet you will use to manipulate your data (offline). The classes you use to make the mapping are the DataTableMapping and DataColumnMapping classes. Comment whatever you have added in your main method and add a reference to the following namespaces.

using System.Data;
using System.Data.Common;
using System.Data.SqlClient;

Paste the following code to your main method. Notice that I retrieve some data from the AdventureWorks database, so either make sure you use the same database or make the respective changes for your programming environment.

static void Main(string[] args)
        {

            // This is my connection string - make sure you change it 
            // respectively if is needed
            string sqlConn = "Data Source=localhost;" +
                "Integrated security=SSPI;Initial Catalog=AdventureWorks;";

            string sqlSelect = "SELECT TOP 5 Title, FirstName, LastName " +
                "FROM Person.Person";

            SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConn);

            // Change the Default DataTable name from Table -> TablePersonMapped
            DataTableMapping dtm = da.TableMappings.Add("Table", "TablePersonMapped");

            // Create the Column mappings
            dtm.ColumnMappings.Add("Title", "ColumnTitleMapped");
            dtm.ColumnMappings.Add("FirstName", "ColumnFirstNameMapped");
            dtm.ColumnMappings.Add("LastName", "ColumnLastNameMapped");

            // Create and fill the DataSet
            DataSet ds = new DataSet();
            da.Fill(ds);

            Console.WriteLine("DataTable name:{0}", ds.Tables[0].TableName);

            foreach (DataColumn col in ds.Tables["TablePersonMapped"].Columns)
            {
                Console.WriteLine("\tDataColumn {0} name = {1}",
                    col.Ordinal, col.ColumnName);
            }

            foreach (DataRow row in ds.Tables["TablePersonMapped"].Rows)
            {
                Console.WriteLine("Title = {0}, FirstName = {1}, LastName = {2}",
                row["ColumnTitleMapped"], row["ColumnFirstNameMapped"],
                row["ColumnLastNameMapped"]);
            }

            Console.WriteLine("\nPress any key to continue.");
            Console.ReadKey();

        }

To access your database with ADO.NET you need an instance of a Connected category class, the SqlDataAdapter. You pass two parameters, your SQL statement and the connection string (line 12). You use the SqlDataAdapter.TableMappings.Add method to change the default DataTable name from “Table” to whatever you want. If you don’t do this, your DataTable’s name will be “Table”. This method, returns a DataTableMapping which you use to make the Column Mappings between your database table columns and your DataTable ones(lines 18-20). Build and run your solution.

adonetoffline_3

Adding Unique Constraints

We are going to show how Constraints and DataRelations are defined in ADO.NET so add a new C# console application project to your solution, named ConstraintsAndDataRelations. Add again a using reference to the System.Data namespace. Paste the following code to your main method.

static void Main(string[] args)
        {
            // This will our DataTable
            DataTable dt = new DataTable("My-Custom-Table");
            // Add some columns
            dt.Columns.Add("Id", typeof(int));
            dt.Columns.Add("Name", typeof(string)).MaxLength = 20;

            // Create a unique constraint on field Id
            UniqueConstraint uniCon = new UniqueConstraint("MyUniqueConstraint", dt.Columns["Id"],true);
            dt.Constraints.Add(uniCon);

            // Let's test if constraint works
            try
            {
                dt.Rows.Add(new object[] { 1, "Chris" });
                dt.Rows.Add(new object[] { 2, "John" });
                dt.Rows.Add(new object[] { 3, "Mary" });
                dt.Rows.Add(new object[] { 1, "Maria" });
            }
            catch (Exception ex)
            {
                Console.WriteLine("Row cannot be added...");
                Console.WriteLine("Error: {0}", ex.Message);
            }
            finally
            {
                foreach (DataRow row in dt.Rows)
                    Console.WriteLine("Id {0}: Name {1}",
                        row["Id"], row["Name"]);
            }
        }

The most important lines of above’s code are (lines 10-11) where the unique constraint is created. You use an instance of the UniqueConstraint class to create a unique constraint, passing to it the constraint’s name, the DataTable’s column you want to be unique and a boolean that indicates if the unique constraint will be also a primary key. Then you need to add this constraint to the DataTable.Constraints collection. In the code, I tried to add 4 records but noticed that the fourth (on purpose) has the same Id with the first one. If the constraint works, the system will prevent adding it to the DataTable. Set this project as the Start Up, build and run it.

adonetoffline_4

Create Primary Keys

Creating primary keys is pretty much the same. Comment the code inside your main method and paste the following.

static void Main(string[] args)
        {
            // create a Product DataTable
            DataTable dt = new DataTable("Product");
            // Add two columns
            DataColumn column1 = new DataColumn("Id");
            column1.DataType = typeof(int);
            dt.Columns.Add(column1);
            dt.Columns.Add("ProductName", typeof(string)).MaxLength = 40;
            
            // Set "Id" as the primary key
            dt.PrimaryKey = new DataColumn[] { column1 };
            
             //Let's see if this work
            try
            {
                dt.Rows.Add(new object[] { 1, "Golf" });
                dt.Rows.Add(new object[] { 2, "Volvo" });
                dt.Rows.Add(new object[] { 3, "BMW" });
                dt.Rows.Add(new object[] { 1, "Ferrari" });
            }
            catch (Exception ex)
            {
                Console.WriteLine("Row cannot be added...");
                Console.WriteLine("Error: {0}", ex.Message);
            }
            finally
            {
                foreach (DataRow row in dt.Rows)
                    Console.WriteLine("Product {0}: Name {1}",
                        row["Id"], row["ProductName"]);
            }

        }

Line 12 sets as the DataTable’s primary key, column1 using the DataTable.PrimaryKey property. This will also make sure that column1 values will be unique. We test this functionality in the same way we did before, that is try to add a record with the same Id as another one. Build and run your solution.

adonetoffline_5

Create Foreign Keys

Let’s see now how to create Foreign Key Constraints in ADO.NET. Basically you need two DataTables objects to create a Foreign Key between two of their columns. Comment your main method code and add the following.

static void Main(string[] args)
        {

            /* -----------   Foreign Key Creation Section  ---------------- */
            DataSet ds = new DataSet();
            // Create a Parent DataTable and add it to the DataSet
            DataTable table1 = new DataTable("Product");
            table1.Columns.Add("ProductId", typeof(int));
            table1.Columns.Add("Name", typeof(string)).MaxLength = 30;
            ds.Tables.Add(table1);

            // Create the child DataTable and add it to the DataSet
            DataTable table2 = new DataTable("Order");
            table2.Columns.Add("OrderID", typeof(int));
            table2.Columns.Add("ProductId", typeof(int));
            table2.Columns.Add("Customer", typeof(string)).MaxLength = 30;
            ds.Tables.Add(table2);

            // Create and add to child table a foreign key constraint
            ForeignKeyConstraint fk = new ForeignKeyConstraint("FK_Order_Product",
                table1.Columns["ProductId"], table2.Columns["ProductId"]);
            table2.Constraints.Add(fk);

            try
            {
                table1.Rows.Add(new object[] { 1, "Golf" });
                table1.Rows.Add(new object[] { 2, "BMW" });
                table1.Rows.Add(new object[] { 3, "Alpha Romeo" });

                table2.Rows.Add(new object[] { 1, 1, "Chris" });
                table2.Rows.Add(new object[] { 2, 2, "John" });
                table2.Rows.Add(new object[] { 3, 4, "Katherin" });
            }
            catch (Exception ex)
            {
                Console.WriteLine("Row cannot be added...");
                Console.WriteLine("Error: {0}", ex.Message);
            }
            finally
            {
                foreach (DataRow row in table1.Rows)
                    Console.WriteLine("Product {0}: Name {1}",
                        row["ProductId"], row["Name"]);
                Console.WriteLine();
                Console.WriteLine("Orders.......");
                foreach (DataRow row in table2.Rows)
                    Console.WriteLine("Order {0}: Product {1}: Customer {2}",
                        row["OrderId"], row["ProductId"],row["Customer"]);
                
            }

            Console.ReadKey();
        }

You can create a foreign key constraint using the ForeignKeyConstraint class. In it’s constructor, you typically pass the name of the FK constraint, the primary key column of the parent DataTable and finally, the foreign key column in the child DataTable Lines 20-22. Since you create the Foreign Key constraint, you cannot add a record to the child DataTable with a foreign key value not matched to a primary key in the parent DataTable. Build and run your solution to test it.

adonetoffline_6

Create DataRelation

Creating a DataRelation in ADO.NET is pretty much the same as using a Foreign Key constraint. In the following example, we will create two tables establishing a relation between them. More specifically, when a record is added to the child table, two column values must match exactly two corresponding values in the parent DataTable. Comment your main method code and paste the following.

static void Main(string[] args)
        {
            /* ----------   DataRelation Section   -----------------------*/
            DataSet ds = new DataSet();
            // Create a Parent DataTable and add it to the DataSet
            DataTable table1 = new DataTable("Product");
            table1.Columns.Add("ProductId", typeof(int));
            table1.Columns.Add("Name", typeof(string)).MaxLength = 30;
            table1.Columns.Add("MadeIn", typeof(string)).MaxLength = 30;
            ds.Tables.Add(table1);

            // Create the child DataTable and add it to the DataSet
            DataTable table2 = new DataTable("Order");
            table2.Columns.Add("OrderID", typeof(int));
            table2.Columns.Add("ProductId", typeof(int));
            table2.Columns.Add("MadeIn", typeof(string)).MaxLength = 30;
            table2.Columns.Add("Customer", typeof(string)).MaxLength = 30;
            ds.Tables.Add(table2);

            DataRelation dr = new DataRelation("Order_Product_Relation",
                new DataColumn[] {table1.Columns["ProductId"],table1.Columns["MadeIn"]},
                new DataColumn[] {table2.Columns["ProductId"],table2.Columns["MadeIn"]});
            ds.Relations.Add(dr);

            try
            {
                table1.Rows.Add(new object[] { 1, "Golf", "Germany" });
                table1.Rows.Add(new object[] { 2, "Toyota", "Japan" });
                table1.Rows.Add(new object[] { 3, "Alpha Romeo", "Italy" });

                table2.Rows.Add(new object[] { 1, 1, "Germany", "Chris" });
                table2.Rows.Add(new object[] { 2, 2, "Japan", "John" });
                table2.Rows.Add(new object[] { 3, 3, "Italy", "Katherin" });
                table2.Rows.Add(new object[] { 3, 3, "Japan", "Smith" });
            }
            catch (Exception ex)
            {
                Console.WriteLine("Row cannot be added...");
                Console.WriteLine("Error: {0}", ex.Message);
            }
            finally
            {
                foreach (DataRow row in table1.Rows)
                    Console.WriteLine("Product {0}: Name {1}: Made in {2}",
                        row["ProductId"], row["Name"], row["MadeIn"]);
                Console.WriteLine();
                Console.WriteLine("Orders.......");
                foreach (DataRow row in table2.Rows)
                    Console.WriteLine("Order {0}: Product {1}: MadeIn {2}: Customer {3}",
                        row["OrderId"], row["ProductId"], row["MadeIn"], row["Customer"]);

            }

            Console.ReadKey();
        }

We create a DataRelation using the DataRelation class. You pass as parameters, the DataRelation’s name, the parent’s DataTable columns you want to use for ForeignKey Constraint when adding records to the child one, and finally the respective child columns that their values in a individual record must always match the corresponding column values in a record in the parent DataTable (Lines 20-23). Build and start your solution.

adonetoffline_7

That’s it. We covered a lot of interesting concepts in ADO.NET Development. Knowledge that we need to be aware of, before start fetching real database data to our DataSets, something that we ‘ll cover in next posts of these series. I hope you enjoyed the post as much I did. You can download the “AdoNetOfflineClasses” solution we created from here.

Advertisements


Categories: ADO.NET

Tags: , ,

1 reply

Trackbacks

  1. Retrieve Child and Parent column values in ADO.NET « chsakell's Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

mohitgoyal.co

Automating infrastructure one line at a time

Diary Of A Programmer

Because every day is worth noting

Chara Plessa

The purpose of this blog is to broaden my education, promote experimentation and enhance my professional development. Albert Einstein once said that “If you can’t explain it simply, you don’t understand it well enough” and I strongly believe him!

chsakell's Blog

Anything around ASP.NET MVC,WEB API, WCF, Entity Framework & AngularJS

Kumikoro

A Front End Developer's Blog

Muhammad Hassan

Full Stack Developer | ASP.NET | MVC | WebAPI | Advanced Javascript | AngularJS | Angular2 | C# | ES6 | SQL | TypeScript | HTML5 | NodeJS, MS candidate @LUMS, Grad & EX-Adjunct Faculty @NUCES-FAST, seasonal blogger & open-source contributor. Seattle, WA.

Software Engineering

Web development

IEvangelist

.NET, ASP.NET, C#, MVC, TypeScript, AngularJS

leastprivilege.com

Dominick Baier on Identity & Access Control

Happy DotNetting

In Love with Technology

Knoldus

Knols of experience to your advantage

knowshnet

Search - Read - Request - Share

Rahul's space

Learn, Share and Grow with me !

Dhananjay Kumar

Developer Evangelist @Infragistics | MVP @Microsoft

SQL Authority with Pinal Dave

SQL Server Performance Tuning Expert

Conficient Blog

Random bits of tech from @conficient

Code! Code! Code!

SOLID & KISS

Code Wala

Designing and coding

Microsoft Mentalist

A way to start with Microsoft Technologies

%d bloggers like this: