none
DataSets and DataTables and Cursors RRS feed

  • Question

  • Hi!

    Is it possible to use cursors in a DataTable? Maybe I don't need cursors though, the original sql query is written in foxpro-sql. The result from the ERP system is an ADO Recordset which I convert to a DataTable using DataAdapter and Fill method.

    The thing I want to accomplish is to create 4 temporary DataTables and use the fields of one DataTable in another DataTable and finally run a query at the last temporary DataTable.

    Is  it possible to do this?

    Best regards,

    Per
    Monday, September 7, 2009 7:35 AM

Answers

  • if you want to read recordy one by one from datatable you can use "foreach" statement like this:
    foreach(datarow row in datatablename.rows)
    {
           string s = row["myfield"] .tostring();
    }
    you can do like the above code.

    let me know if you have any problem

    Pishkari
    Monday, September 7, 2009 11:42 AM
  • LINQ or DataTable.Select should be doing the job.
    Code below should be self explanatory, for each customer, get all customer orders from order table using customer id:

        class Program
        {
            static void Main(string[] args)
            {
                DataSet ds = CreateDataSet();
    
                Console.WriteLine("\nUsing LINQ...");
                var custQuery = from customers in ds.Tables["CustomerTable"].AsEnumerable() select customers;
                foreach (DataRow customerRow in custQuery)
                {
                    string customerId = customerRow["CustId"].ToString();
                    Console.WriteLine("Customer: {0}", customerId);
                    var orderQuery = from orders in ds.Tables["OrderTable"].AsEnumerable() 
                                     where orders["CustId"].ToString().Equals(customerId) select orders;
                    foreach (DataRow orderRow in orderQuery)
                    {
                        Console.WriteLine("\t{0}", orderRow["OrderId"]);
                    }
                }
    
                Console.WriteLine("\nUsing Select...");
                foreach (DataRow customerRow in ds.Tables["CustomerTable"].Rows)
                {
                    string customerId = customerRow["CustId"].ToString();
                    Console.WriteLine("Customer: {0}", customerId);
                    DataRow[] rows = ds.Tables["OrderTable"].Select("CustId = '" + customerId + "'");
                    foreach (DataRow orderRow in rows)
                    {
                        Console.WriteLine("\t{0}", orderRow["OrderId"]);
                    }
                }
            }
    
            private static DataSet CreateDataSet()
            {
                DataTable dt_1 = new DataTable("CustomerTable");
                dt_1.Columns.Add(new DataColumn("CustId", typeof(string)));
                DataTable dt_2 = new DataTable("OrderTable");
                dt_2.Columns.Add(new DataColumn("CustId", typeof(string)));
                dt_2.Columns.Add(new DataColumn("OrderId", typeof(string)));
    
                DataSet ds = new DataSet("OurDataSet");
                ds.Tables.Add(dt_1);
                ds.Tables.Add(dt_2);
                // Add some customers
                dt_1.Rows.Add(new object[]{"CUST_1"});
                dt_1.Rows.Add(new object[]{"CUST_2"});
                dt_1.Rows.Add(new object[]{"CUST_3"});
                // Add some orders
                dt_2.Rows.Add(new object[] { "CUST_1", "CU_1_ORDER_1" });
                dt_2.Rows.Add(new object[] { "CUST_2", "CU_2_ORDER_1" });
                dt_2.Rows.Add(new object[] { "CUST_2", "CU_2_ORDER_2" });
                dt_2.Rows.Add(new object[] { "CUST_2", "CU_2_ORDER_3" });
                dt_2.Rows.Add(new object[] { "CUST_3", "CU_3_ORDER_1" });
                dt_2.Rows.Add(new object[] { "CUST_3", "CU_3_ORDER_2" });
                return ds;
            }
        }
    HTH
    //Michael

    This posting is provided "AS IS" with no warranties.
    Monday, September 7, 2009 1:12 PM
  • for faster performance when iterating through a DataTable, use the DataTableReader

    //Example
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Data;
    
    namespace ConsoleApplication1
    {
        class Program
        {
            static void Main(string[] args)
            {
                DataTable table = new DataTable("MyTable");
                table.Columns.Add("FirstName", typeof(String));
                table.Columns.Add("LastName", typeof(String));
    
                table.Rows.Add("John", "Grove");
                table.Rows.Add("Pamela", "Anderson");
                table.Rows.Add("Carmen", "Elecktra");
                table.Rows.Add("Vlad", "Dracula");
                table.Rows.Add("Harry", "Potter");
                table.Rows.Add("Billy", "Jack");
    
                DataTableReader rdr = table.CreateDataReader();
                Int32 firstName = rdr.GetOrdinal("FirstName");
                Int32 lastName = rdr.GetOrdinal("LastName");
    
                while (rdr.Read())
                {
                    Console.WriteLine("{0} {1}", rdr.GetString(firstName),
                        rdr.GetString(lastName));
                }
                Console.ReadLine();
            }
        }
    }
    

    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    • Marked as answer by Yichun_Feng Monday, September 14, 2009 1:29 AM
    Thursday, September 10, 2009 9:57 PM

All replies

  • if you want to read recordy one by one from datatable you can use "foreach" statement like this:
    foreach(datarow row in datatablename.rows)
    {
           string s = row["myfield"] .tostring();
    }
    you can do like the above code.

    let me know if you have any problem

    Pishkari
    Monday, September 7, 2009 11:42 AM
  • Thanks, but how can I query a DataTable by using output from another DataTable? The tables doesn't have the same structure.

    Best regards,

    Per
    Monday, September 7, 2009 12:18 PM
  • LINQ or DataTable.Select should be doing the job.
    Code below should be self explanatory, for each customer, get all customer orders from order table using customer id:

        class Program
        {
            static void Main(string[] args)
            {
                DataSet ds = CreateDataSet();
    
                Console.WriteLine("\nUsing LINQ...");
                var custQuery = from customers in ds.Tables["CustomerTable"].AsEnumerable() select customers;
                foreach (DataRow customerRow in custQuery)
                {
                    string customerId = customerRow["CustId"].ToString();
                    Console.WriteLine("Customer: {0}", customerId);
                    var orderQuery = from orders in ds.Tables["OrderTable"].AsEnumerable() 
                                     where orders["CustId"].ToString().Equals(customerId) select orders;
                    foreach (DataRow orderRow in orderQuery)
                    {
                        Console.WriteLine("\t{0}", orderRow["OrderId"]);
                    }
                }
    
                Console.WriteLine("\nUsing Select...");
                foreach (DataRow customerRow in ds.Tables["CustomerTable"].Rows)
                {
                    string customerId = customerRow["CustId"].ToString();
                    Console.WriteLine("Customer: {0}", customerId);
                    DataRow[] rows = ds.Tables["OrderTable"].Select("CustId = '" + customerId + "'");
                    foreach (DataRow orderRow in rows)
                    {
                        Console.WriteLine("\t{0}", orderRow["OrderId"]);
                    }
                }
            }
    
            private static DataSet CreateDataSet()
            {
                DataTable dt_1 = new DataTable("CustomerTable");
                dt_1.Columns.Add(new DataColumn("CustId", typeof(string)));
                DataTable dt_2 = new DataTable("OrderTable");
                dt_2.Columns.Add(new DataColumn("CustId", typeof(string)));
                dt_2.Columns.Add(new DataColumn("OrderId", typeof(string)));
    
                DataSet ds = new DataSet("OurDataSet");
                ds.Tables.Add(dt_1);
                ds.Tables.Add(dt_2);
                // Add some customers
                dt_1.Rows.Add(new object[]{"CUST_1"});
                dt_1.Rows.Add(new object[]{"CUST_2"});
                dt_1.Rows.Add(new object[]{"CUST_3"});
                // Add some orders
                dt_2.Rows.Add(new object[] { "CUST_1", "CU_1_ORDER_1" });
                dt_2.Rows.Add(new object[] { "CUST_2", "CU_2_ORDER_1" });
                dt_2.Rows.Add(new object[] { "CUST_2", "CU_2_ORDER_2" });
                dt_2.Rows.Add(new object[] { "CUST_2", "CU_2_ORDER_3" });
                dt_2.Rows.Add(new object[] { "CUST_3", "CU_3_ORDER_1" });
                dt_2.Rows.Add(new object[] { "CUST_3", "CU_3_ORDER_2" });
                return ds;
            }
        }
    HTH
    //Michael

    This posting is provided "AS IS" with no warranties.
    Monday, September 7, 2009 1:12 PM
  • for faster performance when iterating through a DataTable, use the DataTableReader

    //Example
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Data;
    
    namespace ConsoleApplication1
    {
        class Program
        {
            static void Main(string[] args)
            {
                DataTable table = new DataTable("MyTable");
                table.Columns.Add("FirstName", typeof(String));
                table.Columns.Add("LastName", typeof(String));
    
                table.Rows.Add("John", "Grove");
                table.Rows.Add("Pamela", "Anderson");
                table.Rows.Add("Carmen", "Elecktra");
                table.Rows.Add("Vlad", "Dracula");
                table.Rows.Add("Harry", "Potter");
                table.Rows.Add("Billy", "Jack");
    
                DataTableReader rdr = table.CreateDataReader();
                Int32 firstName = rdr.GetOrdinal("FirstName");
                Int32 lastName = rdr.GetOrdinal("LastName");
    
                while (rdr.Read())
                {
                    Console.WriteLine("{0} {1}", rdr.GetString(firstName),
                        rdr.GetString(lastName));
                }
                Console.ReadLine();
            }
        }
    }
    

    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    • Marked as answer by Yichun_Feng Monday, September 14, 2009 1:29 AM
    Thursday, September 10, 2009 9:57 PM