none
Select colums from a datarow RRS feed

  • Question

  • Hi,

    Before I ask the question I need to explain why I have the worst designed database ever. The data comes from a sort of legacy computers that dump textfiles and they are converted into a access database, it is all text and there are no keys. Places where a value should be, could also contain a link to a other place where the value is stored. The computers were never disigend for databases and databases not for their data. Never the less, the database is very usefull ans saves me a lot of time.

    I need to know if there  is a way to get a datarow from a datatable with only the colums I need. The code i am using now is listed below

    string Parts = "[ENGINE] = " + ID;
    DataRow[] data = CrankShaft.Select(Parts);

    This returns all the colums but I need only a couple of them so the string Parts should contain "[Part1][Part2][Part3] where ID = " + ID But, offcourse that does not work. Is there a "easy" way of getting a DataRow with the data I need?

    Thans in advance,

    Rob.

     

    • Edited by AnotherRob Wednesday, November 7, 2018 10:37 PM
    Wednesday, November 7, 2018 10:34 PM

All replies

  • Hi AnotherRob,

    Thank you for posting here.

    For your question, here is a simple example for your reference.

    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SqlClient;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    
    namespace test1
    {
        class Program
        {
            static void Main(string[] args)
            {
                string strcon = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=Exersize;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";
                SqlConnection sqlConnection = new SqlConnection(strcon);
                sqlConnection.Open();
                string sql = "select ID,Part1,Part2,Part3,Part4 from Test";
                SqlDataAdapter sda = new SqlDataAdapter(sql, sqlConnection);
                DataSet dataSet = new DataSet();
                sda.Fill(dataSet, "Test");
                DataTable table = dataSet.Tables["Test"];
                Console.WriteLine("Please  input ID to select Parts");
                string ID = Console.ReadLine();
                string Parts = "ID="+ID;
               DataRow []data = table.Select(Parts);
                for (int i = 0; i < data.Length; i++)
                {
                    Console.WriteLine("Part1's data is"+" "+data[i]["Part1"]);
                    Console.WriteLine("Part2's data is" + " " + data[i]["Part2"]);
                    Console.WriteLine("Part3's data is" + " " + data[i]["Part3"]);
                }
                Console.ReadKey();
    
            }
        }
    }
    

    Database:

    Results:

    Best Regards,

    Wendy


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, November 8, 2018 6:10 AM
    Moderator
  • Wendy,

    Thanks for answering my question, by the looks of it you took the time to program a example :-)

    This works well but now the datarow contains only the columns you specified in the SQL query. What I need is a datarow which is a subselection of a datarow already loaded into memory.

    So in short, Is it possible to extract a couple of columns into a datarow from a big datarow, or select these columns from a datatable into a datarow.

    DataRow []data = table.Select(Parts); selects all the columns, DataRow []data = table.Select(col1, col2 where ID=2); does not work.

    Thursday, November 8, 2018 7:23 PM
  • Hi AnotherRob,

    Thank you for feedback.

    >> This works well but now the datarow contains only the columns you specified in the SQL query.

    Change:

    string sql = "select ID,Part1,Part2,Part3,Part4 from Test";

    To:

    string sql = "select * from Test";

    Change the code, it would contains all the columns in the database.

    >> So in short, Is it possible to extract a couple of columns into a datarow from a big datarow, or select these columns from a datatable into a datarow.

    DataRow []data = table.Select(Parts);

    When using Datable to query with select, the Parts  format  would contain all the couple of columns with "ID=2".

    >> DataRow []data = table.Select(col1, col2 where ID=2); does not work.

    The Datatable do not support the select format you provided.

    Best Regards,

    Wendy


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, November 9, 2018 8:03 AM
    Moderator
  • Wendy,

    I have read the entire internet to come to the conclusion that what I need is not possible in C#. All the queries I need to get the data I need takes forever and thats why I posted the question. But better to wait for the program then searching the data myself :-)

    Thanks for taking the time to answer my question.

    Regards,

    Rob.

    Saturday, November 10, 2018 8:38 PM
  • DataTable dat = CrankShaft.DefaultView.ToTable(false, new string[] { "part1", "part2" });

    string Parts = "[ENGINE] = " + ID;
    DataRow[] data = dat.Select(Parts);


    • Edited by v-xialiu Sunday, November 11, 2018 5:21 AM
    Sunday, November 11, 2018 5:20 AM
  • Hi Rob,

    Don't give up! It *is* possible ... you could use LINQ. In this example, the columns from the existing DataTable will still be there, but they won't contain any data:

    DataRow[] data = CrankShaft.AsEnumerable().Select(row =>
                        {
                            DataRow newRow = dt.NewRow();
                            newRow["Part1"] = row.Field<string>("Part1");
                            newRow["Part2"] = row.Field<string>("Part2");
                            newRow["Part3"] = row.Field<string>("Part3");
                            return newRow;
                        }).Where(row => row.Field<string>("ENGINE") == ID).ToArray();

    I didn't actually test this, but the syntax should be right (hopefully, as it's kind of off the top of my head). Hopefully it will work too!   ;0)


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Sunday, November 11, 2018 5:54 PM
    Moderator
  • Bonnie,

    I did not give up on the project but it makes a lot of queries and somehow datatables are slowing down the program. So I stopped querying the database and loaded the database into array's. This does not work yet because my knowledge of programming in .net is zero. And of course, every solution I come up with creates two more difficulties :-)

    But if I cannot make it work, ill test the solution you showed and also from xialiu.

    Wednesday, November 14, 2018 10:27 PM

  • Hi Rob,

    So, I'm curious ... what do you mean by "So I stopped querying the database and loaded the database into array's"?  Why would you load anything into arrays? What are you loading into arrays? Do you mean the entire database? All the tables? It sounds like too much of a "brute force" methodology to me. If you already have a DataSet and DataTables (which it sounds like you still have), you should make use of them.

    I think you should at least give my suggestion a try before going down the rabbit-hole you seem to be attempting.


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Saturday, November 17, 2018 5:41 PM
    Moderator
  • Hi Bonnie,

    The database I load is a "Static" database because it is a textdump from "legacy" computers converted into a database. Then I dump a list of items I need some values from into a textbox and then the program starts querying the database. But when analysing the program I noticed that the queries to the database consumed a LOT of time. I dropped 3500 strings in the box and had to wait at least 40+ seconds. Now with the database into memory (array's and dictionary's) it takes far less time. 

    It is not finished yet but 800 strings takes less than a second now. So it is about 175 times faster (if my calculations are correct) (just smoked a sigaret and think it is about 40 times faster)

    And yes, it is a brute force but since the database isnt "live" and I dont have to write to it ever, and I am extremely impacient, so I thought I'd go for the speed :-) There is one table left however, that is a index I still make queries to but that stays in memory and does not slow down the program.




    • Edited by AnotherRob Saturday, November 17, 2018 11:08 PM
    Saturday, November 17, 2018 10:22 PM
  • I agree that you shouldn't have to go out to the database for every query, in your situation anyway.

    Your solution works (and quickly), and you're happy … that's all that matters!  =0)


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Saturday, November 17, 2018 11:17 PM
    Moderator