Tutorials ASP.NET Managing Data with ADO.NET DataSets and C# Managing Data with ADO.NET DataSets and C# - Page 2Using 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.
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.
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.
First Page: Managing Data with ADO.NET DataSets and C#
| how do you connect to Excel file using C# |
| How can we get the data in the front end regarding to the value of a selected value from the database using dataset and data adapter. |
