using System; using System.Data; using System.Collections.Generic; using System.Text; class Program { static void Main(string[] args) { DataSet thisDataSet = new DataSet(); thisDataSet.ReadXml("nwinddata.xml"); foreach (DataRow custRow in thisDataSet.Tables["Customers"].Rows) { Console.WriteLine("Customer ID: " + custRow["CustomerID"] + " Name: " + custRow["CompanyName"]); } Console.WriteLine("Table created by ReadXml is called {0}", thisDataSet.Tables[0].TableName); } }
DataSet Read
using System; using System.Data; // Use ADO.NET namespace using System.Data.SqlClient; // Use SQL Server data provider namespace using System.Collections.Generic; using System.Text; class Program { static void Main(string[] args) { SqlConnection thisConnection = new SqlConnection( @"Server=(local)sqlexpress;Integrated Security=True;" + "Database=northwind"); SqlDataAdapter thisAdapter = new SqlDataAdapter( "SELECT CustomerID, ContactName FROM Customers", thisConnection); DataSet thisDataSet = new DataSet(); thisAdapter.Fill(thisDataSet, "Customers"); foreach (DataRow theRow in thisDataSet.Tables["Customers"].Rows) { Console.WriteLine(theRow["CustomerID"] + " " + theRow["ContactName"]); } thisConnection.Close(); } }
For each row in DataSet, reference the column data by column name
using System; using System.Data; using System.Data.SqlClient; class Class1{ static void Main(string[] args){ SqlConnection thisConnection = new SqlConnection("server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI"); SqlDataAdapter thisAdapter = new SqlDataAdapter("SELECT ID, FirstName FROM Employee", thisConnection); DataSet thisDataSet = new DataSet(); thisAdapter.Fill(thisDataSet, "Employee"); foreach (DataRow theRow in thisDataSet.Tables["Employee"].Rows) { Console.WriteLine(theRow["ID"] + " " + theRow["FirstName"]); } } }
DataSet Delete
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
class Program {
static void Main(string[] args) {
DataSet theCarsInventory = new DataSet();
SqlConnection cn = new SqlConnection(“server=(local);User ID=sa;Pwd=;database=Cars”);
SqlDataAdapter da = new SqlDataAdapter(“SELECT * FROM Inventory”, cn);
SqlCommandBuilder invBuilder = new SqlCommandBuilder(da);
da.Fill(theCarsInventory, “Inventory”);
PrintDataSet(theCarsInventory);
try {
theCarsInventory.Tables[“Inventory”].Rows[1].Delete();
da.Update(theCarsInventory, “Inventory”);
} catch (Exception e) {
Console.WriteLine(e.Message);
}
theCarsInventory = new DataSet();
da.Fill(theCarsInventory, “Inventory”);
PrintDataSet(theCarsInventory);
}
static void PrintDataSet(DataSet ds) {
Console.WriteLine(“Tables in '{0}' DataSet.
“, ds.DataSetName);
foreach (DataTable dt in ds.Tables) {
Console.WriteLine(“{0} Table.
“, dt.TableName);
for (int curCol = 0; curCol < dt.Columns.Count; curCol++) {
Console.Write(dt.Columns[curCol].ColumnName.Trim() + " ");
}
for (int curRow = 0; curRow < dt.Rows.Count; curRow++) {
for (int curCol = 0; curCol < dt.Columns.Count; curCol++) {
Console.Write(dt.Rows[curRow][curCol].ToString().Trim() + " ");
}
Console.WriteLine();
}
}
}
}
[/csharp]
Use the Merge() method
using System; using System.Data; using System.Data.SqlClient; class Merge { public static void Main() { SqlConnection mySqlConnection =new SqlConnection("server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;"); SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); mySqlCommand.CommandText = "SELECT ID, FirstName, LastName, Address " + "FROM Customers " + "WHERE ID IN ('001', '002', '003')"; SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(); mySqlDataAdapter.SelectCommand = mySqlCommand; DataSet myDataSet = new DataSet(); mySqlConnection.Open(); mySqlDataAdapter.Fill(myDataSet, "Customers"); mySqlCommand.CommandText = "SELECT ID, FirstName, LastName, Address " + "FROM Customers " + "WHERE CustomerID IN ('008', '009')"; DataSet myDataSet2 = new DataSet(); mySqlDataAdapter.Fill(myDataSet2, "Customers2"); mySqlCommand.CommandText = "SELECT TOP 5 ProductID, ProductName, UnitPrice " + "FROM Products " + "ORDER BY ProductID"; DataSet myDataSet3 = new DataSet(); mySqlDataAdapter.Fill(myDataSet3, "Products"); mySqlConnection.Close(); myDataSet.Merge(myDataSet2); myDataSet.Merge(myDataSet3, true, MissingSchemaAction.Add); foreach (DataTable myDataTable in myDataSet.Tables) { Console.WriteLine(" Reading from the " + myDataTable + "DataTable"); foreach (DataRow myDataRow in myDataTable.Rows) { foreach (DataColumn myDataColumn in myDataTable.Columns) { Console.WriteLine(myDataColumn + "= " + myDataRow[myDataColumn]); } } } } }
Populate a DataSet with multiple DataTable objects using multiple SELECT statements
using System; using System.Data; using System.Data.SqlClient; class MultipleDataTables { public static void Main() { SqlConnection mySqlConnection =new SqlConnection("server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;"); SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); mySqlCommand.CommandText = "SELECT TOP 2 ID, FirstName, LastName " + "FROM Employee " + "ORDER BY ID;" + "SELECT ID, LastName " + "FROM MyEmployee " + "WHERE ID = 9;"; SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(); mySqlDataAdapter.SelectCommand = mySqlCommand; DataSet myDataSet = new DataSet(); mySqlConnection.Open(); int numberOfRows = mySqlDataAdapter.Fill(myDataSet); Console.WriteLine("numberOfRows = " + numberOfRows); mySqlConnection.Close(); myDataSet.Tables["Table"].TableName = "Employee"; myDataSet.Tables["Table1"].TableName = "MyEmployee"; foreach (DataTable myDataTable in myDataSet.Tables) { Console.WriteLine(" Reading from the " + myDataTable.TableName + "DataTable"); foreach (DataRow myDataRow in myDataTable.Rows) { foreach (DataColumn myDataColumn in myDataTable.Columns) { Console.WriteLine(myDataColumn + "= " + myDataRow[myDataColumn]); } } } } }
Populate a DataSet object with multiple DataTable objects
using System; using System.Data; using System.Data.SqlClient; class MultipleDataTables2 { public static void Main() { SqlConnection mySqlConnection =new SqlConnection("server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;"); SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); mySqlCommand.CommandText = "SELECT TOP 2 ID, FirstName, LastName " + "FROM Employee " + "ORDER BY ID"; SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(); mySqlDataAdapter.SelectCommand = mySqlCommand; DataSet myDataSet = new DataSet(); mySqlConnection.Open(); int numberOfRows = mySqlDataAdapter.Fill(myDataSet, "Employee"); Console.WriteLine("numberOfRows = " + numberOfRows); mySqlDataAdapter.SelectCommand.CommandText = "SELECT ID, Name " + "FROM Customers " + "WHERE ID = '001'"; numberOfRows = mySqlDataAdapter.Fill(myDataSet, "Customers"); Console.WriteLine("numberOfRows = " + numberOfRows); mySqlConnection.Close(); foreach (DataTable myDataTable in myDataSet.Tables) { Console.WriteLine(" Reading from the " + myDataTable.TableName + "DataTable"); foreach (DataRow myDataRow in myDataTable.Rows) { foreach (DataColumn myDataColumn in myDataTable.Columns) { Console.WriteLine(myDataColumn + "= " + myDataRow[myDataColumn]); } } } } }