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]); } } } } }
Database ADO.net
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]); } } } } }
Populate a DataSet object using a SELECT statement
using System; using System.Data; using System.Data.SqlClient; class PopulateDataSetUsingSelect { public static void Main() { SqlConnection mySqlConnection = new SqlConnection("server=localhost;database=Northwind;uid=sa;pwd=sa"); SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); mySqlCommand.CommandText = "SELECT TOP 5 ProductID, ProductName, UnitPrice " + "FROM Products " + "ORDER BY ProductID"; SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(); mySqlDataAdapter.SelectCommand = mySqlCommand; DataSet myDataSet = new DataSet(); mySqlConnection.Open(); Console.WriteLine("Retrieving rows from the Products table"); int numberOfRows = mySqlDataAdapter.Fill(myDataSet, "Products"); Console.WriteLine("numberOfRows = " + numberOfRows); mySqlConnection.Close(); DataTable myDataTable = myDataSet.Tables["Products"]; foreach (DataRow myDataRow in myDataTable.Rows) { Console.WriteLine("ProductID = " + myDataRow["ProductID"]); Console.WriteLine("ProductName = " + myDataRow["ProductName"]); Console.WriteLine("UnitPrice = " + myDataRow["UnitPrice"]); } } }
How to perform a SELECT statement and store the returned rows in a DataSet object
using System; using System.Data; using System.Data.SqlClient; class SelectIntoDataSet { public static void Main() { string connectionString = "server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI"; SqlConnection mySqlConnection = new SqlConnection(connectionString); string selectString = "SELECT TOP 10 ID, FirstName, LastName FROM Employee ORDER BY ID"; SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); mySqlCommand.CommandText = selectString; SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(); mySqlDataAdapter.SelectCommand = mySqlCommand; DataSet myDataSet = new DataSet(); mySqlConnection.Open(); Console.WriteLine("Retrieving rows from the Employee table"); mySqlDataAdapter.Fill(myDataSet, "Employee"); mySqlConnection.Close(); DataTable myDataTable = myDataSet.Tables["Employee"]; foreach (DataRow myDataRow in myDataTable.Rows) { Console.WriteLine("ID = "+ myDataRow["ID"]); Console.WriteLine("FirstName = "+ myDataRow["FirstName"]); Console.WriteLine("LastName = "+ myDataRow["LastName"]); } } }
Finding Data
using System; using System.Data; using System.Data.SqlClient; 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, CompanyName FROM Customers", thisConnection); SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter); DataSet thisDataSet = new DataSet(); thisAdapter.Fill(thisDataSet, "Customers"); Console.WriteLine("# rows before change: {0}",thisDataSet.Tables["Customers"].Rows.Count); DataColumn[] keys = new DataColumn[1]; keys[0] = thisDataSet.Tables["Customers"].Columns["CustomerID"]; thisDataSet.Tables["Customers"].PrimaryKey = keys; DataRow findRow = thisDataSet.Tables["Customers"].Rows.Find("AAA"); if (findRow == null) { DataRow thisRow = thisDataSet.Tables["Customers"].NewRow(); thisRow["CustomerID"] = "AAA"; thisRow["CompanyName"] = "AAA Ltd."; thisDataSet.Tables["Customers"].Rows.Add(thisRow); if ((findRow = thisDataSet.Tables["Customers"].Rows.Find("AAA")) != null) { Console.WriteLine("added"); } } else { Console.WriteLine("AAA already present in database"); } thisAdapter.Update(thisDataSet, "Customers"); Console.WriteLine("# rows after change: {0}",thisDataSet.Tables["Customers"].Rows.Count); thisConnection.Close(); } }
Print DataSet out
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
class Program {
static void Main(string[] args) {
string cnStr = “uid=sa;pwd=;Initial Catalog=yourDatabase;Data Source=(local)”;
DataSet myDS = new DataSet(“Cars”);
SqlDataAdapter dAdapt = new SqlDataAdapter(“Select * From Inventory”, cnStr);
DataTableMapping custMap = dAdapt.TableMappings.Add(“Inventory”, “Current Inventory”);
custMap.ColumnMappings.Add(“CarID”, “Car ID”);
custMap.ColumnMappings.Add(“PetName”, “Name of Car”);
try {
dAdapt.Fill(myDS, “Inventory”);
} catch (Exception ex) {
Console.WriteLine(ex.Message);
}
PrintDataSet(myDS);
}
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]
The use of the Find() and FindRows() methods of a DataView to find DataRowView objects
using System;
using System.Data;
using System.Data.SqlClient;
class FindingDataRowViews
{
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 ” +
“FROM Employee”;
SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
mySqlDataAdapter.SelectCommand = mySqlCommand;
DataSet myDataSet = new DataSet();
mySqlConnection.Open();
mySqlDataAdapter.Fill(myDataSet, “Employee”);
mySqlConnection.Close();
DataTable employeeDT = myDataSet.Tables[“Employee”];
string filterExpression = “ID = 9”;
string sortExpression = “ID”;
DataViewRowState rowStateFilter = DataViewRowState.OriginalRows;
DataView employeeDV = new DataView();
employeeDV.Table = employeeDT;
employeeDV.RowFilter = filterExpression;
employeeDV.Sort = sortExpression;
employeeDV.RowStateFilter = rowStateFilter;
foreach (DataRowView myDataRowView in employeeDV)
{
for (int count = 0; count < employeeDV.Table.Columns.Count; count++)
{
Console.WriteLine(myDataRowView[count]);
}
Console.WriteLine("");
}
int index = employeeDV.Find("8");
Console.WriteLine("8 found at index " + index + "
");
DataRowView[] employeeDRVs = employeeDV.FindRows("8");
foreach (DataRowView myDataRowView in employeeDRVs)
{
for (int count = 0; count < employeeDV.Table.Columns.Count; count++)
{
Console.WriteLine(myDataRowView[count]);
}
Console.WriteLine("");
}
}
}
[/csharp]