Finding Data

Posted on February 11, 2011June 27, 2015 by coder

   




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

Posted on February 11, 2011June 27, 2015 by coder

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

Posted on February 11, 2011June 27, 2015 by coder

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]

Adding Data

Posted on February 11, 2011June 27, 2015 by coder

   


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, 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);

        DataRow thisRow = thisDataSet.Tables["Customers"].NewRow();
        thisRow["CustomerID"] = "ZACZI";
        thisRow["CompanyName"] = "Zachary Zithers Ltd.";
        thisDataSet.Tables["Customers"].Rows.Add(thisRow);

        Console.WriteLine("# rows after change: {0}",
                          thisDataSet.Tables["Customers"].Rows.Count);

        thisAdapter.Update(thisDataSet, "Customers");

        thisConnection.Close();
    }
}

Find, filter, and sort DataRow objects

Posted on February 11, 2011June 27, 2015 by coder

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]); } } } } [/csharp]

Setting the Nested property of a DataRelation to true

Posted on February 11, 2011June 27, 2015 by coder

   

using System;
using System.Data;
using System.Data.SqlClient;

class NestedXml
{
  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 EmployeeID, CompanyName " +
      "FROM Employee " +
      "ORDER BY EmployeeID;"  +
      "SELECT DeptID, EmployeeID, City " +
      "FROM Dept " +
      "WHERE EmployeeID IN (" +
      "  SELECT TOP 2 EmployeeID " +
      "  FROM Employee " +
      "  ORDER BY EmployeeID " +
      ")";
    SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
    mySqlDataAdapter.SelectCommand = mySqlCommand;
    DataSet myDataSet = new DataSet();
    mySqlConnection.Open();
    int numberOfRows = mySqlDataAdapter.Fill(myDataSet);
    Console.WriteLine("numberOfRows = " + numberOfRows);
    mySqlConnection.Close();
    DataTable customersDT = myDataSet.Tables["Table"];
    DataTable ordersDT = myDataSet.Tables["Table1"];

    DataRelation customersDeptDataRel =
      new DataRelation(
        "EmployeeDept",
        customersDT.Columns["EmployeeID"],
        ordersDT.Columns["EmployeeID"]
      );
    myDataSet.Relations.Add(
      customersDeptDataRel
    );

    myDataSet.WriteXml("nonNestedXmlFile.xml");

    myDataSet.Relations["EmployeeDept"].Nested = true;

    myDataSet.WriteXml("nestedXmlFile.xml");
  }
}

Set up DataRelation

Posted on February 11, 2011June 27, 2015 by coder

   




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();

        SqlDataAdapter custAdapter = new SqlDataAdapter(
             "SELECT * FROM Customers", thisConnection);
        SqlDataAdapter orderAdapter = new SqlDataAdapter(
             "SELECT * FROM Orders", thisConnection);
        custAdapter.Fill(thisDataSet, "Customers");
        orderAdapter.Fill(thisDataSet, "Orders");

        DataRelation custOrderRel = thisDataSet.Relations.Add("CustOrders",
             thisDataSet.Tables["Customers"].Columns["CustomerID"],
             thisDataSet.Tables["Orders"].Columns["CustomerID"]);

        foreach (DataRow custRow in thisDataSet.Tables["Customers"].Rows) {
            Console.WriteLine("Customer ID: " + custRow["CustomerID"] +
                              " Name: " + custRow["CompanyName"]);
            foreach (DataRow orderRow in custRow.GetChildRows(custOrderRel)) {
                Console.WriteLine("  Order ID: " + orderRow["OrderID"]);
            }
        }
        thisConnection.Close();
    }
}

Page 429 of 815

« 1 … 427 428 429 430 431 … 815 »