|
using System;
using System.Data;
using System.Data.SqlClient;
class FindFilterAndSortDataRows {
public static void Main() {
SqlConnection mySqlConnection = new SqlConnection("server=localhost;database=Northwind;uid=sa;pwd=sa");
SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
mySqlCommand.CommandText =
"SELECT TOP 10 ProductID, ProductName " +
"FROM Products " +
"ORDER BY ProductID;" +
"SELECT TOP 10 OrderID, ProductID, UnitPrice, " +
" Quantity FROM [Order Details] " +
"ORDER BY OrderID";
SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
mySqlDataAdapter.SelectCommand = mySqlCommand;
DataSet myDataSet = new DataSet();
mySqlConnection.Open();
mySqlDataAdapter.Fill(myDataSet);
mySqlConnection.Close();
myDataSet.Tables["Table"].TableName = "Products";
myDataSet.Tables["Table1"].TableName = "Order Details";
DataTable productsDataTable = myDataSet.Tables["Products"];
productsDataTable.PrimaryKey = new DataColumn[] { productsDataTable.Columns["ProductID"] };
DataTable orderDetailsDataTable = myDataSet.Tables["Order Details"];
orderDetailsDataTable.Constraints.Add("Primary key constraint on the OrderID" +
"and ProductID columns", new DataColumn[]
{orderDetailsDataTable.Columns["OrderID"],
orderDetailsDataTable.Columns["ProductID"]
},
true
);
DataRow productDataRow = productsDataTable.Rows.Find("3");
foreach (DataColumn myDataColumn in productsDataTable.Columns) {
Console.WriteLine(myDataColumn + " = " + productDataRow[myDataColumn]);
}
object[] orderDetails = new object[] { 10248, 11 };
DataRow orderDetailDataRow = orderDetailsDataTable.Rows.Find(orderDetails);
foreach (DataColumn myDataColumn in
orderDetailsDataTable.Columns) {
Console.WriteLine(
myDataColumn + " = " +
orderDetailDataRow[myDataColumn]);
}
DataRow[] productDataRows = productsDataTable.Select("ProductID <= 5", "ProductID DESC", DataViewRowState.OriginalRows);
foreach (DataRow myDataRow in productDataRows) {
foreach (DataColumn myDataColumn in productsDataTable.Columns) {
Console.WriteLine(myDataColumn + " = " + myDataRow[myDataColumn]);
}
}
productDataRows = productsDataTable.Select("ProductName LIKE 'Cha*'", "ProductID ASC, ProductName DESC");
foreach (DataRow myDataRow in productDataRows) {
foreach (DataColumn myDataColumn in productsDataTable.Columns) {
Console.WriteLine(myDataColumn + " = " + myDataRow[myDataColumn]);
}
}
}
}
|
