Python for open data file formats

If you are an open data researcher you will need to handle a lot of different file formats from datasets. Sadly, most of the time, you don’t have the opportunity to choose which file format is the best for your project, but you have to comport with all of them to be sure that you won’t find a dead end. There’s always someone who knows the solution to your problem, but that doesn’t mean that answers come easy. Here is a guide for each file formats from the open data handbook and a suggestion with a python library to use.

JSON is a simple file format that is very easy for any programming language to read. Its simplicity means that it is generally easier for computers to process than others, such as XML. Working with JSON in Python is almost the same such as working with a python dictionary. You will need the json library, but it is preinstalled to every python 2.6 and after.

import json
json_data = open("file root")
data = json.load(json_data)

Then data[“key”] prints the data for the json

XML is a widely used format for data exchange because it gives good opportunities to keep the structure in the data and the way files are built on, and allows developers to write parts of the documentation in with the data without interfering with the reading of them. This is pretty easy in python as well. You will need minidom library. It is also preinstalled.

from xml.dom import minidom
xmldoc = minidom.parse("file root")
itemlist = xmldoc.getElementsByTagName("name")

This prints the data for the “name” tag.

RDF is a W3C-recommended format and makes it possible to represent data in a form that makes it easier to combine data from multiple sources. RDF data can be stored in XML and JSON, among other serializations. RDF encourages the use of URLs as identifiers, which provides a convenient way to directly interconnect existing open data initiatives on the Web. RDF is still not widespread, but it has been a trend among Open Government initiatives, including the British and Spanish Government Linked Open Data projects. The inventor of the Web, Tim Berners-Lee, has recently proposed a five-star scheme that includes linked RDF data as a goal to be sought for open data initiatives I use rdflib for this file format. Here is an example.

from rdflib.graph import Graph
g = Graph()
g.parse("file root", format="format")
for stmt in g:
   print(stmt)

In rdf you can run queries too and return only the data you want. But this isn’t easy as parsing it.

Spreadsheets. Many authorities have information left in the spreadsheet, for example Microsoft Excel. This data can often be used immediately with the correct descriptions of what the different columns mean. However, in some cases there can be macros and formulas in spreadsheets, which may be somewhat more cumbersome to handle. It is therefore advisable to document such calculations next to the spreadsheet, since it is generally more accessible for users to read. I prefer to use a tool like xls2csv and then use the output file as a csv. But if you want for any reason to work with an xls, here is the best source I had www.python-excel.org. The most popular is the first one, xlrd. There is also another library openpyxl, where you can work with xlsx files.

Comma Separated Files (CSV) files can be a very useful format because it is compact and thus suitable to transfer large sets of data with the same structure. However, the format is so spartan that data are often useless without documentation since it can be almost impossible to guess the significance of the different columns. It is therefore particularly important for the comma-separated formats that documentation of the individual fields are accurate. Furthermore it is essential that the structure of the file is respected, as a single omission of a field may disturb the reading of all remaining data in the file without any real opportunity to rectify it, because it cannot be determined how the remaining data should be interpreted. You can use the CSV python library. Here is an example.

import csv
with open('eggs.csv', 'rb') as csvfile:
   file = csv.reader("file root", delimiter=' ', quotechar='|')
   for row in file:
      print ', '.join(row)

Plain Text (txt) are very easy for computers to read. They generally exclude structural metadata from inside the document however, meaning that developers will need to create a parser that can interpret each document as it appears. Some problems can be caused by switching plain text files between operating systems. MS Windows, Mac OS X and other Unix variants have their own way of telling the computer that they have reached the end of the line. You can load the txt file but how you will use it after that, it depends on the data format.

text_file = open("file root", "r")
lines = text_file.read()

This example will return the whole txt.

PDF Here is the biggest problem in open data file formats. Many datasets have their data in pdf and unfortunately it isn’t easy to read and then edit them. PDF is really presentation oriented and not content oriented. But you can use PDFMiner to work with it. I won’t include any example here since it isn’t a trivial one, but you can find anything you want in their documentation.

HTML. Nowadays much data is available in HTML format on various sites. This may well be sufficient if the data is very stable and limited in scope. In some cases, it could be preferable to have data in a form easier to download and manipulate, but as it is cheap and easy to refer to a page on a website, it might be a good starting point in the display of data. Typically, it would be most appropriate to use tables in HTML documents to hold data, and then it is important that the various data fields are displayed and are given IDs which make it easy to find and manipulate data. Yahoo has developed a tool yql that can extract structured information from a website, and such tools can do much more with the data if it is carefully tagged. I have used many times a python library called Beautiful Soup for my projects.

from bs4 import BeautifulSoup
soup = BeautifulSoup(html_file)
soup.title
soup.title.name
soup.title.string
soup.title.parent.name
soup.p
soup.p['class']
soup.a
soup.find_all('a')
soup.find(id="link3")

Those are only a few of what you can do with this library. By calling the tag, will return the content. You can find more on their documentation.

Scanned image. Yes. It is true. Probably the least suitable form for most data, but both TIFF and JPEG-2000 can at least mark them with documentation of what is in the picture - right up to mark up an image of a document with full text content of the document. If images are clean, containing only text and without any noise, you can use a library called pytesser. You will need the PIL library to use it. Here is an example.

from pytesser import *
image = Image.open('fnord.tif')  # Open image object using PIL
print image_to_string(image)

Proprietary formats. Last but not least, some dedicated systems, etc. have their own data formats that they can save or export data in. It can sometimes be enough to expose data in such a format - especially if it is expected that further use would be in a similar system as that which they come from. Where further information on these proprietary formats can be found should always be indicated, for example by providing a link to the supplier’s website. Generally it is recommended to display data in non-proprietary formats where feasible.. I suggest to google if there is any library specific for this dataset.

Data Structure in C#

When working with data, we need ways to store it in variables so we can manipulate it. We will use two new data structures that we didn't cover in Introduction to Programming. These are lists and dictionaries.
(This chapter contains the same content as the "Data Structures in Python" chapter of the "Websites with Python Flask" workshop.)
If you are already comfortable with lists and dictionaries then you can skip this and move on to the next chapter.

Understanding data structures

Back in the first session we introduced three of the most common data types used in programming: numbers, strings and booleans. We assigned those data types to variables one-by-one, like so:

x = 3          # numbers
a = "gorillas" # strings
t = True       # booleans

But what if we need something more complicated, like a shopping list? Assigning a variable for every item in the list would makes things very complicated:

item_1 = "milk"
item_2 = "cheese"
item_3 = "bread"

Lists

Fortunately we don't have to do this. Instead, we have the list data type. An empty list is simply []

shopping_list = []

When you are in the Python interpreter you can see what is inside a list by just typing the name of the list. For example:

>>> shopping_list
[]

The interpreter shows us that the list is empty.
Now we can add items to shopping_list. Try typing the following commands into the Python interpreter.

shopping_list.append("milk")
shopping_list.append("cheese")
shopping_list.append("bread")

What is in the shopping list? What happens when you append numbers or booleans to the list?
You can also assign a list with some items in it all in a single line, like this:

shopping_list = [ "milk", "cheese", "bread" ]

To remove an item from the list we use remove():

shopping_list.remove("milk")

Lists can easily be processed in a for loop. Have a look at this example which prints each item of the list in a new row:

for item in shopping_list:
    print(item)

And that's it! Python also makes it really easy to check if something is in a list or not:

if "milk" in shopping_list:
    print("Delicious!")

if "eggs" not in shopping_list:
    print("Well we can't have that!")
    shopping_list.append("eggs")

Lists are the most common data structure in programming. There are lots of other things you can do with lists, and all languages have their own subtly different interpretation. But fundamentally they are all very similar.
In summary:

shopping_list = []
shopping_list.append("cookies")
shopping_list.remove("cookies")

Dictionaries

The other main data type is the dictionary. The dictionary allows you to associate one piece of data (a "key") with another (a "value"). The analogy comes from real-life dictionaries, where we associate a word (the "key") with its meaning. It's a little harder to understand than a list, but Python makes them very easy to deal with.
You can create a dictionary with {}

foods = {}

And you can add items to the dictionary like this:

foods["banana"] = "A delicious and tasty treat!"
foods["dirt"]   = "Not delicious. Not tasty. DO NOT EAT!"

The keys in this example are "banana" and "dirt", and the values are the things that we assign to them. You can use any data type that won't change as a dictionary key. Check it out by using a number, a boolean value, and a list as keys in a dictionary. What does this say about strings?
As with lists, you can always see what is inside a dictionary:

>>> foods
{'banana': 'A delicious and tasty treat!', 'dirt': 'Not delicious. Not tasty. DO NOT EAT!'}

You can look up any entry in the dictionary by its key:

>>> foods["banana"]
'A delicious and tasty treat!'

If the key isn't found in the dictionary, a KeyError occurs:

>>> foods["cheese"]
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
KeyError: 'cheese'

For this reason, you can test whether a key is in the dictionary or not, by using the keyword in:

if "cheese" in foods:
    print("Cheese is one of the known foods!")
    print(foods["cheese"])

not in works as well, just like with lists.
You can delete from a dictionary as well. We don't really need to include an entry for dirt:

del foods["dirt"]

What makes dictionaries so useful is that we can give meaning to the items within them. A list is just a bag of things, but a dictionary is a specific mapping of something to something else. By combining lists and dictionaries you can describe basically any data structure used in computing.
For example, you can easily add a list to a dictionary:

ingredients = {}
ingredients["blt sandwich"] = ["bread", "lettuce", "tomato", "bacon"]

Or add dictionaries to lists:

europe = []
germany = {"name": "Germany", "population": 81000000}
europe.append(germany)
luxembourg = {"name": "Luxembourg", "population": 512000}
europe.append(luxembourg)

Outside of Python, dictionaries are often called hash tables, hash maps or just maps.

File handling in C#

A file is a collection of data stored in a disk with a specific name and a directory path. When a file is opened for reading or writing, it becomes a stream.

The stream is basically the sequence of bytes passing through the communication path. There are two main streams: the input stream and the output stream. The input stream is used for reading data from file (read operation) and the output stream is used for writing into the file (write operation).

C# I/O Classes

The System.IO namespace has various classes that are used for performing numerous operations with files, such as creating and deleting files, reading from or writing to a file, closing a file etc.

The following table shows some commonly used non-abstract classes in the System.IO namespace:

I/O Class	Description
BinaryReader	Reads primitive data from a binary stream.
BinaryWriter	Writes primitive data in binary format.
BufferedStream	A temporary storage for a stream of bytes.
Directory	Helps in manipulating a directory structure.
DirectoryInfo	Used for performing operations on directories.
DriveInfo	Provides information for the drives.
File	Helps in manipulating files.
FileInfo	Used for performing operations on files.
FileStream	Used to read from and write to any location in a file.
MemoryStream	Used for random access to streamed data stored in memory.
Path	Performs operations on path information.
StreamReader	Used for reading characters from a byte stream.
StreamWriter	Is used for writing characters to a stream.
StringReader	Is used for reading from a string buffer.
StringWriter	Is used for writing into a string buffer.

The FileStream Class

The FileStream class in the System.IO namespace helps in reading from, writing to and closing files. This class derives from the abstract class Stream.

You need to create a FileStream object to create a new file or open an existing file. The syntax for creating a FileStream object is as follows:

FileStream <object_name> = new FileStream( <file_name>, <FileMode Enumerator>, <FileAccess Enumerator>, <FileShare Enumerator>);

For example, we create a FileStream object F for reading a file named sample.txt as shown:

FileStream F = new FileStream("sample.txt", FileMode.Open, FileAccess.Read, FileShare.Read);

Parameter	Description
FileMode	The FileMode enumerator defines various methods for opening files. The members of the FileMode enumerator are: Append: It opens an existing file and puts cursor at the end of file, or creates the file, if the file does not exist. Create: It creates a new file. CreateNew: It specifies to the operating system, that it should create a new file. Open: It opens an existing file. OpenOrCreate: It specifies to the operating system that it should open a file if it exists, otherwise it should create a new file. Truncate: It opens an existing file and truncates its size to zero bytes.
FileAccess	FileAccess enumerators have members: Read, ReadWrite and Write.
FileShare	FileShare enumerators have the following members: Inheritable: It allows a file handle to pass inheritance to the child processes None: It declines sharing of the current file Read: It allows opening the file for reading ReadWrite: It allows opening the file for reading and writing Write: It allows opening the file for writing

Example

The following program demonstrates use of the FileStream class:

using System;
using System.IO;

namespace FileIOApplication
{
   class Program
   {
      static void Main(string[] args)
      {
         FileStream F = new FileStream("test.dat", FileMode.OpenOrCreate, FileAccess.ReadWrite);
         for (int i = 1; i <= 20; i++)
         {
            F.WriteByte((byte)i);
         }
         
         F.Position = 0;
         for (int i = 0; i <= 20; i++)
         {
            Console.Write(F.ReadByte() + " ");
         }
         F.Close();
         Console.ReadKey();
      }
   }
}

When the above code is compiled and executed, it produces the following result:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 -1

Program to insert data using C#

using System; using System.Data; using System.Data.SqlClient; namespace InsertingData { class sqlinsertdata { static void Main(string[] args) { try { SqlConnection conn = new SqlConnection("Data source=USER-PC; Database=Emp123;User Id=sa;Password=sa123"); conn.Open(); SqlCommand cmd = new SqlCommand("insert into <Table Name>values(1,'kamal',10000);",conn); cmd.ExecuteNonQuery(); Console.WriteLine("Inserting Data Successfully"); conn.Close(); } catch(Exception e) { Console.WriteLine("Exception happen while creating table:" + e.Message + "\t" + e.GetType()); } Console.ReadKey(); } } }

Private vs. Public vs. Hybrid cloud to use for businesses

When choosing to move to Cloud Computing, you should first decide which type of Cloud you want to utilize. Cloud Computing services are distributed in three main forms: public, private, and hybrid. These three types differ in the level of security available and the administration required for a service. Depending on the Cloud Computing service you are looking to host, you will want to choose the appropriate model for your business requirement.

Public Cloud

The Public Cloud refers to the hosting of a customer’s computing infrastructure at a Cloud vendor’s premises. This means that the services and infrastructure are provisioned and provided from a remote, geographically-distanced location over the public Internet. The customer has no visibility and control over where the Cloud services are being hosted. The core computing infrastructure is shared between many organizations; however, each organization’s data, applications, and infrastructure is logically segregated so only authorized users are allowed access.

An example of a Public Cloud service is NIRIX’s oneBackup, an online, off-site backup program which uses the Internet to backup data off customer’s servers to the NIRIX datacenter for storage.

Private Cloud

The Private Cloud service means that the computing infrastructure is hosted on a “Private Cloud” private hosting platform, dedicated to a particular organization, and not shared with other organizations. There are two types of Private Clouds: on-premise Private Clouds and externally-hosted Private Clouds (externally-hosted Private Clouds are also exclusively used by one organization, but are hosted by a third-party that specializes in Cloud infrastructure).

An example of the Private Cloud is NIRIX’s oneServer with dedicated servers. With this type of service, the customer is provisioned a private server to host applications/web applications, websites, other software, etc. Only the customer has access to the server.

Hybrid Cloud

As a median to the Public and Private Cloud, the Hybrid Cloud has become more popular for businesses wanting to adopt Cloud Computing for efficiency and cost savings, but wanting privacy and control for core business data and systems. The usage of both Private and Public Clouds together is called a Hybrid Cloud. This often entails the customer company’s use of its own personal (in-house) computing infrastructure for regular operations, combined with the hosting of certain specific applications or systems on the Cloud.

An example of the Hybrid Cloud would be a business maintaining in-house backup, but using a services like oneExchange, a Cloud-based email application provided by NIRIX.

When is Each Type of Cloud Appropriate?

Public Cloud For the Masses

Public Clouds are popular with businesses looking to cut IT costs. These businesses are concerned with security, but scalability, efficiency, and affordability are the most important features. These businesses often lack in-house IT administration and expertise.

Public Clouds offer the best level of efficiency and affordability, as well as the ability to scale on-demand, as customers share the larger Cloud. However, Public Clouds can be more vulnerable than Private Clouds as they are public hosting platforms, and may have limited flexibility – configuration, security, and service level – as they are hosted a shared Cloud infrastructure.

Private Cloud when Security, Flexibility, and Control is Critical

Businesses often choose the Private Cloud because they require control of their business-critical data and applications and also require the highest level of security, often due to regulatory compliance, and want private hosting. Private Clouds are popular with highly-regulated industries as they are often more comfortable having their data hosted privately and physically separated from other businesses.

Private Clouds offer the greatest level of security and control. However, Private Clouds are more expensive than Public Clouds as the company needs to invest in and maintain hardware or Private Cloud infrastructure and software (dedicated hosting) and are more limited in scalability. Private Clouds often require in-house IT administration and expertise.

The Best of Both Worlds

Businesses often chose the Hybrid Cloud, a combination of shared hosting and dedicated hosting, to enjoy the best of both worlds. The Hybrid Cloud offers businesses an option to host some applications on the Public Cloud to cut costs, and increase scalability and efficiency while keeping critical data in-house or on a Private Cloud infrastructure for the utmost security and control. This type of Cloud can become complicated as it requires the tracking of multiple providers and security platforms to ensure all systems work cohesively.

When transitioning to the Cloud, it is important to do your homework. You should carefully consider which type of Cloud services best meet your business requirements, and then research Cloud Service Providers (CSPs) that offer your chosen services. Not all Cloud Service Providers support all types of Cloud, and there are no “one solution fits all” situations in the Cloud. Also keep in mind that transitioning from one form of Cloud to another may not be seamless, so initial assessment and evaluation is important. Ensure that you understand the services you require, the level of security you need, any legal obligations to certification and compliancy your industry may require, and your chosen Cloud Service Provider’s stance on Cloud types.

Understanding ADO.NET Components

ADO.NET Components
The ADO.NET is designed to work with multiple kinds of data sources in same fashion. You can categorize ADO.NET components in three categories: disconnected, common or shared and the .NET data providers. The disconnected components build the basic ADO.NET architecture. You can use these components (or classes) with or without data providers. For example, you can use a DataTable object with or without providers and shared or common components are the base classes for data providers. Shared or common components are the base classes for data providers and shared by all data providers. The data provider components are specifically designed to work with different kinds of data sources. For example, ODBC data providers work with ODBC data sources and OleDb data providers work with OLE-DB data sources.
Figure represents the ADO.NET components model and how they work together:

A data provider is a set of components, such as Connection, Command, DataAdapter and DataReader. The Connection is the first component that talks to a data source. The Connection object establishes a connection to a data source and works as a connection reference in Command and DataAdapter objects. A Command object executes a SQL query and stored procedures to read, add, update, and delete data of a data source via a DataAdapter. A DataAdapter is a bridge between a dataset and the connection. It uses Command Object to execute SQL queries and stored procedures.All data providers share the ADO.NET common components. These components represent the data. Some of the common components are DataSet, DataView, and DataViewManager. The DataSet uses XML to store and transfer data between the applications and the data provider. A DataSet is a set of DataTable objects. A DataTable represents a database table. The DataView and DataViewManager objects provide single or multiple views of a dataset. You can attach a DataView or a DataViewManager directly to data–bound controls such as a DataGrid or DataList. Other common components are DataTable, DataRow, DataColumn and so on. Now, I'll break down the ADO.NET model to show how it works.

 

Working with Data using ADO.NET in C#

This post explains about The Role of DataSets in ADO.NET , Using DataSets in ASP.NET , Saving DataSets in Session State, Using DataTable Constraints, DataSet, DataRelations Using DataSets with DataAdapters to Modify Data and the Transactional Model in DataSets.

ASP.NET: Managing Data with ADO.NET DataSets and C#

Introduction

In this article we will explore the DataSet, DataTable and DataRelations classes. Those who have worked with classic asp will be familiar with record sets. Record set was the individual rows that were retrieved from the database and than binded to the screen using html table or any other html tags.

DataSet on the other hand is a new technology in Asp.net and this maps the entire database maps in its self. What I mean is it can have all the fields of the tables which we specify. In other words its a disconnected database. By disconnected I mean that if you make changes in Dataset those changes will not be reflected in the actual database.

The Role of DataSets in ADO.NET

The important of datasets can never be denied nor it can be questioned. DataSet is a mirror image of the table or the query which you run. DataSets makes it easier to edit and update the information. Another good thing is that datasets are disconnected in nature so, if you make any changes in the dataset it will not reflect in the database unless use special methods to perform the change and confirm it.
Using DataSets in ASP.NET

Let’s see how we can use datasets in Asp.net. The most common way of using the DataSet control is with the dataadapter. The DataAdapter fills the dataset control with data coming from the database. Let’s see how we can perform this simple action:

SqlDataAdapter ad = new SqlDataAdapter("SELECT * FROm Categories",myConnection);

DataSet ds = new DataSet();

ad.Fill(ds,"Categories");

DataGrid1.DataSource = ds;

DataGrid1.DataBind();

Explanation of the code:

1. First we declare a simple dataadapter instance which fetches all the rows from the Categories table. ( Categories table is present in the Northwind database which is shipped with Sql Server 2000 ).

2. New we created an instance of the DataSet control.

3. Next we fill the dataset with the data coming from the dataadapter control. As you can see that I have written ad.Fill(ds,"Categories"); you can also write ad.Fill(ds); and this will work fine too. In the former case I was telling the dataadapter which table I am using in my query and later the dataadapter was finding the table itself.

4. Finally I used DataGrid to bind the data on the screen.

Iterating through the DataSet:

Here is a simple code to iterate through the dataset and select individual items instead of selecting all the data in the DataSet.

SqlDataAdapter ad = new SqlDataAdapter("SELECT * FROm Categories",myConnection);

DataSet ds = new DataSet();

ad.Fill(ds,"Categories");

DataTable dt = ds.Tables["Categories"];


Response.Write(dt.Rows[0][1].ToString()) ;

1) First few lines are identical which we have done before.

2) In the forth line we declared the DataTable object. DataTable object is used to hold a single table. As I already told you that dataset can hold multiple tables depending upon the query so we can assign a single table from the dataset to the datatable object. I have done that using the line.

DataTable dt = ds.Tables["Categories"];

3) Next we are printing the first row of the second column. Always remember that the number of the rows and columns in the database does not start with 1 but it starts at 0. So in this line of code:

Response.Write(dt.Rows[0][1].ToString()) ;

This line of code means that get Row ‘0’ and Column 1 of the from the datatable. And so we get the value and it prints out "Beverages".

Saving dataSets in Session State:

DataSets can also be saved in the Session State so that it can be available in other areas of the application and in new pages. The way of storing the DataSet in Session State is very easy.

Session["MyDataSet"] = DataSet;

Later if you want to retrieve the DataSet from the Session State you can easily do this by using this line of code:

DataSet ds = (DataSet) Session["MyDataSet"]

As you can see that when I am retrieving the values from the Session object I am casting it into the dataset object. This is because this is explicit conversion and requires casting. I am unboxing in this case.



Using DataTable:

DataTable is also a collection which you can use like dataset. The difference is that DataTable represents only one table. Usually its used when you don’t have a database and want to save something in the collection.
Here is few lines of code that you can use to make a populate the datatable:

// Create a new DataTable.
System.Data.DataTable myDataTable = new DataTable("ParentTable");
// Declare variables for DataColumn and DataRow objects.
DataColumn myDataColumn;
DataRow myDataRow;

// Create new DataColumn, set DataType, ColumnName and add to DataTable.
myDataColumn = new DataColumn();
myDataColumn.DataType = System.Type.GetType("System.Int32");
myDataColumn.ColumnName = "id";
myDataColumn.ReadOnly = true;
myDataColumn.Unique = true;
// Add the Column to the DataColumnCollection.
myDataTable.Columns.Add(myDataColumn);

DataSet Relations:

In a relational representation of data, individual tables contain rows that are related to one another using a column or set of columns. In the ADO.NET DataSet, the relationship between tables is implemented using a DataRelation. When you create a DataRelation, the parent-child relationships of the columns are managed only through the relation. The tables and columns are separate entities. In the hierarchical representation of data that XML provides, the parent-child relationships are represented by parent elements that contain nested child elements. To facilitate the nesting of child objects when a DataSet is synchronized with an XmlDataDocument or written as XML data using WriteXml, the DataRelation exposes a Nested property. Setting the Nested property of a DataRelation to true causes the child rows of the relation to be nested within the parent column when written as XML data or synchronized with an XmlDataDocument. The Nested property of the DataRelation is false, by default. For example, consider the following DataSet:


SqlDataAdapter custDA = new SqlDataAdapter("SELECT CustomerID, CompanyName FROM Customers", nwindConn);
SqlDataAdapter orderDA = new SqlDataAdapter("SELECT OrderID, CustomerID, OrderDate FROM Orders", nwindConn);

nwindConn.Open();

DataSet custDS = new DataSet("CustomerOrders");
custDA.Fill(custDS, "Customers");
orderDA.Fill(custDS, "Orders");

nwindConn.Close();

DataRelation custOrderRel = custDS.Relations.Add("CustOrders",
custDS.Tables["Customers"].Columns["CustomerID"],
custDS.Tables["Orders"].Columns["CustomerID"]);
Because the Nested property of the DataRelation object is not set to true for this DataSet, the child objects will not be nested within the parent elements when this DataSet is represented as XML data.

The following code example shows the output that will result from calling WriteXml on the DataSet.

ALFKI
Alfreds Futterkiste


ANATR
Ana Trujillo Emparedados y helados


10643
ALFKI
1997-08-25T00:00:00


10692
ALFKI
1997-10-03T00:00:00


10308
ANATR
1996-09-18T00:00:00

Note that the Customers element and the Orders elements are shown as sibling elements. If you wanted to have the Orders elements show up as children of their respective parent elements, the Nested property of the DataRelation would need to be set to true and you would add the following:

The following code shows what the resulting output would look like, with the Orders elements nested within their respective parent elements.



ALFKI

10643
ALFKI
1997-08-25T00:00:00


10692
ALFKI
1997-10-03T00:00:00

Alfreds Futterkiste


ANATR

10308
ANATR
1996-09-18T00:00:00

Ana Trujillo Emparedados y helados

Using DataSet with DataAdapters to Modify Data

We can also use DataAdapters to modify data in the database. There are several properties that are available to perform these operations.

Let’s see some of the properties:

ad.SelectCommand = new SqlCommand("SELECT * FROM Person",myConnection);

ad.UpdateCommand = new SqlCommand("UPDATE TABLE Person SET Name=’john’ WHERE PersonID = 1");

ad.DeleteCommand = new SqlCommand("DELETE TABLE Person WHERE PersonID = 1");

ad.InsertCommand = new SqlCommand("INSERT INTO PERSON VALUES(‘NewName’) ");

All the properties are self explanatory. SelectCommand property is used to select, Update command property is used to update and so forth.

When we issue the command we can just later fill the Dataset with the DataAdatper and later we can bind the dataset to datagrid to view the result on the user interface.