Typed vs. Untyped Datasets

In last few posts we discussed how you can use data adapter, datatables and datasets to fetch data from and save it to SQL Server. In those examples, we created untyped datasets. Untyped dataset is simply an in memory declaration of a dataset. It is a collection of datatables, which themselves are in memory declaration of datatables which are similar to database tables.

While it is possible to create constraints such as column type, relationship between columns, whether a column is nullable or not in a datatable declared in memory, but you have to write extra code for it. Contrast that with a typed dataset which inherits from dataset class and has an XML schema. This allows for you to define constraints and relationships on the design surface. Additionally, you can also drag tables from the database on the design surface and relationships/constraints of your database will automatically follow.

In this example, we will create a dataset using AdventureWorks database and then dragging the tables on the design surface. I am using VS 2010 for this project.

1. Create a sample test project.
2. From the top menu select Data > Add New Data Source and connect to the database you would like to use.

3. Select database and click on Next

4. Create a New Connection or select an existing connection. Here we will create a new connection

5. Define parameters and select your database.

6. Select one or more tables/views/stored procedures/function you would like to include in this dataset. Here we will select all tables.

7. Click on Finish and it will create an xsd file with all the selected tables.

8. If you open the file, you will see all tables that are part of this xsd

Alternatively, you can also create an xsd file manually and drag the tables that you want on the design surface. Click on Add new file and then select an xsd file. Then drag the tables to the design surface. As you drag the tables, note the data type and relationship between the tables.

In addition to dragging the tables on the design surface, you can also add table(s) by right clicking on the design surface and then clicking on Add > New Table or create a new relationship constraint.

Using Typed Dataset

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace TypedDataSets
{
    class Program
    {
        static void Main(string[] args)
        {
            Customers customer1 = new Customers();
            //populate the dataset
            // Step 2: Specify a command (request)
            string connectionstring = System.Configuration.ConfigurationManager.
            ConnectionStrings["TypedDataSets.Properties.Settings.
            AdventureWorksConnectionString"].ToString();
            SqlConnection myConnection = new SqlConnection(connectionstring);
            myConnection.Open(); 
            SqlCommand myCommand = myConnection.CreateCommand();

            myCommand.CommandType = CommandType.Text;
            myCommand.CommandText = "SELECT TOP 10 CustomerID,TerritoryID,AccountNumber, " + 
                "CustomerType,rowguid,ModifiedDate FROM Sales.Customer";

            // now use data sets instead of data read
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = myCommand;
            da.Fill(customer1.Customer);

            Console.WriteLine("Customer ID: " + customer1.Customer[0].CustomerID);
            Console.Read();
        }
    }
}

Typed datasets allow for strong typing and allows for cleaner code, in the sense you don't have to convert data from one type to another or check for the data type before using it.

Thank you.

Using ADO.NET - Part IV

In previous post we discussed how you can use dataset to insert new row and then use HasChanges() method and GetChanges() to only get newly added rows and insert them in the database instead of looping through all rows.

Today, we will see how you can use SQL Data Adapter to update the changes made to the data table and also Datarow's rowstate property to detect a deleted row and then delete it in the database.

In the first example, I have created a method that will change a datarow and then use dataadapter to update the changed row. I didn't have to loop through all records to determine which rows were updated.

Updating Modified Rows

private static void UpdateCustomer(DataSet ds, SqlConnection myConnection)
        {
            SqlCommand myCommand = myConnection.CreateCommand();
            
            //lets modify a record
            if (ds.Tables["Customers"].Rows.Count > 0)
            {
                ds.Tables["Customers"].Rows[0]["TerritoryID"] = "7";
                ds.Tables["Customers"].Rows[0]["CustomerType"] = "S";
                ds.Tables["Customers"].Rows[0]["ModifiedDate"] = DateTime.Now;

            }

            //now save this data in database. 

            //create a command object. 
            string strSQL = "sp_UpdateCustomer";
            myCommand.CommandType = CommandType.StoredProcedure;
            myCommand.Parameters.Add(new SqlParameter("@CustomerID", 
                          SqlDbType.Int,4,"CustomerID"));
            myCommand.Parameters.Add(new SqlParameter("@TerritoryID", 
                          SqlDbType.Int,4,"TerritoryID"));
            myCommand.Parameters.Add(new SqlParameter("@CustomerType", 
                          SqlDbType.Char,1,"CustomerType"));
            myCommand.Parameters.Add(new SqlParameter("@ModifiedDate", 
                          SqlDbType.DateTime,8,"ModifiedDate"));

           
            myCommand.CommandText = strSQL;
            SqlDataAdapter da = new SqlDataAdapter();
            da.UpdateCommand = myCommand;

            da.Update(ds.Tables["Customers"].GetChanges());

        }

In the second example, we will delete a row in a data table then detect the deleted row to delete it from the database.

Deleting a Deleted Row

 private static void DeleteCustomer(DataSet ds, SqlConnection myConnection)
        {
            SqlCommand myCommand = myConnection.CreateCommand();
            string strSQL = "sp_DeleteCustomer";
            myCommand.CommandType = CommandType.StoredProcedure;
            myCommand.CommandText = strSQL;
            myCommand.Parameters.Add(new SqlParameter("@CustomerID", SqlDbType.Int));

            //lets modify a record
            if (ds.Tables["Customers"].Rows.Count > 0)
            {
                ds.Tables["Customers"].Rows[0].Delete();   //delete a record

            }
            foreach (DataRow CustomerRow in ds.Tables["Customers"].GetChanges().Rows)
            {
                //use row state to determine the deleted row
                switch (CustomerRow.RowState)  
                {
                    case DataRowState.Deleted:
                        //use original version to get the ID
                        myCommand.Parameters["@CustomerID"].Value= 
                        CustomerRow["CustomerID",DataRowVersion.Original];   
                        myCommand.ExecuteNonQuery();
                        break;
                 }
                          
            }
                       

        }

Calling from the Main method

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace ADONET2ConnectedCompact
{
    class Program
    {
        static void Main(string[] args)
        {
            // Step 1: Handshake with the database
            string connectionstring = System.Configuration.ConfigurationManager.
                                      ConnectionStrings["MyDBConnection"].ToString();
            SqlConnection myConnection = new SqlConnection(connectionstring);
           myConnection.Open();
           try
           {
               //DemonstrateRowState();
               //Console.Read();

                   // Step 2: Specify a command (request)
               SqlCommand myCommand = myConnection.CreateCommand();

               myCommand.CommandType = CommandType.Text;
               myCommand.CommandText = "SELECT TOP 10 CustomerID,TerritoryID,AccountNumber,
                                       CustomerType,ModifiedDate FROM Sales.Customer";
              
               // now use data sets instead of data read
               SqlDataAdapter da = new SqlDataAdapter();
               da.SelectCommand = myCommand;
               DataSet ds = new DataSet();
               da.Fill(ds);

               // Rename the table in dataset
               ds.Tables[0].TableName = "Customers";
               
               //Dataset is a disconnected representation of the 
               //data in memory. You can update/delete/insert data into this dataset 
               //and then save to the database.
               
               //Update Example
               UpdateCustomer(ds, myConnection);
               //Delete Example
               DeleteCustomer(ds, myConnection);
           }
           catch (Exception ex)
           {
               Console.WriteLine("Exception{0}: ", ex.Message);
           }
           finally
           {
               myConnection.Close();
           }
            

        }

As you saw in last few posts, dataset allows you to store data in memory, move it around, insert a new row, update or delete an existing row and then upload changes to the database.

So far we concerned ourselves with untyped datasets. In the next post we will review typed datasets and the differences between the two.

Thank you.

Using ADO.NET - Part II

In previous post we reviewed ADO.NET object and data reader to read through the data. As I mentioned, data reader is read only, forward only, connected mechanism. Today, we will review data adapter and dataset.

Dataset is a disconnected representation of the data. A dataset is a subset of the database and can has one or more data tables. You can also create relationship between two tables just as in a database. There are two types of datasets - Typed and Untyped. Typed datasets are created during design time and are schematic representation of the subset of your database. You can create typed datasets by creating table schema either manually or by dragging the table from the database on to the design surface. Untyped datasets are create in memory. Although you can create relationship between the tables and enforce data types but it is much easier with typed datasets.

Today, we will review how you can use data adapter and its "Fill" method to populate untyped dataset declared in memory. In future post we will see how you can insert new data or update existing data in a dataset and then save it to the database.

Example

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace ADONET2ConnectedCompact
{
    class Program
    {
        static void Main(string[] args)
        {
            // Step 1: Handshake with the database
            string connectionstring = System.Configuration.ConfigurationManager
                      .ConnectionStrings["MyDBConnection"].ToString();
            SqlConnection myConnection = new SqlConnection(connectionstring);
           myConnection.Open();
           try
           {
               // Step 2: Specify a command (request)
               SqlCommand myCommand = myConnection.CreateCommand();

               myCommand.CommandType = CommandType.Text;
               myCommand.CommandText = "SELECT TOP 10 CustomerID,TerritoryID,
               AccountNumber,CustomerType,ModifiedDate FROM Sales.Customer";
              
               // now use data sets instead of data read
               SqlDataAdapter da = new SqlDataAdapter();
               da.SelectCommand = myCommand;
               DataSet ds = new DataSet();
               da.Fill(ds);
               foreach (DataRow customerrow in ds.Tables[0].Rows)
               {
                   Console.WriteLine(customerrow[0].ToString() + " " + 
                                 customerrow[1] + " " + customerrow[2]);
               }
               
               //You can also name the table in the databaset. Also, if you were 
               //using a stored procedure which returned multiple result sets, 
               //each will go in its own table. For example, if your stored proc 
               //returned Customers and Orders as two separate result set, 
               //Table[0] will have the first result set and table[1] 
               //will have the second result set. Dataset is a subset of 
              //your SQL Database schema and in a typed database you can 
              //also define and enforce relationship integrity and data types.
              
               // Rename the table in dataset
              
               ds.Tables[0].TableName = "Customers";
               foreach (DataRow customerrow in ds.Tables["Customers"].Rows)
               {
                   Console.WriteLine(customerrow[0].ToString() + " " 
                   + customerrow[1] + " " + customerrow[2]);
               }
               Console.Read();

               
           }
           catch (Exception ex)
           {
               Console.WriteLine("Exception{0}: ", ex.Message);
           }
           finally
           {
               myConnection.Close();
           }
            

        }
    }
}

As you can see in this example, a dataset is a collection of datatables. Datatables have rowstate which you can use to determine whether a row has been changed or a new row has been inserted.

Dataset is disconnected and you can pass it to other objects or bind to other elements such as grid, dropdownlist etc.

In future post we will discuss using dataset to insert / update / delete rows and then save them to the database.

Thank you.